使用Apache POI 进行Excel报表的导出下载（GET 请求 && POST请求）

最新推荐文章于 2024-05-21 16:23:54 发布

置顶图图学Java

最新推荐文章于 2024-05-21 16:23:54 发布

阅读量4.8k

点赞数 2

分类专栏： POI 文章标签： Apache poi Excel post

本文链接：https://blog.csdn.net/daobantutu/article/details/79282687

版权

POI 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

简介：之前项目有这样的需求：按各种条件进行筛选数据，然后导出Excel到本地

针对条件条件很少的情况下，使用的是GET请求的方式，直接在URL上拼接请求参数，这种形式很简单就能导出Excel。但是当条件很多很复杂时，就不适合使用GET请求的方式了，所以我使用了POST请求的方式，在这其中遇到了各种问题，估计大家也可能遇见到了。

遇到问题：

请求数据传递正确，数据获取正确，Excel构建也没报错，但是就是不提示Excel下载，一切显得诡异得安静。
请求数据传递正确，数据获取正确，Excel构建也没报错，提示下载了，但是下载的是一个莫名的空Excel，并且文件名称是一串数字。

3.请求数据传递正确，数据获取正确,Excel构建也没报错，提示下载了，但是出现的结果时未找到对应的文件。

大概的问题就这样，具体的代码我就不贴了，我们来看看能实现效果的代码。

我这边使用的相关技术是： freemarker+Html+AngularJS+Hibernate+POI+AbstractXlsView

先来前端页面HTML

<style>
.div_parent{
    width:100%;
    height:700px;
    vertical-align: middle;
    position:relative;
}
.div_child {
    width:600px;
    height:400px;
    margin: auto;  
    position: absolute;  
    top: 0; left: 0; bottom: 0; right: 0; 
    border: 1px solid #b59898;
}
.div_child_custom{
    width:700px;
    height:580px;
    margin: auto;  
    position: absolute;  
    top: 0; left: 0; bottom: 0; right: 0; 
    border: 1px solid #b59898;
}
.div_tiaojian{
    display: inline;
    border: 1px dashed;
    margin-bottom: 10px;
}
.lable_tiaojian{
    padding-left: 0;
    margin-left: 20px;
}
</style>

