文章介绍了使用Java编程技术，通过SXSSFWorkbook库操作Excel，包括创建工作簿、组装表头、数据填充以及设置单元格样式，以导出商品信息，如商品名称、描述、价格范围等，同时处理了复杂表头和合并单元格的需求。
第一步
//导出名
String fileName = "GGGGG.xls";
SXSSFWorkbook workbook = createWorkbook("商品信息", new SXSSFWorkbook(), getTitleListMap("商品信息"), 3, mapList);
try {
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();
// 下面几行是为了解决文件名乱码的问题
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(), StandardCharsets.UTF_8));
response.setHeader("wms-filename", fileName);
// 浏览器通过流下载
response.flushBuffer();
workbook.write(os);
os.flush();
} catch (Exception e) {
e.printStackTrace();
}第二步组装map数据
public List<Map<String, Object>> splicingExport(List<TestGoodsInVo> testGoodsInVos) {
List<Map<String, Object>> dataList = new ArrayList<>();
if (StringUtils.isNotEmpty(testGoodsInVos)) {
Integer i = 0;
for (TestGoodsInVo testGoodsInVo : testGoodsInVos) {
Map<String, Object> baseItemMap = new HashMap<>(16);
baseItemMap.put("NO", i++);
baseItemMap.put("goodsName", testGoodsInVo.getGoodsName());
baseItemMap.put("goodsDescription", testGoodsInVo.getGoodsDescription());
baseItemMap.put("goodsMinPrice", testGoodsInVo.getGoodsMinPrice());
baseItemMap.put("goodsMaxPrice", testGoodsInVo.getGoodsMaxPrice());
// 属性List
List<Map<String, Object>> attributeList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsAttributeList())) {
for (int i1 = 0; i1 < testGoodsInVo.getGoodsAttributeList().size(); i1++) {
Map<String, Object> attributeMap = new HashMap<>(16);
String goodsAttribute = String.valueOf(testGoodsInVo.getGoodsAttributeList().get(i1));
TestGoodsInVo.GoodsAttributeJson parseObject = JSONObject.parseObject(goodsAttribute, TestGoodsInVo.GoodsAttributeJson.class);
attributeMap.put("attributeName", parseObject.getAttributeName() + " ");
attributeMap.put("attributeVal", parseObject.getAttributeVal() + " ");
attributeList.add(attributeMap);
}
} else {
Map<String, Object> attributeMap = new HashMap<>(16);
attributeMap.put("attributeName", "");
attributeMap.put("attributeVal", "");
attributeList.add(attributeMap);
}
baseItemMap.put("attributeList", attributeList);
// 规格List
List<Map<String, Object>> specList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsSpecList())) {
for (int i1 = 0; i1 < testGoodsInVo.getGoodsSpecList().size(); i1++) {
Map<String, Object> specMap = new HashMap<>(16);
String goodsSpec = String.valueOf(testGoodsInVo.getGoodsSpecList().get(i1));
TestGoodsInVo.GoodsSpecJson parseObject = JSONObject.parseObject(goodsSpec, TestGoodsInVo.GoodsSpecJson.class);
specMap.put("specName", parseObject.getSpecName() + " ");
specMap.put("specType", parseObject.getSpecType() + " ");
specList.add(specMap);
}
} else {
Map<String, Object> specMap = new HashMap<>(16);
specMap.put("specName", "");
specMap.put("specType", "");
specList.add(specMap);
}
baseItemMap.put("specList", specList);
// SkuList
List<Map<String, Object>> skuList = new ArrayList<>();
if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsSkuList())) {
for (int i1 = 0; i1 < testGoodsInVo.getGoodsSkuList().size(); i1++) {
Map<String, Object> skuMap = new HashMap<>(16);
String goodsSpec = String.valueOf(testGoodsInVo.getGoodsSkuList().get(i1));
TestGoodsInVo.GoodsSkuJson parseObject = JSONObject.parseObject(goodsSpec, TestGoodsInVo.GoodsSkuJson.class);
skuMap.put("specCode", parseObject.getSpecCode() + " ");
skuMap.put("skuPrice", parseObject.getSkuPrice() + " ");
skuMap.put("skuInventory", parseObject.getSkuInventory() + " ");
skuList.add(skuMap);
}
} else {
Map<String, Object> skuMap = new HashMap<>(16);
skuMap.put("specCode", "");
skuMap.put("skuPrice", "");
skuMap.put("skuInventory", "");
skuList.add(skuMap);
}
baseItemMap.put("skuList", skuList);
dataList.add(baseItemMap);
}
}
return dataList;
}第三步组装EXCEL表头
/**
* 组装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("NO");
row1PnCode.setStartRow(1);
row1PnCode.setWidth(20);
row1PnCode.setEndRow(2);
row1PnCode.setStartColumn(0);
row1PnCode.setEndColumn(0);
firstRow.add(row1PnCode);
CellModel row1GoodsName = new CellModel();
row1GoodsName.setCellName("商品名称");
row1GoodsName.setStartRow(1);
row1GoodsName.setWidth(20);
row1GoodsName.setEndRow(2);
row1GoodsName.setStartColumn(1);
row1GoodsName.setEndColumn(1);
firstRow.add(row1GoodsName);
CellModel row1GoodsDescription = new CellModel();
row1GoodsDescription.setCellName("商品描述");
row1GoodsDescription.setStartRow(1);
row1GoodsDescription.setWidth(20);
row1GoodsDescription.setEndRow(2);
row1GoodsDescription.setStartColumn(2);
row1GoodsDescription.setEndColumn(2);
firstRow.add(row1GoodsDescription);
CellModel row1GoodsMinPrice = new CellModel();
row1GoodsMinPrice.setCellName("商品最低价");
row1GoodsMinPrice.setStartRow(1);
row1GoodsMinPrice.setEndRow(2);
row1GoodsMinPrice.setStartColumn(3);
row1GoodsMinPrice.setEndColumn(3);
firstRow.add(row1GoodsMinPrice);
CellModel row1GoodsMaxPrice = new CellModel();
row1GoodsMaxPrice.setCellName("商品最高价");
row1GoodsMaxPrice.setStartRow(1);
row1GoodsMaxPrice.setEndRow(2);
row1GoodsMaxPrice.setStartColumn(4);
row1GoodsMaxPrice.setEndColumn(4);
firstRow.add(row1GoodsMaxPrice);
CellModel rowDetail = new CellModel();
rowDetail.setCellName("商品属性");
rowDetail.setStartRow(1);
rowDetail.setWidth(20);
rowDetail.setEndRow(1);
rowDetail.setStartColumn(5);
rowDetail.setEndColumn(6);
firstRow.add(rowDetail);
CellModel row1Spec = new CellModel();
row1Spec.setCellName("规格属性");
row1Spec.setStartRow(1);
row1Spec.setWidth(20);
row1Spec.setEndRow(1);
row1Spec.setStartColumn(7);
row1Spec.setEndColumn(8);
firstRow.add(row1Spec);
CellModel row1Sku = new CellModel();
row1Sku.setCellName("规格Sku属性");
row1Sku.setStartRow(1);
row1Sku.setWidth(20);
row1Sku.setEndRow(1);
row1Sku.setStartColumn(9);
row1Sku.setEndColumn(11);
firstRow.add(row1Sku);
// 第二行
List<CellModel> secondRow = new ArrayList<>();
CellModel row2AttributeName = new CellModel();
row2AttributeName.setCellName("属性名称");
row2AttributeName.setStartRow(2);
row2AttributeName.setEndRow(2);
row2AttributeName.setWidth(20);
row2AttributeName.setStartColumn(5);
row2AttributeName.setEndColumn(5);
secondRow.add(row2AttributeName);
CellModel row2AttributeVal = new CellModel();
row2AttributeVal.setCellName("属性值");
row2AttributeVal.setStartRow(2);
row2AttributeVal.setEndRow(2);
row2AttributeVal.setWidth(20);
row2AttributeVal.setStartColumn(6);
row2AttributeVal.setEndColumn(6);
secondRow.add(row2AttributeVal);
CellModel row2SpecName = new CellModel();
row2SpecName.setCellName("规格名称");
row2SpecName.setStartRow(2);
row2SpecName.setEndRow(2);
row2SpecName.setWidth(20);
row2SpecName.setStartColumn(7);
row2SpecName.setEndColumn(7);
secondRow.add(row2SpecName);
CellModel row2SpecType = new CellModel();
row2SpecType.setCellName("规格值数组");
row2SpecType.setStartRow(2);
row2SpecType.setEndRow(2);
row2SpecType.setWidth(20);
row2SpecType.setStartColumn(8);
row2SpecType.setEndColumn(8);
secondRow.add(row2SpecType);
CellModel row2SpecCode = new CellModel();
row2SpecCode.setCellName("规格组合");
row2SpecCode.setStartRow(2);
row2SpecCode.setEndRow(2);
row2SpecCode.setWidth(20);
row2SpecCode.setStartColumn(9);
row2SpecCode.setEndColumn(9);
secondRow.add(row2SpecCode);
CellModel row2SkuPrice = new CellModel();
row2SkuPrice.setCellName("sku售价");
row2SkuPrice.setStartRow(2);
row2SkuPrice.setEndRow(2);
row2SkuPrice.setWidth(20);
row2SkuPrice.setStartColumn(10);
row2SkuPrice.setEndColumn(10);
secondRow.add(row2SkuPrice);
CellModel row2SkuInventory = new CellModel();
row2SkuInventory.setCellName("sku库存");
row2SkuInventory.setStartRow(2);
row2SkuInventory.setEndRow(2);
row2SkuInventory.setWidth(20);
row2SkuInventory.setStartColumn(11);
row2SkuInventory.setEndColumn(11);
secondRow.add(row2SkuInventory);
// 组装提头
cellTitleMap.put("0", titleRow);
// 组装第二行表头标题
cellTitleMap.put("1", firstRow);
// 组装第二行表头标题
cellTitleMap.put("2", secondRow);
return cellTitleMap;
}第四步设置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;
}第五步 填充excel数据
/**
* 填充数据
*
* @param mapsList
*/
public static void fillExcelData(List<Map<String, Object>> mapsList, Sheet sheet, CellStyle cellStyle) {
int rowIndex = 3;
String[] baseInfoStrArr = {"NO", "goodsName", "goodsDescription", "goodsMinPrice", "goodsMaxPrice"};
String[] attributeStrArr = {"attributeName", "attributeVal"};
String[] goodsSpecStrArr = {"specName", "specType"};
String[] goodsSkuStrArr = {"specCode", "skuPrice", "skuInventory"};
List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
for (Map<String, Object> baseMap : mapsList) {
List<Map<String,Object>> attributeJson = (List<Map<String, Object>>) baseMap.get("attributeList");
List<Map<String,Object>> goodsSpecJson = (List<Map<String, Object>>) baseMap.get("specList");
List<Map<String,Object>> goodsSkuJson = (List<Map<String, Object>>) baseMap.get("skuList");
Integer detailSize = 0;
if (detailSize < attributeJson.size()){
detailSize = attributeJson.size();
}
if (detailSize < goodsSpecJson.size()){
detailSize = goodsSpecJson.size();
}
if (detailSize < goodsSkuJson.size()){
detailSize = goodsSkuJson.size();
}
int startIndex = rowIndex;
// 填充明细数据
for (int i = 0; i < detailSize; i++) {
// 建立明细行
Row detailRow = sheet.createRow(rowIndex);
int cellIndex = 0;
// 基础数据
for (String value : baseInfoStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, baseMap.get(value), cellStyle);
cellIndex++;
}
// 属性数据
if (attributeJson.size()>i && attributeJson.get(i) != null){
for (String value : attributeStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, attributeJson.get(i).get(value), cellStyle);
cellIndex++;
}
}else {
for (String value : attributeStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, null, cellStyle);
cellIndex++;
}
}
// 规格数据
if (goodsSpecJson.size()>i && goodsSpecJson.get(i) != null){
for (String value : goodsSpecStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, goodsSpecJson.get(i).get(value), cellStyle);
cellIndex++;
}
}else {
for (String value : goodsSpecStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, null, cellStyle);
cellIndex++;
}
}
// sku数据
if (goodsSkuJson.size()>i && goodsSkuJson.get(i) != null){
for (String value : goodsSkuStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, goodsSkuJson.get(i).get(value), cellStyle);
cellIndex++;
}
}else {
for (String value : goodsSkuStrArr) {
Cell cell = detailRow.createCell(cellIndex);
setExcelValue(cell, null, 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)));
}
}
}附：表头表
@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;
}
效果图
扫一扫
4489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
