SSM-POI的操作Exce模板导出

水深无声 2022-12-19 09:21 128阅读 0赞

# 前提 #

依赖等可以去看看我的 [java -POI的基本操作Excel文章][java -POI_Excel]

通知浏览器下载工具类(一个流,两个头)  
‘记得扫描到工具类’

import org.springframework.stereotype.Component;
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletResponse;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    
    //一个流 输出流
    //两个头 mine类型 和 打开方式
    
    @Component //交给spring管理
    public class DownloadUtil { 
    	
    	/** * @param filePath 要下载的文件路径 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){ 
    		this.prototypeDownload(new File(filePath), returnName, response, delFlag);
    	}
    
    
    	/** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){ 
    		this.prototypeDownload(file, returnName, response, delFlag);
    	}
    	
    	/** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    	public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){ 
    		// 下载文件
    		FileInputStream inputStream = null;
    		ServletOutputStream outputStream = null;
    		try { 
    			if(!file.exists()) return;
    			response.reset();
    			//设置响应类型 PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。 
    			response.setContentType("application/octet-stream;charset=utf-8");
    
    			//设置响应的文件名称,并转换成中文编码
    			//returnName = URLEncoder.encode(returnName,"UTF-8");
    			returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
    			
    			//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
    			response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
    			
    			//将文件读入响应流
    			inputStream = new FileInputStream(file);
    			outputStream = response.getOutputStream();
    			int length = 1024;
    			int readLength=0;
    			byte buf[] = new byte[1024];
    			readLength = inputStream.read(buf, 0, length);
    			while (readLength != -1) { 
    				outputStream.write(buf, 0, readLength);
    				readLength = inputStream.read(buf, 0, length);
    			}
    		} catch (Exception e) { 
    			e.printStackTrace();
    		} finally { 
    			try { 
    				outputStream.flush();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			try { 
    				outputStream.close();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			try { 
    				inputStream.close();
    			} catch (IOException e) { 
    				e.printStackTrace();
    			}
    			//删除原文件
    			
    			if(delFlag) { 				
    				file.delete();
    			}
    		}
    	}
    
    	/** * by tony 2013-10-17 * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream * @param response HttpServletResponse 写入response * @param returnName 返回的文件名 */
    	public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{ 
    		response.setContentType("application/octet-stream;charset=utf-8");
    		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
    		response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);
    		response.setContentLength(byteArrayOutputStream.size());
    		
    		ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
    		byteArrayOutputStream.writeTo(outputstream);					//写到输出流
    		byteArrayOutputStream.close();									//关闭
    		outputstream.flush();											//刷数据
    	}
    }

# 准备一个Excel模板 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI1NzAyMw_size_16_color_FFFFFF_t_70_pic_center]

## 前端代码(JSP) ##

