Java tutorial
/** * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com) * <p> * 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 * <p> * http://www.apache.org/licenses/LICENSE-2.0 * <p> * 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.afterturn.easypoi.excel.export.base; import cn.afterturn.easypoi.cache.ImageCache; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity; import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants; import cn.afterturn.easypoi.excel.entity.vo.PoiBaseConstants; import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler; import cn.afterturn.easypoi.exception.excel.ExcelExportException; import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum; import cn.afterturn.easypoi.handler.inter.IExcelExportServer; import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil; import cn.afterturn.easypoi.util.PoiMergeCellUtil; import cn.afterturn.easypoi.util.PoiPublicUtil; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.builder.ReflectionToStringBuilder; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import java.text.DecimalFormat; import java.util.*; /** * ??POI?? * * @author JueYue 2014617 ?6:15:13 */ @SuppressWarnings("unchecked") public abstract class BaseExportService extends ExportCommonService { private int currentIndex = 0; protected ExcelType type = ExcelType.HSSF; private Map<Integer, Double> statistics = new HashMap<Integer, Double>(); private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); protected IExcelExportStyler excelExportStyler; /** * ? Cells */ public int[] createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight, int cellNum) { try { ExcelExportEntity entity; Row row = sheet.getRow(index) == null ? sheet.createRow(index) : sheet.getRow(index); if (rowHeight != -1) { row.setHeight(rowHeight); } int maxHeight = 1, listMaxHeight = 1; // ???? int margeCellNum = cellNum; int indexKey = createIndexCell(row, index, excelParams.get(0)); cellNum += indexKey; for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); if (entity.getList() != null) { Collection<?> list = getListCellValue(entity, t); int listIndex = 0, tmpListHeight = 0; if (list != null && list.size() > 0) { int tempCellNum = 0; for (Object obj : list) { int[] temp = createCells(patriarch, index + listIndex, obj, entity.getList(), sheet, workbook, rowHeight, cellNum); tempCellNum = temp[1]; tmpListHeight += temp[0]; listIndex++; } cellNum = tempCellNum; listMaxHeight = Math.max(listMaxHeight, tmpListHeight); } } else { Object value = getCellValue(entity, t); if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) { createStringCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) { createDoubleCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); } else { createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t); } if (entity.isHyperlink()) { row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value)); } } } maxHeight += listMaxHeight - 1; if (indexKey == 1 && excelParams.get(1).isNeedMerge()) { excelParams.get(0).setNeedMerge(true); } for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); if (entity.getList() != null) { margeCellNum += entity.getList().size(); } else if (entity.isNeedMerge() && maxHeight > 1) { for (int i = index + 1; i < index + maxHeight; i++) { if (sheet.getRow(i) == null) { sheet.createRow(i); } sheet.getRow(i).createCell(margeCellNum); sheet.getRow(i).getCell(margeCellNum).setCellStyle(getStyles(false, entity)); } PoiMergeCellUtil.addMergedRegion(sheet, index, index + maxHeight - 1, margeCellNum, margeCellNum); margeCellNum++; } } return new int[] { maxHeight, cellNum }; } catch (Exception e) { LOGGER.error("excel cell export error ,data is :{}", ReflectionToStringBuilder.toString(t)); LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e); } } /** * Cell */ public void createImageCell(Drawing patriarch, ExcelExportEntity entity, Row row, int i, String imagePath, Object obj) throws Exception { Cell cell = row.createCell(i); byte[] value = null; if (entity.getExportImageType() != 1) { value = (byte[]) (entity.getMethods() != null ? getFieldBySomeMethod(entity.getMethods(), obj) : entity.getMethod().invoke(obj, new Object[] {})); } createImageCell(cell, 50 * entity.getHeight(), entity.getExportImageType() == 1 ? imagePath : null, value); } /** * Cell */ public void createImageCell(Cell cell, double height, String imagePath, byte[] data) throws Exception { if (height > cell.getRow().getHeight()) { cell.getRow().setHeight((short) height); } ClientAnchor anchor; if (type.equals(ExcelType.HSSF)) { anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1); } else { anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1); } if (StringUtils.isNotEmpty(imagePath)) { data = ImageCache.getImage(imagePath); } if (data != null) { PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, getImageType(data))); } } /** * Cell */ public void createImageCell(Cell cell, double height, int rowspan, int colspan, String imagePath, byte[] data) throws Exception { if (height > cell.getRow().getHeight()) { cell.getRow().setHeight((short) height); } ClientAnchor anchor; if (type.equals(ExcelType.HSSF)) { anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan); } else { anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + colspan), cell.getRow().getRowNum() + rowspan); } if (StringUtils.isNotEmpty(imagePath)) { data = ImageCache.getImage(imagePath); } if (data != null) { PoiExcelGraphDataUtil.getDrawingPatriarch(cell.getSheet()).createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, getImageType(data))); } } private int createIndexCell(Row row, int index, ExcelExportEntity excelExportEntity) { if (excelExportEntity.getFormat() != null && excelExportEntity.getFormat().equals(PoiBaseConstants.IS_ADD_INDEX)) { createStringCell(row, 0, currentIndex + "", index % 2 == 0 ? getStyles(false, null) : getStyles(true, null), null); currentIndex = currentIndex + 1; return 1; } return 0; } /** * List??Cells */ public void createListCells(Drawing patriarch, int index, int cellNum, Object obj, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception { ExcelExportEntity entity; Row row; if (sheet.getRow(index) == null) { row = sheet.createRow(index); if (rowHeight != -1) { row.setHeight(rowHeight); } } else { row = sheet.getRow(index); if (rowHeight != -1) { row.setHeight(rowHeight); } } for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); Object value = getCellValue(entity, obj); if (entity.getType() == BaseEntityTypeConstants.STRING_TYPE) { createStringCell(row, cellNum++, value == null ? "" : value.toString(), row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); if (entity.isHyperlink()) { row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value)); } } else if (entity.getType() == BaseEntityTypeConstants.DOUBLE_TYPE) { createDoubleCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); if (entity.isHyperlink()) { row.getCell(cellNum - 1).setHyperlink(dataHandler.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value)); } } else { createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj); } } } /** * Cell */ public void createStringCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) { Cell cell = row.createCell(index); if (style != null && style.getDataFormat() > 0 && style.getDataFormat() < 12) { cell.setCellValue(Double.parseDouble(text)); cell.setCellType(CellType.NUMERIC); } else { RichTextString rtext; if (type.equals(ExcelType.HSSF)) { rtext = new HSSFRichTextString(text); } else { rtext = new XSSFRichTextString(text); } cell.setCellValue(rtext); } if (style != null) { cell.setCellStyle(style); } addStatisticsData(index, text, entity); } /** * Cell */ public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) { Cell cell = row.createCell(index); cell.setCellType(CellType.NUMERIC); if (text != null && text.length() > 0) { try { cell.setCellValue(Double.parseDouble(text)); } catch (NumberFormatException e) { cell.setCellType(CellType.STRING); cell.setCellValue(text); } } if (style != null) { cell.setCellStyle(style); } addStatisticsData(index, text, entity); } /** * */ public void addStatisticsRow(CellStyle styles, Sheet sheet) { if (statistics.size() > 0) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("add statistics data ,size is {}", statistics.size()); } Row row = sheet.createRow(sheet.getLastRowNum() + 1); Set<Integer> keys = statistics.keySet(); createStringCell(row, 0, "?", styles, null); for (Integer key : keys) { createStringCell(row, key, DOUBLE_FORMAT.format(statistics.get(key)), styles, null); } statistics.clear(); } } /** * ?? */ private void addStatisticsData(Integer index, String text, ExcelExportEntity entity) { if (entity != null && entity.isStatistics()) { Double temp = 0D; if (!statistics.containsKey(index)) { statistics.put(index, temp); } try { temp = Double.valueOf(text); } catch (NumberFormatException e) { } statistics.put(index, statistics.get(index) + temp); } } /** * ?,? * * @author JueYue 20131125 */ public int getImageType(byte[] value) { String type = PoiPublicUtil.getFileExtendName(value); if ("JPG".equalsIgnoreCase(type)) { return Workbook.PICTURE_TYPE_JPEG; } else if ("PNG".equalsIgnoreCase(type)) { return Workbook.PICTURE_TYPE_PNG; } return Workbook.PICTURE_TYPE_JPEG; } private Map<Integer, int[]> getMergeDataMap(List<ExcelExportEntity> excelParams) { Map<Integer, int[]> mergeMap = new HashMap<Integer, int[]>(); // ??,???? int i = 0; for (ExcelExportEntity entity : excelParams) { if (entity.isMergeVertical()) { mergeMap.put(i, entity.getMergeRely()); } if (entity.getList() != null) { for (ExcelExportEntity inner : entity.getList()) { if (inner.isMergeVertical()) { mergeMap.put(i, inner.getMergeRely()); } i++; } } else { i++; } } return mergeMap; } /** * ?? */ public CellStyle getStyles(boolean needOne, ExcelExportEntity entity) { return excelExportStyler.getStyles(needOne, entity); } /** * ?? */ public void mergeCells(Sheet sheet, List<ExcelExportEntity> excelParams, int titleHeight) { Map<Integer, int[]> mergeMap = getMergeDataMap(excelParams); PoiMergeCellUtil.mergeCells(sheet, mergeMap, titleHeight); } public void setCellWith(List<ExcelExportEntity> excelParams, Sheet sheet) { int index = 0; for (int i = 0; i < excelParams.size(); i++) { if (excelParams.get(i).getList() != null) { List<ExcelExportEntity> list = excelParams.get(i).getList(); for (int j = 0; j < list.size(); j++) { sheet.setColumnWidth(index, (int) (256 * list.get(j).getWidth())); index++; } } else { sheet.setColumnWidth(index, (int) (256 * excelParams.get(i).getWidth())); index++; } } } public void setColumnHidden(List<ExcelExportEntity> excelParams, Sheet sheet) { int index = 0; for (int i = 0; i < excelParams.size(); i++) { if (excelParams.get(i).getList() != null) { List<ExcelExportEntity> list = excelParams.get(i).getList(); for (int j = 0; j < list.size(); j++) { sheet.setColumnHidden(index, list.get(j).isColumnHidden()); index++; } } else { sheet.setColumnHidden(index, excelParams.get(i).isColumnHidden()); index++; } } } public void setCurrentIndex(int currentIndex) { this.currentIndex = currentIndex; } public void setExcelExportStyler(IExcelExportStyler excelExportStyler) { this.excelExportStyler = excelExportStyler; } public IExcelExportStyler getExcelExportStyler() { return excelExportStyler; } }