Java操作Excel

来源：互联网发布：java trim函数编辑：程序博客网时间：2024/05/21 11:05
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;    }}
0 0