Java tutorial
/* * * Copyright 2017 Crab2Died * All rights reserved. * * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You 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. * * Browse for more information * 1) https://gitee.com/Crab2Died/Excel4J * 2) https://github.com/Crab2died/Excel4J * */ package com.github.crab2died; import com.github.crab2died.converter.DefaultConvertible; import com.github.crab2died.exceptions.Excel4JException; import com.github.crab2died.exceptions.Excel4jReadException; import com.github.crab2died.handler.ExcelHeader; import com.github.crab2died.handler.SheetTemplate; import com.github.crab2died.handler.SheetTemplateHandler; import com.github.crab2died.sheet.wrapper.MapSheetWrapper; import com.github.crab2died.sheet.wrapper.NoTemplateSheetWrapper; import com.github.crab2died.sheet.wrapper.NormalSheetWrapper; import com.github.crab2died.sheet.wrapper.SimpleSheetWrapper; import com.github.crab2died.utils.Utils; import org.apache.commons.csv.CSVFormat; import org.apache.commons.csv.CSVParser; import org.apache.commons.csv.CSVPrinter; import org.apache.commons.csv.CSVRecord; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.lang.reflect.Array; import java.nio.charset.StandardCharsets; import java.util.*; /** * Excel4J?? * <p> * ??6?,???handler<br> * 1.?Excel?,handler{@link ExcelUtils#readExcel2ObjectsHandler}<br> * 2.?Excel?,handler{@link ExcelUtils#readExcel2ObjectsHandler}<br> * 3.??Excel,handler{@link ExcelUtils#exportExcelByModuleHandler}<br> * 4.??Map?,handler{@link ExcelUtils#exportExcelByModuleHandler}<br> * 5.?,handler{@link ExcelUtils#exportExcelByMapHandler}<br> * 6.?,handler{@link ExcelUtils#exportExcelBySimpleHandler}<br> * 7.?CSV?,handler{@link ExcelUtils#readCSVByMapHandler} * 8.CSV, handler{@link ExcelUtils#exportCSVByMapHandler} * <p> * ???(????,?) * ???,????<br> * <p> * : https://gitee.com/Crab2Died/Excel4J * * @author Crab2Died */ public final class ExcelUtils { /** * ?? * {@link ExcelUtils#getInstance()}? */ private static volatile ExcelUtils excelUtils; private ExcelUtils() { } /** * ????? */ public static ExcelUtils getInstance() { if (null == excelUtils) { synchronized (ExcelUtils.class) { if (null == excelUtils) { excelUtils = new ExcelUtils(); } } } return excelUtils; } /*---------------------------------------1.?Excel?--------------------------------------------*/ /* . ?? */ /* 1) ??,Class? */ /* 2) ???, ?, java */ /* . ? */ /* *) excelPath => Excel */ /* *) InputStream => Excel? */ /* *) clazz => java */ /* *) offsetLine => ???(0) */ /* *) limitLine => ?() */ /* *) sheetIndex => Sheet(0) */ /** * ?Excel??java * * @param excelPath Excel * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param offsetLine Excel(0) * @param limitLine ?() * @param sheetIndex Sheet(0) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int limitLine, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) { return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex); } } /** * ?Excel??java * * @param is Excel?? * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param offsetLine Excel(0) * @param limitLine ?() * @param sheetIndex Sheet(0) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int offsetLine, int limitLine, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(is)) { return readExcel2ObjectsHandler(workbook, clazz, offsetLine, limitLine, sheetIndex); } } /** * ?Excel??java * * @param excelPath Excel * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param offsetLine Excel(0) * @param sheetIndex Sheet(0) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int offsetLine, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { return readExcel2Objects(excelPath, clazz, offsetLine, Integer.MAX_VALUE, sheetIndex); } /** * ?Excel??java * * @param excelPath Excel * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param sheetIndex Sheet(0) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, sheetIndex); } /** * ?Excel??java * * @param excelPath Excel * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(String excelPath, Class<T> clazz) throws Excel4JException, IOException, InvalidFormatException { return readExcel2Objects(excelPath, clazz, 0, Integer.MAX_VALUE, 0); } /** * ?Excel??java * * @param is Excel?? * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param sheetIndex Sheet(0) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, sheetIndex); } /** * ?Excel??java * * @param is Excel?? * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @param <T> ? * @return ?java? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public <T> List<T> readExcel2Objects(InputStream is, Class<T> clazz) throws Excel4JException, IOException, InvalidFormatException { return readExcel2Objects(is, clazz, 0, Integer.MAX_VALUE, 0); } private <T> List<T> readExcel2ObjectsHandler(Workbook workbook, Class<T> clazz, int offsetLine, int limitLine, int sheetIndex) throws Excel4JException { Sheet sheet = workbook.getSheetAt(sheetIndex); Row row = sheet.getRow(offsetLine); List<T> list = new ArrayList<>(); Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz); if (maps == null || maps.size() <= 0) throw new Excel4jReadException( "The Excel format to read is not correct, and check to see if the appropriate rows are set"); long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine) : sheet.getLastRowNum(); for (int i = offsetLine + 1; i <= maxLine; i++) { row = sheet.getRow(i); if (null == row) continue; T obj; try { obj = clazz.newInstance(); } catch (InstantiationException | IllegalAccessException e) { throw new Excel4JException(e); } for (Cell cell : row) { int ci = cell.getColumnIndex(); ExcelHeader header = maps.get(ci); if (null == header) continue; String val = Utils.getCellValue(cell); Object value; String filed = header.getFiled(); // ?? if (null != header.getReadConverter() && header.getReadConverter().getClass() != DefaultConvertible.class) { value = header.getReadConverter().execRead(val); } else { // ? value = Utils.str2TargetClass(val, header.getFiledClazz()); } Utils.copyProperty(obj, filed, value); } list.add(obj); } return list; } /*---------------------------------------2.?Excel?-------------------------------------------------*/ /* . ?? */ /* *) ?Excel,? Cell->String => Row->List<Cell> => Excel->List<Row> */ /* . ? */ /* *) excelPath => Excel */ /* *) InputStream => Excel? */ /* *) offsetLine => ???(0) */ /* *) limitLine => ?() */ /* *) sheetIndex => Sheet(0) */ /** * ?Excel?,{@code List[List[String]]}?? * * @param excelPath ?Excel * @param offsetLine Excel(0) * @param limitLine ?() * @param sheetIndex Sheet(0) * @return {@code List<List<String>>}?? * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(String excelPath, int offsetLine, int limitLine, int sheetIndex) throws IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) { return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex); } } /** * ?Excel?,{@code List[List[String]]}?? * * @param is ?Excel?? * @param offsetLine Excel(0) * @param limitLine ?() * @param sheetIndex Sheet(0) * @return {@code List<List<String>>}?? * @throws Excel4JException * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(InputStream is, int offsetLine, int limitLine, int sheetIndex) throws Excel4JException, IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(is)) { return readExcel2ObjectsHandler(workbook, offsetLine, limitLine, sheetIndex); } } /** * ?Excel?,{@code List[List[String]]}?? * * @param excelPath ?Excel * @param offsetLine Excel(0) * @return {@code List<List<String>>}?? * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(String excelPath, int offsetLine) throws IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) { return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0); } } /** * ?Excel?,{@code List[List[String]]}?? * * @param is ?Excel?? * @param offsetLine Excel(0) * @return {@code List<List<String>>}?? * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(InputStream is, int offsetLine) throws IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(is)) { return readExcel2ObjectsHandler(workbook, offsetLine, Integer.MAX_VALUE, 0); } } /** * ?Excel?,{@code List[List[String]]}?? * * @param excelPath ?Excel * @return {@code List<List<String>>}?? * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(String excelPath) throws IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(new FileInputStream(new File(excelPath)))) { return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0); } } /** * ?Excel?,{@code List[List[String]]}?? * * @param is ?Excel?? * @return {@code List<List<String>>}?? * @throws IOException * @throws InvalidFormatException * @author Crab2Died */ public List<List<String>> readExcel2List(InputStream is) throws IOException, InvalidFormatException { try (Workbook workbook = WorkbookFactory.create(is)) { return readExcel2ObjectsHandler(workbook, 0, Integer.MAX_VALUE, 0); } } private List<List<String>> readExcel2ObjectsHandler(Workbook workbook, int offsetLine, int limitLine, int sheetIndex) { List<List<String>> list = new ArrayList<>(); Sheet sheet = workbook.getSheetAt(sheetIndex); long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine) : sheet.getLastRowNum(); for (int i = offsetLine; i <= maxLine; i++) { List<String> rows = new ArrayList<>(); Row row = sheet.getRow(i); if (null == row) continue; for (Cell cell : row) { String val = Utils.getCellValue(cell); rows.add(val); } list.add(rows); } return list; } /*-------------------------------------------3.??excel------------------------------------------*/ /* . ?? */ /* 1) ?? */ /* 2) ?Java */ /* 3) ? */ /* . ? */ /* *) templatePath => ? */ /* *) sheetIndex => Sheet(0) */ /* *) data => List? */ /* *) extendMap => Map(key???#key) */ /* *) clazz => Class */ /* *) isWriteHeader => ? */ /* *) targetPath => */ /* *) os => ? */ /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param sheetIndex Excelsheet?(0) * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param sheetIndex Excelsheet?(0) * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, String targetPath) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, targetPath); } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, extendMap, clazz, isWriteHeader, os); } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz, String targetPath) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, extendMap, clazz, true, targetPath); } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Map<String, String> extendMap, Class clazz, OutputStream os) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, extendMap, clazz, true, os); } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param clazz Class * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, String targetPath) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, null, clazz, true, targetPath); } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}Excel * * @param templatePath Excel? * @param data ?? * @param clazz Class * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportObjects2Excel(String templatePath, List<?> data, Class clazz, OutputStream os) throws Excel4JException { exportObjects2Excel(templatePath, 0, data, null, clazz, true, os); } // ?sheet private SheetTemplate exportExcelByModuleHandler(String templatePath, int sheetIndex, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException { SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath); generateSheet(sheetIndex, data, extendMap, clazz, isWriteHeader, template); return template; } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}sheetExcel * * @param sheetWrappers sheet * @param templatePath Excel? * @param targetPath Excel * @throws Excel4JException */ public void normalSheet2Excel(List<NormalSheetWrapper> sheetWrappers, String templatePath, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetWrappers)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * Excel?{@link com.github.crab2died.annotation.ExcelField}sheetExcel * * @param sheetWrappers sheet * @param templatePath Excel? * @param os ?Excel?? * @throws Excel4JException */ public void normalSheet2Excel(List<NormalSheetWrapper> sheetWrappers, String templatePath, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByModuleHandler(templatePath, sheetWrappers)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } // sheet private SheetTemplate exportExcelByModuleHandler(String templatePath, List<NormalSheetWrapper> sheets) throws Excel4JException { SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath); for (NormalSheetWrapper sheet : sheets) { generateSheet(sheet.getSheetIndex(), sheet.getData(), sheet.getExtendMap(), sheet.getClazz(), sheet.isWriteHeader(), template); } return template; } // ?sheet? private void generateSheet(int sheetIndex, List<?> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, SheetTemplate template) throws Excel4JException { SheetTemplateHandler.loadTemplate(template, sheetIndex); SheetTemplateHandler.extendData(template, extendMap); List<ExcelHeader> headers = Utils.getHeaderList(clazz); if (isWriteHeader) { // SheetTemplateHandler.createNewRow(template); for (ExcelHeader header : headers) { SheetTemplateHandler.createCell(template, header.getTitle(), null); } } for (Object object : data) { SheetTemplateHandler.createNewRow(template); SheetTemplateHandler.insertSerial(template, null); for (ExcelHeader header : headers) { SheetTemplateHandler.createCell(template, Utils.getProperty(object, header.getFiled(), header.getWriteConverter()), null); } } } /*-------------------------------------4.??Map?----------------------------------------------*/ /* . ?? */ /* 1) ?? */ /* 2) ?Java */ /* 3) ? */ /* . ? */ /* *) templatePath => ? */ /* *) sheetIndex => Sheet(0) */ /* *) data => Map? */ /* *) extendMap => Map(key???#key) */ /* *) clazz => Class */ /* *) isWriteHeader => ? */ /* *) targetPath => */ /* *) os => ? */ /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param sheetIndex Excelsheet?(0) * @param data {@code Map<String, List<?>>}? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, int sheetIndex, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param sheetIndex Excelsheet?(0) * @param data {@code Map<String, List<?>>}? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param isWriteHeader ? * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, int sheetIndex, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param data {@code Map<String, List<?>>}? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, extendMap, clazz, true)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param data {@code Map<String, List<?>>}? * @param extendMap Map?(key???#key,Excel?) * @param clazz Class * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, extendMap, clazz, true)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param data {@code Map<String, List<?>>}? * @param clazz Class * @param targetPath ?Excel * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Class clazz, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, null, clazz, true)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??{@code Map[String, List[?]]}? * ?? * * @param templatePath Excel? * @param data {@code Map<String, List<?>>}? * @param clazz Class * @param os ?Excel?? * @throws Excel4JException * @author Crab2Died */ public void exportMap2Excel(String templatePath, Map<String, List<?>> data, Class clazz, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(templatePath, 0, data, null, clazz, true)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } // ?sheet private SheetTemplate exportExcelByMapHandler(String templatePath, int sheetIndex, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException { // ? SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath); // ?sheet generateSheet(template, sheetIndex, data, extendMap, clazz, isWriteHeader); return template; } /** * ??sheet{@code Map[String, List[?]]}? * ?? * * @param sheetWrappers sheet * @param templatePath Excel? * @param targetPath Excel * @throws Excel4JException */ public void mapSheet2Excel(List<MapSheetWrapper> sheetWrappers, String templatePath, String targetPath) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(sheetWrappers, templatePath)) { sheetTemplate.write2File(targetPath); } catch (IOException e) { throw new Excel4JException(e); } } /** * ??sheet{@code Map[String, List[?]]}? * ?? * * @param sheetWrappers sheet * @param templatePath Excel? * @param os ? * @throws Excel4JException */ public void mapSheet2Excel(List<MapSheetWrapper> sheetWrappers, String templatePath, OutputStream os) throws Excel4JException { try (SheetTemplate sheetTemplate = exportExcelByMapHandler(sheetWrappers, templatePath)) { sheetTemplate.write2Stream(os); } catch (IOException e) { throw new Excel4JException(e); } } // sheet private SheetTemplate exportExcelByMapHandler(List<MapSheetWrapper> sheetWrappers, String templatePath) throws Excel4JException { // ? SheetTemplate template = SheetTemplateHandler.sheetTemplateBuilder(templatePath); // sheet? for (MapSheetWrapper sheet : sheetWrappers) { generateSheet(template, sheet.getSheetIndex(), sheet.getData(), sheet.getExtendMap(), sheet.getClazz(), sheet.isWriteHeader()); } return template; } // sheet? private void generateSheet(SheetTemplate template, int sheetIndex, Map<String, List<?>> data, Map<String, String> extendMap, Class clazz, boolean isWriteHeader) throws Excel4JException { SheetTemplateHandler.loadTemplate(template, sheetIndex); SheetTemplateHandler.extendData(template, extendMap); List<ExcelHeader> headers = Utils.getHeaderList(clazz); if (isWriteHeader) { // SheetTemplateHandler.createNewRow(template); for (ExcelHeader header : headers) { SheetTemplateHandler.createCell(template, header.getTitle(), null); } } for (Map.Entry<String, List<?>> entry : data.entrySet()) { for (Object object : entry.getValue()) { SheetTemplateHandler.createNewRow(template); SheetTemplateHandler.insertSerial(template, entry.getKey()); for (ExcelHeader header : headers) { SheetTemplateHandler.createCell(template, Utils.getProperty(object, header.getFiled(), header.getWriteConverter()), entry.getKey()); } } } } /*--------------------------------------5.?---------------------------------------------------*/ /* . ?? */ /* 1) ?Java */ /* 2) ? */ /* . ? */ /* *) data => List? */ /* *) isWriteHeader => ? */ /* *) sheetName => Sheet??(0) */ /* *) clazz => Class */ /* *) isXSSF => ?Excel2007? */ /* *) targetPath => */ /* *) os => ? */ /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param sheetName Excelsheet?? * @param isXSSF Excel?Excel2007?() * @param targetPath ?Excel * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF, String targetPath) throws Excel4JException, IOException { try (FileOutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, sheetName, isXSSF)) { workbook.write(fos); } } /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param sheetName Excelsheet?? * @param isXSSF Excel?Excel2007?() * @param os ?Excel?? * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF, OutputStream os) throws Excel4JException, IOException { try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, sheetName, isXSSF)) { workbook.write(os); } } /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param targetPath ?Excel * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, String targetPath) throws Excel4JException, IOException { try (FileOutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, null, true)) { workbook.write(fos); } } /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param os ?Excel?? * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException, IOException { try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, isWriteHeader, null, true)) { workbook.write(os); } } /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param os ?Excel?? * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, OutputStream os) throws Excel4JException, IOException { try (Workbook workbook = exportExcelNoTemplateHandler(data, clazz, true, null, true)) { workbook.write(os); } } /** * ??? * * @param data ? * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param targetPath ?Excel * @throws Excel4JException * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, Class clazz, String targetPath) throws Excel4JException, IOException { try (FileOutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelNoTemplateHandler(data, clazz, true, null, true)) { workbook.write(fos); } } // ?sheet? private Workbook exportExcelNoTemplateHandler(List<?> data, Class clazz, boolean isWriteHeader, String sheetName, boolean isXSSF) throws Excel4JException { Workbook workbook; if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } generateSheet(workbook, data, clazz, isWriteHeader, sheetName); return workbook; } /** * ???sheet? * * @param sheets sheet? * @param targetPath ?Excel * @throws Excel4JException * @throws IOException */ public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, String targetPath) throws Excel4JException, IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelNoTemplateHandler(sheets, true)) { workbook.write(fos); } } /** * ???sheet? * * @param sheets sheet? * @param isXSSF Excel?Excel2007?() * @param targetPath ?Excel * @throws Excel4JException * @throws IOException */ public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, boolean isXSSF, String targetPath) throws Excel4JException, IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelNoTemplateHandler(sheets, isXSSF)) { workbook.write(fos); } } /** * ???sheet? * * @param sheets sheet? * @param os ?Excel? * @throws Excel4JException * @throws IOException */ public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, OutputStream os) throws Excel4JException, IOException { try (Workbook workbook = exportExcelNoTemplateHandler(sheets, true)) { workbook.write(os); } } /** * ???sheet? * * @param sheets sheet? * @param isXSSF Excel?Excel2007?() * @param os ?Excel? * @throws Excel4JException * @throws IOException */ public void noTemplateSheet2Excel(List<NoTemplateSheetWrapper> sheets, boolean isXSSF, OutputStream os) throws Excel4JException, IOException { try (Workbook workbook = exportExcelNoTemplateHandler(sheets, isXSSF)) { workbook.write(os); } } // sheet? private Workbook exportExcelNoTemplateHandler(List<NoTemplateSheetWrapper> sheetWrappers, boolean isXSSF) throws Excel4JException { Workbook workbook; if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // sheet for (NoTemplateSheetWrapper sheet : sheetWrappers) { generateSheet(workbook, sheet.getData(), sheet.getClazz(), sheet.isWriteHeader(), sheet.getSheetName()); } return workbook; } // ?sheet? private void generateSheet(Workbook workbook, List<?> data, Class clazz, boolean isWriteHeader, String sheetName) throws Excel4JException { Sheet sheet; if (null != sheetName && !"".equals(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } Row row = sheet.createRow(0); List<ExcelHeader> headers = Utils.getHeaderList(clazz); if (isWriteHeader) { // for (int i = 0; i < headers.size(); i++) { row.createCell(i).setCellValue(headers.get(i).getTitle()); } } // ? Object _data; for (int i = 0; i < data.size(); i++) { row = sheet.createRow(i + 1); _data = data.get(i); for (int j = 0; j < headers.size(); j++) { row.createCell(j).setCellValue( Utils.getProperty(_data, headers.get(j).getFiled(), headers.get(j).getWriteConverter())); } } } /*---------------------------------------6.?----------------------------------------------------*/ /* . ?? */ /* 1) (?) */ /* 2) ? */ /* . ? */ /* *) data => List? */ /* *) header => ?,,? */ /* *) sheetName => Sheet??(0) */ /* *) isXSSF => ?Excel2007? */ /* *) targetPath => */ /* *) os => ? */ /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param header ? * @param sheetName Excelsheet?? * @param isXSSF Excel?Excel2007?() * @param targetPath ?Excel * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF, String targetPath) throws IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelBySimpleHandler(data, header, sheetName, isXSSF)) { workbook.write(fos); } } /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param header ? * @param sheetName Excelsheet?? * @param isXSSF Excel?Excel2007?() * @param os ?Excel?? * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, List<String> header, String sheetName, boolean isXSSF, OutputStream os) throws IOException { try (Workbook workbook = exportExcelBySimpleHandler(data, header, sheetName, isXSSF)) { workbook.write(os); } } /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param header ? * @param targetPath ?Excel * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, List<String> header, String targetPath) throws IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelBySimpleHandler(data, header, null, true)) { workbook.write(fos); } } /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param header ? * @param os ?Excel?? * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, List<String> header, OutputStream os) throws IOException { try (Workbook workbook = exportExcelBySimpleHandler(data, header, null, true)) { workbook.write(os); } } /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param targetPath ?Excel * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, String targetPath) throws IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelBySimpleHandler(data, null, null, true)) { workbook.write(fos); } } /** * ???({@code List[?]}?{@code List[List[?]]}?{@code List[Object[]]}) * * @param data ? * @param os ?Excel?? * @throws IOException * @author Crab2Died */ public void exportObjects2Excel(List<?> data, OutputStream os) throws IOException { try (Workbook workbook = exportExcelBySimpleHandler(data, null, null, true)) { workbook.write(os); } } private Workbook exportExcelBySimpleHandler(List<?> data, List<String> header, String sheetName, boolean isXSSF) { Workbook workbook; if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // ?sheet this.generateSheet(workbook, data, header, sheetName); return workbook; } /** * ???sheet? * * @param sheets sheet? * @param targetPath ?Excel * @throws IOException */ public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, String targetPath) throws IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelBySimpleHandler(sheets, true)) { workbook.write(fos); } } /** * ???sheet? * * @param sheets sheet? * @param isXSSF Excel?Excel2007?() * @param targetPath ?Excel * @throws IOException */ public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, boolean isXSSF, String targetPath) throws IOException { try (OutputStream fos = new FileOutputStream(targetPath); Workbook workbook = exportExcelBySimpleHandler(sheets, isXSSF)) { workbook.write(fos); } } /** * ???sheet? * * @param sheets sheet? * @param os ?Excel?? * @throws IOException */ public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, OutputStream os) throws IOException { try (Workbook workbook = exportExcelBySimpleHandler(sheets, true)) { workbook.write(os); } } /** * ???sheet? * * @param sheets sheet? * @param isXSSF Excel?Excel2007?() * @param os ?Excel?? * @throws IOException */ public void simpleSheet2Excel(List<SimpleSheetWrapper> sheets, boolean isXSSF, OutputStream os) throws IOException { try (Workbook workbook = exportExcelBySimpleHandler(sheets, isXSSF)) { workbook.write(os); } } private Workbook exportExcelBySimpleHandler(List<SimpleSheetWrapper> sheets, boolean isXSSF) { Workbook workbook; if (isXSSF) { workbook = new XSSFWorkbook(); } else { workbook = new HSSFWorkbook(); } // ?sheet for (SimpleSheetWrapper sheet : sheets) { this.generateSheet(workbook, sheet.getData(), sheet.getHeader(), sheet.getSheetName()); } return workbook; } // ?sheet? private void generateSheet(Workbook workbook, List<?> data, List<String> header, String sheetName) { Sheet sheet; if (null != sheetName && !"".equals(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowIndex = 0; if (null != header && header.size() > 0) { // Row row = sheet.createRow(rowIndex++); for (int i = 0; i < header.size(); i++) { row.createCell(i, CellType.STRING).setCellValue(header.get(i)); } } for (Object object : data) { Row row = sheet.createRow(rowIndex++); if (object.getClass().isArray()) { for (int j = 0; j < Array.getLength(object); j++) { row.createCell(j, CellType.STRING).setCellValue(Array.get(object, j).toString()); } } else if (object instanceof Collection) { Collection<?> items = (Collection<?>) object; int j = 0; for (Object item : items) { row.createCell(j++, CellType.STRING).setCellValue(item.toString()); } } else { row.createCell(0, CellType.STRING).setCellValue(object.toString()); } } } /*---------------------------------------7.CSV?--------------------------------------------------*/ /* . ?? */ /* 1) ??,Class? */ /* 2) ???, ?, java */ /* . ? */ /* *) path => ? */ /* *) is => ?? */ /* *) clazz => */ /** * ?CSV * * @param path ? * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @return ?java? * @throws Excel4jReadException exception */ public <T> List<T> readCSV2Objects(String path, Class<T> clazz) { try (InputStream is = new FileInputStream(new File(path))) { return readCSVByMapHandler(is, clazz); } catch (IOException | Excel4JException e) { throw new Excel4jReadException("read [" + path + "] CSV Error: ", e); } } /** * ?CSV * * @param is ?? * @param clazz ({@link com.github.crab2died.annotation.ExcelField}) * @return ?java? * @throws Excel4jReadException exception */ public <T> List<T> readCSV2Objects(InputStream is, Class<T> clazz) { try { return readCSVByMapHandler(is, clazz); } catch (Excel4JException | IOException e) { throw new Excel4jReadException("read CSV Error: ", e); } } // ?csv private <T> List<T> readCSVByMapHandler(InputStream is, Class<T> clazz) throws IOException, Excel4JException { List<T> records = new ArrayList<>(); List<ExcelHeader> headers = Utils.getHeaderList(clazz); if (null == headers || headers.size() <= 0) { throw new Excel4jReadException("[" + clazz + "] must configuration @ExcelFiled"); } String[] csvHeaders = new String[headers.size()]; for (int i = 0; i < headers.size(); i++) { csvHeaders[i] = headers.get(i).getTitle(); } CSVFormat format = CSVFormat.EXCEL.withHeader(csvHeaders).withSkipHeaderRecord(true); try (Reader read = new InputStreamReader(is, StandardCharsets.UTF_8); CSVParser parser = new CSVParser(read, format)) { for (CSVRecord _parser : parser) { T obj; try { obj = clazz.newInstance(); } catch (InstantiationException | IllegalAccessException e) { throw new Excel4jReadException(e); } for (ExcelHeader header : headers) { String value = _parser.get(header.getTitle()); Object objectVal; String filed = header.getFiled(); // ?? if (null != header.getReadConverter() && header.getReadConverter().getClass() != DefaultConvertible.class) { objectVal = header.getReadConverter().execRead(value); } else { // ? objectVal = Utils.str2TargetClass(value, header.getFiledClazz()); } Utils.copyProperty(obj, filed, objectVal); } records.add(obj); } } return records; } /*---------------------------------------8.CSV--------------------------------------------------*/ /* . ?? */ /* 1) (?) */ /* 2) ? */ /* . ? */ /* *) data => List? */ /* *) isWriteHeader => ? */ /* *) path => */ /* *) os => ? */ /* *) clazz => */ /** * CSV * * @param data * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param path * @throws Excel4JException exception */ public void exportObjects2CSV(List<?> data, Class clazz, String path) throws Excel4JException { try { Writer writer = new FileWriter(path); exportCSVByMapHandler(data, clazz, true, writer); } catch (Excel4JException | IOException e) { throw new Excel4JException(e); } } /** * CSV? * * @param data * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param os ? * @throws Excel4JException exception */ public void exportObjects2CSV(List<?> data, Class clazz, OutputStream os) throws Excel4JException { try { Writer writer = new OutputStreamWriter(os); exportCSVByMapHandler(data, clazz, true, writer); } catch (Excel4JException | IOException e) { throw new Excel4JException(e); } } /** * CSV * * @param data * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param path * @throws Excel4JException exception */ public void exportObjects2CSV(List<?> data, Class clazz, boolean isWriteHeader, String path) throws Excel4JException { try { Writer writer = new FileWriter(path); exportCSVByMapHandler(data, clazz, isWriteHeader, writer); } catch (Excel4JException | IOException e) { throw new Excel4JException(e); } } /** * CSV? * * @param data * @param clazz {@link com.github.crab2died.annotation.ExcelField}Class * @param isWriteHeader ? * @param os ? * @throws Excel4JException exception */ public void exportObjects2CSV(List<?> data, Class clazz, boolean isWriteHeader, OutputStream os) throws Excel4JException { try { Writer writer = new OutputStreamWriter(os); exportCSVByMapHandler(data, clazz, isWriteHeader, writer); } catch (Excel4JException | IOException e) { throw new Excel4JException(e); } } private static final byte[] UTF_8_DOM = { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF }; // ?CSV private void exportCSVByMapHandler(List<?> data, Class clazz, boolean isWriteHeader, Appendable appendable) throws Excel4JException, IOException { List<ExcelHeader> headers = Utils.getHeaderList(clazz); appendable.append(new String(UTF_8_DOM, StandardCharsets.UTF_8)); try (CSVPrinter printer = new CSVPrinter(appendable, CSVFormat.EXCEL)) { if (isWriteHeader) { for (ExcelHeader header : headers) { printer.print(header.getTitle()); } printer.println(); } // ? for (Object _data : data) { for (ExcelHeader header : headers) { printer.print(Utils.getProperty(_data, header.getFiled(), header.getWriteConverter())); } printer.println(); } printer.flush(); } } }