Java poi 批量读取Excel

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

public class ExcelHandler {

    private static final String EXCEL_VERSION_03_LOW_SUFFIX = "xls";

    private static final String EXCEL_VERSION_07_UP_SUFFIX = "xlsx";

    // 读取Excel 数据库字段 起始行
    private static final Integer READ_DATABASE_COLUMN_START_ROW = 0;
    // 读取Excel 数据库字段 起始列
    private static final Integer READ_DATABASE_COLUMN_START_COLUMN = 2;
    // 读取Excel 数据库数据记录内容 起始行
    private static final Integer READ_DATABASE_CONTENT_START_ROW = 2;
    // 读取Excel 数据库数据记录内容 起始列
    private static final Integer READ_DATABASE_CONTENT_START_COLUMN = 2;
    // 读取Excel 数据库删除查询操作 起始列
    private static final Integer READ_DATABASE_DDL_SQL_START_COLUMN = 2;

    private static final String EXCEL_FILE_PATH = "E://test1.xls";

    private static final String DELETE_STR = "delete";

    private static final String INSERT_STR = "insert";

    @Autowired
    private DataBaseDAO dataBaseDAO;

    /**
     *  Excel To DataBase 入口
     * @param filePath
     */
    public  void  excelToDatabase(String filePath){
        try {
            readExcel(filePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public StringBuffer insertSQL(String tableName,String tableColumns){
        StringBuffer sql = new StringBuffer();
        sql.append("insert into ").append(tableName).append(" (").append(tableColumns).append(") values(");
        return sql;
    }

    public  String getTableColumnFromDataBase(String tableName){
        List<JSONObject> tableColumns = dataBaseDAO.getTableColumns(tableName);
        StringBuffer columns = new StringBuffer();
        for(JSONObject column:tableColumns){
            columns.append(column.getString("column_name")).append(",");
        }
        return columns.toString().substring(0,columns.length()-1);
    }

    private String getTableColumn(Sheet sheet){
        StringBuffer columns = new StringBuffer();
        int endColNum = sheet.getRow(READ_DATABASE_COLUMN_START_ROW).getPhysicalNumberOfCells();
        for(int index = READ_DATABASE_COLUMN_START_COLUMN; index <= endColNum;index++){
            Row row = sheet.getRow(READ_DATABASE_COLUMN_START_ROW);
            Cell cell = row.getCell(index);
            String column_name = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
            if(null != column_name && !"".equals(column_name)){
                columns.append(cell.getStringCellValue()+",");
            }
        }
        return columns.toString();
    }

    public  List<Row> readExcel(String filePath) throws IOException {
        if(null == filePath || "".equals(filePath)){
            throw new IOException("Excel 文件路径不可以为空！");
        }
        List<Row> rowRecords = new ArrayList<Row>();
        String excelSuffixName = filePath.substring(filePath.lastIndexOf(".")+1);
        if(EXCEL_VERSION_03_LOW_SUFFIX.equals(excelSuffixName)){
            rowRecords = readOfficeVersion2003LowExcel(filePath);
        }
        else if(EXCEL_VERSION_07_UP_SUFFIX.equals(excelSuffixName)){
            rowRecords = readOfficeVersion2007UpExcel(filePath);
        }
        return rowRecords;
    }

    private  List<Row> readOfficeVersion2003LowExcel(String filePath) throws IOException{
        existExcelFile(filePath);
        try {
            InputStream inputStream = new FileInputStream(filePath);
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            List<HSSFSheet> sheets = getWorkBookSheets(workbook);
            // 遍历工作表
            for(HSSFSheet sheet:sheets){
                List<Row> rowRecords = new ArrayList<Row>();
                int endRowNum = sheet.getLastRowNum();
                rowRecords = getExcelRows(sheet, READ_DATABASE_CONTENT_START_ROW, endRowNum);
                String tableName = getTableName(sheet);
                String columnSql = getTableColumn(sheet);
                Integer emptyColumnKeyPosition = getEmptyColumnKeyPosition(sheet);
                columnSql = columnSql.substring(0,columnSql.length()-1);
                // DDL操作语句打印
                String executeDeleteSQL = getExecuteDeleteSql(sheet);
                if(!"".equals(executeDeleteSQL) && null != executeDeleteSQL){
                    System.out.println(executeDeleteSQL);
                    System.out.println("\n");
                }
                for(Row row:rowRecords){
                    StringBuffer insetSql = insertSQL(tableName,columnSql);
                    int lastCellNum = row.getLastCellNum();
                    readExcelRowRecords(row,READ_DATABASE_CONTENT_START_COLUMN,lastCellNum,insetSql,emptyColumnKeyPosition);
                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private  List<Row> readOfficeVersion2007UpExcel(String filePath) throws IOException{
        existExcelFile(filePath);
        try {
            InputStream inputStream = new FileInputStream(filePath);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            // 获取第一个工作表
            List<XSSFSheet> sheets = getWorkBookSheets(workbook);
            // 遍历工作表
            for(XSSFSheet sheet:sheets){
                List<Row> rowRecords = new ArrayList<Row>();
                int endRowNum = sheet.getLastRowNum();
                rowRecords = getExcelRows(sheet, READ_DATABASE_CONTENT_START_ROW, endRowNum);
                // 获取表名
                String tableName = getTableName(sheet);
                String columnSql = getTableColumn(sheet);
                //boolean isExistEmptyKey = isContainBlankEmptyColumnKey(sheet);
                Integer emptyColumnKeyPosition = getEmptyColumnKeyPosition(sheet);
                columnSql = columnSql.substring(0,columnSql.length()-1);
                // DDL操作语句打印
                String executeDeleteSQL = getExecuteDeleteSql(sheet);
                if(!"".equals(executeDeleteSQL) && null != executeDeleteSQL){
                    System.out.println(executeDeleteSQL);
                    System.out.println("\n");
                }
                for(Row row:rowRecords){
                    StringBuffer insetSql = insertSQL(tableName,columnSql);
                    int lastCellNum = row.getLastCellNum();
                    if(null != row){
                        readExcelRowRecords(row,READ_DATABASE_CONTENT_START_COLUMN,lastCellNum,insetSql,emptyColumnKeyPosition);
                    }
                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    private  List<HSSFSheet> getWorkBookSheets(HSSFWorkbook workbook){
        Integer sheetNum = workbook.getNumberOfSheets();
        List<HSSFSheet> sheets = new ArrayList<HSSFSheet>();
        for(int i = 0; i < sheetNum; i++){
            sheets.add(workbook.getSheetAt(i));
        }
        return sheets;
    }

    private  List<XSSFSheet> getWorkBookSheets(XSSFWorkbook workbook){
        Integer sheetNum = workbook.getNumberOfSheets();
        List<XSSFSheet> sheets = new ArrayList<XSSFSheet>();
        for(int i = 0; i < sheetNum; i++){
            sheets.add(workbook.getSheetAt(i));
        }
        return sheets;
    }

    private String getTableName(Sheet sheet){
        Row row = sheet.getRow(0);
        if(null == row){
            return "";
        }
        return row.getCell(0).getStringCellValue();
    }

    private Integer getEmptyColumnKeyPosition(Sheet sheet){
        Integer result = -1;
        int endColNum = sheet.getRow(READ_DATABASE_COLUMN_START_ROW).getPhysicalNumberOfCells();
        for(Integer index = READ_DATABASE_COLUMN_START_COLUMN; index < endColNum;index++){
            Row row = sheet.getRow(READ_DATABASE_COLUMN_START_ROW);
            Cell cell = row.getCell(index);
            String column_name = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
            if( "".equals(column_name)  || null == column_name){
                result = index;
            }
        }
        return result;
    }

    private String getExecuteDeleteSql(Sheet sheet){
        //
        StringBuffer executeSql = new StringBuffer();
        Row row = sheet.getRow(READ_DATABASE_DDL_SQL_START_COLUMN);
        Cell cell = row.getCell(0);
        String ddlStr = org.apache.commons.lang.StringUtils.trim(cell.getStringCellValue());
        String tableName = getTableName(sheet);
        if(!"".equals(ddlStr) && null != ddlStr){
            // 解析 ddl 操作字符
            if(ddlStr.startsWith("delete")){
                executeSql.append("delete from ").append(tableName);
                if(ddlStr.contains("all")){
                    executeSql.append(";");
                }else if(ddlStr.contains("and")){
                    String columnCode = ddlStr.substring(ddlStr.indexOf("(")+1,ddlStr.indexOf("="));
                    String whereSql  = ddlStr.substring(ddlStr.indexOf("=")+1,ddlStr.indexOf("and"));
                    String whereSql2 = ddlStr.substring(ddlStr.indexOf("and")+4, ddlStr.indexOf("=", ddlStr.indexOf("and")));
                    executeSql.append(" where ").append(columnCode).append(" = ").append("'").
                            append(org.apache.commons.lang.StringUtils.trim(whereSql)).append("'").append(" or ").append(whereSql2).append(" = '").
                            append(org.apache.commons.lang.StringUtils.trim(ddlStr.substring(ddlStr.indexOf("=", ddlStr.indexOf("and"))+1, ddlStr.indexOf(")")))).append("';");
                }else{
                    String columnCode = ddlStr.substring(ddlStr.indexOf("(")+1,ddlStr.indexOf("="));
                    String whereSql  = ddlStr.substring(ddlStr.indexOf("=")+1,ddlStr.indexOf(")"));
                    executeSql.append(" where ").append(columnCode).append(" = ").append("'").append(whereSql).append("';");
                }
            }
        }
        return  executeSql.toString();
    }


    private  String getBuildingIdFromExcel(String filePath){
        String buildingId = "";
        try {
            InputStream inputStream = new FileInputStream(filePath);
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
            // 获取第一个工作表
            HSSFSheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);
            buildingId = cell.getStringCellValue();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return buildingId.substring(buildingId.indexOf("3"));
    }

    private  void existExcelFile(String filePath) throws IOException{
        File file = new File(filePath);
        if(!file.exists()){
            throw new IOException("文件名称为"+file.getName()+" Excel文件不存在！");
        }
    }

    private  List<Row> getExcelRows(Sheet sheet,Integer startRowNum,Integer endRowNum){
        List<Row> result = new ArrayList<Row>();
        for(int index = startRowNum; index <= endRowNum;index++){
            Row row = sheet.getRow(index);
            result.add(row);
        }
        return result;
    }

    private  void readExcelRowRecords(Row row,Integer startCellNum,Integer endCellNum,StringBuffer sql,Integer emptyColumnKeyPosition){

        for(int cellIndex = startCellNum;cellIndex < endCellNum;cellIndex++){
            // 空 键值对，不做处理，直接跳出，继续执行
            if(emptyColumnKeyPosition != -1 && cellIndex == emptyColumnKeyPosition){
                continue;
            }else {
                Cell cell = row.getCell(cellIndex);
                switch (cell.getCellType()){
                    case Cell.CELL_TYPE_STRING:
                        sql.append("'").append(cell.getStringCellValue()).append("',");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        sql.append(cell.getBooleanCellValue()).append(",");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        // 科学计数法处理
                        DecimalFormat decimalFormat = new DecimalFormat("#");
                        sql.append(decimalFormat.format(cell.getNumericCellValue())).append(",");
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        sql.append("'").append(cell.getStringCellValue()).append("',");
                        break;
                    default:
                        break;
                }
            }

        }
        sql.deleteCharAt(sql.length()-1);
        System.out.println(sql+");\n");
    }

}