import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
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 javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
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.HSSFFont;
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.util.CellRangeAddress;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
public class ExcelUtil {
public static String NO_DEFINE = "no_define";// 未定义的字段
public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日 HH:mm:ss";// 默认日期格式
public static int DEFAULT_COLOUMN_WIDTH = 20;
/**
* 导出Excel 97(.xls)格式
*
* @param title-标题行
* @param list-要输出的表单
* @param datePattern-日期格式，null则用默认日期格式
* @param colWidth-列宽，默认至少17个字节
* @param out-输出流
*/
public static void exportExcel(String title, List<FormExcelObject> list, 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.setAuthor("LAY"); // 填加xls文件作者信息
si.setApplicationName("**系统导出程序"); // 填加xls文件创建程序信息
si.setComments("LAY is a programmer!"); // 填加xls文件作者信息
si.setTitle(title + "数据"); // 填加xls文件标题信息
si.setSubject(title + "数据导出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.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.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);
for (int j = 0; j < list.size(); j++) {
FormExcelObject formExcelObject = list.get(j);
Map<String, String> headMap = formExcelObject.getHeadMap();
JSONArray jsonArray = formExcelObject.getJsonArray();
String _title = formExcelObject.getTitle();
int k = 1;
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet(_title + "-" + k);
// 设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;// 至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> 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(_title + "-" + (++k));
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth(i, arrColWidth[i] * 256);
}
}
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 < headers.length; i++) {
headerRow.createCell(i).setCellValue(headMap.get(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++;
}
}
try {
workbook.write(out);
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 通过向response中写入数据实现web下载
*
* @param title-文件名
* @param formExcelObjectList-表单数据
* @param response-要写入数据的响应
*/
public static void downloadExcelFile(String title, List<FormExcelObject> formExcelObjectList,
HttpServletResponse response) {
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
ExcelUtil.exportExcel(title, formExcelObjectList, 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 + ".xls").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 class FormExcelObject {
private String title;
private Map<String, String> headMap;
private JSONArray jsonArray;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public Map<String, String> getHeadMap() {
return headMap;
}
public void setHeadMap(Map<String, String> headMap) {
this.headMap = headMap;
}
public JSONArray getJsonArray() {
return jsonArray;
}
public void setJsonArray(JSONArray jsonArray) {
this.jsonArray = jsonArray;
}
}
public static void main(String[] args) throws IOException {
System.out.println("正在生成数据....");
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);
}
String jsonString = "{"name":"姓名","age":"年龄","birthday":"生日","height":"身高","weight":"体重","sex":"性别"}";
LinkedHashMap<String, String> headMap = JSON.parseObject(jsonString,
new TypeReference<LinkedHashMap<String, String>>() {
});
String title = "测试";
OutputStream outXlsx = new FileOutputStream("D://b.xls");
System.out.println("正在导出xls....");
Date d2 = new Date();
FormExcelObject feo = new FormExcelObject();
feo.setTitle("测试title");
feo.setHeadMap(headMap);
feo.setJsonArray(ja);
List<FormExcelObject> list = new ArrayList<FormExcelObject>();
list.add(feo);
ExcelUtil.exportExcel(title, list, null, 0, outXlsx);
System.out.println("共" + count + "条数据，执行耗时" + (new Date().getTime() - d2.getTime()) + "ms");
outXlsx.close();
}
public static class Student {
private String name;
private int age;
private Date birthday;
private float height;
private double weight;
private boolean sex;
public String getName() {
return name;
}
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;
}
}
}
maven项目需要在pom文件中加入以下
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.7</version>
</dependency>
非maven项目中加入jar
fastjson-1.2.7.jar
poi-3.13.jar
注：HttpServletResponse相关类为tomcat中带有的jar，此处不予列出