POI之Excel导出

1，在maven的pom文件中添加依赖

 <dependency>

     <groupId>org.apache.poi</groupId>

     <artifactId>poi-ooxml</artifactId>

     <version>${poi.version}</version>

 </dependency>

2，在Controller中添加转发接口

 @ResponseBody

 @RequestMapping(value="exportExcel", method={RequestMethod.GET, RequestMethod.POST})

 public String exportExcel(HttpServletRequest request, String name, String data) {

     JSONObject result = new JSONObject();

     result.put("code", 0);

     result.put("msg", "success");

     try {

         HSSFWorkbook workbook = new HSSFWorkbook();

         Method method = IExportExcelService.class.getDeclaredMethod(name + "Export", new Class[]{HSSFWorkbook.class, String.class});

         method.invoke(exportExcelService, workbook, data);

         String filename = name + GhFormatUtils.getFileDate() + ".xls";

         String realpath = request.getServletContext().getRealPath("/");

         String filepath = filedir + "/" + filename;

         FileOutputStream fos = new FileOutputStream(realpath + filepath);

         workbook.write(fos);

         fos.close();

         workbook.close();

         result.put("data", filepath);

     } catch (Exception e) {

         e.printStackTrace();

         result.put("code", -1);

         result.put("msg", e.getMessage());

     }

     return result.toString();

 }

3,在Service层编写相应生成格式的pdf

 //公共函数

 public void exportExcel(HSSFSheet sheet, String[] keys, JSONArray data) throws Exception {

     sheet.setDefaultColumnWidth(15);

     JSONObject head = (JSONObject)data.remove(0);

     int lastRowNum = sheet.getLastRowNum();

     //创建标题行

     HSSFRow headRow = sheet.createRow(++lastRowNum);

     HSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle();

     headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

     headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

     headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

     headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

     headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

     headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

     for(int i = 0; i < keys.length; i++) {

         HSSFCell cell = headRow.createCell(i);

         cell.setCellValue(head.getString(keys[i]));

         cell.setCellStyle(headerStyle);

     }

     HSSFCellStyle dataStyle = sheet.getWorkbook().createCellStyle();

     dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

     dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

     dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

     dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

     //创建数据明细行

     for(int i = 0; i < data.size(); i++) {

         HSSFRow row = sheet.createRow(++lastRowNum);

         JSONObject rowData = data.getJSONObject(i);

         for(int j = 0; j < keys.length; j++) {

             HSSFCell cell = row.createCell(j);

             cell.setCellValue(rowData.getString(keys[j]));

             cell.setCellStyle(dataStyle);

         }

     }

 }

 //具体处理函数

 @Override

 public void offerExport(HSSFWorkbook workbook, String dataStr) throws Exception {

     JSONObject data = JSONObject.parseObject(dataStr);

     JSONArray detail = data.getJSONArray("detail");

     Map<String, String> dict = basMgmtService.getDictMap("col1");

     //数据处理，用数据字典翻译数据

     for(int i = 0; i < detail.size(); i++) {

         JSONObject obj = detail.getJSONObject(i);

         obj.put("col1", dict.get(obj.getString("col1")));

     }

     JSONObject head = new JSONObject();

     String keys[] = {"col1","col2"};

     head.putAll(new HashMap<String, String>(){{

         put(keys[0], "第一列名称");

         put(keys[1], "第二列名称");

         //......

     }});

     detail.add(0, head);

     //创建工作页

     HSSFSheet sheet = workbook.createSheet(data.getString("titleName"));

     //创建表头标题

     sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keys.length - 1));

     HSSFRow titleRow = sheet.createRow(0);

     HSSFCell titleCell = titleRow.createCell(0);

     titleCell.setCellValue(data.getString("titleName"));

     HSSFCellStyle titleStyle = workbook.createCellStyle();

     titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

     titleCell.setCellStyle(titleStyle);

     exportExcel(sheet, keys, detail);

 }

4,前端请求

 //公共导出Excel

 function commExcel(url, name, data, appPath) {

     $.ajax({

         url : url,

         type : 'post',

         dataType : "json",

         data : {

             name : name,

             data : JSON.stringify(data)

         },

         async : true,

         success : function(result,status,xhr) {

             if(result.code == 0) {

                 $("<a href=" + appPath + result.data + " />")[0].click();

             } else {

                 var msg = result.msg;

                 if(!msg) {

                     msg = "请求失败，请手动刷新页面！";

                 }

                 top.layer.alert(msg);

             }

         },

         error : function(xhr,status,err) {

             top.layer.alert(err);

         }

     });

 }

 //页面导出Excel

 function exportExcel() {

     var url = "${adminPath}/finance/export/exportExcel";

     var data = {

         titleName : "excel名称",

         customerName: "用户名称",

         detail : detail

     };

     commExcel(url, "offer", data, "${appPath}");

 }