<div class="well well-sm" style="background:white;">
    <ol class="breadcrumb">
        <li style="color:#0b8d39;padding-left:15px;"> 当前位置：</li>
        <li><a href="#/">首页</a></li>
        <li>决策辅助系统</li>
        <li><a href="javascript::" ui-sref="statisticalAnalysis">统计分析主页</a></li>
        <li class="active">{{vm.title}}</li>     
    </ol>

    <div class="div_parent">
        <!-- 项目总库固定类 -->
        <div class="div_child" ng-if="vm.isProjectFixed">
            <form class="form-horizontal" role="form" style="margin-top: 100px;">
                <div class="form-group"> 
                    <label class="col-md-3 control-label">筛选条件：</label>
                    <div class="col-md-5">
                        <select class="form-control" ng-model="vm.isIncludLibrary">
                            <option value="true" ng-selected="vm.isIncludLibrary == true">已纳入项目库</option>
                            <option value="false" ng-selected="vm.isIncludLibrary == false">未纳入项目库</option>
                        </select>
                    </div>
                </div>
                <div class="form-group">
                    <label class="col-md-3 control-label">导出项：</label>
                    <div class="col-md-9">
                        <div style="margin-bottom: 10px;">
                        <!--**get请求导出Excel，请求参数拼接在url上面**-->
                            <a class="btn btn-success btn-sm" href="/management/auxDeci/statisticalAnalysis/exportExcelForProject?classDesc=unit&isIncludLibrary={{vm.isIncludLibrary}}">按项目单位分类</a>
                            <a class="btn btn-success btn-sm" href="/management/auxDeci/statisticalAnalysis/exportExcelForProject?classDesc=category&isIncludLibrary={{vm.isIncludLibrary}}">按项目类别分类</a>
                        </div>
                        <div>
                            <a class="btn btn-success btn-sm" href="/management/auxDeci/statisticalAnalysis/exportExcelForProject?classDesc=industry&isIncludLibrary={{vm.isIncludLibrary}}">按项目行业分类</a>
                            <a class="btn btn-success btn-sm" href="/management/auxDeci/statisticalAnalysis/exportExcelForProject?classDesc=stage&isIncludLibrary={{vm.isIncludLibrary}}">按项目阶段分类</a>
                        </div>
                    </div>
                </div>
            </form> 
        </div>
        <!-- 项目总库自定义类 -->
        <div class="div_child_custom" ng-if="vm.isProjectCustom">
        <!--**post请求导出Excel，请求参数的绑定以及方法在js代码中，这里是触发**-->
            <form class="form-horizontal" role="form" style="margin-top: 10px;" ng-submit="vm.exportExcelForProjectByCustom()">
                <div class="form-group"> 
                    <label class="col-md-2 control-label lable_tiaojian">行业分类：</label>
                    <div class="col-md-9 div_tiaojian">
                        <label class="control-label" ng-repeat="x in vm.basicData.projectIndustry_ZF | orderBy:'itemOrder'">
                            <input type="checkbox" value="{{x.id}}" ng-click="vm.selectProjectIdustry(x.id)"/>{{x.description}}&nbsp;&nbsp;
                        </label>
                    </div>

                    <label class="col-md-2 control-label lable_tiaojian">项目阶段：</label>
                    <div class="col-md-9 div_tiaojian">
                        <label class="control-label" ng-repeat="x in vm.basicData.projectStage">
                            <input type="checkbox" value="{{x.id}}" ng-click="vm.selectProjectStage(x.id)"/>{{x.description}}&nbsp;&nbsp;
                        </label>
                    </div>

                    <label class="col-md-2 control-label lable_tiaojian">项目类别：</label>
                    <div class="col-md-9 div_tiaojian">
                        <label class="control-label" ng-repeat="x in vm.basicData.projectCategory">
                            <input type="checkbox" value="{{x.id}}" ng-click="vm.selectProjectCategory(x.id)"/>{{x.description}}&nbsp;&nbsp;
                        </label>
                    </div>

                    <label class="col-md-2 control-label lable_tiaojian">申报单位：</label>
                    <div class="col-md-9 div_tiaojian">
                        <label class="control-label" ng-repeat="x in vm.basicData.userUnit">
                            <input type="checkbox" value="{{x.id}}" ng-click="vm.selectProjectUnit(x.id)"/>{{x.unitName}}&nbsp;&nbsp;
                        </label>
                    </div>

                    <label class="col-md-2 control-label lable_tiaojian">总投资范围：</label>
                    <div class="col-md-9" style="margin-bottom: 10px;">
                        <input type="number" step="any" class="form-control" style="width:200px;" ng-model="vm.projectInvestSumBegin"/>
                        <span>~</span>
                        <input type="number" step="any" class="form-control" style="width:200px;" min="{{vm.projectInvestSumBegin}}" ng-model="vm.projectInvestSumEnd"/>
                        <span>（万元）</span>
                    </div>
                </div>
                <div class="form-group text-center">
                    <button type="submit" class="btn btn-success btn-sm">导出</button>
                </div>
            </form>
        </div>
    </div>
</div>

页面效果
这里写图片描述
get请求触发页面
post请求触发页面
js代码

原理是利用form表单提交完后之后跳转下载页面进行Excel的下载
//post请求下载文件
/**
 * options：{
 *  url：请求地址
 *  method：请求方法
 *  data：请求数据
 * }
 */
vm.postDownLoadFile = function (options) {
    var config = $.extend(true, { method: 'post' }, options);
    var $iframe = $('<iframe id="down-file-iframe" />');
    var $form = $('<form target="down-file-iframe" method="' + config.method + '" />');
    $form.attr('action', config.url);
    for (var key in config.data) {
        $form.append('<input type="hidden" name="' + key + '" value="' + config.data[key] + '" />');
    }
   $iframe.append($form);
    $(document.body).append($iframe);
    $form[0].submit();
    $iframe.remove();
}

