1.需要效果.
2.引入的jar包.
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.0.2</version>
</dependency>
3.自定义注解.
import java.lang.annotation.*;
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface DropDownSetField {
/*
* 定义固定下拉内容
*/
String[] source() default {};
/*
* 列标号必须和字段下标一致
*/
int indexNum() default 0;
}
4.实体类添加 easyexecl注释 和自定义注释.
5.controller.
@GetMapping("/downloadTemplate")
public void downloadTemplate(HttpServletResponse response) {
try {
Field[] fields = MerchantsCarrier.class.getDeclaredFields();
Map<Integer, String[]> map = new HashMap<>();
Field field = null;
for (int i = 0; i < fields.length; i++) {
field = fields[i];
DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
if (null != dropDownSetField) {
String[] name = dropDownSetField.source();
if (name != null) {
ExcelUtil.insertMap(map, name, dropDownSetField, i);
} else {
ExcelUtil.insertMap(map, null, dropDownSetField, i);
}
}
}
String fileName = URLEncoder.encode("导入招商载体模板.xlsx", "UTF-8");
OutputStream fileOutputStream = null;
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
fileOutputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, MerchantsCarrier.class)
.registerWriteHandler(new ProductCellWriteHandler(map)).build();
WriteSheet sheet = EasyExcel.writerSheet(0, "导入招商载体模板").build();
excelWriter.write(null, sheet);
excelWriter.finish();
fileOutputStream.flush();
fileOutputStream.close();
} catch (Exception e) {
log.error("下载模板失败", e);
}
}
6.工具类
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
public class ExcelUtil {
public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName,
String sheetName, Class<?> clazz) throws Exception {
// 表头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠左对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
if (!Optional.ofNullable(dropDownSetField).isPresent()) {
return null;
}
// 获取固定下拉信息
String[] source = dropDownSetField.source();
if (null != source && source.length > 0) {
return source;
}
if (null != strings && strings.length > 0) {
try {
String[] dynamicSource = strings;
if (null != dynamicSource && dynamicSource.length > 0) {
return dynamicSource;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//插入到map中
public static void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
String[] sources = ExcelUtil.resove(dropDownSetField, params);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
}
7.监听器
import java.util.Map;
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.stereotype.Component;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
@Component
public class ProductCellWriteHandler implements SheetWriteHandler{
private Map<Integer,String[]> map = null;
public ProductCellWriteHandler(Map<Integer,String[]> map){
this.map = map;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表, v为下拉数据集
map.forEach((k, v) -> {
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置下拉单元格的首行, 末行, 首列, 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示","请输入正确的格式的值);
sheet.addValidationData(validation);
});
}
}