SSM数据库数据导出excel

首先，这是我对自己的需求而使用的逻辑，若有可以完美的地方方便告诉下小白。

MAVEN

 

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>

 

1、前端页面，伪异步（页面不刷新）

为什么不用ajax呢？

JQuery的ajax函数的返回类型只有xml、text、json、html等类型，没有“流”类型。所以就用js做个form表单请求

上代码

1 function exportExcel(){
2             var myurl="${context}/assetInLibrary/export";
3             var form=$("<form>");
4             form.attr("style","display:none");
5             form.attr("method","post");
6             form.attr("action",myurl);
7             $("body").append(form);
8         }

 

2、在工具包中创建ViewExcel,继承AbstractExcelView

官网：www.fhadmin.org 先上代码

 1 public class ViewExcel extends AbstractExcelView {
 2 
 3     private String[] titles;
 4     
 5     //传入指定的标题头
 6     public ViewExcel(String[] titles) {
 7         this.titles=titles;
 8     }
 9     
10     @Override
11     protected void buildExcelDocument(Map<String, Object> model,
12             HSSFWorkbook workbook, HttpServletRequest request,
13             HttpServletResponse response) throws Exception {
14         //获取数据
15         List<Map<String, String>> list = (List<Map<String, String>>) model.get("excelList");
16         //在workbook添加一个sheet
17         HSSFSheet sheet = workbook.createSheet();
18         sheet.setDefaultColumnWidth(15);
19         HSSFCell cell=null;
20         //遍历标题
21         for (int i = 0; i < titles.length; i++) {
22             //获取位置
23             cell = getCell(sheet, 0, i);
24             setText(cell, titles[i]);
25         }
26         //数据写出
27         for (int i = 0; i < list.size(); i++) {
28             //获取每一个map
29             Map<String, String> map=list.get(i);
30             //一个map一行数据
31             HSSFRow row = sheet.createRow(i+1);
32             for (int j = 0; j < titles.length; j++) {
33                 //遍历标题，把key与标题匹配
34                 String title=titles[j];
35                 //判断该内容存在mapzhong
36                 if(map.containsKey(title)){
37                     row.createCell(j).setCellValue(map.get(title));
38                 }
39             }
40         }
41          //设置下载时客户端Excel的名称     
42         String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";  
43         response.setContentType("application/vnd.ms-excel");     
44         response.setHeader("Content-disposition", "attachment;filename=" + filename); 
45         OutputStream ouputStream = response.getOutputStream();     
46         workbook.write(ouputStream);     
47         ouputStream.flush();     
48         ouputStream.close();     
49     }
50 
51 }

 

在构造函数中传进来需导出的titles，也就是excel中的标题头，这个逻辑会有点麻烦，因为我是创建Map，让dao中查出来的数据根据我的Map（‘title’,'value'）进行封装，官网：www.fhadmin.org 且title要存在于传进来的titles中，剩下看源码就能明白

3、service中的数据封装

 1 public List<Map<String, String>> selectAllAssetInlibraryInfo() {
 2         List<AssetInlibrary> list = assetInlibraryMapper.selectByExample(null);
 3         List<Map<String, String>> mapList=new ArrayList<Map<String,String>>();
 4         for (AssetInlibrary assetInlibrary : list) {
 5             Map<String, String> map=new HashMap<String, String>();
 6             map.put("编号", assetInlibrary.getId()+"");
 7             map.put("资产名称", assetInlibrary.getTitle());
 8             AssetType assetType = assetTypeMapper.selectByPrimaryKey(assetInlibrary.getAssetTypeId());
 9             map.put("资产类型", assetType.getTitle());
10             AssetBrand assetBrand = assetBrandMapper.selectByPrimaryKey(assetInlibrary.getAssetBrandId());
11             map.put("资产品牌", assetBrand.getTitle());
12             AssetStorage assetStorage = assetStorageMapper.selectByPrimaryKey(assetInlibrary.getAssetStorageId());
13             map.put("资产存放地点", assetStorage.getTitle());
14             AssetProvider assetProvider = assetProviderMapper.selectByPrimaryKey(assetInlibrary.getAssetProviderId());
15             map.put("资产供应商", assetProvider.getTitle());
16             mapList.add(map);
17         }
18         return mapList;
19     }

 

4、controller中的数据交互

1 @RequestMapping("/assetInLibrary/export")
2     public ModelAndView export(ModelMap map) throws Exception{
3         List<Map<String,String>> list = assetInLibraryService.selectAllAssetInlibraryInfo();
4         String[] titles={"编号","资产名称","资产类型","资产品牌","资产存放地点","资产供应商"};
5         ViewExcel excel=new ViewExcel(titles);
6         map.put("excelList", list);
7         return new ModelAndView(excel,map);
8     }

 版权声明：本文为不会代码的小白原创文章，未经允许不得转载。