Java操作Excel

package com.wll.excelAll;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.DateUtil;

public class readExcel {
    public static void main(String[] args) {
        // 需要读取的文件
        readExcel.read("lib/test.xlsx");
    }

    public static void read(String path) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            // 同时支持Excel 2003、2007、2010
            File excelFile = new File(path); // 创建文件对象
            FileInputStream is = new FileInputStream(excelFile); // 文件流
            Workbook workbook = WorkbookFactory.create(is); // 支持03 07 10格式
            int sheetCount = workbook.getNumberOfSheets(); // 工作簿sheet 的数量 默认3张// 遍历每个Sheet
            for (int i = 0; i < 1; i++) {
                Connection connection = null;
                connection = readExcel.getCon();// 获取数据库链接
                StringBuffer ssBuffer = new StringBuffer();
                String string = "";
                Sheet sheet = workbook.getSheetAt(i);
                int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数 13
                // 遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    StringBuffer sBuffer = new StringBuffer("insert into test values('");
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数 4
                    // 遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        switch (cellType) {
                            case Cell.CELL_TYPE_STRING: // 文本 字符串型 1
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC: // 数字、日期 数值型 0
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                                } else {
                                    cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: // 布尔型 布尔型 4
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: // 空白 空值 3
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_ERROR: // 错误 错误 5
                                cellValue = "错误";
                                break;
                            case Cell.CELL_TYPE_FORMULA: // 公式型 2
                                cellValue = "公式";
                                break;
                            default:
                                cellValue = "错误";
                        }
                        System.out.print(cellValue + " ");
                        sBuffer.append(cellValue + "','");
                    }
                    System.out.println();
                    // 拼接执行的SQL语句
                    string = sBuffer.toString().substring(0, sBuffer.toString().lastIndexOf(","));
                    string = string + ");";
                    System.out.println("---sql:" + string);
                    Statement st = connection.createStatement();
                    st.execute(string);
                    ssBuffer.append(string);
                }
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getCon() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("---error:驱动加载失败");
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8", "root", "root");
        } catch (SQLException e) {
            System.err.println("---error:获取链接失败");
            e.printStackTrace();
        }
        System.out.println("-----获取链接成功！");
        return connection;
    }
}