Home  >  Article  >  Java  >  How to download Excel template file from server in Java

How to download Excel template file from server in Java

王林
王林forward
2023-04-19 11:58:031017browse

Method 1

Generate excel template

@RequestMapping("/downloadExcel")
public void downloadExcel(HttpServletResponse response, HttpServletRequest request) {
        String [] excelHeader = {"姓名","手机号(必填)","渠道名","产品名","手机操作系统(IOS/安卓)","是否是XX数据"};
        List<Object> list = new ArrayList<>();
        Object[] obj1 = {"张三","173*****311‬","a1","A","IOS","是"};
        Object[] obj2 = {"李四","138*****742","a2","B","安卓","否"};
        list.add(obj1);
        list.add(obj2);
        FileExport.exportExcel(excelHeader, list, "XXX模板", response, request);
    }

FileExport tool class:

package com.abc.common.utils.file;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 文件导出工具
 * @author abc
 * @date 2019/01/08
 */
public class FileExport {
    
    private static final Logger logger = LoggerFactory.getLogger(FileExport.class);

    /** CSV文件列分隔符 */
    private static final String CSV_COLUMN_SEPARATOR = ",";

    private static final String CSV_COLUM_TABLE = "\t";

    /** CSV文件列分隔符 */
    private static final String CSV_RN = "\r\n";

    /**
     * 导出Excel文件
     * 
     * @param excelHeader
     *            导出文件中表格头
     * @param list
     *            导出的内容
     * @param response
     *            HttpServletResponse对象,用来获得输出流向客户端写导出的文件
     * @param sheetName
     *            Excel的sheet名称,加上时间戳作为导出文件的名称
     */
    public static void exportExcel(String [] excelHeader, List<Object> list, String sheetName, HttpServletResponse response, HttpServletRequest request) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row = sheet.createRow((int) 0);
        /******设置单元格是否显示网格线******/
        sheet.setDisplayGridlines(false);
        
