java 把数据导出导出excl

java 把数据导出导出excl

首先pom中引入相关jar

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
 </dependency>

查询出需要导出的数据构建成List

private HSSFWorkbook buildHSSFWorkbook(List<JSONObject> rows) {
        try {
  			HSSFWorkbook wb = new HSSFWorkbook();
            //定义样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
            HSSFFont headerFont = wb.createFont(); // 创建字体样式
            headerFont.setBold(true); // 字体加粗
            headerFont.setFontName("宋体"); // 设置字体类型
            headerFont.setFontHeightInPoints((short) 16); // 设置字体大小
            style.setFont(headerFont); // 为标题样式设置字体样式
            HSSFSheet hssfSheet = wb.createSheet("sheet名称");
            HSSFCell hssfCell = hssfSheet.createRow(0).createCell(0);
            hssfCell.setCellValue("标题名称");
            hssfCell.setCellStyle(style);
            //14为总列数
            hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));
            HSSFRow hssfRow1 = hssfSheet.createRow(1);
            for (int i = 0; i < 12; i++) {
                hssfSheet.setColumnWidth(i, 22 * 256);
            }
            hssfRow1.createCell(0).setCellValue("第1列名称");
            hssfRow1.createCell(1).setCellValue("第2列名称");
            hssfRow1.createCell(2).setCellValue("第3列名称");
            hssfRow1.createCell(3).setCellValue("第4列名称");
            hssfRow1.createCell(4).setCellValue("第5列名称");
            hssfRow1.createCell(5).setCellValue("第6列名称");
            hssfRow1.createCell(6).setCellValue("第7列名称");
            hssfRow1.createCell(7).setCellValue("第8列名称");
            hssfRow1.createCell(8).setCellValue("第9列名称");
            hssfRow1.createCell(9).setCellValue("第10列名称");
            hssfRow1.createCell(10).setCellValue("第11列名称");
            hssfRow1.createCell(11).setCellValue("第12列名称");
            hssfRow1.createCell(12).setCellValue("第13列名称");
            hssfRow1.createCell(13).setCellValue("第14列名称");
            hssfRow1.createCell(14).setCellValue("第15列名称");
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            int i = 0;
            for (JSONObject item : rows) {
                //设置偏移行
                HSSFRow hssfRow = hssfSheet.createRow(2 + i);
                hssfRow.createCell(0).setCellValue(item.getString("column1"))；
                hssfRow.createCell(1).setCellValue(item.getString("column2"));
                hssfRow.createCell(2).setCellValue(item.getString("column3"));
                hssfRow.createCell(3).setCellValue(item.getString("column4"));
                hssfRow.createCell(4).setCellValue(item.getString("column5"));
                hssfRow.createCell(5).setCellValue(item.getString("column6"));
                hssfRow.createCell(6).setCellValue(item.getString("column7"));
                hssfRow.createCell(7).setCellValue(item.getString("column8"));
                if (item.getDate("column9") == 0) {
                    hssfRow.createCell(8).setCellValue("");
                } else {
                    hssfRow.createCell(8).setCellValue(format.format(item.getDate("column9")));
                }
                String names = "";
                for (Object obj: item.getJSONArray("column10")) {
                    JSONObject json = JSON.parseObject(JSON.toJSONString(obj), JSONObject.class);
                    names += json.getString("name") + ",";
                }
                hssfRow.createCell(9).setCellValue(names.substring(0, names.length() - 1));
                hssfRow.createCell(10).setCellValue(item.getString("column11"));
                hssfRow.createCell(11).setCellValue(item.getString("column12") != null ?item.getString("column12") : "");
                hssfRow.createCell(12).setCellValue(item.getDouble("column13"));
                hssfRow.createCell(13).setCellValue(format.format(item.getDate("column14")));
                hssfRow.createCell(14).setCellValue(item.getInteger("column15"));
                i++;
            }
            if (wb != null) {
                return wb;
            }
        } catch (Exception e) {
            log.error("构建excl错误:{}", e);
            return null;
        }
        return null;
    }

构建完HSSFWorkbook后向浏览器输出

/**
     * 输出到浏览器
     * @param response http响应
     * @param fileName 文件名称带后缀 .xls
     * @param wb 构建好的HSSFWorkbook 
     */
private void setResponseHeader(HttpServletResponse response, String fileName, HSSFWorkbook wb) {
        try {
            try {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                log.error("Exception:{}", e);
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception ex) {
            log.error("Exception:{}", ex.getMessage(), ex);
        }
    }