public String getXlsFile(List updateDscVOS) {
String exportPath = null;
FileOutputStream fileOut = null;
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFont font = wb.createFont();
//编辑字体
font.setFontHeightInPoints((short) 15);
font.setBold(true);
//设置字体大小与加粗与居中
HSSFCellStyle cellStyle = wb.createCellStyle();
//设置居中
HSSFCellStyle cellStyle2 = wb.createCellStyle();
//设置换行与居中
HSSFCellStyle cellStyle3 = wb.createCellStyle();
//设置字体
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
//设置换行
cellStyle3.setWrapText(true);
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
String fileName = new SimpleDateFormat("yyyyMMddhhMMss").format(new Date()) + "dsc.xls";
HSSFSheet sheet = wb.createSheet(fileName);
sheet.autoSizeColumn(0);
sheet.setDefaultColumnWidth(20);
//初始化表格------------------
Class aClass = updateDscVOS.get(0).getClass();
Field[] fields = aClass.getDeclaredFields();
//创建表头
HSSFRow row = sheet.createRow(0);
HSSFRow row2 = sheet.createRow(1);
int j = 0;
for (Field field : fields) {
if (j == 0) {
HSSFCell cell = row.createCell(j);
cell.setCellValue("序号");
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
1, //last row (0-based)
j, //first column (0-based)
j //last column (0-based)
));
j++;
}
if (Optional.ofNullable(field.getAnnotation(XlsPropertie.class)).isPresent()) {
XlsPropertie xlsPropertie = field.getDeclaredAnnotation(XlsPropertie.class);
HSSFCell cell = row.createCell(j);
cell.setCellValue(xlsPropertie.name());
cell.setCellStyle(cellStyle);
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
1, //last row (0-based)
j, //first column (0-based)
j //last column (0-based)
));
j++;
}
if (Optional.ofNullable(field.getAnnotation(XlsMapPropertie.class)).isPresent()) {
XlsMapPropertie xlsMapPropertie = field.getAnnotation(XlsMapPropertie.class);
String name = xlsMapPropertie.name();
HSSFCell cell = row.createCell(j);
cell.setCellValue(name);
cell.setCellStyle(cellStyle);
String[] strings = xlsMapPropertie.boundFactor();
int length = strings.length;
//对每个单元格操作 map集合需要拆解 在此处不合并 可根据需求定义*/
sheet.addMergedRegion(new CellRangeAddress(
0, //first row (0-based)
0, //last row (0-based)
j, //first column (0-based)
j + length - 1 //last column (0-based)
));
for (String string : strings) {
HSSFCell cell1 = row2.createCell(j);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(string);
j++;
}
}
}
//初始化结束---------------------------------
int l = 2;
int index = 0;
int downIndex = 1;
for (UpdateDscVO updateDscVO : updateDscVOS) {
HSSFRow rowNext = sheet.createRow(l);
HSSFCell cell = rowNext.createCell(index);
if (index == 0) {
cell.setCellValue(downIndex);
cell.setCellStyle(cellStyle2);
downIndex++;
index++;
}
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.get***());
cell.setCellStyle(cellStyle2);
index++;
cell = rowNext.createCell(index);
cell.setCellValue(new SimpleDateFormat("yyyy:MM:dd hh:MM:ss").format(updateDscVO.get***()));
cell.setCellStyle(cellStyle2);
index++;
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.get***());
cell.setCellStyle(cellStyle2);
index++;
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.get***());
cell.setCellStyle(cellStyle3);
index++;
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.get***());
cell.setCellStyle(cellStyle3);
index++;
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.get***());
cell.setCellStyle(cellStyle2);
index++;
Set> entries = updateDscVO.getUpdateCore().entrySet();
//没列的下标 递增与恢复设置列
int temp = index;
//录入更新内容与要素名
for (Map.Entry entry : entries) {
index = temp;
for (String s : layerNameList()) {
if (s.equals(entry.getKey())) {
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.getUpdateCore().get(entry.getKey()));
cell.setCellStyle(cellStyle3);
}
index++;
}
}
Set> entries1 = updateDscVO.getUpdateDetailNum().entrySet();
temp = index;
//录入统计量与要素名
for (Map.Entry entry : entries1) {
index = temp;
for (String s : layerNameList()) {
if (s.equals(entry.getKey())) {
cell = rowNext.createCell(index);
cell.setCellValue(updateDscVO.getUpdateDetailNum().get(entry.getKey()));
cell.setCellStyle(cellStyle3);
}
index++;
}
}
index = 0;
l++;
}
exportPath = path + File.separator + fileName;
fileOut = new FileOutputStream(exportPath);
wb.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (IOException e) {
throw new CommonException("文件生成失败");
} finally {
if (fileOut != null) {
try {
fileOut.close();
} catch (IOException e) {
log.info("关闭成功");
}
}
}
return exportPath;
}
public static List layerNameList() {
List layerNameList = new ArrayList<>();
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
layerNameList.add("***");
return layerNameList;
}
实体类：
public class UpdateDscVO implements Serializable {
private static final long serialVersionUID = 576879208023109952L;
private String field0;
@XlsPropertie(name = "**")
private String field1;
@XlsPropertie(name = "***")
private Date field2;
@XlsPropertie(name = "***")
private String field3;
@XlsPropertie(name = "***")
private String field4;
@XlsPropertie(name = "***")
private String field5;
@XlsPropertie(name = "***")
private String field6;
private String field7;
@XlsMapPropertie(name = "第一行",boundFactor = {"各列名1","各列名2","各列名3","各列名4"})
private Map field8;
@XlsMapPropertie(name = "第一行",boundFactor = {"各列名1","各列名2","各列名3","各列名4"})
private Map field9;
}
注解：
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@documented
public @interface XlsPropertie {
String name() default "";
}
效果图：
适合有Map集合的VO对象使用，其他情况建议使用开源框架——EazyExcel
后续可把对象优化为根据反射获取属性对应的get方法。
