Excel是我们平时工作中比较常用的用于存储二维表数据的,JAVA也可以直接对Excel进行操作,在之前那篇文章中介绍了用jxl操作Excel。但是jxl有个不足之处就是只能操作.xls格式的Excel文件,下面将介绍一种新的Java操作Excel既可以操作.xls格式的Excel文件,有可以操作.xlsx格式的Excel。
案例代码:
像Excel中写数据
package com.test.excel;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class PoiWriteDemo {
public static void main(String[] args) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
for (int row = 0; row < 10; row++) {
HSSFRow rows = sheet.createRow(row);
for (int col = 0; col < 10; col++) {
rows.createCell(col).setCellValue("data" + row + col);
}
}
File xlsFile = new File("poi.xlsx");
FileOutputStream xlsStream = new FileOutputStream(xlsFile);
workbook.write(xlsStream);
}
}
从Excel中读取数据
package com.test.excel;
import java.io.File;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class PoiReadDemo {
public static void main(String[] args) throws IOException, InvalidFormatException {
File xlsFile = new File("poi.xlsx");
Workbook workbook = WorkbookFactory.create(xlsFile);
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
Sheet sheet = workbook.getSheetAt(i);
int rows = sheet.getLastRowNum() + 1;
Row tmp = sheet.getRow(0);
if (tmp == null) {
continue;
}
int cols = tmp.getPhysicalNumberOfCells();
for (int row = 0; row < rows; row++) {
Row r = sheet.getRow(row);
for (int col = 0; col < cols; col++) {
System.out.printf("%10s", r.getCell(col).getStringCellValue());
}
System.out.println();
}
}
}
}