前言
项目中经常遇到，模板导出、导入、导出、针对Excel的使用，此处用于记录学习，其他项目使用，可直接使用
使用 poi 操作
- 模板导出
- 根据模板录入数据后导入
- 数据导出
普通springBoot项目
版本依赖poi
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
一、导出模板（包含下拉框数据和级联下拉框数据）
例一： 导出 组织 人员 类型 有效时间 模板
组织和人员级联 联动 下拉框选择某组织 级联该组织下的所有人员
先看效果图
实现
controller
package com.shan.controller;
import com.shan.entity.common.ExportDefinition;
import com.shan.utils.PoiUtils;
import com.shan.utils.Template2Utils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.*;
/**
* 使用poi进行模板导出 含下拉框数据
*/
@Controller
@RequestMapping("test")
public class TestExportTemplate {
/**
* 含有普通下拉框数据和级联下拉框的模板下载
*
* @param response
*/
@GetMapping("testExportTemplateCascade")
public void testExportTemplateCascade(HttpServletResponse response) {
try {
//存下拉数据
List<Map<String, Object>> dictList = new ArrayList<>();
//存级联数据关系
List<List<ExportDefinition>> edListList = new ArrayList<>();
Map<Integer, String> tableHeader = new HashMap<>();
String[] tableHeaderArray = {"组织", "人员", "类型", "有效时间"};
for (int i = 0; i < tableHeaderArray.length; i++) {
tableHeader.put(i, tableHeaderArray[i]);
}
//存下拉框的值
Map<Integer, String[]> sequenceMap = new HashMap<>();
//为了格式化日期 指定日期列
int[] dates = {3};
System.out.println(Arrays.toString(dates));
// 构建级联下拉框数据
initData(dictList, edListList);
//构建普通下拉数据
String[] typeArray = {"类型一", "类型二", "类型三"};
sequenceMap.put(2, typeArray);
response = PoiUtils.out(response, "test1.xls");
String fn = "test1.xls";
//解决乱码
response.setHeader("Content-Disposition",
"attachment;fileName=" + new String(fn.getBytes("GBK"), "ISO8859-1"));
OutputStream os = null;
try {
os = response.getOutputStream();
Template2Utils.createRelate(os, tableHeader, sequenceMap, dates, edListList, dictList);
os.flush();
} finally {
if (os != null) {
os.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
private void initData(List<Map<String, Object>> dictList, List<List<ExportDefinition>> edListList) {
// 举例 一级组织 二级对应组织下的人员
//存一级下拉数据 二级下拉数据
Map<String, Object> dict = new HashMap<>();
//存 二级下拉数据
Map<String, List<String>> subMap = new HashMap<>();
//模拟组织数据
List<String> list = new ArrayList<>();
list.add("组织一");
list.add("组织二");
//模拟人员数据
List<String> userlist1 = new ArrayList<>();
userlist1.add("张三");
userlist1.add("李四");
List<String> userlist2 = new ArrayList<>();
userlist2.add("王五");
subMap.put("组织一", userlist1);
subMap.put("组织二", userlist2);
dict.put("org", list);
dict.put("user", subMap);
dictList.add(dict);
// 创建数据关系
List<ExportDefinition> edList = new ArrayList<>();
edList.add(new ExportDefinition("", "org", "org", "user", "user"));
edList.add(new ExportDefinition("", "user", "user", "", ""));
edListList.add(edList);
}
/**
* 含有下拉框数据的模板下载
*
* @param response
*/
@GetMapping("testExportTemplateSelect")
public void testExportTemplateSelect(HttpServletResponse response) {
try {
Map<Integer, String> tableHeader = new HashMap<>();
String[] tableHeaderArray = {"组织", "应用软件", "类型", "有效时间"};
for (int i = 0; i < tableHeaderArray.length; i++) {
tableHeader.put(i, tableHeaderArray[i]);
}
//存下拉框的值 (单元格位置:1,2)
Map<Integer, String[]> sequenceMap = new HashMap<>();
//软件下拉数据
String[] appArray = {"软件一", "软件二"};
sequenceMap.put(1, appArray);
//类型下拉数据
String[] typeArray = {"类型一", "类型二", "类型三"};
sequenceMap.put(2, typeArray);
//为了格式化日期 指定日期列(单元格位置:3)
int[] dates = {3};
System.out.println(Arrays.toString(dates));
response = PoiUtils.out(response, "test2.xls");
String fn = "test2.xls";
//解决乱码
response.setHeader("Content-Disposition",
"attachment;fileName=" + new String(fn.getBytes("GBK"), "ISO8859-1"));
OutputStream os = null;
try {
os = response.getOutputStream();
PoiUtils.create(os, tableHeader, sequenceMap, null);
os.flush();
} finally {
if (os != null) {
os.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
相关实体
ExportDefinition
package com.shan.entity.common;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class ExportDefinition {
private String title; // 标题
private String field; // 字段
private int rowIndex; // 所在的行
private int cellIndex; // 所在的列
private String mainDict; // 主字典-用于加载主字典的数据
private String subDict; // 子字典-用于加载subField的数据
private String subField; // 即需要级联的字典
private String refName; // 主字段所在的位置
private String point; // 标题的坐标
private boolean validate;// 是否设置数据的有限性
public ExportDefinition(String title, String field, String mainDict, String subDict, String subField) {
this.title = title;
this.field = field;
this.mainDict = mainDict;
this.subDict = subDict;
this.subField = subField;
}
}
RowCellIndex
package com.shan.entity.common;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class RowCellIndex {
/** 单元格的行索引 */
private Integer rowIndex;
/** 单元格的列索引 */
private Integer cellIndex;
public int incrementRowIndexAndGet() {
this.rowIndex++;
return this.getRowIndex();
}
public int incrementCellIndexAndGet() {
this.cellIndex++;
return this.getCellIndex();
}
public int reduceRowIndexAndGet(){
this.rowIndex--;
return this.getRowIndex();
}
public int reduceCellIndexAndGet(){
this.cellIndex--;
return this.getCellIndex();
}
}
工具类
PoiUtils、DateUtils（这两工具类关于介绍poi工具类的有介绍过）、Template2Utils、LinkagePoi2Utils
package com.shan.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.StringUtils;
public class PoiUtils {
private static final String REGEX = "[a-zA-Z]";
public static final int EXPORT_ROWS_MAX_INDEX = 65535;
private String title;
private String sheetName;
private String[] FixedRowname;
private List<List<Object>> dataList;
HttpServletResponse response;
private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
Pattern p = Pattern.compile("[a-zA-Z]");
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
if (isSet) {
sb.append("set");
} else {
try {
Field attributeField = objClass.getDeclaredField(attribute);
if (attributeField.getType() != Boolean.TYPE && attributeField.getType() != Boolean.class) {
sb.append("get");
} else {
sb.append("is");
}
} catch (SecurityException var7) {
var7.printStackTrace();
} catch (NoSuchFieldException var8) {
var8.printStackTrace();
}
}
if (attribute.charAt(0) != '_' && m.find()) {
sb.append(m.replaceFirst(m.group().toUpperCase()));
} else {
sb.append(attribute);
}
return sb.toString();
}
public static HSSFWorkbook create(Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.setDefaultColumnWidth(15);
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
style.setAlignment((short)2);
HSSFCell cell = null;
int i;
for(i = 0; i < titleMap.size(); ++i) {
cell = row.createCell(i);
cell.setCellValue((String)titleMap.get(i));
cell.setCellStyle(style);
if (dataMap.get(i) != null) {
if (((String[])dataMap.get(i)).length > 10) {
sheet = validationHidden(sheet, wb, (String[])dataMap.get(i), 1, 100, i, i);
} else {
sheet = validation(sheet, (String[])dataMap.get(i), 1, 65535, i, i);
}
}
}
if (dates != null && dates.length > 0) {
for(i = 0; i < dates.length; ++i) {
dataformat(wb, sheet, dates[i]);
}
}
return wb;
}
public static void create(OutputStream out, Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) throws IOException {
create(titleMap, dataMap, dates).write(out);
}
public static void dataformat(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
HSSFCellStyle hcs = wb.createCellStyle();
hcs.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d h:mm"));
sheet.setDefaultColumnStyle(Col, hcs);
}
private static List<Object> doImportExcel(String originUrl, int startRow, int endRow, boolean showInfo, Class<?> clazz) throws IOException {
File file = new File(originUrl);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
} else {
HSSFWorkbook wb = null;
FileInputStream fis = null;
ArrayList rowList = new ArrayList();
try {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) {
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo);
}
Row row = null;
for(int i = startRow; i <= lastRowNum + endRow; ++i) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：", showInfo, false);
for(int j = 0; j < row.getLastCellNum(); ++j) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ", showInfo, false);
}
}
out("", showInfo);
}
}
} catch (IOException var18) {
var18.printStackTrace();
} finally {
fis.close();
}
return returnObjectList(rowList, clazz);
}
}
private static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch(cell.getCellType()) {
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
result = DateUtils.getInstance().dateFormat3(date);
} else {
result = cell.getNumericCellValue();
}
break;
case 1:
result = cell.getStringCellValue();
break;
case 2:
result = cell.getCellFormula();
case 3:
default:
break;
case 4:
result = cell.getBooleanCellValue();
break;
case 5:
result = cell.getErrorCellValue();
}
}
return result.toString();
}
public static List<?> importExcel(String originUrl, int startRow, int endRow, Class<?> clazz) throws IOException {
boolean showInfo = true;
return doImportExcel(originUrl, startRow, endRow, showInfo, clazz);
}
public static HttpServletResponse out(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
return response;
}
private static void out(String info, boolean showInfo) {
if (showInfo) {
System.out.print(info + (showInfo ? "\n" : ""));
}
}
private static void out(String info, boolean showInfo, boolean nextLine) {
if (showInfo) {
if (nextLine) {
System.out.print(info + (showInfo ? "\n" : ""));
} else {
System.out.print(info);
}
}
}
private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
List<Object> objectList = null;
Object obj = null;
String attribute = null;
String value = null;
boolean var6 = false;
try {
objectList = new ArrayList();
Field[] declaredFields = clazz.getDeclaredFields();
Iterator var8 = rowList.iterator();
while(var8.hasNext()) {
Row row = (Row)var8.next();
int j = 0;
obj = clazz.newInstance();
Field[] var10 = declaredFields;
int var11 = declaredFields.length;
for(int var12 = 0; var12 < var11; ++var12) {
Field field = var10[var12];
attribute = field.getName().toString();
value = getCellValue(row.getCell(j));
setAttrributeValue(obj, attribute, value);
++j;
}
objectList.add(obj);
}
} catch (Exception var14) {
var14.printStackTrace();
}
return objectList;
}
private static void setAttrributeValue(Object obj, String attribute, String value) {
String method_name = convertToMethodName(attribute, obj.getClass(), true);
Method[] methods = obj.getClass().getMethods();
Method[] var5 = methods;
int var6 = methods.length;
for(int var7 = 0; var7 < var6; ++var7) {
Method method = var5[var7];
if (method.getName().equals(method_name)) {
Class[] parameterC = method.getParameterTypes();
try {
if (parameterC[0] != Integer.TYPE && parameterC[0] != Integer.class) {
if (parameterC[0] != Float.TYPE && parameterC[0] != Float.class) {
if (parameterC[0] != Double.TYPE && parameterC[0] != Double.class) {
if (parameterC[0] != Byte.TYPE && parameterC[0] != Byte.class) {
if (parameterC[0] != Boolean.TYPE && parameterC[0] != Boolean.class) {
if (parameterC[0] == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
if (!StringUtils.isEmpty(value)) {
date = sdf.parse(value);
}
} catch (Exception var13) {
var13.printStackTrace();
}
method.invoke(obj, date);
} else {
method.invoke(obj, parameterC[0].cast(value));
}
break;
}
method.invoke(obj, Boolean.valueOf(value));
break;
}
method.invoke(obj, Byte.valueOf(value));
break;
}
method.invoke(obj, Double.valueOf(value));
break;
}
method.invoke(obj, Float.valueOf(value));
break;
}
value = value.substring(0, value.lastIndexOf("."));
method.invoke(obj, Integer.valueOf(value));
break;
} catch (IllegalArgumentException var14) {
var14.printStackTrace();
} catch (IllegalAccessException var15) {
var15.printStackTrace();
} catch (InvocationTargetException var16) {
var16.printStackTrace();
} catch (SecurityException var17) {
var17.printStackTrace();
}
}
}
}
public static HSSFSheet validation(HSSFSheet sheet, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strFormulaArray);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(validation);
return sheet;
}
public static HSSFSheet validationHidden(HSSFSheet sheet, HSSFWorkbook wb, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
String hiddenSheet = "hidden" + firstCol;
HSSFSheet hidden = wb.createSheet(hiddenSheet);
int i = 0;
for(int length = strFormulaArray.length; i < length; ++i) {
hidden.createRow(endRow + i).createCell(firstCol).setCellValue(strFormulaArray[i]);
}
HSSFName name = wb.createName();
name.setNameName(hiddenSheet);
name.setRefersToFormula(hiddenSheet + "!A1:A" + (strFormulaArray.length + endRow));
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
wb.setSheetHidden(1, true);
sheet.addValidationData(validation);
return sheet;
}
public PoiUtils(String title, String[] fixedRowname, List<List<Object>> dataList) {
this(title, "sheet", fixedRowname, dataList);
}
public PoiUtils(String title, String sheetName, String[] fixedRowname, List<List<Object>> dataList) {
this.dataList = new ArrayList();
this.title = title;
this.sheetName = sheetName;
this.FixedRowname = fixedRowname;
this.dataList = dataList;
}
public HSSFWorkbook export() throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(this.sheetName);
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
HSSFCellStyle style = this.getStyle(workbook);
int fixedColumnNum = this.FixedRowname.length;
HSSFRow rowRowTitle = sheet.createRow(0);
for(int i = 0; i < fixedColumnNum; ++i) {
HSSFCell titleCell = rowRowTitle.createCell(i);
titleCell.setCellType(1);
if (i == 0) {
titleCell.setCellValue(this.title);
}
titleCell.setCellStyle(columnTopStyle);
}
sheet.addMergedRegion(new Region(0, (short)0, 0, (short)(fixedColumnNum - 1)));
HSSFRow rowRowName = sheet.createRow(1);
int i;
for(i = 0; i < fixedColumnNum; ++i) {
HSSFCell fixedCellRowName = rowRowName.createCell(i);
fixedCellRowName.setCellType(1);
HSSFRichTextString text = new HSSFRichTextString(this.FixedRowname[i]);
fixedCellRowName.setCellValue(text);
fixedCellRowName.setCellStyle(columnTopStyle);
}
for(i = 0; i < this.dataList.size(); ++i) {
List<Object> obj = (List)this.dataList.get(i);
HSSFRow row = sheet.createRow(i + 2);
for(int j = 0; j < obj.size(); ++j) {
HSSFCell cell = row.createCell(j, 0);
cell.setCellValue(String.valueOf(obj.get(j)));
cell.setCellStyle(style);
}
}
return workbook;
} catch (Exception var13) {
var13.printStackTrace();
return null;
}
}
public void export(OutputStream out) throws Exception {
this.export().write(out);
}
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)11);
font.setBoldweight((short)700);
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short)1);
style.setBottomBorderColor((short)8);
style.setBorderLeft((short)1);
style.setLeftBorderColor((short)8);
style.setBorderRight((short)1);
style.setRightBorderColor((short)8);
style.setBorderTop((short)1);
style.setTopBorderColor((short)8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short)2);
style.setVerticalAlignment((short)1);
return style;
}
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short)1);
style.setBottomBorderColor((short)8);
style.setBorderLeft((short)1);
style.setLeftBorderColor((short)8);
style.setBorderRight((short)1);
style.setRightBorderColor((short)8);
style.setBorderTop((short)1);
style.setTopBorderColor((short)8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short)2);
style.setVerticalAlignment((short)1);
return style;
}
}
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.shan.utils;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtils {
private static DateUtils instance;
public DateUtils() {
}
public static DateUtils getInstance() {
if (instance == null) {
instance = new DateUtils();
}
return instance;
}
public long dateformat(String datestr) throws Exception {
return this.dateformat(datestr, "yyyy-MM-dd HH:mm:ss").getTime();
}
public Date dateformat(String datestr, String format) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.parse(datestr);
}
public String dateFormat() {
return this.dateFormat(System.currentTimeMillis());
}
public String dateFormat(Date date) {
return this.dateFormat(date, "yyyyMMddHHmmssSSS");
}
public String dateFormat(Date date, String format) {
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.format(date);
}
public String dateFormat(Long currentTimeMillis) {
return this.dateFormat(new Date(currentTimeMillis), "yyyy/MM/dd");
}
public String dateformat2(String datestr) throws Exception {
return String.valueOf(this.dateformat(datestr));
}
public String dateFormat2(Date date) {
return this.dateFormat(date, "yyyyMMddHHmmss");
}
public String dateFormat3(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss");
}
public String dateFormat4(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss:SSS");
}
public String dateFormat5(Date date) {
return this.dateFormat(date, "yyyy");
}
public String dateFormat6() {
return this.dateFormat(new Date(System.currentTimeMillis()), "yyyy/MM/dd HH:mm:ss");
}
public String dateFormat6(Date date) {
return this.dateFormat(date, "yyyy/MM/dd HH:mm:ss");
}
public String dateFormat7(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss");
}
public long dateformat8(String datestr) throws Exception {
return this.dateformat(datestr, "yyyy/MM/dd HH:mm:ss").getTime();
}
public long dateformat9(String datestr) throws Exception {
return this.dateformat(datestr, "yyyyMMddHHmmss").getTime();
}
public String dateFormat10(Long currentTimeMillis) {
return this.dateFormat(new Date(currentTimeMillis), "yyyy-MM-dd");
}
}
package com.shan.utils;
import com.shan.entity.common.ExportDefinition;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Template2Utils {
public static final int EXPORT_ROWS_MAX_INDEX = 65535;
public static HSSFWorkbook create(Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.setDefaultColumnWidth(15);
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = null;
for (int i = 0; i < titleMap.size(); i++) {
cell = row.createCell(i);
cell.setCellValue(titleMap.get(i));
cell.setCellStyle(style);
if (dataMap.get(i) != null) {
if(dataMap.get(i).length>10) {
sheet = validationHidden(sheet,wb,dataMap.get(i), 1, 100, i, i);
}else{
sheet = validation(sheet,dataMap.get(i), 1, EXPORT_ROWS_MAX_INDEX, i, i);
}
}
}
if (dates != null && dates.length > 0) {
for (int i = 0; i < dates.length; i++) {
dataformat(wb, sheet, dates[i]);
sheet.createRow(1).createCell(dates[i]).setCellValue("2099-01-01");
}
}
//sheet.createRow(1).createCell(5).setCellValue("2099-01-01");
return wb;
}
public static void create(OutputStream out, Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) throws IOException {
create(titleMap, dataMap, dates).write(out);
}
public static void createRelate(OutputStream out, Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates, List<List<ExportDefinition>> edList, List<Map<String, Object>> dictList) throws IOException {
HSSFWorkbook wb = create(titleMap, dataMap, dates);
Map<String, Object> map = new HashMap<>();
int startNumber = 0;
for(int i=0;i<dictList.size();i++){
map = dictList.get(i);
if(!CollectionUtils.isEmpty(map)){
wb = LinkagePoi2Utils.createData(wb, edList.get(i), 0, startNumber, "sheet", "dict_data"+i, map);
}
startNumber+=2;
}
wb.write(out);
}
public static HSSFSheet validationHidden(HSSFSheet sheet,HSSFWorkbook wb,String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
String hiddenSheet = "hidden"+firstCol;
HSSFSheet hidden = wb.createSheet(hiddenSheet); // 创建隐藏域
for (int i = 0, length = strFormulaArray.length; i < length; i++) { // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应来获取>=选中行数的数组，将隐藏域加到结束行之后）
hidden.createRow(endRow+i).createCell(firstCol).setCellValue(strFormulaArray[i]);
}
HSSFName name = wb.createName();
name.setNameName(hiddenSheet);
name.setRefersToFormula(hiddenSheet + "!A1:A" + (strFormulaArray.length + endRow)); // A1:A代表隐藏域创建第?列createCell(?)时。以A1列开始A行数据获取下拉数组
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
wb.setSheetHidden(1, true); // 1隐藏、0显示
sheet.addValidationData(validation);
return sheet;
}
public static HSSFSheet validation(HSSFSheet sheet,String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strFormulaArray);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(validation);
return sheet;
}
public static void dataformat(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
HSSFCellStyle hcs = wb.createCellStyle();
// hcs.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-mm-dd
// hh24:mm;ss"));
// HSSFDataFormat format = wb.createDataFormat();
// hcs.setDataFormat(format.getFormat("yyyy年m月d日"));
hcs.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d h:mm"));
sheet.setDefaultColumnStyle(Col, hcs);
}
public static HttpServletResponse out(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
return response;
}
}
package com.shan.utils;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.*;
import com.shan.entity.common.ExportDefinition;
import com.shan.entity.common.RowCellIndex;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
public class LinkagePoi2Utils {
public static void createDataValidateSubList(Sheet sheet, ExportDefinition ed) {
int rowIndex = ed.getRowIndex();
CellRangeAddressList cal;
DVConstraint constraint;
CellReference cr;
DataValidation dataValidation;
for (int i = 0; i < 1000; i++) {
int tempRowIndex = ++rowIndex;
cal = new CellRangeAddressList(tempRowIndex, tempRowIndex, ed.getCellIndex(), ed.getCellIndex());
cr = new CellReference(rowIndex, ed.getCellIndex() - 1, true, true);
constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cr.formatAsString() + ")");
dataValidation = new HSSFDataValidation(cal, constraint);
dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
sheet.addValidationData(dataValidation);
}
}
/**
* 设置数据有效性
* @param edList
* @param sheet
*/
private static void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {
for (ExportDefinition ed : edList) {
if (ed.isValidate()) {// 说明是下拉选
DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());
if (null == ed.getRefName()) {// 说明是一级下拉选
createDataValidate(sheet, ed, constraint);
} else {// 说明是二级下拉选
createDataValidateSubList(sheet, ed);
}
}
}
}
/**
* @param sheet
* @param ed
* @param constraint
*/
private static void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {
CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());
DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
dataValidation.setSuppressDropDownArrow(false);
// 设置提示信息
dataValidation.createPromptBox("操作提示", "请选择下拉选中的值");
// 设置输入错误信息
dataValidation.createErrorBox("错误提示", "请从下拉选中选择，不要随便输入");
sheet.addValidationData(dataValidation);
}
/**
* 创建数据字典sheet页
* @param edList
* @param wb
*/
@SuppressWarnings("unchecked")
private static void createDictSheet(List<ExportDefinition> edList, Workbook wb, String dictSheetName, Map<String, Object> dic) {
Sheet sheet = wb.createSheet(dictSheetName);
sheet.setDefaultColumnWidth (15);// 设置单元格宽度
wb.setSheetHidden(wb.getSheetIndex(dictSheetName), 1);
RowCellIndex rci = new RowCellIndex(0, 0);
for (ExportDefinition ed : edList) {
String mainDict = ed.getMainDict();
List<String> mainDictList = null;
//第一列选择项
if (null != mainDict && null == ed.getRefName()) {// 是第一个下拉选
Object object = dic.get(mainDict);
if (object instanceof ArrayList){
mainDictList = (List<String>) dic.get(mainDict);
if (mainDictList == null || mainDictList.size() == 0) {
continue;
}
String refersToFormula = createParentDictAndReturnRefFormula(sheet, rci, mainDictList, dictSheetName);
// 创建 命名管理
createName(wb, ed.getField(), refersToFormula);
ed.setValidate(true);
}
}
//被联动选择项
if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {// 联动时加载ed.getSubField()的数据
ExportDefinition subEd = fiterByField(edList, ed.getSubField());// 获取需要级联的那个字段
if (null == subEd) {
continue;
}
subEd.setRefName(ed.getPoint());// 保存主下拉选的位置
subEd.setValidate(true);
Map<String, List<String>> subDictListMap = (Map<String, List<String>>) dic.get(ed.getSubDict());
rci.setRowIndex(1);
rci.setCellIndex(0);
for (String keys : mainDictList) {
List<String> values = subDictListMap.get(keys);
String refersToFormula = createSonDicAndReturnRefFormula(sheet, rci, values, dictSheetName);
// 创建 命名管理
createName(wb, keys, refersToFormula);
rci.setRowIndex(1);
rci.incrementCellIndexAndGet();
}
}
}
}
/**
* 纵向输出数据 -- 子节点
*
* @param sheet
* @param rci
* @param datas
* @return
*/
private static String createSonDicAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dicSheetName) {
Row row = sheet.getRow(rci.getRowIndex());
if (row == null) {
row = sheet.createRow(rci.getRowIndex());
row.setHeightInPoints (20);// 行高
}
int startRow = rci.getRowIndex();
int startCell = rci.getCellIndex();
for (String dict : datas) {
row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));
rci.incrementRowIndexAndGet();
row = sheet.getRow(rci.getRowIndex());
if (row == null) {
row = sheet.createRow(rci.getRowIndex());
row.setHeightInPoints (20);// 行高
}
}
rci.reduceRowIndexAndGet();
int endRow = rci.getRowIndex();
int endCell = rci.getCellIndex();
String startName = new CellReference(dicSheetName, startRow, startCell, true, true).formatAsString();
String endName = new CellReference(endRow, endCell, true, true).formatAsString();
String refersToFormula = startName + ":" + endName;
rci.incrementRowIndexAndGet();
return refersToFormula;
}
/**
* 横向输出数据 -- 父节点
*
* @param sheet
* @param rci
* @param datas
* @return
*/
private static String createParentDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dictSheetName) {
Row row = sheet.createRow(rci.getRowIndex());
row.setHeightInPoints (20);// 行高
rci.setCellIndex(0);
int startRow = rci.getRowIndex();
int startCell = rci.getCellIndex();
for (String dict : datas) {
row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));
rci.incrementCellIndexAndGet();
}
rci.reduceCellIndexAndGet();
int endRow = rci.getRowIndex();
int endCell = rci.getCellIndex();
String startName = new CellReference(dictSheetName, startRow, startCell, true, true).formatAsString();
String endName = new CellReference(endRow, endCell, true, true).formatAsString();
String refersToFormula = startName + ":" + endName;
rci.incrementRowIndexAndGet();
return refersToFormula;
}
/**
* @param wb
* @param nameName 表示命名管理的名字
* @param refersToFormula
*/
private static void createName(Workbook wb, String nameName, String refersToFormula) {
Name name = wb.createName();
// setNameName不能添加首位是空格或数字的值,如果首位是数字,则加入下划线
String reg = "^\\d|\\s$";
if ((nameName.charAt(0)+"").matches(reg)) {
nameName = "_"+nameName;
}
name.setNameName(nameName);
name.setRefersToFormula(refersToFormula);
}
private static ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {
for (ExportDefinition ed : edList) {
if (Objects.equals(ed.getField(), field)) {
return ed;
}
}
return null;
}
/**
* 生成联动数据列
* @param edList
* @param wb
* @param startRow 数据开始存放的行
* @param startCell 数据开始存放的列
* @param targetSheetName
* @return
*/
private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb, int startRow, int startCell, String targetSheetName) {
//获取需要加入联动的sheet
Sheet sheet = wb.getSheet(targetSheetName);
if (sheet == null) {
sheet = wb.createSheet(targetSheetName);
}
//初始化位置
RowCellIndex rci = new RowCellIndex(startRow, startCell);
Row row = sheet.getRow(rci.getRowIndex());
if (row == null) {
//创建行
row = sheet.createRow(rci.getRowIndex());
row.setHeightInPoints (20);// 行高
}
CellReference cr = null;
Cell cell = null;
for (ExportDefinition ed : edList) {
//从顶格开始
//创建单元格
cell = row.getCell(rci.getCellIndex());
if(cell == null){
cell = row.createCell(rci.getCellIndex());
if(ed.getTitle() != null && ed.getTitle().trim() != ""){
cell.setCellValue(new HSSFRichTextString(ed.getTitle()));
}
}
ed.setRowIndex(rci.getRowIndex());
ed.setCellIndex(rci.getCellIndex());
//将单元格坐标映射到excel坐标
cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);
ed.setPoint(cr.formatAsString());
rci.incrementCellIndexAndGet();
}
return sheet;
}
/**
* 创建字典sheet并绑定数据下拉
* @param edList
* @param startRow
* @param startCell
* @param targetSheetName
* @param dictSheetName
* @param dic
*/
public static HSSFWorkbook createData(HSSFWorkbook wb,List<ExportDefinition> edList, int startRow, int startCell, String targetSheetName, String dictSheetName, Map<String, Object> dic){
// 1.生成导出模板
Sheet sheet = createExportSheet(edList, wb, startRow, startCell,targetSheetName);
// 2.创建数据字典sheet页
createDictSheet(edList, wb, dictSheetName, dic);
// 3.设置数据有效性
setDataValidation(edList, sheet);
return wb;
}
public static void main(String[] art){
// 1.准备需要生成excel模板的数据
List<ExportDefinition> edList = new ArrayList<>();
edList.add(new ExportDefinition("建筑", "Building", "Building", "Room", "Room"));
edList.add(new ExportDefinition("场室", "Room", "Room", "", ""));
//生成导出模板
HSSFWorkbook wb = new HSSFWorkbook();
//wb = LinkagePoiUtils.createData(wb,edList,0,0,"Sheet1", "dict_data",null);
// 5.保存excel到本地
OutputStream os;
try {
os = new FileOutputStream("D://4.xls");
wb.write(os);
} catch (Exception e) {
e.printStackTrace();
}
}
}
测试
导出含有普通下拉框数据和级联下拉框的模板下载
浏览器访问
http://localhost:9001/test/testExportTemplateCascade
打开文件
导出含有下拉框数据的模板下载
浏览器访问
http://localhost:9001/test/testExportTemplateSelect
打开文件
二、导出列表
导出列表可参考此文章
重新整理了下
controller
package com.shan.controller;
import com.shan.utils.DateUtils;
import com.shan.utils.MyPoiUtils;
import com.shan.utils.PoiUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* 使用poi导出列表工具类
*/
@Controller
@RequestMapping("test")
public class TestExportData {
private final Logger logger = LoggerFactory.getLogger(TestExportData.class);
/**
* 导出列表
* @param response
*/
@GetMapping("testExportList")
public void testExportList(HttpServletResponse response) {
OutputStream os = null;
try {
//标题
String[] headers = {"组织", "应用软件", "类型"};
//数据集合 模拟查询数据
List<List<Object>> outList = new ArrayList<List<Object>>();
for (int i=0;i<50;i++) {
List<Object> inList = new ArrayList<Object>();
inList.add("组织名" +i);
inList.add("应用软件名" +i);
inList.add("类型名" +i);
outList.add(inList);
}
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("multipart/form-data");
String fileName = "exportAccountDetaiBill.xls";
response.setHeader("Content-Disposition",
"attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
//采用默认导出样式 标题 标题头 数据集合
PoiUtils excelSoftWare = new PoiUtils( "账单明细_" + DateUtils.getInstance().dateFormat(new Date(), "yyyy-MM-dd"), headers, outList);
//自定义宽度 参数 自定义每列宽度 标题 标题头 数据集合
//int [] FixedRownameColumnWidth={10*256,35*256,25*256};
// MyPoiUtils excelSoftWare = new MyPoiUtils( "账单明细_" + DateUtils.getInstance().dateFormat(new Date(), "yyyy-MM-dd"), headers,FixedRownameColumnWidth , outList);
os = response.getOutputStream();
excelSoftWare.export(os);
} catch (Exception e) {
e.printStackTrace();
logger.info("/exportAccountDetaiBill 导出出现异常：", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
工具类
package com.shan.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class MyPoiUtils {
private static final String REGEX = "[a-zA-Z]";
public static final int EXPORT_ROWS_MAX_INDEX = 65535;
private String title;
private String sheetName;
private String[] FixedRowname;
private int[] FixedRownameColumnWidth;
private List<List<Object>> dataList;
HttpServletResponse response;
private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
Pattern p = Pattern.compile("[a-zA-Z]");
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
if (isSet) {
sb.append("set");
} else {
try {
Field attributeField = objClass.getDeclaredField(attribute);
if (attributeField.getType() != Boolean.TYPE && attributeField.getType() != Boolean.class) {
sb.append("get");
} else {
sb.append("is");
}
} catch (SecurityException var7) {
var7.printStackTrace();
} catch (NoSuchFieldException var8) {
var8.printStackTrace();
}
}
if (attribute.charAt(0) != '_' && m.find()) {
sb.append(m.replaceFirst(m.group().toUpperCase()));
} else {
sb.append(attribute);
}
return sb.toString();
}
public static HSSFWorkbook create(Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.setDefaultColumnWidth(15);
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
style.setAlignment((short) 2);
HSSFCell cell = null;
int i;
for (i = 0; i < titleMap.size(); ++i) {
cell = row.createCell(i);
cell.setCellValue((String) titleMap.get(i));
cell.setCellStyle(style);
if (dataMap.get(i) != null) {
if (((String[]) dataMap.get(i)).length > 10) {
sheet = validationHidden(sheet, wb, (String[]) dataMap.get(i), 1, 100, i, i);
} else {
sheet = validation(sheet, (String[]) dataMap.get(i), 1, 65535, i, i);
}
}
}
if (dates != null && dates.length > 0) {
for (i = 0; i < dates.length; ++i) {
dataformat(wb, sheet, dates[i]);
}
}
return wb;
}
public static void create(OutputStream out, Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) throws IOException {
create(titleMap, dataMap, dates).write(out);
}
public static void dataformat(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
HSSFCellStyle hcs = wb.createCellStyle();
hcs.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d h:mm"));
sheet.setDefaultColumnStyle(Col, hcs);
}
private static List<Object> doImportExcel(String originUrl, int startRow, int endRow, boolean showInfo, Class<?> clazz) throws IOException {
File file = new File(originUrl);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
} else {
HSSFWorkbook wb = null;
FileInputStream fis = null;
ArrayList rowList = new ArrayList();
try {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) {
out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo);
}
Row row = null;
for (int i = startRow; i <= lastRowNum + endRow; ++i) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：", showInfo, false);
for (int j = 0; j < row.getLastCellNum(); ++j) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ", showInfo, false);
}
}
out("", showInfo);
}
}
} catch (IOException var18) {
var18.printStackTrace();
} finally {
fis.close();
}
return returnObjectList(rowList, clazz);
}
}
private static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
result = DateUtils.getInstance().dateFormat3(date);
} else {
result = cell.getNumericCellValue();
}
break;
case 1:
result = cell.getStringCellValue();
break;
case 2:
result = cell.getCellFormula();
case 3:
default:
break;
case 4:
result = cell.getBooleanCellValue();
break;
case 5:
result = cell.getErrorCellValue();
}
}
return result.toString();
}
public static List<?> importExcel(String originUrl, int startRow, int endRow, Class<?> clazz) throws IOException {
boolean showInfo = true;
return doImportExcel(originUrl, startRow, endRow, showInfo, clazz);
}
public static HttpServletResponse out(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
return response;
}
private static void out(String info, boolean showInfo) {
if (showInfo) {
System.out.print(info + (showInfo ? "\n" : ""));
}
}
private static void out(String info, boolean showInfo, boolean nextLine) {
if (showInfo) {
if (nextLine) {
System.out.print(info + (showInfo ? "\n" : ""));
} else {
System.out.print(info);
}
}
}
private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
List<Object> objectList = null;
Object obj = null;
String attribute = null;
String value = null;
boolean var6 = false;
try {
objectList = new ArrayList();
Field[] declaredFields = clazz.getDeclaredFields();
Iterator var8 = rowList.iterator();
while (var8.hasNext()) {
Row row = (Row) var8.next();
int j = 0;
obj = clazz.newInstance();
Field[] var10 = declaredFields;
int var11 = declaredFields.length;
for (int var12 = 0; var12 < var11; ++var12) {
Field field = var10[var12];
attribute = field.getName().toString();
value = getCellValue(row.getCell(j));
setAttrributeValue(obj, attribute, value);
++j;
}
objectList.add(obj);
}
} catch (Exception var14) {
var14.printStackTrace();
}
return objectList;
}
private static void setAttrributeValue(Object obj, String attribute, String value) {
String method_name = convertToMethodName(attribute, obj.getClass(), true);
Method[] methods = obj.getClass().getMethods();
Method[] var5 = methods;
int var6 = methods.length;
for (int var7 = 0; var7 < var6; ++var7) {
Method method = var5[var7];
if (method.getName().equals(method_name)) {
Class[] parameterC = method.getParameterTypes();
try {
if (parameterC[0] != Integer.TYPE && parameterC[0] != Integer.class) {
if (parameterC[0] != Float.TYPE && parameterC[0] != Float.class) {
if (parameterC[0] != Double.TYPE && parameterC[0] != Double.class) {
if (parameterC[0] != Byte.TYPE && parameterC[0] != Byte.class) {
if (parameterC[0] != Boolean.TYPE && parameterC[0] != Boolean.class) {
if (parameterC[0] == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
if (!StringUtils.isEmpty(value)) {
date = sdf.parse(value);
}
} catch (Exception var13) {
var13.printStackTrace();
}
method.invoke(obj, date);
} else {
method.invoke(obj, parameterC[0].cast(value));
}
break;
}
method.invoke(obj, Boolean.valueOf(value));
break;
}
method.invoke(obj, Byte.valueOf(value));
break;
}
method.invoke(obj, Double.valueOf(value));
break;
}
method.invoke(obj, Float.valueOf(value));
break;
}
value = value.substring(0, value.lastIndexOf("."));
method.invoke(obj, Integer.valueOf(value));
break;
} catch (IllegalArgumentException var14) {
var14.printStackTrace();
} catch (IllegalAccessException var15) {
var15.printStackTrace();
} catch (InvocationTargetException var16) {
var16.printStackTrace();
} catch (SecurityException var17) {
var17.printStackTrace();
}
}
}
}
public static HSSFSheet validation(HSSFSheet sheet, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strFormulaArray);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(validation);
return sheet;
}
public static HSSFSheet validationHidden(HSSFSheet sheet, HSSFWorkbook wb, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
String hiddenSheet = "hidden" + firstCol;
HSSFSheet hidden = wb.createSheet(hiddenSheet);
int i = 0;
for (int length = strFormulaArray.length; i < length; ++i) {
hidden.createRow(endRow + i).createCell(firstCol).setCellValue(strFormulaArray[i]);
}
HSSFName name = wb.createName();
name.setNameName(hiddenSheet);
name.setRefersToFormula(hiddenSheet + "!A1:A" + (strFormulaArray.length + endRow));
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
wb.setSheetHidden(1, true);
sheet.addValidationData(validation);
return sheet;
}
public MyPoiUtils(String title, String[] fixedRowname, List<List<Object>> dataList) {
this(title, "sheet", fixedRowname, null, dataList);
}
public MyPoiUtils(String title, String[] fixedRowname, int[] fixedRownameColumnWidth, List<List<Object>> dataList) {
this(title, "sheet", fixedRowname, fixedRownameColumnWidth, dataList);
}
public MyPoiUtils(String title, String sheetName, String[] fixedRowname, int[] fixedRownameColumnWidth, List<List<Object>> dataList) {
this.dataList = new ArrayList();
this.title = title;
this.sheetName = sheetName;
this.FixedRowname = fixedRowname;
this.dataList = dataList;
this.FixedRownameColumnWidth = fixedRownameColumnWidth;
}
public HSSFWorkbook export() throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(this.sheetName);
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
HSSFCellStyle style = this.getStyle(workbook);
int fixedColumnNum = this.FixedRowname.length;
HSSFRow rowRowTitle = sheet.createRow(0);
if (this.FixedRownameColumnWidth == null) {
sheet.setDefaultColumnWidth(10);
} else {
int fixedRownameColumnWidthNum = this.FixedRownameColumnWidth.length;
for (int i = 0; i < fixedRownameColumnWidthNum; ++i) {
sheet.setColumnWidth(i, this.FixedRownameColumnWidth[i]);
}
}
for (int i = 0; i < fixedColumnNum; ++i) {
HSSFCell titleCell = rowRowTitle.createCell(i);
titleCell.setCellType(1);
if (i == 0) {
titleCell.setCellValue(this.title);
}
titleCell.setCellStyle(columnTopStyle);
}
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (fixedColumnNum - 1)));
HSSFRow rowRowName = sheet.createRow(1);
int i;
for (i = 0; i < fixedColumnNum; ++i) {
HSSFCell fixedCellRowName = rowRowName.createCell(i);
fixedCellRowName.setCellType(1);
HSSFRichTextString text = new HSSFRichTextString(this.FixedRowname[i]);
fixedCellRowName.setCellValue(text);
fixedCellRowName.setCellStyle(columnTopStyle);
}
for (i = 0; i < this.dataList.size(); ++i) {
List<Object> obj = (List) this.dataList.get(i);
HSSFRow row = sheet.createRow(i + 2);
for (int j = 0; j < obj.size(); ++j) {
HSSFCell cell = row.createCell(j, 0);
cell.setCellValue(String.valueOf(obj.get(j)));
cell.setCellStyle(style);
}
}
return workbook;
} catch (Exception var13) {
var13.printStackTrace();
return null;
}
}
public void export(OutputStream out) throws Exception {
this.export().write(out);
}
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 11);
font.setBoldweight((short) 700);
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short) 1);
style.setBottomBorderColor((short) 8);
style.setBorderLeft((short) 1);
style.setLeftBorderColor((short) 8);
style.setBorderRight((short) 1);
style.setRightBorderColor((short) 8);
style.setBorderTop((short) 1);
style.setTopBorderColor((short) 8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short) 2);
style.setVerticalAlignment((short) 1);
return style;
}
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short) 1);
style.setBottomBorderColor((short) 8);
style.setBorderLeft((short) 1);
style.setLeftBorderColor((short) 8);
style.setBorderRight((short) 1);
style.setRightBorderColor((short) 8);
style.setBorderTop((short) 1);
style.setTopBorderColor((short) 8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short) 2);
style.setVerticalAlignment((short) 1);
return style;
}
}
测试
采用默认格式
http://localhost:9001/test/testExportList
下载后
采用自定义列宽度，放开注释即可
http://localhost:9001/test/testExportList
下载后 导出样式
三、根据导出模板，录入数据后，导入
上传模板会先保存服务器，然后读取内容填充到实体中
controller
package com.shan.controller;
import com.shan.entity.po.OrgImportPo;
import com.shan.utils.PoiUtils;
import com.shan.utils.UploaderUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
/**
* 使用poi 上传并导入数据
*/
@RestController
@RequestMapping("test")
public class TestImportData {
@PostMapping("/importTest")
public String importAuthorization(@RequestParam("file") MultipartFile file) {
if (!file.getOriginalFilename().endsWith("xls")) {
return "类型不符";
}
//上传
String path = UploaderUtils.uploadFile(file, "D:\\Download");
List<?> importExcel;
try {
//读取数据填充到实体中
importExcel = PoiUtils.importExcel(path, 1, 0, OrgImportPo.class);
if (CollectionUtils.isEmpty(importExcel)) {
return "SUCCESS";
}
List<OrgImportPo> listDate = (List<OrgImportPo>) importExcel;
for (OrgImportPo entity : listDate) {
System.out.println(entity.toString());
}
} catch (Exception e) {
e.printStackTrace();
return "ERROR";
}
return "SUCCESS";
}
}
工具类
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.shan.utils;
import java.io.File;
import java.io.IOException;
import java.util.Date;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
public class UploaderUtils {
private static final Logger logger = LoggerFactory.getLogger(UploaderUtils.class);
public UploaderUtils() {
}
public static String uploadFile(MultipartFile file, String filePath) {
String fileName = file.getOriginalFilename();
String newFileName = DateUtils.getInstance().dateFormat(new Date(System.currentTimeMillis()));
String ext = "";
if (!StringUtils.isEmpty(fileName)) {
ext = fileName.substring(fileName.lastIndexOf("."), fileName.length());
}
//目标路径+当时时间 yyyy-MM-dd
String targetFilePath = filePath + File.separator + DateUtils.getInstance().dateFormat10(System.currentTimeMillis());
logger.info("上传路径{}", targetFilePath);
System.out.println();
File targetFile = new File(targetFilePath, newFileName + ext);
if (!targetFile.getParentFile().exists()) {
targetFile.getParentFile().mkdirs();
}
try {
file.transferTo(targetFile);
} catch (IllegalStateException var7) {
var7.printStackTrace();
} catch (IOException var8) {
var8.printStackTrace();
}
return targetFile.getPath();
}
}
测试
通过postMan测试
通过前面下载的模板录入数据
上传
看控制台打印 已上传、并把数据填充到实体中
上传位置
控制台
567
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
