最近在改造项目中关于Excel导出功能,之前系统使用的jxl方式进行编写的,由于jxl是通过创建Cell单元格,然后把数据放入cell中进行保存的;故当前在创建cell的过程中势必会很耗费内存;为此,经项目组讨论后,通过IO方式生成Excel文件,这样会减少内存的占用量;
针对大批量数据的导出(百万级别,笔者感觉提出这种需求的绝对的sb),如果我们使用jxl或者poi插件方式实现,由于每次在保存数据到Excel中时,都要把每条数据放入到Cell对象中,然后才能保存数据;这样的话就会额外占用很多内存资源(如果100w条数50列的情况下,100w * 50,数据量相当巨大);所以,如果使用这种方案实现,只能分页获取对应的导出数据(一般是以5k条为一页或更少),这样处理起来内存占用率也不算太高,并且生成文件的速度也是较快的;但是,笔者发现在分页查询数据的时候会出现一个问题(在不使用order by排序的情况下):那就是各个分页的数据可能会出现重复的现象(除非对像主键这样的字段进行order by排序,但是这样如果查询的数据量比较多,当前为百万级别,无非是加大了数据库的负担,似乎有点得不偿失);故此种方案针对大数据量的导出不太适合;
此外就是使用IO方式的导出,这种方式是直接把内存中的数据保存到Excel中,并没有额外的创建Cell对象或其它对象,进而节省了封装数据到对象的内存资源;但是,该方案也就局限;那就是使用IO生成Excel时,只能生成一个sheet页,并且sheet页的名称和文件名称必须一样;故如果我们导出的数据量比较大的情况下,会有一个问题:生成的Excel只能有一个Sheet页,并且使用office 2007打开的时候会出现一个提示框(数据错误!);
当前由于数量较大(百万级别),如果全部读入到内存中的情况下,服务器也是负担不起的;所以,一般都是通过ResultSet读取数据,然后轮询ResultSet对象进行获取数据信息;
此处由于jxl插件和POI插件实现方式差不多,只是列出了POI的实现方式,jxl的就没有列出;另,由于笔者技术有限,有误的地方还望大侠校正,多提宝贵意见!!
附代码:
Poi方式:
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcelByPOI {
private Workbook workBook;
//标题数组
private String[] fields;
/***
* 增加sheet的標題
* @param sheet
* @param fields
*/
public void initHead(Sheet sheet,String[] fields){
this.fields = fields;
Row headRow = sheet.createRow(0);
for(int cellIndex = 0;cellIndex < fields.length;cellIndex ++){
Cell cell = headRow.createCell(cellIndex);
cell.setCellValue(fields[cellIndex]);
}
}
/**
* 增加excel的行
* @param valueMap
* @param sheet
*/
public void addExcelLine(Map<String, List<Object>> valueMap,Sheet sheet){
if(this.fields == null && this.fields.length == 0)
return;
//总记录数
int rowCount = valueMap.get(this.fields[0]).size();
for(int rowIndex = 1;rowIndex <= rowCount;rowIndex ++){
//添加行
Row row = sheet.createRow(rowIndex);
for(int cellIndex = 0;cellIndex < this.fields.length;cellIndex ++){
//添加行中的列数据
Cell cell = row.createCell(cellIndex);
cell.setCellValue((String)valueMap.get(this.fields[cellIndex]).get(rowIndex - 1));
}
}
}
public Sheet addSheet(){
Sheet sheet = this.workBook.createSheet();
return sheet;
}
public Sheet addSheet(String sheetName){
Sheet sheet = this.workBook.createSheet(sheetName);
return sheet;
}
public ExportExcelByPOI(){
}
/**
* 声明XSSFWorkbook对应workbook
* @param workBook
*/
public ExportExcelByPOI(XSSFWorkbook workBook){
this.workBook = workBook;
}
/**
* 声明HSSFWorkbook对应workbook
* @param workBook
*/
public ExportExcelByPOI(HSSFWorkbook workBook){
this.workBook = workBook;
}
public void write(OutputStream outputStream){
try {
this.workBook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public void exportExcel(Map<String, List<Object>> valueMap,String[] fields){
Sheet sheet = addSheet();
initHead(sheet, fields);
addExcelLine(valueMap, sheet);
}
/**
* 比较消耗内存
* @param resultSet
* @param outputStream
*/
public void exportExcel(ResultSet resultSet, OutputStream outputStream){
int rowIndex = 0;
try {
ResultSetMetaData metaData = resultSet.getMetaData();
if(metaData == null || metaData.getColumnCount() == 0){
//为空
return;
}
Sheet sheet = null;
while(resultSet.next()){
if(rowIndex % 60000 == 1){
sheet = this.workBook.createSheet();
}
Row row = sheet.createRow(rowIndex);
for(int cellIndex = 0;cellIndex < metaData.getColumnCount();cellIndex ++){
Cell cell = row.createCell(cellIndex);
switch (metaData.getColumnType(cellIndex + 1)) {
case Types.NUMERIC:
cell.setCellValue(resultSet.getDouble(cellIndex + 1));
break;
case Types.DATE:
cell.setCellValue(resultSet.getDate(cellIndex + 1));
break;
case Types.FLOAT:
cell.setCellValue(resultSet.getDouble(cellIndex + 1));
break;
default:
cell.setCellValue(resultSet.getString(cellIndex + 1));
break;
}
}
rowIndex ++;
}
this.workBook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel中的数据,并打印出
* @throws Exception
*/
public void readExcel() throws Exception{
int sheetCount = this.workBook.getNumberOfSheets();
for(int sheetIndex = 0;sheetIndex < sheetCount;sheetIndex ++){
Sheet sheet = workBook.getSheetAt(sheetIndex);
if(sheet == null){
throw new Exception(" sheet is null ,index is " + (sheetIndex + 1));
}
int rowCount = sheet.getPhysicalNumberOfRows();
for(int rowIndex = 0;rowIndex < rowCount;rowIndex ++){
Row row = sheet.getRow(rowIndex);
if(row == null){
throw new Exception("row is null , sheet index is " + (sheetIndex + 1) + " and row index is " + (rowIndex + 1));
}
int cellCount = row.getPhysicalNumberOfCells();
for(int cellIndex = 0;cellIndex < cellCount;cellIndex ++){
Cell cell = row.getCell(cellIndex);
/**
* 保存方式 具体而定
*/
System.out.println("sheet index is " + (sheetIndex + 1)
+ ", row index is " + (rowIndex + 1)
+ ", cell index is " + (cellIndex + 1)
+ ",value is " + cell.getStringCellValue());
}
}
}
}
/**
* 通过inputstream生成对应的workBook对象
* @param inputSteam
* @return
* @throws IOException
*/
public static Workbook createWorkBook(InputStream inputSteam) throws IOException{
if(!inputSteam.markSupported()){
inputSteam = new PushbackInputStream(inputSteam, 8);
}
/**
* 解析Excel 2003 及其之前的版本
*/
if(POIFSFileSystem.hasPOIFSHeader(inputSteam))
return new HSSFWorkbook(inputSteam);
if(POIXMLDocument.hasOOXMLHeader(inputSteam))
return new XSSFWorkbook(inputSteam);
throw new IOException("当前导入的Excel版本不符合规范,POI解析不了!!!!");
}
}
IO方式:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
/**
* 通过IO流导出Excel文件
* @author Administrator
*
*/
public class ExportExcelByIO {
private FileOutputStream wirter;
private int rowCount;
private String filePath;
private Map<String, String> columnName;
/**
* 通过构造函数实例化fileOutputstream对象
* @param filePath
*/
public ExportExcelByIO(String filePath){
this.filePath = filePath;
try {
this.wirter = new FileOutputStream(filePath);
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
/**
* 关闭流
*/
public void close(){
if(this.wirter != null){
try {
this.wirter.flush();
this.wirter.close();
} catch (Exception e) {
System.out.println("close inputstream throw Exception!");
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
private byte[] getBytes(short n){
byte[] b = new byte[2];
b[0] = (byte)(n & 0xff);
b[1] = (byte)(n >> 8 & 0xff);
return b;
}
private byte[] getBytes(float n){
/*byte[] b = new byte[2];
b[0] = (byte)(n & 0xff);
b[1] = (byte)(n >> 8 & 0xff);*/
return getBytes(new BigDecimal(String.valueOf(n)).doubleValue());
}
private byte[] getBytes(int n){
/*byte[] b = new byte[2];
b[0] = (byte)(n & 0xff);
b[1] = (byte)(n >> 8 & 0xff);*/
return getBytes(new BigDecimal(String.valueOf(n)).doubleValue());
}
private byte[] getBytes(double n){
byte[] b = new byte[8];
long v = Double.doubleToLongBits(n);
b[0] = (byte)(v >>> 0);
b[1] = (byte)(v >>> 8);
b[2] = (byte)(v >>> 16);
b[3] = (byte)(v >>> 24);
b[4] = (byte)(v >>> 32);
b[5] = (byte)(v >>> 40);
b[6] = (byte)(v >>> 48);
b[7] = (byte)(v >>> 56);
return b;
}
public byte[] getBytes(String s){
try {
return s.getBytes("GBK");
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public InputStream getInputStreamResult() throws IOException{
if(this.wirter != null){
endWrite();
}
return new FileInputStream(filePath);
}
/**
*
* @param values
*/
public void writeFile(short[] values){
try {
for(short v : values){
byte[] bs = getBytes(v);
this.wirter.write(bs, 0, bs.length);
this.wirter.flush();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成Excel文件的开始
*/
public void beginWriter(){
try {
writeFile(new short[]{ 0x09, 0x08, 0x00, 0x00, 0x00, 0x10, 0x00, 0x00, 0x00, 0x00});
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 结束流
*/
public void endWrite(){
try {
writeFile(new short[]{0xa,0});
wirter.flush();
wirter.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 插入number类型的数据到Excel
* @param x 指定行
* @param y 指定列
* @param value 对应的值
*/
public void writeNumber(short x,short y,float value){
try {
writeFile(new short[]{ 0x203, 14, x, y, 0});
byte[] b = getBytes(value);
wirter.write(b, 0, b.length);
wirter.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
public void writeNumber(int x,int y,float value){
writeNumber((short)x,(short)y,value);
}
public void writeString(short x,short y,String value){
byte[] b = getBytes(value);
try {
writeFile(new short[]{0x204,(short)(b.length + 8),x,y,0,(short)b.length});
wirter.write(b, 0, b.length);
wirter.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
public void writeString(int x,int y,String value){
writeString((short)x,(short)y,value);
}
public void writeNumber(short x,short y,int value){
byte[] b = getBytes(value);
try {
writeFile(new short[]{0x203,14,x,y,0});
wirter.write(b, 0, b.length);
wirter.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
public void writeNumber(int x,int y,int value){
writeNumber((short)x,(short)y,value);
}
public void writeNumber(short x,short y,double value){
byte[] b = getBytes(value);
try {
writeFile(new short[]{0x203,14,x,y,0});
wirter.write(b, 0, b.length);
wirter.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
public void writeNumber(int x,int y,double value){
writeNumber((short)x,(short)y,value);
}
public void addLine(int rows,String head[]){
if(rows < 0)
rows = 0;
for(int i = 0;head != null && i < head.length;i ++){
writeString(rows, i, head[i]);
}
}
public void addLine(String head[]){
addLine(0,head);
}
public void addLine(int rows,List<String> list){
if(rows < 0)
rows = 0;
for(int i = 0; list != null && i < list.size();i++){
writeString(rows, i, list.get(i));
}
}
public void addLine(List<String> list){
addLine(0,list);
}
public void addBean(List beans ,String fields[]) throws IllegalArgumentException, SecurityException, IllegalAccessException, InvocationTargetException, NoSuchMethodException{
String methodName = null;
Object params[] = new Object[0];
Class paramCls[] = new Class[0];
List<String> list = new ArrayList<String>();
for(Iterator iterator = beans.iterator();iterator.hasNext();){
Object valueObject = iterator.next();
int len = fields.length;
for(int j = 0;j < len;j ++){
String field = fields[j];
String temp;
if(field.startsWith("<number>")){
methodName = (new StringBuffer("get")).append(field.substring(8, 9).toUpperCase()).append(field.substring(9)).toString();
temp = "<number>" + String.valueOf(valueObject.getClass().getMethod(methodName, paramCls).invoke(valueObject, params));
}else{
methodName = (new StringBuffer("get")).append(field.substring(0, 1).toUpperCase()).append(field.substring(1)).toString();
temp = String.valueOf(valueObject.getClass().getMethod(methodName, paramCls).invoke(valueObject, params));
}
list.add(temp);
}
addLine(list);
list.clear();
}
}
/**
* 把Resultset中的数据导出为Excel
* @param resultSet
*/
public void ExportExcelByResultSet(ResultSet resultSet){
try {
int rowIndex = 1;//行下标
SimpleDateFormat dateFormat = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss");
//resultSet.getMetaData();
while(resultSet.next()){
for(String column : columnName.keySet()){
int cellIndex = 0;//列下标
if(columnName.get(column).equals("NUMBER")){
//number类型的数据
writeNumber(rowIndex, cellIndex, resultSet.getDouble(column));
}else if(columnName.get(column).equals("DATE")){
//日期类型的数据
if(resultSet.getDate(column) != null){
writeString(rowIndex, cellIndex, dateFormat.format(resultSet.getTimestamp(column)));
}else{
writeString(rowIndex, cellIndex, resultSet.getString(column));
}
}else{
//其他类型一律按照String处理
writeString(rowIndex, cellIndex, resultSet.getString(column));
}
cellIndex ++;
}
rowIndex ++;
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
public void exportExcel(Map<String, List<Object>> valueMap){
String[] fields = null;
fields = valueMap.keySet().toArray(fields);
if(fields != null && fields.length > 0){
int rowCount = valueMap.get(fields[0]).size();
for(int rowIndex = 1;rowIndex <= rowCount;rowIndex ++){
for(int columnIndex = 0;columnIndex < fields.length;columnIndex ++){
writeString(rowIndex,columnIndex,(String)valueMap.get(fields[columnIndex]).get(rowIndex));
}
}
}
}
/**
* 主函数部分
* @param args
*/
public static void main(String[] args) {
ExportExcelByIO writer = new ExportExcelByIO("E:\\temp\\test\\DownLoad\\excelIO001.xls");
System.out.println("begin export Excel:" + new Date().getTime());
writer.beginWriter();
String[] heads= new String[]{"column1","column2","column3","column4","column5","column6","column7","column8","column9","column10"};
writer.addLine(0, heads);
System.out.println("begin ddata: " + new Date().getTime());
Map<String, List<Object>> valueMap = new TreeMap<String, List<Object>>();
for(int rowIndex = 1;rowIndex <= 10000;rowIndex ++){
for(int columnIndex = 0;columnIndex < 10;columnIndex ++){
if(columnIndex == 0)
valueMap.put(heads[columnIndex], new ArrayList<Object>());
valueMap.get(heads[columnIndex]).add( "CCCC" + rowIndex + columnIndex);
//writer.writeString(rowIndex, columnIndex, "CCCC" + rowIndex + columnIndex);
}
}
writer.exportExcel(valueMap);
writer.endWrite();
System.out.println("end export Excel:" + new Date().getTime());
writer.close();
}
public void setColumnName(Map<String, String> columnName) {
this.columnName = columnName;
}
public Map<String, String> getColumnName() {
return columnName;
}
}