Util --poi完整导出

packageutil;

 

importjava.io.*;

importjava.lang.reflect.Method;

importjava.math.BigDecimal;

importjava.text.SimpleDateFormat;

importjava.util.ArrayList;

importjava.util.Date;

importjava.util.Iterator;

importjava.util.LinkedHashMap;

importjava.util.List;

importjava.util.Map;

 

importentity.Student;

importorg.apache.poi.hpsf.SummaryInformation;

importorg.apache.poi.hssf.usermodel.HSSFCell;

importorg.apache.poi.hssf.usermodel.HSSFCellStyle;

importorg.apache.poi.hssf.usermodel.HSSFClientAnchor;

importorg.apache.poi.hssf.usermodel.HSSFComment;

importorg.apache.poi.hssf.usermodel.HSSFFont;

importorg.apache.poi.hssf.usermodel.HSSFPatriarch;

importorg.apache.poi.hssf.usermodel.HSSFRichTextString;

importorg.apache.poi.hssf.usermodel.HSSFRow;

importorg.apache.poi.hssf.usermodel.HSSFSheet;

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;

importorg.apache.poi.ss.usermodel.CellStyle;

importorg.apache.poi.ss.usermodel.DateUtil;

importorg.apache.poi.ss.usermodel.Font;

importorg.apache.poi.ss.util.CellRangeAddress;

 

 

importcom.alibaba.fastjson.JSONArray;

importcom.alibaba.fastjson.JSONObject;

importorg.apache.poi.xssf.streaming.SXSSFCell;

importorg.apache.poi.xssf.streaming.SXSSFRow;

importorg.apache.poi.xssf.streaming.SXSSFSheet;

importorg.apache.poi.xssf.streaming.SXSSFWorkbook;

importorg.junit.Test;

 

importjavax.servlet.ServletOutputStream;

importjavax.servlet.http.HttpServletResponse;

 

