使用poi导出excel合并表头

朱雀 2020-10-14 12:17 1133阅读 0赞

一、js代码

  1. $("body").on("click", "#btn_print", function () {
  2.             assessRankQueryPrint();
  3.         });
  4. function assessRankQueryPrint(){
  5.         var value_columns1 = ["考核年度","被考核人员","单位","岗位","专职","第一季度","第一季度","第二季度","第二季度","第三季度","第三季度","第四季度","第四季度","备注"];
  6.         var key_columns2 = ["year_","name_user","name_org","name_postduty","label","info01","info02","info04","info05","info07","info08","info10","info11","remarks"];
  7.         var value_columns2 = ["考核年度","被考核人员","单位","岗位","专职","考核得分","本周期岗位排名","考核得分","本周期岗位排名","考核得分","本周期岗位排名","考核得分","本周期岗位排名","备注"];
  8.         var url = "jee/VAssessQuarterRankQueryC/exportExecl?key_columns2="+key_columns2+"&value_columns1="+value_columns1+"&value_columns2="+value_columns2;
  9.         window.location.href= url;
  10.     }

二、后台代码

（1）controller

  1. @RequestMapping(value="jee/VAssessQuarterRankQueryC")
  2. @Controller
  3. public class VAssessQuarterRankQueryController extends BaseEntityController<VAssessQuarterRankQuery>{
  5.     public VAssessQuarterRankQueryController(){
  6.         super(VAssessQuarterRankQuery.class);
  7.     }
  9.     @Resource
  10.     private VAssessQuarterRankQueryService vAssessQuarterRankQueryService;
  12.     @ResponseBody
  13.     @SystemControllerLog(description="考核结果排名查询--导出功能")
  14.     @RequestMapping(value="exportExecl")
  15.     public ReturnDatas exportExecl(String[] key_columns2,String[] value_columns1,String[] value_columns2,HttpServletResponse response){
  16.         ReturnDatas returnDatas = ReturnDatas.getSuccessReturnDatas();
  17.         try {
  18.             response.setCharacterEncoding("UTF-8");
  19.             response.setContentType("application/msexcle");   
  20.             response.setHeader("content-disposition", "attachment;filename=test.xlsx");
  21.             HSSFWorkbook workbook = vAssessQuarterRankQueryService.exportExecl(key_columns2,value_columns1,value_columns2);
  22.             //写入Excel文件  
  23.             OutputStream outputStream =  response.getOutputStream();  
  24.             workbook.write(outputStream);  
  25.             outputStream.close();  
  26.             returnDatas.setStatus(ReturnDatas.SUCCESS);
  27.             return returnDatas;
  28.         } catch (Exception e) {
  29.             e.printStackTrace();
  30.             LogUtil.error("考核结果排名查询--导出功能异常："+e.getMessage(),e);
  31.             returnDatas.setStatus(ReturnDatas.ERROR);
  32.             returnDatas.setMessage("考核结果排名查询--导出功能异常。");
  33.         }
  34.         return returnDatas;
  35.     }
  36. }

