使用 POI创建一个简单的 Excel 文件

初级程序员一枚,看到公司大佬写的生成Excel文件,做下记录,同时也分享给大家参考

  ·······································实现原理是用workBook 然后还有OutputStream

首先是一个ExcelDataBean 这个实体类 用来 声明 Excel的最大行数,最大列数 以及用来保存  所传入的 标题列 内容列 并且对它们做出校验。

import java.util.ArrayList;import java.util.List;/** * 使用 Excel007Util 工具类创建 Excel 2007 文件所需要的数据类. * 包含多个包含标题信息的头列表 * 和多个内容行列表. * 超出 Excel 文件行列限制时会抛出异常, 不过没测试过这么大的数据. * * Created by gongsidalao on 17-5-10. */public class ExcelDataBean {    /**     * 2007的是1048576行、16384列.     */    public static final int MAX_ROW_SIZE = 1048576;    public static final int MAX_COL_SIZE = 16384;    private List<List<?>> headerList;    private List<List<?>> rowList;    public List<List<?>> getHeaderList() {        return headerList;    }    public List<List<?>> getRowList() {        return rowList;    }    private ExcelDataBean(List<List<?>> headerList, List<List<?>> rowList) {        this.headerList = headerList;        this.rowList = rowList;    }    public static class Builder {        private List<List<?>> headerList;        private List<List<?>> rowList;        public Builder() {            headerList = new ArrayList<>();            rowList = new ArrayList<>();        }        public Builder addHeaderList(List<?> header) {            if (header.size() > MAX_COL_SIZE) {                throw new RuntimeException("数据列超出 Excel 允许的最大值!");            }            if (headerList.size() + rowList.size() + 1 > MAX_ROW_SIZE) {                throw new RuntimeException("数据行超出 Excel 允许的最大值!");            }            this.headerList.add(header);            return this;        }        public Builder addRowList(List<?> row) {            if (row.size() > MAX_COL_SIZE) {                throw new RuntimeException("数据列超出 Excel 允许的最大值!");            }            if (headerList.size() + rowList.size() + 1 > MAX_ROW_SIZE) {                throw new RuntimeException("数据行超出 Excel 允许的最大值!");            }            this.rowList.add(row);            return this;        }        public ExcelDataBean build() {            return new ExcelDataBean(headerList, rowList);        }    }}

 然后下面就是一个工具类了 ,用于生成行row 生成单元格cell然后数据填充

import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.IOException;import java.io.OutputStream;import java.time.LocalDateTime;import java.time.ZoneId;import java.util.*;import java.util.stream.IntStream;/** * 使用 POI 创建一个简单的 Excel 文件 * Created by gongsidalao on 17-5-10. */public class Excel2007Util {    /**     *     * @param excelDataBean     * @param outputStream     * @throws IOException     */    public static void createExcel(final ExcelDataBean excelDataBean,                                   final OutputStream outputStream) throws IOException {        //创建工作簿对象用于创建Sheet        final Workbook workbook = new XSSFWorkbook();

        //创建一个Sheet(纸)对象也就是一个EXCEL        final Sheet sheet = workbook.createSheet();        //标题行数据List        final List<List<?>> headerList = excelDataBean.getHeaderList();        //根据标题列的大小创建几个带有标题行的Sheet        IntStream.range(0, headerList.size())                .forEach(i ->                        createRow(i, 0, headerList, sheet ));      //行偏移量用于创建数据行的时候 做行偏移处理 如果只有一个标题行的话可以写死为1        final int rowOffset = headerList.size();        final List<List<?>> rowList = excelDataBean.getRowList();        IntStream.range(0, rowList.size())                .forEach(i ->                        createRow(i, rowOffset, rowList, sheet ));        workbook.write(outputStream);    }    //创建行    private static void createRow(final int index, final int rowOffset,                                  final List<List<?>> rowList,                                  final Sheet sheet) {        List<?> data = rowList.get(index);        if (Objects.nonNull(data) && !data.isEmpty()) {            final Row row = sheet.createRow(index + rowOffset);            IntStream.range(0, data.size())                    .forEach(j ->                        createCell(j, data, row));        }    }   //创建单元格 并且填充data    private static void createCell(final int index, final List<?> data, final Row row) {        final Object obj = data.get(index);        final Cell cell = row.createCell(index);        if (obj == null) {            cell.setCellValue("");        } else if (Double.class.isInstance(obj)) {            cell.setCellValue((double) obj);        } else if (Date.class.isInstance(obj)) {            Date date = (Date) obj;            cell.setCellValue(LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault()).toString());        } else if (Calendar.class.isInstance(obj)) {            cell.setCellValue((Calendar) obj);        } else if (Boolean.class.isInstance(obj)) {            cell.setCellValue((boolean) obj);        } else {            final CreationHelper createHelper = row.getSheet().getWorkbook().getCreationHelper();            cell.setCellValue(createHelper.createRichTextString(obj.toString()));        }    }}

  附上一个测试类以便测试

import org.junit.Test;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.*;/** * Created by gongsidalao on 17-5-11. */public class Excel2007UtilTest {    @Test    public void TestUtil() {        List<String> header = Arrays.asList("姓名", "年龄", "生日", "记录创建时间", "婚否", "备注");        List<Object> list1 = new ArrayList<>();        list1.add("AA");        list1.add(18);        list1.add(Calendar.getInstance());        list1.add(new Date());        list1.add(true);        list1.add(null);        List<Object> list2 = new ArrayList<>();        list2.add("BB");        list2.add(18);        list2.add(Calendar.getInstance());        list2.add(new Date());        list2.add(true);        list2.add(null);        System.out.println(list1.get(0));        String url = "L:/ceshi.xlsx";        try (OutputStream outputStream = new FileOutputStream(url)) {            ExcelDataBean excelDataBean = new ExcelDataBean.Builder()                    .addHeaderList(header)//                  .addHeaderList(header)                    .addRowList(list1)                    .addRowList(list2)                    .build();            Excel2007Util.createExcel(excelDataBean, outputStream);        } catch (IOException e) {            e.printStackTrace();        }    }}

  有什么不足的还望大家指出来,高手轻喷,做个日常记录以便学习.