POI通用导出Excel(.xls,.xlsx)的方法

POI通用导出Excel(.xls,.xlsx)的方法

POI操作EXCEL对象

HSSF：操作Excel 97(.xls)格式

XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL内存占用高于HSSF

SXSSF:从POI3.8 beta3开始支持，基于XSSF，低内存占用。

使用POI的HSSF对象，生成Excel 97(.xls)格式，生成的EXCEL不经过压缩直接导出。

线上问题：负载服务器转发请求到应用服务器阻塞,以及内存溢出 。

如果系统存在大数据量报表导出，则考虑使用POI的SXSSF进行EXCEL操作。

HSSF生成的Excel 97(.xls)格式本身就有每个sheet页不能超过65536条的限制。

XSSF生成Excel 2007 OOXML (.xlsx)格式，条数增加了，但是导出过程中，内存占用率却高于HSSF.

SXSSF是自3.8-beta3版本后，基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中，这样内存中只保存了少量的EXCEL行进行操作。

EXCEL的压缩率特别高，能达到80%，12M的文件压缩后才2M左右。 如果未经过压缩、不仅会占用用户带宽，且会导致负载服务器（apache）和应用服务器之间，长时间占用连接(二进制流转发)，导致负载服务器请求阻塞，不能提供服务。

一定要注意文件流的关闭

防止前台（页面）连续触发导出EXCEL

1.通用核心导出工具类 ExcelUtil.java

package sy.util;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.Iterator;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.hpsf.SummaryInformation;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

import org.apache.poi.hssf.usermodel.HSSFComment;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFPatriahttp://rch;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.formula.functions.T;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.DateUtil;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.streaming.SXSSFCell;

import org.apache.poi.xssf.streaming.SXSSFRow;

import org.apache.poi.xssf.streaming.SXSSFSheet;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.alibaba.fastjson.JSONArray;

import com.alibaba.fastjson.JSONObject;