vm.exportExcelForProjectByCustom=function(){
    vm.postDownLoadFile({
        url:url+"/exportExcelForProjectByCustom",//请求地址，替换就好了
        data:vm.model,//请求参数，使用对象封装或者json格式的键值对均可
        method:'post'
     });
}

后台java 控制类：GET请求的响应

/**get请求直接返回ModelAndView即可**/
@RequestMapping(name="项目总库-项目分类统计",path="exportExcelForProject",method=RequestMethod.GET)
public ModelAndView exportExcelForProject(HttpServletRequest request,@RequestParam String classDesc,@RequestParam String isIncludLibrary) throws ParseException{
    List<ProjectStatisticsBean> data = ProjectService.getProjectStatistics(classDesc,isIncludLibrary);
    return new ModelAndView(new ProjectStatisticsView(classDesc,isIncludLibrary), "data", data);
}
//get 请求很简单的，直接查询数据，构建Excel即可，例子中ProjectStatisticsView对象就是Excel页面的设计对象,对象的构造方法可有可无。

GET请求的Excel设置

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import cs.model.Statistics.ProjectStatisticsBean;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 
 * @ClassName: ProjectStatisticsView 
 * @Description: 项目总库项目导出Excel页面设计类
 * @author cx
 * @date 2018年1月24日 下午4:17:34 
 *
 */
public class ProjectStatisticsView extends AbstractXlsView {
    private String type;
    private String isIncludLibrary;
    public ProjectStatisticsView(String type,String isIncludLibrary){
        this.type=type;
        this.isIncludLibrary=isIncludLibrary; 
    }

    @SuppressWarnings("deprecation")
    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String typeDesc=type.equals("unit")?"项目单位":type.equals("category")?"项目类别":type.equals("industry")?"项目行业":type.equals("stage")?"项目阶段":"";
        isIncludLibrary=isIncludLibrary.equals("true")?"已纳入项目库项目":isIncludLibrary.equals("false")?"未纳入项目库项目":isIncludLibrary.equals("all")?"项目总库":"";

        String fileName = "光明新区政府投资项目库"+isIncludLibrary+typeDesc+"分类汇总表.xls";
        response.setHeader("Content-Disposition", "attachment;filename=" +new String(fileName.getBytes("gb2312"), "iso8859-1"));
        Sheet sheet = workbook.createSheet("表1");

        CellStyle cellStyleTitle = workbook.createCellStyle();
        CellStyle cellStyleSubTitleLeft = workbook.createCellStyle();
        CellStyle cellStyleO = workbook.createCellStyle();

        //设置字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14); // 字体高度
        font.setFontName(" 黑体 "); // 字体
        cellStyleTitle.setFont(font);
        //设置表格边框
        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        //创建行
        Row title=sheet.createRow(0);
        Row subTitle=sheet.createRow(1);
        Row row_head = sheet.createRow(2);

        //设置行高
        title.setHeight((short)800);
        subTitle.setHeight((short)500);
        row_head.setHeight((short)360);

        //begin#标题
        //创建列
        createCellAlignCenter(workbook,title,0,"光明新区政府投资项目库"+isIncludLibrary+typeDesc+"分类汇总表",cellStyleTitle);
        //合并标题
        //参数1：开始行、结束行、开始列、结束列
        CellRangeAddress cellRangeTitle = null;
        cellRangeTitle = type.equals("unit")?new CellRangeAddress(0,0,0,8):new CellRangeAddress(0,0,0,3);
        setRegionStyle(sheet,cellRangeTitle,cellStyleTitle);
        sheet.addMergedRegion(cellRangeTitle);
        //end#标题

