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

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

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

预览 & 功能

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

image.png

image.png

2.便捷实现下拉框

image.png

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操作集成

(本文完)

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×