poi导出Excel

最新推荐文章于 2022-10-12 00:54:53 发布

HelloWorld小能手

最新推荐文章于 2022-10-12 00:54:53 发布

阅读量256

点赞数

分类专栏：工具后端文章标签： poi java excel

本文链接：https://blog.youkuaiyun.com/qq_43379834/article/details/118792825

版权

工具同时被 2 个专栏收录

9 篇文章

订阅专栏

后端

8 篇文章

订阅专栏

这段代码展示了如何使用Apache POI库从数据库查询数据并创建Excel文件进行导出。首先，从数据库获取交易信息数据，然后初始化并填充Excel工作簿，包括设置标题行和数据行。最后，通过HTTP响应将生成的Excel文件发送给客户端。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.使用poi导出Excel，需要poi的jar包

 /**
     *
     *  Excel导出
     *
     *  @作者：     zktian
     *  @参数：     workbook；  createExcelname Excel名
     *  @返回值：
     *
     *  @修改记录（修改时间、作者、原因）：
     */
    public static boolean OutExcel(HttpServletRequest request, HttpServletResponse response, Workbook workbook,String createExcelname) throws Exception {

        boolean message = false;
        String dir = request.getSession().getServletContext().getRealPath("/output");
        File fileLocation = new File(dir);
        if (!fileLocation.exists()) {
            boolean isCreated = fileLocation.mkdir();
            if (!isCreated) {
            }
        }
        String webUrl = request.getSession().getServletContext().getRealPath("/output");
        String outputFile = webUrl + File.separator + createExcelname;

        FileOutputStream fOut = new FileOutputStream(outputFile);
        workbook.write(fOut);
        fOut.flush();
        fOut.close();
        File f = new File(outputFile);
        if (f.exists() && f.isFile()) {
            try {
                FileInputStream fis = new FileInputStream(f);
                URLEncoder.encode(f.getName(), "utf-8");
                byte[] b = new byte[fis.available()];
                fis.read(b);
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + createExcelname + "");
                ServletOutputStream out = response.getOutputStream();
                out.write(b);
                out.flush();
                out.close();
                if (fis != null) {
                    fis.close();
                }
                f.delete();
                message = true;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return message;
    }

2.测试

        //从数据库查询数据
        List<Map<String, Object>> list = this.getjyxx();
        if (list == null || list.size() == 0) {//判空
            return false;
        }
        //初始化Excel表格
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //设置Excel工作表
        workbook.setSheetName(0, "交易信息");
        //设置标题行
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell0 = row1.createCell(0, CellType.STRING);
        HSSFCell cell1 = row1.createCell(1, CellType.STRING);
        HSSFCell cell2 = row1.createCell(2, CellType.STRING);
        HSSFCell cell3 = row1.createCell(3, CellType.STRING);
        HSSFCell cell4 = row1.createCell(4, CellType.STRING);
        HSSFCell cell5 = row1.createCell(5, CellType.STRING);
        cell0.setCellValue("序号");
        cell1.setCellValue("名称");
        cell2.setCellValue("金额（万元）");
        cell3.setCellValue("类别");
        cell4.setCellValue("方式");
        cell5.setCellValue("日期");
        //逐行写入数据
        for (int i = 0; i < list.size(); i++) {
            Map map = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell c0 = row.createCell(0, CellType.STRING);
            HSSFCell c1 = row.createCell(1, CellType.STRING);
            HSSFCell c2 = row.createCell(2, CellType.STRING);
            HSSFCell c3 = row.createCell(3, CellType.STRING);
            HSSFCell c4 = row.createCell(4, CellType.STRING);
            HSSFCell c5 = row.createCell(5, CellType.STRING);
            c0.setCellValue(i + 1);
            c1.setCellValue(map.get("xmmc") != null ? map.get("mc").toString() : "名为空！");
            c2.setCellValue(map.get("xmje") != null ? map.get("je").toString() : "0");
            c3.setCellValue(map.get("jylb") != null ? map.get("lb").toString() : "0");
            c4.setCellValue(map.get("jyfs") != null ? map.get("fs").toString() : "0");
            c5.setCellValue(map.get("jyrq") != null ? DateTimeUtil.cnDate((Number) map.get("rq")) : "0");
        }
        try {
            //定义文件名
            String fileName = DateTimeUtil.now8() + "交易信息.xls";
            response.setContentType("application/octet-stream");
            //防乱码
            fileName = new String(fileName.getBytes(), "ISO-8859-1");
            boolean flag= ExcelUtils.OutExcel(request, response, workbook, fileName);
            return flag;
        } catch (Exception e) {
            //log error
            log.error(e.getMessage(),e);
        }
        return false;