java 读取数据库数据并下载为Excel

java 读取数据库数据并下载为Excel

前台

html

<input type="button" id="javaExcel" value="java下载表格">

script

<script>
    $("#javaExcel").click(function () {
        $.ajax({
            type: "POST",
            url: "../java/excel",
            data:{name:"javaExcel"},
            dataType: "json",
            success: function (data) {
                alert(data.status);
            },
            error:function () {
                alert("错误")
            }
        });
    })
</script>

controller

private Map excel(){
        Map map =new HashMap();
        map.put("status","false");
        try {
            log.info("recent inactive hospital statistics start");
            //命名行名
            List<String> cellNameList = new ArrayList<>();
            cellNameList.add("医院id");
            cellNameList.add("医院名称");
            cellNameList.add("渠道号");
            cellNameList.add("联系人");
            cellNameList.add("总关注数");
            cellNameList.add("不活跃天数");
            cellNameList.add("最后活跃日期");
            cellNameList.add("申请日期");
            cellNameList.add("创建天数");
            //给文件命名及设置路径,如果该路径下有该文件则覆盖该文件
            String excelPath="/Users/user/Desktop/不活泼医院信息.xls";
            //给表命名
            String title=LocalDate.now().minusDays(14)+"至"+LocalDate.now().minusDays(8)+"不活泼医院信息";
            HSSFWorkbook excel = Excel.createExcel(title, cellNameList);
            //写数据，这里随便写点数据（可以从数据库读数据然后循环写）
            for(int i=1;i<5;i++){
                List<String> excelData = new ArrayList<>();
                excelData.add(i+"");
                excelData.add("name");
                excelData.add(i+"");
                excelData.add("联系人");
                excelData.add("总关注数");
                excelData.add("不活跃天数");
                excelData.add("最后活跃日期");
                excelData.add("申请日期");
                excelData.add("创建天数");
                excel = Excel.createExcelData(excel, excelData, i);
            }
            //输出数据
            FileOutputStream fos = new FileOutputStream(excelPath);
            excel.write(fos);
            fos.close();
            map.put("status","true");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return map;
    }

util包的工具类

public class Excel {

    public static HSSFWorkbook createExcel(String sheetName, List<String> cellNameList) {

        HSSFWorkbook excel = new HSSFWorkbook();
        HSSFSheet sheet = excel.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        int cellIndex = 0;
        for (String cellName : cellNameList) {
            HSSFCell cell = row.createCell(cellIndex);
            cell.setCellValue(cellName);
            cellIndex++;
        }
        return excel;
    }

    public static HSSFWorkbook createExcelData(HSSFWorkbook excel, List<String> excelData,int rowIndex){
        HSSFRow row=excel.getSheetAt(0).createRow(rowIndex);
        for(int i = 0; i < excelData.size(); i++){
            row.createCell(i).setCellValue(excelData.get(i));
        }
        return excel;
    }


}