数据导入导出功能 - yiyanglij/yiyanglij.github.io GitHub Wiki

EasyExcel

相关依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.1</version>
</dependency>

导入导出需要定义对象，对象上需要引用easyexcel标签，所以model模块需要引入，scope：provided

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <scope>provided </scope>
</dependency>

导入导出我们会把它封装成工具类，放在common-util中，所有模块公用，所以该模块也得引入

 <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
 </dependency>

实体

 @ExcelProperty(value = "id",index = 0)
 private Long id;

 @ExcelProperty(value = "上级id",index = 1)
 private Long parentId;

Controller导出

 @ApiOperation(value="导出")
 @GetMapping(value = "/exportData")
 public void exportData(HttpServletResponse response) {
    dictService.exportData(response);
 }

Service导出

  void exportData(HttpServletResponse response);

ServiceImpl导出

  @Override
  public void exportData(HttpServletResponse response) {
  try {
  response.setContentType("application/vnd.ms-excel");
  response.setCharacterEncoding("utf-8");
  // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
  String fileName = URLEncoder.encode("数据字典", "UTF-8");
  response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");

  List<Dict> dictList = dictMapper.selectList(null);
  List<DictEeVo> dictVoList = new ArrayList<>(dictList.size());
  for(Dict dict : dictList) {
     DictEeVo dictVo = new DictEeVo();
     BeanUtils.copyBean(dict, dictVo, DictEeVo.class);
     dictVoList.add(dictVo);
  }

  EasyExcel.write(response.getOutputStream(), DictEeVo.class).sheet("数据字典").doWrite(dictVoList);
  } catch (IOException e) {
      e.printStackTrace();
  }
}

