springboot 导出xlsx文件
阅读原文时间:2023年07月09日阅读:3

@Override
public String downModelXlsFile(SxSourceReq param, HttpServletResponse response) {
      //设置响应编码
response.setCharacterEncoding("GBK");
response.setContentType("multipart/form-data");
ImModSourceVo imModSourceVo = findDetail(param);
//ImModSourcePojo imModSourcePojo = imModSourcePojoMapper.get(param.getModSourceId());
if (imModSourceVo == null) {
return ReturnJsonFormatUntil.json(false, "该模板不存在!!!");
}
boolean isCustom = "2".equals(imModSourceVo.getModType());
//List modSourceSheetPojos = imModSourceSheetPojoMapper.findList(param);
if ((imModSourceVo.getList() == null || imModSourceVo.getList().size() == 0) && !isCustom) {
return ReturnJsonFormatUntil.json(false, "该模板,没有sheet信息!!!");
}
if (!isCustom) {
List factorPojoList = imImpSourceSheetFactorPojoMapper.findListBySheetIds(param);
Map> sheetFactorListMap = factorPojoList.stream().collect(Collectors.groupingBy(ImImpSourceSheetFactorPojo::getModSourceSheetId));
List> sheetData = new ArrayList<>(imModSourceVo.getList().size());
Map sheetMap = null;
List>> dataRowList = null;
ImModSourceSheetVo sheetParam = new ImModSourceSheetVo();
List showFactorColumnPojos = getOrderImShowFactorColumnPojos(sheetParam);
for (ImModSourceSheetVo imModSourceSheetVo:imModSourceVo.getList()) {
sheetMap = new HashMap<>(3);
sheetMap.put("sheetIndex",imModSourceSheetVo.getSheetIndex());
sheetMap.put("sheetName",imModSourceSheetVo.getModSourceSheetName());
List tableUnitIndexList = imModSourceSheetVo.getList();
tableUnitIndexList.stream().sorted(Comparator.comparing(ImModTableUnitVo::getCoordinateY));
dataRowList = new ArrayList<>(tableUnitIndexList.size()+1);
List> headers = new ArrayList<>();
List sheetFactorPojos = null;
if (ObjectsUtil.isNotEmpty(imModSourceSheetVo.getFactorList())) {
sheetFactorPojos = imModSourceSheetVo.getFactorList();
} else {
ListUtils.copyList(showFactorColumnPojos,sheetFactorPojos,ImModSourceSheetFactorVo.class);
}
sheetFactorPojos.stream().sorted(Comparator.comparing(ImImpSourceSheetFactorPojo::getOrderBy));

            Map<String,Object> header = null;  
            Set<String> sortKeys = new HashSet<>();  
            for (ImImpSourceSheetFactorPojo sheetFactorPojo : sheetFactorPojos) {  
                header = new HashMap<>(1);  
                header.put("value",sheetFactorPojo.getFactorPropertyName());  
                sortKeys.add(sheetFactorPojo.getFactorName());  
                headers.add(header);  
            }  
            dataRowList.add(headers);  

/* header.put("value","序号");
headers.add(header);
header = new HashMap<>(1);
header.put("value","指标名称");
headers.add(header);
header = new HashMap<>(1);
header.put("value","单位");
headers.add(header);
header = new HashMap<>(1);
header.put("value","本期上报值");
headers.add(header);
header = new HashMap<>(1);
header.put("value","备注说明");
headers.add(header);*/
List> cellList = null;
Map cellMap = null;
if (ObjectsUtil.isNotEmpty(imModSourceSheetVo.getList())) {
for (ImModTableUnitVo imModTableUnitVo : tableUnitIndexList) {
cellList = new ArrayList<>(5);
cellMap = new HashMap<>(1);
for (String sortKey : sortKeys) {
if ("indexName".equals(sortKey)) {
cellMap.put("value",imModTableUnitVo.getValue());
} else if ("indexUnit".equals(sortKey)) {
if (ObjectsUtil.isNotEmpty(imModTableUnitVo.getIndexVo())) {
cellMap.put("value",imModTableUnitVo.getIndexVo().getIndexUnit());
} else {
cellMap.put("value","");
}
} else {
cellMap.put("value","");
}
cellList.add(cellMap);
}
/* cellMap.put("value",serial);
cellList.add(cellMap);
cellMap = new HashMap<>(1);
cellMap.put("value",imModTableUnitIndexVo.getIndexName());
cellList.add(cellMap);
cellMap = new HashMap<>(1);
cellMap.put("value",imModTableUnitIndexVo.getIndexUnit());
cellList.add(cellMap);
cellMap = new HashMap<>(1);
cellMap.put("value","");
cellList.add(cellMap);
cellMap = new HashMap<>(1);
cellMap.put("value",imModTableUnitIndexVo.getDescription());
cellList.add(cellMap);*/
//serial++;
dataRowList.add(cellList);
}
}
sheetMap.put("data",dataRowList);
sheetData.add(sheetMap);
}
String today = DateUtil.dateToStr(new Date());
String dirPath = IReportUtils.saveXlsFileToLocal(today);

        //输出文件到本地  
        String defaultFileName =  DateUtil.getStringAllDate() + ".xls";  
        File file = new File(dirPath + "/" + defaultFileName);  
        String path = dirPath + File.separator + defaultFileName;  
        response.setHeader("Content-Disposition", "attachment;fileName=" + defaultFileName);  
        ByteArrayOutputStream byteArrayOutputStream = null;  
        try {  
            OutputStream os = response.getOutputStream();  
            log.info("导出开始!!!");  
            //outputStream = new BufferedOutputStream(new FileOutputStream(file));  
            byteArrayOutputStream = new ByteArrayOutputStream();  
            //ExcelUtil.write2OutputStream(os,sheetData);  

          //写文件
ExcelUtil.write2OutputStream(byteArrayOutputStream,sheetData);
response.setContentLength(byteArrayOutputStream.size());
ServletOutputStream outputStream = response.getOutputStream();
byteArrayOutputStream.writeTo(outputStream);
byteArrayOutputStream.close();
outputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (byteArrayOutputStream != null) {
try {
byteArrayOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
log.info("下载文件成功");
String results = ipAddress + path;
log.info(results);
return ReturnJsonFormatUntil.json(true, results);
} else {
if (ObjectsUtil.isEmpty(imModSourceVo.getFileId())) {
return ReturnJsonFormatUntil.json(false, "没有该模板文件!!");
}
ImFilePojo pojo = imFilePojoMapper.get(imModSourceVo.getFileId());
String filePath = "";
if (ObjectUtils.isEmpty(pojo)) {
log.error("查询的文件为空,不下载");
return ReturnJsonFormatUntil.json(false, "该文件id错误,无该文件!!!");
}
log.info("开始下载文件到本地");
String fileName = ObjectUtils.isEmpty(pojo.getFileName()) ? UuidUtils.uuid() : pojo.getFileName();
String path = System.getProperty("user.dir");
try {
if(!FtpUtil.downloadFileJlf(1,response,
ftpConstants.getFTP_HOST_INDEX(),
ftpConstants.getFTP_PORT_INDEX(),
ftpConstants.getFTP_USER_INDEX(),
ftpConstants.getFTP_PASSWORD_INDEX(),
pojo.getFilePath(),
pojo.getFileName(),
"",
pojo.getFileName()
)
){
return ReturnJsonFormatUntil.json(false, ResultConstants.DOWNLOAD_FALURE);
}
//下载路径
//path = today + "_report/" + defaultFileName;
log.info("导出成功!!!");
} catch (Exception e) {
log.error("下载失败+++++++++++++++" + fileName);
log.error(e.getMessage());
e.printStackTrace();
return filePath;
}
log.info("下载文件成功");
String results = ipAddress + path;
log.info(results);
return ReturnJsonFormatUntil.json(true, results);
}
}

首先开始设置响应格式

response.setCharacterEncoding("GBK");
response.setContentType("multipart/form-data");

然后设置响应文件名称

String today = DateUtil.dateToStr(new Date());
String dirPath = IReportUtils.saveXlsFileToLocal(today);

        //输出文件到本地  
        String defaultFileName =  DateUtil.getStringAllDate() + ".xls";  
        File file = new File(dirPath + "/" + defaultFileName);  
        String path = dirPath + File.separator + defaultFileName;  
        response.setHeader("Content-Disposition", "attachment;fileName=" + defaultFileName);

导出文件到本地,并且把输出流写入给response

ByteArrayOutputStream byteArrayOutputStream = null;
try {
OutputStream os = response.getOutputStream();
log.info("导出开始!!!");
//outputStream = new BufferedOutputStream(new FileOutputStream(file));
byteArrayOutputStream = new ByteArrayOutputStream();
//ExcelUtil.write2OutputStream(os,sheetData);
ExcelUtil.write2OutputStream(byteArrayOutputStream,sheetData);
response.setContentLength(byteArrayOutputStream.size());
ServletOutputStream outputStream = response.getOutputStream();
byteArrayOutputStream.writeTo(outputStream);
byteArrayOutputStream.close();
outputStream.flush();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (byteArrayOutputStream != null) {
try {
byteArrayOutputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
log.info("下载文件成功");
String results = ipAddress + path;
log.info(results);

return ReturnJsonFormatUntil.json(true, results);

ftp下载xlsx文件代码为

public static boolean downloadFileJlf(int fileType, HttpServletResponse response, String host, int port, String username, String password, String remotePath,
String fileName, String localPath,String saveAsFileName) {

    int count=0;

    FTPClient ftpClient = new FTPClient();  
    ftpClient.setControlEncoding("GBK");  
    response.setContentType("multipart/form-data");  
    log.info(host + ":" + port + ":" + username + ":" + ":" + password + ":" + remotePath + ":" + fileName + ":" + localPath + ":" + saveAsFileName +":" + fileType);  

//
// FTPClientConfig conf = new FTPClientConfig(FTPClientConfig.SYST_UNIX);
// conf.setServerLanguageCode("zh");
// ftpClient.configure(conf);
String fileLastName="";

    try {  
        int reply;  
        ftpClient.connect(host, port);  
        ftpClient.login(username, password);  
        reply = ftpClient.getReplyCode();  
        if (!FTPReply.isPositiveCompletion(reply)) {  
            ftpClient.disconnect();  
            return false;  
        }  
       /\* ftpClient.changeWorkingDirectory(remotePath);//转移到FTP服务器目录  
        FTPFile\[\] fs = ftpClient.listFiles();  
        for (int i = 0; i < fs.length; i++) {  
            if (fs\[i\].getName().trim().equals(fileName)) {  
                if (fileType==1){  
                    fileLastName=getFileNameNoEx(1,fs\[i\].getName());  
                }  
                saveAsFileName = new String(saveAsFileName.getBytes("GB2312"), "ISO-8859-1");  
                response.setHeader("Content-Disposition", "attachment;fileName=" + saveAsFileName);  
                OutputStream os = response.getOutputStream();  
                ftpClient.enterLocalPassiveMode();  
                ftpClient.setFileType(FTPClient.BINARY\_FILE\_TYPE);  
                ftpClient.retrieveFile(fs\[i\].getName(), os);  
                os.flush();  
                os.close();  
                count++;  
                break;  
            }  
        }  

*/
// FTPFile[] files = ftpClient.listFiles(remotePath);
saveAsFileName = new String(saveAsFileName.getBytes("GBK"), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;fileName=" + saveAsFileName);

        OutputStream os = response.getOutputStream();  
        ftpClient.enterLocalPassiveMode();  
        ftpClient.setFileType(FTPClient.BINARY\_FILE\_TYPE);  
        ftpClient.retrieveFile(remotePath+"/"+fileName, os);  
        os.flush();  
        os.close();  
        ftpClient.logout();  
    } catch (IOException e) {  
        e.printStackTrace();  
    } finally {  
        if (ftpClient.isConnected()) {  
            try {  
                ftpClient.disconnect();  
            } catch (IOException ioe) {  
                ioe.printStackTrace();  
                return false;  
            }  
        }  
    }

    return true;  
}

由于xlsx文件比较特殊,如果还是下载失败,最后一种笨方法就是把文件下载到服务器本地,每天把文件放入指定文件夹,返回一个可以访问文件的路径,让用户从本地直接访问路径直接下载文件,第二天如果还有人下载文件,创建新文件夹,删除

昨天的旧文件夹即可,该方法只适用于下载文件比较少的情况,如果文件下载量太大,还是老老实实研究一下怎么下载xlsx文件吧!!!

@font-face { font-family: "yourDictFontAwesome"; src: url("chrome-extension://nnijdpomjcmgahgjcdgimjldkopghaef/lib/fontawesome-webfont.ttf") format("truetype"); font-weight: normal; font-style: normal }
@font-face { font-family: "yourDictFontAwesome"; src: url("chrome-extension://nnijdpomjcmgahgjcdgimjldkopghaef/lib/fontawesome-webfont.ttf") format("truetype"); font-weight: normal; font-style: normal }