java POI 导出excel 多个sheet 每个sheet各不相同

导出excel

直接上核心步骤

1 初始化工具类

ExportExcel<ExcelVo> ex = new ExportExcel<RedistributionExcelVo>();

2 初始化数据

    List<ExcelVo> Vos1 = new ArrayList();
    List<ExcelVo> Vos2 = new ArrayList();

表头

    String[] ss1 = new String[headers.size()];
    String[] ss 2= new String[headers.size()];

3 装载数据省略

4 导出表

path 相同就把两个sheet组合成一个Excel了

 ex.exportExcel(path," 参数二 sheet 名称1   ", ss1, Vos1,"yyyyMMdd");
 ex.exportExcel(path," 参数二 sheet 名称2", ss2, Vos2,"yyyyMMdd");

工具类

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


public class ExportExcel<T> {
    private static final Pattern P = Pattern.compile("^\\d+(\\.\\d+)?$");
    private static final String SEPARATOR = System.getProperty("line.separator");

    public ExportExcel() {
    }

    public void convertXls2CSVorTxt(String excelFile, String csvFile) throws Exception {
        String msg = "convert excel to csv excelFile=" + excelFile + " csvFile=" + csvFile;
        FileWriter fw = null;

        try {
            HSSFWorkbook readWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
            HSSFSheet sourceSheet = readWorkbook.getSheetAt(0);
            fw = new FileWriter(csvFile);
            String content = "";

            for(int i = 0; i <= sourceSheet.getLastRowNum(); ++i) {
                HSSFRow sourceRow = sourceSheet.getRow(i);

                for(int j = 0; j < sourceRow.getLastCellNum(); ++j) {
                    HSSFCell sourceCell = sourceRow.getCell(j);
                    if (j == sourceRow.getLastCellNum() - 1) {
                        content = content + "\"" + sourceCell.toString() + "\"";
                    } else {
                        content = content + "\"" + sourceCell.toString() + "\",";
                    }
                }

                content = content + SEPARATOR;
            }

            fw.write(content);
            if (fw != null) {
                fw.close();
            }

        } catch (Exception var12) {
            msg = msg + " error:" + var12.getMessage();
            throw new Exception(msg, var12);
        }
    }

    private void createHeaders(HSSFWorkbook workbook, HSSFSheet sheet, String[] headers) {
        HSSFRow row = sheet.createRow(0);

        for(int i = 0; i < headers.length; ++i) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

    }

    private String createHeaders(String[] headers) {
        String content = "";
        if (null != headers && headers.length > 0) {
            for(int i = 0; i < headers.length; ++i) {
                content = content + "\"" + headers[i] + "\",";
            }

            content = content.substring(0, content.length() - 1) + SEPARATOR;
        }

        return content;
    }

    private String createRows(Collection<T> dataset, String pattern) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        String content = "";

        for(Iterator it = dataset.iterator(); it.hasNext(); content = content.substring(0, content.length() - 1) + SEPARATOR) {
            T t = it.next();
            Field[] fields = t.getClass().getDeclaredFields();

            for(int i = 0; i < fields.length; ++i) {
                String textValue = this.getTextValue(pattern, t, fields, i);
                if (textValue != null) {
                    Matcher matcher = P.matcher(textValue);
                    if (matcher.matches()) {
                        content = content + "" + textValue + ",";
                    } else {
                        content = content + "\"" + textValue + "\",";
                    }
                }
            }
        }

