devbox
凡人多烦事01
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

POI 3.17 导出Excel，基础代码_poi3.17使用commons-lang3

作者：凡人多烦事01 | 2024-03-01 02:07:29

赞

踩

poi3.17使用commons-lang3

工作中经常要用到excel的导出，所以参考其他人的demo写了一个导出excel的基础代码。如果以后再需要用的时候，直接拿来修改下就能直接使用。

参考博文地址：https://blog.csdn.net/phil_jing/article/details/78307819

以下是maven依赖

  1.         <dependency>
  2.             <groupId>org.apache.poi</groupId>
  3.             <artifactId>poi</artifactId>
  4.             <version>3.17</version>
  5.         </dependency>
  6.         <dependency>
  7.             <groupId>org.apache.poi</groupId>
  8.             <artifactId>poi-ooxml</artifactId>
  9.             <version>3.17</version>
  10.         </dependency>

工具包

  1.         <dependency>
  2.             <groupId>org.apache.commons</groupId>
  3.             <artifactId>commons-lang3</artifactId>
  4.             <version>3.9</version>
  5.         </dependency>

以下是导出代码实现

  1. package com.example.demo.utils;
  2.  
  3. import org.apache.commons.lang3.StringUtils;
  4. import org.apache.poi.ss.util.CellRangeAddress;
  5. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  6. import org.apache.commons.lang3.time.FastDateFormat;
  7. import java.util.Collection;
  8. import java.util.Date;
  9. import java.util.Iterator;
  10. import java.util.Map;
  11. import java.util.regex.Matcher;
  12. import java.util.regex.Pattern;
  13.  
  14. import org.apache.poi.ss.usermodel.*;
  15. import org.apache.poi.ss.usermodel.Cell;
  16. import org.apache.poi.ss.usermodel.CellStyle;
  17. import org.apache.poi.ss.usermodel.FillPatternType;
  18. import org.apache.poi.ss.usermodel.Font;
  19. import org.apache.poi.ss.usermodel.HorizontalAlignment;
  20. import org.apache.poi.ss.usermodel.IndexedColors;
  21. import org.apache.poi.ss.usermodel.Row;
  22. import org.apache.poi.ss.usermodel.Sheet;
  23. import org.apache.poi.ss.usermodel.VerticalAlignment;
  24. import org.apache.poi.ss.usermodel.Workbook;
  25.  
  26. public class ExportExcel {
  27.  
  28.     /**
  29.      * excel导出
  30.      * @param sheetName sheet名称
  31.      * @param title 标题
  32.      * @param headers 表头
  33.      * @param columns 字段名称
  34.      * @param dataset 数据集合
  35.      * @param pattern 日期格式
  36.      * @return Workbook
  37.      */
  38.     public static Workbook exportExcel(String sheetName, String title, String[] headers, String[] columns,
  39.                                            Collection<Map<String, Object>> dataset, String pattern){
  41.         // 创建excel工作簿
  42.         Workbook workbook = new XSSFWorkbook();
  43.         // 创建第一个sheet（页），并命名
  44.         Sheet sheet = workbook.createSheet(sheetName);
  45.  
  46.         // -----------------表格标题行样式-----------------
  47.         CellStyle titleStyle = workbook.createCellStyle();
  48.         // 设置这些样式
  49.         titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // 填充颜色
  50.         titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充样式
  51.         titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
  52.         titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
  53.         titleStyle.setBorderBottom(BorderStyle.THIN);
  54.         titleStyle.setBorderLeft(BorderStyle.THIN);
  55.         titleStyle.setBorderRight(BorderStyle.THIN);
  56.         titleStyle.setBorderTop(BorderStyle.THIN);
  57.         // 生成一个字体
  58.         Font font = workbook.createFont();
  59.         font.setColor(IndexedColors.WHITE.getIndex());
  60.         font.setFontHeightInPoints((short) 12);
  61.         font.setBold(true);
  62.         // font.setBoldweight((short)700));
  63.         // 把字体应用到当前的样式
  64.         titleStyle.setFont(font);
  65.  
  66.         // ----------------样式2 内容的背景----------------
  67.         CellStyle style2 = workbook.createCellStyle();
  68.         style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
  69.         style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
  70.         style2.setBorderBottom(BorderStyle.THIN);
  71.         style2.setBorderLeft(BorderStyle.THIN);
  72.         style2.setBorderRight(BorderStyle.THIN);
  73.         style2.setBorderTop(BorderStyle.THIN);
  74.         style2.setAlignment(HorizontalAlignment.CENTER);
  75.         style2.setVerticalAlignment(VerticalAlignment.CENTER);
  76.         // 生成另一个字体
  77.         Font font2 = workbook.createFont();
  78.         font.setBold(true);
  79.         // font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
  80.         // 把字体应用到当前的样式
  81.         style2.setFont(font2);
  82.  
  83.         int rowCount = 0; // 起始行号
  84.         Row row;
  85.         // ------------设置首行标题标题--------------
  86.         if (StringUtils.isNotEmpty(title)){
  87.             // 如果有标题
  88.             row = sheet.createRow(rowCount);
  89.             Cell cell = row.createCell(rowCount);
  90.             cell.setCellValue(title);
  91.             cell.setCellStyle(titleStyle);
  92.             //合并单元格
  93.             CellRangeAddress region = new CellRangeAddress(rowCount, rowCount, 0, headers.length-1);
  94.             sheet.addMergedRegion(region);
  95.             rowCount++;
  96.         }
  97.  
  98.         // ----------------表头------------------
  99.         row = sheet.createRow(rowCount);
  100.         //
  101.         for (int i = 0; i < headers.length; i++) {
  102.             Cell cell = row.createCell(i);
  103.             cell.setCellStyle(titleStyle);
  104.             // 富文本字符串：用于同一单元格内容展示不用样式
  105.             // RichTextString text = new XSSFRichTextString(headers[i]);
  106.             cell.setCellValue(headers[i]);
  107.         }
  108.         rowCount++;
  109.  
  110.         // --------------单元格数据----------------
  111.         if(StringUtils.isEmpty(pattern)) {
  112.             pattern = "yyyy/MM/dd";
  113.         }
  114.         FastDateFormat instance = FastDateFormat.getInstance(pattern);
  115.         // 遍历集合数据，产生数据行
  116.         Iterator<Map<String, Object>> it = dataset.iterator(); // 多个Map集合
  117.         int count = 0;
  118.         while (it.hasNext()) {
  119.             row = sheet.createRow(rowCount);
  120.             Map<String, Object> map = it.next();
  121.             count = headers.length < columns.length ? headers.length : columns.length;
  122.             for (int i = 0; i < count; i++) {
  123.                 Cell cell = row.createCell(i);
  124.                 cell.setCellStyle(style2);
  125.                 try {
  126.                     Object value = map.get(columns[i]);
  127.                     // 判断值的类型后进行强制类型转换
  128.                     String textValue = null;
  129.                     if (value instanceof Date) {
  130.                         Date date = (Date) value;
  131.                         textValue = instance.format(date);
  132.                     }else {
  133.                         // 其它数据类型都当作字符串简单处理
  134.                         if (value != null) {
  135.                             textValue = value.toString();
  136.                         } else {
  137.                             textValue = "";
  138.                         }
  139.                     }
  140.                     // 利用正则表达式判断textValue是否全部由数字组成
  141.                     if (textValue != null) {
  142.                         Pattern p = Pattern.compile("^//d+(//.//d+)?$");
  143.                         Matcher matcher = p.matcher(textValue);
  144.                         if (matcher.matches()) {
  145.                             // 是数字当作double处理
  146.                             cell.setCellValue(Double.parseDouble(textValue));
  147.                         } else {
  148.                             cell.setCellValue(textValue);
  149.                             cell.setCellStyle(style2);
  150.                         }
  151.                     }
  152.                 } catch (SecurityException e) {
  153.                     e.printStackTrace();
  154.                 }
  155.             }
  156.             rowCount++;
  157.         }
  158.         return workbook;
  159.     }
  160. }

