Java读取excel-20220528

pom

 <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi.version}</version>
        </dependency>

        <!-- 读取大量excel数据时使用 -->
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>2.1.0</version>
        </dependency>

代码

package com.dxy.demo_05_08.study.excelread;


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.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.Date;

public class ReadExcel {
    public static void main(String[] args) throws Exception {
        long t1 = new Date().getTime();
        excel();
        long t2 = new Date().getTime();

        System.out.println((t2 - t1) / 1000 + "秒");
    }

    public static void excel() throws Exception {
        //
        FileWriter fw = new FileWriter("E:\\other\\text.txt");
        File file = new File("E:\\other\\0526.xlsx");
        if (!file.exists()) {
            throw new Exception("文件不存在!");
        }
        InputStream in = new FileInputStream(file);

        // 读取整个Excel
        XSSFWorkbook sheets = new XSSFWorkbook(in);
        // 获取第一个表单Sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);

        //默认第一行为标题行，i = 0
        XSSFRow titleRow = sheetAt.getRow(0);
        // 循环获取每一行数据
        for (int i = 2; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheetAt.getRow(i);
            String var = null;
            // 读取每一格内容
            StringBuilder sb = new StringBuilder();
            for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                XSSFCell titleCell = titleRow.getCell(index);
                XSSFCell cell = row.getCell(index);


                //数字类型
                if (cell.getCellType().equals(CellType.NUMERIC)) {
                    cell.setCellType(CellType.NUMERIC);
                   System.out.println("数字：" + cell);
                    var = String.valueOf(cell);
                }
                //公式类型
                if (cell.getCellType().equals(CellType.FORMULA)) {
                    cell.setCellType(CellType.STRING);
                    BigDecimal bigDecimal = new BigDecimal(String.valueOf(cell));
                    DecimalFormat decimalFormat = new DecimalFormat("0.0000#");
                    String format = decimalFormat.format(bigDecimal);
                    System.out.println("公式：" + bigDecimal);
                    var = format;
                }

                if(cell.getCellType().equals(CellType.STRING)){
                    cell.setCellType(CellType.STRING);
                    var = String.valueOf(cell);
                }
                cell.setCellType(CellType.STRING);
                if (cell.getStringCellValue().equals("")) {
                    continue;
                }
                //sb.append(var + ",");
                sb.append(var + ",");

            }
            System.out.println(i + "\t" + sb);
            //
            fw.write(String.valueOf(sb + "\n"));
        }
        fw.close();
    }
}