Spring+SpringMvc的Excel导出功能

水深无声 2022-05-30 08:00 311阅读 0赞

该功能采用的是Excel2007版，以Map键值对作为数据导出，当然List数据也是一样的。Excel模板存放于：WebRoot-WEB-INF下面创建一个自己的文件夹：excelModelFile里，excel模板表头要自己先设计好。下面之间贴代码

1.controller：

@Controller
    public class ExcelController {
        public static org.apache.log4j.Logger logger = org.apache.log4j.Logger.getLogger(ExcelController.class);
    
        @ResponseBody
        @RequestMapping(value="excelDownload.html",method={RequestMethod.POST})
        public  JSONObject  excelDownload(HttpServletRequest request, HttpServletResponse response) {
            System.out.println("通过 jquery.form.js 提供的ajax方式导出文件！");
            JSONObject js = new JSONObject();
            JSONObject resultJs = new JSONObject();
            String reqStr = request.getParameter("Data");
            if(reqStr == null){
                return resultDataJs("","请选择要导出的数据！",false,resultJs);
            }
            JSONObject dataJs = JSONObject.fromObject(reqStr);
            OutputStream os = null;
            Workbook wb = null;    //工作薄
            String type = dataJs.getString("type");//这是用于多个文件导出时的标识
        //这里只举例一个
            try {
                if("bmSend".equals(type)){
                    wb = writeBMSendDtl(js,wb,os,response,dataJs,resultJs);
                } //else if("bmReceipt".equals(type)){
                    //wb = writeBMReceiptDtl(js,wb,os,response,dataJs,resultJs);
                //} 
                if(wb == null){
                    return resultDataJs("",resultJs.getString("erroe"),false,resultJs);
                }
                os = response.getOutputStream();
                wb.write(os);
            } catch (Exception e) {
                return resultDataJs("","下载出错了,请联系客服!",false,resultJs);
            }
            finally{
                try {
                    os.flush();
                    os.close();
                } catch (IOException e) {
                    return resultDataJs("","下载出错了,请联系客服!",false,resultJs);
                }
    
    
            }
            return resultDataJs("","下载完成",true,resultJs);
        }
    
        /** * 出库明细导出 * @param js * @param wb * @param os * @param response * @param dataJs * @return * @throws Exception */ private Workbook writeBMSendDtl(JSONObject js, Workbook wb, OutputStream os, HttpServletResponse response,JSONObject dataJs,JSONObject resultJs) throws Exception{
            logger.info("进入荒料出库明细导出接口");
            js = SlswUtils.erpAxisRequest(Global.WSDLURL, "ReportDataWebService",
                    "getBMOut",
                    "{ user:'sczx',password:'123456',reqKey:'SSS',data:[{"+analysisJson(dataJs)+"}] }");//这是调用别人的报表接口返回的数据
    
    
            List<Map<String,Object>> jsList = (List<Map<String, Object>>) js.get("list");
            if(jsList == null){
                resultJs.put("error","荒料出库明细导出失败，没有查询到相应数据，若有疑问请联系客服！");
                resultJs.put("flag",false);
                return null;
            }
            JSONObject jsDtl = SlswUtils.erpAxisRequest(Global.WSDLURL, "ReportDataWebService",
                    "getBMOutDtl",
                    "{ user:'sczx',password:'123456',reqKey:'SSS',data:[{"+analysisJson(dataJs)+"}] }");//这是调用别人的报表接口返回的数据
            List<Map<String,Object>> jsOutDtl = (List<Map<String, Object>>) jsDtl.get("list");//这里获取接口返回的集合数据
            logger.info("荒料出库明细导出接口，ERP调用接口完成，开始写入EXCEL");
            //导出Excel文件数据
            File file =ExportExcelUtil.getExcelDemoFile("/ExcelModelFile/荒料出库明细.xlsx");
            String sheetName ="荒料出库汇总";//分页名称
            String sheetName2="荒料出库明细";
            wb = ExcelModel.writeBMSendAndReceipDtlExcel(wb,file, sheetName,sheetName2,jsList,jsOutDtl);//因为该excel有两个分页
            logger.info("荒料出库明细导出  数据解析完成==");
            ExportExcelUtil.outResponseForDown(response,"荒料出库.xlsx",wb,os);
            resultJs.put("flag",true);
            return wb;
        }}

2.导出excelUtil类：