public class ExcelUtil{

public static String NO_DEFINE = "no_define";//未定义的字段

public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式

public static int DEFAULT_COLOUMN_WIDTH = 17;

/**

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

* @param title 标题行

* @param headMap 属性-列名

* @param jsonArray 数据集

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

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

* @param out 输出流

*/

public static void exportExcel(String title,Map headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {

if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;

// 声明一个工作薄

HSSFWorkbook workbook = new HSSFWorkbook();

workbook.createInformationProperties();

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

SummaryInformation si = workbook.getSummaryInformation();

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

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

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

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

si.setCreateDateTime(new Date());

//表头样式

HSSFCellStyle titleStyle = workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFFont titleFont = workbook.createFont();

titleFont.setFontHeightInPoints((short) 20);

titleFont.setBoldweight((short) 700);

titleStyle.setFont(titleFont);

// 列头样式

HSSFCellStyle headerStyle = 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);

HSSFFont headerFont = workbook.createFont();

headerFont.setFontHeightInPoints((short) 12);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

// 单元格样式

HSSFCellStyle cellStyle = workbook.createCellStyle();

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);

HSSFFont cellFont = workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

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

HSSFSheet sheet = workbook.createSheet();

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

HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

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

HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,

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

// 设置注释内容

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

comment.setAuthor("JACK");

//设置列宽

int minBytes = colWidth

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

headers[ii] = fieldName;

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

HSSFRow dataRow = sheet.createRow(rowIndex);

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

{

HSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 导出Excel 2007 OOXML (.xlsx)格式

* @param title 标题行

* @param headMap 属性-列头

* @param jsonArray 数据集

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

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

* @param out 输出流

*/

public static void exportExcelX(String title,Map headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {

if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;

// 声明一个工作薄

SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存

workbook.setCompressTempFiles(true);

//表头样式

CellStyle titleStyle = workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

Font titleFont = workbook.createFont();

titleFont.setFontHeightInPoints((short) 20);

titleFont.setBoldweight((short) 700);

titleStyle.setFont(titleFont);

// 列头样式

CellStyle headerStyle = 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);

Font headerFont = workbook.createFont();

headerFont.setFontHeightInPoints((short) 12);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

// 单元格样式

CellStyle cellStyle = workbook.createCellStyle();

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);

cellSLAwNutyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

Font cellFont = workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

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

SXSSFSheet sheet = workbook.createSheet();

//设置列宽

int minBytes = colWidth

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

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

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

SXSSFRow dataRow = sheet.createRow(rowIndex);

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

{

SXSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else if(o instanceof Float || o instanceof Double)

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

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

workbook.dispose();

} catch (IOException e) {

e.printStackTrace();

}

}

//Web 导出excel

public static void downloadExcelFile(String title,Map headMap,JSONArray ja,HttpServletResponse response){

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

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

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

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

response.reset();

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

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

response.setContentLength(content.length);

ServletOutputStream outputStream = response.getOutputStream();

BufferedInputStream bis = new BufferedInputStream(is);

BufferedOutputStream bos = new BufferedOutputStream(outputStream);

byte[] buff = new byte[8192];

int bytesRead;

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

bos.write(buff, 0, bytesRead);

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException {

int count = 100000;

JSONArray ja = new JSONArray();

for(int i=0;i<100000;i++){

Student s = new Student();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(new Date());

s.setHeight(i);

s.setWeight(i);

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

ja.add(s);

}

Map headMap = new LinkedHashMap();

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

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

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

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

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

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

String title = "测试";

/*

OutputStream outXls = new FileOutputStream("E://a.xls");

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

Date d = new Date();

ExcelUtil.exportExcel(title,headMap,ja,null,outXls);

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

outXls.close();*/

//

OutputStream outXlsx = new FileOutputStream("E://b.xlsx");

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

Date d2 = new Date();

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

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

outXlsx.close();

}

}

class Student {

private String name;

private int age;

private Date birthday;

private float height;

private double weight;

private boolean sex;

public String getName() {

return namehttp://;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public float getHeight() {

return height;

}

public void setHeight(float height) {

this.height = height;

}

public double getWeight() {

return weight;

}

public void setWeight(double weight) {

this.weight = weight;

}

public boolean isSex() {

return sex;

}

public void setSex(boolean sex) {

this.sex = sex;

}

public void setAge(Integer age) {

this.age = age;

}

}

2. 控制器Controller 的写法

//导出配件列表

@RequestMapping(value = "partExport")

@ResponseBody

public void partExportHttpServletResponse response){

JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集

Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头

String title = "配件统计表";

ExcelUtil.downloadExcelFile(title,headMap,ja,response);

}

3.前端页面的写法（不要用异步方式请求，如$.post）

//可以点击一个按钮事件触发下面的代码进行导出

window.open("partExport","_blank");

//或者可以提交表单

$('#form').attr('action','partExport');

$('#form').attr('target','_blank');

$('#form').submit();

4.POI依赖的jar包（maven pom)

org.apache.poi

poi-ooxml

3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

headers[ii] = fieldName;

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

HSSFRow dataRow = sheet.createRow(rowIndex);

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

{

HSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 导出Excel 2007 OOXML (.xlsx)格式

* @param title 标题行

* @param headMap 属性-列头

* @param jsonArray 数据集

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

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

* @param out 输出流

*/

public static void exportExcelX(String title,Map headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {

if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;

// 声明一个工作薄

SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存

workbook.setCompressTempFiles(true);

//表头样式

CellStyle titleStyle = workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

Font titleFont = workbook.createFont();

titleFont.setFontHeightInPoints((short) 20);

titleFont.setBoldweight((short) 700);

titleStyle.setFont(titleFont);

// 列头样式

CellStyle headerStyle = 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);

Font headerFont = workbook.createFont();

headerFont.setFontHeightInPoints((short) 12);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

// 单元格样式

CellStyle cellStyle = workbook.createCellStyle();

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);

cellSLAwNutyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

Font cellFont = workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

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

SXSSFSheet sheet = workbook.createSheet();

//设置列宽

int minBytes = colWidth

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

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

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

SXSSFRow dataRow = sheet.createRow(rowIndex);

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

{

SXSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else if(o instanceof Float || o instanceof Double)

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

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

workbook.dispose();

} catch (IOException e) {

e.printStackTrace();

}

}

//Web 导出excel