        /******设置头单元格样式******/
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        Font fontHeader = wb.createFont();
        fontHeader.setBold(true);
        fontHeader.setFontHeight((short) 240);
        style.setFont(fontHeader);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        
        /******设置头内容******/
        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue("  " +excelHeader[i] + "  ");
            cell.setCellStyle(style);            
        }   
        
        /******设置内容单元格样式******/
        HSSFCellStyle styleCell = wb.createCellStyle();
        Font fontCell = wb.createFont();
        fontCell.setColor(HSSFColor.BLACK.index);
        styleCell.setAlignment(HorizontalAlignment.CENTER);
        styleCell.setFont(fontCell);
        styleCell.setBorderBottom(BorderStyle.THIN);
        styleCell.setBorderLeft(BorderStyle.THIN);
        styleCell.setBorderRight(BorderStyle.THIN);
        styleCell.setBorderTop(BorderStyle.THIN);
        /******设置单元格内容******/
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            /******设置行高******/
            row.setHeightInPoints(20);
            Object[] obj = (Object[]) list.get(i);            
            for (int j = 0; j < excelHeader.length; j++) {
                styleCell.setWrapText(false);
                HSSFCell cell = row.createCell(j);
                if (obj[j] != null){
                     cell.setCellValue(obj[j].toString());
                }else{
                    cell.setCellValue(""); 
                }            
                //if(obj[j].toString().length()>20)
                //    styleCell.setWrapText(true);
                cell.setCellStyle(styleCell);
                sheet.autoSizeColumn(j);
            }   
        } 
        
        OutputStream ouputStream = null;
        try {
            
            String encoding = "UTF-8";
            /** 获取浏览器相关的信息 */
            String userAgent = request.getHeader("user-agent");
            /** 判断是否为msie浏览器 */
            if (userAgent.toLowerCase().indexOf("msie") != -1){
                 encoding = "gbk";
            }

            response.setCharacterEncoding(encoding);
            response.setContentType("application/vnd.ms-excel"); 
            String fileName = sheetName;
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHMMSS");
            fileName += (dateFormat.format(new Date())).toString()+".xls";
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, encoding));
            ouputStream = response.getOutputStream();   
            wb.write(ouputStream);     
            ouputStream.flush();  
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(ouputStream!=null) {
                    ouputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出CSV文件
     * @param dataList 集合数据
     * @param colNames 表头部数据
     * @param mapKey 查找的对应数据
     */
    public static boolean doExport(List<Map<String, Object>> dataList, String colNames, String mapKey, OutputStream os) {
        try {
            StringBuffer buf = new StringBuffer();

            String[] colNamesArr = null;
            String[] mapKeyArr = null;

            colNamesArr = colNames.split(",");
            mapKeyArr = mapKey.split(",");

            /******完成数据csv文件的封装******/
            /******输出列头******/
            for (int i = 0; i < colNamesArr.length; i++) {
                buf.append(colNamesArr[i]).append(CSV_COLUMN_SEPARATOR);
            }
            buf.append(CSV_RN);

            if (null != dataList) {
                /******输出数据******/
                for (int i = 0; i < dataList.size(); i++) {
                    for (int j = 0; j < mapKeyArr.length; j++) {
                        buf.append(dataList.get(i).get(mapKeyArr[j])).append(CSV_COLUM_TABLE).append(CSV_COLUMN_SEPARATOR);
                    }
                    buf.append(CSV_RN);
                }
            }
            /******写出响应******/
            os.write(buf.toString().getBytes("GBK"));
            os.flush();
            return true;
        } catch (Exception e) {
            logger.error("doExport错误...", e);
        }
        return false;
    }
    
    /**
     * 设置响应格式
     * @param fileName
     * @param response
     * @throws UnsupportedEncodingException
     */
    public static void responseSetProperties(String fileName, HttpServletResponse response) throws UnsupportedEncodingException {
        /******设置文件后缀******/
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fn = fileName + sdf.format(new Date()).toString() + ".csv";
        /******读取字符编码******/
        String utf = "UTF-8";

        /******设置响应******/
        response.setContentType("application/ms-txt.numberformat:@");
        response.setCharacterEncoding(utf);
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
    }
}

Export CSV file

@GetMapping("/exportFailureRecord")
public void exportFailureRecord(String batchNumber, HttpServletResponse response) {
        if (StringUtils.isBlank(batchNumber)) {
            log.warn("失败记录导出失败,批次号为空...");
            return;
        }

        //这里根据你的业务查询出数据
        List<ImportFailureRecord> list = importFailureRecordService.selectList(new EntityWrapper<ImportFailureRecord>()
                .eq("is_delete", 0)
                .eq("batch_number", batchNumber));
        if (CollectionUtil.isEmpty(list)) {
            log.warn("未查询到可导出的数据...");
            return;
        }
        log.info("===========查询到{}条可导出数据==============", list.size());

        String sTitle = "用户姓名,手机号,失败原因";
        String fName = "xxx失败记录数据_";
        String mapKey = "userName,userPhone,failureReason";

        List<Map<String, Object>> dataList = new ArrayList<>();
        for (ImportFailureRecord data : list) {
            Map<String, Object> map = new HashMap<>();
            map.put("userName", data.getUserName() == null ? "" : data.getUserName());
            map.put("userPhone", data.getUserPhone() == null ? "" : data.getUserPhone());
            map.put("failureReason", data.getFailureReason() == null ? "" : data.getFailureReason());
            dataList.add(map);
        }
        try (final OutputStream os = response.getOutputStream()) {
            log.info("=============失败记录导出开始============");
            FileExport.responseSetProperties(fName, response);
            FileExport.doExport(dataList, sTitle, mapKey, os);
            log.info("=============失败记录导出结束============");
        } catch (Exception e) {
            log.error("导出失败记录数据失败", e);
        }
    }

Method 2