        //begin#子标题
        createCellAlignLeft(workbook,subTitle,0,"打印日期："+new SimpleDateFormat("yyyy年MM月dd日").format(new Date()),cellStyleSubTitleLeft);
        CellRangeAddress cellRangeSubTitleLeft = null;
        if(type.equals("unit")){
             //设置子标题列宽
            sheet.setColumnWidth(1, 256*30+184);
            sheet.setColumnWidth(2, 256*13+184);
            sheet.setColumnWidth(7, 256*16+184);
            sheet.setColumnWidth(8, 256*15+184);

            createCellAlignRight(workbook,subTitle,8,"资金：万   元\n面积：平方米",workbook.createCellStyle());
            cellRangeSubTitleLeft = new CellRangeAddress(1,1,0,7);
        }else{
             //设置子标题列宽
            sheet.setColumnWidth(1, 256*26+184);
            sheet.setColumnWidth(2, 256*25+184);
            sheet.setColumnWidth(3, 256*27+184);

            createCellAlignRight(workbook,subTitle,3,"资金：万   元\n面积：平方米",workbook.createCellStyle());
            cellRangeSubTitleLeft = new CellRangeAddress(1,1,0,2);
        }
        setRegionStyle(sheet,cellRangeSubTitleLeft,cellStyleSubTitleLeft);
        sheet.addMergedRegion(cellRangeSubTitleLeft);
        //end#子标题

        //begin表格头
        createCellAlignCenter(workbook,row_head,0,"序号",cellStyleO);
        if(type.equals("unit")){
            Row row_subHead = sheet.createRow(3);
            row_subHead.setHeight((short)400);
            createCellAlignCenter(workbook,row_head,1,"项目单位",cellStyleO);
            createCellAlignCenter(workbook,row_head,2,"项目数量",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,2,"前期储备阶段",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,3,"前期阶段",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,4,"施工阶段",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,5,"停工阶段",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,6,"竣工阶段",cellStyleO);
            createCellAlignCenter(workbook,row_subHead,7,"固定资产登记阶段",cellStyleO);
            createCellAlignCenter(workbook,row_head,8,"项目总投资",cellStyleO);

            CellRangeAddress cellRange0 = new CellRangeAddress(2,3,0,0);
            CellRangeAddress cellRange1 = new CellRangeAddress(2,3,1,1);
            CellRangeAddress cellRange2 = new CellRangeAddress(2,2,2,7);
            CellRangeAddress cellRange3 = new CellRangeAddress(2,3,8,8);

            setRegionStyle(sheet,cellRange0,cellStyleO);
            setRegionStyle(sheet,cellRange1,cellStyleO);
            setRegionStyle(sheet,cellRange2,cellStyleO);
            setRegionStyle(sheet,cellRange3,cellStyleO);

            sheet.addMergedRegion(cellRange0);
            sheet.addMergedRegion(cellRange1);
            sheet.addMergedRegion(cellRange2);
            sheet.addMergedRegion(cellRange3);
        }
        else{
            createCellAlignCenter(workbook,row_head,1,typeDesc,cellStyleO);
            createCellAlignCenter(workbook,row_head,2,"项目数量",cellStyleO);
            createCellAlignCenter(workbook,row_head,3,"项目总投资",cellStyleO);
        }
        //end#表格头

        //begin#数据列
        int rowNum=type.equals("unit")?4:3;//数据加载开始行
        int index=1;