我创建的是一个jsp文件,只要请求到导出方法即可,其余无所谓

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
    <%@taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
    <%@ include file="../../base.jsp"%>
    <!DOCTYPE html>
    <html>
    
    <head>
        
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <title>数据导出</title>
        <meta name="description" content="AdminLTE2定制版">
        <meta name="keywords" content="AdminLTE2定制版">
        
        <meta content="width=device-width,initial-scale=1,maximum-scale=1,user-scalable=no" name="viewport">
        
    </head>
    <body>
    <div id="frameContent" class="content-wrapper" style="margin-left:0px;">
    
    
            <form role="form" action="请求地址">
                <div class="input-group input-group-sm" >
                    <div class="input-group-addon">
                        <i class="fa fa-calendar"></i>
                    </div>
                    <input type="text" name="inputDate" class="form-control pull-right" id="datepicker1">
                    <span class="input-group-btn">
                <button type="submit" class="btn btn-info btn-flat">根据模板导出</button>
            </span>
                </div>
            </form>
        </div>
    </section>
    </div>
    </body>
    <script src="../../plugins/datepicker/bootstrap-datepicker.js"></script>
    <script src="../../plugins/datepicker/locales/bootstrap-datepicker.zh-CN.js"></script>
    <link rel="stylesheet" href="../../css/style.css">
    <script> $('#datepicker1').datepicker({ language: "zh-CN", autoclose: true, format: 'yyyy-mm', startView: 'months', //开始视图层，为月视图层 maxViewMode:'years', //最大视图层，为年视图层 minViewMode:'months', //最小视图层，为月视图层 }); </script>
    
    </html>

# Java代码模板 #

只是模板,里面中文的地方请仔细看

@Autowired //注入工具类
        private DownloadUtil downloadUtil;
    
     @RequestMapping(value = "/这根据需求来定义" ,name = "带模板导出表")
        public void printExcelTemplate(String inputDate) throws Exception{ 
    // 1、查询导出数据,具体方法根据业务来
            List<CprintExcelVo> contractProductVoList = contractService.findContractProductVoListByShipTime(inputDate,getCompanyId());
    
    // 创建一个有内容的工作薄
            String templatePath = session.getServletContext().getRealPath("/make/xlsprint/tOUTPRODUCT.xlsx");
            //读取项目下的模板
            Workbook workbook = new XSSFWorkbook(new FileInputStream(templatePath));
    		//获取第一个工作簿
            Sheet sheet = workbook.getSheetAt(0);
    // 添加内容2020-10 2020年10月份出货表
            sheet.getRow(0).getCell(1).setCellValue(inputDate.replaceAll("-0","年").replaceAll("-","年")+"月份出货表 ");
    
    
    // 获取模板中的8个样式
            CellStyle[] cellStyles = new CellStyle[8];
            Row row = sheet.getRow(2);
            for (int i = 1; i <=8; i++) { 
                cellStyles[i-1] = row.getCell(i).getCellStyle();
            }
    // 从第二个
            int rowIndex = 2;
            Cell cell = null;
            for (ContractProductVo contractProductVo : contractProductVoList) { 
                row = sheet.createRow(rowIndex);
                row.setHeightInPoints(20);
    
    //以下是获取样式,并将查询的字段写入到表格中
                cell = row.createCell(1);
                cell.setCellValue(CprintExcelVo.getCustomName());
                cell.setCellStyle(cellStyles[0]);
    
                cell = row.createCell(2);
                cell.setCellValue(CprintExcelVo.getContractNo());
                cell.setCellStyle(cellStyles[1]);
    
                cell = row.createCell(3);
                cell.setCellValue(CprintExcelVo.getProductNo());
                cell.setCellStyle(cellStyles[2]);
    
                cell = row.createCell(4);
                cell.setCellValue(CprintExcelVo.getCnumber());
                cell.setCellStyle(cellStyles[3]);
    
                cell = row.createCell(5);
                cell.setCellValue(CprintExcelVo.getFactoryName());
                cell.setCellStyle(cellStyles[4]);
    //将日期格式:yyyy-MM-dd
                cell = row.createCell(6);
                cell.setCellValue( new SimpleDateFormat("yyyy-MM-dd").format( contractProductVo.getDeliveryPeriod())); //日期
                cell.setCellStyle(cellStyles[5]);
    
                cell = row.createCell(7);
                cell.setCellValue( new SimpleDateFormat("yyyy-MM-dd").format( contractProductVo.getShipTime()));   //日期
                cell.setCellStyle(cellStyles[6]);
    
                cell = row.createCell(8);
                cell.setCellValue(contractProductVo.getTradeTerms());
                cell.setCellStyle(cellStyles[7]);
    
                rowIndex++;
            }
    // 创建字节数组输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //将Excel输出到流中
            workbook.write(byteArrayOutputStream);
    //调用工具类,通知浏览器下载
            downloadUtil.download(byteArrayOutputStream,response,"导出表.xlsx");
    
        }

# 结果图 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI1NzAyMw_size_16_color_FFFFFF_t_70_pic_center 1]

[java -POI_Excel]: https://blog.csdn.net/weixin_44257023/article/details/109517585
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI1NzAyMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/98becd33d71d42b1a24339e7f3fb009d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI1NzAyMw_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201106175450188.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDI1NzAyMw==,size_16,color_FFFFFF,t_70#pic_center