我使用的是VUE + springboot
引包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
//以下是传参数时候从新引入的jar包
<dependency>
<groupId>org.apache.xmlbeans</groupId>
<artifactId>xmlbeans</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
先上VUE页面代码,下载模板的话貌似不能使用ajax,只能使用url方式请求,查了好久,以下方法好用
<button class="layui-btn layui-btn-normal" @click="downLoadInfo">下载模板</button>
//下载模板
downLoadInfo(){
var url = config.userUrl + 'manager/getHeadTeacherExcelTemplate'; //你的方法
fetch(url,{
method: 'POST',
headers: new Headers({
'token':dataUtils.getData(config.key.tokenKey) // 指定提交方式为表单提交
}),
}).then(res => res.blob().then(blob => { //这段不是很清楚
var a = document.createElement('a');
var url = window.URL.createObjectURL(blob);
var filename = '班主任信息模板.xlsx';
a.href = url;
a.download = filename;
a.click();
window.URL.revokeObjectURL(url);
}));
},
如果需要参数需要更改成如下:
fetch(url,{
method: 'POST',
body: JSON.stringify({'business':'1'}),
headers: new Headers({ 'Content-Type': 'application/json',
'token':dataUtils.getData(config.key.tokenKey) // 指定提交方式为表单提交
}),
springboot代码
工具 1
package com.baokang.utils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
/**
* 导出工具类
*
* @author ty
* @version 2019-2-13
*/
public class DownExcelTemp {
/**
* @param
* @param filePath Excel文件路径
* @param
* @param headers Excel列标题(数组)
* @param
* @param downData 下拉框数据(数组)
* @param
* @param downRows 下拉列的序号(数组,序号从0开始)
* @return void
* @throws String filePath, String sheetTitle, List<String> headers, Map<String,String[]> map, String[] fatherOption
* @Title: testFunction
* @Description: 生成Excel导入模板
*/
public static void createExcelTemplate(String filePath, String sheetTitle, List<String> headers, List<String[]> downData, List<String> downRows) throws IOException {
// 创建一个excel
@SuppressWarnings("resource")
XSSFWorkbook book = new XSSFWorkbook();
CellStyle style = book.createCellStyle();
// 创建一个居中格式
style.setAlignment(HorizontalAlignment.CENTER);
// 创建需要用户填写的sheet
XSSFSheet sheetPro = book.createSheet(sheetTitle);
Row row0 = sheetPro.createRow(0);
Cell cell1 = row0.createCell((short) 0);
for (int i = 0; i < headers.size(); i++) {
cell1.setCellValue(headers.get(i));
cell1.setCellStyle(style);
//设置单元格宽度
sheetPro.setColumnWidth(i, 20 * 256);
//设置居中样式
sheetPro.setDefaultColumnStyle(i, style);
cell1 = row0.createCell((short) i + 1);
}
//创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建,否则无法隐藏。
XSSFSheet hideSheet = book.createSheet("hideSheet");
//这一行作用是将此sheet隐藏,功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
book.setSheetHidden(book.getSheetIndex(hideSheet), true);
GetNewExcelTemplate(filePath, downData, downRows, sheetPro, hideSheet, book);
}
/**
* @param filePath 生成的模板文件路径
* @param downData 下拉框数据集合
* @param downRows 设置列的序号集合(从0开始)
*/
public static void GetNewExcelTemplate(String filePath, List<String[]> downData, List<String> downRows, XSSFSheet sheetPro, XSSFSheet hideSheet, XSSFWorkbook workbook) throws IOException {
// FileInputStream fileInput = new FileInputStream(filePath);
// // 创建一个webbook,对应一个Excel文件
// XSSFWorkbook workbook = new XSSFWorkbook(fileInput);
// //对应Excel文件中的sheet,0代表第一个
// XSSFSheet sheet1 = workbook.getSheetAt(0);
// XSSFSheet sheet2 = workbook.getSheetAt(1);
//修改excle表的数据
// 设置下拉框数据
if (downData.size() > 0 && downRows.size() > 0) {
String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
int index = 0;
//数据行
Row row = null;
for (int r = 0; r < downRows.size(); r++) {
// 获取下拉对象
String[] dlData = downData.get(r);
int rownum = Integer.parseInt(downRows.get(r));
// 255以内的下拉
if (dlData.length < 5) {
// 255以内的下拉,参数分别是:作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
// 超过255个报错
sheetPro.addValidationData(setDataValidation(sheetPro, dlData, 1, 5000, rownum, rownum));
} else {
// 255以上的下拉,即下拉列表元素很多的情况
// 1、设置有效性
// String strFormula = "Sheet2!$A$1:$A$5000" ;
// //Sheet2第A1到A5000作为下拉列表来源数据
// Sheet2第A1到A5000作为下拉列表来源数据
String strFormula = "hideSheet!$" + arr[index] + "$1:$" + arr[index] + "$" + (dlData.length + 1);
// 设置每列的列宽
hideSheet.setColumnWidth(r, 4000);
// 设置数据有效性加载在哪个单元格上,参数分别是:从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
// 下拉列表元素很多的情况
sheetPro.addValidationData(SetDataValidation(workbook, hideSheet, ("data" + r), strFormula, 1, 5000, rownum, rownum));
// 2、生成sheet2内容
for (int j = 0; j < dlData.length; j++) {
// 第1个下拉选项,直接创建行、列
if (index == 0) {
// 创建数据行
row = hideSheet.createRow(j);
// 设置每列的列宽
hideSheet.setColumnWidth(j, 4000);
// 设置对应单元格的值
row.createCell(0).setCellValue(dlData[j]);
} else {
// 非第1个下拉选项
int rowCount = hideSheet.getLastRowNum();
// 前面创建过的行,直接获取行,创建列
if (j <= rowCount) {
// 获取行,创建列
// 设置对应单元格的值
hideSheet.getRow(j).createCell(index).setCellValue(dlData[j]);
} else {
// 未创建过的行,直接创建行、创建列
// 设置每列的列宽
hideSheet.setColumnWidth(j, 4000);
// 创建行、创建列
// 设置对应单元格的值
hideSheet.createRow(j).createCell(index).setCellValue(dlData[j]);
}
}
}
index++;
}
}
}
//将修改后的文件写出到D:\\excel目录下
// 写文件
File f = new File(filePath);
// 不存在则新增
if (!f.getParentFile().exists()) {
f.getParentFile().mkdirs();
}
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
out.flush();
workbook.write(out);
//关闭流
out.close();
}
/**
* @Title: SetDataValidation
* @Description: 下拉列表元素很多的情况(255以上的下拉)
* @param @param strFormula
* @param @param firstRow 起始行
* @param @param endRow 终止行
* @param @param firstCol 起始列
* @param @param endCol 终止列
* @param xls格式
* @return HSSFDataValidation @throws
*/
/*private static HSSFDataValidation SetDataValidation(String strFormula, int firstRow, int endRow, int firstCol,
int endCol) {
// 设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
return dataValidation;
}*/
/**
* @param workbook 当前工作簿
* @param sheet 存放下拉菜单数据的sheet
* @param dataName 数据有效性名称
* @param strFormula 下拉菜单取数据范围
* @param firstRow
* @param endRow
* @param firstCol
* @param endCol
* @return
* @Description: xlsx格式 255以上下拉菜单
*/
private static XSSFDataValidation SetDataValidation(XSSFWorkbook workbook, XSSFSheet sheet, String dataName, String strFormula, int firstRow, int endRow, int firstCol, int endCol) {
XSSFName name = workbook.createName();
name.setNameName(dataName);
name.setRefersToFormula(strFormula);
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
.createFormulaListConstraint(dataName);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
//sheet.addValidationData(validation);
return validation;
}
/**
* @Title: setDataValidation @Description:
* 下拉列表元素不多的情况(255以内的下拉) @param @param sheet @param @param
* textList @param @param firstRow @param @param endRow @param @param
* firstCol @param @param endCol @param @return @return
* DataValidation @throws
*/
private static XSSFDataValidation setDataValidation(XSSFSheet sheet, String[] textList, int firstRow, int endRow,
int firstCol, int endCol) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);
CellRangeAddressList addressList = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
// 07默认setSuppressDropDownArrow(true);
// validation.setSuppressDropDownArrow(true);
// validation.setShowErrorBox(true);
return validation;
//xls 格式下拉菜单begin DataValidation
/*DataValidationHelper helper = sheet.getDataValidationHelper();
// 加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
// DVConstraint constraint = new DVConstraint();
constraint.setExplicitListValues(textList);
// 设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol,
(short) endCol);
// 数据有效性对象
DataValidation data_validation = helper.createValidation(constraint, regions);
//xls 格式下拉菜单end
return data_validation;*/
}
}
工具 2
package com.dosion.core.common.utils.excel;
import com.ctrip.framework.apollo.core.utils.PropertiesUtil;
import com.dosion.core.common.utils.StringUtils;
import org.springframework.core.io.DefaultResourceLoader;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
public class DownLoadUtil {
/**
*
* @Title: encodeChineseDownloadFileName
*
* @param @param request
* @param @param pFileName
* @param @return
* @param @throws UnsupportedEncodingException
* @return String
* @throws
*/
public static String encodeChineseDownloadFileName(HttpServletRequest request, String pFileName)
throws UnsupportedEncodingException {
String filename = null;
String agent = request.getHeader("USER-AGENT");
if (null != agent) {
if (-1 != agent.indexOf("Firefox")) {
//Firefox
filename = "=?UTF-8?B?" + (new String(org.apache.commons.codec.binary.Base64.encodeBase64(pFileName.getBytes("UTF-8")))) + "?=";
} else if (-1 != agent.indexOf("Chrome")) {
//Chrome
filename = new String(pFileName.getBytes(), "ISO8859-1");
} else {
//IE7+
filename = java.net.URLEncoder.encode(pFileName, "UTF-8");
//替换特殊字符
filename = StringUtils.replace(filename, "+", "%20");
}
} else {
filename = pFileName;
}
return filename;
}
public static String getPysicalPath(String virtualPath,HttpServletRequest request) {
//获得根绝对路径
String physicalPath = getProjectPath();
//获得项目路径
String basePath = request.getContextPath();
if(virtualPath.startsWith(basePath)){
virtualPath = virtualPath.substring(basePath.length());
}
return physicalPath + virtualPath;
}
/**
* @Title: downFile
* @Description:
* @param @param url文件url
* @param @param fileName 文件名
* @param @param response
* @return void
* @throws
*/
public static void downFile(String url,String fileName,HttpServletRequest request,HttpServletResponse response) {
try {
//1.定义ContentType为("multipart/form-data")让浏览器自己解析文件格式
response.setContentType("multipart/form-data");
//2.中文名转码
// response.setHeader("Content-disposition", "attachment; filename=\""+encodeChineseDownloadFileName(request, fileName+".xlsx") +"\"");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
//获得文件
File file = new File(url);
FileInputStream in = new FileInputStream(file);
//3.将文件写入缓冲区OutputStream(out)
OutputStream out = new BufferedOutputStream(response.getOutputStream());
int b = 0;
byte[] buffer = new byte[2048];
while ((b=in.read(buffer)) != -1){
//4.将缓冲区文件输出到客户端(out)
out.write(buffer,0,b);
}
in.close();
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取工程项目根路径
* @return
*/
public static String getProjectPath(){
// 如果配置了工程路径,则直接返回,否则自动获取。
String projectPath = null;
// if (StringUtils.isNotBlank(projectPath)){
// return projectPath;
// }
try {
File file = new DefaultResourceLoader().getResource("").getFile();
if (file != null){
while(true){
File f = new File(file.getPath() + File.separator + "src" + File.separator + "main");
if (f == null || f.exists()){
break;
}
if (file.getParentFile() != null){
file = file.getParentFile();
}else{
break;
}
}
projectPath = file.toString();
}
} catch (IOException e) {
e.printStackTrace();
}
return projectPath;
}
}
下载方法
@ApiOperation("下载模板")
@RequestMapping(value = "exportCustomerTemplate", method = RequestMethod.POST)
@Permission("customer:customer:edit")
public void exportCustomerTemplate(CustomerInputDTO model, HttpServletRequest request, HttpServletResponse response) throws IOException {
//指定文件路径
String uploadPath = "/src/main/resources/temp/";
// 文件新名
String newFileName = DateUtils.formatDate(new Date(), "yyyyMMddHHmmssSSS") + ".xlsx";
//将虚拟路径转换物理路径
String newFilePath = DownLoadUtil.getPysicalPath(uploadPath + newFileName, request);
//模板名称
String fileName = "客户信息模板";
//设置头
String[] headers = {"客户姓名", "服务方向", "性别", "联系方式", "负责人id", "来源"};
List<String> headerList = new ArrayList<>();
for (int i = 0; i < headers.length; i++) {
headerList.add(headers[i]);
}
//处理数据
DictInputDTO dictInputDTO = new DictInputDTO();
List<DictOutDTO> dictOutDTO = userService.findBusiness(dictInputDTO);
List<CustomerDO> sexList = customerService.findSexList();
List<CustomerDO> sourceList = customerService.findSourceList();
//创建数据集
String sourceStr = "";
String sexStr = "";
String serviceStr = "";
//存放数据集
List<String[]> downData = new ArrayList<>();
//数据所在列
List<String> downRows = Lists.newArrayList();
//下拉框数据处理
for (int i = 0; i < sourceList.size(); i++) {
sourceStr += sourceList.get(i).getSourceStr() + ",";
}
for (int i = 0; i < sexList.size(); i++) {
sexStr += sexList.get(i).getSex() + ",";
}
for (int i = 0; i < dictOutDTO.size(); i++) {
serviceStr += dictOutDTO.get(i).getLabel() + ",";
}
String[] service = serviceStr.substring(0, serviceStr.length()-1).split(",");
String[] sex = sexStr.substring(0, sexStr.length()-1).split(",");
String[] source = sourceStr.substring(0, sourceStr.length()-1).split(",");
downData.add(service);
downRows.add("1");
downData.add(sex);
downRows.add("2");
downData.add(source);
downRows.add("5");
//生成待下载excel模板
DownExcelTemp.createExcelTemplate(newFilePath, fileName, headerList, downData, downRows);
//下载模板
DownLoadUtil.downFile(newFilePath, fileName, request, response);
//删除临时文件
File delFile = new File(newFilePath);
if (delFile.exists()) {
delFile.delete();
}
}
获取参数后台接收参数方式:
加入参数 @RequestBody Map<String, Object> map
导出效果