【Apache POI】设置单元格字体、颜色、边框、对齐方式、Excel读取导入、解析工具类

朴灿烈づ我的快乐病毒、 2021-09-21 23:28 683阅读 0赞

操作Excel设置单元格样式是比较繁琐的，还有导入导出是设置的Header信息，为了简化这些操实现了如下工具类。工具类具有设置单元格的基本背景颜色、字体、字号、字体颜色、边框等，还实现了导入导出、磁盘读写Excel。

这个工具类来源于我们项目的实践，简化了一些重复性的工作，欢迎尝试，欢迎提出问题。

### 预览 & 功能 ###

1.设置简单的单元格样式。如字体、字号、对齐方式、颜色、边框

![image.png][]

![image.png][image.png 1]

2.便捷实现下拉框

![image.png][image.png 2]

3.提供一组默认属性方便设置行高和字体

4.提供上传文件的读取（读取 Request 流）、下载文件导出（导出到 Response 流）、指定磁盘文件导出、指定磁盘文件读取

5.Excel数据解析为Java List

### 实现 ###

#### 导出一个excel大致的代码 ####

这是使用工具类来完成一个表格的生成到导出。

// 导出赛事
        public HSSFWorkbook downloadTemplate(Long competitionId, HttpServletResponse response) { 
            // work book
            HSSFWorkbook workbook = new HSSFWorkbook();
            String sheetName = "导入模板";
    
            // sheet
            HSSFSheet sheet = workbook.createSheet(sheetName);
    
            // 默认高度 16像素
            sheet.setDefaultRowHeightInPoints(16F);
    
            //设置指定列宽
            sheet.setColumnWidth(0, 14 * 256);
            sheet.setColumnWidth(1, 14 * 256);
            sheet.setColumnWidth(2, 16 * 256);
            sheet.setColumnWidth(3, 16 * 256);
            sheet.setColumnWidth(4, 32 * 256);
            sheet.setColumnWidth(5, 64 * 256);
    	
    	// 单元格样式
            HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
                    .createStyle(workbook)
                    .font("宋体", (short) 11, true)
                    .colorRed()
                    .alignCenter()
                    .backgroundBlue()
                    .build();
    
    	// 单元格样式
            HSSFCellStyle tipStyle = new ExcelUtil.HSSFStyleBuilder()
                    .createStyle(workbook)
                    .font("宋体", (short) 11, true)
                    .horizontalAlignLeft()
                    .verticalAlignCenter()
                    .wrapText()
                    .build();
    
    	// 单元格样式
            HSSFCellStyle titleRedStyle = new ExcelUtil.HSSFStyleBuilder()
                    .createStyle(workbook)
                    .font("宋体", (short)11, true)
                    .colorRed()
                    .alignCenter()
                    .wrapText()
                    .build();
    
    	// 单元格样式
            HSSFCellStyle titleBlackStyle = new ExcelUtil.HSSFStyleBuilder()
                    .createStyle(workbook)
                    .font("宋体", (short)11, true)
                    .colorBlack()
                    .alignCenter()
                    .wrapText()
                    .build();
    
            // 设置单元格合并
            CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 5);
            sheet.addMergedRegion(rowRegion);
    
            // 第一行 row=0
            HSSFRow row = sheet.createRow(0);
    
            Competition competition = adminCompetitionMapper.selectById(competitionId);
    
            row.setHeightInPoints(110);
            HSSFCell cell = row.createCell(0);
            StringBuilder info = new StringBuilder(256);
            info.append(sheetName).append("赛事编号为：<").append(competitionId).append(">\r\n")
                    .append("赛事名称为：").append(competition.getName()).append("\r\n")
                    .append("说明信息：\r\n")
                    .append("1) 请不要将此模板应用用在除(").append(competition.getName()).append(")以为的赛事。\r\n")
                    .append("2) 红色标题的字段必填。“序号”字段不做要求，性别、参数经验、赛事名称可从下拉列表框选取。\r\n")
                    .append("3) 填在此表格中的用户必须已注册在系统中，需保证手机号码正确。\r\n")
                    .append("4) 填写时，行与行之间禁止留有空行，禁止修改标题，不要动其他信息。");
    
            // 设置说明信息
            cell.setCellValue(info.toString());
            cell.setCellStyle(tipStyle);
    
            // 第二行 row=1
            row = sheet.createRow(1);
            row.setHeightInPoints(18F);
    
            // 标题 excelTitles = new String[] {"序号","用户名","手机号","性别（男/女）","有无参赛经验（有/无）","赛事名称"};
            ExcelUtil.cellSetter(row.createCell(0), titleBlackStyle).setCellValue(excelTitles[0]);
            ExcelUtil.cellSetter(row.createCell(1), titleRedStyle).setCellValue(excelTitles[1]);
            ExcelUtil.cellSetter(row.createCell(2), titleRedStyle).setCellValue(excelTitles[2]);
            ExcelUtil.cellSetter(row.createCell(3), titleRedStyle).setCellValue(excelTitles[3]);
            ExcelUtil.cellSetter(row.createCell(4), titleRedStyle).setCellValue(excelTitles[4]);
            ExcelUtil.cellSetter(row.createCell(5), titleRedStyle).setCellValue(excelTitles[5]);
    
            //设置约束，下拉列表
            ExcelUtil.createDataValidation(sheet, new String[]{ "男", "女"}, 2, 1000, 3, 3);
            ExcelUtil.createDataValidation(sheet, new String[]{ "有", "无"}, 2, 1000, 4, 4);
    	
    	// 导出
            ExcelUtil.exportXlsExcel(response, "赛事导入模板", workbook);
        }

