目录
1、根据excel模板导出excel，sheet页数固定，标题固定，需要生成折线图、树状图等【根据隐藏sheet页存放数据实现】
2、前端js生成excel文件传会后端修改样式【需同时参考另一篇博客：js导出excel】
2、异常Your InputStream was neither an OLE2 stream, nor an OOXML stream
需求：
1、根据excel模板导出excel，sheet页数固定，标题固定，需要生成折线图、树状图等【根据隐藏sheet页存放数据实现】
2、前端js生成excel文件传会后端修改样式【需同时参考另一篇博客：js导出excel】
相关资源：
excel模板下载：https://pan.baidu.com/s/1y_YBMUdY8LEX811CJKhTog
导出excel样例下载：https://pan.baidu.com/s/1H2pkoq8SHG3TNAEXkjcfYA
ExcelUtils代码下载【poi版本3.11】：https://pan.baidu.com/s/19dTxNbRD6NIDsl5OEazTsg
调用导出代码下载：https://pan.baidu.com/s/1yIoyoSO71SOHfMCBe1ieRA
相关博客：
折线图系列值不能添加公式，应该使用定义名称的方式解决：http://www.exceloffice.net/archives/2689
excel动态获取数据源方法：http://www.360doc.com/content/18/0123/12/28740943_724398529.shtml
POI工厂类型获取WorkBook对象：https://www.jianshu.com/p/d64f7231411a?utm_source=oschina-app
【WorkbookFactory.create(inputStream)】
导出指定格式的excel：https://blog.csdn.net/sinat_33151213/article/details/78405187
指定resource文件：https://www.cnblogs.com/whalesea/p/11677657.html
需要注意的
1、无法加载到资源：
/**
* 根据模板文件创建临时文件
* @param fileName 模板文件名
* @return 临时文件
*/
public static File getFileFromTemplate(String fileName) throws IOException {
// 获得模板文件
ClassPathResource classPathResource = new ClassPathResource(fileName);
InputStream inputStream = classPathResource.getInputStream();
// 根据模板文件创建临时文件
File file = File.createTempFile(fileName.substring(0, fileName.indexOf('.')), fileName.substring(fileName.indexOf('.')));
FileUtils.copyInputStreamToFile(inputStream, file);
IOUtils.closeQuietly(inputStream);
return file;
}2、异常Your InputStream was neither an OLE2 stream, nor an OOXML stream
指定maven不编译xls、xlsx文件【Your InputStream was neither an OLE2 stream, nor an OOXML stream】：https://blog.csdn.net/mlsama/article/details/89709966
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
<nonFilteredFileExtension>xls</nonFilteredFileExtension>
<nonFilteredFileExtension>woff2</nonFilteredFileExtension>
<nonFilteredFileExtension>woff</nonFilteredFileExtension>
<nonFilteredFileExtension>ttf</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
</plugins>
</build>只有resources文件夹下面的资源会默认被编译，xls、xlsx被编译，并且该项配置会导致webapp下面的文件被编译
代码相关
需求1：根据模板导出
1、service调用层代码：
/**
*根据模板导出excel
*/
@Override
public void exportAll(String fileName, InvestmentAnalysisDto dto, HttpServletRequest request, HttpServletResponse response) throws Exception {
ExcelUtils excelUtils = new ExcelUtils();
// 数据集合【每一个ExcelUtils.ParaMap 对应一个Sheet页】
// ExcelUtils.ParaMap的构造函数 【数据开始填充的行号、数据开始填充的列号、sheet名字、数据集List、数据类型class、 ... 加粗行的get方法、】
List<ExcelUtils.ParaMap> list = new ArrayList<>();
List<InvestmentAnalysis> investManage = queryInvestmentManageInfo(dto);// 投资分析-投资基本管理
List<InvestmentAnalysis> scaleOverview = queryScaleOverviewInfo(dto); // 投资分析-规模概览
Map<String, List<InvestmentAnalysis>> returnInvestmentManagement = queryReturnInvestmentManagement(dto);// 投资分析-投资管理收益率
Map<String, List<InvestmentAnalysis>> distributionOfYield = queryDistributionOfYield(dto); // 投资分析-收益率分布情况
Map<String, List<InvestmentAnalysis>> tenAccountsBeforeAndAfter = queryTenAccountsBeforeAndAfter(dto); // 投资分析-前后十账户
List<InvestmentAnalysis> keyCombination = queryKeyCombination(dto); // 投资分析-重点组合
List<InvestmentAnalysis> crisisCombination = queryCrisisCombination(dto); // 投资分析-危机组合
List<InvestmentAnalysis> accountDetails = queryAccountDetails(dto); // 投资分析-账户明细
List<InvestmentAnalysis> competitiveAccount = queryCompetitiveAccount(dto); // 投资分析-竞争账户
// 投资基本管理
ExcelUtils.ParaMap paraMap1 = excelUtils.new ParaMap(1,1, "投资管理基本情况", investManage, InvestmentAnalysis.class, "getType", "合计");
list.add(paraMap1);
// 规模概览
List<InvestmentAnalysis> enterAnn = scaleOverview.stream().filter(obj -> obj.getType().equals("企业年金")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap2 = excelUtils.new ParaMap(1,0, "规模概览-数据", enterAnn, InvestmentAnalysis.class);
list.add(paraMap2); // 企业年金
List<InvestmentAnalysis> ocupaAnn = scaleOverview.stream().filter(obj -> obj.getType().equals("职业年金")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap3 = excelUtils.new ParaMap(1,3, "规模概览-数据", ocupaAnn, InvestmentAnalysis.class);
list.add(paraMap3); // 职业年金
List<InvestmentAnalysis> oldPension = scaleOverview.stream().filter(obj -> obj.getType().equals("养老金产品")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap4 = excelUtils.new ParaMap(1,5, "规模概览-数据", oldPension, InvestmentAnalysis.class);
list.add(paraMap4); // 养老金产品
// 投资管理收益率-年金投资收益率
List<InvestmentAnalysis> annuityInvestment = returnInvestmentManagement.get("annuityInvestment");
List<InvestmentAnalysis> annInvest = annuityInvestment.stream().filter(obj -> obj.getFundGroup1().equals("企业年金-固定")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap5 = excelUtils.new ParaMap(1,0, "年金投资收益率-数据", annInvest, InvestmentAnalysis.class);
list.add(paraMap5); // 年金投资收益率-企业年金-固定
List<InvestmentAnalysis> ann2Invest = annuityInvestment.stream().filter(obj -> obj.getFundGroup1().equals("企业年金-含权")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap6 = excelUtils.new ParaMap(1,3, "年金投资收益率-数据", ann2Invest, InvestmentAnalysis.class);
list.add(paraMap6); // 年金投资收益率-企业年金-含权
List<InvestmentAnalysis> occInvest = annuityInvestment.stream().filter(obj -> obj.getFundGroup1().equals("职业年金-含权")).collect(Collectors.toList());
ExcelUtils.ParaMap paraMap7 = excelUtils.new ParaMap(1,5, "年金投资收益率-数据", occInvest, InvestmentAnalysis.class);
list.add(paraMap7); // 年金投资收益率-职业年金-含权
// 投资管理收益率
List<InvestmentAnalysis> enterAnnuityOne = returnInvestmentManagement.get("enterAnnuityOne");
ExcelUtils.ParaMap paraMap8 = excelUtils.new ParaMap(2,2, "投资管理收益率", enterAnnuityOne, InvestmentAnalysis.class, "getFundType", "合计");
list.add(paraMap8); // 投资管理收益率-企业年金（一级组合类型）
List<InvestmentAnalysis> enterAnnuityTwo = returnInvestmentManagement.get("enterAnnuityTwo");
ExcelUtils.ParaMap paraMap9 = excelUtils.new ParaMap(13,2, "投资管理收益率", enterAnnuityTwo, InvestmentAnalysis.class, "getFundGroup2", "合计");
list.add(paraMap9); // 投资管理收益率-企业年金（二级组合类型）
List<InvestmentAnalysis> OccupAnnuity = returnInvestmentManagement.get("OccupAnnuity");
ExcelUtils.ParaMap paraMap10 = excelUtils.new ParaMap(24,0, "投资管理收益率", OccupAnnuity, InvestmentAnalysis.class);
list.add(paraMap10); // 投资管理收益率-职业年金
// 收益率分布情况
List<InvestmentAnalysis> fixedIncome = distributionOfYield.get("fixedIncome");
ExcelUtils.ParaMap paraMap11 = excelUtils.new ParaMap(2,0, "收益率分布情况", fixedIncome, InvestmentAnalysis.class, "getRankTypemx", "合计");
list.add(paraMap11); // 收益率分布情况-固定收益率
List<InvestmentAnalysis> rightsAndProfit = distributionOfYield.get("rightsAndProfit");
ExcelUtils.ParaMap paraMap12 = excelUtils.new ParaMap(13,0, "收益率分布情况", rightsAndProfit, InvestmentAnalysis.class, "getRankTypemx", "合计");
list.add(paraMap12); // 收益率分布情况-含权益类
// 前后十账户
List<InvestmentAnalysis> fixedIncomeBefore = tenAccountsBeforeAndAfter.get("fixedIncomeBefore");
ExcelUtils.ParaMap paraMap13 = excelUtils.new ParaMap(2,0, "前后十账户", fixedIncomeBefore, InvestmentAnalysis.class);
list.add(paraMap13); // 前后十账户-固收前十
List<InvestmentAnalysis> fixedIncomeAfter = tenAccountsBeforeAndAfter.get("fixedIncomeAfter");
ExcelUtils.ParaMap paraMap14 = excelUtils.new ParaMap(15,0, "前后十账户", fixedIncomeAfter, InvestmentAnalysis.class);
list.add(paraMap14); // 前后十账户-固收后十
List<InvestmentAnalysis> rightBefore = tenAccountsBeforeAndAfter.get("rightBefore");
ExcelUtils.ParaMap paraMap15 = excelUtils.new ParaMap(28,0, "前后十账户", rightBefore, InvestmentAnalysis.class);
list.add(paraMap15); // 前后十账户-权益前十
List<InvestmentAnalysis> rightAfter = tenAccountsBeforeAndAfter.get("rightAfter");
ExcelUtils.ParaMap paraMap16 = excelUtils.new ParaMap(41,0, "前后十账户", rightAfter, InvestmentAnalysis.class);
list.add(paraMap16); // 前后十账户-权益后十
// 重点组合
ExcelUtils.ParaMap paraMap17 = excelUtils.new ParaMap(1,0, "重点组合", keyCombination, InvestmentAnalysis.class);
list.add(paraMap17);
// 危机组合
ExcelUtils.ParaMap paraMap18 = excelUtils.new ParaMap(1,0, "危机组合", crisisCombination, InvestmentAnalysis.class);
list.add(paraMap18);
// 账户明细
ExcelUtils.ParaMap paraMap19 = excelUtils.new ParaMap(1,0, "账户明细", accountDetails, InvestmentAnalysis.class);
list.add(paraMap19);
// 竞争账户
ExcelUtils.ParaMap paraMap20 = excelUtils.new ParaMap(1,0, "竞争账户-数据", competitiveAccount, InvestmentAnalysis.class);
list.add(paraMap20); // 竞争账户-数据
// 数据集交给excelUtils处理，根据模板生成excel文件
excelUtils.exportExcel(fileName.substring(0, fileName.indexOf(".")) + dto.getStartDate() + "-" + dto.getdDate() + fileName.substring(fileName.indexOf(".")), fileName, request, response, list);
}
2、导出模板ExcelUtils ：【需求1需求2共用】
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
/**
* 模板定义【方法名、数值类型、是否需要千分位】
* 例：getProfit,double,THOUS_BIT 需要千分位且右对齐
* 例：getCunt,double 不用千分位右对齐
* 例：ORDER_NUM,int 该列使用自增序号且居中
* int、String：居中, double：右对齐
* @author wan
* @date 2019/12/5 0005 上午 9:38
* @Version 1.0
*/
public class ExcelUtils {
public static final String DATA_LIST= "DATA_LIST"; // 数据集合
public static final String DATA_CLASS= "DATA_CLASS"; // 数据类型
public static final String INIT_ROWNUM= "INIT_ROWNUM"; // 开始填充数据行号
public static final String INIT_CELLNUM = "INIT_CELLNUM"; // 开始填充数据列号
public static final String SHEET_NAME = "SHEET_NAME"; // sheetName
public static final String ORDER_NUM= "ORDER_NUM"; // 序号列
public static final String BOLD_FIELD = "BOLD_FIELD"; // 加粗行 "getFieldName","合计","getFieldName2","合计",
public static final String THOUS_BIT = "THOUS_BIT"; // 千分位
private static short THOUS_BIT_SHORT = 0; // 千分位格式
private static Boolean ORDER_NUM_FLAG = false; // 是否需要序号
private static Boolean BOLD_IS_FLAG = false; // 是否加粗
private static CellStyle[][][][] styles = new CellStyle[2][2][2][2];
/**
* 初始化CellStyle数组,以供重复使用,每个单元格重新创建严重影响性能
* @param workbook
*/
private void init(Workbook workbook){
THOUS_BIT_SHORT = workbook.createDataFormat().getFormat("#,##0.00");
// 加粗Font【createNormalStyle设置了普通格式的Font，如果需要加粗则需重新设置setFont(fontBold)】
Font fontBold = this.createNormalFont(workbook);
fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体显示
CellStyle lemon = this.createNormalStyle(workbook);
lemon.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemon.setFillPattern(CellStyle.SOLID_FOREGROUND);
styles[0][1][1][1] = lemon;
CellStyle white = this.createNormalStyle(workbook);
white.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
white.setFillPattern(CellStyle.SOLID_FOREGROUND);
styles[1][1][1][1] = white;
CellStyle lemonBold = this.createNormalStyle(workbook); // 柠檬色加粗
lemonBold.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonBold.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonBold.setFont(fontBold); // 加粗
styles[0][0][1][1] = lemonBold;
CellStyle whiteBold = this.createNormalStyle(workbook); // 白色加粗
whiteBold.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteBold.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteBold.setFont(fontBold); // 加粗
styles[1][0][1][1] = whiteBold;
CellStyle lemonThous = this.createNormalStyle(workbook); // 柠檬色千分位
lemonThous.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonThous.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonThous.setDataFormat(THOUS_BIT_SHORT); // 千分位
styles[0][1][0][1] = lemonThous;
CellStyle whiteThous = this.createNormalStyle(workbook); // 白色千分位
whiteThous.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteThous.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteThous.setDataFormat(THOUS_BIT_SHORT); // 千分位
styles[1][1][0][1] = whiteThous;
CellStyle lemonRight = this.createNormalStyle(workbook); // 柠檬色右对齐
lemonRight.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[0][1][1][0] = lemonRight;
CellStyle whiteRight = this.createNormalStyle(workbook); // 白色右对齐
whiteRight.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[1][1][1][0] = whiteRight;
CellStyle lemonBoldThous = this.createNormalStyle(workbook); // 柠檬色加粗千分位
lemonBoldThous.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonBoldThous.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonBoldThous.setFont(fontBold); // 加粗
lemonBoldThous.setDataFormat(THOUS_BIT_SHORT); // 千分位
styles[0][0][0][1] = lemonBoldThous;
CellStyle whiteBoldThous = this.createNormalStyle(workbook); // 白色加粗千分位
whiteBoldThous.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteBoldThous.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteBoldThous.setFont(fontBold); // 加粗
whiteBoldThous.setDataFormat(THOUS_BIT_SHORT); // 千分位
styles[1][0][0][1] = whiteBoldThous;
CellStyle lemonBoldRight = this.createNormalStyle(workbook); // 柠檬色加粗右对齐
lemonBoldRight.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonBoldRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonBoldRight.setFont(fontBold); // 加粗
lemonBoldRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[0][0][1][0] = lemonBoldRight;
CellStyle whiteBoldRight = this.createNormalStyle(workbook); // 白色加粗右对齐
whiteBoldRight.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteBoldRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteBoldRight.setFont(fontBold); // 加粗
whiteBoldRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[1][0][1][0] = whiteBoldRight;
CellStyle lemonThousRight = this.createNormalStyle(workbook); // 柠檬色千分位右对齐
lemonThousRight.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonThousRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonThousRight.setDataFormat(THOUS_BIT_SHORT); // 千分位
lemonThousRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[0][1][0][0] = lemonThousRight;
CellStyle whiteThousRight = this.createNormalStyle(workbook); // 白色千分位右对齐
whiteThousRight.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteThousRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteThousRight.setDataFormat(THOUS_BIT_SHORT); // 千分位
whiteThousRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[1][1][0][0] = whiteThousRight;
CellStyle lemonBoldThousRight = this.createNormalStyle(workbook); // 柠檬色加粗千分位右对齐
lemonBoldThousRight.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());// 柠檬色
lemonBoldThousRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
lemonBoldThousRight.setFont(fontBold); // 加粗
lemonBoldThousRight.setDataFormat(THOUS_BIT_SHORT); // 千分位
lemonBoldThousRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[0][0][0][0] = lemonBoldThousRight;
CellStyle whiteBoldThousRight = this.createNormalStyle(workbook); // 白色加粗千分位右对齐
whiteBoldThousRight.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 白色
whiteBoldThousRight.setFillPattern(CellStyle.SOLID_FOREGROUND);
whiteBoldThousRight.setFont(fontBold); // 加粗
whiteBoldThousRight.setDataFormat(THOUS_BIT_SHORT); // 千分位
whiteBoldThousRight.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
styles[1][0][0][0] = whiteBoldThousRight;
}
/**
* 初始化标识参数【调优字段，加快for循环中的if判断，短路】
*/
private void init_flag(){
ORDER_NUM_FLAG = false;
BOLD_IS_FLAG = false;
}
/**
* 设置普通格式Font【微软雅黑、size：10】
* @param workbook
* @return
*/
private Font createNormalFont(Workbook workbook) {
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 10);
return font;
}
/**
* 设置普通格式样式【边框、边框颜色、普通格式Font】
* @param workbook
* @return
*/
private CellStyle createNormalStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置边框
style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(CellStyle.BORDER_THIN); // 左边框
style.setBorderTop(CellStyle.BORDER_THIN); // 上边框
style.setBorderRight(CellStyle.BORDER_THIN); // 右边框
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 设置底边颜色
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 设置左边颜色
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 设置右边颜色
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); // 设置顶边颜色
style.setFont(this.createNormalFont(workbook)); // 设置普通格式的Font
return style;
}
/**
* 根据模板导出excel
* @param fileName 导出的excel名字
* @param templateFileName 临时模板文件名
* @param list 集合数据【一个元素代表一个sheet页】
*/
public void exportExcel(String fileName, String templateFileName,
HttpServletRequest request, HttpServletResponse response,
List<Object> list) throws Exception {
// 根据模板名查找模板【resource下】并创建临时文件返回
File newFile = ExcelUtils.getFileFromTemplate(templateFileName);
// 根据临时文件输入流获取工作簿
Workbook workbook = WorkbookFactory.create(new FileInputStream(newFile));
// 初始化工作簿
this.init(workbook);
// 获取文件输出流
FileOutputStream fos = new FileOutputStream(newFile);
// 编辑工作簿
for (int i = 0; i < list.size(); i++) {
init_flag();// 初始化参数
this.fillDataToTemplate(workbook, (Map<String, Object>)list.get(i));
}
// 工作簿写入输出流
workbook.write(fos);
fos.flush();
IOUtils.closeQuietly(fos);
// 下载
// 设置response头信息
this.setDownloadHeader(request, response, fileName, newFile.length());
// 将该临时文件写入response流
OutputStream out = response.getOutputStream();
FileInputStream in = new FileInputStream(newFile);
byte[] b = new byte[1024];
int len;
while((len = in.read(b)) > -1) {
out.write(b, 0, len);
}
out.flush();
IOUtils.closeQuietly(out);
// 删除临时文件
this.deleteFile(newFile);
}
/**
* 特定样式导出excel【无模板，由前端FormData包装的blob对象传给后端 用MultipartFile接收】
* @param fname 文件名
* @param is 文件流
* @param gsCells 固收最末列
* @param qyCells 权益最末列
*/
public void exportExcelAggInvAna(String fname, InputStream is, Integer gsCells, Integer qyCells, HttpServletRequest request, HttpServletResponse response) throws Exception {
// 根据流创建临时文件
File newFile = File.createTempFile(fname.substring(0, fname.indexOf('.')), fname.substring(fname.indexOf('.')));
// 将流内容写入该临时文件
FileUtils.copyInputStreamToFile(is, newFile);
// 根据file文件获取workbook
Workbook workbook = WorkbookFactory.create(new FileInputStream(newFile));
init(workbook); // 初始化
// 获取sheet数,循环sheet
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
int numberOfCells = i == 0 ? gsCells : qyCells;
Sheet sheet = workbook.getSheetAt(i);
sheet.setDisplayGridlines(false);// 取消显示网格线
// 获取sheet的总行数,循环行
int numberOfRows = sheet.getLastRowNum() + 1;
for (int j = 0; j < numberOfRows; j++) {
Row row = sheet.getRow(j);
if (row == null) {
row = sheet.createRow(j);
}
if (j == 27) { // 设置行高【该行需要伸展高度】
row.setHeightInPoints(34);//【需要整行设置】
}
// 循环列【列由前端传回,因为有合并项,所以无法确定最后一列的下标,样式调整时可能导致边框颜色无法设置】
for (int k = 0; k < numberOfCells; k++) {
Cell cell = row.getCell(k);
if (cell == null) {
cell = row.createCell(k);
}
// 数值部分【右对齐】
if (j > 1 && k > 6) {
cell.setCellStyle(chooseStyle(j % 2, false, false, true));
if (k > 8 && k % 3 == 0) {
cell.setCellStyle(chooseStyle(0, false, false, true));
}
}else { // 标题部分【加粗、居中】
cell.setCellStyle(chooseStyle(j % 2, true, false, false));
if (k == 0 || (j == 11 && k == 2)) {
cell.setCellStyle(chooseStyle(0, true, false, false));
}
}
}
}
}
// 获取临时文件输出流
FileOutputStream fos = new FileOutputStream(newFile);
// 模板写入文件
workbook.write(fos);
fos.flush();
IOUtils.closeQuietly(fos);
// 设置response头信息
this.setDownloadHeader(request, response, fname, newFile.length());
// 将文件写入response的输出流
OutputStream out = response.getOutputStream();
FileInputStream in = new FileInputStream(newFile);
byte[] b = new byte[1024];
int len;
while((len = in.read(b)) > -1) {
out.write(b, 0, len);
}
out.flush();
IOUtils.closeQuietly(out);
// 删除创建的新文件
this.deleteFile(newFile);
}
/**
* 根据模板填充excel
*/
public <E> void fillDataToTemplate(Workbook workbook, Map<String, Object> map) throws Exception {
fillDataToTemplate(workbook, map.get(SHEET_NAME) == null ? "" : map.get(SHEET_NAME).toString(), map);
}
/**
* 根据模板填充excel
* @param workbook 模板excel
* @param sheetName sheet名
* @param map 导出相关配置map
*/
public <E> void fillDataToTemplate(Workbook workbook, String sheetName, Map<String, Object> map) throws Exception {
// 填充的数据类型
Class clz = (Class) map.get(DATA_CLASS);
// 开始填充数据的行下标
int rownum = (int) map.get(INIT_ROWNUM);
// 开始填充数据的列下标
int cellnum = (int) map.get(INIT_CELLNUM);
// 填充的数据集
List<E> objs = (List<E>) map.get(DATA_LIST);
Sheet sheet = null;
if (sheetName == null || sheetName == "") {
throw new Exception();
}else {
sheet = workbook.getSheet(sheetName);
if (sheet == null) {
sheet = workbook.createSheet(sheetName);
}
}
// get方法名,模板中定义【没有直接获取Method对象,因为要配合序号使用,序号列的value会存入ORDER_NUM】
TreeMap<Integer, String> methodMap = new TreeMap();
// excel类型,模板中定义【STRING、INT：居中,DOUBLE：右对齐】
TreeMap<Integer, String> typedMap = new TreeMap();
// 是否需要千分位【THOUS_BIT】
TreeMap<Integer, String> thoudsBitMap = new TreeMap();
// 根据起始行下标获取row
Row sheetRow = sheet.getRow(rownum);
int cellField = cellnum;
for (int count = 0; true; cellField++, count++) {// 如果excel没有END则会出现空指针异常
// 根据起始行、起始列获取cellValue
String value = sheetRow.getCell(cellField).toString();
// 是否为序号列
if (value.contains(ORDER_NUM)) {
ORDER_NUM_FLAG = true; // 将排序字段标识符设为true
methodMap.put(count, ORDER_NUM); // 如果excel没有END则会出现空指针异常
// 这里给序号也设置了type,所以序号也可以右对齐
typedMap.put(count, value.substring(value.indexOf(',') + 1));// 如果excel没有END则会出现空指针异常
continue;
}
if ("END".equals(value)) {
break;
}
List<String> strs = Arrays.asList(value.split(","));
methodMap.put(count, strs.get(0)); // 如果excel没有END则会出现空指针异常
typedMap.put(count, strs.get(1)); // 如果excel没有END则会出现空指针异常
if (value.contains(THOUS_BIT)) { // 包含千分位,将需要千分位的
thoudsBitMap.put(count, strs.get(0)); // 将需要千分位的字段，将getField方法名放进去
}
}
sheetRow.getCell(cellField).setCellValue("");// 将END置空
Row row = null;
Cell cell = null;
String type = "";
if (objs != null && objs.size() > 0) {
Map<String, String> boldMap = (Map<String, String>)map.get(BOLD_FIELD);
if (boldMap != null && boldMap.size() > 0) {
BOLD_IS_FLAG = true; // 将加粗标识符设为true
}
// 一个元素是一行
for (int i = 0; i < objs.size(); i++) {
E obj = objs.get(i);
row = sheet.getRow(i + rownum) == null ? sheet.createRow(i + rownum) : sheet.getRow(i + rownum);
// 当前行是否加粗【行级样式】
boolean rowBoldFlag = false;
if (BOLD_IS_FLAG) {
for (Map.Entry<String, String> entry : boldMap.entrySet()) {
// 反射获得该元素的该字段,是否等于标识为需要加粗的值,例如"合计"
if (entry.getValue().equals(clz.getDeclaredMethod(entry.getKey()).invoke(obj))) {
rowBoldFlag = true;
break;
}
}
}
// 元素属性循环
for (int j = 0; j < methodMap.size(); j++) {
boolean cellThousFlag = false; // 千分位标识
boolean cellRightFlag = false; // 右对齐标识
type = typedMap.get(j).toUpperCase(); // 数值类型
cell = row.createCell(j + cellnum);
String methodName = methodMap.get(j); // 获取方法名
// 当前单元格是否需要千分位
if (methodName.equals(thoudsBitMap.get(j))) {
cellThousFlag = true;
}
// 所有DOUBLE默认右对齐
if ("DOUBLE".equals(type)) {
cellRightFlag = true;
}
cell.setCellStyle(chooseStyle(i % 2, rowBoldFlag, cellThousFlag, cellRightFlag));// 【背景色_加粗_千分位_右对齐】
// 设置cellValue值
if (ORDER_NUM_FLAG && ORDER_NUM.equals(methodName)){ // 如果包含序号则直接将i为其设置
cell.setCellValue(i + 1);
continue;
}
Object value = clz.getDeclaredMethod(methodName).invoke(obj);
if (value == null) {
cell.setCellValue("");
}else {
switch (type) {
case "DOUBLE":
cell.setCellValue(Double.parseDouble(value.toString()));
break;
case "INT":
cell.setCellValue(Integer.parseInt(value.toString()));
break;
case "STRING":
cell.setCellValue(value.toString());
break;
default:
cell.setCellValue(value.toString());
break;
}
}
}
}
}else {
for (int j = 0; j < methodMap.size(); j++) {// 如果没有数据，将公式清空
row = sheet.getRow(rownum) == null ? sheet.createRow(rownum) : sheet.getRow(rownum);
row.getCell(j + cellnum).setCellValue("");// 将公式设成空值
}
}
}
/**
* 获取CellStyle
* @param i 行号 起始行是0柠檬黄
* @param rowBoldFlag 加粗
* @param cellThousFlag 千分位处理
* @param cellRightFlag 右对齐
* @return
*/
private CellStyle chooseStyle(int i, boolean rowBoldFlag, boolean cellThousFlag, boolean cellRightFlag) {
int colorIndex = i == 0 ? 0 : 1;
int boldIndex = rowBoldFlag ? 0 : 1;
int thousIndex = cellThousFlag ? 0 : 1;
int rightIndex = cellRightFlag ? 0 : 1;
return styles[colorIndex][boldIndex][thousIndex][rightIndex];
}
/**
* 删除文件
*/
private void deleteFile(File... files) {
for (File file : files) {
if (file.exists()) {
file.delete();
}
}
}
/**
* 根据模板文件创建临时文件
* @param fileName 模板文件名
* @return 临时文件
*/
public static File getFileFromTemplate(String fileName) throws IOException {
// 获得模板文件
ClassPathResource classPathResource = new ClassPathResource(fileName);
InputStream inputStream = classPathResource.getInputStream();
// 根据模板文件创建临时文件
File file = File.createTempFile(fileName.substring(0, fileName.indexOf('.')), fileName.substring(fileName.indexOf('.')));
FileUtils.copyInputStreamToFile(inputStream, file);
IOUtils.closeQuietly(inputStream);
return file;
}
/**
* 设置response头信息
* @param request
* @param response
* @param fileName
*/
public static void setDownloadHeader(HttpServletRequest request,
HttpServletResponse response,
String fileName, long length) {
String userAgent = request.getHeader("USER-AGENT").toLowerCase();
try {
Object finalFileName = null;
if (userAgent.contains("webkit")) {
finalFileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else if (userAgent.contains("firefox")) {
finalFileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
} else {
finalFileName = new String(fileName.getBytes("GBK"), "ISO8859-1");
}
response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName.toString() + "\"");
response.addHeader("Content-Length", "" + length);
response.setContentType("text/html;charset=ISO8859-1");
response.setContentType("application/x-msdownload");
} catch (UnsupportedEncodingException var5) {
var5.printStackTrace();
}
}
/**
* 导出数据包装类【一个ParaMap对应一个sheet】
* @param <E>
*/
public class ParaMap<E> extends HashMap {
public ParaMap(){
super();
}
public ParaMap(int rownum, int cellnum, String sheetName, List<E> datas, Class clz, String ...boldStr){
this();
HashMap<String, String> map = new HashMap<>();
for (int i = 0; i < boldStr.length; i = i + 2) {
map.put(boldStr[i], boldStr[i + 1]);
}
this.put(ExcelUtils.INIT_ROWNUM, rownum); // 开始填充数据的行下标
this.put(ExcelUtils.INIT_CELLNUM, cellnum); // 开始填充数据的列下标
this.put(ExcelUtils.SHEET_NAME, sheetName); // sheetName
this.put(ExcelUtils.DATA_LIST, datas); // 数据集合
this.put(ExcelUtils.DATA_CLASS, clz); // 数据类型
this.put(ExcelUtils.BOLD_FIELD, map); // 加粗行
}
}
}
需求2：js根据table生成excel对象传回后端
使用：xlsx-style、异步、blob、FormData
/**
* 无模板版本导出excel【该版本是通过js生成的excel文件，包装成FormData对象传给后端接收处理样式，后端用MultipartFile 接收FormData对象】
* 【其中FormData对象包装的"data"是blob对象，可结合js代码理解】
* @param fname 文件名
* @param file FormData包装的blob文件
* @param gsCells 固收最末列下标
* @param qyCells 权益最末列下标
*/
@PostMapping(value = "exportCfgAna")
@ResponseBody
public void exportCfgAna(@RequestParam(value = "fname", required = false)String fname,
@RequestParam(value = "data", required = false)MultipartFile file,
@RequestParam(value = "gsCells", required = false)Integer gsCells,
@RequestParam(value = "qyCells", required = false)Integer qyCells,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
GeneralResult result = new GeneralResult();
ExcelUtils utils = new ExcelUtils();
try {
utils.exportExcelAggInvAna(fname, file.getInputStream(), gsCells, qyCells, request, response);
} catch (Exception e) {
result.fail(CodeUtils.CREATE_MODEL_ERROR, e.getMessage());
e.printStackTrace();
}
}
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
