本文主要是java整合apache poi,可以实现下载到本地或者返回输出流的util。
代码还有很大的优化空间,自行优化!!!
STEP1:
导入依赖:
我用的3.15版本
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${apache.poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${apache.poi.version}</version>
</dependency>
STEP2:
参数dataList,就是对象的集合,比如User对象,那么传入的就是List<User>,会自动解析生成表头(第一列);
Util如下:
package com.dzh.extra.utils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
public class POIUtil {
/**
* 写到本地指定目录
*/
public static void writeExcelData(List<Map> dataList, String filePath){
// String filePath = new StringBuffer("E:\\es_less\\").append(index).append("_").append(differentList.size()).append(XLSX).toString();
try {
XSSFWorkbook workbook = getWork(dataList);
FileOutputStream fos = new FileOutputStream(filePath);
workbook.write(fos);
System.out.println("写入成功");
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 输出流
*/
public static ByteArrayOutputStream getExcelOutStream(List<Map> dataList){
try {
XSSFWorkbook workbook = getWork(dataList);
ByteArrayOutputStream out = new ByteArrayOutputStream();
workbook.write(out);
return out;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private static XSSFWorkbook getWork(List<Map> dataList) throws Exception{
if (CollectionUtil.isEmpty(dataList)){
return null;
}
List<String> columns = new ArrayList<>();
Map mapFirst = dataList.get(0);
Iterator iterator = mapFirst.entrySet().iterator();
while(iterator.hasNext()){
java.util.Map.Entry entry = (java.util.Map.Entry)iterator.next();
columns.add(String.valueOf(entry.getKey()));
}
// 创建文档以及工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("sheet1");
// 写入第一行表头(标题)
XSSFRow row = sheet.createRow(0);
for (int i = 0; i < columns.size(); i++) {
row.createCell(i).setCellValue(columns.get(i));
}
// 具体数据
for (int i = 0; i < dataList.size(); i++) {
XSSFRow row1 = sheet.createRow(i + 1);
Map map = dataList.get(i);
// 创建单元格并设值
for (int j = 0; j < columns.size(); j++) {
row1.createCell(j).setCellValue(String.valueOf(map.get(columns.get(j))));
}
}
return workbook;
}
}