java后端导出excel及自适应列宽
调用方法
// An highlighted block
ExportBigExcel ex = new ExportBigExcel(title, rowname, list);
File file = new File(filepathdemand);
if (!file.exists()) {
file.mkdir();// 创建该文件夹目录
}
String name ="维修周期信息" + ".xlsx";
OutputStream os = new BufferedOutputStream( new FileOutputStream(file.getAbsolutePath() + "/" + name));
ex.export1(os);
ExportBigExcel的方法
// 显示的导出表的标题
private String title;
// 导出表的列名
private String[] rowName;
List<Object[]> dataList;
private Row nRow = null; //行对象
private Cell nCell = null; //列对象
// 构造函数,传入要导出的数据
//导出时,传入List
public ExportBigExcel(String title, String[] rowName, List<Object[]> dataList) {
this.dataList = dataList;
this.rowName = rowName;
this.title = title;
}
//写入excel到服务器上
// 与传入list配套使用,大量数据处理快速
public void export2(OutputStream out) throws Exception {
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
if(title==null || title.equals("")){
title="sheet_";
}
Sheet sheet = workbook.createSheet(title);
//存储最大列宽
Map<Integer,Integer> maxWidth = new HashMap<Integer,Integer>();
// 定义所需列数
int columnNum = rowName.length;
Row rowRowName = sheet.createRow(0);
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
Cell cellRowName = rowRowName.createCell(n);
cellRowName.setCellType(SXSSFCell.CELL_TYPE_STRING);
XSSFRichTextString text = new XSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text);
maxWidth.put(n,cellRowName.getStringCellValue().getBytes().length * 256 + 200);
}
int rowNo = 0; //总行号
int pageRowNo = 1; //页行号
for(Object[] dataObject:dataList) {
//打印1000000条后切换到下个工作表,可根据需要自行拓展,2百万,3百万...数据一样操作,只要不超过1048576就可以
if(rowNo%1000000==0){
System.out.println("Current Sheet:" + rowNo/1000000);
sheet = workbook.createSheet("sheet"+(rowNo/1000000));//建立新的sheet对象
sheet = workbook.getSheetAt(rowNo/1000000); //动态指定当前的工作表
pageRowNo = 1; //每当新建了工作表就将当前工作表的行号重置为0
}
rowNo++;
nRow = sheet.createRow(pageRowNo++); //新建行对象
// 打印每行, rsmd.getColumnCount() --- 列属性的个数
for(int j=0;j<columnNum;j++){
nCell = nRow.createCell(j,Cell.CELL_TYPE_STRING);
nCell.setCellValue(dataObject[j]!=null?dataObject[j].toString():"");
int length=0;
if (nCell.getStringCellValue()!=null)
length = nCell.getStringCellValue().getBytes().length * 256 + 200;
//这里把宽度最大限制到15000
if (length>15000){
length = 15000;
}
maxWidth.put(j,Math.max(length,maxWidth.get(j)));
}
if(rowNo%10000==0){
System.out.println("row no: " + rowNo);
}
}
for (int i = 0; i < columnNum; i++) {
// sheet.autoSizeColumn(i);
// sheet.setColumnWidth(i,sheet.getColumnWidth(i)*17/10);
sheet.setColumnWidth(i,maxWidth.get(i));
}
sheet.setDefaultRowHeight((short) (2 * 256));
try {
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}
}