        @SuppressWarnings("unchecked")
        List<ProjectStatisticsBean> data = (List<ProjectStatisticsBean>) model.get("data");
        for (ProjectStatisticsBean obj:data) {
            Row row = sheet.createRow(rowNum);
            //创建数据
            createCellAlignCenter(workbook,row,0, index,cellStyleO);//序号
            createCellAlignCenter(workbook,row,1, obj.getClassDesc(),cellStyleO);//项目统计分类
            if(type.equals("unit")){
                createCellAlignCenter(workbook,row,2, obj.getPrereserveNumbers(),cellStyleO);//前期储备阶段项目数
                createCellAlignCenter(workbook,row,3, obj.getPreNumbers(),cellStyleO);//前期阶段项目数
                createCellAlignCenter(workbook,row,4, obj.getConstructionNumbers(),cellStyleO);//施工阶段项目数
                createCellAlignCenter(workbook,row,5, obj.getShutdownNumbers(),cellStyleO);//停工阶段项目数
                createCellAlignCenter(workbook,row,6, obj.getCompletedNumbers(),cellStyleO);//竣工阶段项目数
                createCellAlignCenter(workbook,row,7, obj.getFixedAssetsNumbers(),cellStyleO);//固定资产登记阶段项目数
                createCellAlignCenter(workbook,row,8, obj.getProjectInvestSum(),cellStyleO);//项目总投资
            }else{
                createCellAlignCenter(workbook,row,2, obj.getProjectNumbers(),cellStyleO);//项目总数
                createCellAlignCenter(workbook,row,3, obj.getProjectInvestSum(),cellStyleO);//项目总投资
            }
            rowNum++;index++;
        }
        //end#数据列
    }
