Java大批量数据导出实现方式

最新推荐文章于 2024-05-15 11:18:17 发布

weixin_34402408

最新推荐文章于 2024-05-15 11:18:17 发布

阅读量1.5k

点赞数

文章标签： java 数据库大数据

原文链接：http://www.cnblogs.com/chinaWang/p/3374111.html

版权

最近在改造项目中关于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;

}

转载于:https://www.cnblogs.com/chinaWang/p/3374111.html

weixin_34402408

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Java大批量数据导出实现方式

最近在改造项目中关于Excel导出功能，之前系统使用的jxl方式进行编写的，由于jxl是通过创建Cell单元格，然后把数据放入cell中进行保存的；故当前在创建cell的过程中势必会很耗费内存；为此，经项目组讨论后，通过IO方式生成Excel文件，这样会减少内存的占用量；针对大批量数据的导出(百万级别,笔者感觉提出这种需求的绝对的sb)，如果我们使用jxl或...
复制链接

扫一扫