利用POI读取Excel文件并写入MySQL

昨天需要将Excel中的内容读取至MySQL中，我利用POI来实现这一需求，POI可以从这里下载。同时，如果要操作Excel2007，必须还要加入xmlbeans-2.3.0.jar。

 

这是我要读取的Excel表的内容：

注意表中数据存在日期、空格。

 

定义一个Project类：

package readexcel;

public class Project {

    private int proNum; // 项目序号
    private String proID;// 项目编号
    private String proName;// 项目名称
    private String Manager;// 项目负责人
    private String manClass;// 班级
    private String deadline;// 截止日期

    public int getProNum() {
        return proNum;
    }

    public void setProNum(int proNum) {
        this.proNum = proNum;
    }

    public String getProID() {
        return proID;
    }

    public void setProID(String proID) {
        this.proID = proID;
    }

    public String getProName() {
        return proName;
    }

    public void setProName(String proName) {
        this.proName = proName;
    }

    public String getManager() {
        return Manager;
    }

    public void setManager(String manager) {
        Manager = manager;
    }

    public String getManClass() {
        return manClass;
    }

    public void setManClass(String manClass) {
        this.manClass = manClass;
    }

    public String getDeadline() {
        return deadline;
    }

    public void setDeadline(String deadline) {
        this.deadline = deadline;
    }

    public String toString() {
        return "proNum=" + proNum + "  proID=" + proID + "  proName=" + proName + "  Manager=" + Manager + "  manClass="
                + manClass + "  deadline=" + deadline;
    }

}

 

数据库配置信息：