（2）service

  1. @Service("vAssessQuarterRankQueryService")
  2. public class VAssessQuarterRankQueryServiceImpl implements VAssessQuarterRankQueryService {
  3.     private List<VAssessQuarterRankQuery> list;
  4.     @Resource
  5.     private IbaseInfoService baseInfoService;
  7.     /**
  8.      * 
  9.      * @Description 考核结果排名查询--查询操作
  10.      * @param rows
  11.      * @param page
  12.      * @param sort
  13.      * @param order
  14.      * @param key
  15.      * @param year_
  16.      * @param id_postduty
  17.      * @param label
  18.      * @return PageResult
  19.      * @throws Exception 
  20.      *
  21.      */
  22.     @Override
  23.     public PageResult listAllVAssessQuarterRankQuery(int rows, int page, String sort, String order, String key, String year_, String id_postduty, String label) throws Exception {
  24.         List<Object> ls_param = new ArrayList<Object>();
  25.         String hql = "select u from VAssessQuarterRankQuery u where (del_flag is null or del_flag != '1')";
  26.         String hqlCount = "select count(*) from VAssessQuarterRankQuery u where (del_flag is null or del_flag != '1')";
  27.         if (StringUtils.isBlank(key)) {
  28.             key = "";
  29.         }
  30.         if (StringUtils.isNotBlank(key)) {
  31.             hql += " and ( instr(type,?) > 0 or instr(updateuser,?)>0 or instr(updatetime,?)>0 )";
  32.             hqlCount += " and ( instr(type,?) > 0 or instr(updateuser,?)>0 or instr(updatetime,?)>0 )";
  33.             ls_param.add(key);
  34.             ls_param.add(key);
  35.             ls_param.add(key);
  36.         }
  37.         if (StringUtils.isNotBlank(year_)) {
  38.             year_ = year_.substring(0,4);
  39.             hql += " and year_ = ?";
  40.             hqlCount += " and year_ = ?";
  41.             ls_param.add(year_);
  42.         }
  43.         if (StringUtils.isNotBlank(label)) {
  44.             hql += " and soleduty_type = ?";
  45.             hqlCount += " and soleduty_type = ?";
  46.             ls_param.add(label);
  47.         }
  48.         if (StringUtils.isNotBlank(id_postduty)) {
  49.             hql += " and id_postduty = ?";
  50.             hqlCount += " and id_postduty = ?";
  51.             ls_param.add(id_postduty);
  52.         }
  53.         if (StringUtils.isNotBlank(sort) && StringUtils.isNotBlank(order)) {
  54.             hql += " order by " + sort + " " + order;
  55.         }
  56.         list = baseInfoService.listQueryByHql(hql, ls_param, rows, page);
  57.         Object total = baseInfoService.getObjectByHql(hqlCount, ls_param);
  58.         PageResult result = new PageResult();
  59.         if (list != null) {
  60.             result.setRows(list);
  61.             if (total != null) {
  62.                 result.setTotal(Integer.parseInt(total.toString()));
  63.             } else {
  64.                 result.setTotal(0);
  65.             }
  66.         }
  67.         return result;
  68.     }
  70.     /**
  71.      * 
  72.      * @Description 考核结果排名查询--导出功能  
  73.      * @param key_columns
  74.      * @param value_columns
  75.      * @return HSSFWorkbook
  76.      * @throws Exception 
  77.      *
  78.      */
  79.     @Override
  80.     public HSSFWorkbook exportExecl(String[] key_columns1,String[] value_columns1,String[] value_columns2) throws Exception {
  81.         List<List<String>> list1 = new ArrayList<>(); //所有单元格数据
  82.         List<String> list4 = new ArrayList<>(); //需要排名的字段
  83.         list4.add("info02");
  84.         list4.add("info05");
  85.         list4.add("info08");
  86.         list4.add("info11");
  87.         for (VAssessQuarterRankQuery v : list) {
  88.             List<String> list2 = new ArrayList<>(); //每一行数据
  89.             for (int i = 0; i < key_columns1.length; i++) {
  90.                 String field = ExportUtil.getFiled(v, key_columns1[i]);
  91.                 if(list4.contains(key_columns1[i])){
  92.                     String filed = key_columns1[i].replace("info", "");
  93.                     String total_person = "0";
  94.                     if(filed.matches("\\d+")) {//判断能否转为数字
  95.                         int f = Integer.parseInt(filed);
  96.                         String info = "info";
  97.                         if(f<10){
  98.                             info += 0;
  99.                         }
  100.                         total_person = ExportUtil.getFiled(v, info+(f+1));
  101.                     }
  102.                     if(StringUtils.isBlank(field)||"0".equals(field)) {
  103.                         list2.add("--/--");
  104.                     }else if("0".equals(total_person)) {
  105.                         list2.add("--/--");
  106.                     }else {
  107.                         list2.add(field+"/"+total_person);
  108.                     }
  109.                 }else {
  110.                     list2.add(field);
  111.                 }
  112.             }
  113.             list1.add(list2);
  114.         }
  115.         List<Integer[]> list3 = new ArrayList<>(); //要合并的行和列
  116.         Integer[] array1 = {0, 1, 0, 0};  
  117.         Integer[] array2 = {0, 1, 1, 1}; 
  118.         Integer[] array3 = {0, 1, 2, 2}; 
  119.         Integer[] array4 = {0, 1, 3, 3}; 
  120.         Integer[] array5 = {0, 1, 4, 4}; 
  121.         Integer[] array6 = {0, 0, 5, 6}; 
  122.         Integer[] array7 = {0, 0, 7, 8}; 
  123.         Integer[] array8 = {0, 0, 9, 10}; 
  124.         Integer[] array9 = {0, 0, 11, 12}; 
  125.         Integer[] array10 = {0, 1, 13, 13}; 
  126.         list3.add(array1);
  127.         list3.add(array2);
  128.         list3.add(array3);
  129.         list3.add(array4);
  130.         list3.add(array5);
  131.         list3.add(array6);
  132.         list3.add(array7);
  133.         list3.add(array8);
  134.         list3.add(array9);
  135.         list3.add(array10);
  136.         return ExportUtil.exportCell("季度考核成绩排名",key_columns1,value_columns1,value_columns2,list1,list3);
  137.     }
  138. }

