Java tutorial
/* * Copyright 2015-2016 the original author or authors. * * 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.admin.poi; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.BeanUtils; import org.springframework.util.Assert; import java.beans.PropertyDescriptor; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.InvocationTargetException; import java.util.Map; import java.util.Objects; import java.util.Optional; /** * excel * * @author fei */ public class ExcelUtils { /** * excel * * @param excelSheet sheet ? * @param outputStream ? */ public static void export(ExcelSheet excelSheet, OutputStream outputStream) throws IllegalAccessException, IOException, InvocationTargetException { Assert.notNull(outputStream, "? null"); export(excelSheet, ExcelSheet.ExcelType.XLSX, outputStream); } /** * excel * * @param excelSheet sheet ? * @param fileType * @param outputStream ? */ private static void export(ExcelSheet excelSheet, ExcelSheet.ExcelType fileType, OutputStream outputStream) throws IllegalAccessException, IOException, InvocationTargetException { Assert.notNull(fileType, "?"); Assert.notNull(outputStream, "? null"); Workbook workbook; if (fileType == ExcelSheet.ExcelType.XLS) { workbook = new HSSFWorkbook(); } else if (fileType == ExcelSheet.ExcelType.XLSX) { workbook = new XSSFWorkbook(); } else { throw new IllegalArgumentException("???"); } export(excelSheet, workbook, outputStream); } /** * excel * * @param excelSheet sheet ? * @param workbook * @param outputStream ? */ private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream) throws IOException, InvocationTargetException, IllegalAccessException { Sheet sheet; sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex()); // write head writeHead(excelSheet, sheet); // sheet int writeRowIndex = excelSheet.getStartRowIndex(); if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) { for (Object rowData : excelSheet.getDataList()) { // proc row Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex)); writeRow(excelSheet, row, rowData); writeRowIndex++; } } workbook.write(outputStream); } /** * * * @param excelSheet sheet ? * @param sheet sheet */ private static void writeHead(ExcelSheet excelSheet, Sheet sheet) { Row row = Optional.ofNullable(sheet.getRow(excelSheet.getHeadRowIndex())) .orElse(sheet.createRow(excelSheet.getHeadRowIndex())); for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping() .export().entrySet()) { ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue(); Integer colIndex = attribute.getIndex(); Cell cell = row.getCell(colIndex); if (cell == null) { cell = row.createCell(colIndex); } cell.setCellValue(attribute.getHead()); } } /** * ? * * @param excelSheet sheet ? * @param row * @param rowData ? */ private static void writeRow(ExcelSheet excelSheet, Row row, Object rowData) throws InvocationTargetException, IllegalAccessException { for (Map.Entry<String[], ExcelFieldMapping.ExcelFieldMappingAttribute> entry : excelSheet.getFieldMapping() .export().entrySet()) { String[] fieldName = entry.getKey(); ExcelFieldMapping.ExcelFieldMappingAttribute attribute = entry.getValue(); Integer colIndex = attribute.getIndex(); Object val = null; if (rowData != null) { val = getFieldValue(rowData, fieldName); } // proc cell Cell cell = Optional.ofNullable(row.getCell(colIndex)).orElse(row.createCell(colIndex)); ExcelCellProcessor processor = attribute.getCellProcessor(); if (processor == null) { writeCell(cell, val); continue; } processor.process(val, cell); } } /** * ? * * @param rowData ? * @param fieldNames ?? * @return */ private static Object getFieldValue(Object rowData, String... fieldNames) throws InvocationTargetException, IllegalAccessException { Object val; if (rowData instanceof Map) { StringBuilder var = new StringBuilder(); for (String fieldName : fieldNames) { var.append(((Map) rowData).get(fieldName)); } val = var.toString(); } else {// java bean val = ExcelUtils.getProperty(rowData, fieldNames); } // trim val = ((String) val).trim(); if ("".equals(val)) { val = null; } return val; } /** * ?? * * @param cell ? * @param val */ private static void writeCell(Cell cell, Object val) { if (val == null) { cell.setCellValue((String) null); return; } if (val instanceof Double) { cell.setCellValue((Double) val); } else {// String cell.setCellValue(Objects.toString(val)); } } /** * ? * * @param rowData ? * @param fieldNames ?? * @return */ private static Object getProperty(Object rowData, String... fieldNames) throws InvocationTargetException, IllegalAccessException { Objects.requireNonNull(fieldNames, "???"); Object result = null; for (String fieldName : fieldNames) { PropertyDescriptor pd = BeanUtils.getPropertyDescriptor(rowData.getClass(), fieldName); if (pd == null || pd.getReadMethod() == null) { throw new IllegalStateException( "In class" + rowData.getClass() + ", no getter method found for field '" + fieldName + "'"); } // if (fieldNames.length == 1) { return pd.getReadMethod().invoke(rowData, (Object) null); } result = Optional.ofNullable(result).map(Object::toString).orElse("") + pd.getReadMethod().invoke(rowData, (Object[]) null).toString(); } return result; } }