java数据导出到Excel

最新推荐文章于 2024-12-27 17:45:46 发布

Bolon0708

最新推荐文章于 2024-12-27 17:45:46 发布

阅读量6.9k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Java

本文链接：https://blog.youkuaiyun.com/l18848956739/article/details/82702962

Java 专栏收录该内容

277 篇文章

订阅专栏

本文介绍如何使用Apache POI库进行Excel数据导出，包括在Maven项目中引入依赖、创建Excel工作簿、设置表头和数据行，以及通过HTTP响应将Excel文件下载到客户端的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

excel处理函数需要引入poi的jar包,在pom.xml引入一下代码
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency><!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Auther: 
 * @Date: 2018/9/13 15:45
 * @Description:
 */
@RestController
@RequestMapping("/file")
public class FileController {
    private Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 数据导出
     */
    @RequestMapping("/downloadEventList")
    public void exportExceptionRecord(HttpServletRequest request, HttpServletResponse response) {
        BhEventServiceImpl eventService = (BhEventServiceImpl) SpringManager.getBean("bhEventServiceImpl");
        try {
            BhEventMessage eventMessage = new BhEventMessage();
            // if (SSHValidateHelper.isNotEmptyString(request.getParameter("userNo"))) {
            //     eventMessage.setUserNo(Long.valueOf(request.getParameter("userNo")));
            // }
            // if (SSHValidateHelper.isNotEmptyString(request.getParameter("typeId"))) {
            //     eventMessage.setTypeId(Long.valueOf(request.getParameter("typeId")));
            // }
            // if (SSHValidateHelper.isNotEmptyString(request.getParameter("userName"))) {
            //     eventMessage.setUserName(request.getParameter("userName"));
            // }
            // if (SSHValidateHelper.isNotEmptyString(request.getParameter("certNo"))) {
            //     eventMessage.setCertNo(request.getParameter("certNo"));
            // }
            // if (SSHValidateHelper.isNotEmptyString(request.getParameter("telephone"))) {
            //     eventMessage.setTelephone(request.getParameter("telephone"));
            // }
            JSONObject object = JsonUtil.getJSONParam(request);
            if (!SSHValidateHelper.isEmpty(object)) {
                if (!SSHValidateHelper.isEmpty(object.get("userNo"))) {
                    eventMessage.setUserNo(Long.valueOf(object.get("userNo").toString()));
                }
                if (!SSHValidateHelper.isEmpty(object.get("userName"))) {
                    eventMessage.setUserName(object.get("userName").toString());
                }
                if (!SSHValidateHelper.isEmpty(object.get("certNo"))) {
                    eventMessage.setCertNo(object.get("certNo").toString());
                }
                if (!SSHValidateHelper.isEmpty(object.get("telephone"))) {
                    eventMessage.setTelephone(object.get("telephone").toString());
                }
                if (!SSHValidateHelper.isEmpty(object.get("takeTimes"))) {
                    List<Date> datelist = new ArrayList();
                    List<String> List = (List) object.get("takeTimes");
                    for (String string : List) {
                        // String转日期
                        datelist.add(SSHDateUtils.string2Date(string, SSHDateFormatUtils.DATE_FORMAT1));
                    }
                    eventMessage.setTakeTimes(datelist);
                }
                if (!SSHValidateHelper.isEmpty(object.get("typeId"))) {
                    eventMessage.setTypeId(Long.valueOf(object.get("typeId").toString()));
                }
            }
            List<BhEvent> eventlist = eventService.getEventlist(eventMessage);
            //获得数据库中数据
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            HSSFSheet sheet = workbook.createSheet("表");
            //设置要导出的文件的名字
            String fileName = SSHDateFormatUtils.formatDate(new Date()) + ".xls";
            //headers表示excel表中第一行的表头
            String[] headers = {"序号", "姓名", "身份证号码", "手机号", "时间", "类型", "内容"};
            HSSFRow row = sheet.createRow(0);
            //在excel表中添加表头
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
                sheet.autoSizeColumn((short) i); //调整每一列宽度
            }
            int rowNum = 1;
            int num = 1;
            //在表中存放查询到的数据放入对应的列
            for (BhEvent event : eventlist) {
                HSSFRow row1 = sheet.createRow(rowNum);
                row1.createCell(0).setCellValue(num);
                if (SSHValidateHelper.isNotEmptyString(event.getUserName())) {
                    row1.createCell(1).setCellValue(event.getUserName());
                }
                if (SSHValidateHelper.isNotEmptyString(event.getCertNo())) {
                    row1.createCell(2).setCellValue(event.getCertNo());
                }
                if (SSHValidateHelper.isNotEmptyString(event.getTelephone())) {
                    row1.createCell(3).setCellValue(event.getTelephone());
                }
                if (!SSHValidateHelper.isEmpty(event.getTakeTime())) {
                    String formatDate = SSHDateFormatUtils.formatDate(event.getTakeTime());
                    row1.createCell(4).setCellValue(formatDate);
                }
                if (!SSHValidateHelper.isEmpty(event.getTypeName())) {
                    row1.createCell(5).setCellValue(event.getTypeName());
                }
                if (!SSHValidateHelper.isEmpty(event.getDescr())) {
                    row1.createCell(6).setCellValue(event.getDescr());
                }
                rowNum++;
                num++;
            }
            for (int i = 0; i < headers.length; i++) {
                // 调整每一列宽度
                sheet.autoSizeColumn((short) i);
                // 解决自动设置列宽中文失效的问题
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            logger.error("事件导出出现异常");
        }
    }
}