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     @Override11     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添加一个sheet17         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             //获取每一个map29             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                 //判断该内容存在mapzhong36                 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     }

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