事例:按照下图所属内容,根据商品类型进行合并单元格,EsayPoi可能无法满足其需求,因此考虑了poi
前端展示如上图所示
导出效果图
具体代码如下:
//poi导出excil
//创建一个Workbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//sheet名称
String sheetName = "展示数据记录";
//标题
String title = "展示数据记录";
//表头
String[] row_first = {"名称", "商品类型", "单位", "实时", "账面", " 差额"};
//设置sheet名称 标题 表头
final HSSFSheet sheet = PoiUtil.createSheetTitleAndFirstRow(wb, sheetName, title, row_first, 5, inventoryStatisticsVOS);
//合并单元格
if (num != 1 && cellRange.get() == 1) {
sheet.addMergedRegion(new CellRangeAddress(2, 2 + num - 1, 4, 4));
sheet.addMergedRegion(new CellRangeAddress(2, 2 + num - 1, 5, 5));
}
//导出
String fileName = "展示数据记录.xls";
PoiUtil.exportExcel(fileName, httpServletResponse, wb);
return ResultVO.success("导出记录成功", null);
PoiUtil中代码如下
/**
*@Description: 设置sheet名称 标题 表头 num 代表表头列数
*/
public static HSSFSheet createSheetTitleAndFirstRow(HSSFWorkbook wb,String sheetName, String title, String[] row_first, Integer num, List<InventoryStatisticsVO> inventoryStatisticsVOS) {
//poi导出excil
//创建一个Workbook,对应一个Excel文件
// HSSFWorkbook wb = new HSSFWorkbook();
//在Workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(20);
//设置样式以及字体样式
HSSFCellStyle titleStyle = PoiUtil.createTitleCellStyle(wb);
HSSFCellStyle headerStyle = PoiUtil.createHeadCellStyle(wb);
HSSFCellStyle contentStyle = PoiUtil.createContentCellStyle(wb);
//创建标题 ,合并标题单元格
// 行号
int rowNum = 0;
// 创建第一页的第一行,索引从0开始
HSSFRow row0 = sheet.createRow(rowNum++);
row0.setHeight((short) 800);// 设置行高
// String title = "展示数据记录";
HSSFCell c00 = row0.createCell(0);
c00.setCellValue(title);
c00.setCellStyle(titleStyle);
// 合并单元格,参数依次为起始行,结束行,起始列,结束列 (索引0开始)
//标题合并单元格操作,5为总列数
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
// 第二行
HSSFRow row1 = sheet.createRow(rowNum++);
row1.setHeight((short) 500);
for (int i = 0; i < row_first.length; i++) {
HSSFCell tempCell = row1.createCell(i);
tempCell.setCellStyle(headerStyle);
tempCell.setCellValue(row_first[i]);
}
//循环每一行数据
for (InventoryStatisticsVO inventoryStatisticsVO : inventoryStatisticsVOS) {
HSSFRow tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = 0; j < 6; j++) {
HSSFCell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(contentStyle);
String tempValue;
if (j == 0) {
// 名称
tempValue = inventoryStatisticsVO.getStoreHouse();
} else if (j == 1) {
// 类型
tempValue = inventoryStatisticsVO.getGoodsType();
} else if (j == 2) {
// 单位
tempValue = inventoryStatisticsVO.getUnit();
} else if (j == 3) {
// 实时
tempValue = inventoryStatisticsVO.getRealTimeInventory().toString();
} else if (j == 4) {
// 账面
tempValue = inventoryStatisticsVO.getBookInventory().toString();
} else {
// 差额
tempValue = inventoryStatisticsVO.getInventoryBalance().toString();
}
tempCell.setCellValue(tempValue);
}
}
return sheet;
}