public static void downloadExcelFile(String title,Map headMap,JSONArray ja,HttpServletResponse response){

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

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

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

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

response.reset();

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

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

response.setContentLength(content.length);

ServletOutputStream outputStream = response.getOutputStream();

BufferedInputStream bis = new BufferedInputStream(is);

BufferedOutputStream bos = new BufferedOutputStream(outputStream);

byte[] buff = new byte[8192];

int bytesRead;

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

bos.write(buff, 0, bytesRead);

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException {

int count = 100000;

JSONArray ja = new JSONArray();

for(int i=0;i<100000;i++){

Student s = new Student();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(new Date());

s.setHeight(i);

s.setWeight(i);

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

ja.add(s);

}

Map headMap = new LinkedHashMap();

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

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

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

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

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

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

String title = "测试";

/*

OutputStream outXls = new FileOutputStream("E://a.xls");

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

Date d = new Date();

ExcelUtil.exportExcel(title,headMap,ja,null,outXls);

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

outXls.close();*/

//

OutputStream outXlsx = new FileOutputStream("E://b.xlsx");

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

Date d2 = new Date();

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

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

outXlsx.close();

}

}

class Student {

private String name;

private int age;

private Date birthday;

private float height;

private double weight;

private boolean sex;

public String getName() {

return namehttp://;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public float getHeight() {

return height;

}

public void setHeight(float height) {

this.height = height;

}

public double getWeight() {

return weight;

}

public void setWeight(double weight) {

this.weight = weight;

}

public boolean isSex() {

return sex;

}

public void setSex(boolean sex) {

this.sex = sex;

}

public void setAge(Integer age) {

this.age = age;

}

}

2. 控制器Controller 的写法

//导出配件列表

@RequestMapping(value = "partExport")

@ResponseBody

public void partExportHttpServletResponse response){

JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集

Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头

String title = "配件统计表";

ExcelUtil.downloadExcelFile(title,headMap,ja,response);

}

3.前端页面的写法（不要用异步方式请求，如$.post）

//可以点击一个按钮事件触发下面的代码进行导出

window.open("partExport","_blank");

//或者可以提交表单

$('#form').attr('action','partExport');

$('#form').attr('target','_blank');

$('#form').submit();

4.POI依赖的jar包（maven pom)

org.apache.poi

poi-ooxml

3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

{

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

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

}

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

}

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

HSSFRow dataRow = sheet.createRow(rowIndex);

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

{

HSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 导出Excel 2007 OOXML (.xlsx)格式

* @param title 标题行

* @param headMap 属性-列头

* @param jsonArray 数据集

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

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

* @param out 输出流

*/

public static void exportExcelX(String title,Map headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) {

if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN;

// 声明一个工作薄

SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存

workbook.setCompressTempFiles(true);

//表头样式

CellStyle titleStyle = workbook.createCellStyle();

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

Font titleFont = workbook.createFont();

titleFont.setFontHeightInPoints((short) 20);

titleFont.setBoldweight((short) 700);

titleStyle.setFont(titleFont);

// 列头样式

CellStyle headerStyle = 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);

Font headerFont = workbook.createFont();

headerFont.setFontHeightInPoints((short) 12);

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerStyle.setFont(headerFont);

// 单元格样式

CellStyle cellStyle = workbook.createCellStyle();

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);

cellSLAwNutyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

Font cellFont = workbook.createFont();

cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

cellStyle.setFont(cellFont);

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

SXSSFSheet sheet = workbook.createSheet();

//设置列宽

int minBytes = colWidth

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

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

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

SXSSFRow dataRow = sheet.createRow(rowIndex);

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

{

SXSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else if(o instanceof Float || o instanceof Double)

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

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

workbook.dispose();

} catch (IOException e) {

e.printStackTrace();

}

}

//Web 导出excel

