// 獲取 Excel 文件輸入流
InputStream is = new BufferedInputStream(new FileInputStream(filePath));
Workbook workbook = WorkbookFactory.create(is);

// 遍歷每個(gè) Sheet
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    String sheetName = sheet.getSheetName();
    System.out.println("開始處理 Sheet：" + sheetName);

    // 準(zhǔn)備寫入的輸出流
    OutputStream os = new BufferedOutputStream(new FileOutputStream(outputDir + "/" + sheetName + ".xlsx"));

    // 設(shè)置寫入的 Sheet 名稱
    SXSSFWorkbook writer = new SXSSFWorkbook(new XSSFWorkbook(), 10000);
    SXSSFSheet outSheet = writer.createSheet(sheetName);

    // 讀取并寫入 Sheet 的標(biāo)題行
    Row titleRow = sheet.getRow(0);
    Row outTitleRow = outSheet.createRow(0);
    for (int i = 0; i < titleRow.getLastCellNum(); i++) {
        outTitleRow.createCell(i).setCellValue(titleRow.getCell(i).getStringCellValue());
    }

    // 逐行讀取并寫入數(shù)據(jù)
    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        Row outRow = outSheet.createRow(i);
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell != null) {
                switch (cell.getCellType()) {
                    case BLANK:
                        outRow.createCell(j, CellType.BLANK);
                        break;
                    case BOOLEAN:
                        outRow.createCell(j, CellType.BOOLEAN).setCellValue(cell.getBooleanCellValue());
                        break;
                    case ERROR:
                        outRow.createCell(j, CellType.ERROR).setCellValue(cell.getErrorCellValue());
                        break;
                    case FORMULA:
                        outRow.createCell(j, CellType.FORMULA).setCellFormula(cell.getCellFormula());
                        break;
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            outRow.createCell(j, CellType.NUMERIC).setCellValue(cell.getDateCellValue());
                        } else {
                            outRow.createCell(j, CellType.NUMERIC).setCellValue(cell.getNumericCellValue());
                        }
                        break;
                    case STRING:
                        outRow.createCell(j, CellType.STRING).setCellValue(cell.getStringCellValue());
                        break;
                    default:
                        outRow.createCell(j, CellType.BLANK);
                        break;
                }
            }
        }

        // 每隔 10000 行進(jìn)行一次緩存寫入
        if (i % 10000 == 0) {
            ((SXSSFSheet) outSheet).flushRows();
        }
    }

    // 最后寫入緩存的數(shù)據(jù)
    writer.write(os);
    os.flush();
    os.close();
    writer.dispose();
    System.out.println("處理 Sheet：" + sheetName + " 完成");
}

// 關(guān)閉輸入流
is.close();

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelImporter {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String DB_USER = "myuser";
    private static final String DB_PASSWORD = "mypassword";
    private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            FileInputStream file = new FileInputStream("myexcel.xlsx");
            Workbook workbook = new XSSFWorkbook(file);
            int numSheets = workbook.getNumberOfSheets();

            for (int i = 0; i < numSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                for (Row row : sheet) {
                    String col1 = null;
                    String col2 = null;
                    int col3 = 0;

                    for (Cell cell : row) {
                        int columnIndex = cell.getColumnIndex();
                        switch (columnIndex) {
                            case 0:
                                col1 = cell.getStringCellValue();
                                break;
                            case 1:
                                col2 = cell.getStringCellValue();
                                break;
                            case 2:
                                col3 = (int) cell.getNumericCellValue();
                                break;
                            default:
                                // Ignore other columns
                                break;
                        }
                    }

                    PreparedStatement statement = conn.prepareStatement(INSERT_SQL);
                    statement.setString(1, col1);
                    statement.setString(2, col2);
                    statement.setInt(3, col3);
                    statement.executeUpdate();
                }
            }

            System.out.println("Import successful");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ExcelImporter {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String DB_USER = "myuser";
    private static final String DB_PASSWORD = "mypassword";
    private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            Workbook workbook = Workbook.getWorkbook(new File("myexcel.xls"));
            int numSheets = workbook.getNumberOfSheets();

            for (int i = 0; i < numSheets; i++) {
                Sheet sheet = workbook.getSheet(i);
                for (int j = 1; j < sheet.getRows(); j++) {
                    String col1 = null;
                    String col2 = null;
                    int col3 = 0;

                    for (int k = 0; k < sheet.getColumns(); k++) {
                        Cell cell = sheet.getCell(k, j);
                        switch (k) {
                            case 0:
                                col1 = cell.getContents();
                                break;
                            case 1:
                                col2 = cell.getContents();
                                break;
                            case 2:
                                col3 = Integer.parseInt(cell.getContents());
                                break;
                            default:
                                // Ignore other columns
                                break;
                        }
                    }

                    PreparedStatement statement = conn.prepareStatement(INSERT_SQL);
                    statement.setString(1, col1);
                    statement.setString(2, col2);
                    statement.setInt(3, col3);
                    statement.executeUpdate();
                }
            }

            System.out.println("Import successful");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Sheet;

import java.util.ArrayList;
import java.util.List;

public class ExcelImporter {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String DB_USER = "myuser";
    private static final String DB_PASSWORD = "mypassword";
    private static final String INSERT_SQL = "INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        List<List<Object>> data = new ArrayList<>();
        EasyExcel.read("myexcel.xlsx", new MyEventListener()).sheet().doRead();
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            PreparedStatement statement = conn.prepareStatement(INSERT_SQL);

            for (List<Object> row : data) {
                statement.setString(1, (String) row.get(0));
                statement.setString(2, (String) row.get(1));
                statement.setInt(3, (Integer) row.get(2));
                statement.addBatch();
            }

            statement.executeBatch();
            System.out.println("Import successful");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    static class MyEventListener extends AnalysisEventListener<Object> {
        private List<Object> row = new ArrayList<>();

        @Override
        public void invoke(Object data, AnalysisContext context) {
            row.add(data);
            if (context.getCurrentRowNum() == 0) {
                // Ignore the header row
                row.clear();
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // Ignore
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // Ignore
        }
    }
}