生成EXCEL和CSV文件
通过POI操作生成EXCEL本身没有什么难度，主要记住以下几个概念就行。
- 引包，excel本身有不同版本，POI包也有所不同。
- workbook、sheet、row、cell、style之间的关系
添加maven引用
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>5.7.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
workbook、sheet、row、cell、style之间的关系
- XSSFWorkbook: 可以看作整个excel文件
- XSSFSheet：excel中的一个sheet（一个XSSFWorkbook可以有多个sheet）
- XSSFRow： sheet中的行（同理一个sheet中有很多行）
- XSSFCell：单元格（一行中有很多个单元格）
- XSSFCellStyle：单元格样式，像背景颜色、边框、字体等都是通过创建XSSFCellStyle对象然后赋值给到某个具体的XSSFCell实现的
- sheet.addMergedRegion：合并多列
package com.kms.console.utils;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import com.opencsv.CSVWriter;
import org.apache.poi.ss.usermodel.*;
import com.kms.console.enums.ReportFilePrefixEnum;
import com.kms.console.enums.ReportFileTileEnum;
import com.kms.console.enums.ReportFileTypeEnum;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.opencsv.exceptions.CsvDataTypeMismatchException;
import com.opencsv.exceptions.CsvRequiredFieldEmptyException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.*;
@Slf4j
public class ReportUtil<T> {
/**
* 根据报表查询数据生成excel文件
*
* @param reportDataList 查询到的数据集合
* @param reportFileTileEnum 报表类别枚举
* @param clazz 查询到的数据实体类型，便于反射生成excel表格字段
* @param generatorDateString 报表生成日期字符串
* @param beginTime 开始时间
* @param endTime 结束时间
*/
public String createExcel(List<T> reportDataList, ReportFileTileEnum reportFileTileEnum, ReportFilePrefixEnum reportFilePrefixEnum, Class clazz, String filePath, String generatorDateString, Date beginTime, Date endTime) {
String fullFilePath = getFullFilePath(reportFilePrefixEnum, filePath, generatorDateString, ReportFileTypeEnum.XLSX);
File file = new File(fullFilePath);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
//创建工作薄
try (XSSFWorkbook wb = new XSSFWorkbook()) {
//新建一个sheet
XSSFSheet sheet = wb.createSheet("sheet1");
Field[] declaredFields = clazz.getDeclaredFields();
// 标题、汇总信息等
createBannerSegment(reportDataList, reportFileTileEnum, generatorDateString, beginTime, endTime, wb, sheet);
// 生成表格数据部分
createDataTableSegment(reportDataList, wb, sheet, declaredFields);
FileOutputStream out = null;
// 写文件
try {
out = new FileOutputStream(fullFilePath);
out.flush();
wb.write(out);
} catch (FileNotFoundException ex) {
log.error("report file generator failed,The file does not exist or the path is wrong", ex);
} catch (IOException e) {
log.error("report file generator failed,An exception occurred while writing the file ", e);
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
log.error("close FileOutputStream failed", e);
}
}
} catch (Exception ex) {
log.error("generator excel workbook failed", ex);
}
return fullFilePath;
}
/**
* 标题、汇总信息等
*
* @param reportDataList
* @param reportFileTileEnum
* @param generatorDateString
* @param beginTime
* @param endTime
* @param wb
* @param sheet
*/
private void createBannerSegment(List<T> reportDataList, ReportFileTileEnum reportFileTileEnum, String generatorDateString, Date beginTime, Date endTime, XSSFWorkbook wb, XSSFSheet sheet) {
// XSSFRow rowFirst = sheet.createRow(0);
// XSSFCell cellFirst = rowFirst.createCell(0);
// cellFirst.setCellValue(titleString);
// XSSFCellStyle rowFirstStyle = wb.createCellStyle();
// rowFirstStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// XSSFColor rowFirstColor = new XSSFColor(new java.awt.Color(0xDDEBF7), new DefaultIndexedColorMap());
// rowFirstStyle.setFillForegroundColor(rowFirstColor);
// rowFirstStyle.setAlignment(HorizontalAlignment.CENTER);
// cellFirst.setCellStyle(rowFirstStyle);
// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, declaredFields.length));
// 报表内容
// XSSFRow rowContent = sheet.createRow(2);
// XSSFCell contentCell = rowContent.createCell(0);
// contentCell.setCellValue("report content");
// XSSFCellStyle contentStyle = wb.createCellStyle();
// contentStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// XSSFColor contColor = new XSSFColor(new java.awt.Color(0xFFC000), new DefaultIndexedColorMap());
// contentStyle.setFillForegroundColor(contColor);
// contentCell.setCellStyle(contentStyle);
// 报表内容下面表格样式
XSSFCellStyle contentTableStyle = wb.createCellStyle();
contentTableStyle.setBorderBottom(BorderStyle.THIN);
contentTableStyle.setBorderTop(BorderStyle.THIN);
contentTableStyle.setBorderRight(BorderStyle.THIN);
contentTableStyle.setBorderLeft(BorderStyle.THIN);
// 报表周期
XSSFRow cycleRow = sheet.createRow(3);
XSSFCell periodCell0 = cycleRow.createCell(0);
periodCell0.setCellValue("periodNo");
periodCell0.setCellStyle(contentTableStyle);
XSSFCell periodCell1 = cycleRow.createCell(1);
periodCell1.setCellValue(reportFileTileEnum.getValue() + "00001" + generatorDateString);
periodCell1.setCellStyle(contentTableStyle);
// 开始时间
XSSFRow beginTimeRow = sheet.createRow(4);
XSSFCell beginTimeCell0 = beginTimeRow.createCell(0);
beginTimeCell0.setCellValue("startTime");
beginTimeCell0.setCellStyle(contentTableStyle);
XSSFCell beginTimeCell1 = beginTimeRow.createCell(1);
beginTimeCell1.setCellValue(DateUtils.formatDate(beginTime));
beginTimeCell1.setCellStyle(contentTableStyle);
// 结束时间
XSSFRow endTimeRow = sheet.createRow(5);
XSSFCell endTimeCell0 = endTimeRow.createCell(0);
endTimeCell0.setCellValue("endTime");
endTimeCell0.setCellStyle(contentTableStyle);
XSSFCell endTimeCell1 = endTimeRow.createCell(1);
endTimeCell1.setCellValue(DateUtils.formatDate(endTime));
endTimeCell1.setCellStyle(contentTableStyle);
// 总条数
XSSFRow totalCountRow = sheet.createRow(6);
XSSFCell totalCountCell0 = totalCountRow.createCell(0);
totalCountCell0.setCellValue("totalCount");
totalCountCell0.setCellStyle(contentTableStyle);
XSSFCell totalCountCell1 = totalCountRow.createCell(1);
totalCountCell1.setCellValue(reportDataList.size());
totalCountCell1.setCellStyle(contentTableStyle);
// dataSetTitle
// XSSFRow dataSetTitle = sheet.createRow(9);
// XSSFCell dataSetTitleCell = dataSetTitle.createCell(0);
// dataSetTitleCell.setCellValue(dataSetTitleString);
// CellStyle dataSetTitleStyle = wb.createCellStyle();
// Font dataSetTitleFont = wb.createFont();
// dataSetTitleFont.setFontHeightInPoints((short) 11);
// dataSetTitleStyle.setFont(dataSetTitleFont);
// dataSetTitleCell.setCellStyle(dataSetTitleStyle);
// sheet.addMergedRegion(new CellRangeAddress(9, 9, 0, declaredFields.length));
}
/**
* 表格数据部分
*
* @param reportDataList
* @param wb
* @param sheet
* @param declaredFields
*/
private void createDataTableSegment(List<T> reportDataList, XSSFWorkbook wb, XSSFSheet sheet, Field[] declaredFields) {
String[] headers = new String[declaredFields.length];
for (int i = 0; i < declaredFields.length; i++) {
headers[i] = declaredFields[i].getName();
}
// 表格详细数据列名称
XSSFRow dataSetHeader = sheet.createRow(10);
//写详细数据标题行的每一个单元格
for (int i = 0; i < headers.length; i++) {
//获取第一行的每个单元格
XSSFCell cell = dataSetHeader.createCell(i);
//往单元格里写数据
cell.setCellValue(headers[i]);
XSSFCellStyle style = wb.createCellStyle();
XSSFColor xssfColor = new XSSFColor(new java.awt.Color(0xFFF2CC), new DefaultIndexedColorMap());
style.setFillForegroundColor(xssfColor);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(style);
//设置每列的列宽
// sheet.trackAllColumnsForAutoSizing();
sheet.autoSizeColumn(i,true);
}
//写数据集
for (int i = 0; i < reportDataList.size(); i++) {
//创建数据行
XSSFRow row = sheet.createRow(i + dataSetHeader.getRowNum() + 1);
for (int j = 0; j < headers.length; j++) {
Field f = declaredFields[j];
f.setAccessible(true);
try {
if (f.get(reportDataList.get(i)) != null) {
XSSFCell dataCell = row.createCell(j);
CellStyle dataCommonCellStyle = wb.createCellStyle();
dataCommonCellStyle.setBorderTop(BorderStyle.THIN);
dataCommonCellStyle.setBorderBottom(BorderStyle.THIN);
dataCommonCellStyle.setBorderLeft(BorderStyle.THIN);
dataCommonCellStyle.setBorderRight(BorderStyle.THIN);
Object cellValue = f.get(reportDataList.get(i));
//设置对应单元格的值
//时间类型
if (f.getType() == Date.class) {
dataCell.setCellValue((Date) cellValue);
CreationHelper createHelper = wb.getCreationHelper();
dataCommonCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/M/d H:mm:ss"));
// 货币类型
} else if (f.getType() == BigDecimal.class) {
dataCell.setCellValue(Double.parseDouble(cellValue.toString()));
} else if (f.getType() == Long.class) {
dataCell.setCellValue((Long) cellValue);
} else {
dataCell.setCellValue(cellValue.toString());
}
dataCell.setCellStyle(dataCommonCellStyle);
} else {
row.createCell(j).setCellValue("");
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
/**
* 拼文件全路径
*
* @param reportFilePrefixEnum
* @param filePath
* @param generatorDateString
* @return
*/
private String getFullFilePath(ReportFilePrefixEnum reportFilePrefixEnum, String filePath, String generatorDateString, ReportFileTypeEnum reportFileTypeEnum) {
String fileName = reportFilePrefixEnum.getValue() + "_" + generatorDateString + "." + reportFileTypeEnum.getValue();
String fullFilePath = filePath + File.separator + fileName;
return fullFilePath;
}
public String createCsv(List<T> reportDataList, ReportFileTileEnum reportFileTileEnum, ReportFilePrefixEnum reportFilePrefixEnum, Class clazz, String filePath, String generatorDateString, Date beginTime, Date endTime) {
String fullFilePath = getFullFilePath(reportFilePrefixEnum, filePath, generatorDateString, ReportFileTypeEnum.CSV);
File file = new File(fullFilePath);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
Writer writer = null;
try {
writer = new FileWriter(fullFilePath);
CSVWriter csvWriter=new CSVWriter(writer);
// 写报表内容部分，如报表名称，报表时间段，报表总数等
String periodRowString = "periodNo," + reportFileTileEnum.getValue() + "00001" + generatorDateString;
String[] periodRow = periodRowString.split(",");
csvWriter.writeNext(periodRow);
String startTimeRowString = "startTime," + DateUtils.formatDate(beginTime);
String[] startTimeRow = startTimeRowString.split(",");
csvWriter.writeNext(startTimeRow);
String endTimeRowString = "endTime," + DateUtils.formatDate(endTime);
String[] endTimeRow = endTimeRowString.split(",");
csvWriter.writeNext(endTimeRow);
String totalCountString="totalCount,"+reportDataList.size();
String[] totalCountRow = totalCountString.split(",");
csvWriter.writeNext(totalCountRow);
//空行隔开
csvWriter.writeNext(new String[]{});
//写datatable暑假
StatefulBeanToCsv beanToCsv = new StatefulBeanToCsvBuilder(writer).build();
beanToCsv.write(reportDataList);
} catch (Exception e) {
log.error("create csv file failed", e);
} finally {
try {
if (writer != null) {
writer.close();
}
} catch (Exception ex) {
log.error("close writer failed", ex);
}
}
return fullFilePath;
}
}
总是有些基础内容用过就忘掉了，缺少总结记录。在此记录下来便于以后查找也为遇到类似问题的人一个小小的帮助。