package readexcel;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtil {

    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/test";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    public static final String SQL = "insert into test.project(proNum,proID,proName,Manager,manClass,deadline) values(?,?,?,?,?,?)";

    public static Connection getJDBCConnection() {

        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

}

 

据说97和2000和03版本的Excel文件底层存储结构是一样的。所以我们可以用HSSFWorkbook类来解析并将excel文件封装成对象。EXCEL2007底层结构据说改成xml了，所以其解析封装Excel文件不能用上述HSSFWorkbook类，改用XSSFWorkbook类来封装07版本的Excel文件，其余方法基本类似。

 

利用POI读取Excel97~2003（文件后缀为.xls）：

package readexcel;

import java.io.InputStream;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelReader_xls {

    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;

    /*
     * 根据HSSFCell类型设置数据
     */
    private String getCellFormatValue(HSSFCell cell) {

        String cellValue = "";
        if (cell != null) {
            // 判断当前Cell的类型
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前Cell的是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型，转化为Date格式
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {// 如果是纯数字,取得当前Cell的数值
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
                // 如果当前Cell的Type为String
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前Cell的字符串
                cellValue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    /*
     * 取出Excel表中每一行的各个单元格中的值
     */
    public String[] getRowValues(HSSFRow hssfRow, int colNum) {
        if (hssfRow == null || hssfRow.getLastCellNum() == -1) {
            return null;
        }
        String[] values = new String[colNum];
        for (int cellNum = 0; cellNum < colNum; cellNum++) {
            HSSFCell cell = hssfRow.getCell(cellNum);
            if (cell != null) {
                values[cellNum] = getCellFormatValue(cell);
            }
        }
        return values;
    }

    /*
     * 将Excel表中的数据存入ArrayList中
     */
    public ArrayList<Project> saveToArrayList(InputStream is) {

        ArrayList<Project> projects = new ArrayList<Project>();
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();// 得到Excel表标题栏的总列数
        for (int rowIndex = 1; rowIndex <= rowNum; rowIndex++) {
            row = sheet.getRow(rowIndex);
            String[] values = getRowValues(row, colNum);
            Project project = new Project();
            if (values[0] != null && !values[0].trim().equals("")) {
                project.setProNum((int) Double.parseDouble(values[0].trim()));
            }
            if (values[1] != null && !values[1].trim().equals("")) {
                project.setProID(String.valueOf((int) Double.parseDouble(values[1].trim())));
            }
            if (values[2] != null && !values[2].trim().equals(" ")) {
                project.setProName(values[2]);
            } else {
                project.setProName("null");
            }
            if (values[3] != null && !values[3].trim().equals("")) {
                project.setManager(values[3]);
            } else {
                project.setManager("null");
            }
            if (values[4] != null && !values[4].trim().equals("")) {
                project.setManClass(values[4]);
            } else {
                project.setManClass("null");
            }
            if (values[5] != null && !values[5].trim().equals("")) {
                project.setDeadline(values[5]);
            } else {
                project.setDeadline("0000-00-00");
            }
            System.out.println(project);
            projects.add(project);
        }
        return projects;
    }

}

 

利用POI读取Excel2007（文件后缀为.xlsx）：

package readexcel;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

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;

public class ExcelReader_xlsx {
    private XSSFWorkbook wb;
    private XSSFSheet sheet;
    private XSSFRow row;

    /*
     * 根据HSSFCell类型设置数据
     */
    private String getCellFormatValue(XSSFCell cell) {

        String cellValue = "";
        if (cell != null) {
            // 判断当前Cell的类型
            switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_NUMERIC:
            case XSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前Cell的是否为Date
                if (XSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型，转化为Date格式
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {// 如果是纯数字,取得当前Cell的数值
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
                // 如果当前Cell的Type为String
            case XSSFCell.CELL_TYPE_STRING:
                // 取得当前Cell的字符串
                cellValue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    /*
     * 取出Excel表中每一行的各个单元格中的值
     */
    public String[] getRowValues(XSSFRow xssfRow, int colNum) {
        if (xssfRow == null || xssfRow.getLastCellNum() == -1) {
            return null;
        }
        String[] values = new String[colNum];
        for (int cellNum = 0; cellNum < colNum; cellNum++) {
            XSSFCell cell = xssfRow.getCell(cellNum);
            if (cell != null) {
                values[cellNum] = getCellFormatValue(cell);
            }
        }
        return values;
    }

    /*
     * 将Excel表中的数据存入ArrayList中
     */
    public ArrayList<Project> saveToArrayList() {

        ArrayList<Project> projects = new ArrayList<Project>();
        try {
            wb = new XSSFWorkbook(SaveDataToDB.EXCELPATH_XLSX);
        } catch (Exception e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();// 得到Excel表标题栏的总列数
        for (int rowIndex = 1; rowIndex <= rowNum; rowIndex++) {
            row = sheet.getRow(rowIndex);
            String[] values = getRowValues(row, colNum);
            Project project = new Project();
            if (values[0] != null && !values[0].trim().equals("")) {
                project.setProNum((int) Double.parseDouble(values[0].trim()));
            }
            if (values[1] != null && !values[1].trim().equals("")) {
                project.setProID(String.valueOf((int) Double.parseDouble(values[1].trim())));
            }
            if (values[2] != null && !values[2].trim().equals(" ")) {
                project.setProName(values[2]);
            } else {
                project.setProName("null");
            }
            if (values[3] != null && !values[3].trim().equals("")) {
                project.setManager(values[3]);
            } else {
                project.setManager("null");
            }
            if (values[4] != null && !values[4].trim().equals("")) {
                project.setManClass(values[4]);
            } else {
                project.setManClass("null");
            }
            if (values[5] != null && !values[5].trim().equals("")) {
                project.setDeadline(values[5]);
            } else {
                project.setDeadline("0000-00-00");
            }
            System.out.println(project);
            projects.add(project);
        }
        return projects;
    }
}

 

注意ExcelReader_xls类中getCellFormatValue有如下代码段：

case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前Cell的是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型，转化为Date格式
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(date);
                } else {// 如果是纯数字,取得当前Cell的数值
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }

 

但是在XSSF中，功能类似于HSSFDateUtil的类并不存在，解决办法是解决方法是找出HSSFDateUtil的源文件，修改源代码即可：

package readexcel;

import java.util.Calendar;

import org.apache.poi.ss.usermodel.DateUtil;

public class XSSFDateUtil extends DateUtil {
    protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
        return DateUtil.absoluteDay(cal, use1904windowing);
    }
}

 

读取出来后，写入MySQL：

package readexcel;

import java.io.FileInputStream;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

public class SaveDataToDB {

    public static void insert(Project project) throws SQLException {

        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getJDBCConnection();
            statement = connection.prepareStatement(DBUtil.SQL);
            statement.setInt(1, project.getProNum());
            statement.setString(2, project.getProID());
            statement.setString(3, project.getProName());
            statement.setString(4, project.getManager());
            statement.setString(5, project.getManClass());
            statement.setString(6, project.getDeadline());
            boolean flag = statement.execute();
            if (!flag) {
                System.out.println("Save " + project + " succeed!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }

    }

    public static void saveXLSToDB(String filePath) {

        ArrayList<Project> projects = null;
        try {
            InputStream is = new FileInputStream(filePath);
            ExcelReader_xls excelReader_xls = new ExcelReader_xls();
            projects = excelReader_xls.saveToArrayList(is);
            for (Project project : projects) {
                insert(project);
                // System.out.println(project);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void saveXLSXToDB(String filePath) {

        ArrayList<Project> projects = null;
        try {
            ExcelReader_xlsx excelReader_xlsx = new ExcelReader_xlsx();
            projects = excelReader_xlsx.saveToArrayList(filePath);
            for (Project project : projects) {
                insert(project);
                // System.out.println(project);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

 

测试：

package test;

import java.io.File;

import readexcel.SaveDataToDB;

public class test {

    public static void readFiles(String filePath) {

        File file = new File(filePath);
        String fileName = "";
        String postfix = "";
        if (!file.isDirectory()) {
            System.out.println("fileName=" + file.getName());
        } else if (file.isDirectory()) {
            String[] fileList = file.list();
            for (int index = 0; index < fileList.length; index++) {
                File readFile = new File(filePath + "\\" + fileList[index]);
                if (!readFile.isDirectory()) {
                    fileName = readFile.getName();
                    postfix = fileName.substring(fileName.lastIndexOf(".") + 1);
                    if (postfix.equals("xls")) {
                        SaveDataToDB.saveXLSToDB(readFile.getAbsolutePath());
                    } else if (postfix.equals("xlsx")) {
                        SaveDataToDB.saveXLSXToDB(readFile.getAbsolutePath());
                    }
                } else {
                    readFiles(filePath + "\\" + fileList[index]);
                }
            }
        }
    }

    public static void main(String[] args) {

        readFiles("D:\\ExcelFiles");

    }

}

 

 

注意ExcelReader方法中的getCellFormatValue(HSSFCell cell)方法，因为Excel表中每个单元格中的数据有不同的格式，所以需要对不同格式的数据进行解析以返回对应的数据。