导入创建监听器

 public class DictListener extends AnalysisEventListener<DictEeVo> {

private DictMapper dictMapper;
public DictListener(DictMapper dictMapper) {
    this.dictMapper = dictMapper;
}

//一行一行读取
@Override
public void invoke(DictEeVo dictEeVo, AnalysisContext analysisContext) {
    //调用方法添加数据库
    Dict dict = new Dict();
    BeanUtils.copyProperties(dictEeVo,dict);
    dictMapper.insert(dict);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {

}
}

Service导入

@Override
public void importDictData(MultipartFile file) {
try {
    EasyExcel.read(file.getInputStream(),DictEeVo.class,new DictListener(baseMapper)).sheet().doRead();
} catch (IOException e) {
    e.printStackTrace();
}
}

Controller 导入

@ApiOperation(value = "导入")
@PostMapping("importData")
public Result importData(MultipartFile file) {
  dictService.importData(file);
return Result.ok();
}

poi实现数据的导出

相关依赖

 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.1.0</version>
 </dependency>

Controller

@ApiOperation("导出应收账单")
@GetMapping(value = "getFinanceExcel")
@ApiImplicitParams({
        @ApiImplicitParam(name = "type", value = "类型", dataType = "String"),
        @ApiImplicitParam(name = "status", value = "状态", dataType = "String"),
        @ApiImplicitParam(name = "orderNo", value = "运单号", dataType = "String"),
        @ApiImplicitParam(name = "customerName", value = "客户", dataType = "String"),
        @ApiImplicitParam(name = "bankAccount", value = "单号", dataType = "String"),
        @ApiImplicitParam(name = "beginTime", value = "开始时间", dataType = "String"),
        @ApiImplicitParam(name = "endTimes", value = "结束时间", dataType = "String")
})
public String getFinanceExcel(FinanceVo financeVo) {
    return this.financeService.getFinanceExcel(financeVo);
}

Service

 String getFinanceExcel(FinanceVo financeVo);

ServiceImpl

 @Override
public String getFinanceExcel(FinanceVo financeVo) {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("应收账单数据表");
    createTitle(workbook, sheet);

    List<FinanceMonthVo> financeVoIPage = this.financeMapper.getFinanceReceivableList(financeVo);

    //设置日期格式
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN); //下边框
    style.setBorderLeft(BorderStyle.THIN);//左边框
    style.setBorderTop(BorderStyle.THIN);//上边框
    style.setBorderRight(BorderStyle.THIN);//右边框
    //style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    //新增数据行，并且设置单元格数据
    int rowNum = 3;

    double amountTotal = 0;
    for (FinanceMonthVo financeMonthVo : financeVoIPage) {
        List<FinanceVo> financeVoList = this.financeMapper.financeVoListExcel(financeMonthVo.getMemberId(), financeMonthVo.getMonth());
        HSSFRow row = sheet.createRow(rowNum);

        for (FinanceVo financeVo1 : financeVoList) {
            row = sheet.createRow(rowNum);
            row.setHeightInPoints(20);
            HSSFCell cell;
            cell = row.createCell(0);
            cell.setCellValue(financeMonthVo.getMonth());
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue(financeMonthVo.getCustomerName());
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue(financeVo1.getContacts());
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue(financeVo1.getOrderNo());
            cell.setCellStyle(style);

            cell = row.createCell(4);
            cell.setCellValue(financeVo1.getShipAddress());
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue(financeVo1.getReceivingAddress());
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue(financeVo1.getGoodsNum());
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue(financeVo1.getGoodsVolume());
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue(financeVo1.getGoodsLoad());
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue(financeVo1.getDefinedNo());
            cell.setCellStyle(style);

            cell = row.createCell(10);
            String type = Finance.EType.valueOf(financeVo1.getType()).desc();
            if(type.equals("基础费用")){
                type = "运费";
            }
            cell.setCellValue(type);
            cell.setCellStyle(style);

            if(financeVo1.getServiceRemark()!=null&&StringUtils.isNumeric(financeVo1.getServiceRemark())){
                amountTotal += Integer.parseInt(financeVo1.getServiceRemark());
            }
            if (financeVo1.getType().equals(Finance.EType.BASICS.val())) {
                cell = row.createCell(11);
                cell.setCellValue(financeVo1.getServiceRemark());
                cell.setCellStyle(style);
            } else if (financeVo1.getType().equals(Finance.EType.CHANGE.val())) {
                cell = row.createCell(11);
                cell.setCellValue(financeVo1.getCheckRemark());
                cell.setCellStyle(style);
            } else {
                cell = row.createCell(11);
                cell.setCellValue(financeVo1.getOrderCarriageRemark());
                cell.setCellStyle(style);
            }

            // row.setHeightInPoints(30);
            rowNum++;
        }


        /*HSSFCell cell = row.createCell(0);
        cell.setCellStyle(style);*/
        // rowNum += 1;
    }

    HSSFCellStyle styleColor = workbook.createCellStyle();
    //styleColor.setFillForegroundColor((short)43);// 设置背景色
    //style.setFillBackgroundColor(new HSSFColor.RED().getIndex());
    styleColor.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
    // style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
    // style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());//前景填充色
    styleColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    styleColor.setBorderBottom(BorderStyle.THIN); //下边框
    styleColor.setBorderLeft(BorderStyle.THIN);//左边框
    styleColor.setBorderTop(BorderStyle.THIN);//上边框
    styleColor.setBorderRight(BorderStyle.THIN);//右边框
   /* HSSFFont font = workbook.createFont();
    font.setBold(true);
    font.setFontHeightInPoints((short) 12);
    font.setColor(Font.COLOR_NORMAL);;
    styleColor.setFont(font);*/

    HSSFRow row = sheet.createRow(rowNum);
   /* row.setHeightInPoints(20);
    row.createCell(10).setCellValue("合计：");
    row.createCell(11).setCellValue(amountTotal);
    row.setRowStyle(styleColor);*/
   /* HSSFCell cell = row.createCell(10);
    cell.setCellStyle(styleColor);
    HSSFCell cell1 = row.createCell(11);
    cell1.setCellStyle(styleColor);*/


    HSSFCell cell1;
    cell1 = row.createCell(10);
    cell1.setCellValue("合计:");
    cell1.setCellStyle(styleColor);
    row.setHeightInPoints(20);

    cell1 = row.createCell(11);
    cell1.setCellValue(amountTotal);
    cell1.setCellStyle(styleColor);
    row.setHeightInPoints(20);


    row.setHeightInPoints(20);


    return this.fileSystem.write(o -> {
        try {
            workbook.write(o);
            workbook.close();
        } catch (IOException e) {
            log.info("IOException ", e);
        }

    });
}