以下会单独讲工具类的使用。

#### 单元格样式：字体、字号、对齐方式、颜色、边框 ####

通过工具类可以创建简单的单元格样式，如果想看具体的样式如何设置请看工具类 `HSSFStyleBuilder` 类的具体设置。

设置宋体、11号字体、加粗、黑色字体、文本超出换行、细线边框、蓝色背景、居中对齐。

// 生成style对象
    HSSFCellStyle titleStyle = new ExcelUtil.HSSFStyleBuilder(wb)
                    .font("宋体", (short)11, true)
                    .alignCenter()
                    .colorBlack()
                    .backgroundBlue()
                    .border()
                    .wrapText()
                    .build();
    // 使用样式
    ExcelUtil.cellSetter(row.createCell(0), titleBlackStyle).setCellValue("序号");
    
    // ExcelUtil.cellSetter方法实现如下, 它仅完成设置单元格样式这一步
    public static HSSFCell cellSetter(@NotNull HSSFCell cell, HSSFCellStyle style){ 
        cell.setCellStyle(style);
        return cell;
    }

#### 实现下拉框效果 ####

在需要进行数据验证，限定一部分数据的时候，可以使用下拉框。

设置后的下拉框仅对数据进行验证，默认不选择任何一条数据。

// 通过工具类为sheet的第四列设置只能填入男或女验证的下拉框
    ExcelUtil.createDataValidation(sheet, new String[]{ "男", "女"}, 2, 1000, 3, 3);
    
    //ExcelUtil.createDataValidation 类的实现
    public static void createDataValidation(@NotNull Sheet sheet, @NotNull String[] options,
                                     int firstRow, int lastRow, int firstCol, int lastCol) { 
        // 下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
    
        // CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    
        DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
    
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) { 
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else { 
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

#### 使用默认一些值 ####

通常在导出的excel只需要设置行高，然后所有的列根据内容自适应就行了。

标题字体推荐11、12字号，行高18像素。

正文字体推荐10号字体，行高16像素。

设置示例：

// 整体默认高度，另外，不推荐设置默认列宽
            sheet.setDefaultRowHeightInPoints(16F);
    
            // 设置指定列宽
            sheet.setColumnWidth(0, 14 * 256);
            sheet.setColumnWidth(1, 14 * 256);
    
    	// 字号通过HSSFStyleBuilder设置
            HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder()
                    .createStyle(workbook)
    		// 字体，字号，是否加粗
                    .font("宋体", (short) 11, true)
    		.build();
    
    	// ---------------分割线----------------------
    
    	// 原始方式设置字体样式 style
    	HSSFFont font = workbook.createFont();
    	HSSFCellStyle style = workbook.createCellStyle();
    	// 设置字体
    	font.setFontName(DEFAULT_FONT_NAME);
    	// 设置字号
    	font.setFontHeightInPoints((short)11);
    	// 加粗
    	font.setBold(true);
    	// 设置字体
    	style.setFont(font);

以下是工具类默认常量，也是推荐的行高、列宽、字号的值。

// 以下是一些常用参数，在创建workbook后可以通过一些设置默认参数
    // 默认，正文推荐字体、字号
    public static final String DEFAULT_FONT_NAME = "宋体";
    public static final short DEFAULT_FONT_SIZE = 10;
    
    // 标题推荐字体和行高
    public static final short TITLE_FONT_SIZE = 12;
    public static final float TITLE_ROW_HEIGHT_POINT = 18F;
    
    // 推荐行高列宽
    public static final float DEFAULT_ROW_HEIGHT_POINT = 16F;
    public static final int DEFAULT_COL_WIDTH = 10 * 256;

#### 工作簿导入导出、读取、写入 ####

**excel导入 - 读取request流中的数据**

// 工具类方法，导出从流中读取并生成工作簿Workbook
        public static Workbook readWorkBook(@NotNull InputStream input) { 
            Workbook wb = null;
            try { 
                wb = WorkbookFactory.create(input);
            } catch (InvalidFormatException | IOException e) { 
                e.printStackTrace();
            }
            return wb;
        }
    
        // 使用
        1. 通过MultipartFile或HttptServletRequest对象获取输入流: file.getInputStream() 或 request.getInputStream()
        2. 调用工具类方法

**excel导出 - 输出到response流**

掉用工具类如下方法。

/** * 导出 .xls （excel 2007）格式的excel * * @param response HttpServletResponse * @param fileName excel文件名称 * @param workbook 工作簿 * @throws IOException */
        public static void exportXlsExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
                                          @NotNull HSSFWorkbook workbook) throws IOException { 
            fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
    
            // 设置响应体
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            // 将Excel工作簿数据写入字节输出流
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            // 发送数据
            doExport(os, response);
        }

**读取磁盘Excel、写入磁盘Excel**

工具类方法。

/** * Description：以 “绝对路径+文件名” 读取一个excel *<pre> * win: * ExcelUtil.readWorkBook("D:\\workspace\\测试数据.xls"); * * unix/linux: * ExcelUtil.readWorkBook("/root/workspace/测试数据.xls"); *</pre> * @param abstractPathname 全路径名称 * @return */
    static Workbook readWorkBook(@NotNull String abstractPathname);

#### 解析excel数据 ####

传入一个工作簿，将每个sheet的数据解析出来放到List中。解析出来的均为String类型。

/** * Description：读取workbook的数据 * * @param wb 工作簿 * @return */
        private static List<List<List<String>>> analysisWorkBook(@NotNull Workbook wb) { 
            Sheet sheet = null;
            Row row = null;
            List<List<List<String>>> excelDataList = null;
            // 解析数据
            if (wb != null) { 
                try { 
                    int sheetNumber = wb.getNumberOfSheets();
                    excelDataList = new ArrayList<>(sheetNumber);
                    // 循环页签
                    for (int sheetNum = 0; sheetNum < sheetNumber; sheetNum++) { 
                        // 指定页签的值
                        sheet = wb.getSheetAt(sheetNum);
    
                        int lastRowNum = sheet.getLastRowNum();
    
                        // 定义存放一个页签中所有数据的List, 容量为行总数
                        List<List<String>> sheetList = new ArrayList<>(lastRowNum);
    
                        // 循环行
                        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) { 
                            // 指定行的值
                            row = sheet.getRow(rowNum);
                            if(row == null){ 
                                continue;
                            }
    
                            short lastCellNum = row.getLastCellNum();
    
                            // 定义存放一行数据的List
                            List<String> rowList = new ArrayList<>(lastCellNum);
                            // 循环列
                            for (int cellNum = 0; cellNum < lastCellNum; cellNum++) { 
                                Cell cell = sheet.getRow(rowNum).getCell(cellNum);
                                rowList.add(getStringCellValue(cell));
                            }
                            sheetList.add(rowList);
                        }
                        excelDataList.add(sheetList);
                    }
                } catch (Exception e) { 
                    e.printStackTrace();
                    throw new RuntimeException("解析Excel出错");
                }
            } else { 
                throw new RuntimeException("工作簿 work book 读取为null");
            }
            return excelDataList;
        }