publicclassExcelUtil{

publicstaticStringNO_DEFINE="no_define";//未定义的字段

publicstaticStringDEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式

publicstaticintDEFAULT_COLOUMN_WIDTH=17;

/**

*导出Excel97(.xls)格式，少量数据

*@paramtitle标题行

*@paramheadMap属性-列名

*@paramjsonArray数据集

*@paramdatePattern日期格式，null则用默认日期格式

*@paramcolWidth列宽默认至少17个字节

*@paramout输出流

*/

publicstaticvoidexportExcel(Stringtitle,Map<String,String>headMap,JSONArrayjsonArray,StringdatePattern,intcolWidth,OutputStreamout){

if(datePattern==null)datePattern=DEFAULT_DATE_PATTERN;

//声明一个工作薄

HSSFWorkbookworkbook=newHSSFWorkbook();

workbook.createInformationProperties();

workbook.getDocumentSummaryInformation().setCompany("*****公司");

SummaryInformationsi=workbook.getSummaryInformation();

si.setAuthor("JACK");//填加xls文件作者信息

si.setApplicationName("导出程序");//填加xls文件创建程序信息

si.setLastAuthor("最后保存者信息");//填加xls文件最后保存者信息

si.setComments("JACKisaprogrammer!");//填加xls文件作者信息

si.setTitle("POI导出Excel");//填加xls文件标题信息

si.setSubject("POI导出Excel");//填加文件主题信息

si.setCreateDateTime(newDate());

//表头样式

HSSFCellStyletitleStyle=workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFFonttitleFont=workbook.createFont();

titleFont.setFontHeightInPoints((short)20);

titleFont.setBoldweight((short)700);

titleStyle.setFont(titleFont);

//列头样式

HSSFCellStyleheaderStyle=workbook.createCellStyle();

headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFFontheaderFont=workbook.createFont();

headerFont.setFontHeightInPoints((short)12);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

//单元格样式

HSSFCellStylecellStyle=workbook.createCellStyle();

cellStyle.setFillForegroundColor((short)13);//设置背景色

cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

HSSFFontcellFont=workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

//生成一个(带标题)表格

HSSFSheetsheet=workbook.createSheet();

//声明一个画图的顶级管理器

HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();

//定义注释的大小和位置,详见文档

HSSFCommentcomment=patriarch.createComment(newHSSFClientAnchor(0,

0,0,0,(short)4,2,(short)6,5));

//设置注释内容

comment.setString(newHSSFRichTextString("可以在POI中添加注释！"));

//设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.

comment.setAuthor("JACK");

//设置列宽

intminBytes=colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数

int[]arrColWidth=newint[headMap.size()];

//产生表格标题行,以及设置列宽

String[]properties=newString[headMap.size()];

String[]headers=newString[headMap.size()];

intii=0;

for(Iterator<String>iter=headMap.keySet().iterator();iter

.hasNext();){

StringfieldName=iter.next();

 

properties[ii]=fieldName;

headers[ii]=fieldName;

 

intbytes=fieldName.getBytes().length;

arrColWidth[ii]=bytes<minBytes?minBytes:bytes;

sheet.setColumnWidth(ii,arrColWidth[ii]*256);

ii++;

}

//遍历集合数据，产生数据行

introwIndex=0;

for(Objectobj:jsonArray){

if(rowIndex==65535||rowIndex==0){

if(rowIndex!=0)sheet=workbook.createSheet();//如果数据超过了，则在第二页显示

 

HSSFRowtitleRow=sheet.createRow(0);//表头rowIndex=0

titleRow.createCell(0).setCellValue(title);

titleRow.getCell(0).setCellStyle(titleStyle);

sheet.addMergedRegion(newCellRangeAddress(0,0,0,headMap.size()-1));

 

HSSFRowheaderRow=sheet.createRow(1);//列头rowIndex=1

for(inti=0;i<headers.length;i++)

{

headerRow.createCell(i).setCellValue(headers[i]);

headerRow.getCell(i).setCellStyle(headerStyle);

 

}

rowIndex=2;//数据内容从rowIndex=2开始

}

JSONObjectjo=(JSONObject)JSONObject.toJSON(obj);

HSSFRowdataRow=sheet.createRow(rowIndex);

for(inti=0;i<properties.length;i++)

{

HSSFCellnewCell=dataRow.createCell(i);

 

Objecto=jo.get(properties[i]);

StringcellValue="";

if(o==null)cellValue="";

elseif(oinstanceofDate)cellValue=newSimpleDateFormat(datePattern).format(o);

elsecellValue=o.toString();

 

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

//自动调整宽度

/*for(inti=0;i<headers.length;i++){

sheet.autoSizeColumn(i);

}*/

try{

workbook.write(out);

out.flush();

out.close();

}catch(IOExceptione){

e.printStackTrace();

}

}

/**

*导出Excel2007OOXML(.xlsx)格式

*@paramtitle标题行

*@paramheadMap属性-列头

*@paramjsonArray数据集

*@paramdatePattern日期格式，传null值则默认年月日

*@paramcolWidth列宽默认至少17个字节

*@paramout输出流

*/

publicstaticvoidexportExcelX(Stringtitle,Map<String,String>headMap,JSONArrayjsonArray,StringdatePattern,intcolWidth,OutputStreamout){

if(datePattern==null)datePattern=DEFAULT_DATE_PATTERN;

//声明一个工作薄

SXSSFWorkbookworkbook=newSXSSFWorkbook(100000);//缓存

workbook.setCompressTempFiles(true);

//表头样式

CellStyletitleStyle=workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

FonttitleFont=workbook.createFont();

titleFont.setFontHeightInPoints((short)20);

titleFont.setBoldweight((short)700);

titleStyle.setFont(titleFont);

//列头样式

CellStyleheaderStyle=workbook.createCellStyle();

//headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

FontheaderFont=workbook.createFont();

headerFont.setFontHeightInPoints((short)15);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

//单元格样式

CellStylecellStyle=workbook.createCellStyle();

//cellStyle.setFillForegroundColor((short)11);//设置背景色

//cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

FontcellFont=workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

 

//生成一个(带标题)表格

SXSSFSheetsheet=workbook.createSheet();

 

//设置列宽

intminBytes=colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数

int[]arrColWidth=newint[headMap.size()];

 

//产生表格标题行,以及设置列宽

String[]properties=newString[headMap.size()];

String[]headers=newString[headMap.size()];

 

intii=0;

for(Iterator<String>iter=headMap.keySet().iterator();iter

.hasNext();){

StringfieldName=iter.next();

 

properties[ii]=fieldName;

headers[ii]=headMap.get(fieldName);

 

intbytes=fieldName.getBytes().length;

arrColWidth[ii]=bytes<minBytes?minBytes:bytes;

sheet.setColumnWidth(ii,arrColWidth[ii]*256);

ii++;

}

//遍历集合数据，产生数据行

introwIndex=0;

for(Objectobj:jsonArray){

if(rowIndex==65535||rowIndex==0){

if(rowIndex!=0)sheet=workbook.createSheet();//如果数据超过了，则在第二页显示

 

SXSSFRowtitleRow=sheet.createRow(0);//表头rowIndex=0

titleRow.createCell(0).setCellValue(title);

titleRow.getCell(0).setCellStyle(titleStyle);

sheet.addMergedRegion(newCellRangeAddress(0,0,0,headMap.size()-1));

 

SXSSFRowheaderRow=sheet.createRow(1);//列头rowIndex=1

for(inti=0;i<headers.length;i++)

{

headerRow.createCell(i).setCellValue(headers[i]);

headerRow.getCell(i).setCellStyle(headerStyle);

 

}

rowIndex=2;//数据内容从rowIndex=2开始

}

JSONObjectjo=(JSONObject)JSONObject.toJSON(obj);

SXSSFRowdataRow=sheet.createRow(rowIndex);

for(inti=0;i<properties.length;i++)

{

SXSSFCellnewCell=dataRow.createCell(i);

 

Objecto=jo.get(properties[i]);

StringcellValue="";

if(o==null)cellValue="";

elseif(oinstanceofDate)cellValue=newSimpleDateFormat(datePattern).format(o);

elseif(oinstanceofFloat||oinstanceofDouble)

cellValue=newBigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();

elsecellValue=o.toString();

 

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

//自动调整宽度

/*for(inti=0;i<headers.length;i++){

sheet.autoSizeColumn(i);

}*/

try{

workbook.write(out);

workbook.close();

workbook.dispose();

}catch(IOExceptione){

e.printStackTrace();

}

}

//Web导出excel

publicstaticvoiddownloadExcelFile(Stringtitle,Map<String,String>headMap,JSONArrayja,HttpServletResponseresponse){

try{

ByteArrayOutputStreamos=newByteArrayOutputStream();

ExcelUtil.exportExcelX(title,headMap,ja,null,0,os);

byte[]content=os.toByteArray();

InputStreamis=newByteArrayInputStream(content);

//设置response参数，可以打开下载页面

response.reset();

 

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");

response.setHeader("Content-Disposition","attachment;filename="+newString((title+".xlsx").getBytes(),"iso-8859-1"));

response.setContentLength(content.length);

ServletOutputStreamoutputStream=response.getOutputStream();

BufferedInputStreambis=newBufferedInputStream(is);

BufferedOutputStreambos=newBufferedOutputStream(outputStream);

byte[]buff=newbyte[8192];

intbytesRead;

while(-1!=(bytesRead=bis.read(buff,0,buff.length))){

bos.write(buff,0,bytesRead);

 

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch(Exceptione){

e.printStackTrace();

}

}

 

publicstaticvoidmain(String[]args)throwsIOException{

intcount=1000;

JSONArrayja=newJSONArray();

for(inti=0;i<1000;i++){

Students=newStudent();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(newDate());

s.setHeight(i);

s.setWeight(i);

s.setSex(i/2==0?false:true);

ja.add(s);

}

Map<String,String>headMap=newLinkedHashMap<String,String>();

headMap.put("name","姓名");

headMap.put("age","年龄");

headMap.put("birthday","生日");

headMap.put("height","身高");

headMap.put("weight","体重");

headMap.put("sex","性别");

 

Stringtitle="测试";

 

OutputStreamoutXlsx=newFileOutputStream("E://b.xlsx");

System.out.println("正在导出xlsx....");

Dated2=newDate();

ExcelUtil.exportExcelX(title,headMap,ja,null,17,outXlsx);

System.out.println("共"+count+"条数据,执行"+(newDate().getTime()-d2.getTime())+"ms");

outXlsx.close();

 

}

 

}

 

实体类

packageentity;

 

importjava.util.Date;

 

publicclassStudent{

 

privateStringname;

privateintage;

privateDatebirthday;

privatefloatheight;

privatedoubleweight;

privatebooleansex;

 

publicStringgetName(){

returnname;

}

 

publicvoidsetName(Stringname){

this.name=name;

}

 

publicIntegergetAge(){

returnage;

}

 

publicDategetBirthday(){

returnbirthday;

}

 

publicvoidsetBirthday(Datebirthday){

this.birthday=birthday;

}

 

publicfloatgetHeight(){

returnheight;

}

 

publicvoidsetHeight(floatheight){

this.height=height;

}

 

publicdoublegetWeight(){

returnweight;

}

 

publicvoidsetWeight(doubleweight){

this.weight=weight;

}

 

publicbooleanisSex(){

returnsex;

}

 

publicvoidsetSex(booleansex){

this.sex=sex;

}

 

publicvoidsetAge(Integerage){

this.age=age;

}

}

 

pom文件

<!--导入导出-->

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.14</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.14</version>

</dependency>