java：excel文件读写二、poi写xls和xlsx

小徐也要努力鸭

已于 2022-05-06 19:44:20 修改

阅读量2.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： Java 文章标签： java apache 开发语言

于 2022-03-16 14:00:49 首次发布

本文链接：https://blog.youkuaiyun.com/a232884c/article/details/123460426

Java 专栏收录该内容

75 篇文章

订阅专栏

该篇博客介绍了如何使用Apache POI库在Java中创建和写入Excel文件，包括对xls和xlsx两种格式的支持。通过示例代码展示了创建工作簿、设置样式、填充数据以及保存文件的过程。此外，还提供了一个方法用于写入带有表头和数据的Excel文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

java：excel文件读写二、poi写xls和xlsx

1.前言

apache poi官网：https://poi.apache.org/

在讲解组件api时，提到了EXCEL（HSSF，XSSF）中，如何同时兼容写入xls和xlsx文件的使用，地址如下：

https://poi.apache.org/components/spreadsheet/converting.html

在这里插入图片描述
官方指明更新到poi的3.5版本，可以用一种兼容的方式操作xls和xlsx，依赖配置如下：

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.5.4</version>
</parent>

<dependencies>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
	<!--  xls  -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.1</version>
    </dependency>

	<!--  xlsx  -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.1</version>
    </dependency>

</dependencies>

2.使用

package com.xiaoxu.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.lang.NonNull;

import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author xiaoxu
 * @date 2022-03-13 13:26
 * spring_boot:com.xiaoxu.utils.ExcelUtil
 */
public class ExcelUtil {
    private static final String xlsSeparator = ".xls";
    private static final String xlsxSeparator = ".xlsx";
    private static final String separator = "\\";

    public static void createExcel(@NonNull String fileName){
        /*
         *fileName 文件名
         * */
        File deskUrl = FileSystemView.getFileSystemView().getHomeDirectory();
        List<String> splitArr = ExcelUtil.splitFileName(fileName);

        if(splitArr.size()==1){
            fileName = deskUrl+ separator + splitArr.get(0) +xlsSeparator;
        }else if(splitArr.size()==2){
            if(!(xlsxSeparator.equals(splitArr.get(1))||xlsSeparator.equals(splitArr.get(1)))){
                throw new RuntimeException("文件名后缀必须为.xls或者.xlsx");
            }
            StringBuilder sb = new StringBuilder();
            sb.append(deskUrl).append(separator);
            for (String s : splitArr) {
                sb.append(s);
            }
            fileName = sb.toString();
        }else{
            throw new RuntimeException("文件名不正确");
        }

        //例以前为：HSSFWorkbook和XSSFWorkbook
        Workbook wb;

        //创建xls（HSSF）和xlsx（XSSF）文件
        if(fileName.endsWith(".xls")){
            wb=new HSSFWorkbook();
        }else if(fileName.endsWith(".xlsx")){
            wb=new XSSFWorkbook();
        }else{
            throw new RuntimeException("文件后缀必须是xls或xlsx");
        }

        CreationHelper creationHelper = wb.getCreationHelper();
        //例以前为：HSSFSheet和XSSFSheet
        Sheet s = wb.createSheet("我是sheet1");
        //例以前为：HSSFRow和XSSFRow
        Row r;
        //例以前为：HSSFCell和XSSFCell
        Cell cell1;
        Cell cell2;

        //样式创建,以前为：HSSFCellStyle和XSSFCellStyle,以此类推
        CellStyle c1 = wb.createCellStyle();
        CellStyle c2 = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();

        Font f1 =wb.createFont();
        Font f2 =wb.createFont();

        f1.setFontHeightInPoints((short) 14);
        f1.setColor(IndexedColors.SEA_GREEN.getIndex());
        f1.setBold(true);

        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.DARK_RED.getIndex());
        f2.setBold(false);

        c1.setFont(f1);
        c1.setDataFormat(df.getFormat("#,##0.0"));

        c2.setFont(f2);
        c2.setDataFormat(df.getFormat("text"));
        c2.setBorderBottom(BorderStyle.DASH_DOT);

        for (int i=0;i<3;i++){
            r = s.createRow(i);
            for(int j=0;j<10;j+=2){
                cell1 = r.createCell(j);
                cell2 = r.createCell(j+1);
                cell1.setCellStyle(c1);
                cell2.setCellStyle(c2);

                cell1.setCellValue(i+((double)j/10));
                cell2.setCellValue(
                        creationHelper.createRichTextString("你好,"+j)
                );
            }
        }

        if(wb instanceof HSSFWorkbook){
            System.out.println("我是xls文件~");
        }else{
            System.out.println("我是xlsx文件~");
        }

