POI后端导出excel

郝少

已于 2023-01-11 14:07:14 修改

阅读量447

点赞数

分类专栏： # javaWeb SpringBoot技术应用 Java及框架文章标签： poi导出excel excel导出

于 2019-11-29 19:42:45 首次发布

本文链接：https://blog.csdn.net/hyj_king/article/details/103316897

版权

Java及框架同时被 3 个专栏收录

49 篇文章 0 订阅

订阅专栏

SpringBoot技术应用

20 篇文章 0 订阅

订阅专栏

javaWeb

12 篇文章 0 订阅

订阅专栏

1、导入pom依赖

<!--核心jar包-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.17</version>
</dependency>
<!--支持xlsx读取-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.17</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
   <version>3.17</version>
</dependency>

2、html格式

(1)格式1

<a href="#" onclick="javascript:exprot_excel()" class="btn btn-primary radius">导出</a>
 <form id = "export_data" action="/Path/exportData" method="post" ></form>
 
function exprot_excel (type){
       $('#export_data').html('')
        var startTime=$("#startTime").val()
        var endTime=$("#endTime").val()
        //获取选中的select值
        var selectValue=$("#selectId").find("option:selected").text()
        var exportForm = $('#export_data');
        var input1=$('<input>');
        input1.attr('type','hidden')
        input1.attr('name','startTime')
        input1.attr('value',startTime)
        exportForm.append(input1);

        var input2=$('<input>');
        input2.attr('type','hidden')
        input2.attr('name','endTime')
        input2.attr('value',endTime)
        exportForm.append(input2);

        var input3=$('<input>');
        input3.attr('type','hidden')
        input3.attr('name','userName')
        input3.attr('value',selectValue)
        exportForm.append(input3);
        exportForm.submit()
}

(2)格式2

<button id="exportButton" type="button" class="btn btn-w-m btn-primary">数据导出</button>


<script type="text/javascript">
$(function(){
    //根据时间范围下载每日标记数据
    $("#exportButton").click(function(){
    var startTime=$("#startTime").val()
    var endTime=$("#endTime").val()
    //获取选中的select值
    var selectValue=$("#selectId").find("option:selected").text()
    //跳转页面
    window.location.href="http://127.0.0.1:8094/Path/exportData?startTime="+startTime+"&endTime="+endTime+"&userName="+selectValue;
    })
})
</script>

3、后端逻辑

(1)controller

    @RequestMapping("/Path/exportData")
    @ResponseBody
    public String exportData(String date,HttpServletResponse response){
        if(exportService.exportDataService(date,response)){
            return "success";
        }
        return  "error";
    }

(2)service

	public boolean exportDataService(String date,HttpServletResponse response) {
        SimpleDateFormat format0 = new SimpleDateFormat("yyyyMMddHHmmss");
        String nowTime = format0.format(new Date());

        List<Entity> dataList = new ArrayList<>();

        String partition = "";
        if (date == null || date.trim().length() == 0) {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
            partition = "Z" + simpleDateFormat.format(new Date());
        } else {
            partition = "Z" + date.replaceAll("-", "");
        }

        Integer c = operaterDao.listEntityCount(partition);
        if (c > 0) {
            dataList = operaterDao.listEntity(partition);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("导出数据");

            HSSFRow row = sheet.createRow(0);
            HSSFFont font = wb.createFont();
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(font);
            font.setBold(true);

            List<String> excelHead = new ArrayList<>();
            excelHead.add("姓名");
            excelHead.add("年龄");

            HSSFCell cell;
            for (int i = 0; i < 2; i++) {
                sheet.setColumnWidth(i, 20 * 256);
                cell = row.createCell(i);
                cell.setCellStyle(style);
                cell.setCellValue(excelHead.get(i));
            }

            for (int j = 1; j < dataList.size() + 1; j++) {


                Entity entity = dataList.get(j - 1);

                row = sheet.createRow(j);

                cell = row.createCell(0);
                cell.setCellValue(entity.getName());

                cell = row.createCell(1);
                cell.setCellValue(entity.getAge());

            }
            try {
                //输出Excel文件
                OutputStream output = response.getOutputStream();
                response.reset();
                //设置响应头，
                response.setHeader("Content-disposition", "attachment; filename="+nowTime+".xls");
                response.setContentType("application/msexcel");
                wb.write(output);
                output.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

(3)entity

public class Entity {

    private String name;
    private String age;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setage(String age) {
        this.age = age;
    }
}