1、一些常量
package com.dsanjun.poi.constant;
public class Constants {
List<Short> ALL_EXCEL_FORMAT_INDEX = Arrays.asList(new Short[] { 14, 15, 16, 17, 18, 19, 20, 21, 22, 30, 31, 32, 33,
45, 46, 47, 176, 177, 178, 179, 180, 181, 182, 55, 183, 56, 184, 185, 57, 186, 58, 187, 188, 189, 190, 191,
192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208 });
public static String EXCEL_SUFFIX_07 = "xlsx";
public static String EXCEL_SUFFIX_03 = "xls";
public static String GENERAL = "General";
}
2、Excel表头对象
package com.dsanjun.poi.bean;
public final class TitleCell {
private Integer index;
private String name;
private TitleCell(Integer index, String name) {
this.index = index;
this.name = name;
}
public static TitleCell getInstance(Integer index, String name) {
return new TitleCell(index, name);
}
public Integer getIndex() {
return index;
}
public void setIndex(Integer index) {
this.index = index;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Title [index=" + index + ", name=" + name + "]";
}
}
3、Excel表格解析对象
package com.dsanjun.poi.parse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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 com.dsanjun.poi.bean.TitleCell;
import com.dsanjun.poi.constant.Constants;
public class ExcelParser {
private List<TitleCell> titleList;
private Workbook workbook;
private Boolean haseSheetList;
private List<Sheet> sheetList;
public ExcelParser(Workbook workbook, List<TitleCell> titleList, boolean initSheetList) {
this.workbook = workbook;
this.haseSheetList = initSheetList;
this.titleList = titleList;
if (initSheetList) {
initSheetList();
}
}
public ExcelParser(InputStream inp, List<TitleCell> titleList, boolean initSheetList) {
try {
this.workbook = WorkbookFactory.create(inp);
this.haseSheetList = initSheetList;
this.titleList = titleList;
if (initSheetList) {
initSheetList();
}
} catch (EncryptedDocumentException | IOException e) {
e.printStackTrace();
}
}
public Map<Integer, List<Map<String, Object>>> parseExcel() {
Map<Integer, List<Map<String, Object>>> sheetDatas = new HashMap<Integer, List<Map<String, Object>>>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
List<Map<String, Object>> sheetData = parseExcel(i);
if (sheetData != null) {
sheetDatas.put(i, sheetData);
}
}
return sheetDatas;
}
public List<Map<String, Object>> parseExcel(int sheetIndex) {
if (haseSheetList) {
return parseSheet(sheetList.get(sheetIndex));
} else {
return parseSheet(workbook.getSheetAt(sheetIndex));
}
}
protected void initSheetList() {
if (workbook != null) {
sheetList = new ArrayList<Sheet>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
sheetList.add(workbook.getSheetAt(i));
}
}
}
protected List<Map<String, Object>> parseSheet(Sheet sheet) {
List<Map<String, Object>> rowDatas = new ArrayList<Map<String, Object>>();
if (sheet != null) {
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
Map<String, Object> rowData = parseRow(sheet.getRow(i));
rowDatas.add(rowData);
}
}
return rowDatas;
}
protected Map<String, Object> parseRow(Row row) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < titleList.size(); i++) {
map.put(titleList.get(i).getName(), parseCell(row.getCell(i)));
}
return map;
}
public Object parseCell(Cell cell) {
return getCellValue(cell);
}
public Object getCellValue(Cell cell) {
if (cell == null)
return null;
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case FORMULA:
return cell.getCellFormula();
case NUMERIC:
double value = cell.getNumericCellValue();
if (Constants.ALL_EXCEL_FORMAT_INDEX.contains(cell.getCellStyle().getDataFormat())) {
Date date = DateUtil.getJavaDate(value);
return date;
} else {
return cell.getNumericCellValue();
}
case BOOLEAN:
return cell.getBooleanCellValue();
case BLANK:
return null;
case ERROR:
return null;
case _NONE:
return null;
default:
return null;
}
}
public Workbook getWorkbook() {
return workbook;
}
public List<Sheet> getSheetList() {
return sheetList;
}
}
4、工具
package com.dsanjun.poi.parse;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
import com.dsanjun.poi.utils.TitleUtils;
public class ExcelImport {
public static Map<Integer, List<Map<String, Object>>> imports(InputStream inp, String[] titleNameArr,
String[] keyNameArr, boolean initSheetList) {
Map<Integer, List<Map<String, Object>>> map = new ExcelParser(inp, TitleUtils.convertToTitleList(titleNameArr),
initSheetList).parseExcel();
return ImportConvert.convertToListMap(titleNameArr, keyNameArr, map);
}
public static Map<Integer, List<Map<String, Object>>> imports(Workbook workbook, String[] titleNameArr,
String[] keyNameArr, boolean initSheetList) {
Map<Integer, List<Map<String, Object>>> map = new ExcelParser(workbook,
TitleUtils.convertToTitleList(titleNameArr), initSheetList).parseExcel();
return ImportConvert.convertToListMap(titleNameArr, keyNameArr, map);
}
public static List<Map<String, Object>> imports(InputStream inp, String[] titleNameArr, String[] keyNameArr,
boolean initSheetList, int sheetIndex) {
List<Map<String, Object>> map = new ExcelParser(inp, TitleUtils.convertToTitleList(titleNameArr), initSheetList)
.parseExcel(sheetIndex);
return ImportConvert.convertToMapList(titleNameArr, keyNameArr, map);
}
public static List<Map<String, Object>> imports(Workbook workbook, String[] titleNameArr, String[] keyNameArr,
boolean initSheetList, int sheetIndex) {
List<Map<String, Object>> map = new ExcelParser(workbook, TitleUtils.convertToTitleList(titleNameArr),
initSheetList).parseExcel(sheetIndex);
return ImportConvert.convertToMapList(titleNameArr, keyNameArr, map);
}
}
5、转换器
package com.dsanjun.poi.parse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import com.dsanjun.poi.utils.TitleUtils;
public class ImportConvert {
public static List<Map<String, Object>> convertToMapList(String[] titleNameArr, String[] keyNameArr,
List<Map<String, Object>> list) {
return doConvertToMapList(TitleUtils.titleMapFile(titleNameArr, keyNameArr), list);
}
public static Map<Integer, List<Map<String, Object>>> convertToListMap(String[] titleNameArr, String[] keyNameArr,
Map<Integer, List<Map<String, Object>>> map) {
Set<Integer> keySet = map.keySet();
for (Integer key : keySet) {
map.put(key, convertToMapList(titleNameArr, keyNameArr, map.get(key)));
}
return map;
}
private static List<Map<String, Object>> doConvertToMapList(Map<String, String> titleMapFile,
List<Map<String, Object>> list) {
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
for (Map<String, Object> map : list) {
resultList.add(doConvertToMap(titleMapFile, map));
}
return resultList;
}
private static Map<String, Object> doConvertToMap(Map<String, String> titleMapFile, Map<String, Object> map) {
Map<String, Object> resultMap = new HashMap<String, Object>();
Set<String> titleKeySet = titleMapFile.keySet();
for (String title : titleKeySet) {
resultMap.put(titleMapFile.get(title), map.get(title));
}
return resultMap;
}
}
6、表头映射工具
package com.dsanjun.poi.utils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.dsanjun.poi.bean.TitleCell;
public class TitleUtils {
public static Map<String, String> titleMapFile(String[] titleNameArr, String[] nameArr) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < titleNameArr.length; i++) {
map.put(titleNameArr[i], nameArr[i]);
}
return map;
}
public static List<TitleCell> convertToTitleList(String[] titleNameArr) {
List<TitleCell> titleList = new ArrayList<TitleCell>();
for (int i = 0; i < titleNameArr.length; i++) {
titleList.add(TitleCell.getInstance(i, titleNameArr[i]));
}
return titleList;
}
}
7、 表单数据解析对外工具
package com.dsanjun.poi;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
import com.dsanjun.poi.parse.ExcelImport;
public class POIParseExcelUtils {
public static Map<Integer, List<Map<String, Object>>> importExcelToMap(String[] titleNameArr, String[] keyNameArr,
Workbook workbook, boolean initSheetList) {
return ExcelImport.imports(workbook, titleNameArr, keyNameArr, initSheetList);
}
public static Map<Integer, List<Map<String, Object>>> importExcelToMap(String[] titleNameArr, String[] keyNameArr,
InputStream inp, boolean initSheetList) {
return ExcelImport.imports(inp, titleNameArr, keyNameArr, initSheetList);
}
public static List<Map<String, Object>> importExcelToMapList(String[] titleNameArr, String[] keyNameArr,
Workbook workbook, boolean initSheetList, int sheetIndex) {
return ExcelImport.imports(workbook, titleNameArr, keyNameArr, initSheetList, sheetIndex);
}
public static List<Map<String, Object>> importExcelToMapList(String[] titleNameArr, String[] keyNameArr,
InputStream inp, boolean initSheetList, int sheetIndex) {
return ExcelImport.imports(inp, titleNameArr, keyNameArr, initSheetList, sheetIndex);
}
}