excel 下载
controller
//查询后 开始下载
@requestMAPPing("/printexcel")
public void printExcel(String inputDate) throws Exception {
//借助servletcontext对象的getrealpath获取文件的路径
String templatePath = session.getServletContext().getRealPath("/make/xlsprint/tOUTPRODUCT.xlsx");
//获取模板
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(templatePath);
//初始化
Row nRow = null; //行
Cell nCell = null;
int rIndex = 0;
//获取表格 sheet
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
//1.获取表头行 获取单元格 并修改成我们需要的
nRow = sheet.getRow(rIndex++); // 0
nCell = nRow.getCell(1);
String str = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
nCell.setCellValue(str); //设置表头内容信息
//2.获取表名 不应修改 获取样式信息
nRow = sheet.getRow(rIndex++); // 1
//3.获取表格内容 还是填写数据
int index = 1;
CellStyle style1 = null;
CellStyle style2 = null;
CellStyle style3 = null;
CellStyle style4 = null;
CellStyle style5 = null;
CellStyle style6 = null;
CellStyle style7 = null;
CellStyle style8 = null;
List<ContractProductVo> list = contractProductService.findContractProductByShipTime(companyId, inputDate);
if(list != null && list.size()>0){
for (ContractProductVo vo : list) {
if (rIndex == 2) { //第一条信息 表格有自己的样式
nRow = sheet.getRow(rIndex++); //2
Cell cell1 = nRow.getCell(index++);
Cell cell2 = nRow.getCell(index++);
Cell cell3 = nRow.getCell(index++);
Cell cell4 = nRow.getCell(index++);
Cell cell5 = nRow.getCell(index++);
Cell cell6 = nRow.getCell(index++);
Cell cell7 = nRow.getCell(index++);
Cell cell8 = nRow.getCell(index++);
//输入内容
cell1.setCellValue(vo.getCustomName());
cell2.setCellValue(vo.getContractNo());
cell3.setCellValue(vo.getProductNo());
cell4.setCellValue(vo.getCnumber());
cell5.setCellValue(vo.getFactoryName());
cell6.setCellValue(UtilFuns.dateTimeFormat(vo.getDeliveryPeriod(), "yyyy-MM"));
cell7.setCellValue(UtilFuns.dateTimeFormat(vo.getShipTime(), "yyyy-MM"));
cell8.setCellValue(vo.getTradeTerms());
//获取样式
style1 = cell1.getCellStyle();
style2 = cell2.getCellStyle();
style3 = cell3.getCellStyle();
style4 = cell4.getCellStyle();
style5 = cell5.getCellStyle();
style6 = cell6.getCellStyle();
style7 = cell7.getCellStyle();
style8 = cell8.getCellStyle();
} else {
nRow = sheet.createRow(rIndex++);
Cell cell1 = nRow.createCell(index++);
Cell cell2 = nRow.createCell(index++);
Cell cell3 = nRow.createCell(index++);
Cell cell4 = nRow.createCell(index++);
Cell cell5 = nRow.createCell(index++);
Cell cell6 = nRow.createCell(index++);
Cell cell7 = nRow.createCell(index++);
Cell cell8 = nRow.createCell(index++);
cell1.setCellValue(vo.getCustomName());
cell2.setCellValue(vo.getContractNo());
cell3.setCellValue(vo.getProductNo());
cell4.setCellValue(vo.getCnumber());
cell5.setCellValue(vo.getFactoryName());
cell6.setCellValue(UtilFuns.dateTimeFormat(vo.getDeliveryPeriod(), "yyyy-MM"));
cell7.setCellValue(UtilFuns.dateTimeFormat(vo.getShipTime(), "yyyy-MM"));
cell8.setCellValue(vo.getTradeTerms());
cell1.setCellStyle(style1);
cell2.setCellStyle(style2);
cell3.setCellStyle(style3);
cell4.setCellStyle(style4);
cell5.setCellStyle(style5);
cell6.setCellStyle(style6);
cell7.setCellStyle(style7);
cell8.setCellStyle(style8);
}
index = 1;
}
}else{
nRow = sheet.getRow(rIndex++); //2
Cell cell1 = nRow.getCell(index++);
Cell cell2 = nRow.getCell(index++);
Cell cell3 = nRow.getCell(index++);
Cell cell4 = nRow.getCell(index++);
Cell cell5 = nRow.getCell(index++);
Cell cell6 = nRow.getCell(index++);
Cell cell7 = nRow.getCell(index++);
Cell cell8 = nRow.getCell(index++);
//输入内容
cell1.setCellValue("");
cell2.setCellValue("");
cell3.setCellValue("");
cell4.setCellValue("");
cell5.setCellValue("");
cell6.setCellValue("");
cell7.setCellValue("");
cell8.setCellValue("");
}
//开始输出页面 进行下载
ByteArrayoutputstream byteArrayOutputStream = new ByteArrayOutputStream();
xssfWorkbook.write(byteArrayOutputStream);
new DownloadUtil().download(byteArrayOutputStream, response, str + "出货表.xlsx");
byteArrayOutputStream.close();
xssfWorkbook.close();
}
工具类
package com.itheima.common.utils;
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;
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(); //刷数据
}
}
坐标
<!--poi坐标-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
文章最后发布于: 2019-05-17 19:59:25
相关阅读
Excel中如何做到按递增顺序就行排序呢?接下来是seo实验室小编为大家带来的excel表格数据按递增排序的方法，供大家参考。excel表格
分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow也欢迎大家转载本篇文章。分享知识，造福人民，
Excel中sumif函数的怎么使用的呢?具体怎么去进行的?今天，seo实验室小编就教大家在Excel中sumif函数操作的使用方法。Excel中sumif
迅雷9绿色版下载：链接: https://pan.baidu.com/s/1i5ahBpj 密码: d4cr破解迅雷限制法将以下记录：127.0.0.1 hub5btmain.sandai.net1
第一步：下载eclipse，并安装。 下载链接：http://www.eclipse.org/downloads/ 点击 Download Packages;根据自己的系统选择32位还是64