（3）导出工具类

  1. public class ExportUtil {
  3.     public static HSSFWorkbook exportCell(String sheetName,String[] key_columns2,String[] value_columns1,String[] value_columns2,List<List<String>> list,List<Integer[]> list3){
  4.         //创建一个Excel文件
  5.         HSSFWorkbook workbook = new HSSFWorkbook();
  6.         //创建一个工作表
  7.         HSSFSheet sheet = workbook.createSheet(sheetName);
  8.         //添加表头行
  9.         HSSFRow hssfRow = sheet.createRow(0);
  10.         //设置单元格格式
  11.         HSSFCellStyle cellStyle = workbook.createCellStyle();
  12.         //设置单元格边框
  13.         cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  14.         cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  15.         cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  16.         cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  17.         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
  18.         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
  19.         for (int i = 0; i < value_columns1.length; i++) {
  20.             //添加表头内容
  21.             HSSFCell headCell = hssfRow.createCell(i);
  22.             headCell.setCellValue(value_columns1[i]);
  23.             headCell.setCellStyle(cellStyle);
  24.         }
  25.         hssfRow = sheet.createRow(1);
  26.         for (int i = 0; i < value_columns2.length; i++) {
  27.             //添加表头内容
  28.             HSSFCell headCell = hssfRow.createCell(i);
  29.             headCell.setCellValue(value_columns2[i]);
  30.             headCell.setCellStyle(cellStyle);
  31.         }
  32.         for (Integer[] arr : list3) {//合并表头
  33.             sheet.addMergedRegion(new CellRangeAddress(arr[0], arr[1], arr[2], arr[3])); 
  34.         }
  35.         //把数据添加到excel
  36.         for (int i = 0; i < list.size(); i++) {
  37.             hssfRow = sheet.createRow(i + 2);
  38.             for (int j = 0; j < list.get(i).size(); j++) {
  39.                 //创建单元格，并设置值
  40.                 HSSFCell cell = hssfRow.createCell(j);
  41.                 cell.setCellValue(list.get(i).get(j));
  42.                 cell.setCellStyle(cellStyle);
  44.             }
  45.         }
  46.         return workbook;
  47.     }  
  49.     /**
  50.      * 
  51.      * @Date 2018年6月15日 下午1:53:52
  52.      * @Description 利用反射获取get方法 
  53.      * @Fcunction getFiled
  54.      * @param object
  55.      * @param field
  56.      * @return String
  57.      *
  58.      */
  59.     public static String getFiled(Object object, String field) {
  60.         Class<? extends Object> clazz  = object.getClass();
  61.         PropertyDescriptor pd = null;
  62.         Method getMethod = null;
  63.         try {
  64.             pd = new PropertyDescriptor(field, clazz);
  65.             if (null != pd) {
  66.                 // 获取field 属性 的get方法
  67.                 getMethod = pd.getReadMethod();
  68.                 Object invoke = getMethod.invoke(object);
  69.                 if(invoke==null) {
  70.                     invoke = "";
  71.                 }
  72.                 return invoke.toString();
  73.             }
  74.         } catch (Exception e) {
  75.             e.printStackTrace();
  76.         }
  77.         return "";
  78.     }
  80. }

注：合并导org.apache.poi.ss.util.CellRangeAddress包，org.apache.poi.hssf.util.CellRangeAddress包已废弃；

  1. import org.apache.poi.ss.util.CellRangeAddress;

合并addMergedRegion方法四个属性分别是（开始行，结束行，开始列，结束列）

  1. sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 1));

发表评论

表情：
评论列表 （有 0 条评论，1133人围观）

还没有评论，来说两句吧...

相关阅读

    相关 使用POI导出Excel

    > 提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录 前言 一、导入依赖 二、使用步骤 1.编写工具类

    r囧r小猫r囧r小猫/ 0 赞/ 251 阅读

    相关 使用POI导出Excel

    > 这次是参与一个老项目的维护工作，在测试的过程中发现有一个遗留的bug，数据导出时，当列超过256时，则超出的数据无法正常导出显示。查了一些资料才知道原来项目中使用的`jxl

    悠悠悠悠/ 0 赞/ 198 阅读