easyExcel使用总结
1、数据库查询出来的数据写入excel文件中,不使用实体类
package com.fgi.importdata.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.fgi.importdata.service.impl.ModelLogServiceImpl;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class ExcelUtils {
private static ExcelUtils instance = new ExcelUtils();
private ModelLogServiceImpl modelLogService;
public ExcelUtils(ModelLogServiceImpl modelLogService) {
this.modelLogService = modelLogService;
}
private ExcelUtils() {
}
public static ExcelUtils getInstance() {
return instance;
}
public String createExcel(List<Map<String, Object>> mapList, String filename, String title) {
Map<String, Object> map = mapList.get(0);
Set<String> stringSet = map.keySet();
ArrayList<String> headList = new ArrayList<>(stringSet);
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(title);
sheet.setDefaultRowHeight((short) (2 * 256));
for (int i = 0; i < headList.size(); i++) {
sheet.setColumnWidth(i, 8000);
}
XSSFFont font = wb.createFont();
font.setFontName("等线");
font.setFontHeightInPoints((short) 16);
XSSFRow titleRow = sheet.createRow(0);
XSSFCell titleCell = titleRow.createCell(0);
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headList.size() - 1));
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
titleCell.setCellStyle(cellStyle);
XSSFRow row = sheet.createRow(1);
for (int i = 0; i < headList.size(); i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(headList.get(i));
}
XSSFRow rows;
XSSFCell cells;
for (int i = 0; i < mapList.size(); i++) {
rows = sheet.createRow(i + 2);
for (int j = 0; j < headList.size(); j++) {
String value = mapList.get(i).get(headList.get(j)).toString();
cells = rows.createCell(j);
cells.setCellValue(value);
}
}
try {
File file = new File("D:\\test\\file\\" + filename);
FileOutputStream fileOutputStream = new FileOutputStream(file);
wb.write(fileOutputStream);
wb.close();
fileOutputStream.close();
System.out.println("生成成功!");
} catch (IOException e) {
e.printStackTrace();
}
return filename;
}
public static void main(String[] args) {
System.out.println("数据加载...");
List<Map<String, Object>> mapArrayList = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Map<String, Object> map = new HashMap<>();
map.put("姓名", i);
map.put("年龄", i);
map.put("性别", i);
mapArrayList.add(map);
}
System.out.println("数据加载完成...");
ExcelUtils.getInstance().createExcel(mapArrayList, "文件名", "测试数据");
}
}
2、同源写入多个sheet中
public <T, E> String createExcel02(Integer num, String filePath, Class<T> clazz, List<E> dataList) {
ExcelWriter excelWriter = EasyExcel.write(filePath, clazz).build();
int sheetNum = (dataList.size() + (num - 1)) / num;
for (int i = 0; i < sheetNum; i++) {
WriteSheet sheet = EasyExcel.writerSheet("sheet" + (i + 1)).build();
int startIndex = i * num;
int endIndex = Math.min(startIndex + num, dataList.size());
List<E> sheetData = dataList.subList(startIndex, endIndex);
excelWriter.write(sheetData, sheet);
}
excelWriter.finish();
return filePath;
}