public class ExportExcelUtil {
        public static org.apache.log4j.Logger logger = org.apache.log4j.Logger.getLogger(ExportExcelUtil.class);
        /** * 描述：根据文件路径获取项目中的文件 * @param fileDir 文件路径 * @return * @throws Exception */ public static File getExcelDemoFile(String fileDir) throws Exception{
            String classDir = null;
            String fileBaseDir = null;
            File file = null;
            classDir = Thread.currentThread().getContextClassLoader().getResource("/").getPath();
            fileBaseDir = classDir.substring(0, classDir.lastIndexOf("classes"));
            logger.info("文件地址===="+(fileBaseDir+fileDir));
            file = new File(fileBaseDir+fileDir);
            logger.info("判断读取文件boolean == "+file.exists());
            if(!file.exists()){
                throw new Exception("模板文件不存在！");
            }
            return file;
        }
    
    
        /** * 描述：设置简单的Cell样式 * @return */ public static CellStyle setSimpleCellStyle(Workbook wb,String str){
            CellStyle cs = wb.createCellStyle();
    
            cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框
            cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框
            cs.setBorderTop(CellStyle.BORDER_THIN);//上边框
            cs.setBorderRight(CellStyle.BORDER_THIN);//右边框
            if("center".equals(str)){
                cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中对齐
            } else if("right".equals(str)){
                cs.setAlignment(CellStyle.ALIGN_RIGHT); // 右对齐
            } else if("left".equals(str)){
                cs.setAlignment(CellStyle.ALIGN_LEFT); // 左对齐
            }
    
            return cs;
        }
    
    
        public static Workbook getWorkbook(InputStream inStr, String fileName) {
            Workbook wb = null;
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            try {
    
            if(".xls".equals(fileType)){
                wb = new HSSFWorkbook(inStr);
            }else if(".xlsx".equals(fileType)){
                wb = new XSSFWorkbook(inStr);  //2007+
            }
            } catch (IOException e) {
                e.printStackTrace();
            }
    
            return wb;
        }
    
        public static void outResponseForDown(HttpServletResponse response,String fileName,Workbook wb, OutputStream os) throws IOException{
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
            /*os = response.getOutputStream();
            wb.write(os);*/
    
        }
       
    
    
    }

3.ExcelModel类：

public class ExcelModel {
        public static org.apache.log4j.Logger logger = org.apache.log4j.Logger.getLogger(ExcelModel.class);
    
        /** * 荒料出库明细导出 * 荒料入库明细导出 * * @param file * @param sheetName * @param lis * @return * @throws Exception */ public static Workbook writeBMSendAndReceipDtlExcel(Workbook wb, File file, String sheetName, String sheetName2, List<Map<String, Object>> lis, List<Map<String, Object>> listJson) throws Exception {
            Row row = null;
            Cell cell = null;
    
            FileInputStream fis = new FileInputStream(file);
            wb = ExportExcelUtil.getWorkbook(fis, file.getName());    //获取工作薄
            Sheet sheet = wb.getSheet(sheetName);
            //循环插入数据
            int lastRow = sheet.getLastRowNum() + 1;    //插入数据的数据ROW
            for (int i = 0; i < lis.size(); i++) {
                row = sheet.createRow(lastRow + i); //创建新的ROW，用于数据插入
                Map<String, Object> map = lis.get(i);
                //按项目实际需求，在该处将对象数据插入到Excel中
    
                //Cell赋值开始
                cell = row.createCell(0);
                cell.setCellValue(i + 1);
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "center"));
    
                cell = row.createCell(1);
                cell.setCellValue(map.get("mtlname").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(2);
                cell.setCellValue(map.get("qty").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(3);
                cell.setCellValue(map.get("weight").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(4);
                cell.setCellValue(map.get("volume").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
            }
    
            Sheet sheet2 = wb.getSheet(sheetName2);
            //循环插入数据
            int lastRow2 = sheet2.getLastRowNum() + 1;
            for (int i = 0; i < listJson.size(); i++) {
                row = sheet2.createRow(lastRow2 + i); //创建新的ROW，用于数据插入
                Map<String, Object> map = listJson.get(i);
                //按项目实际需求，在该处将对象数据插入到Excel中
    
                //Cell赋值开始
                cell = row.createCell(0);
                cell.setCellValue(i + 1);
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "center"));
    
                cell = row.createCell(1);
                cell.setCellValue(getSubTime(map.get("billdate").toString()));
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(2);
                cell.setCellValue(map.get("mtlname").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(3);
                cell.setCellValue(map.get("materialtype").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(4);
                cell.setCellValue(map.get("msid") + "/" + map.get("csid"));
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(5);
                cell.setCellValue(map.get("whsname").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(6);
                cell.setCellValue(map.get("storeareaname").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(7);
                cell.setCellValue(map.get("locationname").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "left"));
    
                cell = row.createCell(8);
                cell.setCellValue(map.get("lenght").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(9);
                cell.setCellValue(map.get("width").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(10);
                cell.setCellValue(map.get("height").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
    
                cell = row.createCell(11);
                cell.setCellValue(map.get("weight").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
                cell = row.createCell(12);
                cell.setCellValue(map.get("volume").toString());
                cell.setCellStyle(ExportExcelUtil.setSimpleCellStyle(wb, "right"));
    
            }
            return wb;
        }
    
    }

4.到这里导出功能就完成了，但是要注意的是文件地址一定不能有空格或中文否则都会发生错误。

模板位置：

![70][]

模板excel设计：

![70 1][]

[70]: /images/20220530/93923f9b56904e85867117b5ef689648.png
[70 1]: /images/20220530/c1a6231f57fa4fcbaa9e099a274473cc.png