java统计数据并导出Excel

业务代码：

@RequestMapping(value = "/exportDepartStatistics.html")
    @ResponseBody
    public HashMap<String, Object> exportDepartStatistics(HttpServletRequest request, HttpServletResponse response)  {
        System.out.println("exportDepartStatistics");

        String declareYear = request.getParameter("declareYear");

        HashMap<String, Object> modelmap = new HashMap<>();
        String status = "success"; //ajax状态
        //excel标题
        String[] title = {"序号","部门","已审核","未审核","已总结","合计"};
        //excel文件名
        String fileName = "DepartStatistics"+ DateTools.dateToString(new Date(),"yyy-MM-dd-HHmmss") + ".xls";
        //sheet名
        String sheetName = "";
        if (declareYear == null || "".equals(declareYear)){
            sheetName =  "各部门情况统计";
        }else{
            sheetName =  declareYear + "年各部门情况统计";
        }

        //响应到客户端
        try {
            //获取存储路径
            ConfigItem configItem = configItemService.queryConfigItem(ConfigItem.TYPE_STATISTICS_PATH);
            if(configItem == null || configItem.getCurrentValue() == null){
                modelmap.put("status","lose");
                return modelmap;
            }

			Map<String,String> map = new HashMap<String,String>();
			map.put("type", Case.TYPE_DEPART);
			map.put("declareYear",declareYear);
			map.put("handlingDepartment", null);

            //获取数据
            List<Map<String, Object>> countList = caService.getCountList(map);

            String [][] content = new String[countList.size()][];
            for (int i = 0; i < countList.size(); i++) {
                content[i] = new String[title.length];
                Map<String, Object> declareMap = countList.get(i);
                String handlingDepartment = "";
                String notCheck = "";
                String checked = "";
                String declared = "";
                String caseCount = "";

                if(declareMap.get("HANDLINGDEPARTMENTNAME") != null )
                    handlingDepartment = declareMap.get("HANDLINGDEPARTMENTNAME").toString();
                if(declareMap.get("notCheck") != null)
                    notCheck = declareMap.get("notCheck").toString();
                if(declareMap.get("checked") != null)
                    checked = declareMap.get("checked").toString();
                if(declareMap.get("declared") != null)
                    declared = declareMap.get("declared").toString();
                if(declareMap.get("caseCount") != null)
                    caseCount = declareMap.get("caseCount").toString();

                if(i != (countList.size()-1)) {
					content[i][0] = (i + 1) + "";
				}
                content[i][1] = handlingDepartment;
                content[i][2] = notCheck;
                content[i][3] = checked;
                content[i][4] = declared;
                content[i][5] = caseCount;
            }

            //创建HSSFWorkbook
            HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
            //配置路径下 增加年月文件夹
            String filePath = configItem.getCurrentValue()+ "\" + DateTools.dateToString(new Date(),"yyyyMM");
            //文件夹不存在进行创建
            FileUpAndDown.checkDirExists(filePath);
            //上传至服务器
            File file = new File(filePath + "\" + fileName);
            FileOutputStream outFile = new FileOutputStream(file);
            wb.write(outFile);
            outFile.flush();
            outFile.close();
        } catch (Exception e) {
            status = "fail";
            e.printStackTrace();
        }

        modelmap.put("status",status);
        modelmap.put("fileName",fileName);
        modelmap.put("fileKey",ConfigItem.TYPE_STATISTICS_PATH);
        return modelmap;
    }

Excel工具类:

import org.apache.poi.hssf.usermodel.*;

public class ExcelUtil {

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        // 设置字体
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 13);// 字体大小
        // 设置单元格格式
        HSSFDataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        style.setFont(font);
        style.setWrapText(true);// 自动换行

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                HSSFCell cellBody = row.createCell(j);
                cellBody.setCellValue(values[i][j]);
                cellBody.setCellStyle(style);
            }
        }

        //自动调整列宽
        for(int i=0;i<title.length;i++){
            sheet.autoSizeColumn((short)i); //调整第一列宽度
        }
        return wb;
    }
}

js代码：

 function exportExcel(){
        var declareYear = $("#declareYear").val();
        $.ajax({
            url:"exportDepartStatistics.html", //exportDeclareStatistics exportInputStatistics  exportDepartStatistics
            dataType: "json",
            data: {declareYear:declareYear},
            success:function(result){
                if (result.status == "success"){
                    window.location.href = "downLoadFile.html?fileKey="+ result.fileKey+"&&fileName="+result.fileName;
                }else if (result.status == "fail"){
                    alert("下载失败");
                }
            }}
        );
    }

报表下载:

//下载报表  @param【fileKey:配置类型（获取路径） fileName:文件名】
    @RequestMapping(value = "/downLoadFile.html")
    @ResponseBody
    public void downLoadFile(HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取配置路径
        String fileKey = request.getParameter("fileKey");
        ConfigItem configItem = configItemService.queryConfigItem(fileKey);
        String filePath = configItem.getCurrentValue()+ "\" + DateTools.dateToString(new Date(),"yyyyMM");
        String fileName = request.getParameter("fileName");

        fileName =  new String(fileName.getBytes("ISO-8859-1"),"UTF-8");
        File file = new File(filePath + "\" + fileName);
        try {
            response.setContentType("application/-excel");
            fileName = java.net.URLEncoder.encode(fileName,"UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+fileName);
            OutputStream out = response.getOutputStream();
            byte d[] = new byte[256];
            int count = 0;
            FileInputStream in = new FileInputStream(file);
            while((count = in.read(d)) != -1){
                out.write(d,0,count);
            }
            out.flush();
            out.close();
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

最后

以上就是斯文鸡为你收集整理的java统计数据并导出Excel的全部内容，希望文章能够帮你解决java统计数据并导出Excel所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。