        FileOutputStream fileOutputStream;
        try {
            fileOutputStream = new FileOutputStream(fileName);
            wb.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            wb.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException("文件路径未找到或进程无法访问："+e.getMessage());
        }catch (IOException i){
            throw new RuntimeException("文件写入出错："+i.getMessage());
        }
    }

    public static List<String> splitFileName(String fileName){
        String suffix = null;
        String file;
        List<String> files = new ArrayList<>();
        int i;
        if(fileName!=null){
            i = fileName.lastIndexOf(".");
            if(i!=-1){
                file = fileName.substring(0,i);
                suffix = fileName.substring(i);
            }else{
                file = fileName;
            }
        }else{
            throw new NullPointerException("文件名不能为null");
        }
        files.add(file);
        files.add(suffix);
        return files.stream().filter(Objects::nonNull).collect(Collectors.toList());
    }

    public static void main(String[] args) {
        ExcelUtil.createExcel("3.xlsx");
    }
}

执行结果如下：

我是xlsx文件~
13:59:13.640 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save core properties part
13:59:13.643 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:13.748 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:13.754 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:13.758 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:13.764 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
13:59:13.774 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:13.785 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:13.788 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:13.790 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:13.793 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:13.793 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:13.794 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:13.795 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'

桌面找到文件，打开xlsx文件展示如下：
在这里插入图片描述
同理创建.xls文件：

    public static void main(String[] args) {
//        ExcelUtil.createExcel("3.xlsx");
        ExcelUtil.createExcel("2.xls");
    }

执行结果如下：

12:01:08.345 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - creating new workbook from scratch
12:01:08.400 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getNumSheets=1
12:01:08.409 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - exit create new workbook from scratch
12:01:08.524 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - Sheet createsheet from scratch called
12:01:08.559 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - Sheet createsheet from scratch exit
12:01:08.560 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=21
12:01:08.562 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=22
12:01:08.565 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=22
12:01:08.565 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=23
12:01:08.579 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow 
12:01:08.579 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.584 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.584 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.585 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord 
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
我是xls文件~
12:01:08.624 [main] DEBUG org.apache.poi.hssf.usermodel.HSSFWorkbook - HSSFWorkbook.getBytes()
12:01:08.624 [main] WARN org.apache.poi.POIDocument - DocumentSummaryInformation property set came back as null
12:01:08.624 [main] WARN org.apache.poi.POIDocument - SummaryInformation property set came back as null
12:01:08.630 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - setting bof for sheetnum =0 at pos=1545
12:01:08.633 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - Serializing Workbook with offsets
12:01:08.634 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - Exiting serialize workbook

打开文件，效果一致：
在这里插入图片描述
3 另外的方式

private static final String xlsSeparator = ".xls";
private static final String xlsxSeparator = ".xlsx";
private static final String separator = "\\";

/**
 * @param fileName 写入excel的文件名称
 * @param headers  excel的表头
 * @param data excel的表格内容 ：map的key就是表头的值，然后value就是填入的值
 */
public static void writeToExcel(@NonNull String fileName,List<String> headers,List<Map<String,String>> data){
    fileName = getExcelFileName(fileName,null);
    if(!(fileName.endsWith(xlsSeparator)||fileName.endsWith(xlsxSeparator))){
        throw new RuntimeException("文件名后缀必须为.xls或者.xlsx");
    }
    if(CollectionUtils.isEmpty(headers)){
        throw new IllegalArgumentException("excel的headers不能为空");
    }
    if(data==null){
        throw new NullPointerException("excel的data不能为null");
    }
    Workbook wb=null;
    if(fileName.endsWith(xlsSeparator)){
        wb= new HSSFWorkbook();
    }else if(fileName.endsWith(xlsxSeparator)){
        wb = new XSSFWorkbook();
    }
    String sheetName = WorkbookUtil.createSafeSheetName("我是第一个sheet");
    Sheet sheet = wb.createSheet(sheetName);

    /*
     * 字体样式1
     * */
    Font font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(IndexedColors.BLUE.getIndex());

    //CellStyle1
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFont(font1);

    Row row = sheet.createRow(0);
    headers.forEach(h->{
        row.createCell(headers.indexOf(h)).setCellValue(h);
    });

    int s = data.size();
    int startRowNum=0,endRowNum=0;
    if(s>=1){
        startRowNum = 1;
        endRowNum = s;
    }
    Set<Integer> indexs = new HashSet<>();
    if(startRowNum!=0){
        for (int i = 1; i <=endRowNum; i++) {
            Row row1 = sheet.createRow(i);
            Map<String, String> stringStringMap = data.get(i-1);
            stringStringMap.forEach((key,value)->{
                headers.forEach(h->{
                    if(key.equals(h)){
                        row1.createCell(headers.indexOf(key)).setCellValue(value);
                        indexs.add(headers.indexOf(key));
                    }
                });
            });
        }
    }
    // 全部统一设置列宽
    for (int i = 0; i < headers.size(); i++) {
        sheet.setColumnWidth(i,256*headers.get(i).length()*2);
    }

    //针对每一列数据，有数据的则根据数据自适应宽度
    //在setCellValue后，adjust column width to fit the content
    for (Integer index : indexs) {
        sheet.autoSizeColumn(index);
    }

    try(OutputStream outputStream = new FileOutputStream(fileName)){
        wb.write(outputStream);
        wb.close();
    }catch (IOException i){
        System.out.println(i.getMessage());
    }
}