工作原理:做一个判断,当大于65535的时候,就创建一个新的sheet。
如遇到内存问题:最好配置 JDK:-Xms556m -Xmx556m -XX:MaxNewSize=556m -XX:MaxPermSize=956m
个人代码:不好之处,多多指正。
package com.hdkj.tools;
import java.io.IOException;
import java.io.OutputStream;
import javax.servlet.http.HttpServletResponse;
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.hssf.util.HSSFColor;
public class ExportToExcel {
public static void export(Object[] head, Object[][] body,String filename,
HttpServletResponse response) throws IOException {
int sumrecord=0;//总记录 大于2万 ,新建一个sheet
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename ="
+ new String(filename.getBytes("GB2312"), "ISO-8859-1"));
response.setCharacterEncoding("GBK");
HSSFWorkbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
String sheetnameString="";
HSSFSheet sheet = wb.createSheet("new sheet");// 建立新的sheet对象
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格背景色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 230);
cellStyle.setFont(font);
HSSFRow row = sheet.createRow((short) 0);// 建立新行(表头)
row.setHeight((short) 550);
row.setRowStyle(cellStyle);
// 跟据head创建表头
if(head!=null){
for (int i = 0; i < head.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(cellStyle);
cell.setCellValue(head[i].toString());
sheetnameString=head[3].toString();
}
}
if(body!=null){
// 循环插入数据
int sheetname1=1;//用于自动化创建名字
int record=2;//计算记录
int rownumb=0;
for (int j = 0; j < body.length; j++) {
rownumb++;
if(record%30001== 0){//超过30001就自动创建一个sheet
rownumb=0;
sheet = wb.createSheet(""+sheetnameString+""+sheetname1+"");
cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格背景色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格字体
font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 230);
cellStyle.setFont(font);
row = sheet.createRow((short) 0);// 建立新行(表头)
row.setHeight((short) 550);
row.setRowStyle(cellStyle);
// 跟据head创建表头
if(head!=null){
for (int i = 0; i < head.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(cellStyle);
cell.setCellValue(head[i].toString());
}
}
sheetname1++;
}
row = sheet.createRow((short) rownumb + 1);
for (int m = 0; m < head.length; m++) {
HSSFCell cell = row.createCell((short) m);
cell.setCellValue(body[j][m].toString());
}
record++;
//sumrecord=body.length;
}
}
OutputStream fileOut = response.getOutputStream();
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
}
http://poi.apache.org/spreadsheet/quick-guide.html#ShiftRows
http://zhidao.baidu.com/question/510403234.html