Java POI 操作EXCEL合并单元格实现拆分并填充内容

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
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;

public class Test {
    public static void main(String[] args) throws Exception {
        File f = new File("E:"+File.separator+"test.xlsx");
        InputStream in = new FileInputStream(f);
        Workbook wb = WorkbookFactory.create(in);

        // 如果指定sheet名,则取指定sheet中的内容 否则默认指向第1个sheet
        Sheet sheet = wb.getSheetAt(0);
        if (sheet == null)
        {
            throw new IOException("文件sheet不存在");
        }
        //合并单元格数量
        for(int i = sheet.getNumMergedRegions() -1 ; i >= 0; i--) {
            //合并单元格位置(地址)
            CellRangeAddress range = sheet.getMergedRegion(i);
            //拆分单元格
            sheet.removeMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            Cell cfirst = sheet.getRow(firstRow).getCell(firstColumn);
            //填充被拆分单元格的值
            for (int m = firstRow + 1; m <= lastRow; m++) {
                Cell tc = sheet.getRow(m).getCell(firstColumn);
                tc.setCellStyle(cfirst.getCellStyle());
                tc.setCellValue(cfirst.getStringCellValue());
            }
        }

        FileOutputStream fos = new FileOutputStream("E:\test1.xlsx");
        wb.write(fos);
        fos.close();
    }
}