public static void downloadExcelFile(String title,Map headMap,JSONArray ja,HttpServletResponse response){

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

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

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

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

response.reset();

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

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

response.setContentLength(content.length);

ServletOutputStream outputStream = response.getOutputStream();

BufferedInputStream bis = new BufferedInputStream(is);

BufferedOutputStream bos = new BufferedOutputStream(outputStream);

byte[] buff = new byte[8192];

int bytesRead;

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

bos.write(buff, 0, bytesRead);

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException {

int count = 100000;

JSONArray ja = new JSONArray();

for(int i=0;i<100000;i++){

Student s = new Student();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(new Date());

s.setHeight(i);

s.setWeight(i);

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

ja.add(s);

}

Map headMap = new LinkedHashMap();

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

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

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

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

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

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

String title = "测试";

/*

OutputStream outXls = new FileOutputStream("E://a.xls");

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

Date d = new Date();

ExcelUtil.exportExcel(title,headMap,ja,null,outXls);

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

outXls.close();*/

//

OutputStream outXlsx = new FileOutputStream("E://b.xlsx");

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

Date d2 = new Date();

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

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

outXlsx.close();

}

}

class Student {

private String name;

private int age;

private Date birthday;

private float height;

private double weight;

private boolean sex;

public String getName() {

return namehttp://;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public float getHeight() {

return height;

}

public void setHeight(float height) {

this.height = height;

}

public double getWeight() {

return weight;

}

public void setWeight(double weight) {

this.weight = weight;

}

public boolean isSex() {

return sex;

}

public void setSex(boolean sex) {

this.sex = sex;

}

public void setAge(Integer age) {

this.age = age;

}

}

2. 控制器Controller 的写法

//导出配件列表

@RequestMapping(value = "partExport")

@ResponseBody

public void partExportHttpServletResponse response){

JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集

Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头

String title = "配件统计表";

ExcelUtil.downloadExcelFile(title,headMap,ja,response);

}

3.前端页面的写法（不要用异步方式请求，如$.post）

//可以点击一个按钮事件触发下面的代码进行导出

window.open("partExport","_blank");

//或者可以提交表单

$('#form').attr('action','partExport');

$('#form').attr('target','_blank');

$('#form').submit();

4.POI依赖的jar包（maven pom)

org.apache.poi

poi-ooxml

3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

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

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

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

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

int ii = 0;

for (Iterator iter = headMap.keySet().iterator(); iter

.hasNext();) {

String fieldName = iter.next();

properties[ii] = fieldName;

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

int bytes = fieldName.getBytes().length;

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

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

ii++;

}

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

int rowIndex = 0;

for (Object obj : jsonArray) {

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

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

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

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

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

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

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

for(int i=0;i

{

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

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

}

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

}

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

SXSSFRow dataRow = sheet.createRow(rowIndex);

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

{

SXSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else if(o instanceof Float || o instanceof Double)

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

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

workbook.dispose();

} catch (IOException e) {

e.printStackTrace();

}

}

//Web 导出excel

public static void downloadExcelFile(String title,Map headMap,JSONArray ja,HttpServletResponse response){

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

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

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

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

response.reset();

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

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

response.setContentLength(content.length);

ServletOutputStream outputStream = response.getOutputStream();

BufferedInputStream bis = new BufferedInputStream(is);

BufferedOutputStream bos = new BufferedOutputStream(outputStream);

byte[] buff = new byte[8192];

int bytesRead;

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

bos.write(buff, 0, bytesRead);

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException {

int count = 100000;

JSONArray ja = new JSONArray();

for(int i=0;i<100000;i++){

Student s = new Student();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(new Date());

s.setHeight(i);

s.setWeight(i);

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

ja.add(s);

}

Map headMap = new LinkedHashMap();

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

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

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

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

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

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

String title = "测试";

/*

OutputStream outXls = new FileOutputStream("E://a.xls");

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

Date d = new Date();

ExcelUtil.exportExcel(title,headMap,ja,null,outXls);

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

outXls.close();*/

//

OutputStream outXlsx = new FileOutputStream("E://b.xlsx");

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

Date d2 = new Date();

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

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

outXlsx.close();

}

}

class Student {

private String name;

private int age;

private Date birthday;

private float height;

private double weight;

private boolean sex;

public String getName() {

return namehttp://;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public float getHeight() {

return height;

}

public void setHeight(float height) {

this.height = height;

}

public double getWeight() {

return weight;

}

public void setWeight(double weight) {

this.weight = weight;

}

public boolean isSex() {

return sex;

}

public void setSex(boolean sex) {

this.sex = sex;

}

public void setAge(Integer age) {

this.age = age;

}

}

