话不多说,先上代码:
注意:这种写法只能压缩一个excel表
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
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.streaming.SXSSFWorkbook;
public class createexcel {
public static void main(String[] args) {
OutputStream os = null;
ZipOutputStream zipos = null;
try {
Workbook wb = new SXSSFWorkbook(100);
Date now1 = new Date();
//定义数据总量
long numcount = 3000000;
//定义每页数据有多少条
int pagesize = 10000;
System.out.println("打印数据总量为:"+numcount);
//获取页数
int pagenum = 0;
if(numcount % pagesize > 0) {
pagenum = (int) ((numcount/pagesize) + 1);
}else {
pagenum = (int) (numcount/pagesize);
}
//循环打印数据
for(int m = 0;m<pagenum;m++) {
//创建工作表
Sheet sheet = wb.createSheet("第"+(m+1)+"页");
//如果不是整数页
if(numcount % pagesize > 0) {
if(m<(pagenum-1)) {
for(int j = 0;j<pagesize;j++) {
//创建表头
Row head = sheet.createRow(j);
for(int i=1;i<11;i++) {
Cell cell = head.createCell(i-1);
cell.setCellValue(i);
}
}
}else {
//最后一页打印
for(int j = 0;j<(numcount % pagesize);j++) {
//创建表头
Row head = sheet.createRow(j);
for(int i=1;i<11;i++) {
Cell cell = head.createCell(i-1);
cell.setCellValue(i);
}
}
}
}else {
for(int j = 0;j<pagesize;j++) {
//创建表头
Row head = sheet.createRow(j);
for(int i=1;i<11;i++) {
Cell cell = head.createCell(i-1);
cell.setCellValue(i);
}
}
}
}
//保存到本地
os = new FileOutputStream("e://test.zip");
zipos = new ZipOutputStream(os);
ZipEntry entry = new ZipEntry("test.xls");
zipos.putNextEntry(entry);
wb.write(zipos);
zipos.flush();
/*
os = new FileOutputStream("e://test.xlsx");
wb.write(os);
os.flush();
*/
Date now2 = new Date();
System.out.println("共耗时:"+((now2.getTime()-now1.getTime())/1000)+"秒");
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
if(os!=null) {
try {
os.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(zipos!=null) {
try {
zipos.close();
} catch (Exception e2) {
// TODO: handle exception
e2.printStackTrace();
}
}
}
}
}
300w条数据,单单导出到excel表,大小是77m左右,如果压缩为zip,大小为3.8m,还是挺有效率的。
同时,在导出为excel表的时候,也试着导出9000w条数据,时间大概是970秒,excel文件大小是2g,但是有个问题,excel表无法打开。不知道是什么原因。