0%

java操作excel工具

发表于 分类于

文章字数：119,阅读全文大约需要1分钟

依赖

1
2
3
4
5
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;

/**
 * excel操作工具类
 *
 * @author colin.cheng
 * @date 2021-11
 * @since 1.0.0
 */
public class ExcelWriter {

    private XSSFWorkbook workbook;
    private XSSFSheet sheet;
    private String filePath;

    /**
     * 打开或者创建一个excel
     *
     * @param filePath 文件位置
     * @param sheetName excel表格名
     * @throws IOException
     */
    public ExcelWriter(String filePath, String sheetName) throws IOException {
        if(Files.exists(Paths.get(filePath))) {
            try (FileInputStream fileInputStream = new FileInputStream(filePath)){
                workbook = new XSSFWorkbook(fileInputStream);
            }
        } else {
            workbook = new XSSFWorkbook();
        }
        sheet = workbook.getSheet(sheetName);
        if(sheet == null) {
            sheet = workbook.createSheet(sheetName);
        }
        this.filePath = filePath;
    }

    /**
     * 根据行和列的索引写入单元格的数据
     *
     * @param row 行
     * @param column 列
     * @param val 值
     */
    public void write(int row,int column, String val) {
        XSSFRow xssRow = sheet.getRow(row);
        if(xssRow == null) {
            xssRow = sheet.createRow(row);
        }
        XSSFCell cell = xssRow.getCell(column);
        if(cell == null) {
            cell = xssRow.createCell(column);
        }
        if(val != null) {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(val);
        } else {
            cell.setCellType(CellType.BLANK);
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row 行
     * @param column 列
     * @return 值
     */
    public String read(int row,int column){
        XSSFRow xssRow = sheet.getRow(row);
        if(xssRow == null) {
            return null;
        }
        XSSFCell cell = xssRow.getCell(column);
        return cell == null ? null : cell.toString();
    }

    /**
     * 关闭文件，并保存修改内容
     *
     * @throws IOException
     */
    public void saveAndClose() throws IOException {
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }

    /**
     * 关闭文件，不保存修改
     *
     * @throws IOException
     */
    public void close() throws IOException {
        workbook.close();
    }
}