Java tutorial
/* * Copyright 2015-2020 MSUN.com All right reserved. This software is the confidential and proprietary information of * MSUN.com ("Confidential Information"). You shall not disclose such Confidential Information and shall use it only * in accordance with the terms of the license agreement you entered into with MSUN.com. */ package com.fengduo.spark.commons.file.excel; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.Collections; import java.util.Comparator; import java.util.Date; import java.util.List; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 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.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import com.fengduo.spark.commons.core.utils.Reflections; import com.fengduo.spark.commons.file.excel.annotation.ExcelField; import com.google.common.collect.Lists; /** * Excel?XLS?XLSX?? * * @author zxc Jun 4, 2015 10:11:01 PM */ public class ImportExcel { private static Logger log = LoggerFactory.getLogger(ImportExcel.class); /** * */ private Workbook wb; /** * */ private Sheet sheet; /** * ? */ private int headerNum; /** * * * @param path ? * @param headerNum ???=?+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException { this(new File(fileName), headerNum); } /** * * * @param path ? * @param headerNum ???=?+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException { this(file, headerNum, 0); } /** * * * @param path * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(new File(fileName), headerNum, sheetIndex); } /** * * * @param path * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(file.getName(), new FileInputStream(file), headerNum, sheetIndex); } /** * * * @param file * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex); } /** * * * @param path * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException { if (StringUtils.isBlank(fileName)) { throw new RuntimeException("!"); } else if (fileName.toLowerCase().endsWith("xls")) { this.wb = new HSSFWorkbook(is); } else if (fileName.toLowerCase().endsWith("xlsx")) { this.wb = new XSSFWorkbook(is); } else { throw new RuntimeException("??!"); } if (this.wb.getNumberOfSheets() < sheetIndex) { throw new RuntimeException("!"); } this.sheet = this.wb.getSheetAt(sheetIndex); this.headerNum = headerNum; log.debug("Initialize success."); } /** * ? * * @param rownum * @return */ public Row getRow(int rownum) { return this.sheet.getRow(rownum); } /** * ??? * * @return */ public int getDataRowNum() { return headerNum + 1; } /** * ???? * * @return */ public int getLastDataRowNum() { return this.sheet.getLastRowNum() + headerNum; } /** * ??? * * @return */ public int getLastCellNum() { return this.getRow(headerNum).getLastCellNum(); } /** * ?? * * @param row ? * @param column ??? * @return ? */ public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { val = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; } /** * ?? * * @param cls * @param groups */ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException { List<Object[]> annotationList = Lists.newArrayList(); // Get annotation field Field[] fs = cls.getDeclaredFields(); for (Field f : fs) { ExcelField ef = f.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, f }); break; } } } } else { annotationList.add(new Object[] { ef, f }); } } } // Get annotation method Method[] ms = cls.getDeclaredMethods(); for (Method m : ms) { ExcelField ef = m.getAnnotation(ExcelField.class); if (ef != null && (ef.type() == 0 || ef.type() == 2)) { if (groups != null && groups.length > 0) { boolean inGroup = false; for (int g : groups) { if (inGroup) { break; } for (int efg : ef.groups()) { if (g == efg) { inGroup = true; annotationList.add(new Object[] { ef, m }); break; } } } } else { annotationList.add(new Object[] { ef, m }); } } } // Field sorting Collections.sort(annotationList, new Comparator<Object[]>() { public int compare(Object[] o1, Object[] o2) { return new Integer(((ExcelField) o1[0]).sort()).compareTo(new Integer(((ExcelField) o2[0]).sort())); }; }); // log.debug("Import column count:"+annotationList.size()); // Get excel data List<E> dataList = Lists.newArrayList(); for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) { E e = (E) cls.newInstance(); int column = 0; Row row = this.getRow(i); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { Object val = this.getCellValue(row, column++); if (val != null) { ExcelField ef = (ExcelField) os[0]; // If is dict type, get dict value // if (StringUtils.isNotBlank(ef.dictType())) { // val = DictUtils.getDictValue(val.toString(), ef.dictType(), ""); // log.debug("Dictionary type value: ["+i+","+colunm+"] " + val); // } // Get param type and type cast Class<?> valType = Class.class; if (os[1] instanceof Field) { valType = ((Field) os[1]).getType(); } else if (os[1] instanceof Method) { Method method = ((Method) os[1]); if ("get".equals(method.getName().substring(0, 3))) { valType = method.getReturnType(); } else if ("set".equals(method.getName().substring(0, 3))) { valType = ((Method) os[1]).getParameterTypes()[0]; } } // log.debug("Import value type: ["+i+","+column+"] " + valType); try { if (valType == String.class) { String s = String.valueOf(val.toString()); if (StringUtils.endsWith(s, ".0")) { val = StringUtils.substringBefore(s, ".0"); } else { val = String.valueOf(val.toString()); } } else if (valType == Integer.class) { val = Double.valueOf(val.toString()).intValue(); } else if (valType == Long.class) { val = Double.valueOf(val.toString()).longValue(); } else if (valType == Double.class) { val = Double.valueOf(val.toString()); } else if (valType == Float.class) { val = Float.valueOf(val.toString()); } else if (valType == Date.class) { val = DateUtil.getJavaDate((Double) val); } else { if (ef.fieldType() != Class.class) { val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString()); } else { val = Class .forName(this.getClass().getName().replaceAll( this.getClass().getSimpleName(), "fieldtype." + valType.getSimpleName() + "Type")) .getMethod("getValue", String.class).invoke(null, val.toString()); } } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value if (os[1] instanceof Field) { Reflections.invokeSetter(e, ((Field) os[1]).getName(), val); } else if (os[1] instanceof Method) { String mthodName = ((Method) os[1]).getName(); if ("get".equals(mthodName.substring(0, 3))) { mthodName = "set" + StringUtils.substringAfter(mthodName, "get"); } Reflections.invokeMethod(e, mthodName, new Class[] { valType }, new Object[] { val }); } } sb.append(val + ", "); } dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; } // /** // * // */ // public static void main(String[] args) throws Throwable { // // ImportExcel ei = new ImportExcel("target/export.xlsx", 1); // // for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) { // Row row = ei.getRow(i); // for (int j = 0; j < ei.getLastCellNum(); j++) { // Object val = ei.getCellValue(row, j); // System.out.print(val+", "); // } // System.out.print("\n"); // } // } }