表头设置信息

 private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
    int rowNum = 0;
    HSSFRow row = sheet.createRow(0);
    //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
    for (int i = 0; i < 18; i++) {
        sheet.setColumnWidth(i, 17 * 256);
        sheet.autoSizeColumn(i, true);//先设置自动列宽
        //sheet.setColumnWidth(i, sheet.getColumnWidth(i)*20/10);
    }

    //设置为居中加粗
    HSSFCellStyle style0 = workbook.createCellStyle();
    HSSFFont font0 = workbook.createFont();
    font0.setBold(true);
    font0.setFontHeightInPoints((short) 16);
    style0.setAlignment(HorizontalAlignment.CENTER);
    //  style0.setBorderBottom(BorderStyle.THIN); //下边框
    // style0.setBorderLeft(BorderStyle.THIN);//左边框
    // style0.setBorderTop(BorderStyle.THIN);//上边框
    style0.setBorderRight(BorderStyle.THIN);//右边框
    //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style0.setFont(font0);

    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 11);
    sheet.addMergedRegion(region);
    region = new CellRangeAddress(1, 1, 0, 11);
    sheet.addMergedRegion(region);

    HSSFCell cell1;
    cell1 = row.createCell(0);
    cell1.setCellValue("美尔顺供应链管理有限公司");
    cell1.setCellStyle(style0);
    row.setHeightInPoints(35);
    cell1 = row.createCell(11);
    cell1.setCellStyle(style0);

    rowNum++;
    row = sheet.createRow(rowNum);
    //  row.setHeightInPoints(30);


    //设置为居中加粗
    HSSFCellStyle style1 = workbook.createCellStyle();
    HSSFFont font1 = workbook.createFont();
    font1.setBold(true);
    //style.setAlignment(HorizontalAlignment.CENTER);
    //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style1.setFont(font1);
    // style1.setAlignment(HorizontalAlignment.CENTER);
    style1.setBorderBottom(BorderStyle.THIN); //下边框
    style1.setBorderLeft(BorderStyle.THIN);//左边框
    style1.setBorderTop(BorderStyle.THIN);//上边框
    style1.setBorderRight(BorderStyle.THIN);//右边框

    HSSFCell cell2;
    cell2 = row.createCell(0);
    cell2.setCellValue("账户：东莞市美尔顺供应链管理有限公司        账号：718572742934            开户行：中国银行东莞南城支行");
    cell2.setCellStyle(style1);

    cell2 = row.createCell(1);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(2);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(3);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(4);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(5);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(6);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(7);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(8);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(9);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(10);
    cell2.setCellStyle(style1);

    cell2 = row.createCell(11);
    cell2.setCellStyle(style1);



    row.setHeightInPoints(20);
    rowNum++;

    // row.setHeightInPoints(30);
    /* row.createCell(0).setCellValue(" 美尔顺供应链管理有限公司");
    row.setRowStyle(style);
    row = sheet.createRow(rowNum);
    rowNum++;

    row.createCell(0).setCellValue(" 账户1：东莞市美尔顺供应链管理有限公司        账号：718572742934            开户行：中国银行东莞南城支行  ");
    row.setRowStyle(style);
    row = sheet.createRow(rowNum);
    rowNum++;*/


    //设置为居中加粗
    HSSFCellStyle style = workbook.createCellStyle();
    HSSFFont font = workbook.createFont();
    font.setBold(true);
    //font.setFontHeightInPoints((short)12);
    style.setAlignment(HorizontalAlignment.CENTER);
    //style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFont(font);
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setBorderBottom(BorderStyle.THIN); //下边框
    style.setBorderLeft(BorderStyle.THIN);//左边框
    style.setBorderTop(BorderStyle.THIN);//上边框
    style.setBorderRight(BorderStyle.THIN);//右边框

    row = sheet.createRow(rowNum);
    row.setHeightInPoints(20);

    HSSFCell cell;
    cell = row.createCell(0);
    cell.setCellValue("月份");
    cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue("客户");
    cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue("联系人");
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("关联运单号");
    cell.setCellStyle(style);


    cell = row.createCell(4);
    cell.setCellValue("提货地");
    cell.setCellStyle(style);


    cell = row.createCell(5);
    cell.setCellValue("送货地");
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("数量(件)");
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellValue("体积(方)");
    cell.setCellStyle(style);

    cell = row.createCell(8);
    cell.setCellValue("重量(公斤)");
    cell.setCellStyle(style);

    cell = row.createCell(9);
    cell.setCellValue("客户单号");
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellValue("项目");
    cell.setCellStyle(style);


    cell = row.createCell(11);
    cell.setCellValue("总费用");
    cell.setCellStyle(style);

}
⚠️ **GitHub.com Fallback** ⚠️