Workbook_workbook对象内存-CSDN博客

工作需要。话不多说。

我们来看传统的poi。使用HSSFWorkbook创建2003格式为xls后缀的文档。如下：

[html] view plain copy

public String toTotal() {
String strs = "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'," + "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004',"
+ "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'," + "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004',"
+ "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'";
String sql1 = "select id,question_note,item1,item2,item3,item4,item5,item6,item7,item8" + ",item9,item10,item11 from PR_VOTE_QUESTION where FK_VOTE_PAPER_ID in (" + strs + ")";
String s1 = "SELECT DISTINCT ip from pr_vote_ANSWER where questionid=";
dbpool db = new dbpool();
MyResultSet m1 = null;
MyResultSet y1 = null;
MyResultSet x1 = null;
try {
m1 = db.executeQuery(sql1);
HSSFWorkbook wb = new HSSFWorkbook();// 建立新HSSFWorkbook对象
HSSFSheet sheet = wb.createSheet("total");// 建立新的sheet对象
// HSSFCellStyle cellStyle = wb.createCellStyle();//建立新的cell样式---日期用
// cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy
// h:mm"));//设置cell样式为定制的日期格式
// HSSFCell dCell =row.createCell((short)4);
// dCell.setCellvalue(new Date());//设置cell为日期类型的值
// dCell.setCellStyle(cellStyle); //设置该cell日期的显示格式
HSSFRow row = sheet.createRow(0);
setCellGBKValue(row.createCell((short) 0), "用户IP");
setCellGBKValue(row.createCell((short) 1), "题目");
setCellGBKValue(row.createCell((short) 2), "选项");
// 四个参数分别是：起始行，起始列，结束行，结束列
sheet.addMergedRegion(new Region(0, (short) 2, 0, (short) 13));
int i = 1;
while (m1.next()) {
String id = m1.getString("id");
String question_note = m1.getString("question_note");
question_note = question_note.replace("", "").replace("", "").replace(" ", "").replace(" ", "");
System.out.println("题目：" + question_note);
HSSFRow row2 = sheet.createRow((short) i);// 建立新行
HSSFCell cell = row2.createCell((short) 1);// 建立新cell
setCellGBKValue(cell, question_note);// 设置cell的整数类型的值
// HSSFRow row1 = sheet.createRow(++i);
// setCellGBKValue(row2.createCell((short) 1),"");
setCellGBKValue(row2.createCell((short) 2), m1.getString("item1"));
setCellGBKValue(row2.createCell((short) 3), m1.getString("item2"));
setCellGBKValue(row2.createCell((short) 4), m1.getString("item3"));
setCellGBKValue(row2.createCell((short) 5), m1.getString("item4"));
setCellGBKValue(row2.createCell((short) 6), m1.getString("item5"));
setCellGBKValue(row2.createCell((short) 7), m1.getString("item6"));
setCellGBKValue(row2.createCell((short) 8), m1.getString("item7"));
setCellGBKValue(row2.createCell((short) 9), m1.getString("item8"));
setCellGBKValue(row2.createCell((short) 10), m1.getString("item9"));
setCellGBKValue(row2.createCell((short) 11), m1.getString("item10"));
setCellGBKValue(row2.createCell((short) 12), m1.getString("item11"));
y1 = db.executeQuery(s1 + "'" + id + "'");
while (y1.next()) {
String str = "select a.ANSWERID ans,case when a.ANSWERID='1' then Q1.item1 " + " when a.ANSWERID='2' then Q1.item2 when a.ANSWERID='3' then Q1.item3 "
+ " when a.ANSWERID='4' then Q1.item4 when a.ANSWERID='5' then Q1.item5 " + " when a.ANSWERID='6' then Q1.item6 when a.ANSWERID='7' then Q1.item7 "
+ " when a.ANSWERID='8' then Q1.item8 when a.ANSWERID='9' then Q1.item9 " + " when a.ANSWERID='10' then Q1.item10 when a.ANSWERID='11' then Q1.item11 "
+ " end usr_get from pr_vote_ANSWER a " + "INNER JOIN PR_VOTE_QUESTION q1 on q1.id=a.questionid ";
String ip = y1.getString("ip");
System.out.println("用户ip:" + ip);
HSSFRow row3 = sheet.createRow((short) ++i);// 建立新行
HSSFCell cell3 = row3.createCell((short) 0);// 建立新cell
setCellGBKValue(cell3, ip);// 设置cell的整数类型的值
str += " where questionid='" + id + "' and ip='" + ip + "'";
x1 = db.executeQuery(str);
while (x1.next()) {
int ans = Integer.valueOf(x1.getString("ans"));
System.out.println("用户选项：" + ans);
HSSFCell cell4 = row3.createCell((short) (ans + 1));// 建立新cell
setCellGBKValue(cell4, x1.getString("usr_get"));// 设置cell的值
}
db.close(x1);
}
db.close(y1);
i++;
}
db.close(m1);
FileOutputStream fileOut = new FileOutputStream("E:\\调查问卷-统计信息.xls");
wb.write(fileOut);
fileOut.close();
} catch (SQLException e) {
e.printStackTrace();
if (db != null && x1 != null) {
db.close(x1);
}
if (db != null && y1 != null) {
db.close(y1);
}
if (db != null && m1 != null) {
db.close(m1);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (db != null && x1 != null) {
db.close(x1);
}
if (db != null && y1 != null) {
db.close(y1);
}
if (db != null && m1 != null) {
db.close(m1);
}
}
return null;
}

类似的创建2007以及版本的xlsx则为XSSFWorkbook。

当然除此之外，我们可以有其它的选择。使用SXSSFWorkbook。目前我只知道poi3.8以上版本有这个玩意。其它没试过。

下载下来后，你可以看有关它的记录。用来输出W级别以上的sql是个不错的选择。数据保存在内存中，它可以及时输出缓冲数据，指定内存中可以放入的数据，超过这个数据我们就进行输出。

[java] view plain copy

//导出每个IP用户的调查问卷 lizhuang
public String toDo() {
String strs = "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'," + "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004',"
+ "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'," + "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004',"
+ "'40288b163a72fd15013a730d44430004','40288b163a72fd15013a730d44430004'";
String sql1 = "select id,question_note,item1,item2,item3,item4,item5,item6,item7,item8" + ",item9,item10,item11 from PR_VOTE_QUESTION where FK_VOTE_PAPER_ID in (" + strs + ")";
String s1 = "SELECT DISTINCT ip from pr_vote_ANSWER where questionid=";
dbpool db = new dbpool();
MyResultSet m1 = null;
MyResultSet y1 = null;
MyResultSet x1 = null;
Map<String, Row> ips = new HashMap<String, Row>();//string对应用户IP，row存储用户所在的行。购物车原型【存储用户ID和商品】
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
m1 = db.executeQuery(sql1);
SXSSFWorkbook wb = new SXSSFWorkbook(100);// 只在内存中保留100行数据，大数据时使用。兼容HSSFWorkbook2003和XSSFWorkbook2007
wb.setCompressTempFiles(true); // temp files will be gzipped
Sheet sheet = wb.createSheet("total");// 建立新的sheet对象
sheet.setDefaultColumnWidth((short) 15);
Row row = sheet.createRow(0);
setCellGBKValue(row.createCell((short) 0), "用户IP");
// 合并单元格四个参数分别是：起始行，起始列，结束行，结束列
// sheet.addMergedRegion(new Region(0,(short)2,0,(short)13));
int j = 1;//列的坐标，递增
int k=1;//每个用户一行数据
int cols = 2;// 默认4列
while (m1.next()) {
String id = m1.getString("id");
//取标题
String question_note = m1.getString("question_note");
question_note = question_note.replace("", "").replace("", "").replace(" ", "").replace(" ", "");
setCellGBKValue(row.createCell((short) j++), "");
setCellGBKValue(row.createCell((short) j++), question_note);
//取出选项，最多11个，可扩展。
setCellGBKValue(row.createCell((short) j++), m1.getString("item1"));
setCellGBKValue(row.createCell((short) j++), m1.getString("item2"));
String item3 = m1.getString("item3");
String item4 = m1.getString("item4");
String item5 = m1.getString("item5");
String item6 = m1.getString("item6");
String item7 = m1.getString("item7");
String item8 = m1.getString("item8");
String item9 = m1.getString("item9");
String item10 = m1.getString("item10");
String item11 = m1.getString("item11");
if (item3 != null) {
cols = 3;
setCellGBKValue(row.createCell((short) j++), item3);
}
if (item4 != null) {
cols = 4;
setCellGBKValue(row.createCell((short) j++), item4);
}
if (item5 != null) {
cols = 5;
setCellGBKValue(row.createCell((short) j++), item5);
}
if (item6 != null) {
cols = 6;
setCellGBKValue(row.createCell((short) j++), item6);
}
if (item7 != null) {
cols = 7;
setCellGBKValue(row.createCell((short) j++), item7);
}
if (item8 != null) {
cols = 8;
setCellGBKValue(row.createCell((short) j++), item8);
}
if (item9 != null) {
cols = 9;
setCellGBKValue(row.createCell((short) j++), item9);
}
if (item10 != null) {
cols = 10;
setCellGBKValue(row.createCell((short) j++), item10);
}
if (item11 != null) {
cols = 11;
setCellGBKValue(row.createCell((short) j++), item11);
}
y1 = db.executeQuery(s1 + "'" + id + "'");
Row row3 = null;
while (y1.next()) {
String str = "select a.ANSWERID ans,case when a.ANSWERID='1' then Q1.item1 " + " when a.ANSWERID='2' then Q1.item2 when a.ANSWERID='3' then Q1.item3 "
+ " when a.ANSWERID='4' then Q1.item4 when a.ANSWERID='5' then Q1.item5 " + " when a.ANSWERID='6' then Q1.item6 when a.ANSWERID='7' then Q1.item7 "
+ " when a.ANSWERID='8' then Q1.item8 when a.ANSWERID='9' then Q1.item9 " + " when a.ANSWERID='10' then Q1.item10 when a.ANSWERID='11' then Q1.item11 "
+ " end usr_get from pr_vote_ANSWER a " + "left JOIN PR_VOTE_QUESTION q1 on q1.id=a.questionid ";
String ip = y1.getString("ip");
if (!ips.containsKey(ip)) {
row3 = sheet.createRow((short) k++);// 建立新行，每行存储用户IP和答案
ips.put(ip, row3);// 利用购物车原理，只不过购物车存储的是每件商品，这里记录上次的行。
} else {
row3 = ips.get(ip);// 如果有商品就取出商品，计算商品数量++，金额++，此处取出上次的行
}
setCellGBKValue(row3.createCell((short) 0), ip);// 设置IP
str += " where questionid='" + id + "' and ip='" + ip + "'";
x1 = db.executeQuery(str);
while (x1.next()) {
int ans = Integer.valueOf(x1.getString("ans"));
setCellGBKValue(row3.createCell((short) (j - 1 - cols + ans)), x1.getString("usr_get"));
}
db.close(x1);
str = null;// destroy this,collection rubbish
}
db.close(y1);
if (k % 100 == 0) {
((SXSSFSheet) sheet).flushRows(100);// flushRows(0)
}
}
db.close(m1);
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);// 将excel写入流
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
HttpServletResponse response = ServletActionContext.getResponse();
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8"); // msexcel
response.setHeader("Content-Disposition", "attachment;filename=" + new String("调查问卷-统计信息.xlsx".getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
out.close();
//直接写到本地，测试用
//FileOutputStream fileOut = new FileOutputStream("E:\\调查问卷-统计信息.xlsx");
//wb.write(fileOut);
//fileOut.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (db != null && x1 != null) {
db.close(x1);
}
if (db != null && y1 != null) {
db.close(y1);
}
if (db != null && m1 != null) {
db.close(m1);
}
try {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
private static void setCellGBKValue(Cell cell, String value) {
cell.setCellType(Cell.CELL_TYPE_STRING);// 设置CELL的编码信息
cell.setCellValue(value);
}