/**
 * 描述:下载外部案件导入模板
 * @param response
 * @param request
 * @author songfayuan
 * 2018年6月7日下午5:03:59
 */
    @RequestMapping("/downloadExcel")
    @ResponseBody
    public void downloadExcel(HttpServletResponse response,HttpServletRequest request) {
        //方法一:直接下载路径下的文件模板(这种方式貌似在SpringCloud和Springboot中,打包成JAR包时,无法读取到指定路径下面的文件,不知道记错没,你们可以自己尝试下!!!)
        try {
            //获取要下载的模板名称
            String fileName = "ApplicationImportTemplate.xlsx";
            //设置要下载的文件的名称
            response.setHeader("Content-disposition", "attachment;fileName=" + fileName);
            //通知客服文件的MIME类型
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            //获取文件的路径
            String filePath = getClass().getResource("/template/" + fileName).getPath();
            FileInputStream input = new FileInputStream(filePath);
            OutputStream out = response.getOutputStream();
            byte[] b = new byte[2048];
            int len;
            while ((len = input.read(b)) != -1) {
                out.write(b, 0, len);
            }
            //修正 Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容?如果信任此工作簿的来源,请点击"是"
            response.setHeader("Content-Length", String.valueOf(input.getChannel().size()));
            input.close();
            //return Response.ok("应用导入模板下载完成");
        } catch (Exception ex) {
            logger.error("getApplicationTemplate :", ex);
            //return Response.ok("应用导入模板下载失败!");
        }
        
        
        //方法二:可以采用POI导出excel,但是比较麻烦(这里类似方法一)
        /*try {
            Workbook workbook = new HSSFWorkbook();
            request.setCharacterEncoding("UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-download");
    
            
            String filedisplay = "导入模板.xls";
         
            filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename="+ filedisplay);
            
            // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet  
            Sheet sheet = workbook.createSheet("导入模板");
            // 第三步,在sheet中添加表头第0行
            Row row = sheet.createRow(0);
            // 第四步,创建单元格,并设置值表头 设置表头居中 
            CellStyle style = workbook.createCellStyle();  
            style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 
            
            Cell cell = row.createCell(0);  
            cell.setCellValue("商品名称");  
            cell.setCellStyle(style); 
            sheet.setColumnWidth(0, (25 * 256));  //设置列宽,50个字符宽
            
            cell = row.createCell(1);  
            cell.setCellValue("商品编码");  
            cell.setCellStyle(style); 
            sheet.setColumnWidth(1, (20 * 256));  //设置列宽,50个字符宽
            
            cell = row.createCell(2);  
            cell.setCellValue("商品价格");  
            cell.setCellStyle(style);  
            sheet.setColumnWidth(2, (15 * 256));  //设置列宽,50个字符宽
            
            cell = row.createCell(3);  
            cell.setCellValue("商品规格");  
            cell.setCellStyle(style);  
            sheet.setColumnWidth(3, (15 * 256));  //设置列宽,50个字符宽
            
            // 第五步,写入实体数据 实际应用中这些数据从数据库得到
            row = sheet.createRow(1);
            row.createCell(0, Cell.CELL_TYPE_STRING).setCellValue(1);  
            row.createCell(1, Cell.CELL_TYPE_STRING).setCellValue(2); 
            row.createCell(2, Cell.CELL_TYPE_STRING).setCellValue(3);   //商品价格
            row.createCell(3, Cell.CELL_TYPE_STRING).setCellValue(4);  //规格
        
            // 第六步,将文件存到指定位置  
            try  
            {  
                OutputStream out = response.getOutputStream();
                workbook.write(out);
                out.close();  
            }  
            catch (Exception e)  
            {  
                e.printStackTrace();  
            }  
        } catch (Exception e) {
            e.printStackTrace();
        }*/
    }

Template location:

How to download Excel template file from server in Java

The above is the detailed content of How to download Excel template file from server in Java. For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:yisu.com. If there is any infringement, please contact admin@php.cn delete