本文将讨论利用Apache POI提供的类实现Excel文件的读与写操作。
整个项目的目录结构基于前面的一篇文章:《java读取pdf内容》
1。pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
2。将内容写入 Excel文件
下面的代码利用apache poi把内容写入到一个excel文件中。待写入的内容放到了一个2维数组中,内容将被写入到XSSFWorkbook对象中,利用XSSFSheet指定excel中工作的sheet。
package org.thinkingingis.optionexcel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ApachePOIExcelWrite {
private static final String FILE_NAME = "/Users/gisboy/Desktop/MyFirstExcel.xlsx";
public static void main(String[] args) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet in java");
Object[][] datas = {
{"name", "address", "email"},
{"thinking", "beijing", "thinking@thinking.com"},
{"in", "beijing", "in@in.com"},
{"gis", "beijing", "gis@gis.com"}
};
int rowNum = 0;
System.out.println("apache poi is creating excel...");
for(Object[] obj : datas) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for(Object field : obj) {
Cell cell = row.createCell(colNum++);
if(field instanceof String) {
cell.setCellValue(field.toString());
} else if (field instanceof Integer) {
cell.setCellValue((Integer)field);
}
}
}
try {
FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
workbook.write(outputStream);
workbook.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
System.out.println("done!");
}
}
注:FILE_NAME根据操作系统的不同而指定。
执行上面代码,你会得到下面内容的excel:
3。读取Excel文件
下面的代码将说明通过apache poi如何读取Excel文件
package org.thinkingingis.optionexcel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.xssf.usermodel.XSSFWorkbook;
public class ApachePOIExcelRead {
private static final String FILE_NAME = "/Users/gisboy/Desktop/MyFirstExcel.xlsx";
public static void main(String[] args) {
try {
FileInputStream excelFile = new FileInputStream(FILE_NAME);
Workbook workbook = new XSSFWorkbook(excelFile);
Sheet dataSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = dataSheet.iterator();
while(iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
while(cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
if(currentCell.getCellTypeEnum() == CellType.STRING) {
System.out.print(currentCell.getStringCellValue() + "--");
}else if(currentCell.getCellTypeEnum() == CellType.NUMERIC) {
System.out.print(currentCell.getNumericCellValue() + "--");
}
}
System.out.println();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
执行上面的代码,结果如下:
4 。一些解释:
1.XSSF 是一些操作Mircosoft Excel 2007及以后的版本 类的前缀;
2.XSSFWorkbook是抽象为Excel工作簿的类;
3.XSSFSheet是Excel中sheet的抽象;
4.ROW是excel中的一行
5.Cell是excel行中单元格的地址。
参考引用:
https://poi.apache.org/apidocs/
至此,一个简单的spring boot + thymeleaf + ajax 程序 就搭建好了。
(如遇到问题,请留言给作者,以便共同探讨gis知识。thinkingingis@qq.com)
更多干货 欢迎关注微信公众号: ThinkingInGIS
当然,如果你觉得本文对你有帮助,想支持一下作者的话,也是可以赞赏一下的:)