        return content;
    }

    private void createRows(HSSFWorkbook workbook, HSSFSheet sheet, Collection<T> dataset, String pattern) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Iterator<T> it = dataset.iterator();
        int index = 0;
        HSSFFont font3 = workbook.createFont();

        while(it.hasNext()) {
            ++index;
            HSSFRow row = sheet.createRow(index);
            T t = it.next();
            Field[] fields = t.getClass().getDeclaredFields();

            for(int i = 0; i < fields.length; ++i) {
                HSSFCell cell = row.createCell(i);
                String textValue = this.getTextValue(pattern, t, fields, i);
                if (textValue != null) {
                    Matcher matcher = P.matcher(textValue);
                    if (matcher.matches()) {
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }
                }
            }
        }

    }

    private void createRows(HSSFWorkbook workbook, HSSFSheet sheet, List<List<String>> dataset, String pattern) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Iterator<List<String>> it = dataset.iterator();
        int index = 0;

        while(it.hasNext()) {
            ++index;
            HSSFRow row = sheet.createRow(index);
            List<String> t = (List)it.next();

            for(int i = 0; i < t.size(); ++i) {
                HSSFCell cell = row.createCell(i);
                String textValue = (String)t.get(i);
                if (textValue != null) {
                    Matcher matcher = P.matcher(textValue);
                    if (matcher.matches()) {
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        HSSFFont font3 = workbook.createFont();
                        richString.applyFont(font3);
                        cell.setCellValue(richString);
                    }
                }
            }
        }

    }

    public void exportCsv(String csvFile, String[] headers, boolean annotationHeaders, Collection<T> dataset, String pattern) throws Exception {
        String msg = "export csv csvFile=" + csvFile + " headers=" + headers + " annotationHeaders=" + annotationHeaders + " dataset=" + dataset + " pattern=" + pattern;

        try {
            String content = "";

            try {
                if (annotationHeaders) {
                    content = "#";
                }

                content = content + this.createHeaders(headers);
            } catch (Exception var11) {
                throw new Exception(" createHeaders error:" + var11.getMessage(), var11);
            }

            try {
                content = content + this.createRows(dataset, pattern);
            } catch (Exception var10) {
                throw new Exception(" createRows error:" + var10.getMessage(), var10);
            }

            try {
                FileWriter fw = new FileWriter(csvFile);
                fw.write(content);
                if (fw != null) {
                    fw.close();
                }

            } catch (IOException var9) {
                throw new Exception(" save file error:" + var9.getMessage(), var9);
            }
        } catch (Exception var12) {
            msg = msg + " error:" + var12.getMessage();
            throw new Exception(msg, var12);
        }
    }

    public void exportCsv(String csvFile, String[] headers, boolean annotationHeaders, Collection<T> dataset) throws Exception {
        this.exportCsv(csvFile, headers, annotationHeaders, dataset, "yyyyMMdd");
    }

    public void exportExcel(String excelFile, String title, String[] headers, Collection<T> dataset, String pattern) throws Exception {
        String msg = "export excel excelFile=" + excelFile + " title=" + title + " headers=" + headers + " dataset=" + dataset + " pattern=" + pattern;

        try {
            File excelin = new File(excelFile);
            HSSFWorkbook workbook;
            if (excelin.exists()) {
                FileInputStream excelFileInputStream = new FileInputStream(excelFile);
                workbook = new HSSFWorkbook(excelFileInputStream);
                excelFileInputStream.close();
            } else {
                workbook = new HSSFWorkbook();
            }

            HSSFSheet sheet;
            if (null != title && !title.isEmpty()) {
                sheet = workbook.createSheet(title);
            } else {
                sheet = workbook.createSheet();
            }

            sheet.setDefaultColumnWidth(15);

            try {
                this.createHeaders(workbook, sheet, headers);
            } catch (Exception var13) {
                throw new Exception(" createHeaders error:" + var13.getMessage(), var13);
            }

            try {
                this.createRows(workbook, sheet, dataset, pattern);
            } catch (Exception var12) {
                throw new Exception(" createRows error:" + var12.getMessage(), var12);
            }

            try {
                OutputStream out = new FileOutputStream(excelFile);
                workbook.write(out);
                out.close();
            } catch (IOException var11) {
                throw new Exception(" save file error:" + var11.getMessage(), var11);
            }
        } catch (Exception var14) {
            msg = msg + " error:" + var14.getMessage();
            throw new Exception(msg, var14);
        }
    }

    public void exportExcel(String excelFile, String title, String[] headers, List<List<String>> dataset, String pattern) throws Exception {
        String msg = "export excel excelFile=" + excelFile + " title=" + title + " headers=" + headers + " dataset=" + dataset + " pattern=" + pattern;

        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet;
            if (null != title && !title.isEmpty()) {
                sheet = workbook.createSheet(title);
            } else {
                sheet = workbook.createSheet();
            }

            sheet.setDefaultColumnWidth(15);

            try {
                this.createHeaders(workbook, sheet, headers);
            } catch (Exception var12) {
                throw new Exception(" createHeaders error:" + var12.getMessage(), var12);
            }

            try {
                this.createRows(workbook, sheet, dataset, pattern);
            } catch (Exception var11) {
                throw new Exception(" createRows error:" + var11.getMessage(), var11);
            }

            try {
                OutputStream out = new FileOutputStream(excelFile);
                workbook.write(out);
                out.close();
            } catch (IOException var10) {
                throw new Exception(" save file error:" + var10.getMessage(), var10);
            }
        } catch (Exception var13) {
            msg = msg + " error:" + var13.getMessage();
            throw new Exception(msg, var13);
        }
    }

    public void exportExcel(String excelFile, String[] headers, Collection<T> dataset) throws Exception {
        this.exportExcel(excelFile, (String)null, headers, (Collection)dataset, "yyyyMMdd");
    }

    public void exportExcel(String excelFile, String[] headers, List<List<String>> dataset) throws Exception {
        this.exportExcel(excelFile, (String)null, headers, (List)dataset, "yyyyMMdd");
    }

    private String getTextValue(String pattern, T t, Field[] fields, int i) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        Field field = fields[i];
        String fieldName = field.getName();
        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        Class<? extends Object> tCls = t.getClass();
        Method getMethod = tCls.getMethod(getMethodName);
        Object value = getMethod.invoke(t);
        String textValue = null;
        if (null != value && !"null".equals(value)) {
            if (value instanceof Boolean) {
                Boolean bValue = (Boolean)value;
                if (bValue) {
                    textValue = "Y";
                } else {
                    textValue = "";
                }
            } else if (value instanceof Date) {
                Date date = (Date)value;
                textValue = DateUtil.dateToString(date, pattern);
            } else {
                textValue = value.toString();
            }
        } else {
            textValue = "";
        }

        return textValue;
    }
}