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.bzvs.excel.export.base; import cn.bzvs.cache.ImageCache; import cn.bzvs.excel.entity.enmus.ExcelType; import cn.bzvs.excel.entity.params.ExcelExportEntity; import cn.bzvs.excel.entity.vo.BaseEntityTypeConstants; import cn.bzvs.excel.entity.vo.PoiBaseConstants; import cn.bzvs.excel.export.styler.IExcelExportStyler; import cn.bzvs.util.PoiMergeCellUtil; import cn.bzvs.util.PoiPublicUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import java.text.DecimalFormat; import java.util.*; /** * ??POI?? */ @SuppressWarnings("unchecked") public abstract class ExcelExportBase extends ExportBase { 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 * * @param patriarch * @param index * @param t * @param excelParams * @param sheet * @param workbook * @param rowHeight * @return * @throws Exception */ public int createCells(Drawing patriarch, int index, Object t, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception { ExcelExportEntity entity; Row row = sheet.createRow(index); row.setHeight(rowHeight); int maxHeight = 1, cellNum = 0; 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 listC = 0; if (list != null && list.size() > 0) { for (Object obj : list) { createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook); listC++; } } cellNum += entity.getList().size(); if (list != null && list.size() > maxHeight) { maxHeight = list.size(); } } else { Object value = getCellValue(entity, t); if (entity.getType() == BaseEntityTypeConstants.StringType) { createStringCell(row, cellNum++, value == null ? "" : value.toString(), index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); if (entity.isHyperlink()) { row.getCell(cellNum - 1).setHyperlink(dataHanlder.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value)); } } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) { 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(dataHanlder.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), t, entity.getName(), value)); } } else { createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t); } } } // ???? cellNum = 0; for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); if (entity.getList() != null) { cellNum += entity.getList().size(); } else if (entity.isNeedMerge()) { for (int i = index + 1; i < index + maxHeight; i++) { sheet.getRow(i).createCell(cellNum); sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity)); } sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum)); cellNum++; } } return maxHeight; } /** * Cell * * @param patriarch * @param entity * @param row * @param i * @param imagePath * @param obj * @throws Exception */ public void createImageCell(Drawing patriarch, ExcelExportEntity entity, Row row, int i, String imagePath, Object obj) throws Exception { row.setHeight((short) (50 * entity.getHeight())); row.createCell(i); ClientAnchor anchor; if (type.equals(ExcelType.HSSF)) { anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1), row.getRowNum() + 1); } else { anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) i, row.getRowNum(), (short) (i + 1), row.getRowNum() + 1); } byte[] value = null; if (entity.getExportImageType() == 1) { if (StringUtils.isNotEmpty(imagePath)) { value = ImageCache.getImage(imagePath); } } else { value = (byte[]) (entity.getMethods() != null ? getFieldBySomeMethod(entity.getMethods(), obj) : entity.getMethod().invoke(obj, new Object[] {})); } if (value != null) { patriarch.createPicture(anchor, row.getSheet().getWorkbook().addPicture(value, getImageType(value))); } } private int createIndexCell(Row row, int index, ExcelExportEntity excelExportEntity) { if (excelExportEntity.getName().equals("??") && 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 * * @param patriarch * @param index * @param cellNum * @param obj * @param excelParams * @param sheet * @param workbook * @throws Exception */ public void createListCells(Drawing patriarch, int index, int cellNum, Object obj, List<ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook) throws Exception { ExcelExportEntity entity; Row row; if (sheet.getRow(index) == null) { row = sheet.createRow(index); row.setHeight(getRowHeight(excelParams)); } else { row = sheet.getRow(index); } for (int k = 0, paramSize = excelParams.size(); k < paramSize; k++) { entity = excelParams.get(k); Object value = getCellValue(entity, obj); if (entity.getType() == BaseEntityTypeConstants.StringType) { 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(dataHanlder.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value)); } } else if (entity.getType() == BaseEntityTypeConstants.DoubleType) { 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(dataHanlder.getHyperlink( row.getSheet().getWorkbook().getCreationHelper(), obj, entity.getName(), value)); } } else { createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), obj); } } } /** * Cell * * @param row * @param index * @param text * @param style * @param entity */ 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(Cell.CELL_TYPE_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 * * @param row * @param index * @param text * @param style * @param entity */ public void createDoubleCell(Row row, int index, String text, CellStyle style, ExcelExportEntity entity) { Cell cell = row.createCell(index); if (text != null && text.length() > 0) { cell.setCellValue(Double.parseDouble(text)); } else { cell.setCellValue(-1); } cell.setCellType(Cell.CELL_TYPE_NUMERIC); if (style != null) { cell.setCellStyle(style); } addStatisticsData(index, text, entity); } /** * * * @param styles * @param sheet */ public void addStatisticsRow(CellStyle styles, Sheet sheet) { if (statistics.size() > 0) { 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(); } } /** * ?? * * @param index * @param text * @param entity */ 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); } } /** * ?,? * * @param value * @return */ public int getImageType(byte[] value) { String type = PoiPublicUtil.getFileExtendName(value); if (type.equalsIgnoreCase("JPG")) { return Workbook.PICTURE_TYPE_JPEG; } else if (type.equalsIgnoreCase("PNG")) { 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; } /** * ?? * * @param entity * @param needOne * @return */ public CellStyle getStyles(boolean needOne, ExcelExportEntity entity) { return excelExportStyler.getStyles(needOne, entity); } /** * ?? * * @param sheet * @param excelParams * @param titleHeight */ 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 setCurrentIndex(int currentIndex) { this.currentIndex = currentIndex; } public void setExcelExportStyler(IExcelExportStyler excelExportStyler) { this.excelExportStyler = excelExportStyler; } public IExcelExportStyler getExcelExportStyler() { return excelExportStyler; } }