【POI】-XSSFWorkbook实现Excel导出、导入

- 日理万妓 2021-11-29 13:56 2035阅读 0赞

首先依赖jar包：

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.14</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.14</version>
    </dependency>

主要的工具类：

import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.*;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * Excel工具类
     * @author gourd
     */
    @Slf4j
    public class ExportExcelUtil {
    
        /**
         * 导出数据
         * @param rowName  头部列
         * @param dataList 行数据
         * @param excelTitle 导出文件名称
         * @param dataTitle  表格头名称
         * @param request
         * @param response
         */
        public static void export(String[] rowName,List<Object[]> dataList,String excelTitle,String dataTitle,
                                  HttpServletRequest request, HttpServletResponse response) {
            // firefox浏览器
            try {
                if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                    excelTitle = new String(excelTitle.getBytes("UTF-8"), "ISO8859-1");
                } else {
                    // 其他浏览器包括IE浏览器和google浏览器
                    excelTitle = URLEncoder.encode(excelTitle, "UTF-8");
                }
            } catch (UnsupportedEncodingException e) {
                log.error("转换标题失败", e);
            }
            response.setHeader("conent-type", "application/octet-stream");
            response.setContentType("application/octet-stream");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-Disposition", "attachment; filename=" + excelTitle);
    
            if(dataList == null){
                // 设置空集合
                dataList = new ArrayList<>();
            }
            //  try-with-resources 语句，会自动关闭资源
            try (OutputStream outputStream = response.getOutputStream()) {
                // 创建工作簿对象
                XSSFWorkbook workbook = new XSSFWorkbook();
                // 创建工作表
                XSSFSheet sheet = workbook.createSheet(dataTitle);
                // 设置所有单元格高度为 25
                sheet.setDefaultRowHeight((short)500);
                //设置所有单元格宽度
                //sheet.setDefaultColumnWidth((short)10);
    
                // 产生表格标题行
                XSSFRow rowm = sheet.createRow(0);
                XSSFCell cellTiltle = rowm.createCell(0);
                // 获取列头样式对象
                XSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
                // 单元格样式对象
                XSSFCellStyle style = getStyle(workbook);
                // 设置合并单元格（标题）
                sheet.addMergedRegion(new CellRangeAddress(0, 1, 0,
                        (rowName.length - 1)));
                cellTiltle.setCellStyle(columnTopStyle);
                cellTiltle.setCellValue(dataTitle);
    
                // 定义所需列数
                int columnNum = rowName.length;
                // 在索引2的位置创建行(最顶端的行开始的第二行)
                XSSFRow rowRowName = sheet.createRow(2);
                // 将列头设置到sheet的单元格中
                for (int i = 0; i < columnNum; i++) {
                    // 创建列头对应个数的单元格
                    XSSFCell cellRowName = rowRowName.createCell(i);
                    // 设置列头单元格的数据类型
                    cellRowName.setCellType(XSSFCell.CELL_TYPE_STRING);
                    XSSFRichTextString text = new XSSFRichTextString(rowName[i]);
                    // 设置列头单元格的值
                    cellRowName.setCellValue(text);
                    // 设置列头单元格样式
                    cellRowName.setCellStyle(columnTopStyle);
                }
    
                // 将数据设置到sheet对应的单元格中
                for (int i = 0; i < dataList.size(); i++) {
                    // 遍历每个对象
                    Object[] obj = dataList.get(i);
                    // 创建所需的行数
                    XSSFRow row = sheet.createRow(i + 3);
                    for (int j = 0; j < obj.length; j++) {
                        // 设置单元格的数据类型
                        XSSFCell cell = null;
                        cell = row.createCell(j, XSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            // 设置单元格的值
                            cell.setCellValue(obj[j].toString());
                        }
                        // 设置单元格样式
                        cell.setCellStyle(style);
                    }
                }
                // 让列宽随着导出的列长自动适应
                for (int colNum = 0; colNum < columnNum; colNum++) {
                    int columnWidth = sheet.getColumnWidth(colNum) / 256;
                    for (int rowNum = 0; rowNum < sheet.getLastRowNum()+1; rowNum++) {
                        XSSFRow currentRow;
                        // 当前行未被使用过
                        if (sheet.getRow(rowNum) == null) {
                            currentRow = sheet.createRow(rowNum);
                        } else {
                            currentRow = sheet.getRow(rowNum);
                        }
                        if (currentRow.getCell(colNum) != null) {
                            XSSFCell currentCell = currentRow.getCell(colNum);
                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                int length = currentCell.getStringCellValue()
                                        .getBytes().length;
                                if (columnWidth < length) {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    // 列最大宽度为255
                    sheet.setColumnWidth(colNum, (columnWidth > 255 ? 255 : columnWidth)*256);
                }
                workbook.write(outputStream);
            } catch (Exception e) {
                log.error("导出失败", e);
            }
    
        }
    
    
        /**
         * 导入
         * @param file 文件
         * @param request
         */
        public static void read(MultipartFile file, HttpServletRequest request){
            XSSFWorkbook workBook = null;
            try (InputStream inputStream = file.getInputStream()){
                //读取工作簿
                workBook = new XSSFWorkbook(inputStream);
                //读取工作表
                XSSFSheet sheet = workBook.getSheetAt(0);
                //读取行
                XSSFRow row = sheet.getRow(2);
                //读取单元格
                XSSFCell cell = row.getCell(2);
                String value = cell.getStringCellValue();
                // 业务处理 ...
    
            } catch (IOException e) {
                log.error("读取excel失败",e);
            }finally {
                if(workBook != null){
                    //关闭工作簿
                    try {
                        workBook.close();
                    } catch (IOException e) {
                        log.error("关闭工作簿失败",e);
                    }
                }
            }
    
        }
    
    
        /**
         * 列头单元格样式
         * @param workbook
         * @return
         */
        public static XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) {
    
            // 设置字体
            XSSFFont font = workbook.createFont();
            // 设置字体大小
            font.setFontHeightInPoints((short) 11);
            // 字体加粗
            font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
            // 设置字体名字
            XSSFCellStyle style = getXssfCellStyle(workbook, font);
            return style;
    
        }
    
        /**
         * 列数据信息单元格样式
         * @param workbook
         * @return
         */
        public static XSSFCellStyle getStyle(XSSFWorkbook workbook) {
            // 设置字体
            XSSFFont font = workbook.createFont();
            XSSFCellStyle style = getXssfCellStyle(workbook, font);
    
            return style;
    
        }
    
        /**
         * 获取单元格样式
         * @param workbook
         * @param font
         * @return
         */
        private static XSSFCellStyle getXssfCellStyle(XSSFWorkbook workbook, XSSFFont font) {
            // 设置字体名字
            font.setFontName("Courier New");
            // 设置样式;
            XSSFCellStyle style = workbook.createCellStyle();
            // 设置底边框;
            style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
            // 设置左边框;
            style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
            // 设置右边框;
            style.setBorderRight(XSSFCellStyle.BORDER_THIN);
            // 设置顶边框;
            style.setBorderTop(XSSFCellStyle.BORDER_THIN);
            // 在样式用应用设置的字体;
            style.setFont(font);
            // 设置自动换行;
            style.setWrapText(true);
            // 设置水平对齐的样式为居中对齐;
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            // 设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            return style;
        }
    }

使用工具类非常简单：

// 准备数据
    //定义表的内容
    List<Object[]> dataList = new ArrayList<Object[]>();
    // 定义表的列名
    String[] columnNames = { "ID", "姓名", "性别" };
    for (int i = 0; i < 10; i++) {
        Object[] object = new Object[3];
        object[0] = 1;
        object[1] = "gourd";
        object[2] = "男";
        dataList.add(object);
    }
    // 导出Excel文件
    ExportExcelUtil.export(columnNames,dataList,"测试数据.xlsx","用户数据",request,response);

使用easypoi导出导入更方便：[https://blog.csdn.net/HXNLYW/article/details/96871319][https_blog.csdn.net_HXNLYW_article_details_96871319]

[https_blog.csdn.net_HXNLYW_article_details_96871319]: https://blog.csdn.net/HXNLYW/article/details/96871319