使用jxl导出excel时怎么创建多个sheet 快来打我* 2022-08-05 14:28 260阅读 0赞 虽然知道excel导出可能会出现要记录要分多个sheet的情况,但迄今为止还是没有遇到过,今天有空试了一把 <%@ page language="java" import="java.util.\*" pageEncoding="UTF-8"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>export</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body> <% try\{ 查询开始/// Class.forName("oracle.jdbc.driver.OracleDriver"); //连接数据库 java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","test","test123"); long start = 0; long end = 100000; //查询记录 List list = com.test.TEST\_Manager.selectByCondition(conn, null,start,end); int length = list.size(); //查询结果记录数 //设置每个sheet显示的记录数 int sheetSize = 100; //有多少个sheet int sheetNum = 1; //计算要创建的sheet个数 if(length%sheetSize > 0)\{ sheetNum = length/sheetSize+1; \}else\{ sheetNum = length/sheetSize; \} conn.close(); conn = null; 查询结束/// //导出excel的名称 String fileName = "test\_list.xls"; response.reset(); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); response.setContentType("application/vnd.ms-excel"); //输出流 java.io.OutputStream os = response.getOutputStream(); //创建可写工作薄 jxl.write.WritableWorkbook wwb = jxl.Workbook.createWorkbook(os); for(int kk=0; kk<sheetNum ; kk++)\{ /创建sheet\[kk\]开始/ //创建可写工作表 jxl.write.WritableSheet ws = wwb.createSheet(("sheet"+kk), kk); //设定第一行的行高 ws.setRowView(0,400); //将第一列的宽度设为20 ws.setColumnView(0,15); ws.setColumnView(1,30); ws.setColumnView(2,30); ws.setColumnView(3,30); ws.setColumnView(4,30); ws.setColumnView(5,15); ws.setColumnView(6,30); ws.setColumnView(7,15); //设置标题开始 //设置写入字体 jxl.write.WritableFont wf = new jxl.write.WritableFont(jxl.write.WritableFont.ARIAL, 11,jxl.write.WritableFont.BOLD, false); //设置CellFormat jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf); //用于Number的格式 //jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0.00"); //jxl.write.WritableCellFormat priceformat = new jxl.write.WritableCellFormat(nf); // 把水平对齐方式指定为左对齐 wcfF.setAlignment(jxl.format.Alignment.LEFT); // 把垂直对齐方式指定为居中对齐 wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置列名 jxl.write.Label labelCF1 = new jxl.write.Label(0, 0, "测试ID",wcfF); jxl.write.Label labelCF2 = new jxl.write.Label(1, 0, "测试名称",wcfF); jxl.write.Label labelCF3 = null; labelCF3 = new jxl.write.Label(2, 0, "测试上线时间",wcfF); jxl.write.Label labelCF4 = new jxl.write.Label(3, 0, "别名",wcfF); jxl.write.Label labelCF5 = new jxl.write.Label(4, 0, "数量",wcfF); jxl.write.Label labelCF6 = new jxl.write.Label(5, 0, "状态1",wcfF); jxl.write.Label labelCF7 = new jxl.write.Label(6, 0, "状态2",wcfF); jxl.write.Label labelCF8 = new jxl.write.Label(7, 0, "类型",wcfF); //绑定值 ws.addCell(labelCF1); ws.addCell(labelCF2); ws.addCell(labelCF3); ws.addCell(labelCF4); ws.addCell(labelCF5); ws.addCell(labelCF6); ws.addCell(labelCF7); ws.addCell(labelCF8); //设置标题结束 jxl.write.WritableFont wf1 = new jxl.write.WritableFont(jxl.write.WritableFont.ARIAL, 11,jxl.write.WritableFont.NO\_BOLD, false); //设置CellFormat jxl.write.WritableCellFormat wcfF2 = new jxl.write.WritableCellFormat(wf1); /循环写excel主体开始 for(int i = kk\*sheetSize; i< (kk+1)\*sheetSize ; i++ )\{ if(i<length )\{ com.test.TEST test = (com.test.TEST) list.get(i); jxl.write.Label data1 = new jxl.write.Label(0, i+1-(kk\*sheetSize) , (""+test.getTestid()),wcfF2); jxl.write.Label data2 = new jxl.write.Label(1, i+1-(kk\*sheetSize) , (""+test.getTest\_name()),wcfF2); jxl.write.Label data3 = new jxl.write.Label(2, i+1-(kk\*sheetSize) , (""+test.getTest\_date()),wcfF2); jxl.write.Label data4 = new jxl.write.Label(3, i+1-(kk\*sheetSize) , (""+test.getOthername()),wcfF2); jxl.write.Label data5 = new jxl.write.Label(4, i+1-(kk\*sheetSize) , (""+test.getSize()),wcfF2); jxl.write.Label data6 = new jxl.write.Label(5, i+1-(kk\*sheetSize) , (""+test.getState1()),wcfF2); jxl.write.Label data7 = new jxl.write.Label(6, i+1-(kk\*sheetSize) , (""+test.getState2()),wcfF2); jxl.write.Label data8 = new jxl.write.Label(7, i+1-(kk\*sheetSize) , (""+test.getType()),wcfF2); ws.addCell(data1); ws.addCell(data2); ws.addCell(data3); ws.addCell(data4); ws.addCell(data5); ws.addCell(data6); ws.addCell(data7); ws.addCell(data8); \}else\{ break; \} \} /循环写excel主体结束 /创建sheet\[kk\]结束/ \} //我猜测数据太多时,可能会导致内存溢出 wwb.write(); wwb.close(); os.flush(); os.close(); out.clear(); out = pageContext.pushBody(); \}catch(Exception e)\{ e.printStackTrace(); \} %><br> </body> </html>
还没有评论,来说两句吧...