2. 控制器Controller 的写法

//导出配件列表

@RequestMapping(value = "partExport")

@ResponseBody

public void partExportHttpServletResponse response){

JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集

Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头

String title = "配件统计表";

ExcelUtil.downloadExcelFile(title,headMap,ja,response);

}

3.前端页面的写法（不要用异步方式请求，如$.post）

//可以点击一个按钮事件触发下面的代码进行导出

window.open("partExport","_blank");

//或者可以提交表单

$('#form').attr('action','partExport');

$('#form').attr('target','_blank');

$('#form').submit();

4.POI依赖的jar包（maven pom)

org.apache.poi

poi-ooxml

3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

{

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

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

}

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

}

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

SXSSFRow dataRow = sheet.createRow(rowIndex);

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

{

SXSSFCell newCell = dataRow.createCell(i);

Object o = jo.get(properties[i]);

String cellValue = "";

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

else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);

else if(o instanceof Float || o instanceof Double)

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

else cellValue = o.toString();

newCell.setCellValue(cellValue);

newCell.setCellStyle(cellStyle);

}

rowIndex++;

}

// 自动调整宽度

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

sheet.autoSizeColumn(i);

}*/

try {

workbook.write(out);

workbook.close();

workbook.dispose();

} catch (IOException e) {

e.printStackTrace();

}

}

//Web 导出excel

public static void downloadExcelFile(String title,Map headMap,JSONArray ja,HttpServletResponse response){

try {

ByteArrayOutputStream os = new ByteArrayOutputStream();

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

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

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

response.reset();

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

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

response.setContentLength(content.length);

ServletOutputStream outputStream = response.getOutputStream();

BufferedInputStream bis = new BufferedInputStream(is);

BufferedOutputStream bos = new BufferedOutputStream(outputStream);

byte[] buff = new byte[8192];

int bytesRead;

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

bos.write(buff, 0, bytesRead);

}

bis.close();

bos.close();

outputStream.flush();

outputStream.close();

}catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws IOException {

int count = 100000;

JSONArray ja = new JSONArray();

for(int i=0;i<100000;i++){

Student s = new Student();

s.setName("POI"+i);

s.setAge(i);

s.setBirthday(new Date());

s.setHeight(i);

s.setWeight(i);

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

ja.add(s);

}

Map headMap = new LinkedHashMap();

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

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

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

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

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

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

String title = "测试";

/*

OutputStream outXls = new FileOutputStream("E://a.xls");

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

Date d = new Date();

ExcelUtil.exportExcel(title,headMap,ja,null,outXls);

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

outXls.close();*/

//

OutputStream outXlsx = new FileOutputStream("E://b.xlsx");

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

Date d2 = new Date();

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

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

outXlsx.close();

}

}

class Student {

private String name;

private int age;

private Date birthday;

private float height;

private double weight;

private boolean sex;

public String getName() {

return namehttp://;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public float getHeight() {

return height;

}

public void setHeight(float height) {

this.height = height;

}

public double getWeight() {

return weight;

}

public void setWeight(double weight) {

this.weight = weight;

}

public boolean isSex() {

return sex;

}

public void setSex(boolean sex) {

this.sex = sex;

}

public void setAge(Integer age) {

this.age = age;

}

}

2. 控制器Controller 的写法

//导出配件列表

@RequestMapping(value = "partExport")

@ResponseBody

public void partExportHttpServletResponse response){

JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集

Map headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头

String title = "配件统计表";

ExcelUtil.downloadExcelFile(title,headMap,ja,response);

}

3.前端页面的写法（不要用异步方式请求，如$.post）

//可以点击一个按钮事件触发下面的代码进行导出

window.open("partExport","_blank");

//或者可以提交表单

$('#form').attr('action','partExport');

$('#form').attr('target','_blank');

$('#form').submit();

4.POI依赖的jar包（maven pom)

org.apache.poi

poi-ooxml

3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，会及时回复大家的。在此也非常感谢大家对我们网站的支持！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。