Java tutorial
/** * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com) * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package cn.bzvs.excel.imports; import cn.bzvs.excel.annotation.ExcelTarget; import cn.bzvs.excel.entity.ImportParams; import cn.bzvs.excel.entity.params.ExcelCollectionParams; import cn.bzvs.excel.entity.params.ExcelImportEntity; import cn.bzvs.excel.entity.result.ExcelImportResult; import cn.bzvs.excel.entity.result.ExcelVerifyHanlderResult; import cn.bzvs.excel.imports.base.ImportBaseService; import cn.bzvs.exception.ExcelImportException; import cn.bzvs.exception.enums.ExcelImportEnum; import cn.bzvs.handler.inter.IExcelModel; import cn.bzvs.util.PoiPublicUtil; import cn.bzvs.util.PoiReflectorUtil; import cn.bzvs.util.PoiValidationUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.POIXMLDocument; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.*; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileOutputStream; import java.io.InputStream; import java.io.PushbackInputStream; import java.lang.reflect.Field; import java.util.*; /** * Excel ? */ @SuppressWarnings({ "rawtypes", "unchecked", "hiding" }) public class ExcelImportServer extends ImportBaseService { private CellValueServer cellValueServer; private boolean verfiyFail = false; /** * ?styler */ private CellStyle errorCellStyle; public ExcelImportServer() { this.cellValueServer = new CellValueServer(); } /*** * ?List? * * @param object * @param param * @param row * @param titlemap * @param targetId * @param pictures * @param params */ private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception { Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object, param.getName()); Object entity = PoiPublicUtil.createObject(param.getType(), targetId); String picId; boolean isUsed = false;// ?? for (int i = row.getFirstCellNum(); i < param.getExcelParams().size(); i++) { Cell cell = row.getCell(i); String titleString = (String) titlemap.get(i); if (param.getExcelParams().containsKey(titleString)) { if (param.getExcelParams().get(titleString).getType() == 2) { picId = row.getRowNum() + "_" + i; saveImage(object, picId, param.getExcelParams(), titleString, pictures, params); } else { saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row); } isUsed = true; } } if (isUsed) { collection.add(entity); } } /** * ?key,????? * * @param cell * @return */ private String getKeyValue(Cell cell) { Object obj = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: obj = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: obj = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_FORMULA: obj = cell.getCellFormula(); break; default: cell.setCellType(Cell.CELL_TYPE_STRING); obj = cell.getStringCellValue(); } return obj == null ? null : obj.toString().trim(); } /** * ?? * * @param excelImportEntity * @param object * @return * @throws Exception */ private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception { String url = ""; if (excelImportEntity.getSaveUrl().equals("upload")) { if (excelImportEntity.getMethods() != null && excelImportEntity.getMethods().size() > 0) { object = getFieldBySomeMethod(excelImportEntity.getMethods(), object); } url = object.getClass().getName().split("\\.")[object.getClass().getName().split("\\.").length - 1]; return excelImportEntity.getSaveUrl() + "/" + url; } return excelImportEntity.getSaveUrl(); } private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params, Map<String, PictureData> pictures) throws Exception { List collection = new ArrayList(); Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>(); List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>(); String targetId = null; if (!Map.class.equals(pojoClass)) { Field fileds[] = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); if (etarget != null) { targetId = etarget.value(); } getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null); } Iterator<Row> rows = sheet.rowIterator(); for (int j = 0; j < params.getTitleRows(); j++) { rows.next(); } Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection); checkIsValidTemplate(titlemap, excelParams, params, excelCollection); Row row = null; Object object = null; String picId; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { row = rows.next(); // ???,?,? // keyIndex ??,?? if (params.getKeyIndex() != null && (row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } } else { object = PoiPublicUtil.createObject(pojoClass, targetId); try { for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) { Cell cell = row.getCell(i); String titleString = (String) titlemap.get(i); if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) { if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) { picId = row.getRowNum() + "_" + i; saveImage(object, picId, excelParams, titleString, pictures, params); } else { saveFieldValue(params, object, cell, excelParams, titleString, row); } } } for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } if (verifyingDataValidity(object, row, params, pojoClass)) { collection.add(object); } } catch (ExcelImportException e) { if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) { throw new ExcelImportException(e.getType(), e); } } } } return collection; } /** * ?? * @param object * @param row * @param params * @param pojoClass * @return */ private boolean verifyingDataValidity(Object object, Row row, ImportParams params, Class<?> pojoClass) { boolean isAdd = true; Cell cell = null; if (params.isNeedVerfiy()) { String errorMsg = PoiValidationUtil.validation(object); if (StringUtils.isNotEmpty(errorMsg)) { cell = row.createCell(row.getLastCellNum()); cell.setCellValue(errorMsg); if (object instanceof IExcelModel) { IExcelModel model = (IExcelModel) object; model.setErrorMsg(errorMsg); } else { isAdd = false; } verfiyFail = true; } } if (params.getVerifyHanlder() != null) { ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object); if (!result.isSuccess()) { if (cell == null) cell = row.createCell(row.getLastCellNum()); cell.setCellValue( (StringUtils.isNoneBlank(cell.getStringCellValue()) ? cell.getStringCellValue() + "," : "") + result.getMsg()); if (object instanceof IExcelModel) { IExcelModel model = (IExcelModel) object; model.setErrorMsg( (StringUtils.isNoneBlank(model.getErrorMsg()) ? model.getErrorMsg() + "," : "") + result.getMsg()); } else { isAdd = false; } verfiyFail = true; } } if (cell != null) cell.setCellStyle(errorCellStyle); return isAdd; } /** * ???? * @param rows * @param params * @param excelCollection * @return */ private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params, List<ExcelCollectionParams> excelCollection) { Map<Integer, String> titlemap = new HashMap<Integer, String>(); Iterator<Cell> cellTitle; String collectionName = null; ExcelCollectionParams collectionParams = null; Row row = null; for (int j = 0; j < params.getHeadRows(); j++) { row = rows.next(); if (row == null) { continue; } cellTitle = row.cellIterator(); while (cellTitle.hasNext()) { Cell cell = cellTitle.next(); String value = getKeyValue(cell); int i = cell.getColumnIndex(); //???? if (StringUtils.isNotEmpty(value)) { if (titlemap.containsKey(i)) { collectionName = titlemap.get(i); collectionParams = getCollectionParams(excelCollection, collectionName); titlemap.put(i, collectionName + "_" + value); } else if (StringUtils.isNotEmpty(collectionName) && collectionParams.getExcelParams().containsKey(collectionName + "_" + value)) { titlemap.put(i, collectionName + "_" + value); } else { collectionName = null; collectionParams = null; } if (StringUtils.isEmpty(collectionName)) { titlemap.put(i, value); } } } } return titlemap; } /** * ????? * @param excelCollection * @param collectionName * @return */ private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection, String collectionName) { for (ExcelCollectionParams excelCollectionParams : excelCollection) { if (collectionName.equals(excelCollectionParams.getExcelName())) { return excelCollectionParams; } } return null; } /** * Excel field Integer,Long,Double,Date,String,Boolean * * @param inputstream * @param pojoClass * @param params * @return * @throws Exception */ public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass, ImportParams params) throws Exception { List<T> result = new ArrayList<T>(); Workbook book = null; boolean isXSSFWorkbook = true; if (!(inputstream.markSupported())) { inputstream = new PushbackInputStream(inputstream, 8); } if (POIFSFileSystem.hasPOIFSHeader(inputstream)) { book = new HSSFWorkbook(inputstream); isXSSFWorkbook = false; } else if (POIXMLDocument.hasOOXMLHeader(inputstream)) { book = new XSSFWorkbook(OPCPackage.open(inputstream)); } createErrorCellStyle(book); Map<String, PictureData> pictures; for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex() + params.getSheetNum(); i++) { if (isXSSFWorkbook) { pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i), (XSSFWorkbook) book); } else { pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i), (HSSFWorkbook) book); } result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures)); } if (params.isNeedSave()) { saveThisExcel(params, pojoClass, isXSSFWorkbook, book); } return new ExcelImportResult(result, verfiyFail, book); } /** * ??? * @param titlemap * @param excelParams * @param params * @param excelCollection */ private void checkIsValidTemplate(Map<Integer, String> titlemap, Map<String, ExcelImportEntity> excelParams, ImportParams params, List<ExcelCollectionParams> excelCollection) { if (params.getImportFields() != null) { for (int i = 0, le = params.getImportFields().length; i < le; i++) { if (!titlemap.containsValue(params.getImportFields()[i])) { throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE); } } } else { Collection<ExcelImportEntity> collection = excelParams.values(); for (ExcelImportEntity excelImportEntity : collection) { if (excelImportEntity.isImportField() && !titlemap.containsValue(excelImportEntity.getName())) { throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE); } } for (int i = 0, le = excelCollection.size(); i < le; i++) { ExcelCollectionParams collectionparams = excelCollection.get(i); collection = collectionparams.getExcelParams().values(); for (ExcelImportEntity excelImportEntity : collection) { if (excelImportEntity.isImportField() && !titlemap .containsValue(collectionparams.getExcelName() + "_" + excelImportEntity.getName())) { throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE); } } } } } /** * ?(?,,?) * * @param params * @param object * @param cell * @param excelParams * @param titleString * @param row * @throws Exception */ private void saveFieldValue(ImportParams params, Object object, Cell cell, Map<String, ExcelImportEntity> excelParams, String titleString, Row row) throws Exception { Object value = cellValueServer.getValue(params.getDataHanlder(), object, cell, excelParams, titleString); if (object instanceof Map) { if (params.getDataHanlder() != null) { params.getDataHanlder().setMapValue((Map) object, titleString, value); } else { ((Map) object).put(titleString, value); } } else { setValues(excelParams.get(titleString), object, value); } } /** * * @param object * @param picId * @param excelParams * @param titleString * @param pictures * @param params * @throws Exception */ private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams, String titleString, Map<String, PictureData> pictures, ImportParams params) throws Exception { if (pictures == null) { return; } PictureData image = pictures.get(picId); if (image == null) { return; } byte[] data = image.getData(); String fileName = "pic" + Math.round(Math.random() * 100000000000L); fileName += "." + PoiPublicUtil.getFileExtendName(data); if (excelParams.get(titleString).getSaveType() == 1) { String path = PoiPublicUtil.getWebRootPath(getSaveUrl(excelParams.get(titleString), object)); File savefile = new File(path); if (!savefile.exists()) { savefile.mkdirs(); } savefile = new File(path + "/" + fileName); FileOutputStream fos = new FileOutputStream(savefile); try { fos.write(data); } finally { IOUtils.closeQuietly(fos); } setValues(excelParams.get(titleString), object, getSaveUrl(excelParams.get(titleString), object) + "/" + fileName); } else { setValues(excelParams.get(titleString), object, data); } } private void createErrorCellStyle(Workbook workbook) { errorCellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); font.setColor(Font.COLOR_RED); errorCellStyle.setFont(font); } }