初级程序员一枚,看到公司大佬写的生成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();
}
}
}
有什么不足的还望大家指出来,高手轻喷,做个日常记录以便学习.