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
41
42
43
44
45
46
47
48
49
public void test(Sheet sheet)  {
        int numMergedRegions = sheet.getNumMergedRegions();
        for(int z = 0; z < numMergedRegions; z++) {
            CellRangeAddress merge = sheet.getMergedRegion(z);
            //System.out.println("Number: " + z);
            //System.out.println("first column: " + merge.getFirstColumn() + " last column " + merge.getLastColumn());
            //System.out.println("first row: " + merge.getFirstRow() + " last row " + merge.getLastRow());
            //System.out.println("==============");
            int firstCol = merge.getFirstColumn();
            int lastCol = merge.getLastColumn();
            int firstRow = merge.getFirstRow();
            int lastRow = merge.getLastRow();
            Cell cell = null;
            for (int k = firstRow; k <= lastRow; k++) {
                for (int l = firstCol; l <= lastCol; l++) {
                    if (cell == null){//拿到第一个值就是合并单元格的值
                        cell = sheet.getRow(k).getCell(l);
                    }else {
                        Cell cell1 = sheet.getRow(k).getCell(l);
                        switch (cell.getCellType()) {
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) { //判断是否为日期类型
                                    Date date = cell.getDateCellValue();
                                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
                                    cell1.setCellValue(formater.format(date));
                                } else {
                                    DecimalFormat df = new DecimalFormat("####.####");
                                    cell1.setCellValue( df.format(cell.getNumericCellValue()));
                                }
                                break;
                            case STRING:
                                cell1.setCellValue(cell.getStringCellValue());
                                break;
                            case BOOLEAN:
                                cell1.setCellValue(cell.getBooleanCellValue() + "");
                                break;
                            case BLANK: //空值
                                cell1.setCellValue("");
                                break;
                            case ERROR:
                                cell1.setCellValue("非法字符");
                                break;
                        }
                    }
                }
            }
        }
    }