java中.xls格式的Excel下载（POI）

原创已于 2023-12-20 09:53:43 修改 · 518 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#excel

于 2019-02-25 19:35:00 首次发布

java 专栏收录该内容

37 篇文章

订阅专栏

本文详细介绍了如何使用Apache POI库在Java环境中创建并填充Excel文件。从Maven依赖配置开始，到创建Excel工作簿，填充数据，设置样式和列宽，直至最终将文件输出到指定路径。适合初学者和需要快速实现Excel导出功能的开发者。

1、第一步在pom.xml文件中添加maven依赖

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.9</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.9</version>
</dependency>

2、第二步

String path = request.getSession().getServletContext().getRealPath(File.separator) + "/activity/downloadFile/";
String returnExclePath = "";//需要返回的路径
String downloadExcleFileName = "";//需要下载的Excle的名称
HSSFWorkbook workbook = new HSSFWorkbook();
/**需要往Excle表中写入的数据*/
List<Map<String, Object>> activityPrizeList = activityManagerDao.getActivityPrizeList(actId);
workbook = exportExcel(activityPrizeList);
downloadExcleFileName = "奖品清单";//需要下载的Excle的名称
OutputStream out = null;
File file1 = new File(path);
//如果文件夹不存在则创建
if (!file1.exists()) {
    file1.mkdir();
}
String fileName = downloadExcleFileName + ".xls";
File file = new File(path + fileName);
if (file.exists()) {
    file.delete();
}
out = new FileOutputStream(path + fileName); // 输出目的地
workbook.write(out);

returnExclePath = "/activity/downloadFile/" + fileName;//返回值为returnExclePath

3、第三步

/**给Excle中填充数据*/
private HSSFWorkbook exportExcel(List dataList) throws Exception {
    HSSFWorkbook workbook = null;
    try {
        workbook = new HSSFWorkbook();
        //创建工作表实例
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //设置列宽
        this.setSheetColumnWidth(sheet);
        //获取样式
        HSSFCellStyle style = this.createTitleStyle(workbook);

        if (dataList != null && dataList.size() > 0) {
            //创建表头
            HSSFRow row = sheet.createRow((short) 0);
            this.createCell(row, 0, style, HSSFCell.CELL_TYPE_STRING, "序号");          
            this.createCell(row, 1, style, HSSFCell.CELL_TYPE_STRING, "活动名称");
            this.createCell(row, 2, style, HSSFCell.CELL_TYPE_STRING, "奖品名称");
            this.createCell(row, 3, style, HSSFCell.CELL_TYPE_STRING, "奖品规格");
            this.createCell(row, 4, style, HSSFCell.CELL_TYPE_STRING, "奖品类型");
            this.createCell(row, 5, style, HSSFCell.CELL_TYPE_STRING, "总数量");
            this.createCell(row, 6, style, HSSFCell.CELL_TYPE_STRING, "已中奖数");
           
            //给excel填充数据
            for (int i = 0; i < dataList.size(); i++) {
                //填充数据
                Map<String, String> data = (Map<String, String>) dataList.get(i);
                //创建新行
                HSSFRow row1 = sheet.createRow((short) (i + 1));
                this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING, i + 1);

                if (data.get("act_name") != null) {//库存活动名称
                    this.createCell(row1, 1, style, HSSFCell.CELL_TYPE_STRING, data.get("act_name"));
                }
                if (data.get("prize_name") != null) {//奖品名称
                    this.createCell(row1, 2, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_name"));
                }
                if (data.get("stock_prize_model") != null) {//奖品规格
                    this.createCell(row1, 3, style, HSSFCell.CELL_TYPE_STRING, data.get("stock_prize_model"));
                }
                if (data.get("prize_type") != null) {//奖品类型
                    String prizeType = "";//奖品类型
                    switch ( data.get("prize_type") ) {
                        case "1":
                            prizeType = "电子卷";
                            break;
                        case "2":
                            prizeType = "实物";
                            break;
                        case "3":
                            prizeType = "谢谢参与";
                            break;
                    }
                    this.createCell(row1, 4, style, HSSFCell.CELL_TYPE_STRING, prizeType);
                }
                if (data.get("prize_count") != null) {//总数量
                    this.createCell(row1, 5, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_count"));
                }
                if (data.get("prize_already_count") != null) {//已中奖数
                    this.createCell(row1, 6, style, HSSFCell.CELL_TYPE_STRING, data.get("prize_already_count"));
                }
             }
        } else {
            this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return workbook;
}

4、第四步

/**设置列宽与列数*/
private void setSheetColumnWidth(HSSFSheet sheet,Integer type) {
    // 根据你数据里面的记录有多少列，就设置多少列
    sheet.setColumnWidth(0, 1000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 6000);
    sheet.setColumnWidth(3, 6000);
    sheet.setColumnWidth(4, 8000);
    sheet.setColumnWidth(5, 6000);
    sheet.setColumnWidth(6, 5000);
}

5、第五步

/** 设置excel的title样式*/
private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
    HSSFFont boldFont = wb.createFont();
    boldFont.setFontHeight((short) 200);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(boldFont);
    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
    return style;
}

6、第六步

/** 创建Excel单元格*/
private void createCell(HSSFRow row, int column, HSSFCellStyle style, int cellType, Object value) {
    HSSFCell cell = row.createCell(column);
    if (style != null) {
        cell.setCellStyle(style);
    }
    switch (cellType) {
        case HSSFCell.CELL_TYPE_BLANK: {
        }
        break;
        case HSSFCell.CELL_TYPE_STRING: {
            cell.setCellValue(value.toString());
        }
        break;
        case HSSFCell.CELL_TYPE_NUMERIC: {
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Double.parseDouble(value.toString()));
        }
        break;
        default:
            break;
    }
}

以上代码为自己在工作中亲自编写