可以多次写入excel,避免内存溢出
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.junit.Test;
/**
* 大数据测试
* @author liubenlong
*
*/
public class BigDataExcelOut {
/**
* 测试导出--根据模板
* @throws Exception
*/
@Test
void testExportByTemplete() throws Exception {
String fileName = "f:\\世界五百强企业名次表.xls";
FileInputStream inputStream = new FileInputStream(new File(fileName));
Workbook wb = new HSSFWorkbook(inputStream);
Sheet sheet = wb.getSheetAt(0);
String[] a = {"11","北京","xxx","y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y"};
/**
* 可以多次写入,避免内存溢出问题
*/
for (int h = 6 ; h < 8 ; h ++) {
a[0] = h + "";
for (int j = h*5000; j < 5000*(h+1); j++) {
Row row = sheet.createRow(j);
for (int i = 0 ; i <a.length ; i ++) {
Cell cell = row.createCell(i);
cell.setCellValue(a[i]);
}
}
FileOutputStream out=new FileOutputStream(fileName);
out.flush();
wb.write(out);
out.close();
}
System.out.println(sheet.getLastRowNum());
}
/**
* 测试导入
* @throws Exception
*/
@Test
void testImport() throws Exception {
String fileName = "f:\\世界五百强企业名次表.xls";
FileInputStream inputStream = new FileInputStream(new File(fileName));
Workbook wb = new HSSFWorkbook(inputStream);
Sheet sheet = wb.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
for (Row row : sheet) {
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for (Cell cell : row) {
System.out.print(cell.getStringCellValue());
}
System.out.println();
}
inputStream.close();
System.out.println(sheet.getLastRowNum());
}
/**
* 测试导出
* @throws Exception
*/
@Test
void testExport() throws Exception {
String fileName = "f:\\世界五百强企业名次表.xls";
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("2222");
String[] a = {"11","北京","xxx","y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y","y谁谁谁谁谁谁谁谁谁谁谁谁水水水水谁谁谁y"};
/**
* 可以多次写入,避免内存溢出问题
*/
for (int h = 1 ; h < 5 ; h ++) {
a[0] = h + "";
for (int j = h*5000; j < 5000*(h+1); j++) {
Row row = sheet.createRow(j);
for (int i = 0 ; i <a.length ; i ++) {
Cell cell = row.createCell(i);
cell.setCellValue(a[i]);
}
}
FileOutputStream out=new FileOutputStream(fileName);
out.flush();
wb.write(out);
out.close();
}
System.out.println(sheet.getLastRowNum());
}
}