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 com.qihang.winter.poi.excel.export.base; import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.IOException; import java.text.DecimalFormat; import java.util.Collection; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import javax.imageio.ImageIO; 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.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.ClientAnchor; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.RichTextString; 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.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import com.qihang.winter.poi.util.PoiPublicUtil; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * ??POI?? * * @author Zerrion * @date 2014617 ?6:15:13 */ public abstract class ExcelExportBase extends ExportBase { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportBase.class); private int currentIndex = 0; protected com.qihang.winter.poi.excel.entity.enmus.ExcelType type = com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF; private Map<Integer, Double> statistics = new HashMap<Integer, Double>(); private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00"); private com.qihang.winter.poi.excel.export.styler.IExcelExportStyler excelExportStyler; /** * ? Cells * * @param styles * @param rowHeight * @throws Exception */ public int createCells(Drawing patriarch, int index, Object t, List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook, short rowHeight) throws Exception { com.qihang.winter.poi.excel.entity.params.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; 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() == 1) { createStringCell(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); } } } // ???? 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, com.qihang.winter.poi.excel.entity.params.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(com.qihang.winter.poi.excel.entity.enmus.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); } if (StringUtils.isEmpty(imagePath)) { return; } if (entity.getExportImageType() == 1) { ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg; try { String path = PoiPublicUtil.getWebRootPath(imagePath); path = path.replace("WEB-INF/classes/", ""); path = path.replace("file:/", ""); bufferImg = ImageIO.read(new File(path)); ImageIO.write(bufferImg, imagePath.substring(imagePath.indexOf(".") + 1, imagePath.length()), byteArrayOut); byte[] value = byteArrayOut.toByteArray(); patriarch.createPicture(anchor, row.getSheet().getWorkbook().addPicture(value, getImageType(value))); } catch (IOException e) { LOGGER.error(e.getMessage(), e); } } else { byte[] 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, com.qihang.winter.poi.excel.entity.params.ExcelExportEntity excelExportEntity) { if (excelExportEntity.getName().equals("??") && excelExportEntity.getFormat() .equals(com.qihang.winter.poi.excel.entity.vo.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 styles */ public void createListCells(Drawing patriarch, int index, int cellNum, Object obj, List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet, Workbook workbook) throws Exception { com.qihang.winter.poi.excel.entity.params.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() == 1) { createStringCell(row, cellNum++, value == null ? "" : value.toString(), row.getRowNum() % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity); } 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, com.qihang.winter.poi.excel.entity.params.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(com.qihang.winter.poi.excel.entity.enmus.ExcelType.HSSF)) { Rtext = new HSSFRichTextString(text); } else { Rtext = new XSSFRichTextString(text); } cell.setCellValue(Rtext); } 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, com.qihang.winter.poi.excel.entity.params.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 excelParams * @return */ public int getFieldWidth(List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams) { int length = -1;// 0? for (com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity : excelParams) { length += entity.getList() != null ? entity.getList().size() : 1; } return length; } /** * ?,? * * @param value * @return * @author Zerrion * @date 20131125 */ 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<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams) { Map<Integer, int[]> mergeMap = new HashMap<Integer, int[]>(); // ??,???? int i = 0; for (com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity : excelParams) { if (entity.isMergeVertical()) { mergeMap.put(i, entity.getMergeRely()); } if (entity.getList() != null) { for (com.qihang.winter.poi.excel.entity.params.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, com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity) { return excelExportStyler.getStyles(needOne, entity); } /** * ?? * * @param sheet * @param excelParams * @param styles */ public void mergeCells(Sheet sheet, List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, int titleHeight) { Map<Integer, int[]> mergeMap = getMergeDataMap(excelParams); com.qihang.winter.poi.util.PoiMergeCellUtil.mergeCells(sheet, mergeMap, titleHeight); } public void setCellWith(List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet) { int index = 0; for (int i = 0; i < excelParams.size(); i++) { if (excelParams.get(i).getList() != null) { List<com.qihang.winter.poi.excel.entity.params.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( com.qihang.winter.poi.excel.export.styler.IExcelExportStyler excelExportStyler) { this.excelExportStyler = excelExportStyler; } public com.qihang.winter.poi.excel.export.styler.IExcelExportStyler getExcelExportStyler() { return excelExportStyler; } }