Java导出Excel到本地

import com.bc.base.element.Page;
import com.bc.digitalmanage.entity.common.ResultMsg;
import com.bc.digitalmanage.entity.common.enums.ErrorEnum;
import com.bc.tour.dao.tourPC.PaymentManagementDao;
import com.bc.tour.entity.TourUser;
import com.bc.tour.entity.dto.PaymentDTO;
import com.bc.tour.entity.dto.TransactionDetail;
import com.bc.tour.entity.enums.AdminEnum;
import com.bc.tour.entity.vo.AccountCheckingVO;
import com.bc.tour.service.tourPC.PaymentManagementService;
import com.bc.tour.util.ExportExcel;
import com.sun.xml.internal.messaging.saaj.packaging.mime.internet.MimeUtility;
import lombok.extern.slf4j.Slf4j;
import org.apache.catalina.servlet4preview.http.HttpServletRequest;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.functions.Columns;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;    


    @Override
    public ResultMsg<Object> downLoadAreaHoseFile(HttpServletRequest request, HttpServletResponse response, AccountCheckingVO accountCheckingVO) {
        List<PaymentDTO> paymentDTOList = new ArrayList<>();
        BigDecimal pirceSum = new BigDecimal(0);
        int count = 0;
        //查询当前登录的是什么级别的管理员
        if (StringUtils.isNotBlank(accountCheckingVO.getUserId())) {
            TourUser tourUser = paymentManagementDao.queryUserSole(accountCheckingVO.getUserId());
            if (tourUser == null)return ResultMsg.failResult(ErrorEnum.USER_IS_NULL_ERROR);
            Map<String,Object> objectMap = new HashMap<>();
            //权限为主管理员
            if (tourUser.getIsAdmin().equals(AdminEnum.systemAdmin.getValue())){
                paymentDTOList = paymentManagementDao.accountCheckingList(null,accountCheckingVO,tourUser);
                //收入总额
                pirceSum = paymentDTOList.stream().map(PaymentDTO::getPrice).reduce(BigDecimal.ZERO,BigDecimal::add);
                //订单总笔数
                count = paymentManagementDao.count(accountCheckingVO,tourUser);
            }
            //权限为普通管理员
            if (tourUser.getIsAdmin().equals(AdminEnum.ordinaryAdmin.getValue())){
                paymentDTOList = paymentManagementDao.accountCheckingList(null,accountCheckingVO,tourUser);
                //管理员对应景区收入总额
                pirceSum = paymentDTOList.stream().map(PaymentDTO::getPrice).reduce(BigDecimal.ZERO,BigDecimal::add);
                //管理员对应景区订单总笔数
                count = paymentManagementDao.count(accountCheckingVO,tourUser);
            }
        }
        try {
            // 申明一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 创建sheet页
            HSSFSheet sheet = workbook.createSheet( "账单统计");
            // 设置表格的默认列宽为18
            sheet.setDefaultColumnWidth(14);
//            sheet.setColumnWidth(0,0);

            // 设置第一行提示样式
            HSSFCellStyle styleTip = workbook.createCellStyle();
            styleTip.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            styleTip.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleTip.setBorderBottom(BorderStyle.THIN);
            styleTip.setBorderLeft(BorderStyle.THIN);
            styleTip.setBorderRight(BorderStyle.THIN);
            styleTip.setBorderTop(BorderStyle.THIN);
            styleTip.setAlignment(HorizontalAlignment.CENTER);
            styleTip.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont fontTip = workbook.createFont();
            fontTip.setFontHeightInPoints((short)22);
            fontTip.setFontName("宋体");
            fontTip.setColor(IndexedColors.BLACK.getIndex());
            styleTip.setFont(fontTip);

            // 设置表头样式
            HSSFCellStyle styleTop = workbook.createCellStyle();
            styleTop.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            styleTop.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleTop.setBorderBottom(BorderStyle.THIN);
            styleTop.setBorderLeft(BorderStyle.THIN);
            styleTop.setBorderRight(BorderStyle.THIN);
            styleTop.setBorderTop(BorderStyle.THIN);
            styleTop.setAlignment(HorizontalAlignment.CENTER);
            styleTop.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont fontTop = workbook.createFont();
            fontTop.setFontHeightInPoints((short)12);
            fontTop.setFontName("黑体");
            fontTop.setColor(IndexedColors.BLACK.getIndex());
            styleTop.setFont(fontTop);

            // 设置正文样式
            HSSFCellStyle styleBody = workbook.createCellStyle();
            styleBody.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            styleBody.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styleBody.setBorderBottom(BorderStyle.THIN);
            styleBody.setBorderLeft(BorderStyle.THIN);
            styleBody.setBorderRight(BorderStyle.THIN);
            styleBody.setBorderTop(BorderStyle.THIN);
            styleBody.setAlignment(HorizontalAlignment.CENTER);
            styleBody.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont fontBody = workbook.createFont();
            fontBody.setFontHeightInPoints((short)11);
            fontBody.setFontName("宋体");
            styleBody.setFont(fontBody);
            //营销精确到项目、客服精确到分期
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 7);
            CellRangeAddress region1 = new CellRangeAddress(1, 1, 0, 3);
            CellRangeAddress region2 = new CellRangeAddress(1, 1, 4, 7);
            sheet.addMergedRegion(region);
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);

            //创建前三行
            // 把字体应用到当前的样式
            HSSFRow row0= sheet.createRow(0);//第一行
            HSSFRow row1 = sheet.createRow(1);//第二行
            HSSFRow row2 = sheet.createRow(2);//第三行

            // 第一行提示信息
            short rowHeight = 800;
            row0.setHeight(rowHeight);
            HSSFCell topCell0 = row0.createCell(0);
            topCell0.setCellStyle(styleTip);
            topCell0.setCellValue("账单统计");

            HSSFCell topCell06 = row1.createCell(0);
            topCell06.setCellStyle(styleTop);
            topCell06.setCellValue("总收入(元)："+pirceSum.toString());
            HSSFCell topCell07 = row1.createCell(3);
            topCell07.setCellStyle(styleTop);
            topCell07.setCellValue("笔数："+count);


            //第三行
            String[] top = {"序号","日期", "商户名称","所在点位","收入(元)","账户余额(元)","商品名称","商品系列"};
            for(int i = 0 ; i<=7 ; i++){
                HSSFCell topCell_2 = row2.createCell(i);
                topCell_2.setCellStyle(styleTop);
                if(i>=0){
                    topCell_2.setCellValue(top[i-0]);
                }
            }
            if(!CollectionUtils.isEmpty(paymentDTOList)){
                for (int i = 1; i <= paymentDTOList.size(); i++) {
                    PaymentDTO item = paymentDTOList.get(i - 1);
                    HSSFCellStyle style = styleBody;
                    HSSFRow row = sheet.createRow(i + 2);

                    HSSFCell cell6 = row.createCell(0);
                    cell6.setCellStyle(style);
                    cell6.setCellValue(String.valueOf(i));
                    HSSFCell cell7 = row.createCell(1);
                    cell7.setCellStyle(style);
                    if(item.getCreateTime() != null){
                        cell7.setCellValue(String.valueOf(item.getCreateTime()));
                    }
                    HSSFCell cell8 = row.createCell(2);
                    cell8.setCellStyle(style);
                    if(item.getMerchantName() != null){
                        cell8.setCellValue(String.valueOf(item.getMerchantName()));
                    }
                    HSSFCell cell9 = row.createCell(3);
                    cell9.setCellStyle(style);
                    if(item.getScenicAreaName() != null){
                        cell9.setCellValue(String.valueOf(item.getScenicAreaName()));
                    }
                    HSSFCell cell10 = row.createCell(4);
                    cell10.setCellStyle(style);
                    if(item.getPrice() != null){
                        cell10.setCellValue(String.valueOf(item.getPrice()));
                    }
                    HSSFCell cell11 = row.createCell(5);
                    cell11.setCellStyle(style);
                    if(item.getAccountPrice() != null){
                        cell11.setCellValue(String.valueOf(item.getAccountPrice()));
                    }
                    HSSFCell cell12 = row.createCell(5);
                    cell12.setCellStyle(style);
                    if(item.getCommodityName() != null){
                        cell12.setCellValue(String.valueOf(item.getCommodityName()));
                    }
                    HSSFCell cell13 = row.createCell(5);
                    cell13.setCellStyle(style);
                    if(item.getCommodityCategory() != null){
                        cell13.setCellValue(String.valueOf(item.getCommodityCategory()));
                    }
                }
            }else{
                return ResultMsg.failResult(ErrorEnum.NODATA_ERROR);
            }
            //导出文档，不用修改
            String fileName = "账单统计.xls";
            fileName = MimeUtility.encodeText(URLEncoder.encode(fileName, "UTF-8"), "UTF-8", "B");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            OutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
            return ResultMsg.successfulResult("导出成功");
        } catch (IOException e) {
            e.printStackTrace();
            return ResultMsg.failResult(ErrorEnum.SYSTEM_ERROR);
        }
    }