### 工具类源码 ###

这个工具类产生于我们项目的实践，并经过测试使用，目前无问题，若出现问题可评论反馈。

工具类依赖 Apache POI jar包，Web上Excel导入导出依赖Servlet API。

pom文件POI依赖如下：

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

工具类代码

package marchsoft.modules.admin.utils;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import javax.validation.constraints.NotNull;
    import java.io.*;
    import java.net.URLEncoder;
    import java.nio.charset.StandardCharsets;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.regex.Pattern;
    
    /** * Description: excel工具类, 导出格式总为xls（excel 2007） * * @author: liuqichun * @date: 2021/4/15 11:16 */
    @Slf4j
    public class ExcelUtil { 
    
        /** * Description: 为单元格设置style的工具方法，使用如下。 * * <p>示例： * <pre> * ExcelUtil.cellSetter(row.createCell(0),titleStyle).setCellValue("序号"); * </pre> * * @param cell HSSFCell 单元格 * @param style 要设置的样式 * @return */
        public static HSSFCell cellSetter(@NotNull HSSFCell cell, HSSFCellStyle style){ 
            cell.setCellStyle(style);
            return cell;
        }
    
        /** * Description：以 “绝对路径+文件名” 读取一个excel *<pre> * win: * ExcelUtil.readExcelFormDisk("D:\\workspace\\测试数据.xls"); * * unix/linux: * ExcelUtil.readExcelFormDisk("/root/workspace/测试数据.xls"); *</pre> * @param abstractPathname 全路径名称 * @return */
        public static List<List<List<String>>> readExcelFormDisk(@NotNull String abstractPathname) { 
            Workbook wb = readWorkBook(abstractPathname);
            return analysisWorkBook(wb);
        }
    
        /** * 读取一个流中的数据，常用在上传是request中的input流。 * @param input * @return */
        public static List<List<List<String>>> readExcelFormInputStream(@NotNull InputStream input) { 
            Workbook wb = readWorkBook(input);
            return analysisWorkBook(wb);
        }
    
        /** * 读取excel文件 * * @param abstractPathname 文件全路径名称 * @return org.apache.poi.ss.usermodel.Workbook */
        public static Workbook readWorkBook(@NotNull String abstractPathname) { 
            Workbook wb = null;
            File file = new File(abstractPathname);
            if (file.isDirectory() || !file.exists()) { 
                throw new RuntimeException("path 是目录而非文件或文件不存在");
            }
            try { 
                wb = WorkbookFactory.create(file);
            } catch (InvalidFormatException | IOException e) { 
                e.printStackTrace();
            }
            return wb;
        }
    
        /** * Description: 读输入流生成工作簿对象Workbook * * @param input 输入流（通常是request中） * @return org.apache.poi.ss.usermodel.Workbook */
        public static Workbook readWorkBook(@NotNull InputStream input) { 
            Workbook wb = null;
            try { 
                wb = WorkbookFactory.create(input);
            } catch (InvalidFormatException | IOException e) { 
                e.printStackTrace();
            }
            return wb;
        }
    
        /** * 创建excel * * @param workbook 工作簿 * @param abstractPathname 文件全路径名 */
        public static void createExcel(@NotNull Workbook workbook, @NotNull String abstractPathname) { 
            FileOutputStream fileOut = null;
            try { 
                fileOut = new FileOutputStream(abstractPathname);
                workbook.write(fileOut);
            } catch (Exception e) { 
                log.error("Error create excel: {}", e.getMessage());
            } finally { 
                try { 
                    if (fileOut != null) { 
                        fileOut.close();
                    }
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
        }
    
        /** * Description：将工作簿数据写入文件 * * @param workbook 工作簿 * @param file 一个文件 * @throws IOException io异常 */
        public static void writeWorkBookToDisk(Workbook workbook, File file) throws IOException { 
            if (workbook == null || file == null) { 
                throw new RuntimeException("参数为null");
            }
            if (!file.exists()) { 
                try { 
                    file.createNewFile();
                } catch (IOException e) { 
                    log.error("创建file文件失败");
                    throw new RuntimeException("创建file文件失败");
                }
            }
            if (file.isDirectory()) { 
                throw new RuntimeException("file为目录而非文件");
            }
    
            // work book 写出字节数据
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
    
            // Excel 数据 src
            byte[] content = os.toByteArray();
            os.close();
    
            // 输出
            FileOutputStream out = new FileOutputStream(file);
    
            // 缓存传输
            bufferTransfer(content, out);
        }
    
        /** * Description: 判断一个字符串是否为：整数、小数、科学计数小数. 常用来判断手机号码 * * @param str 字符串 * @return boolean * @author: liuqichun * @date: 2021/4/17 14:58 */
        public static boolean isNumeric(String str) { 
            if (null == str || "".equals(str)) { 
                return false;
            }
            String regx = "[+-]*\\d+\\.?\\d*[Ee]*[+-]*\\d+";
            Pattern pattern = Pattern.compile(regx);
            boolean isNumber = pattern.matcher(str).matches();
            if (isNumber) { 
                return isNumber;
            }
            regx = "^[-\\+]?[.\\d]*$";
            pattern = Pattern.compile(regx);
            return pattern.matcher(str).matches();
        }
    
        /** * Description: 为sheet创建下拉列表 * * <pre> * 示例： * * //为sheet设置下拉列表框，范围是第4列从第3行到1001行 * ExcelUtil.createDataValidation(sheet, new String[]{"男", "女"}, 2, 1000, 3, 3); * </pre> * * @param sheet * @param options String array {"选项一", "选项二", "选项三",}; * @param firstRow 第一行 * @param lastRow 最后一行 * @param firstCol 第一列 * @param lastCol 最后一列 * @author: liuqichun * @date: 2021/4/16 10:39 */
        public static void createDataValidation(@NotNull Sheet sheet, @NotNull String[] options,
                                         int firstRow, int lastRow, int firstCol, int lastCol) { 
            // 下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();
    
            // CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    
            DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
    
            //处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation) { 
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else { 
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }
    
        /** * 导出 .xls （excel 2007）格式的excel * * @param response HttpServletResponse * @param fileName excel文件名称 * @param workbook 工作簿 * @throws IOException */
        public static void exportXlsExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
                                          @NotNull HSSFWorkbook workbook) throws IOException { 
            fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
    
            // 设置响应体
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            // 将Excel工作簿数据写入字节输出流
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            // 发送数据
            doExport(os, response);
        }
    
        /** * 导出 .xlsx 格式的excel * * @param response HttpServletResponse * @param fileName excel文件名称 * @param workbook 工作簿 * @throws IOException */
        public static void exportXlsxExcel(@NotNull HttpServletResponse response, @NotNull String fileName,
                                           @NotNull XSSFWorkbook workbook)
                throws IOException { 
    
            fileName = new String(fileName.getBytes("GB2312"), StandardCharsets.ISO_8859_1);
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
    
            // 设置响应体
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String((fileName + ".xlsx").getBytes(), StandardCharsets.UTF_8));
    
            // 将Excel工作簿数据写入字节输出流
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);
            // 发送数据
            doExport(os, response);
        }
    
        // -------------------------------------- private methods ----------------------------------------------------------
        /** * Description：读取workbook的数据 * * @param wb 工作簿 * @return */
        private static List<List<List<String>>> analysisWorkBook(@NotNull Workbook wb) { 
            Sheet sheet = null;
            Row row = null;
            List<List<List<String>>> excelDataList = null;
            // 解析数据
            if (wb != null) { 
                try { 
                    int sheetNumber = wb.getNumberOfSheets();
                    excelDataList = new ArrayList<>(sheetNumber);
                    // 循环页签
                    for (int sheetNum = 0; sheetNum < sheetNumber; sheetNum++) { 
                        // 指定页签的值
                        sheet = wb.getSheetAt(sheetNum);
    
                        int lastRowNum = sheet.getLastRowNum();
    
                        // 定义存放一个页签中所有数据的List, 容量为行总数
                        List<List<String>> sheetList = new ArrayList<>(lastRowNum);
    
                        // 循环行
                        for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) { 
                            // 指定行的值
                            row = sheet.getRow(rowNum);
                            if(row == null){ 
                                continue;
                            }
    
                            short lastCellNum = row.getLastCellNum();
    
                            // 定义存放一行数据的List
                            List<String> rowList = new ArrayList<>(lastCellNum);
                            // 循环列
                            for (int cellNum = 0; cellNum < lastCellNum; cellNum++) { 
                                Cell cell = sheet.getRow(rowNum).getCell(cellNum);
                                rowList.add(getStringCellValue(cell));
                            }
                            sheetList.add(rowList);
                        }
                        excelDataList.add(sheetList);
                    }
                } catch (Exception e) { 
                    e.printStackTrace();
                    throw new RuntimeException("解析Excel出错");
                }
            } else { 
                throw new RuntimeException("工作簿 work book 读取为null");
            }
            return excelDataList;
        }
    
        /** * 导出处理 * * @param output 输出流 * @param response 输出流对象，为 HttpServletResponse 中的 ServletOutputStream * @throws IOException */
        private static void doExport(ByteArrayOutputStream output, HttpServletResponse response) throws IOException { 
            // Excel 所有数据 src
            byte[] content = output.toByteArray();
            output.close();
    
            // 输出
            ServletOutputStream sout = response.getOutputStream();
    
            // 缓存传输
            bufferTransfer(content, sout);
        }
    
        /** * Description: 缓存传输数据 * @param content 传输内存 baty array * @param out 输出流 * @author: liuqichun * @date: 2021/4/18 11:02 */
        private static void bufferTransfer(byte[] content, OutputStream out) { 
            // 传输内容作为输入流
            ByteArrayInputStream in = new ByteArrayInputStream(content);
    
            // 缓存传输
            try (
                    // 输入输出缓冲
                    BufferedInputStream bis = new BufferedInputStream(in);
                    BufferedOutputStream bos = new BufferedOutputStream(out)
            ) { 
                // 缓冲池
                byte[] buff = new byte[2048];
                // 读取字节数量
                int bytesRead;
                // Simple read/write loop.
                while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { 
                    bos.write(buff, 0, bytesRead);
                }
            } catch (Exception e) { 
                log.error("导出xls的Excel出现异常:", e);
            }
        }
    
        /** * Description: 转换单元格的值 * * @param cell * @return String */
        private static String getStringCellValue(Cell cell) { 
            String cellvalue = "";
    
            if (cell == null) { 
                return cellvalue;
            }
    
            switch (cell.getCellTypeEnum()) { 
                case STRING:
                    cellvalue = cell.getStringCellValue();
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) { 
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        Date date = cell.getDateCellValue();
                        cellvalue = sdf.format(date);
                    } else { 
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellvalue = String.valueOf(cell.getBooleanCellValue());
                    break;
                default: cellvalue="";
            }
            return cellvalue;
        }
    
        // ------------------------------- static inner class --------------------------------------------------------------
        /** * Description: 构建HSSFCellStyle工具, 使用这个工具可以轻松构建简单的excel样式而不必反复设置各种参数。 * 使用方法参考下方示例。 * * <p> 示例: * <pre> * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder() * .createStyle(workbook) * .font("宋体", (short) 11, true) * .colorRed() * .alignCenter() * .backgroundBlue() * .build(); * </pre> * @author: liuqichun * @date: 2021/4/17 17:14 */
        public static class HSSFStyleBuilder{ 
            private HSSFCellStyle style;
            private HSSFFont  font;
    
            // 以下是一些常用参数，在创建workbook后可以通过一些设置默认参数
            // 默认，正文推荐字体、字号
            public static final String DEFAULT_FONT_NAME = "宋体";
            public static final short DEFAULT_FONT_SIZE = 10;
    
            // 标题推荐字体和行高
            public static final short TITLE_FONT_SIZE = 12;
            public static final float TITLE_ROW_HEIGHT_POINT = 18F;
    
            // 推荐行高列宽
            public static final float DEFAULT_ROW_HEIGHT_POINT = 16F;
            public static final int DEFAULT_COL_WIDTH = 10 * 256;
    
    
            /** * <p> 示例: * <pre> * // 宋体11号加粗，红色字体，单元格居中，蓝色背景 * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder() * .createStyle(workbook) * .font("宋体", (short) 11, true) * .colorRed() * .alignCenter() * .backgroundBlue() * .build(); * </pre> */
            public HSSFStyleBuilder(){ 
    
            }
    
            /** * <p> 示例: * <pre> * // 宋体11号加粗，红色字体，单元格居中，蓝色背景 * HSSFCellStyle testStyle = new ExcelUtil.HSSFStyleBuilder(workbook) * .font("宋体", (short) 11, true) * .colorRed() * .alignCenter() * .backgroundBlue() * .build(); * </pre> */
            public HSSFStyleBuilder(@NotNull HSSFWorkbook workbook){ 
                style = workbook.createCellStyle();
                font = workbook.createFont();
            }
    
            /** * 创建Style，这个使用builder的第一步。如果创建builder时传入workbook则不用执行此方法。 * * @param workbook 工作簿 * @return HSSFStyleBuilder */
            public  HSSFStyleBuilder createStyle(@NotNull HSSFWorkbook workbook){ 
                style = workbook.createCellStyle();
                font = workbook.createFont();
                return this;
            }
    
            /** * 设置默认字体 * @return HSSFStyleBuilder */
            public HSSFStyleBuilder font(){ 
                preCheck();
                font(null,null,null);
                return this;
            }
    
            /** * 设置字体 * * @param fontName 字体名称 * @param isBold 是否加粗 * @return */
            public HSSFStyleBuilder font(String fontName, Boolean isBold){ 
                preCheck();
                font(fontName, null, isBold);
                return this;
            }
    
            /** * 设置字体 * * @param fontName 字体名称 * @param size 字号 * @param isBold 是否加粗 * @return HSSFStyleBuilder */
            public HSSFStyleBuilder font(String fontName, Short size, Boolean isBold){ 
                preCheck();
                if (fontName != null){ 
                    font.setFontName(fontName);
                }else{ 
                    font.setFontName(DEFAULT_FONT_NAME);
                }
    
                if( size != null ){ 
                    font.setFontHeightInPoints(size);
                }else{ 
                    font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
                }
    
                if(isBold != null){ 
                    font.setBold(isBold);
                }
                return this;
            }
    
            /** * 设置红色字体 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder colorRed(){ 
                preCheck();
                font.setColor(HSSFFont.COLOR_RED);
                return this;
            }
    
            /** * 设置黑色字体（默认字体颜色） * @return */
            public HSSFStyleBuilder colorBlack(){ 
                preCheck();
                font.setColor(HSSFFont.COLOR_NORMAL);
                return this;
            }
    
            /** * 字体居中对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder alignCenter(){ 
                preCheck();
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                style.setAlignment(HorizontalAlignment.CENTER);
                return this;
            }
    
            /** * 字体垂直靠上对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder verticalAlignTop(){ 
                preCheck();
                style.setVerticalAlignment(VerticalAlignment.TOP);
                return this;
            }
    
            /** * 字体垂直居中对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder verticalAlignCenter(){ 
                preCheck();
                style.setVerticalAlignment(VerticalAlignment.CENTER);
                return this;
            }
    
            /** * 字体垂直底部对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder verticalAlignBottom(){ 
                preCheck();
                style.setVerticalAlignment(VerticalAlignment.BOTTOM);
                return this;
            }
    
            /** * 字体水平靠左对齐 * @return HSSFStyleBuilder */
            public HSSFStyleBuilder horizontalAlignLeft(){ 
                preCheck();
                style.setAlignment(HorizontalAlignment.LEFT);
                return this;
            }
    
            /** * 字体水平居中对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder horizontalAlignCenter(){ 
                preCheck();
                style.setAlignment(HorizontalAlignment.CENTER);
                return this;
            }
    
            /** * 字体水平靠右对齐 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder horizontalAlignRight(){ 
                preCheck();
                style.setAlignment(HorizontalAlignment.RIGHT);
                return this;
            }
    
            /** * 红色背景 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder backgroundRed(){ 
                preCheck();
                style.setFillForegroundColor(IndexedColors.RED.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                return this;
            }
    
            /** * 灰色背景 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder backgroundGray(){ 
                preCheck();
                style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                return this;
            }
    
            /** * 绿色背景 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder backgroundGreen(){ 
                preCheck();
                style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                return this;
            }
    
            /** * 蓝色背景 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder backgroundBlue(){ 
                preCheck();
                style.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                return this;
            }
    
            /** * 细线黑色边框 * * @return HSSFStyleBuilder */
            public HSSFStyleBuilder border(){ 
                preCheck();
                //设置下边框的线条粗细（有14种选择，可以根据需要在BorderStyle这个类中选取）
                style.setBorderBottom(BorderStyle.THIN);
                //设置下边框的边框线颜色（颜色和上述的颜色对照表是一样的）
                style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderLeft(BorderStyle.THIN);//左边框
                style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderTop(BorderStyle.THIN); //上边框
                style.setRightBorderColor(IndexedColors.BLACK.getIndex());
                style.setBorderRight(BorderStyle.THIN);//右边框
                style.setTopBorderColor(IndexedColors.BLACK.getIndex());
                return this;
            }
    
            /** * 文本超出自动换行，如果一个单元格写多行也许开启换行功能 * * @return */
            public HSSFStyleBuilder wrapText(){ 
                preCheck();
                style.setWrapText(true);
                return this;
            }
    
            /** * 获取构建的Style * * @return */
            public HSSFCellStyle build(){ 
                preCheck();
                style.setFont(font);
                return style;
            }
    
            /** * 检查是否初始化 */
            private void preCheck(){ 
                if (style == null) { 
                    throw new RuntimeException("请先初始化style，调用create方法");
                }
            }
        }
    }

### 总结 ###

**局限性**

这个工具类操作的大多是HSSFWorkbook(excel 2008, xls), 如果是XSSFWorkbook那就要再写一些方法了，不过目前常见的Excel导入导出完全可以胜任。

**未来，下一个版本**

1.  集成对 XSSFWorkbook (xlsx 格式) 的支持。
2.  增加对workbook、sheet操作集成

（本文完）

[image.png]: /images/20210920/ab9a446bf80e4170bf40251fdbeb4009.png
[image.png 1]: /images/20210920/85df1396e07a4da183d67764804b3b08.png
[image.png 2]: /images/20210920/c75cae8545b448ce9d280e0fa2f51e9b.png