java中的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、前端代码

<script src="../../js/jquery-1.11.1.min.js"></script>
<script src="../../js/bootstrap.min.js"></script>
<script src="../../js/bootstrap-modal.js"></script>

<button type="button" class="btn btn-primary" onclick="stockPrizeExport()">库存导出</button>

/**导出库存清单*/
function stockPrizeExport(){
    debugger;
    var index = layer.confirm('确定要导出现有库存奖品吗？', {
        btn: ['确定','取消'],title: "提示" //按钮
    }, function(){
        $("#global_body").mask("数据正在下载，请耐心等待...");
        $.ajax({
            url: '/activityList/stockPrizeExport.do',
            type: 'post',
            async: true,
            dataType : "json",
            error : function(data) {
                $("#global_body").unmask();
                layer.alert("库存奖品导出异常！");
            },
            success : function(data) {
                debugger;
                $("#global_body").unmask();
                if(data.resultCode==200){
                    window.location.href = data.path;
                }
                layer.close(index);
            }
        });
    });
}

3、后台代码

String filePath = request.getSession().getServletContext().getRealPath(File.separator) + "/excleFile/stockListExport/";
/**获取需要下载的Excle文件的名称*/
String fileName = activityManagerService.getDownloadExcleFilePath(filePath);
String path = "/excleFile/stockListExport/" + fileName;

/**
 * 获取需要下载文件名称
 *
 * @param parameterMap
 * @return
 */
@Override
@Transactional
public String getDownloadExcleFilePath(String filePath) throws Exception {

    String returnExclePath = "";//需要返回的文件名称
    String downloadExcleFileName = "";//需要下载的Excle的名称
    XSSFWorkbook workbook = new XSSFWorkbook();
    /**库存奖品清单*/
    List<Map<String, Object>> stockPrizeList = activityManagerDao.stockPrizeExport();
    workbook = exportExcel(stockPrizeList);
    downloadExcleFileName = "库存奖品清单";//需要导出的Excle的名称
    OutputStream out = null;
    File file1 = new File(filePath);
    //如果文件夹不存在则创建
    if (!file1.exists()) {
        file1.mkdir();
    }
    String fileName = downloadExcleFileName + ".xlsx";
    File file = new File(filePath + fileName);
    if (file.exists()) {
        file.delete();
    }
    out = new FileOutputStream(filePath + fileName); // 输出目的地
    workbook.write(out);

    returnExclePath = fileName;
    return returnExclePath;
}

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

        if (dataList != null && dataList.size() > 0) {
            //创建表头
            XSSFRow row = sheet.createRow((short) 0);
            this.createCell(row, 0, style, XSSFCell.CELL_TYPE_STRING, "序号");
            this.createCell(row, 1, style, XSSFCell.CELL_TYPE_STRING, "奖品名称");
            this.createCell(row, 2, style, XSSFCell.CELL_TYPE_STRING, "奖品规格");
            this.createCell(row, 3, style, XSSFCell.CELL_TYPE_STRING, "剩余件数");
            this.createCell(row, 4, style, XSSFCell.CELL_TYPE_STRING, "奖品类型");
            this.createCell(row, 5, style, XSSFCell.CELL_TYPE_STRING, "库存导入人");
            this.createCell(row, 6, style, XSSFCell.CELL_TYPE_STRING, "库存导入时间");
            this.createCell(row, 7, style, XSSFCell.CELL_TYPE_STRING, "库存奖品状态");

            //给excel填充数据
            for (int i = 0; i < dataList.size(); i++) {
                //填充数据
                Map<String, String> data = (Map<String, String>) dataList.get(i);
                //创建新行
                XSSFRow row1 = sheet.createRow((short) (i + 1));
                this.createCell(row1, 0, style, XSSFCell.CELL_TYPE_STRING, i + 1);

                if (data.get("stock_prize_name") != null) {//奖品名称
                    this.createCell(row1, 1, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_prize_name"));
                }
                if (data.get("stock_prize_model") != null) {//奖品规格
                    this.createCell(row1, 2, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_prize_model"));
                }

                if (data.get("stock_overplus_count") != null) {//剩余件数
                    this.createCell(row1, 3, style, XSSFCell.CELL_TYPE_STRING, data.get("stock_overplus_count"));
                }
                if (data.get("stock_prize_type") != null) {//奖品类型
                    String stockPrizeType = "";//奖品类型
                    switch (data.get("stock_prize_type")) {
                        case "1":
                            stockPrizeType = "电子卷";
                            break;
                        case "2":
                            stockPrizeType = "实物";
                            break;
                    }
                    this.createCell(row1, 4, style, XSSFCell.CELL_TYPE_STRING, stockPrizeType);
                }
                if (data.get("creation_by") != null) {//库存导入人
                    this.createCell(row1, 5, style, XSSFCell.CELL_TYPE_STRING, data.get("creation_by"));
                }
                if (data.get("creation_date") != null) {//库存导入时间
                    this.createCell(row1, 6, style, XSSFCell.CELL_TYPE_STRING, data.get("creation_date"));
                }
                if (data.get("stock_prize_status") != null) {//库存奖品状态
                    String stockPrizeStatus = "";//库存奖品状态
                    switch (data.get("stock_prize_status")) {
                        case "0":
                            stockPrizeStatus = "禁用";
                            break;
                        case "1":
                            stockPrizeStatus = "启用";
                            break;
                    }
                    this.createCell(row1, 7, style, XSSFCell.CELL_TYPE_STRING, stockPrizeStatus);
                }
            }
        } else {
            this.createCell(sheet.createRow(0), 0, style, XSSFCell.CELL_TYPE_STRING, "查无资料");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return workbook;
}

/**
 * 设置列宽与列数
 *
 * @param sheet
 */
private void setSheetColumnWidth(XSSFSheet sheet, Integer type) throws Exception {
    // 根据你数据里面的记录有多少列，就设置多少列
    sheet.setColumnWidth(0, 1000);
    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 2000);
    sheet.setColumnWidth(4, 2000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 6000);
    sheet.setColumnWidth(7, 3000);
}

/**
 * 设置excel的title样式
 *
 * @param wb
 * @return
 */
private XSSFCellStyle createTitleStyle(XSSFWorkbook wb) throws Exception {
    XSSFFont boldFont = wb.createFont();
    boldFont.setFontHeight((short) 200);
    XSSFCellStyle style = wb.createCellStyle();
    style.setFont(boldFont);
    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
    return style;
}

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