Java tutorial
/** * Copyright © 2012-2013 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved. * * Licensed under the Apache License, Version 2.0 (the "License"); */ package me.utils.excel; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Date; import java.util.List; import java.util.Map; import javax.validation.constraints.NotNull; import me.entity.Zbx; import me.utils.BusinessException; import me.utils.DateUtils; 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.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.hibernate.validator.constraints.NotBlank; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.web.multipart.MultipartFile; import org.springside.modules.utils.Reflections; import com.google.common.collect.Lists; import com.google.common.collect.Maps; /** * Excel?XLS?XLSX?? * @author wj * @version 2014-10-15 */ public class ImportExcelme { private static Logger log = LoggerFactory.getLogger(ImportExcelme.class); /** * */ private Workbook wb; /** * */ private Sheet sheet; /** * ? */ private int headerNum; /** * * @param path ? * @param headerNum ???=?+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcelme(String fileName, int headerNum) throws InvalidFormatException, IOException { this(new File(fileName), headerNum); } /** * * @param path ? * @param headerNum ???=?+1 * @throws InvalidFormatException * @throws IOException */ public ImportExcelme(File file, int headerNum) throws InvalidFormatException, IOException { this(file, headerNum, 0); } /** * * @param path * @param headerNum ???=?+1 * @param sheetIndex ? * @throws InvalidFormatException * @throws IOException */ public ImportExcelme(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 ImportExcelme(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 ImportExcelme(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 ImportExcelme(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(); // 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 */ public <E> List<E> getDataList(Class<E> cls, List<Zbx> zbxList) throws Exception { String title = (String) getMergedRegionValue(sheet, 0, 1);//? String tbDw = StringUtils.substringAfter((String) getMergedRegionValue(sheet, 1, 0), "");// Date tbDate = DateUtils .parseDate((StringUtils.substringAfter((String) getMergedRegionValue(sheet, 1, 4), ":")));//5? // Date tbDate = DateUtil.getJavaDate(Double.valueOf(StringUtils.substringAfter((String)getMergedRegionValue(sheet, 1, 4), ":"))) ;//5? Map<String, String> map = Maps.newHashMap(); for (Zbx zbx : zbxList) { map.put(zbx.getPropertyText(), zbx.getPropertyName()); } List<String> propertyNames = Lists.newArrayList(); for (int i = 0; i < this.getLastCellNum(); i++) {//?header ? Row row = this.getRow(this.getHeaderRowNum()); Object val = this.getCellValue(row, i); String propertyName = map.get(val); if (StringUtils.isEmpty(propertyName)) { throw new BusinessException("" + val + "?"); } propertyNames.add(propertyName); } //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 (String propertyName : propertyNames) { Object val = this.getCellValue(row, column++);//string if (val != null) { // Get param type and type cast Class<?> valType = Reflections.getAccessibleField(e, propertyName).getType(); check(e, propertyName, val, i, column); //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); val = DateUtils.parseDate(val.toString()); } else {//? wjmany-to-one etc. } } catch (Exception ex) { log.info("Get cell value [" + i + "," + column + "] error: " + ex.toString()); val = null; } // set entity value Reflections.invokeSetter(e, propertyName, val); } sb.append(val + ", "); } Reflections.invokeSetter(e, "title", title); Reflections.invokeSetter(e, "tbDw", tbDw); Reflections.invokeSetter(e, "tbDate", tbDate); dataList.add(e); log.debug("Read success: [" + i + "] " + sb.toString()); } return dataList; } /** * ?? * @wj * @param e * @param propertyName * @param val * @param i * @param column * @throws BusinessException */ private <E> void check(E e, String propertyName, Object val, int i, int column) throws BusinessException { // NotNull notNullAnno = Reflections.getAccessibleField(e, propertyName).getAnnotation(NotNull.class); // NotBlank notBlankAnno = Reflections.getAccessibleField(e, propertyName).getAnnotation(NotBlank.class); String getMethodName = "get" + StringUtils.capitalize(propertyName); NotNull notNullAnno = Reflections.getAccessibleMethodByName(e, getMethodName).getAnnotation(NotNull.class); NotBlank notBlankAnno = Reflections.getAccessibleMethodByName(e, getMethodName) .getAnnotation(NotBlank.class); if (notNullAnno != null) { if (StringUtils.isBlank(String.valueOf(val.toString()))) { throw new BusinessException(" [" + i + "," + column + "] ?"); } } if (notBlankAnno != null) { if (StringUtils.isBlank(String.valueOf(val.toString()))) { throw new BusinessException(" [" + i + "," + column + "] ?"); } } } private int getHeaderRowNum() { return this.headerNum; } /** * ??? * @param sheet * @param row * @param column * @return */ public Object getMergedRegionValue(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { Row row_ = this.getRow(row); return this.getCellValue(row_, column); } } } return null; } /** * ? * @param sheet * @param row * @param column * @return */ private boolean isMergedRow(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row == firstRow && row == lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } // /** // * // */ // 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"); // } // // } }