Java操作Excel详解

依赖

<!--excel相关依赖-->
<!--07版本的，行数不受限制-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<!--日期格式化工具-->
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.1</version>
</dependency>

写数据的代码案例

package excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

public class ExcelTest {

    static String PATH = "E:/tmpfile/";

    public static void main(String[] args) throws Exception {
// 创建一个工作簿，03版本，最多为65536行。xls后缀名，如果超出65536行会报异常，优点是过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
//        Workbook workbook = new HSSFWorkbook();
        // 创建一个工作簿，07版本，不限行，xlsx后缀名，速度慢，可能会报oom异常
//        Workbook workbook = new XSSFWorkbook();
        // 超级实现类，07版本的加强类，为了解决可能发生的内存溢出，而且写速度快，
        /**
         * 过程中会产生临时文件，需要清理临时文件
         * 默认由100万条记录被保存到内存中，如果超过这个数量，则最前面的数据被写入临时文件
         * 如果想定义内存中数据的数量，可以使用new SXSSFWorkbook(数量);
         */
        Workbook workbook = new SXSSFWorkbook();
        // 创建sheet页
        Sheet sheet = workbook.createSheet("sheet页的名称");
        // 创建行,参数为行数索引从0开始
        Row row = sheet.createRow(0);
        // 创建一个单元格，单数为行的单元格的索引，从0开始
        Cell cell0 = row.createCell(0);
        cell0.setCellValue("第一行的第一个单元格");
        // 保存日期格式的
        Cell cell2 = row.createCell(1);
        CreationHelper creationHelper = workbook.getCreationHelper();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        cell2.setCellValue(new Date());
        cell2.setCellStyle(cellStyle);
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(PATH + "test.xlsx");
            workbook.write(fileOutputStream);
            // 使用SXSSFWorkbook实现类时清掉临时缓存
            ((SXSSFWorkbook)workbook).dispose();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOutputStream != null){
                fileOutputStream.close();
            }
        }
    }
}

读数据代码案例

InputStream inputStream = null;
try {
    inputStream = new FileInputStream("E:/tmpfile/test.xlsx");
    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    // 返回有值的列的数量
    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
    String cellValue = "";
    for (int i = 0; i < physicalNumberOfCells; i++) {
        Cell cell = row.getCell(i);
        int cellType = cell.getCellType();

        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

        switch (cellType) {
            case HSSFCell.CELL_TYPE_STRING:
                System.out.println("字符串=" + cell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                System.out.println("BOOLEAN=" + cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
//                         判读是否是日期
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 日期
                    Date dateCellValue = cell.getDateCellValue();
                    System.out.println("日期=" + new DateTime(dateCellValue).toString("yyyy-MM-dd"));
                } else {
                    // 数字
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    System.out.println("数字=" + cell.getStringCellValue());
                }
            case HSSFCell.CELL_TYPE_FORMULA:
                // 得到公式
                String cellFormula = cell.getCellFormula();
                // 获取值
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String value = evaluate.formatAsString();
                System.out.println("公式=" + value);
        }
    }
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (inputStream != null) {
        inputStream.close();
    }
}

设置样式

对齐方式

/**
     *  设置对齐的样式
     * @param workbook
     * @param row 行
     * @param column 列
     */
    public static void testAlignStyle(Workbook workbook, Row row ,int column){
        Cell cell = row.createCell(column);
        cell.setCellValue("测试");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
        cell.setCellStyle(cellStyle);
    }

设置边框样式

/**
 *  设置单元格边框的样式
 * @param workbook
 * @param row 行
 * @param column 列
 */
public static void testBorder(Workbook workbook, Row row ,int column){
    Cell cell = row.createCell(column);
    cell.setCellValue("测试单元格边框样式");
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框，细的
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色

    cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
    cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色

    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setRightBorderColor(IndexedColors.BLUE.getIndex());

    cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); // 上边边框虚线
    cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());
    cell.setCellStyle(cellStyle);
}

合并单元格

/**
 * 合并单元格
 *
 * @param sheet
 * @param row    行
 * @param column 列
 */
