java hssfworkbook 导出_Java使用POI的SXSSFWorkbook与HSSFWorkbook导出复杂表头

packagecom.test.excel;importjava.io.BufferedOutputStream;importjava.io.File;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.HorizontalAlignment;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.VerticalAlignment;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.streaming.SXSSFSheet;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;public classSXSSFExecelPOI {public static voidmain(String[] args) {try{boolean createExcelFile =createExcelFile();if(createExcelFile) {

System.out.println("SXSSF_excel导出成功");

}else{

System.out.println("SXSSFexcel导出失败");

}

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}private static boolean createExcelFile() throwsException {boolean state = false;

OutputStream os= null;

FileOutputStream fos= null;

String resultFileName= null;

String fileName= "";try{

org.apache.poi.ss.usermodel.Workbook wb;

fileName= "kemu.xlsx";

wb=createReportWorkbook();

SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMdd");

resultFileName= "E:\\" + File.separator + "excel" + File.separator + sdf.format(newDate())+ File.separator +fileName;

resultFileName= resultFileName.replaceAll("\\\\", "/");

File file= newFile(resultFileName);if (!file.exists()) {

file.getParentFile().mkdirs();

}

fos= newFileOutputStream(file);

os= new BufferedOutputStream(fos, 1024);

wb.write(os);

os.flush();

state= true;

}finally{if (os != null)

os.close();

}returnstate;

}private staticWorkbook createReportWorkbook() {

String[] titles= { "时间", "科目", "科目", "科目", "科目", "备注"};

String[] handClum= { "0,1,0,0", "0,0,1,4", "0,1,5,5"};

String[] titles2= { "时间", "科目一", "科目二", "科目三", "科目四", "备注"};//100 指定Excel在屏幕尺寸下可滑动数据为100条

SXSSFWorkbook wb = new SXSSFWorkbook(100);

CellStyle style=wb.createCellStyle();

style.setAlignment(HorizontalAlignment.CENTER);//设置单元格水平居中

style.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格垂直居中

Sheet sheet = wb.createSheet("sheet1");for (int i = 0; i < titles.length; i++) {

sheet.setColumnWidth(i,5000);

}

Row row= sheet.createRow(0);//创建表头1

for (int i = 0; i < titles.length; i++) {

Cell cell=row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(titles[i]);

}//动态合并单元格

for (int i = 0; i < handClum.length; i++) {//sheet.autoSizeColumn(i, true);

String[] temp = handClum[i].split(",");

Integer startrow= Integer.parseInt(temp[0]);

Integer overrow= Integer.parseInt(temp[1]);

Integer startcol= Integer.parseInt(temp[2]);

Integer overcol= Integer.parseInt(temp[3]);

sheet.addMergedRegion(newCellRangeAddress(startrow, overrow, startcol, overcol));

}

row= sheet.createRow(1);//创建表头2

for (int i = 0; i < titles2.length; i++) {

Cell cell=row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(titles2[i]);

}

List kemus = new ArrayList();for (int i = 0; i < 1000; i++) {

keMu kemu= newkeMu();

kemu.setCreateDate("2018/11/06");

kemu.setKe1("理论" +i);

kemu.setKe2("模拟" +i);

kemu.setKe3("上机" +i);

kemu.setKe4("实操" +i);

kemu.setRemark("通过");

kemus.add(kemu);

}//填充数据

if (kemus != null && kemus.size() > 0) {for (int i = 0; i < kemus.size(); i++) {

Row contentRow= sheet.createRow(i + 2);//填充类容,从第2行开始,0行给表头

if (i % 100 == 0) {try{

((SXSSFSheet) sheet).flushRows(100);

}catch(IOException e) {

e.printStackTrace();

}

}for (int j = 0; j < titles2.length; j++) {

Cell cell=contentRow.createCell(j);

cell.setCellStyle(style);

keMu content=kemus.get(i);switch(j) {case 0:

cell.setCellValue(content.getCreateDate());break;case 1:

cell.setCellValue(content.getKe1());break;case 2:

cell.setCellValue(content.getKe2());break;case 3:

cell.setCellValue(content.getKe3());break;case 4:

cell.setCellValue(content.getKe4());break;case 5:

cell.setCellValue(content.getRemark());break;

}

}

}

}returnwb;

}static classkeMu {privateString createDate;privateString ke1;privateString ke2;privateString ke3;privateString ke4;privateString remark;publicString getCreateDate() {returncreateDate;

}public voidsetCreateDate(String createDate) {this.createDate =createDate;

}publicString getKe1() {returnke1;

}public voidsetKe1(String ke1) {this.ke1 =ke1;

}publicString getKe2() {returnke2;

}public voidsetKe2(String ke2) {this.ke2 =ke2;

}publicString getKe3() {returnke3;

}public voidsetKe3(String ke3) {this.ke3 =ke3;

}publicString getKe4() {returnke4;

}public voidsetKe4(String ke4) {this.ke4 =ke4;

}publicString getRemark() {returnremark;

}public voidsetRemark(String remark) {this.remark =remark;

}

}

}