控制层调用

  1. package com.example.demo.controller;
  2.  
  3. import com.example.demo.model.Bean;
  4. import com.example.demo.utils.ExportExcel;
  5. import org.apache.poi.ss.usermodel.Workbook;
  6. import org.springframework.util.StringUtils;
  7. import org.springframework.web.bind.annotation.*;
  8.  
  9. import javax.servlet.http.HttpServletResponse;
  10. import java.time.LocalTime;
  11. import java.util.*;
  12.  
  13. @RestController
  14. public class TestController {
  15.  
  16.     private static String EXPORT_XLSX_FILE_SUFFIX = ".xlsx";
  17.     private static String EXPORT_XLS_FILE_SUFFIX = ".xls";
  18.  
  19.     /**
  20.      * 导出
  21.      */
  22.     @RequestMapping(value = "/export")
  23.     public void getExcel1(@RequestParam Map<String, Object> map, HttpServletResponse response) throws Exception {
  24.         LocalTime localTime1 = LocalTime.now();
  25.         System.out.println("开始时间：" + localTime1);
  26.         //查询条件
  27.         System.out.println((String) map.get("name"));
  28.         //
  29.         List<Bean> beanList = new ArrayList<>();
  30.         for (int i = 0; i<10000; i++){
  31.             Bean bean1 = new Bean();
  32.             bean1.setAttribute("属性" + i);
  33.             bean1.setName("字段" + i);
  34.             bean1.setLength(i);
  35.             bean1.setType("类型" + i);
  36.             bean1.setNote("注释" + i);
  37.             beanList.add(bean1);
  38.         }
  39.         List<Map<String,Object>> list = new ArrayList<>();
  40.         for (Bean bean : beanList) {
  41.             Map<String,Object> hashMap = new HashMap<>();
  42.             //
  43.             hashMap.put("attribute",bean.getAttribute());
  44.             hashMap.put("name",bean.getName());
  45.             hashMap.put("length",bean.getLength());
  46.             hashMap.put("type",bean.getType());
  47.             hashMap.put("note",bean.getNote());
  48.             //
  49.             list.add(hashMap);
  50.         }
  51.  
  52.         String sheetName = "sheet1";
  53.         String title1 = "标题"; // 标题
  54.         String[] title2 = {"属性", "字段", "长度","类型","注释"}; // 表头
  55.         String[] columns = {"attribute", "name", "length","type","note"}; // 字段
  56.         String pattern = "yyyy/MM/dd"; // 日期格式
  57.         //
  58.         String excelType;
  59.         if(StringUtils.isEmpty(map.get("excel_type"))) {
  60.             excelType = EXPORT_XLSX_FILE_SUFFIX;
  61.         } else {
  62.             excelType = (String) map.get("excel_type");
  63.         }
  64.         Workbook workbook = ExportExcel.exportExcel(sheetName,title1,title2,columns,list,pattern);
  65.         response.setHeader("Content-type", "application/vnd.ms-excel");
  66.         response.setCharacterEncoding("UTF-8");
  67.         response.setHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + excelType);
  68.         workbook.write(response.getOutputStream());
  69.         workbook.close();
  70.         //
  71.         LocalTime localTime2 = LocalTime.now();
  72.         System.out.println("结束时间：" + localTime2);
  73.     }
  74.  
  75. }

项目启动后，在浏览器地址栏输入：localhost:8080/export?excel_type=.xls&name=小明

导出1w条数据所用时间大概为3-4秒

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/凡人多烦事01/article/detail/170221
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号