这个是是在别人的框架上面改造的,做的复杂组合表格的导出
@RequiresPermissions("business:type:export")
@PostMapping("/export")
@ResponseBody
public AjaxResult export(CardType cardType)
{
// cardType.s
// List<CardType> list = cardTypeService.selectCardTypeList(cardType);
List<SysDictData> listDict = sysDictDataService.selectDictDataByType("card_type");
Map<String, String> dictMap = new HashMap<String, String>();
for(SysDictData sysDictData : listDict) {
dictMap.put(sysDictData.getDictValue(),sysDictData.getDictLabel());
}
List<CardType> list = cardTypeService.selectCardTypeAndTblOrgLinkList(cardType);
for(CardType cardTypeInfo : list) {
cardTypeInfo.setCrdType(dictMap.get(cardTypeInfo.getCrdType()));
}
//创建一个工作簿
HSSFWorkbook wb = new HSSFWorkbook();
String sheetName = "type";
//创建一个工作表
HSSFSheet sheet = wb.createSheet(sheetName);
//创建一行,行的索引是从0开始
HSSFRow row = null;
//创建内容体的单元格的样式
HSSFCellStyle style_content = wb.createCellStyle();
style_content.setBorderBottom(BorderStyle.THIN);//下边框
style_content.setBorderTop(BorderStyle.THIN);//上边框
style_content.setBorderLeft(BorderStyle.THIN);//左边框
style_content.setBorderRight(BorderStyle.THIN);//右边框
//对齐方式:水平居中
style_content.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
style_content.setVerticalAlignment(VerticalAlignment.CENTER);
//创建内容样式的字体
HSSFFont font_content = wb.createFont();
//设置字体名称,相当选中哪种字符
font_content.setFontName("宋体");
//设置字体的大小
font_content.setFontHeightInPoints((short)11);
style_content.setFont(font_content);
//表头标题样式
HSSFCellStyle style_title = wb.createCellStyle();
// CellStyle style_title = wb.createCellStyle();
style_title.setAlignment(HorizontalAlignment.CENTER);
style_title.setVerticalAlignment(VerticalAlignment.CENTER);
style_title.setBorderBottom(BorderStyle.THIN);//下边框
style_title.setBorderTop(BorderStyle.THIN);//上边框
style_title.setBorderLeft(BorderStyle.THIN);//左边框
style_title.setBorderRight(BorderStyle.THIN);//右边框
//设置表头背景颜色
//设置填充
style_title.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置填充颜色
style_title.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
//设置表头字体样式
HSSFFont style_font = wb.createFont();
style_font.setFontName("黑体");
style_font.setFontHeightInPoints((short)10);
//加粗
style_font.setBold(true);
style_title.setFont(style_font);
for(int i = 0; i < 2; i++){
row = sheet.createRow(i);
for(int j = 0; j < 17; j++){
//设置单元格的样式
sheet.getRow(i).createCell(j).setCellStyle(style_title);
}
//row.setHeight((short)500);
}
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 3, 9));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 13));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 14, 16));
for(int i = 2; i < list.size() + 2; i++){
row = sheet.createRow(i);
for(int j = 0; j < 17; j++){
//设置单元格的样式
row.createCell(j).setCellStyle(style_content);
}
//row.setHeight((short)500);
}
//给Excle设置单元格值 getRow(0) : 第一行 getCell(0) 第一列
sheet.getRow(0).getCell(0).setCellValue("序号");
sheet.getRow(0).getCell(1).setCellValue("机构名称");
sheet.getRow(0).getCell(2).setCellValue("卡种类");
sheet.getRow(0).getCell(3).setCellValue("当前");
sheet.getRow(0).getCell(10).setCellValue("本期");
sheet.getRow(0).getCell(14).setCellValue("序号");
sheet.getRow(1).getCell(3).setCellValue("主卡数");
sheet.getRow(1).getCell(4).setCellValue("附卡数");
sheet.getRow(1).getCell(5).setCellValue("活卡数");
sheet.getRow(1).getCell(6).setCellValue("客户数");
sheet.getRow(1).getCell(7).setCellValue("睡眠数(张)");
sheet.getRow(1).getCell(8).setCellValue("睡眠数(户数)");
sheet.getRow(1).getCell(9).setCellValue("存款余额");
sheet.getRow(1).getCell(10).setCellValue("发卡数(张)");
sheet.getRow(1).getCell(11).setCellValue("正常换卡数(张)");
sheet.getRow(1).getCell(12).setCellValue("挂失补卡数(张)");
sheet.getRow(1).getCell(13).setCellValue("销卡数(张)");
sheet.getRow(1).getCell(14).setCellValue("发卡数(张)");
sheet.getRow(1).getCell(15).setCellValue("销卡数(张)");
sheet.getRow(1).getCell(16).setCellValue("激活数(张)");
for(int i = 0; i < list.size(); i++){
sheet.getRow(i+2).getCell(0).setCellValue(list.get(i).getCardtypeId());
sheet.getRow(i+2).getCell(1).setCellValue(list.get(i).getOpnCrdOrgNo());
sheet.getRow(i+2).getCell(2).setCellValue(list.get(i).getCrdType());
sheet.getRow(i+2).getCell(3).setCellValue(list.get(i).getMainNum());
sheet.getRow(i+2).getCell(4).setCellValue(list.get(i).getSubNum());
sheet.getRow(i+2).getCell(5).setCellValue(list.get(i).getSlpNum());
sheet.getRow(i+2).getCell(6).setCellValue(list.get(i).getSlpNum1());
sheet.getRow(i+2).getCell(7).setCellValue(list.get(i).getActNum());
sheet.getRow(i+2).getCell(8).setCellValue(list.get(i).getAcctNum());
sheet.getRow(i+2).getCell(9).setCellValue(list.get(i).getDpsBal().toString());
sheet.getRow(i+2).getCell(10).setCellValue(list.get(i).getOpnCrdNum());
sheet.getRow(i+2).getCell(11).setCellValue(list.get(i).getCheCarNum());
sheet.getRow(i+2).getCell(12).setCellValue(list.get(i).getCmsLosNum());
sheet.getRow(i+2).getCell(13).setCellValue(list.get(i).getCanCrdNum());
sheet.getRow(i+2).getCell(14).setCellValue(list.get(i).getLjOpnCrdNum());
sheet.getRow(i+2).getCell(15).setCellValue(list.get(i).getLjAtvCrdNum());
sheet.getRow(i+2).getCell(16).setCellValue(list.get(i).getLjCanCrdNum());
}
sheet.getRow(0).setHeight((short)500);
sheet.getRow(1).setHeight((short)500);
//设置列宽
for(int i = 0; i < 17; i++){
sheet.setColumnWidth(i, 6000);
}
OutputStream out = null;
try {
String filename = encodingFilename(sheetName);
out = new FileOutputStream(getAbsoluteFile(filename));
wb.write(out);
return AjaxResult.success(filename);
// wb.write(new FileOutputStream(getAbsoluteFile(encodingFilename("type"))));
// return AjaxResult.success("type");
}
catch (Exception e)
{
// log.error("导出Excel异常{}", e.getMessage());
throw new BusinessException("导出Excel失败,请联系网站管理员!");
}
finally
{
if (wb != null)
{
try
{
wb.close();
}
catch (IOException e1)
{
e1.printStackTrace();
}
}
if (out != null)
{
try
{
out.close();
}
catch (IOException e1)
{
e1.printStackTrace();
}
}
}
}
/**
* 编码文件名
*/
public String encodingFilename(String filename)
{
filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
return filename;
}
/**
* 获取下载路径
*
* @param filename 文件名称
*/
public String getAbsoluteFile(String filename)
{
String downloadPath = Global.getDownloadPath() + filename;
File desc = new File(downloadPath);
if (!desc.getParentFile().exists())
{
desc.getParentFile().mkdirs();
}
return downloadPath;
}