//创建值为string字体居中的单元格    
    @SuppressWarnings("deprecation")
    private void createCellAlignCenter(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为double字体居中的单元格    
    private void createCellAlignCenter(Workbook workbook,Row row, int cellNumber,double value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为string字体居左的单元格
    private void createCellAlignLeft(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_LEFT,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为string字体居右的单元格    
    private void createCellAlignRight(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_RIGHT,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//重写创建列
    private void createCell(Workbook workbook,Row row, int cellNumber,String value, short halign, short valign,CellStyle cellStyle){
        Cell cell=row.createCell(cellNumber);
        cell.setCellValue(value);

        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cellStyle.setWrapText(true);
      //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    @SuppressWarnings("deprecation")
  //重写创建列
    private void createCell(Workbook workbook,Row row, int cellNumber,double value, short halign, short valign,CellStyle cellStyle){
        Cell cell=row.createCell(cellNumber);// 创建单元格
        cell.setCellValue(value);// 设置值

        cellStyle.setAlignment(halign);// 设置单元格水平方向对齐方式
        cellStyle.setVerticalAlignment(valign);// 设置单元格垂直方向对齐方式
        cellStyle.setWrapText(true);
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }


    /**
     * 
     * @Title: setRegionStyle 
     * @Description: 设置单元格样式
     * @param sheet 当前表
     * @param region 合并列
     * @param cs  样式
     */
    public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = (HSSFRow) sheet.getRow(i);
            if (row == null)
                row = (HSSFRow) sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }
}

结果秀一秀
这里写图片描述

后台java 控制类：POST请求的响应有区别了

@RequestMapping(name="项目总库-自定义条件统计",path="exportExcelForProjectByCustom",method=RequestMethod.POST)
@ResponseStatus(value = HttpStatus.NO_CONTENT)
public void exportExcelForProjectByCustom(HttpServletRequest request,HttpServletResponse response) throws Exception{
    // 读取请求参数
   String investSumBeginStr= request.getParameter("projectInvestSumBegin");
   String investSumEndStr= request.getParameter("projectInvestSumEnd");
   String industrySelect=request.getParameter("industry");
   String stageSelect=request.getParameter("stage");
   String categorySelect=request.getParameter("category");
   String unitSelect=request.getParameter("unit");
   //处理请求参数
   Double investSumBegin = Util.isNotNull(investSumBeginStr)?Double.valueOf(investSumBeginStr):null;
   Double investSumEnd = Util.isNotNull(investSumEndStr)?Double.valueOf(investSumEndStr):null;
   String[] industrySelected = Util.isNotNull(industrySelect)?industrySelect.split(","):null;
   String[] stageSelected = Util.isNotNull(stageSelect)?stageSelect.split(","):null;
   String[] categorySelected = Util.isNotNull(categorySelect)?categorySelect.split(","):null;
   String[] unitSelected = Util.isNotNull(unitSelect)?unitSelect.split(","):null;

   //查询获取数据
   List<ProjectStatisticsBean> data = ProjectService.getProjectStatisticsByCustom(industrySelected,stageSelected,categorySelected,unitSelected,investSumBegin,investSumEnd);

   try {
       String fileName="光明新区政府投资项目总库统计表.xls";
       String newexcelname = new String(fileName.getBytes("utf-8"),"ISO_8859_1");
       response.reset();
       response.setContentType("APPLICATION/OCTET-STREAM");
       response.setHeader("Content-disposition", "attachment; filename=\"" + newexcelname + "\""); // 实现下载
       HSSFWorkbook workbook = new GenerateExcelForProject().getHSSFWorkBook(data);//构建Excel
       workbook.write(response.getOutputStream());// 实现输出
       response.flushBuffer();
  }catch (Exception e) {
    e.printStackTrace();
  }
}

Excel构建也差不多的

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import cs.model.Statistics.ProjectStatisticsBean;

public class GenerateExcelForProject {

    @SuppressWarnings("deprecation")
    public HSSFWorkbook getHSSFWorkBook(List<ProjectStatisticsBean> data){

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("表1");

        CellStyle cellStyleTitle = workbook.createCellStyle();
        CellStyle cellStyleSubTitleLeft = workbook.createCellStyle();
        CellStyle cellStyleO = workbook.createCellStyle();

        //设置字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 14); // 字体高度
        font.setFontName(" 黑体 "); // 字体
        cellStyleTitle.setFont(font);
        //设置表格边框
        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleSubTitleLeft.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleO.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        //创建行
        Row title=sheet.createRow(0);
        Row subTitle=sheet.createRow(1);
        Row row_head = sheet.createRow(2);

        //设置行高
        title.setHeight((short)800);
        subTitle.setHeight((short)500);
        row_head.setHeight((short)360);

        //begin#标题
        //创建列
        createCellAlignCenter(workbook,title,0,"光明新区政府投资项目总库统计表",cellStyleTitle);
        //合并标题
        //参数1：开始行、结束行、开始列、结束列
        CellRangeAddress cellRangeTitle = null;
        cellRangeTitle = new CellRangeAddress(0,0,0,8);
        setRegionStyle(sheet,cellRangeTitle,cellStyleTitle);
        sheet.addMergedRegion(cellRangeTitle);
        //end#标题

        //begin#子标题
        createCellAlignLeft(workbook,subTitle,0,"打印日期："+new SimpleDateFormat("yyyy年MM月dd日").format(new Date()),cellStyleSubTitleLeft);
        CellRangeAddress cellRangeSubTitleLeft = new CellRangeAddress(1,1,0,7);
        setRegionStyle(sheet,cellRangeSubTitleLeft,cellStyleSubTitleLeft);
        sheet.addMergedRegion(cellRangeSubTitleLeft);
        createCellAlignRight(workbook,subTitle,8,"资金：万   元\n面积：平方米",workbook.createCellStyle());

         //设置列宽
        sheet.setColumnWidth(0, 256*6+184);
        sheet.setColumnWidth(1, 256*20+184);
        sheet.setColumnWidth(2, 256*18+184);
        sheet.setColumnWidth(3, 256*13+184);
        sheet.setColumnWidth(4, 256*12+184);
        sheet.setColumnWidth(5, 256*10+184);
        sheet.setColumnWidth(6, 256*20+184);
        sheet.setColumnWidth(7, 256*10+184);
        sheet.setColumnWidth(8, 256*12+184);
        //end#子标题

        //begin表格头
        createCellAlignCenter(workbook,row_head,0,"序号",cellStyleO);
        createCellAlignCenter(workbook,row_head,1,"项目名称",cellStyleO);
        createCellAlignCenter(workbook,row_head,2,"建设单位",cellStyleO);
        createCellAlignCenter(workbook,row_head,3,"项目阶段",cellStyleO);
        createCellAlignCenter(workbook,row_head,4,"行业类型",cellStyleO);
        createCellAlignCenter(workbook,row_head,5,"总投资",cellStyleO);
        createCellAlignCenter(workbook,row_head,6,"项目主要建设内容",cellStyleO);
        createCellAlignCenter(workbook,row_head,7,"已安排资金",cellStyleO);
        createCellAlignCenter(workbook,row_head,8,"已拨付资金",cellStyleO);
        //end#表格头

        //begin#数据列
        int rowNum=3;//数据加载开始行
        int index=1;

        for (ProjectStatisticsBean obj:data) {
            Row row = sheet.createRow(rowNum);
            //创建数据
            createCellAlignCenter(workbook,row,0, index,cellStyleO);//序号
            createCellAlignCenter(workbook,row,1, obj.getProjectName(),cellStyleO);//项目名称
            createCellAlignCenter(workbook,row,2, obj.getUnitName(),cellStyleO);//建设单位
            createCellAlignCenter(workbook,row,3, obj.getProjectStageDesc(),cellStyleO);//项目阶段
            createCellAlignCenter(workbook,row,4, obj.getProjectIndustryDesc(),cellStyleO);//行业类型
            createCellAlignCenter(workbook,row,5, obj.getProjectInvestSum(),cellStyleO);//总投资
            createCellAlignCenter(workbook,row,6, obj.getProjectGuiMo(),cellStyleO);//项目主要建设内容
            createCellAlignCenter(workbook,row,7, obj.getProjectInvestAccuSum(),cellStyleO);//已安排资金
            createCellAlignCenter(workbook,row,8, "-",cellStyleO);//已拨付资金

            rowNum++;index++;
        }

        return workbook;
    }

    //创建值为string字体居中的单元格    
    @SuppressWarnings("deprecation")
    private void createCellAlignCenter(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为double字体居中的单元格    
    private void createCellAlignCenter(Workbook workbook,Row row, int cellNumber,double value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_CENTER,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为string字体居左的单元格
    private void createCellAlignLeft(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_LEFT,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//创建值为string字体居右的单元格    
    private void createCellAlignRight(Workbook workbook,Row row, int cellNumber,String value,CellStyle cellStyle){
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        createCell(workbook,row,cellNumber,value,CellStyle.ALIGN_RIGHT,CellStyle.VERTICAL_CENTER,cellStyle);
    }
    @SuppressWarnings("deprecation")
//重写创建列
    private void createCell(Workbook workbook,Row row, int cellNumber,String value, short halign, short valign,CellStyle cellStyle){
        Cell cell=row.createCell(cellNumber);
        cell.setCellValue(value);

        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cellStyle.setWrapText(true);
      //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }
    @SuppressWarnings("deprecation")
  //重写创建列
    private void createCell(Workbook workbook,Row row, int cellNumber,double value, short halign, short valign,CellStyle cellStyle){
        Cell cell=row.createCell(cellNumber);// 创建单元格
        cell.setCellValue(value);// 设置值

        cellStyle.setAlignment(halign);// 设置单元格水平方向对齐方式
        cellStyle.setVerticalAlignment(valign);// 设置单元格垂直方向对齐方式
        cellStyle.setWrapText(true);
        //设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cell.setCellStyle(cellStyle);
    }


    /**
     * 
     * @Title: setRegionStyle 
     * @Description: 设置单元格样式
     * @param sheet 当前表
     * @param region 合并列
     * @param cs  样式
     */
    public static void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = (HSSFRow) sheet.getRow(i);
            if (row == null)
                row = (HSSFRow) sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }
}

结果也展示一下
这里写图片描述

OK，以上结束。大佬们路过可否给个赞，有啥子话也可以在评论区泼洒泼洒！

图图学Java

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
1
评论
使用Apache POI 进行Excel报表的导出下载（GET 请求 && POST请求）

简介：之前项目有这样的需求：按各种条件进行筛选数据，然后导出Excel到本地针对条件条件很少的情况下，使用的是GET请求的方式，直接在URL上拼接请求参数，这种形式很简单就能导出Excel。但是当条件很多很复杂时，就不适合使用GET请求的方式了，所以我使用了POST请求的方式，在这其中遇到了各种问题，估计大家也可能遇见到了。遇到问题：请求数据传递正确，数据获取正确，Excel构建也没...
复制链接

扫一扫