public static void testMergeCell(Sheet sheet, Row row, int column) {
    Cell cell = row.createCell(column);
    cell.setCellValue("测试合并单元格1");

    Cell cell2 = row.createCell(column + 1);
    cell2.setCellValue("测试合并单元格");
    // 如果合并前单个单元格内都有内容，则合并后的内容为索引最小的单元格的内容
    sheet.addMergedRegion(new CellRangeAddress(
            1, // 起始行
            2, // 终止行
            1,  // 起始列
            2));// 终止列
}

设置单元格背景色

/**
 * 背景色
 *
 * @param workbook
 * @param row    行
 * @param column 列
 */
public static void testBgColor(Workbook workbook,Sheet sheet, Row row, int column) {
    Cell cell = row.createCell(column);
    cell.setCellValue("测试合并单元格1");
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell.setCellStyle(cellStyle);
}

设置单元格为文本格式

Workbook workbook = new XSSFWorkbook(); // 创建一个新的Excel工作簿  
        Sheet sheet = workbook.createSheet("Sheet1"); // 创建一个新的工作表  
        // 创建一个样式，将数据格式设置为文本  
        CellStyle textStyle = workbook.createCellStyle();  
        textStyle.setDataFormat((short) 49);  
  
        // 将样式应用于整个工作表的单元格  
        for (int i = 0; i <= 100; i++) { // 假设工作表有100行  
            Row row = sheet.createRow(i);  
            for (int j = 0; j < 10; j++) { // 假设每行有10个单元格  
                Cell cell = row.createCell(j);  
                cell.setCellStyle(textStyle); // 应用样式到单元格  
            }  
        }  
        // 将工作簿写入文件  
        try (FileOutputStream outputStream = new FileOutputStream("output.xlsx")) {  
            workbook.write(outputStream);  
            System.out.println("Excel文件已成功创建！");  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                workbook.close(); // 关闭工作簿  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        } 

如果报错The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook，需要把创建textStyle提到类属性上，只创建一次即可

设置字体

/**
* 字体设置
*
* @param workbook
* @param row    行
* @param column 列
*/
public static void testFont(Workbook workbook,Sheet sheet, Row row, int column) {
   Cell cell = row.createCell(column);
   cell.setCellValue("测试合并单元格1");
   Font font = workbook.createFont();
   font.setFontHeightInPoints((short)24);
   font.setFontName("Courier New"); // 字体名称
   font.setItalic(true); // 是否斜体
   font.setStrikeout(true); // 是否有删除线

   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setFont(font);
   cell.setCellStyle(cellStyle);
}

在原有Excel基础上追加数据

将读取文件的流作为XSSFWorkbook的构造参数即可

static String PATH = "E:/tmpfile/";

public static void main(String[] args) throws Exception {
    InputStream inputStream = new FileInputStream(PATH + "test.xlsx");
    Workbook workbook = new XSSFWorkbook(inputStream);
    // 创建sheet页
    Sheet sheet = workbook.getSheet("sheet页的名称");
    // 创建行,参数为行数索引从0开始
    Row row = sheet.getRow(0);

    Cell cell = row.createCell(2);
    cell.setCellValue("测试追加");
    FileOutputStream fileOutputStream = null;
    try {
        fileOutputStream = new FileOutputStream(PATH + "test.xlsx");
        workbook.write(fileOutputStream);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (fileOutputStream != null) {
            fileOutputStream.close();
        }
    }
}

实现Excel的下载

@RequestMapping("/download")
public void testdownload(HttpServletRequest request, HttpServletResponse response) throws Exception {
    Workbook workbook = new XSSFWorkbook();
    // 创建sheet页
    Sheet sheet = workbook.createSheet("sheet页的名称");
    // 创建行,参数为行数索引从0开始
    Row row = sheet.createRow(0);

    Cell cell = row.createCell(2);
    cell.setCellValue("测试");
    response.setContentType("multipart/form-data"); // 设置contentType
    // 下载文件名,如果包含中文名称，则需要编码
    String filename = URLEncoder.encode("测试下载文件.xlsx", "UTF-8");
    // 文件下载方式，附件下载还是当前浏览器打开attachment为附件下载
    response.setHeader("Content-Disposition",
            "attachment;fileName=" + filename);
    workbook.write(response.getOutputStream());
}