动态导出文件,客户把文件信息补充完整,导入文件时,需要校验文件的真实性及文件的唯一性,确保导入的文件是单据的!
// 动态导出文件
@ApiOperation(value = "填报登记模板下载")
@PostMapping(value = "/xxx/templateDownLoad")
public void download(@RequestBody String jsonString, @RequestHeader("x-user-token") String token, HttpServletResponse response) {
// 生成初始化,list内放需要导出的数据
List<TemplateVo> findList = new ArrayList<>();
Model model = JSON.parseObject(jsonString, Model.class);
String msgid = model.getMsgid();
if (StringUtils.isEmpty(msgid)){
logger.info("未获取到唯一标识");
throw new BusizException("唯一标识不能为空");
}
// 取得sql或者业务的内容
findList = xxxService.getAll(model);
try {
//设置单元格位置
CellRangeAddress cellAddresses = new CellRangeAddress(0, 0, 0, 0);
//1.创建excel文档对象
XSSFWorkbook wb = new XSSFWorkbook();
//样式
Map<String, XSSFCellStyle> cellStyle = createCellStyle(wb);
//2.创建sheet对象
XSSFSheet sheet = wb.createSheet("模板");
//创建提示标题信息
XSSFRow row0 = sheet.createRow(0);
createTitle(row0, cellAddresses, sheet, cellStyle,msgid);
//3.创建表头,创建第一行
XSSFRow row1 = sheet.createRow(1);
createHeaders(row1, cellAddresses, sheet, cellStyle);
//填充数据
createDataCellFdplPlan(findList1, sheet, cellStyle);
//设置列宽
setColumnLength(sheet);
// 设置日期信息
Calendar now = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateNowStr = sdf.format(now.getTime());
// 开启流
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
byte[] bytes = bos.toByteArray();
bos.close();
response.setHeader("Content-Disposition", "attachment; filename=\"iJEP_" + dateNowStr + ".xlsx\"");
response.addHeader("Content-Length", "" + bytes.length);
response.setContentType("application/octet-stream; charset=UTF-8");
IOUtils.write(bytes, response.getOutputStream());
logger.info("登记模板--附件下载完成!");
} catch (Exception e) {
logger.info("登记模板--附件下载失败!" + e);
}
}
// 样式设计
private Map<String, XSSFCellStyle> createCellStyle(XSSFWorkbook wb){
Map<String, XSSFCellStyle> styles = new HashMap();
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short)12);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom((XSSFCellStyle)styles.get("data"));
style.setAlignment(HorizontalAlignment.LEFT);
Font dataFont2 = wb.createFont();
dataFont2.setFontName("Arial");
dataFont2.setFontHeightInPoints((short)12);
dataFont2.setColor(Font.COLOR_RED);
style.setFont(dataFont2);
style.setIndention((short)2);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom((XSSFCellStyle)styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short)10);
headerFont.setBold(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
Font totalFont = wb.createFont();
totalFont.setFontName("Arial");
totalFont.setColor(Font.COLOR_RED);
totalFont.setFontHeightInPoints((short)10);
style.setFont(totalFont);
styles.put("total", style);
style = wb.createCellStyle();
style.cloneStyleFrom((XSSFCellStyle)styles.get("data"));
style.setAlignment(HorizontalAlignment.LEFT);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom((XSSFCellStyle)styles.get("data"));
style.setAlignment(HorizontalAlignment.CENTER);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom((XSSFCellStyle)styles.get("data"));
style.setAlignment(HorizontalAlignment.RIGHT);
styles.put("data4", style);
return styles;
}
private void createTitle(XSSFRow row0, CellRangeAddress cellAddresses, XSSFSheet sheet, Map<String, XSSFCellStyle> style, String msgid) {
XSSFCell cell0 = row0.createCell(0);
cell0.setCellValue("注意:模版编号为:"+msgid+",仅填写子节点项目即红色字体部分");
XSSFCellStyle style1 = style.get("total");
style1.setWrapText(true);
cell0.setCellStyle(style1); //为单元格设置样式
cellAddresses.setFirstRow(0); // 第一行的内容进行合并
cellAddresses.setLastRow(0);
cellAddresses.setFirstColumn(0); // 合并 从第一列开始
cellAddresses.setLastColumn(2); // 合并 最后一列的表示
sheet.addMergedRegion(cellAddresses);
}
// 创建标题
private void createHeaders(XSSFRow row1, CellRangeAddress cellAddresses, XSSFSheet sheet, Map<String, XSSFCellStyle> style) {
// 第一行
XSSFCell cell0 = row1.createCell(0);
cell0.setCellValue("项目编号");
cell0.setCellStyle(style.get("header"));//为单元格设置样式
XSSFCell cell1 = row1.createCell(1);
cell1.setCellValue("项目");
cell1.setCellStyle(style.get("header"));//为单元格设置样式
XSSFCell cell2 = row1.createCell(2);
cell2.setCellValue("金额(元)");
cell2.setCellStyle(style.get("header"));//为单元格设置样式
}
private void createDataCellFdplPlan(List<TemplateVo> findList, XSSFSheet sheet, Map<String, XSSFCellStyle> style) {
if (findList!=null&&findList.size()>0){
for (int i = 0; i < findList.size(); i++) {
XSSFRow rowi = sheet.createRow(i + 2);
TemplateVo templateVo = findList.get(i);
for (int j = 0; j <1 ; j++) {
XSSFCell cell0 = rowi.createCell(j+0);
cell0.setCellValue(templateVo .getPlanItemCod());
cell0.setCellStyle(style.get("data2"));//为单元格设置样式
XSSFCell cell1 = rowi.createCell(j+1);
if ("0".equals(templateVo .getIsSuperCode())){
cell1.setCellValue("*"+templateVo .getPlanItemNam());
XSSFCellStyle style1 = style.get("data1");
style1.setWrapText(true);
cell1.setCellStyle(style1);//为单元格设置样式
}else{
cell1.setCellValue(templateVo .getPlanItemNam());
XSSFCellStyle style2 = style.get("data2");
style2.setWrapText(true);
cell1.setCellStyle(style2);//为单元格设置样式
}
XSSFCell cell2 = rowi.createCell(j+2);
cell2.setCellValue("");
cell2.setCellStyle(style.get("data4"));//为单元格设置样式
}
}
}
}
// 设置列宽
private void setColumnLength(XSSFSheet sheet) {
sheet.autoSizeColumn(0,true);
sheet.autoSizeColumn(1,true);
int width = sheet.getColumnWidth(1)+1600;
sheet.setColumnWidth(1,width);
sheet.setColumnWidth(2,5000);
}
动态导出文件,特别表示的最好在第一行或者最后一行
导入文件设置:
// 导入文件,从第几行开始验证,怎么读取第一行数据
@ApiOperation(value = "上传内容")
@PostMapping(value = "/import/upload")
public ResponseBean upload(@RequestParam(value = "file") MultipartFile file, TempleVo templeVo , @ApiIgnore @RequestHeader(WebHeaderCode.X_USER_TOKEN) String token){
FdpPlanRegisterReturnVo fdpPlanRegisterReturn = new FdpPlanRegisterReturnVo(); // 错误信息及成功信息的返回信息
// 前端传来的唯一标识
String msgid = templeVo .getMsgid();
if ("".equals(msgid) || null == msgid) {
logger.info("未获取到计划唯一标识");
return ResponseBean.fail("未获取到计划唯一标识!");
}
InputStream is = null;
try {
is = file.getInputStream(); // 前端传来的文件
if (is == null) {
logger.info("计划填报登记导入模板");
fdpPlanRegisterReturn.setCode("401");
fdpPlanRegisterReturn.setMessage("计划填报登记导入模板");
return ResponseBean.success(fdpPlanRegisterReturn);
}
//模版校验 is 不要进行重新赋值,不然流会变空
String msg = checkTemplet(msgid, file.getInputStream());
if (StringUtils.isNotEmpty(msg)){
return ResponseBean.fail(msg);
}
UserInfoCache cacheInfo = userInfoService.getCacheInfo(token); // 从token获取用户信息
// 解析数据
ImportParams params = new ImportParams();
params.setTitleRows(1); // 标题从有几行
params.setHeadRows(1); // 从第几行开始读取
params.setStartRows(1); // 开始的行数
logger.info("解析数据开始");
// ExportExcel 你要映射的实体类
List<ExportExcel> plans = ExcelImportUtil.importExcel(is, ExportExcel.class, params);
logger.info("解析数据结束");
if (plans != null && plans.size() == 0) {
logger.info("计划填报登记导入模板无数据");
fdpPlanRegisterReturn.setCode("401");
fdpPlanRegisterReturn.setMessage("计划填报登记导入模板无数据");
return ResponseBean.success(fdpPlanRegisterReturn);
}
//数据校验 等
String msg = xxService.checkData(plans );
return ResponseBean.success(fdpPlanRegisterReturn);
} catch (NoSuchElementException e) {
fdpPlanRegisterReturn.setCode("402");
fdpPlanRegisterReturn.setMessage("excel文件不能为空");
return ResponseBean.success(fdpPlanRegisterReturn);
}catch (Exception e) {
if(e instanceof ExcelImportException){
fdpPlanRegisterReturn.setCode("402");
fdpPlanRegisterReturn.setMessage("请使用系统指定模板上传文件");
return ResponseBean.success(fdpPlanRegisterReturn);
}
if(e instanceof IllegalArgumentException){
fdpPlanRegisterReturn.setCode("402");
fdpPlanRegisterReturn.setMessage("请使用系统指定模板上传文件");
return ResponseBean.success(fdpPlanRegisterReturn);
}
logger.error("解析文件异常:" + e);
return ResponseBean.fail("解析文件异常");
}finally {
try {
if(is != null){
is.close();
}
} catch (IOException e) {
logger.error("", e);
}
}
}
// 根据唯一标识校验模板是否是这个单据的
private String checkTemplet(String msgid, InputStream is) {
Workbook book = null;
String title = null;
String msg = "";
try {
book = xxxxService.importExcel(is);
Sheet sheet = book.getSheetAt(0);
title = sheet.getRow(0).getCell(0).getStringCellValue();
if ("".equals(title) || null == title) {
msg = "标题不能为空";
} else {
int first = title.lastIndexOf(":")+1;
int last = title.lastIndexOf(",");
String templeteId = title.substring(first, last);
if (null==templeteId||""==templeteId){
msg = "模版编号不能为空";
}else{
if (!msgid.equals(templeteId)){
msg = "模版编号不一致,请检查导入模版是否为同一模版";
}
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
return msg;
}
实体类设置
package com.pactera.ts.service.fdpl.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import java.math.BigDecimal;
public class FdplPlanExportExcel {
// isImportField excel表格里必须有这个字段,报错内容为:请使用系统指定模板上传文件
@ApiModelProperty("资金计划项目编号")
@Excel(name = "资金计划项目编号",orderNum = "0",isImportField = "true")
private String planItemCod;
@ApiModelProperty("资金计划项目")
@Excel(name = "资金计划项目",orderNum = "1",isImportField = "true")
private String planItemNam;
@ApiModelProperty("计划金额(元)")
@Excel(name = "计划金额(元)",orderNum = "2",isImportField = "true",numFormat="#,##0.00")
private BigDecimal planAmt;
public String getPlanItemCod() {
return planItemCod;
}
public void setPlanItemCod(String planItemCod) {
this.planItemCod = planItemCod;
}
public String getPlanItemNam() {
return planItemNam;
}
public void setPlanItemNam(String planItemNam) {
this.planItemNam = planItemNam;
}
public BigDecimal getPlanAmt() {
return planAmt;
}
public void setPlanAmt(BigDecimal planAmt) {
this.planAmt = planAmt;
}
@Override
public String toString() {
return "FdplPlanExportVo{" +
"planItemCod='" + planItemCod + '\'' +
", planItemNam='" + planItemNam + '\'' +
", planAmt=" + planAmt +
'}';
}
}