1、添加依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>
2.直接调用方法下载
package com.shucha.deveiface.web.controller;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
/**
* @author tqf
* @Description 动态生成excel模板文件
* @Version 1.0
* @since 2022-03-17 10:08
*/
@RestController
@RequestMapping("/template")
@Slf4j
public class CreateExcelTemplate {
// 生成的模板文件临时路径
private String filePath = "D://员工信息表.xls";
/**
* 下载excel模板 根据不同的内容生成的
* @param response
* @throws IOException
*/
@GetMapping("/downTemplate")
public void downTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
//模板名称
String fileName = filePath;
//列标题
String[] title = {"姓名","性别","证件类型","证件号码","服务结束时间","参保地","民族"};
//下拉框数据
List<String[]> downData = new ArrayList();
String[] str1 = {"男","女","未知"};
String[] str2 = {"北京","上海","广州","深圳","武汉","长沙","湘潭"};
String[] str3 = {"01-汉族","02-蒙古族","03-回族","04-藏族","05-维吾尔族","06-苗族","07-彝族","08-壮族","09-布依族","10-朝鲜族","11-满族","12-侗族","13-瑶族","14-白族","15-土家族","16-哈尼族","17-哈萨克族","18-傣族","19-黎族","20-傈僳族","21-佤族","22-畲族","23-高山族","24-拉祜族","25-水族","26-东乡族","27-纳西族","28-景颇族","29-柯尔克孜族","30-土族","31-达斡尔族","32-仫佬族","33-羌族","34-布朗族","35-撒拉族","36-毛难族","37-仡佬族","38-锡伯族","39-阿昌族","40-普米族","41-塔吉克族","42-怒族","43-乌孜别克族","44-俄罗斯族","45-鄂温克族","46-德昂族","47-保安族","48-裕固族","49-京族","50-塔塔尔族","51-独龙族","52-鄂伦春族","53-赫哲族","54-门巴族","55-珞巴族","56-基诺族","98-外国血统","99-其他"};
downData.add(str1);
downData.add(str2);
downData.add(str3);
String [] downRows = {"1","5","6"}; //下拉的列序号数组(序号从0开始)
try {
createExcelTemplate(fileName, title, downData, downRows);
//通过文件路径获得File对象
File file = new File(filePath);
//1.设置文件ContentType类型,这样设置,会自动判断下载文件类型
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
//2.设置文件头:最后一个参数是设置下载文件名
String fileName1 = URLEncoder.encode("员工信息", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName1 + ".xls");
FileInputStream in = new FileInputStream(file);
//3.通过response获取OutputStream对象(out)
OutputStream out = new BufferedOutputStream(response.getOutputStream());
int b = 0;
byte[] buffer = new byte[2048];
while ((b=in.read(buffer)) != -1){
out.write(buffer,0,b); //4.写到输出流(out)中
}
in.close();
out.flush();
out.close();
delFile(filePath);
} catch (Exception e) {
log.error("批量导入信息异常:" + e.getMessage());
}
}
/**
* @Title: createExcelTemplate
* @Description: 生成Excel导入模板
* @param @param filePath Excel文件路径
* @param @param handers Excel列标题(数组)
* @param @param downData 下拉框数据(数组)
* @param @param downRows 下拉列的序号(数组,序号从0开始)
* @return void
* @throws
*/
private static void createExcelTemplate(String filePath, String[] title, List<String[]> downData, String[] downRows){
HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
//表头样式
HSSFCellStyle style = wb.createCellStyle();
// 创建一个居中格式
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short)12);
// fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(fontStyle);
// 新建sheet
HSSFSheet sheet1 = wb.createSheet("Sheet1");
HSSFSheet sheet2 = wb.createSheet("Sheet2");
HSSFSheet sheet3 = wb.createSheet("Sheet3");
// 生成sheet1内容
// 第一个sheet的第一行为标题
HSSFRow rowFirst = sheet1.createRow(0);
// 写标题
for(int i=0;i<title.length;i++){
// 获取第一行的每个单元格
HSSFCell cell = rowFirst.createCell(i);
// 设置每列的列宽
sheet1.setColumnWidth(i, 4000);
//加样式
cell.setCellStyle(style);
// 往单元格里写数据
cell.setCellValue(title[i]);
}
// 设置下拉框数据
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;
HSSFRow row = null;
for(int r=0;r<downRows.length;r++){
// 获取下拉对象
String[] dlData = downData.get(r);
int rownum = Integer.parseInt(downRows[r]);
// 255以内的下拉
if(dlData.length<5){
// 255以内的下拉,参数分别是:作用的sheet、下拉内容数组、起始行、终止行、起始列、终止列
sheet1.addValidationData(setDataValidation(sheet1, dlData, 1, 500, rownum ,rownum)); //超过255个报错
} else {
// 255以上的下拉,即下拉列表元素很多的情况
// 1、设置有效性
// Sheet2第A1到A5000作为下拉列表来源数据
// String strFormula = "Sheet2!$A$1:$A$5000" ;
// Sheet2第A1到A5000作为下拉列表来源数据
String strFormula = "Sheet2!$"+arr[index]+"$1:$"+arr[index]+"$5000";
// 设置每列的列宽
sheet2.setColumnWidth(r, 4000);
// 设置数据有效性加载在哪个单元格上,参数分别是:从sheet2获取A1到A5000作为一个下拉的数据、起始行、终止行、起始列、终止列
//下拉列表元素很多的情况
sheet1.addValidationData(SetDataValidation(strFormula, 1, 50000, rownum, rownum));
//2、生成sheet2内容
for(int j=0;j<dlData.length;j++){
if(index==0){ //第1个下拉选项,直接创建行、列
// 创建数据行
row = sheet2.createRow(j);
// 设置每列的列宽
sheet2.setColumnWidth(j, 4000);
// 设置对应单元格的值
row.createCell(0).setCellValue(dlData[j]);
} else { //非第1个下拉选项
int rowCount = sheet2.getLastRowNum();
//System.out.println("========== LastRowNum =========" + rowCount);
// 前面创建过的行,直接获取行,创建列
if(j<=rowCount){
// 获取行,创建列
// 设置对应单元格的值
sheet2.getRow(j).createCell(index).setCellValue(dlData[j]);
} else { //未创建过的行,直接创建行、创建列
// 设置每列的列宽
sheet2.setColumnWidth(j, 4000);
// 创建行、创建列
// 设置对应单元格的值
sheet2.createRow(j).createCell(index).setCellValue(dlData[j]);
}
}
}
index++;
}
}
try {
File f = new File(filePath); //写文件
//不存在则新增
if(!f.getParentFile().exists()){
f.getParentFile().mkdirs();
}
if(!f.exists()){
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
out.flush();
wb.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
*
* @Title: SetDataValidation
* @Description: 下拉列表元素很多的情况 (255以上的下拉)
* @param @param strFormula
* @param @param firstRow 起始行
* @param @param endRow 终止行
* @param @param firstCol 起始列
* @param @param endCol 终止列
* @param @return
* @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);
dataValidation.createErrorBox("Error", "Error");
dataValidation.createPromptBox("", null);
return dataValidation;
}
/**
*
* @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 DataValidation setDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) {
DataValidationHelper helper = sheet.getDataValidationHelper();
//加载下拉列表内容
DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
//DVConstraint constraint = new DVConstraint();
constraint.setExplicitListValues(textList);
//设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow,firstCol, endCol);
//数据有效性对象
DataValidation data_validation = helper.createValidation(constraint, regions);
//DataValidation data_validation = new DataValidation(regions, constraint);
return data_validation;
}
/**
* @Title: delFile
* @Description: 删除文件
* @param @param filePath 文件路径
* @return void
* @throws
*/
public static void delFile(String filePath) {
java.io.File delFile = new java.io.File(filePath);
delFile.delete();
}
}