之前，本想与客户商量做几张固定的报表予使用，结果发现客户每个月都需要各种各样的报表，所以我们做了个窗口用于直接执行SQL语句；数据量一开始并不是很大查询出来的数据较少(约1～6W左右)，所以刚开始几个月很好用，查询出来的数据直接从页面复制下来贴到Excel做月报表，就这样一年过去了，最近做三期，发现运维人员月底几天特别的忙，数据逾百万(汗)，SQL查询语句都要写n多分页。。。。
伙伴们如此幸苦，还是我来拯救他们吧～
我的解决思路大致如下：
A>界面增加查询倒出Excel表功能(SQL不需要分页，也不需要在页面显示)
B>在后端把查询结果字段全转换成String类型(主要解决BigDeceal还有long类型在JXL中数据类型转换问题)
C>把查询出来的数据按6W／每页 分页(主要由于JXL只能导出.XLS文件类型(2003型)的EXCEL文件，故每sheet最多只能容纳65536行数据)
D>查询出来的数据遍历分页(需要注意的是sheet名需要按当前数据量和位置命名，EXCEL样式为固定样式)
开始上代码啦~
A就直接略过...
SQL语句传入JdbcTemplate获取数据后会返回一个SqlRowSet对象,现在就开始把每一列的字段类型通过遍历获取字段的String数据，然之放入Map中，以此类推，把所有记录的各个字段全转换成String，其中需要主要的是，Map的Key需要通过getMetaData().getColumnNames()获取，这是个String数组，你需要遍历这个String数组(下标从0开始)，对应的Value的下标是从1开始的，千万不要搞错啦~(附代码)
1 /** 2 * 查询数据 3 * @param sql 4 * @return 5 */ 6 public List<Map<String,String>> queryToFile(String sql){ 7 SqlRowSet rs=jdbc.queryForRowSet(sql);//JdbcTemplate 8 List<Map<String,String>> mList=new ArrayList<Map<String,String>>(); 9 while(rs.next()){ 10 Map<String,String> mp=new HashMap<String,String>(); 14 for(int i=0;i<rs.getMetaData().getColumnNames().length;i++){ 15 mp.put(rs.getMetaData().getColumnNames()[i], rs.getString(i+1));//注意! 16 } 17 mList.add(mp); 18 } 19 return mList; 20 }
SqlRowSet提供的Api确实够用,如果是从存储过程取固定列长度类型的数据完全可以使用getBigDecimal(...)、getInt(...)等方法直接获取指定的列的数据 再行处理更为便捷
以上数据处理算是个难点，数据处理好了，接下来还有两个问题：如何分页，如何按数据的index给sheet命名？
下面我是按6W每份切割源数据,核心就是整除取余,记住整除的时候预计会是一个float或double的数，这不重要，重要的是你用int接收，double的小数部分会被无情地截掉,因此页数不会小于1的，（代码）:
1 /** 2 * 将数据切割成6W每组的List 3 * @param data 4 * @return 5 * @throws ParseException 6 */ 7 public List<List<Map<String,String>>> splitDatas(List<Map<String,String>> data) throws ParseException{ 8 List<List<Map<String,String>>> mList=new ArrayList<List<Map<String,String>>>(); 9 int len=data.size()%60000==0?data.size()/60000:data.size()/60000+1;//关键点！！！ 10 for(int i=0;i<len;i++){ 11 if(i<len-1){//不是最后一组数据 12 List<Map<String,String>> l=new ArrayList<Map<String,String>>(); 13 l.addAll(data.subList(i==0?0:i*60000, (i+1)*60000)); 14 mList.add(l); 15 }else{//最后一组数据这样处理 16 List<Map<String,String>> l=new ArrayList<Map<String,String>>(); 17 l.addAll(data.subList(i*60000, data.size())); 18 mList.add(l); 19 } 20 } 21 return mList; 22 }
做到这里，我们已经把数据按每6W/份 扔进List里面了，但是导出的数据量大的时候不可能不看sheet名吧，sheet是可以命名的干嘛要使用自动生成的sheet1、sheet2...?
说白了，为了更方便些，我们需要做成这样子：
定位准确清晰易懂岂不更好？
说的容易做的并不轻巧，这时你需要处理好当前组的index和size才行，要不然做出来的东西可能就存在断号或遗漏的问题...，以下是个人的处理逻辑，请小心查看book.createSheet(...)的命名方式(也就是sheet的命名方式):
1 /** 2 * 导出多Sheet Excel,按6W每份分sheet 3 * @param data 数据 4 * @param headerName 头名称 5 * @param cellName 数据名称 6 * @param formName 表格名称 7 * @param response 响应 8 * @throws ParseException 异常 9 */ 10 public void expSheetsExcel(List<Map<String,String>> data,List<String> headerName,List<String> cellName,String formName, HttpServletResponse response) throws ParseException{ 11 try { 12 OutputStream os=response.getOutputStream(); 13 response.reset(); 14 WritableFont font1 = new WritableFont(WritableFont.TAHOMA, 11, WritableFont.BOLD, false); 15 WritableFont font2 = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.NO_BOLD, false); 16 WritableCellFormat cellFormat1 = new WritableCellFormat(font1); 17 WritableCellFormat cellFormat2 = new WritableCellFormat(font2); 18 cellFormat1.setBackground(Colour.GRAY_25); 19 cellFormat1.setAlignment(jxl.format.Alignment.CENTRE); 20 cellFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 21 cellFormat2.setAlignment(jxl.format.Alignment.CENTRE); 22 cellFormat2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); 23 WritableWorkbook book=Workbook.createWorkbook(os);//创建一个Excel表 24 String fileName =formName+".xls"; 25 response.setContentType("application/x-excel"); 26 response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("GBK"),"iso8859-1")); 27 /*切割数据为6W每份*/ 28 List<List<Map<String,String>>>mData=splitDatas(data); 29 for (List<Map<String, String>> mList : mData) { 30 //第一个sheet 参数(sheet名称,sheet的序号) 31 WritableSheet sheet=book.createSheet(String.format("%s~%s", 32 (data.size()>60000? 33 mData.indexOf(mList)*6000+1//(mData.size()==(mData.indexOf(mList)+1)?mData.indexOf(mList)*6000:60000*(mData.indexOf(mList))) 34 :0)+"", 35 (data.size()>60000? 36 (mData.size()==(mData.indexOf(mList)+1)?data.size():60000*(mData.indexOf(mList)+1)) 37 :data.size())+""), 38 mData.indexOf(mList) 39 ); 40 for(int i=1;i<cellName.size();i++){ 41 sheet.setRowView(i,350,false); //设置行高 42 } 43 for(int j=0;j<mList.size();j++){ 44 sheet.setColumnView(j,30);//设置宽度 45 } 46 /*设置表头数据*/ 47 int colLength=0;//标记行数 48 for(int k=0;k<headerName.size();k++){ 49 sheet.addCell(new Label(k,colLength,headerName.get(k),cellFormat1));//(?列,?行,单元格内容,样式) 50 } 51 colLength+=1;//添加一行 52 /*设置表体数据*/ 53 for(Map<String,String> d : mList) { 54 for(int m=0;m<cellName.size();m++){ 55 sheet.addCell(new Label(m,colLength,d.get(cellName.get(m)),cellFormat2));//(?列,?行,单元格内容,样式) 56 } 57 colLength+=1;//添加一行 58 } 59 } 60 book.write(); 61 book.close(); 62 os.close(); 63 os.flush(); 64 response.flushBuffer(); 65 } catch(Exception e){ 66 e.printStackTrace(); 67 } 68 }
关键的地方用了三目运算，新手同学需自行查找三目运算相关的资料，这里就不再缀诉啦~~~
funnyzpc@gmail.com
