Excel读

# 一、xls读-03和07

# 1、03

package com.atgui.excelpoi;

public class ExcelReadTest {

    @Test
    public void testRead03() throws Exception{

        InputStream is = new FileInputStream("d:/excel-poi/商品表-03.xls");

        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        // 读取第一行第一列
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);

        // 输出单元内容
        System.out.println(cell.getStringCellValue());

        // 操作结束，关闭文件
        is.close();
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 2、07

@Test
public void testRead07() throws Exception{

    InputStream is = new FileInputStream("d:/excel-poi/商品表-07.xlsx");

    Workbook workbook = new XSSFWorkbook(is);
    
    ......
}

1
2
3
4
5
6
7
8
9

# 二、读取不同类型的数据

# 1、读取不同的数据类型

@Test
public void testCellType() throws Exception {

    InputStream is = new FileInputStream("d:/excel-poi/会员消费商品明细表.xls");
    Workbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);


    // 读取标题所有内容
    Row rowTitle = sheet.getRow(0);
    if (rowTitle != null) {// 行不为空
        // 读取cell
        int cellCount = rowTitle.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null) {

                int cellType = cell.getCellType();
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + "|");
            }
        }
        System.out.println();
    }

    // 读取商品列表数据
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {

        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {// 行不为空

            // 读取cell
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {

                System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) + "】");

                Cell cell = rowData.getCell(cellNum);
                if (cell != null) {

                    int cellType = cell.getCellType();

                    //判断单元格数据类型
                    String cellValue = "";
                    switch (cellType) {
                        case HSSFCell.CELL_TYPE_STRING://字符串
                            System.out.print("【STRING】");
                            cellValue = cell.getStringCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                            System.out.print("【BOOLEAN】");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;

                        case HSSFCell.CELL_TYPE_BLANK://空
                            System.out.print("【BLANK】");
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.print("【NUMERIC】");
                            //cellValue = String.valueOf(cell.getNumericCellValue());

                            if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                                System.out.print("【日期】");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                // 不是日期格式，则防止当数字过长时以科学计数法显示
                                System.out.print("【转换成字符串】");
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;

                        case Cell.CELL_TYPE_ERROR:
                            System.out.print("【数据类型错误】");
                            break;
                    }

                    System.out.println(cellValue);
                }
            }
        }
    }

    is.close();
}

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

# 2、计算公式

@Test
public void testFormula() throws Exception{

    InputStream is = new FileInputStream("d:/excel-poi/计算公式.xls");

    Workbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    // 读取第五行第一列
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    //公式计算器
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    // 输出单元内容
    int cellType = cell.getCellType();
    switch (cellType) {
        case Cell.CELL_TYPE_FORMULA://2

            //得到公式
            String formula = cell.getCellFormula();
            System.out.print(formula);

            CellValue evaluate = formulaEvaluator.evaluate(cell);
            //String cellValue = String.valueOf(evaluate.getNumberValue());
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);

            break;
    }
}

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

#

编辑

上次更新: 2024/06/15, 15:12:25

← Excel写附件-内存分析工具→