java使用poi导出excel到浏览器，apache.poi导出，HSSFWorkbook导出excel，XSSFWorkbook导出excel

java使用poi导出excel，apache.poi导出，HSSFWorkbook导出excel，XSSFWorkbook导出excel

导入依赖

使用apache.poi依赖，可以自行调整版本。

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

前端用这种调用

location.href =  url

后台导出excel的java主要代码

package com.chatopera.cc.app.agentservice.controller;

import com.chatopera.cc.app.handler.Handler;
import com.chatopera.cc.app.vo.AgentServerTjVO;
import com.chatopera.cc.util.Constants;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;

@Controller
@RequestMapping("/agentService")
public class AgentServiceController extends Handler {

    private static Logger logger = LoggerFactory.getLogger(AgentServiceController.class);

//HSSFWorkbook导出excel
    private void agentServerTjExportExcel(List<AgentServerTjVO> entityList, HttpServletResponse response) {
        String tempFileName = null;
        try {
            tempFileName = URLEncoder.encode("业务量统计" + System.currentTimeMillis() + ".xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            logger.error("文件名格式化失败:{}", e.getMessage());
        }
        response.reset();
        //设置响应头，
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;fileName=" + tempFileName);
        response.setContentType("application/msexcel");

        //代写入的文件流
        try {
            //创建HSSFWorkbook对象
            HSSFWorkbook wb = new HSSFWorkbook();
            //建立sheet对象
            HSSFSheet sheet = wb.createSheet("业务量统计");

            //在sheet里创建第一行，参数为行索引
            HSSFRow row0 = sheet.createRow(0);
            //创建单元格
            HSSFCell cell0 = row0.createCell(0);
            //设置标题栏字体
            HSSFFont titleFont = wb.createFont();
            titleFont.setBold(true);
            titleFont.setFontHeight((short) 380);
            //设置标题栏样式
            HSSFCellStyle titleCellStyle = wb.createCellStyle();
            titleCellStyle.setFont(titleFont);
            titleCellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); //水平居中对齐
            titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
            titleCellStyle.setLocked(true); //锁定单元格
            cell0.setCellStyle(titleCellStyle);
            //设置单元格内容
            cell0.setCellValue("业务量统计");
            //合并单元格 CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

            String[] headers = {"组织机构", "服务类型", "服务坐席", "线上窗口办税业务量", "共享桌面教办业务量", "办税大厅业务量", "政策咨询业务量"};
            //设置列宽
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 6000);
            sheet.setColumnWidth(4, 6000);
            sheet.setColumnWidth(5, 5000);
            sheet.setColumnWidth(6, 5000);

            // 表头
            HSSFRow row = sheet.createRow(1);
            HSSFCellStyle cellStyle = wb.createCellStyle();
            // 单元格字体
            HSSFFont font = wb.createFont();
            font.setBold(true);
            font.setFontHeight((short) 230);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); //水平居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
            cellStyle.setLocked(true); //锁定单元格
            cellStyle.setWrapText(true); // 是否换行
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell1 = row.createCell(i);
                cell1.setCellStyle(cellStyle);
                cell1.setCellValue(headers[i]);
            }

            for (int i = 0; i < entityList.size(); i++) {
                //在sheet里创建第二行
                AgentServerTjVO entity = entityList.get(i);
                HSSFRow contentRow = sheet.createRow(i + 2);
                HSSFCell cell = contentRow.createCell(0);
                cell.setCellValue(entity.getOrganName());

                cell = contentRow.createCell(1);
                 cell.setCellValue(Constants.CHATBOT_MESSAGE_ZCZX);

                cell = contentRow.createCell(2);
                cell.setCellValue(entity.getAgentusername());

                cell = contentRow.createCell(3);
                cell.setCellValue(entity.getBsdtCkbsNums());

                cell = contentRow.createCell(4);
                cell.setCellValue(entity.getBsdtYwzxNums());

                cell = contentRow.createCell(5);
                cell.setCellValue(entity.getBsdtNums());

                cell = contentRow.createCell(6);
                cell.setCellValue(entity.getZczxNums());
            }

            //输出Excel文件
            OutputStream output = response.getOutputStream();
            wb.write(output);
            output.close();

        } catch (Exception e) {
            logger.error("导出失败:{}", e.getMessage());
        }
    }

//XSSFWorkbook导出excel
    private void agentServerTjExportMethod(List<AgentServerTjVO> entityList, HttpServletResponse response) throws Exception {
        String fileName = new String("业务量统计".getBytes("utf-8"), "ISO-8859-1");
        response.reset();
        response.setHeader("Content-Disposition", "attachment;fileName=" + fileName + ".xlsx");
        response.setContentType("application/octet-stream");
        try (OutputStream os = response.getOutputStream();
             BufferedOutputStream bos = new BufferedOutputStream(os)) {

            String[] headers = {"组织机构", "服务类型", "服务坐席", "线上窗口办税业务量", "共享桌面教办业务量", "办税大厅业务量", "政策咨询业务量"};

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("业务量统计");

            //设置列宽
            sheet.setColumnWidth(0, 4000);
            sheet.setColumnWidth(1, 4000);
            sheet.setColumnWidth(2, 5000);
            sheet.setColumnWidth(3, 6000);
            sheet.setColumnWidth(4, 6000);
            sheet.setColumnWidth(5, 5000);
            sheet.setColumnWidth(6, 5000);

            // 表头
            XSSFRow row = sheet.createRow(0);
            XSSFCellStyle cellStyle = wb.createCellStyle();
            // 单元格字体
            XSSFFont font = wb.createFont();
            font.setBold(true);
            font.setFontHeight((short) 230);
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION); //水平居中对齐
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中
            cellStyle.setLocked(true); //锁定单元格
            for (int i = 0; i < headers.length; i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(headers[i]);
            }

            for (int i = 0; i < entityList.size(); i++) {
                AgentServerTjVO entity = entityList.get(i);

                XSSFRow contentRow = sheet.createRow(i + 1);
                XSSFCell cell = contentRow.createCell(0);
                cell.setCellValue(entity.getOrganName());

                cell = contentRow.createCell(1);
                switch (entity.getServerType()) {
                    case Constants.SERVER_TYPE_ZCZX:
                        cell.setCellValue(Constants.CHATBOT_MESSAGE_ZCZX);
                        break;
                    case Constants.SERVER_TYPE_BSDT:
                        cell.setCellValue(Constants.CHATBOT_MESSAGE_BSDT);
                        break;
                    case Constants.SERVER_TYPE_BSDT_CKBS:
                        cell.setCellValue(Constants.CHATBOT_MESSAGE_BSDT_CKBS);
                        break;
                    case Constants.SERVER_TYPE_BSDT_YWZX:
                        cell.setCellValue(Constants.CHATBOT_MESSAGE_BSDT_YWZX);
                        break;
                    default:
                        cell.setCellValue("**");
                }

                cell = contentRow.createCell(2);
                cell.setCellValue(entity.getAgentusername());

                cell = contentRow.createCell(3);
                cell.setCellValue(entity.getBsdtCkbsNums());

                cell = contentRow.createCell(4);
                cell.setCellValue(entity.getBsdtYwzxNums());

                cell = contentRow.createCell(5);
                cell.setCellValue(entity.getBsdtNums());

                cell = contentRow.createCell(6);
                cell.setCellValue(entity.getZczxNums());
            }

            wb.write(bos);
            bos.flush();
            bos.close();
            wb.close();
        }
    }
}

各位大佬，有不对的地方欢迎交流指正。