easyexcel

2019独角兽企业重金招聘Python工程师标准>>>

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.WriteHandler;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.beans.PropertyDescriptor;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class EasyExcelUtilV2 {

    /**
     * T为实体类，headAndKeys 的key 为要写入表格的属性名
     * @param filePath
     * @param sheetName
     * @param rows
     * @param headAndKeys
     * @param excelTypeEnum
     * @param writeHandler
     * @param valueFormatters
     * @param <T>
     */
    public static <T> void writeExcelByJavaPojo(String filePath, String sheetName, List<T> rows, Map<String, String> headAndKeys, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        String[] keys = headAndKeys.keySet().toArray(new String[headAndKeys.size()]);
        String[] heads = headAndKeys.values().toArray(new String[headAndKeys.size()]);
        writeExcelByJavaPojo(filePath, sheetName, rows, keys, heads, excelTypeEnum, writeHandler, valueFormatters);
    }

    /**
     * T为实体类，keys 为要写入表格的属性名
     * @param filePath
     * @param sheetName
     * @param rows
     * @param keys
     * @param heads
     * @param excelTypeEnum
     * @param writeHandler
     * @param valueFormatters
     * @param <T>
     */
    public static <T> void writeExcelByJavaPojo(String filePath, String sheetName, List<T> rows, String[] keys, String[] heads, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        byte[] bytes = getExcelBytesByJavaPojo(sheetName, rows, keys, heads, excelTypeEnum, writeHandler, valueFormatters);
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(filePath));
            outputStream.write(bytes);
            outputStream.flush();
        } catch (Throwable t) {
            throw new RuntimeException(t);
        } finally {
            try {
                outputStream.close();
            } catch (Exception e) {

            }
        }
    }

    /**
     * 单元格的值调用 T.toString() 生成
     * @param filePath
     * @param sheetName
     * @param rows
     * @param heads
     * @param excelTypeEnum
     * @param writeHandler
     * @param valueFormatters
     * @param <T>
     */
    public static <T> void writeExcelByList(String filePath, String sheetName, List<List<T>> rows, String[] heads, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        byte[] bytes = getExcelBytesByList(sheetName, rows, heads, excelTypeEnum, writeHandler, valueFormatters);
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File(filePath));
            outputStream.write(bytes);
            outputStream.flush();
        } catch (Throwable t) {
            throw new RuntimeException(t);
        } finally {
            try {
                outputStream.close();
            } catch (Exception e) {

            }
        }
    }

    /**
     * 单元格的值调用 T.toString() 生成
     * @param sheetName
     * @param rows
     * @param writeHandler
     * @param valueFormatters 格式转换
     * @param <T>
     * @return
     */
    public static <T> byte[] getExcelBytesByList(String sheetName, List<List<T>> rows, String[] heads, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream(1024);
        ExcelWriter excelWriter = new ExcelWriter(null, outputStream, excelTypeEnum, false, writeHandler);
        List<List<String>> datas = rows.stream().map(e -> e.stream().map(e2 -> format(e2, valueFormatters)).collect(Collectors.toList())).collect(Collectors.toList());
        Sheet sheet = new Sheet(1);
        sheet.setSheetName(sheetName);
        sheet.setStartRow(-1);
        if (heads != null) {
            datas.add(0, Arrays.asList(heads));
        }
        excelWriter.write0(datas, sheet);
        excelWriter.finish();
        return outputStream.toByteArray();
    }

    /**
     * T为实体类，headAndKeys 的key 为要写入表格的属性名
     * @param sheetName
     * @param rows
     * @param headAndKeys
     * @param excelTypeEnum
     * @param writeHandler
     * @param valueFormatters
     * @param <T>
     * @return
     */
    public static <T> byte[] getExcelBytesByJavaPojo(String sheetName, List<T> rows, Map<String, String> headAndKeys, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        String[] keys = headAndKeys.keySet().toArray(new String[headAndKeys.size()]);
        String[] heads = headAndKeys.values().toArray(new String[headAndKeys.size()]);
        List<List<Object>> datas = rows.stream().map(e -> parseFieldValue(e, keys)).collect(Collectors.toList());
        return getExcelBytesByList(sheetName, datas, heads, excelTypeEnum, writeHandler, valueFormatters);
    }

    /**
     * T为实体类，keys 为要写入表格的属性名
     * @param sheetName
     * @param rows
     * @param keys
     * @param writeHandler
     * @param valueFormatters
     * @param <T>
     * @return
     */
    public static <T> byte[] getExcelBytesByJavaPojo(String sheetName, List<T> rows, String[] keys, String[] heads, ExcelTypeEnum excelTypeEnum, WriteHandler writeHandler, Map<Class<?>, ValueFormatter> valueFormatters) {
        List<List<Object>> datas = rows.stream().map(e -> parseFieldValue(e, keys)).collect(Collectors.toList());
        return getExcelBytesByList(sheetName, datas, heads, excelTypeEnum, writeHandler, valueFormatters);
    }

    private static <T> List<Object> parseFieldValue(T obj, String[] fields) {
        return Arrays.asList(fields).stream().map(e -> {
            try {
                return new PropertyDescriptor(e, obj.getClass()).getReadMethod().invoke(obj);
            } catch (Throwable t) {
                throw new RuntimeException(t);
            }
        }).collect(Collectors.toList());
    }

    private static String format(Object value, Map<Class<?>, ValueFormatter> valueFormatters) {
        return valueFormatters != null && value != null ? valueFormatters.computeIfAbsent(value.getClass(), e -> ValueFormatter.DEFAULT_FORMATTER).format(value) : ValueFormatter.DEFAULT_FORMATTER.format(value);
    }

    public interface ValueFormatter {

        String format(Object value);

        ValueFormatter DEFAULT_FORMATTER = v -> {
            if (v == null) {
                return "";
            }
            if (v.getClass() == Date.class) {
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return simpleDateFormat.format((Date)v);
            }
            return v.toString();
        };
    }

    public static void main(String[] args) {
        List<List<String>> rows = IntStream.range(0, 1000).boxed().map(i -> IntStream.range(0, 10).boxed().map(j -> "" + j).collect(Collectors.toList())).collect(Collectors.toList());
        writeExcelByList("E:/test.xls", "你很好", rows, new String[]{"head1", "head2", "3", "4", "5", "6", "7", "8", "9", "10"}, ExcelTypeEnum.XLS, null, null);
    }

}

 

转载于:https://my.oschina.net/linchuhao23/blog/2996883