1.POM依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>2.自定义单元格实体类
@Data
public class CellModel implements Serializable {
private static final long serialVersionUID = 3349253091834024425L;
/** 表头列名称 */
private String cellName;
/** 起始行 */
private Integer startRow;
/** 结束行 */
private Integer endRow;
/** 起始列 */
private Integer startColumn;
/** 结束列 */
private Integer endColumn;
/** 设置单元格宽度 */
private Integer width;
}3.封装表头
/**
* 组装EXCEl表头，一共三行数据
*
* @param titleStr
* @return
*/
private Map<String, List<CellModel>> getTitleListMap(String titleStr) {
Map<String, List<CellModel>> cellTitleMap = new HashMap<>(16);
// 提头
List<CellModel> titleRow = new ArrayList<>();
CellModel title = new CellModel();
title.setCellName(titleStr);
title.setStartRow(0);
title.setEndRow(0);
title.setStartColumn(0);
title.setEndColumn(11);
titleRow.add(title);
// 创建第一行
List<CellModel> firstRow = new ArrayList<>();
CellModel row1PnCode = new CellModel();
row1PnCode.setCellName("品号");
row1PnCode.setStartRow(1);
row1PnCode.setWidth(20);
row1PnCode.setEndRow(2);
row1PnCode.setStartColumn(0);
row1PnCode.setEndColumn(0);
firstRow.add(row1PnCode);
CellModel row1PnName = new CellModel();
row1PnName.setCellName("品名");
row1PnName.setStartRow(1);
row1PnName.setWidth(20);
row1PnName.setEndRow(2);
row1PnName.setStartColumn(1);
row1PnName.setEndColumn(1);
firstRow.add(row1PnName);
CellModel row1PnModel = new CellModel();
row1PnModel.setCellName("规格");
row1PnModel.setStartRow(1);
row1PnModel.setWidth(20);
row1PnModel.setEndRow(2);
row1PnModel.setStartColumn(2);
row1PnModel.setEndColumn(2);
firstRow.add(row1PnModel);
CellModel row1Good = new CellModel();
row1Good.setCellName("良品总数");
row1Good.setStartRow(1);
row1Good.setEndRow(2);
row1Good.setStartColumn(3);
row1Good.setEndColumn(3);
firstRow.add(row1Good);
CellModel row1Bad = new CellModel();
row1Bad.setCellName("不良总数");
row1Bad.setStartRow(1);
row1Bad.setEndRow(2);
row1Bad.setStartColumn(4);
row1Bad.setEndColumn(4);
firstRow.add(row1Bad);
CellModel row1Line = new CellModel();
row1Line.setCellName("线边总数");
row1Line.setStartRow(1);
row1Line.setEndRow(2);
row1Line.setStartColumn(5);
row1Line.setEndColumn(5);
firstRow.add(row1Line);
CellModel rowDetail = new CellModel();
rowDetail.setCellName("库存明细数据");
rowDetail.setStartRow(1);
rowDetail.setWidth(20);
rowDetail.setEndRow(1);
rowDetail.setStartColumn(6);
rowDetail.setEndColumn(11);
firstRow.add(rowDetail);
// 第二行
List<CellModel> secondRow = new ArrayList<>();
CellModel row2HouseNo = new CellModel();
row2HouseNo.setCellName("库位编号");
row2HouseNo.setStartRow(2);
row2HouseNo.setEndRow(2);
row2HouseNo.setWidth(20);
row2HouseNo.setStartColumn(6);
row2HouseNo.setEndColumn(6);
secondRow.add(row2HouseNo);
CellModel row2ProduceNo = new CellModel();
row2ProduceNo.setCellName("批次");
row2ProduceNo.setStartRow(2);
row2ProduceNo.setEndRow(2);
row2ProduceNo.setWidth(20);
row2ProduceNo.setStartColumn(7);
row2ProduceNo.setEndColumn(7);
secondRow.add(row2ProduceNo);
CellModel row2InTime = new CellModel();
row2InTime.setCellName("入库时间");
row2InTime.setStartRow(2);
row2InTime.setEndRow(2);
row2InTime.setWidth(20);
row2InTime.setStartColumn(8);
row2InTime.setEndColumn(8);
secondRow.add(row2InTime);
CellModel row2Good = new CellModel();
row2Good.setCellName("良品");
row2Good.setStartRow(2);
row2Good.setEndRow(2);
row2Good.setWidth(10);
row2Good.setStartColumn(9);
row2Good.setEndColumn(9);
secondRow.add(row2Good);
CellModel row2Bad = new CellModel();
row2Bad.setCellName("不良");
row2Bad.setStartRow(2);
row2Bad.setEndRow(2);
row2Bad.setWidth(10);
row2Bad.setStartColumn(10);
row2Bad.setEndColumn(10);
secondRow.add(row2Bad);
CellModel row2Line = new CellModel();
row2Line.setCellName("线边");
row2Line.setStartRow(2);
row2Line.setEndRow(2);
row2Line.setWidth(10);
row2Line.setStartColumn(11);
row2Line.setEndColumn(11);
secondRow.add(row2Line);
// 组装提头
cellTitleMap.put("0", titleRow);
// 组装第二行表头标题
cellTitleMap.put("1", firstRow);
// 组装第二行表头标题
cellTitleMap.put("2", secondRow);
return cellTitleMap;
}4.封装数据（数据需根据业务需求，自行定义）
/**
* 查询数据
*/
private List<Map<String, Object>> getDataList(PnStockBoardVo pnStockBoard) {
List<Map<String, Object>> dataList = new ArrayList<>();
// 通过检索条件查询数据
List<PnStockBoardVo> pnList = wmsStockInfoMapper.selectWmsStockPnBoardList(pnStockBoard);
if (StringUtils.isNotEmpty(pnList)) {
Map<String, Object> params = pnStockBoard.getParams();
if (params == null) {
params = new HashMap<>(16);
}
for (PnStockBoardVo baseItem : pnList) {
Map<String, Object> baseItemMap = new HashMap<>(16);
baseItemMap.put("pnCode", baseItem.getPnCode());
baseItemMap.put("pnName", baseItem.getPnName());
baseItemMap.put("pnModel", baseItem.getPnModel());
baseItemMap.put("goodNum", NumberUtils.clearNoUseZero(baseItem.getGoodNum()) + "");
baseItemMap.put("badNum", NumberUtils.clearNoUseZero(baseItem.getBadNum()) + "");
baseItemMap.put("lineNum", NumberUtils.clearNoUseZero(baseItem.getLineNum()) + "");
// 明细数据
List<Map<String, String>> dataDetailList = new ArrayList<>();
// 库存明细
List<PnStockDetailBoardVo> detailList = wmsStockInfoMapper.selectWmsStockPnBoardDetailListByPnId(baseItem.getPnId(), params);
if (StringUtils.isNotEmpty(detailList)) {
for (PnStockDetailBoardVo baseDetail : detailList) {
Map<String, String> detailItemMap = new HashMap<>(16);
detailItemMap.put("houseUniqueNo", baseDetail.getHouseUniqueNo());
detailItemMap.put("produceNo", baseDetail.getProduceNo());
detailItemMap.put("inTimeStr", baseDetail.getInTimeStr());
detailItemMap.put("goodNum", NumberUtils.clearNoUseZero(baseDetail.getGoodNum()) + "");
detailItemMap.put("badNum", NumberUtils.clearNoUseZero(baseDetail.getBadNum()) + "");
detailItemMap.put("lineNum", NumberUtils.clearNoUseZero(baseDetail.getLineNum()) + "");
dataDetailList.add(detailItemMap);
}
} else {
// 未查询到，也要放入空数据，确保excel每一行存在记录
Map<String, String> detailItemMap = new HashMap<>(16);
detailItemMap.put("houseUniqueNo", "");
detailItemMap.put("produceNo", "");
detailItemMap.put("inTimeStr", "");
detailItemMap.put("goodNum", "");
detailItemMap.put("badNum", "");
detailItemMap.put("lineNum", "");
dataDetailList.add(detailItemMap);
}
baseItemMap.put("dataDetailList", dataDetailList);
dataList.add(baseItemMap);
}
}
return dataList;
}5.创建EXCEL表格并填充数据
/**
* 生成表格（用于生成复杂表头）
*
* @param sheetName sheet名称
* @param wb 表对象
* @param cellTitleMap 表头数据
* @param cellRowNum 表头总占用行数
* @param exportData 行数据
* @return SXSSFWorkbook 数据表对象
*/
@SuppressWarnings({"rawtypes", "unchecked"})
public static SXSSFWorkbook createWorkbook(String sheetName, SXSSFWorkbook wb,
Map<String, List<CellModel>> cellTitleMap,
Integer cellRowNum, List<Map<String, Object>> exportData) {
// 设置表格名称
Sheet sheet = wb.createSheet(sheetName);
// 定义title列cell样式
CellStyle cellTitleStyle = wb.createCellStyle();
cellTitleStyle.setAlignment(HorizontalAlignment.CENTER); // 文字居中
cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格内容垂直对齐
cellTitleStyle.setWrapText(true); // 设置自动换行
cellTitleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); // 背景色
cellTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
cellTitleStyle.setBorderBottom(BorderStyle.THIN);
cellTitleStyle.setBorderLeft(BorderStyle.THIN);
cellTitleStyle.setBorderRight(BorderStyle.THIN);
cellTitleStyle.setBorderTop(BorderStyle.THIN);
// 定义title列cell字体
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
// titleFont.setColor(IndexedColors.WHITE.getIndex()); //字体颜色
titleFont.setFontHeightInPoints((short) 10);
titleFont.setBold(true);
cellTitleStyle.setFont(titleFont);
List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
for (int t = 0; t < cellRowNum; t++) {
Row row = sheet.createRow(t);
if (t == 0) {
row.setHeight((short) (2 * 256));
}
List<CellModel> cellNameList = cellTitleMap.get(String.valueOf(t));
for (CellModel cellModel : cellNameList) {
// 遍历插入表头
if (cellModel.getStartColumn() != null) {
Cell cell = row.createCell(cellModel.getStartColumn());
cell.setCellValue(cellModel.getCellName());
cell.setCellStyle(cellTitleStyle);
}
Integer startRow = cellModel.getStartRow();
Integer endRow = cellModel.getEndRow();
Integer startColumn = cellModel.getStartColumn();
Integer endColumn = cellModel.getEndColumn();
// 满足合并单元格条件，加入到合并集合
if (!startRow.equals(endRow) || !startColumn.equals(endColumn)) {
List<Integer> mergeParam = new ArrayList<>(4);
mergeParam.add(startRow);
mergeParam.add(endRow);
mergeParam.add(startColumn);
mergeParam.add(endColumn);
mergeParams.add(mergeParam);
}
// 根据标题设置单元格宽度
if (cellModel.getWidth() != null) {
sheet.setColumnWidth(startColumn, cellModel.getWidth() * 256);
} else {
sheet.setColumnWidth(startColumn, cellModel.getCellName().getBytes().length * 256);
}
}
}
// 合并单元格
if (StringUtils.isNotEmpty(mergeParams)) {
for (List<Integer> list : mergeParams) {
// 合并单元格之前设置单元格的样式，避免合并后部分失效
Integer startRow = list.get(0);
Integer endRow = list.get(1);
Integer startCell = list.get(2);
Integer endCell = list.get(3);
setStyleBeforeMerging(sheet, startRow, endRow, startCell, endCell, cellTitleStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, endRow, startCell, endCell));
}
}
// 明细数据样式
CellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setAlignment(HorizontalAlignment.CENTER); // 设置单元格内容水平对齐
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格内容垂直对齐
bodyStyle.setWrapText(true); // 设置自动换行
bodyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
bodyStyle.setBorderBottom(BorderStyle.THIN);
bodyStyle.setBorderLeft(BorderStyle.THIN);
bodyStyle.setBorderRight(BorderStyle.THIN);
bodyStyle.setBorderTop(BorderStyle.THIN);
// 明细数据样式字体样式
Font bodyFont = wb.createFont();
bodyFont.setFontName("Arial");
bodyFont.setFontHeightInPoints((short) 10);
bodyStyle.setFont(bodyFont);
fillExcelData(exportData, sheet, bodyStyle);
return wb;
}
/**
* 填充数据
*
* @param mapsList
*/
public static void fillExcelData(List<Map<String, Object>> mapsList, Sheet sheet, CellStyle cellStyle) {
int rowIndex = 3;
String[] baseInfoStrArr = {"pnCode", "pnName", "pnModel", "goodNum", "badNum", "lineNum"};
String[] detailInfoStrArr = {"houseUniqueNo", "produceNo", "inTimeStr", "goodNum", "badNum", "lineNum"};
String dataListKey = "dataDetailList";
List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
for (Map<String, Object> baseMap : mapsList) {
List<Map<String, Object>> detailList = (List<Map<String, Object>>) baseMap.get(dataListKey);
int detailSize = detailList.size(); // 明细长度
int startIndex = rowIndex;
// 填充明细数据
for (Map<String, Object> detailMap : detailList) {
// 建立明细行
Row detailRow = sheet.createRow(rowIndex);
int cellIndex = 0;
// 基础数据
for (String value : baseInfoStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, baseMap.get(value), cellStyle);
cellIndex++;
}
// 明细数据
for (String s : detailInfoStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, detailMap.get(s), cellStyle);
cellIndex++;
}
rowIndex++;
}
if (detailSize > 1) { // 需要合并单元格加入集合
int baseLength = baseInfoStrArr.length;
int bi = 0;
for (; bi < baseLength; bi++) {
List<Integer> mergeParam = new ArrayList<>(4);
mergeParam.add(startIndex);
mergeParam.add(startIndex + detailSize - 1);
mergeParam.add(bi);
mergeParam.add(bi);
mergeParams.add(mergeParam);
}
}
}
if (StringUtils.isNotEmpty(mergeParams)) {
// 合并单元格
for (List<Integer> list : mergeParams) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(list.get(0), list.get(1), list.get(2), list.get(3)));
}
}
}
/**
* 设置单元格值
*
* @param cell 单元格类
* @param value 传入的值
*/
public static void setExcelValue(Cell cell, Object value, CellStyle cellStyle) {
// 写数据
if (value == null) {
cell.setCellValue("");
} else {
if (value instanceof Integer || value instanceof Long) {
cell.setCellValue(Long.parseLong(value.toString()));
} else if (value instanceof BigDecimal) {
cell.setCellValue(((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue());
} else {
cell.setCellValue(value.toString());
}
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description: 合并单元格之前设置单元格的样式，避免合并后部分失效
* </p>
*
* @param sheet excel的sheet
* @param firstRow 开始行下标
* @param lastRow 结束行下标
* @param firstCol 开始列下标
* @param lastCol 结束列下标
* @param style 样式
*/
public static void setStyleBeforeMerging(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, CellStyle style) {
if (lastRow >= firstRow) {
//设置合并单元格之前，先设置一遍样式
for (int l = firstRow; l <= lastRow; l++) {
Row row = CellUtil.getRow(l, sheet);
for (int k = firstCol; k <= lastCol; k++) {
Cell cell = CellUtil.getCell(row, k);
cell.setCellStyle(style);
}
}
}
}6.导出预览
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
