關於SpringMVC中如何把查詢資料全轉成String型別

funnyZpC發表於2017-06-28

  之前,本想與客戶商量做幾張固定的報表予使用,結果發現客戶每個月都需要各種各樣的報表,所以我們做了個視窗用於直接執行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     }

關鍵的地方用了三目運算,新手同學需自行查詢三目運算相關的資料,這裡就不再綴訴啦~~~

 

相關文章