页面下载Excel POI操作Excel

最新推荐文章于 2025-09-05 14:24:44 发布

weixin_34327223

最新推荐文章于 2025-09-05 14:24:44 发布

阅读量53

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/xiaoxian1369/archive/2013/05/10/3071242.html

本文介绍了一种使用Java将数据库数据导出至Excel的方法，并实现了数据的自动汇总及平均值计算。通过创建样式、设置单元格类型及宽度等操作，确保了表格的美观性和数据的准确性。

// 从数据库中提取数据保存到Excel中
    public void downExcel(HttpServletResponse response, List fwmxList,
            String fileName) throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户访问明细");

        // 设置公式自动计算
        // sheet.setForceFormulaRecalculation(true);

        // 设置单元格下边框
        HSSFCellStyle style1 = workbook.createCellStyle();
        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        // 设置单元格上边框
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);

        // 设置单元格宽度
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3000);

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("访问时间");
        cell.setCellStyle(style1);
        cell = row.createCell(1);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("访问人数");
        cell.setCellStyle(style1);

        int size = fwmxList.size() + 1, totalCount = 0;
        for (int i = 1; i < size; i++) {
            row = sheet.createRow(i);
            Map mapRow = (Map) fwmxList.get(i - 1);
            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(mapRow.get("TIME").toString());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            int count = Integer.parseInt(mapRow.get("SJ_Z").toString());
            cell.setCellValue(count);
            totalCount += count;
        }
        row = sheet.createRow(size);

        cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("访问总数");
        cell.setCellStyle(style2);

        cell = row.createCell(1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        // String strFormula = "sum(B2:B" + size + ")";// 设置求和公式
        // cell.setCellFormula(strFormula);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(totalCount);
        cell.setCellStyle(style2);

        row = sheet.createRow(size + 1);

        cell = row.createCell(0);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue("平均访问数");

        cell = row.createCell(1);
        // cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        // strFormula = "rounddown(average(B2:B" + size + "),0)";// 设置求平均值与取整数公式
        // cell.setCellFormula(strFormula);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellValue(totalCount / (size - 1));

        // 将内容保存到文件fileName中
        OutputStream outputStream = null;
        File file = new File(fileName);
        // System.out.println(file.getAbsolutePath());
        outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();

        // 下载设置
        response.setHeader("Content-Disposition", "inline;filename="
                + URLEncoder.encode(fileName, "UTF-8"));
        // response.setHeader("Content-Disposition", "attachment;filename="
        // + URLEncoder.encode(fileName, "UTF-8"));
        // response.setContentType("application/msexcel;charset=UTF-8");//
        // 文件名为中文，设置编码为UTF-8
        response.setContentType("application/vnd.ms-excel;charset=utf-8");

        ServletOutputStream servletOutputStream = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        bis = new BufferedInputStream(new FileInputStream(fileName));
        bos = new BufferedOutputStream(servletOutputStream);
        byte[] buff = new byte[2048];
        int buffLen = -1;
        while ((buffLen = bis.read(buff, 0, buff.length)) != -1) {
            bos.write(buff, 0, buffLen);
        }
        bos.close();
        bis.close();
        file.delete();
    }