Java-ApachePOI读取Excel模板

Excel模板

需求

将模板中的数据读取出来，当数据库中存在表时，用id更新或插入数据，当表不存在时，建表并插入数据。

进度

目前只是将数据包装好，并未实现sql语句的拼接。封装过程详见demo。

Demo

package excelImport;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 ReadExcel {
    public static void main(String[] args) {
        XSSFWorkbook workbook = ReadExcel.getWorkbook("D:/test.xlsx");
        Map<String, XSSFSheet> sheetMap = ReadExcel.getSheetMap(workbook);
        XSSFSheet currentSheet = sheetMap.get("Sheet1");
        Map<String, Object> rowMap = getRowMap(currentSheet);
        List<XSSFRow> list = getTableInfoList(rowMap, "hdmx(Level=test)");
        Map<String, Object> m = getTableInfoMap(list);
        System.out.println(m.get("tableName"));
        System.out.println(((String[]) m.get("colName"))[2]);
        System.out.println(((String[]) m.get("colTypeArray"))[1]);
        Object[][] oArr = (Object[][]) m.get("dataInfoArray");
        System.out.println(oArr[2][2]);
    }

    /**
     * 根据路径获取workbook
     * 
     * @param excelPath
     * @return
     */
    private static XSSFWorkbook getWorkbook(String excelPath) {
        XSSFWorkbook wb = null;
        try {
            File file = new File(excelPath);
            FileInputStream fis = new FileInputStream(file);
            wb = new XSSFWorkbook(fis);
            fis.close();
        } catch (Exception e) {
        }
        return wb;
    }

    /**
     * 根据workbook获取sheet
     * 
     * @param workbook
     * @return
     */
    private static Map<String, XSSFSheet> getSheetMap(XSSFWorkbook workbook) {
        int sheetNum = workbook.getNumberOfSheets();
        Map<String, XSSFSheet> sheetMap = new HashMap<String, XSSFSheet>();
        for (int i = 0; i < sheetNum; i++) {
            XSSFSheet sheet = workbook.getSheetAt(i);
            sheetMap.put(sheet.getSheetName(), sheet);
        }
        return sheetMap;
    }

    /**
     * 根据sheet获取map
     * 
     * @param sheet
     * @return
     */
    private static Map<String, Object> getRowMap(XSSFSheet sheet) {
        Map<String, Object> resMap = new HashMap<String, Object>();
        List<XSSFRow> rowList = new ArrayList<XSSFRow>();
        int totalRowNum = sheet.getLastRowNum();
        String currentTable = "";
        for (int i = 0; i < totalRowNum; i++) {
            XSSFRow currentRow = sheet.getRow(i);
            if (currentRow != null) {
                String tableName = sheet.getRow(i).getCell(0).toString();
                if (tableName.contains("Level")) {
                    currentTable = tableName;
                }
                if (!currentTable.equals("")) {
                    rowList.add(currentRow);
                    resMap.put(currentTable, rowList);
                }
            } else {
                currentTable = "";
                rowList = new ArrayList<XSSFRow>();
            }
        }
        return resMap;
    }

    /**
     * 根据封装好的map和key获取某一个表的list集合
     * 
     * @param rowMap
     * @param rowMapKey
     * @return
     */
    private static List<XSSFRow> getTableInfoList(Map<String, Object> rowMap,
            String rowMapKey) {
        @SuppressWarnings("unchecked")
        List<XSSFRow> tableInfoList = (List<XSSFRow>) rowMap.get(rowMapKey);
        return tableInfoList;
    }

    /**
     * 根据list集合封装为map数据，其中包含： tableName 表名 String colName 列名 String[]
     * dataInfoArray 待处理数据 Object[][] colTypeArray 列类型 String[]
     * 
     * @param list
     * @return
     */
    private static Map<String, Object> getTableInfoMap(List<XSSFRow> list) {
        Map<String, Object> tableInfoMap = new HashMap<String, Object>();

        String tableName = list.get(0).getCell(0).getRichStringCellValue()
                .toString();
        tableInfoMap.put("tableName", tableName);
        XSSFRow colNameRow = list.get(1);
        String[] colNameArr = getColNameArray(colNameRow);
        tableInfoMap.put("colName", colNameArr);
        int dataNum = list.size() - 3;
        if (dataNum <= 0) {
            return null;
        } else {
            int dataLen = colNameRow.getLastCellNum();
            Object[][] dataArr = getDataArrays(list, dataNum, dataLen);
            tableInfoMap.put("dataInfoArray", dataArr);
            String[] colTypeArray = getColTypeOfSqlServerDatabaseByDataArray(dataArr[dataArr.length - 1]);
            tableInfoMap.put("colTypeArray", colTypeArray);
        }
        return tableInfoMap;
    }

    /**
     * 根据Java类型获取对应数据库类型
     * 
     * @param objects
     * @return
     */
    private static String[] getColTypeOfSqlServerDatabaseByDataArray(
            Object[] objects) {
        int len = objects.length;
        String[] colTypeArr = new String[len];
        for (int i = 0; i < len; i++) {
            Object obj = objects[i];
            if (obj instanceof Number) {
                obj = "number";
            } else if (obj instanceof String) {
                obj = "varchar2";
            } else if (obj instanceof Date) {
                obj = "date";
            } else {
                obj = "";
            }
            colTypeArr[i] = (String) obj;
        }
        return colTypeArr;
    }

    /**
     * 根据列名行获取列名数组
     * 
     * @param colNameRow
     * @return
     */
    private static String[] getColNameArray(XSSFRow colNameRow) {
        int colNum = colNameRow.getLastCellNum();
        String[] colNameArray = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            String colName = colNameRow.getCell(i).getRichStringCellValue()
                    .getString();
            colNameArray[i] = colName;
        }
        return colNameArray;
    }

    /**
     * 根据模板特点，传入某表所有行数据，将待处理数据转换成二维数组形式
     * 
     * @param list
     * @param dataNum
     * @param dataLen
     * @return
     */
    private static Object[][] getDataArrays(List<XSSFRow> list, int dataNum,
            int dataLen) {
        Object[] dataArrays[] = new Object[dataNum][dataLen];
        for (int i = 0; i < dataNum; i++) {
            XSSFRow row = list.get(i + 3);
            Object[] cellArray = getCellArray(row);
            dataArrays[i] = cellArray;
        }
        return dataArrays;
    }

    /**
     * 根据row获取cell的数组数据
     * 
     * @param row
     * @return
     */
    private static Object[] getCellArray(XSSFRow row) {
        Object[] os = new Object[row.getLastCellNum()];
        for (int i = 0; i < row.getLastCellNum(); i++) {
            XSSFCell cell = row.getCell(i);
            Object obj = getJavaTypeValueOfCell(cell);
            os[i] = obj;
        }
        return os;
    }

    /**
     * 根据cell类型获取相应java类型数据
     * 
     * @param cell
     * @return
     */
    private static Object getJavaTypeValueOfCell(XSSFCell cell) {
        int cellType = cell.getCellType();
        Object obj = new Object();
        switch (cellType) {
        case Cell.CELL_TYPE_NUMERIC:
            Double cellDoubleValue = cell.getNumericCellValue();
            if (DateUtil.isCellDateFormatted(cell)) {
                obj = cell.getDateCellValue();
            } else if (cellDoubleValue > 0 && cellDoubleValue < 1) {
                obj = setScale(cellDoubleValue, 2);
            } else {
                obj = Math.round(cellDoubleValue);
            }
            break;
        case Cell.CELL_TYPE_STRING:
            obj = cell.getStringCellValue();
            break;
        default:
            obj = "";
            break;
        }
        return obj;
    }

    /**
     * 设置double数据保留小数点位置
     * 
     * @param d
     * @param scale
     * @return
     */
    public static Double setScale(Double d, int scale) {
        BigDecimal bigDecimal = new BigDecimal(d);
        double bdfScale = bigDecimal.setScale(scale, BigDecimal.ROUND_HALF_UP)
                .doubleValue();
        return bdfScale;
    }
}

运行结果