导出EXCEL
在实际的开发过程当中,我们会遇到一些比较复杂的导出需求,例如需要导出的实体类中需要嵌套集合对象等,正好最近碰到了所以分享出来,希望对大家有帮助
一、POI是什么
简单的说就是Apache软件基金会提供的一个库,这个库里有API,API赋予了Java程序更强大的读和写的能力
二、使用步骤
这里是结果
1、POM.XML
<dependencies>
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.10</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.6.0</version>
</dependency>
</dependencies>
2、常量
public static final Integer ZERO = 0;
public static final Integer ONE = 1;
public static final Integer UNQUALIFIED_INFO_INDEX = 24;
public static final String[] TITLE_LIST = new String[]{
"汉皇重色思倾国", "御宇多年求不得", "杨家有女初长成", "养在深闺人未识", "天生丽质难自弃", "一朝选在君王侧",
"回眸一笑百媚生", "六宫粉黛无颜色", "春寒赐浴华清池", "温泉水滑洗凝脂", "侍儿扶起娇无力", "始是新承恩泽时",
"云鬓花颜金步摇", "芙蓉帐暖度春宵", "春宵苦短日高起", "从此君王不早朝", "承欢侍宴无闲暇", "春从春游夜专夜",
"后宫佳丽三千人", "三千宠爱在一身", "金屋妆成娇侍夜", "玉楼宴罢醉和春", "姊妹弟兄皆列土", "可怜光彩生门户",
"花冠不整下堂来", "风吹仙袂飘飖举", "犹似霓裳羽衣舞", "在天愿作比翼鸟", "在地愿为连理枝", "天长地久有时尽",
"此恨绵绵无绝期", "九重城阙烟尘生", "千乘万骑西南行", "西出都门百余里"};
该处为标题
3、实体类
package cn.itsaxon.vo;
import lombok.*;
import java.io.Serializable;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Getter
@Setter
public class ExportVO implements Serializable {
private String orderId = "";
private String customerProvince = "";
private String customerArea = "";
private String dealerName = "";
private String customerName = "";
private String customerTel = "";
private String customerIdCard = "";
private String inverterList = "";
private String inverterNum = "";
private String cooperationName = "";
private String customerAddress = "";
private String roofType = "";
private String singlePower = "";
private String actualNum = "";
private String actualPower = "";
private String onGridModel = "";
private String installDate = "";
private String taskType = "";
private String startInspectionDate = "";
private String endInspectionDate = "";
private String taskOperatorName = "";
private String auditor = "";
private String taskStatus = "";
private String acceptResult = "";
private String overTimeStatus = "";
private String proposalSubmitDate = "";
private String resultSubmitDate = "";
private String onGridPeriod= "";
private String riskAssessment = "";
private String applyDescribe = "";
private String handlingOpinions = "";
private String completedFlag = "";
private String unqualifiedInfo = "";
}
4、表格工具类
package cn.itsaxon.utils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
/**
* Excel工具类
*
* @author itsaxon
* @date 2021-03-17 16:39
*/
public class ExcelUtil {
/**
* 将Sheet列号变为列名
*
* @param index 列号, 从0开始
* @return 0->A; 1->B...26->AA
*/
public static String index2ColName(int index) {
if (index < 0) {
return null;
}
// A的Unicode码
int num = 65;
String colName = "";
do {
if (colName.length() > 0) {
index--;
}
int remainder = index % 26;
colName = ((char) (remainder + num)) + colName;
index = (int) ((index - remainder) / 26);
} while (index > 0);
return colName;
}
/**
* 根据表元的列名转换为列号
*
* @param colName 列名, 从A开始
* @return A1->0; B1->1...AA1->26
*/
public static int colName2Index(String colName) {
int index = -1;
// A的Unicode码
int num = 65;
int length = colName.length();
for (int i = 0; i < length; i++) {
char c = colName.charAt(i);
// 确定指定的char值是否为数字
if (Character.isDigit(c)