代码涉及到业务,仅供参考,请根据个人业务自行修改。
package com.twqc.modules.lowcode.bpm.utils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @author wsl
* @date 2023/6/14
*/
@Component
public class ExcelHutoolUtil {
/**
* 获取表头样式
*
* @param workbook 工作簿
* @return CellStyle 表头样式
*/
public CellStyle getHeadStyle(Workbook workbook) {
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setBorderTop(BorderStyle.THIN);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBold(true);
headerStyle.setFont(headerFont);
return headerStyle;
}
/**
* 获取表体样式
*
* @param workbook 工作簿
* @return CellStyle 表体样式
*/
public CellStyle getBodyStyle(Workbook workbook) {
// 创建内容样式
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
Font contentFont = workbook.createFont();
contentFont.setFontHeightInPoints((short) 11);
style.setFont(contentFont);
return style;
}
/**
* 导出Excel
*/
public void exportExcel(SXSSFWorkbook workbook, List<Map<String, Object>> dataList, List<Map<String, Object>> fieldList, String sheetName) {
Sheet sheet = workbook.createSheet(sheetName);
// 设置表头
Row headerRow = sheet.createRow(0);
for (int i = 0; i < fieldList.size(); i++) {
Map<String, Object> map = fieldList.get(i);
String columnName = map.get("name").toString();
// 必填项标识*,放在列名前面,例:*姓名
if (null != map.get("mustFlag") && "1".equals(map.get("mustFlag").toString())) {
String asterisk = "*";
columnName = asterisk + columnName;
}
Cell headerCell = headerRow.createCell(i);
headerCell.setCellValue(columnName);
CellStyle headStyle = getHeadStyle(workbook);
headerCell.setCellStyle(headStyle);
// 判断当前列是否为importOrgType列,如果是则设置下拉框的数据验证
if (isImportOrgTypeColumn(map)) {
Sheet orgTypeSheet = getOrgTypeSheet(workbook, map);
// 设置下拉框的数据验证
setDropDownValidation(sheet, i, 1, 999, orgTypeSheet);
}
}
// 输出内容
if (dataList != null && !dataList.isEmpty()) {
int rowIndex = 1;
CellStyle bodyStyle = getBodyStyle(workbook);
CreationHelper creationHelper = workbook.getCreationHelper();
for (Map<String, Object> map : dataList) {
Row row = sheet.createRow(rowIndex++);
for (int i = 0; i < fieldList.size(); i++) {
Map<String, Object> fieldMap = fieldList.get(i);
String columnName = fieldMap.get("code").toString();
Object value = map.get(columnName);
Cell cell = row.createCell(i);
if (value instanceof Date) {
cell.setCellValue((Date) value);
bodyStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(bodyStyle);
} else {
cell.setCellValue(value != null ? value.toString() : "");
cell.setCellStyle(bodyStyle);
}
}
}
}
// 设置列宽
for (int i = 0; i < fieldList.size(); i++) {
sheet.setColumnWidth(i, 25 * 256);
}
}
/**
* 判断当前列是否为importOrgType列
*
* @param map 字段信息
* @return boolean
*/
private boolean isImportOrgTypeColumn(Map<String, Object> map) {
return "importOrgType".equals(map.get("code").toString());
}
/**
* 获取机构下拉框数据Sheet
*
* @param workbook 工作簿
* @param map 字段信息
* @return 机构下拉框数据Sheet
*/
public Sheet getOrgTypeSheet(SXSSFWorkbook workbook, Map<String, Object> map) {
String name = map.get("name").toString();
name = name.replace("*", "");
List<String> dropDownList = new ArrayList<>();
// TODO 设置dropDownList数据,这里省略......
// 创建"机构下拉框数据" Sheet并隐藏
Sheet dropDownSheet = workbook.createSheet("机构下拉框数据");
workbook.setSheetHidden(workbook.getSheetIndex(dropDownSheet), true);
// 添加下拉框选项到"网格下拉框数据" sheet
for (int i = 0; i < dropDownList.size(); i++) {
Row row = dropDownSheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue(dropDownList.get(i));
}
return dropDownSheet;
}
/**
* 设置下拉框数据验证
*
* @param sheet 工作表
* @param columnIndex 列索引
* @param startRow 开始行
* @param endRow 结束行
* @param dropDownSheet 下拉框数据Sheet
*/
public void setDropDownValidation(Sheet sheet, int columnIndex, int startRow, int endRow, Sheet dropDownSheet) {
int lastRowNum = dropDownSheet.getLastRowNum();
CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, columnIndex, 0);
// 创建下拉框数据验证对象,并设置数据来源为Sheet2的第一列数据
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
String formula = dropDownSheet.getSheetName() + "!$A$1:$A$" + (lastRowNum + 1);
DataValidationConstraint constraint = validationHelper.createFormulaListConstraint(formula);
DataValidation validation = validationHelper.createValidation(constraint, addressList);
// 将下拉框数据验证对象应用到Sheet1的指定列
sheet.addValidationData(validation);
}
}