Java如何使用POI读写Excel呢?

下文笔者讲述POI操作excel的方法及示例分享
学习完本篇之后,你将掌握POI操作excel的方法,如下所示

POI操作excel的实现思路

 
//1.引入相应依赖
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.17</version>
</dependency>

//2.引入HSSFWorkbook对象

//3.操作excel

例:POI操作excel的示例

package com.java265;
 
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.FillPatternType;
  
public class POItest {
	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("testsheet");//Sheet名称
		for(int i=0;i<10;i++) {
			HSSFRow row = sheet.createRow(i);//行
			for(int j=0;j<10;j++) {
				HSSFCell cell = row.createCell(j);//单元格
				String val = createString(i,j);
				setCellStyle(i,j,cell,wb);//设置样式
				cell.setCellValue(val);//设置值
			}
		}
		
		FileOutputStream fos = new FileOutputStream("D:\\Test\\exceltest.xls");//写入文件
		wb.write(fos);
		fos.flush();
		fos.close();
		System.out.println("xls finished.");
	}
 
 
	private static void setCellStyle(int i, int j, HSSFCell cell,HSSFWorkbook wb) {
		HSSFFont font1 = wb.createFont();
		HSSFCellStyle style1 = wb.createCellStyle();
		font1.setColor(HSSFColorPredefined.BLUE.getIndex());//字体格式
		font1.setBold(true);
		style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);//单元格格式
		style1.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
		style1.setFont(font1);
		HSSFFont font2 = wb.createFont();
		HSSFCellStyle style2 = wb.createCellStyle();
		font2.setColor(HSSFColorPredefined.GREY_80_PERCENT.getIndex());
		style2.setFont(font2);
		if(0==i && 0==j) {
			cell.setCellStyle(style1);
		}else if(0==i) {
			cell.setCellStyle(style1);
		}else if(0==j){
			cell.setCellStyle(style1);
		}else if(j>i){
		}else {
			cell.setCellStyle(style2);
		}
	}
 
 
	private static String createString(int i, int j) {
		if(0==i && 0==j) {
			return "九九乘法";
		}else if(0==i) {
			return j+"";
		}else if(0==j){
			return i+"";
		}else if(j>i){
			return "";
		}else {
			return j+"*"+i+"="+(i*j);
		}
	}
}

注意事项:
   如果用POI读取Excel,抛出异常
   The supplied data appears to be in the Office 2007+ XML.
   You are calling the part of POI that deals with OLE2 Office Documents. 
   You need to call a different part of POI to process this data
   (eg XSSF instead of HSSF)

可使用XSSFWorkbook和XSSFSheet处理Excel
   maven导入

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.2.0</version>
</dependency>

POI提供了两套处理Excel的工具：
  对2003版本的Excel使用HSSFWorkbook(xls后缀Excel导入poi)
  对2007版本或更高版本Excel使用XSSFWorkbook(xlsx后缀Excel导入poi-ooxml)
 

例:

@Test
public void test1() {
	try (FileInputStream ins = new FileInputStream(new File("test.xlsx"));) {
		XSSFWorkbook wb = new XSSFWorkbook(ins);
		XSSFSheet sheet0 = wb.getSheetAt(0);
		int firstRowNum = sheet0.getFirstRowNum();
		int lastRowNum = sheet0.getLastRowNum();
		System.out.println(wb);
		System.out.println(sheet0);
		System.out.println("firstRowNum=" + firstRowNum);
		System.out.println("lastRowNum=" + lastRowNum);
		XSSFRow row0 = sheet0.getRow(0);
		System.out.println("row0.firstCellNum=" + row0.getFirstCellNum());
		System.out.println("row0.LastCellNum=" + row0.getLastCellNum());
		XSSFCell row0Cell0 = row0.getCell(0);
		System.out.println("row0cell0.value=" + row0Cell0.getStringCellValue());
		System.out.println("row0cell1.value=" + row0.getCell(1).getStringCellValue());
		System.out.println("row1cell1=" + sheet0.getRow(1).getCell(1).getNumericCellValue());
	} catch(Exception e) {
		System.out.println(e);
	}
}