Excel导入导出工具类
- 前言
- 代码分享
- 1.导出工具类
- 2.导入工具类
- 3.注解类
- 4.字典实体
- 5.导出示例
- 6.导入示例
前言
基于频繁的导出导出业务功能，和功能的重用，设计该工具类，支持功能如下：
1、导出：
支持导出列名、宽度设置；
导出顺序控制；
支持导出字段按字典自动转换（单个或多个值）；
多sheet导出；
2、导入：
非空、数据长度校验、提示信息；
支持导入字段按字典自动转换（单个或多个值）；
代码分享
1.导出工具类
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.linkcm.iemp.viewserver.annotation.ExcelColumn;
import com.linkcm.iemp.viewserver.entity.DictionaryConfPo;
public class ExportUtil {
private static Logger logger = LoggerFactory.getLogger(ExportUtil.class);
/**
* setSheetColumnWidth:设置xls列宽. <br/>
*
* @param sheet 工作表
* @param widthList 列宽
* @author chenpb
*/
public static void setSheetColumnWidth(HSSFSheet sheet, int[] widthList) {
// 增加序列
sheet.setColumnWidth(0, 2000);
if (widthList == null || widthList.length < 1) {
for (int i = 0; i < 10; i++) {
sheet.autoSizeColumn(i);
}
return;
}
for (int i = 0; i < widthList.length; i++) {
sheet.setColumnWidth((i + 1), widthList[i]);
}
}
/**
* createTitle:设置xls标题列. <br/>
*
* @param row 行
* @param style 样式
* @param titleStr 标题
* @author chenpb
*/
public static void createTitle(HSSFRow row, HSSFCellStyle style, List<String> titleList) {
// 增加序列
createCell(row, 0, style, CellType.STRING, "序号");
for (int i = 0; i < titleList.size(); i++) {
createCell(row, (i + 1), style, CellType.STRING, titleList.get(i));
}
}
/**
* createTitleStyle:设置标题样式. <br/>
*
* @param wb 工作簿
* @return
* @author chenpb
*/
public static HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontName("黑体");// 字体
boldFont.setFontHeight((short) 250);// 设置字体大小
//boldFont.setBold(true);//加粗
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置背景填充模式
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());//设置单元格背景
/*// 获取工作簿的调色面板
HSSFPalette palette = wb.getCustomPalette();
// 将调色面板中蓝色索引替换为红色
palette.setColorAtIndex(HSSFColorPredefined.BLUE.getIndex(), (byte) 176, (byte) 226, (byte) 255);
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//设置单元格背景*/
style.setBorderBottom(BorderStyle.THIN);//右边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
// style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
style.setDataFormat(HSSFDataFormat.getBuiltinFormat(""));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);// 自动换行
return style;
}
/**
* createCellStyle:设置数据样式. <br/>
*
* @param wb 工作簿
* @return
* @author chenpb
*/
public static HSSFCellStyle createCellStyle(HSSFWorkbook wb) {
HSSFFont boldFont = wb.createFont();
boldFont.setFontHeight((short) 210); // 设置字体大小
HSSFCellStyle style = wb.createCellStyle();
style.setFont(boldFont);
// style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setWrapText(true);// 自动换行
return style;
}
/**
* createCell:创建单元格. <br/>
*
* @param row 行
* @param column 列
* @param style 样式
* @param cellType 数据类型
* @param value 数据
* @author chenpb
*/
public static void createCell(HSSFRow row, int column, HSSFCellStyle style, CellType cellType, Object value) {
HSSFCell cell = row.createCell(column);
if (style != null) {
cell.setCellStyle(style);
}
if (value != null) {
if (cellType == CellType.STRING) {
cell.setCellType(CellType.STRING);
cell.setCellValue(value.toString());
} else if (cellType == CellType.NUMERIC) {
cell.setCellType(CellType.NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
} else {
cell.setCellType(CellType.STRING);
cell.setCellValue(value.toString());
}
}
}
/**
* setResponseAttribute:设置Response中xls属性. <br/>
*
* @param response
* @param xlsName xls表名
* @throws UnsupportedEncodingException
* @author chenpb
*/
public static void setResponseAttribute(HttpServletResponse response, String xlsName) {
try {
response.reset();
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(xlsName, "UTF-8"));
response.setContentType("application/*; charset=utf-8");
} catch (Exception e) {
logger.error("设置Reesponse中xls属性异常！" + e.getMessage());
e.printStackTrace();
}
}
/**
* export:(excel导出，按默认宽度导). <br/>
*
* @param list 导出的数据
* @param fileName 导出的文件名
* @param os
* @param response
* @author chenpb
*/
public static void export(List<List<String>> list, String fileName, HttpServletResponse response) {
export(list, fileName, response, null);
}
/**
* export:(excel导出，按默认宽度导). <br/>
*
* @param list 导出的数据
* @param fileName 文件名
* @param os
* @author chenpb
*/
public static void export(List<List<String>> list, String fileName, OutputStream os) {
export(list, fileName, os, null);
}
/**
* export:(excel注解导出，按默认宽度导). <br/>
*
* @param list 导出的数据
* @param fileName 文件名
* @param os
* @author chenpb
*/
public static <E> void exportByAnnotation(List<E> list, Class<E> clazz, String fileName, OutputStream os) {
exportByAnnotation(list, clazz, fileName, os, null, null);
}
/**
* export:(excel导出，按指定宽度导). <br/>
*
* @param list 导出的数据
* @param fileName 导出的文件名
* @param os
* @param sheetWidth 列宽度
* @author chenpb
*/
public static void export(List<List<String>> list, String fileName, OutputStream os, int[] sheetWidth) {
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet(fileName);
HSSFCellStyle titleStyle = createTitleStyle(workBook);
HSSFCellStyle cellStyle = createCellStyle(workBook);
for (int i = 0; i < list.size(); i++) {
setSheetColumnWidth(sheet, sheetWidth);
HSSFRow row = sheet.createRow(i);
if (i == 0) {
createTitle(row, titleStyle, list.get(i));
} else {
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, i);
int cellnumb = 1;
for (String value : list.get(i)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
}
}
String date = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
fileName = fileName + "-" + date + ".xls";
try {
workBook.write(os);
workBook.close();
} catch (IOException e1) {
logger.error("excel导出出错！", e1);
}
if (os != null) {
try {
os.flush();
os.close();
} catch (Exception e) {
logger.info("close os error");
}
}
}
/**
* export:(excel导出，按指定宽度导). <br/>
*
* @param list 导出的数据
* @param fileName 导出的文件名
* @param response
* @param sheetWidth 列宽度
* @author chenpb
*/
public static void export(List<List<String>> list, String fileName, HttpServletResponse response, int[] sheetWidth) {
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = workBook.createSheet(fileName);
HSSFCellStyle titleStyle = createTitleStyle(workBook);
HSSFCellStyle cellStyle = createCellStyle(workBook);
for (int i = 0; i < list.size(); i++) {
setSheetColumnWidth(sheet, sheetWidth);
HSSFRow row = sheet.createRow(i);
if (i == 0) {
createTitle(row, titleStyle, list.get(i));
} else {
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, i);
int cellnumb = 1;
for (String value : list.get(i)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
}
}
String date = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
fileName = fileName + "-" + date + ".xls";
setResponseAttribute(response, fileName);
OutputStream os = null;
try {
os = response.getOutputStream();
workBook.write(os);
workBook.close();
} catch (IOException e1) {
logger.error("excel导出出错！", e1);
}
if (os != null) {
try {
os.flush();
os.close();
} catch (Exception e) {
logger.info("close os error");
}
}
}
/**
* export2:(当数据量大，分多个sheet导出). <br/>
*
* @param list 导出的数据
* @param fileName 导出的文件名
* @param response
* @param sheetWidth 列宽度
* @author chenpb
*/
public static void export2(List<List<String>> list, String fileName, HttpServletResponse response, int[] sheetWidth) {
HSSFWorkbook workBook = new HSSFWorkbook();
int size = list.size(); //总数据数
int pageSize = 5000; //每个sheet最大数据数
int pageTotal = size / pageSize + (size % pageSize > 0 ? 1 : 0); //总sheet页数
for (int page = 1; page <= pageTotal; page++) {
HSSFSheet sheet = workBook.createSheet(fileName + "-" + page);
setSheetColumnWidth(sheet, sheetWidth);
HSSFCellStyle titleStyle = createTitleStyle(workBook);
HSSFCellStyle cellStyle = createCellStyle(workBook);
//每个sheet都生成标题行
HSSFRow row = sheet.createRow(0);
createTitle(row, titleStyle, list.get(0));
int k = 1; //记录每个Sheet数据序号
for (int i = (page - 1) * pageSize + 1; i < size && i <= page * pageSize; i++) {
row = sheet.createRow(k);
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, i);
int cellnumb = 1;
for (String value : list.get(i)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
k++;
}
}
String date = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
fileName = fileName + "-" + date + ".xls";
setResponseAttribute(response, fileName);
OutputStream os = null;
try {
os = response.getOutputStream();
workBook.write(os);
workBook.close();
} catch (IOException e1) {
logger.error("excel导出出错！", e1);
}
if (os != null) {
try {
os.flush();
os.close();
} catch (Exception e) {
logger.info("close os error");
}
}
}
/**
* exportSheetPlural:导出多个Sheet
*
* @param fileName 表名称
* @param sheetNameList Sheet名称
* @param Arraylist 数据以List保存
* @param sheetWidthList 列宽，如果只存在一个数组则每个页签宽度都已第一个为准
* @param response
* @author Lijw
*/
public static void exportSheetPlural(String fileName, List<String> sheetNameList, List<List<List<String>>> Arraylist, List<int[]> sheetWidthList, HttpServletResponse response) {
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFCellStyle titleStyle = createTitleStyle(workBook);
HSSFCellStyle cellStyle = createCellStyle(workBook);
for (int i = 0; i < Arraylist.size(); i++) {
int[] sheetWidth = sheetWidthList.size() > 1 ? sheetWidthList.get(i) : sheetWidthList.get(0); //列宽
List<List<String>> list = Arraylist.get(i); //页数据
String sheetName = sheetNameList.get(i); //页名称
HSSFSheet sheet = workBook.createSheet(sheetName);
setSheetColumnWidth(sheet, sheetWidth);
for (int k = 0; k < list.size(); k++) {
HSSFRow row = sheet.createRow(k);
if (k == 0) {
createTitle(row, titleStyle, list.get(k));
} else {
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, k);
int cellnumb = 1;
for (String value : list.get(k)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
}
}
}
String date = new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
fileName = fileName + "-" + date + ".xls";
setResponseAttribute(response, fileName);
OutputStream os = null;
try {
os = response.getOutputStream();
workBook.write(os);
workBook.close();
} catch (IOException e1) {
logger.error("excel导出出错！", e1);
}
if (os != null) {
try {
os.flush();
os.close();
} catch (Exception e) {
logger.info("close os error");
}
}
}
/**
* getExportData: 将数据对象转换为List<List<String>>格式，方便导出数据
*
* @param list 导出的对象数据
* @param clazz 对象类型
* @param dictMap 转义字典
* @return
* @author chenpb
*/
public static <E> List<List<String>> getExportData(List<E> list, Class<E> clazz, HashMap<String, HashMap<String, String>> dictMap) {
List<List<String>> exportList = new ArrayList<>();
List<String> titleList = new ArrayList<>(); //标题行
Map<Integer, String> titleMap = new HashMap<>();
Map<Integer, Field> fieldMap = new HashMap<>();
Field[] fields = clazz.getDeclaredFields();
List<Integer> sortList = new ArrayList<>();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String title = annotation == null ? null : annotation.exportValue(); //获取value注解
if (StringUtils.isEmpty(title)) {
continue;
}
Integer sort = field.getAnnotation(ExcelColumn.class).sort(); //获取sort注解
titleMap.put(sort, title);
fieldMap.put(sort, field);
sortList.add(sort);
}
Collections.sort(sortList); //升序排序
for (Integer sort : sortList) {
titleList.add(titleMap.get(sort));
}
exportList.add(titleList);
for (int i = 0; i < list.size(); i++) {
List<String> cellList = new ArrayList<>(); //行数据
String[] array = null;
E e = list.get(i);
for (Integer sort : sortList) {
Field field = fieldMap.get(sort);
Object o = CompareFieldsUtil.getFieldValue(e, field.getName()); //获取对象属性值
String dictCode = field.getAnnotation(ExcelColumn.class).translationCode(); //获取translationCode注解
String cellValue = o == null ? "" : o.toString();
if (!StringUtils.isEmpty(dictCode)) { //是转义字段，则根据字典把中文转义值转换为数据库保存值
String dictValue = "";
List<String> dictValueArray = new ArrayList<String>();
if (cellValue != null) {
boolean isMultiple = field.getAnnotation(ExcelColumn.class).isMultiple(); //获取是否多选值注解
array = String.valueOf(cellValue).replace("，", ",").split(",");
//元素去除前后空格
for (String s : array) {
String v = StringHelper.trim(s);
if (!StringUtils.isEmpty(v)) {
dictValueArray.add(v);
}
}
//校验字段是否允许多个值
if (!isMultiple && dictValueArray.size() > 1) {
throw new RuntimeException("第" + (i + 1) + "个数据的列(" + titleList.get(i - 1) + ")不允许多个值！");
}
}
if (dictValueArray != null && dictValueArray.size() > 0) {
for (String dict : dictValueArray) {
if (dictMap.get(dictCode) == null || StringUtils.isEmpty(dictMap.get(dictCode).get(dict))) {
throw new RuntimeException("第" + (i + 1) + "个数据的列(" + titleList.get(i - 1) + ")的反转义值：(" + dict + ")失败！从字典获取不到转义数据");
}
dictValue += dictMap.get(dictCode).get(dict) + ",";
}
if (dictValue.lastIndexOf(",") == dictValue.length() - 1) {
dictValue = dictValue.substring(0, dictValue.lastIndexOf(","));
}
}
cellList.add(dictValue);
} else {
cellList.add(cellValue);
}
}
exportList.add(cellList);
}
return exportList;
}
/**
* exportByAnnotation:(excel注解导出，按指定宽度导). <br/>
*
* @param list 导出的对象数据
* @param clazz 对象类型
* @param fileName 导出的文件名
* @param response
* @param sheetWidth 列宽度
* @param extDictList 字典数据
* @author chenpb
*/
public static <E> void exportByAnnotation(List<E> list, Class<E> clazz, String fileName, HttpServletResponse response, int[] sheetWidth, List<DictionaryConfPo> extDictList) {
HashMap<String, HashMap<String, String>> dictMap = changeDictData(extDictList);
List<List<String>> exportList = getExportData(list, clazz, dictMap);
export(exportList, fileName, response, sheetWidth);
}
/**
* exportByAnnotation:(excel注解导出，按指定宽度导). <br/>
*
* @param list 导出的对象数据
* @param clazz 对象类型
* @param fileName 导出的文件名
* @param response
* @param sheetWidth 列宽度
* @param extDictList 字典数据
* @author chenpb
*/
public static <E> void exportByAnnotation(List<E> list, Class<E> clazz, String fileName, OutputStream os, int[] sheetWidth, List<DictionaryConfPo> extDictList) {
HashMap<String, HashMap<String, String>> dictMap = changeDictData(extDictList);
List<List<String>> exportList = getExportData(list, clazz, dictMap);
export(exportList, fileName, os, sheetWidth);
}
/**
* getResourcePath:获取项目资源文件路径. <br/>
*
* @param s
* @return
* @author chenpb
*/
public String getResourcePath(String s) {
return getClass().getClassLoader().getResource(s).getPath();
}
/**
* oppositeGroupList:根据dict_group字段分组，再将字典值中的Key和value反转过来通过Value来获取Key. <br/>
*
* @param list
* @return 格式：HashMap<dict_group, HashMap<value, code>>
* @author chenpb
*/
public static HashMap<String, HashMap<String, String>> oppositeGroupList(List<DictionaryConfPo> list) {
if (CollectionUtils.isEmpty(list)) {
return new HashMap<String, HashMap<String, String>>();
}
HashMap<String, HashMap<String, String>> listMap = new HashMap<String, HashMap<String, String>>();
Iterator<DictionaryConfPo> listIto = list.iterator();
while (listIto.hasNext()) {
DictionaryConfPo dictionary = listIto.next();
String key = dictionary.getDictGroup();
HashMap<String, String> pmsDictionaryMap = listMap.get(key);
if (pmsDictionaryMap == null) {
pmsDictionaryMap = new HashMap<String, String>();
}
pmsDictionaryMap.put(dictionary.getCode(), dictionary.getValue());
listMap.put(key, pmsDictionaryMap);
}
return listMap;
}
/**
* changeDictData: 转换字典数据为HashMap<String, HashMap<String, String>>格式，方便获取字典数据
*
* @param extDictList
* @return
* @author chenpb
*/
public static HashMap<String, HashMap<String, String>> changeDictData(List<DictionaryConfPo> extDictList) {
HashMap<String, HashMap<String, String>> dictMap = null;
if (extDictList != null && extDictList.size() > 0) {
List<DictionaryConfPo> dictList = new ArrayList<>(); //TODO 可以添加读取字典配置表获取数据
dictList.addAll(extDictList);
// 将所有的字典值已键值对的形式保存在HashMap中，转义时直接通过key获取对应的转义
dictMap = oppositeGroupList(dictList);
}
return dictMap;
}
/**
* 将sheet数据写入excel空间
*
* @param workbook excel对象
* @param value 数据对象
* @param sheetName sheet的名字
* @param titleStr 标题名字
* @param dataStr 要获取内容的字段名
* @param widthList 列宽
*/
public static void writeDataToExcel(HSSFWorkbook workbook, List<Map<String, Object>> value, String sheetName, String titleStr, String dataStr, int[] widthList) {
List<List<String>> rowList = new ArrayList<>();
//设置标题的名称
String[] title = titleStr.split(",");
List<String> rowTitle = Arrays.asList(title);
rowList.add(rowTitle);
String[] row = dataStr.split(",");
//设置标题的数据
for (Map<String, Object> map : value) {
List<String> rowData = new ArrayList<>();
for (int i = 0; i < row.length; i++) {
Object obj = map.get(row[i]);
if (obj==null){
rowData.add(null);
continue;
}
rowData.add(obj.toString());
}
rowList.add(rowData);
}
//将数据写入excel
ExportUtil.exportSheets(workbook, rowList, sheetName, widthList);
}
/**
* 将sheet数据写入excel空间
*
* @param workbook excel对象
* @param value 数据对象
* @param sheetName sheet的名字
* @param index 从第几行开始写
* @param dataStr 要获取内容的字段名
* @param widthList 列宽
*/
public static void writeDataToExcel(HSSFWorkbook workbook, List<Map<String, Object>> value, String sheetName, int index, String dataStr, int[] widthList) {
List<List<Object>> rowList = new ArrayList<>();
String[] row = dataStr.split(",");
//设置数据
for (Map<String, Object> map : value) {
List<Object> rowData = new ArrayList<>();
for (int i = 0; i < row.length; i++) {
Object obj = map.get(row[i]);
if (obj==null){
rowData.add(null);
continue;
}
rowData.add(obj);
}
rowList.add(rowData);
}
//将数据写入excel
ExportUtil.exportSheetsNotTitle(workbook, rowList, index, sheetName, widthList);
}
public static void exportSheets(HSSFWorkbook workbook, List<List<String>> list, String sheetName, int[] sheetWidth) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置列宽
setSheetColumnWidth(sheet, sheetWidth);
//设置标题样式
HSSFCellStyle titleStyle = createTitleStyle(workbook);
//设置单元格格样式
HSSFCellStyle cellStyle = createCellStyle(workbook);
//为表格生成标题行
HSSFRow row = sheet.createRow(0);
createTitle(row, titleStyle, list.get(0));
//遍历集合，产生数据行
int index = 1;
for (int i = 1; i < list.size(); i++) {
row = sheet.createRow(index);
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, i);
int cellnumb = 1;
for (String value : list.get(i)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
index++;
}
}
public static void exportSheetsNotTitle(HSSFWorkbook workbook, List<List<Object>> list, int index, String sheetName, int[] sheetWidth) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置列宽
setSheetColumnWidth(sheet, sheetWidth);
//设置标题样式
HSSFCellStyle titleStyle = createTitleStyle(workbook);
//设置单元格格样式
HSSFCellStyle cellStyle = createCellStyle(workbook);
//遍历集合，产生数据行
for (int i = 1; i < list.size(); i++) {
HSSFRow row = sheet.createRow(index);
// 增加序列
createCell(row, 0, cellStyle, CellType.STRING, i);
int cellnumb = 1;
for (Object value : list.get(i)) {
createCell(row, cellnumb, cellStyle, CellType.STRING, value);
cellnumb++;
}
index++;
}
}
}2.导入工具类
public class ReadExcelUtil {
private static final Logger logger = LogManager.getLogger(ReadExcelUtil.class);
private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0"); // 格式化
private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%"); // 格式化分比格式，后面不足2位的用0补齐
private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyyMMddHHmmss");
private static final DecimalFormat DECIMAL_FORMAT_NUMBER = new DecimalFormat("0.00E000"); // 格式化科学计数器
private static final Pattern POINTS_PATTERN = Pattern.compile("^([0-9]{1,}[.][0-9]*)$"); // 小数匹配
// private static final DecimalFormat df_per_ = new DecimalFormat("0.00%"); //格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%
// private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
@PostConstruct //@PostConstruct修饰的方法会在服务器加载Servle的时候运行，并且只会被服务器执行一次。PostConstruct在构造函数之后执行,init()方法之前执行
public void init() {
}
/**
* 对外提供读取excel 的方法，表格数据，数据开始行默认为1，数据开始列默认为2（默认第一列为序号列）
*
* @author peibinchen
* @param file 需要读取的文件
* @param cls 需要转换的对象
* @return 文件通过头文件转换为对象按照顺序保存在List集合中，通过泛型指点对象类型
* @throws IOException
*
* @remark 1.<T>要转换的类型中是通过ExcelColumn自定义注解进行匹配属性的。需要在实体类的熟悉上面加上Excel字段名称进行关联.
* 例如: model: @ExcelColumn(value = "姓名") 对应Excel: 姓名 性别
* private String name; 张三 男
* @ExcelColumn(value = "性别")
* private String sex;
*/
public static <T> List<T> readExcel(MultipartFile file, Class<T> cls, List<DictionaryConfPo> extDictList) throws IOException {
String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
if (Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(file.getInputStream(), cls, extDictList, 1, 2);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 对外提供读取excel 的方法，表格数据，数据开始行默认为1，数据开始列默认为2（默认第一列为序号列）
*
* @author peibinchen
* @param file 需要读取的文件
* @param cls 需要转换的对象
* @return 文件通过头文件转换为对象按照顺序保存在List集合中，通过泛型指点对象类型
* @throws IOException
*
* @remark 1.<T>要转换的类型中是通过ExcelColumn自定义注解进行匹配属性的。需要在实体类的熟悉上面加上Excel字段名称进行关联.
* 例如: model: @ExcelColumn(value = "姓名") 对应Excel: 姓名 性别
* private String name; 张三 男
* @ExcelColumn(value = "性别")
* private String sex;
*/
public static <T> List<T> readExcel(MultipartFile file, Class<T> cls, List<DictionaryConfPo> extDictList, int rowIndex, int colIndex) throws IOException {
String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
if (Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(file.getInputStream(), cls, extDictList, rowIndex, colIndex);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 对外提供读取excel 的方法，表格数据，数据开始行默认为1，数据开始列默认为2（默认第一列为序号列）
*
* @author peibinchen
* @param file 需要读取的文件
* @param cls 需要转换的对象
* @param extDictList 扩展字典数据
* @return 文件通过头文件转换为对象按照顺序保存在List集合中，通过泛型指点对象类型
* @throws IOException
*
* @remark 1.<T>要转换的类型中是通过ExcelColumn自定义注解进行匹配属性的。需要在实体类的熟悉上面加上Excel字段名称进行关联.
* 例如: model: @ExcelColumn(value = "姓名") 对应Excel: 姓名 性别
* private String name; 张三 男
* @ExcelColumn(value = "性别")
* private String sex;
*/
public static <T> List<T> readExcel(File file, Class<T> cls, List<DictionaryConfPo> extDictList) throws IOException {
return readExcel(file, cls, extDictList, 1, 2);
}
/**
* 对外提供读取excel 的方法
*
* @author peibinchen
* @param file 需要读取的文件
* @param cls 需要转换的对象
* @param extDictList 扩展字典数据
* @param rowIndex 表格数据的第一行行号
* @param colIndex 表格数据的第一列列号
* @return 文件通过头文件转换为对象按照顺序保存在List集合中，通过泛型指点对象类型
* @throws IOException
*
* @remark 1.<T>要转换的类型中是通过ExcelColumn自定义注解进行匹配属性的。需要在实体类的熟悉上面加上Excel字段名称进行关联.
* 例如: model: @ExcelColumn(value = "姓名") 对应Excel: 姓名 性别
* private String name; 张三 男
* @ExcelColumn(value = "性别")
* private String sex;
*/
public static <T> List<T> readExcel(File file, Class<T> cls, List<DictionaryConfPo> extDictList, int rowIndex, int colIndex) throws IOException {
String extension = file.getName().substring(file.getName().lastIndexOf(".") + 1).toLowerCase();
if (Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(new FileInputStream(file), cls, extDictList, rowIndex, colIndex);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 对外提供读取excel 的方法
*
* @author peibinchen
* @param file 需要读取的文件
* @param cls 需要转换的对象
* @param extDictList 扩展字典数据
* @param rowIndex 表格数据的第一行行号
* @param colIndex 表格数据的第一列列号
* @return 文件通过头文件转换为对象按照顺序保存在List集合中，通过泛型指点对象类型
* @throws IOException
*
* @remark 1.<T>要转换的类型中是通过ExcelColumn自定义注解进行匹配属性的。需要在实体类的熟悉上面加上Excel字段名称进行关联.
* 例如: model: @ExcelColumn(value = "姓名") 对应Excel: 姓名 性别
* private String name; 张三 男
* @ExcelColumn(value = "性别")
* private String sex;
* 2.extDictList参数使用例子：
List<DictionaryConfPo> extDictList = new ArrayList<>();
DictionaryConfPo dictionary = new DictionaryConfPo();
dictionary.setDictGroup("standard_level");
dictionary.setCode("1"); //对应数据库保存数据
dictionary.setValue("Ⅰ"); //对应转义数据
extDictList.add(dictionary);
final List<SectionEntity> list = ReadExcelUtil.readExcel(f, SectionEntity.class, extDictList);
* 然后在属性加上注解@ExcelColumn(value = "excel列名（例：标准等级）",translationCode="standard_level")
*/
public static <T> List<T> readExcel(InputStream in, String fileName, Class<T> cls, List<DictionaryConfPo> extDictList, int rowIndex, int colIndex) throws IOException {
String extension = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
if (Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
return readExcel(in, cls, extDictList, rowIndex, colIndex);
} else {
throw new IOException("不支持的文件类型");
}
}
/**
* 获取excel数据将之转换成bean
*
* @author peibinchen
* @param InputStream 流
* @param cls 类对象
* @param extDictList 扩展字典数据
* @param <T>
* @return
*/
@SuppressWarnings("resource")
private static <T> List<T> readExcel(InputStream in, Class<T> cls, List<DictionaryConfPo> extDictList, int rowIndex, int colIndex) {
List<T> dataList = new LinkedList<T>();
Workbook workbook = null;
// 从0开始计数，非1开始计数
rowIndex--;
colIndex--;
List<DictionaryConfPo> dictList = new ArrayList<>(); //TODO 可以添加读取字典配置表获取数据
if(extDictList != null && extDictList.size() > 0) {
dictList.addAll(extDictList);
}
// 将所有的字典值已键值对的形式保存在HashMap中，转义时直接通过key获取对应的转义
HashMap<String, HashMap<String, String>> dictMap = oppositeGroupList(dictList);
try {
workbook = WorkbookFactory.create(in); //兼容xls和xlsx
Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();
Field[] superFields = null;
Field[] fields = null;
if(cls.getSuperclass() != null){
superFields = cls.getSuperclass().getDeclaredFields(); //查找父类属性
}
Field[] thisFields = cls.getDeclaredFields(); //查找本类属性
if(superFields != null){ //如果存在父类则于子类合并
fields = new Field[thisFields.length+superFields.length];
System.arraycopy(superFields, 0, fields, 0, superFields.length);
System.arraycopy(thisFields, 0, fields, superFields.length, thisFields.length);
}else{
fields = thisFields; //如果不存在则等于本类
}
/**
* 循环遍历拿到的字段，对比
*/
for (Field field : fields) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
String value = annotation.value();
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<Field>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
}
Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
int sheetsNumber = workbook.getNumberOfSheets();
List<String> titleList = new ArrayList<String>();
Integer columnNum = 0;
for (int n = 0; n < sheetsNumber; n++) { //遍历每个Sheet
Sheet sheet = workbook.getSheetAt(n);
Row firstRow = sheet.getRow(rowIndex);
if(firstRow == null) {
continue;
}
columnNum = firstRow.getPhysicalNumberOfCells(); //获取标题行列数量
if(columnNum == null || columnNum == 0) {
continue;
}
for (int j = sheet.getRow(rowIndex).getFirstCellNum() + colIndex; j < columnNum; j++) { //首行提取注解
Object cellValue = getCellValue(sheet.getRow(rowIndex).getCell(j));
if(cellValue != null){
titleList.add(cellValue.toString()); //保存标题行数据
}
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
Row row = null;
Cell cell = null;
String[] array = null;
for (int i = rowIndex + 1; i < sheet.getPhysicalNumberOfRows(); i++) { //从第[rowIndex + 1]行开始遍历sheet的每一行
row = sheet.getRow(i);
if(isRowEmpty(row)) { //行的每列都为空,跳过该行
continue;
}
T t = cls.newInstance();
for (int j = colIndex; j < columnNum; j++) { //遍历每一行的列
cell = row.getCell(j);
if (reflectionMap.containsKey(j)) {
Object cellValue = getCellValue(cell); //获取列值
List<Field> fieldList = reflectionMap.get(j);
for (Field field : fieldList) {
String dictCode = field.getAnnotation(ExcelColumn.class).translationCode(); //获取translationCode注解
//根据属性的translationCode注解是否存在且不为空判断该字段是否是转义字段
if(StringUtils.hasLength(dictCode)){ //是转义字段，则根据字典把中文转义值转换为数据库保存值
String dictValue = "";
List<String> dictValueArray = new ArrayList<String>();
if (cellValue != null) {
boolean isMultiple = field.getAnnotation(ExcelColumn.class).isMultiple(); //获取是否多选值注解
array = String.valueOf(cellValue).replace("，", ",").split(",");
//元素去除前后空格
for(String s : array){
String v = StringHelper.trim(s);
if(StringUtils.hasLength(v)){
dictValueArray.add(v);
}
}
//校验字段是否允许多个值
if(!isMultiple && dictValueArray.size() > 1){
throw new RuntimeException("第"+ (n+1) + "个Sheet第" + (i+1) + "行第" + (j+1) +"列(" + titleList.get(j-1) + ")不允许多个值！");
}
}
if(dictValueArray != null && dictValueArray.size() > 0){
for (String dict : dictValueArray) {
if(dictMap.get(dictCode) == null || !StringUtils.hasLength(dictMap.get(dictCode).get(dict))){
throw new RuntimeException("第"+ (n+1) + "个Sheet第" + (i+1) + "行第" + (j+1) +"列(" + titleList.get(j-1) + ")的反转义值：(" + dict + ")失败！从字典获取不到转义数据");
}
dictValue += dictMap.get(dictCode).get(dict)+",";
}
if(dictValue.lastIndexOf(",") == dictValue.length() - 1){
dictValue = dictValue.substring(0, dictValue.lastIndexOf(","));
}
if(StringUtils.hasLength(dictValue)){
CompareFieldsUtil.setField(t, field.getName(), dictValue);
}
}
}else{ //不是转义字段，直接赋值
int length = field.getAnnotation(ExcelColumn.class).length(); //获取长度控制注解
if(length > 0 && cellValue != null && length < StringHelper.trim(cellValue.toString()).length()){
throw new RuntimeException("第"+ (n+1) + "个Sheet第" + (i+1) + "行第" + (j+1) +"列(" + titleList.get(j-1) + ")的长度过长(" + StringHelper.trim(cellValue.toString()).length() +"个字符)，限制长度为：" + length + "个字符！");
}
CompareFieldsUtil.setField(t, field.getName(), cellValue);
}
}
}
}
dataList.add(t);
}
}
} catch (Exception e) {
dataList = null;
e.printStackTrace();
logger.error(e.toString());
throw new RuntimeException(e);
} finally {
IOUtils.closeQuietly(workbook);
IOUtils.closeQuietly(in);
}
return dataList;
}
/**
* 获取excel单元格数据
*
* @author peibinchen
* @param cell
* @return
*/
private static Object getCellValue(Cell cell) {
Object value = null;
if(cell != null){
switch (cell.getCellTypeEnum()) {
case _NONE:
break;
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) { // 日期
value = FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));// 统一转成yyyyMMddHHmmss
} else if ("@".equals(cell.getCellStyle().getDataFormatString())
|| "General".equals(cell.getCellStyle().getDataFormatString())) { // 文本 or 常规
value = cell.getNumericCellValue();
} else if (cell.getCellStyle().getDataFormatString()!=null&&cell.getCellStyle().getDataFormatString().startsWith("0_")) { //整型数值
value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
} else if ("0.00E+00".equals(cell.getCellStyle().getDataFormatString())) { // 科学计数
value = cell.getNumericCellValue(); // 待完善
value = DECIMAL_FORMAT_NUMBER.format(value);
} else if ("0.00%".equals(cell.getCellStyle().getDataFormatString())) { // 百分比
value = cell.getNumericCellValue(); // 待完善
value = DECIMAL_FORMAT_PERCENT.format(value);
} else if ("# ?/?".equals(cell.getCellStyle().getDataFormatString())) { // 分数
value = cell.getNumericCellValue();
} else {
value = cell.toString();
}
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case BLANK:
break;
default:
value = cell.toString();
}
}
return value;
}
/**
*
* oppositeGroupList:根据dict_group字段分组，再将字典值中的Key和value反转过来通过Value来获取Key. <br/>
* @author chenpb
* @param list
* @return 格式：HashMap<dict_group, HashMap<value, code>>
*/
public static HashMap<String, HashMap<String, String>> oppositeGroupList(List<DictionaryConfPo> list){
if(CollectionUtils.isEmpty(list)){
return new HashMap<String, HashMap<String, String>>();
}
HashMap<String,HashMap<String, String>> listMap = new HashMap<String, HashMap<String,String>>();
Iterator<DictionaryConfPo> listIto = list.iterator();
while(listIto.hasNext()){
DictionaryConfPo dictionary = listIto.next();
String key = dictionary.getDictGroup();
HashMap<String, String> pmsDictionaryMap = listMap.get(key);
if (pmsDictionaryMap == null){
pmsDictionaryMap = new HashMap<String,String>();
}
pmsDictionaryMap.put(dictionary.getValue(), dictionary.getCode());
listMap.put(key, pmsDictionaryMap);
}
return listMap;
}
/**
* isRowEmpty:判断行的每列是否全空. <br/>
* @author chenpb
* @param row
* @return 全空返回true，否则false
*/
public static boolean isRowEmpty(Row row) {
if(row == null) {
return true;
}
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellTypeEnum() != CellType.BLANK) {
return false;
}
}
return true;
}
}3.注解类
/***
• 给实体类对象加上注解，用于导入导出功能匹配字段
*/@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelColumn{
//Excel的中文列名
public String value() default “”;
//Excel的中文列名(导出)
public String exportValue() default “”;
//需要转义的字典值的code
public String translationCode() default “”;
//是否多选值，默认否
public boolean isMultiple() default false;
//长度控制
public int length() default -1;
//导出时的列顺序
public int sort() default -1;//字段不为空
public boolean isNotNull() default false;
//字段信息
public String msg() default "";4.字典实体
/***
* 字典配置表对象
*/
@TableName("w_conf_dictionary")
public class DictionaryConfPo {
/**
* dictGroup:对应注解
* code：数据库保存的值
* value：数据库保存的中文名称
*/
@ApiModelProperty(value = "id")
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@ApiModelProperty(value = "字典组名称")
@TableField(value = "name", fill = FieldFill.DEFAULT)
private String dictGroup;
@ApiModelProperty(value = "代码")
@TableField(value = "code", fill = FieldFill.DEFAULT)
private String code;
@ApiModelProperty(value = "名称")
@TableField(value = "name", fill = FieldFill.DEFAULT)
private String name;
@ApiModelProperty(value = "值")
@TableField(value = "value", fill = FieldFill.DEFAULT)
private String value;
@ApiModelProperty(value = "状态")
@TableField(value = "status", fill = FieldFill.DEFAULT)
private Integer status;
@ApiModelProperty(value = "描述")
@TableField(value = "description", fill = FieldFill.DEFAULT)
private String description;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDictGroup() {
return dictGroup;
}
public void setDictGroup(String dictGroup) {
this.dictGroup = dictGroup;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}5.导出示例
String exportPath = "D:\\【杨滘】20200511监测数据.xls";
exportPath = URLDecoder.decode(exportPath, "UTF-8");
File outf = new File(exportPath);
if(!outf.exists()){
outf.createNewFile();
}
OutputStream os = new FileOutputStream(outf);
List<List<String>> exportList = new ArrayList<>();
List<String> titleList = new ArrayList<>();
titleList.add("站点名称");
titleList.add("时间");
titleList.add("氰化物");
titleList.add("镉");
exportList.add(titleList);
for(StationMonitor wiPo : stationMonitor) {
List<String> cellList = new ArrayList<>();
cellList.add("杨滘");
cellList.add(get(wiPo, "dataTime"));
cellList.add(get(wiPo, "w21016"));
cellList.add(get(wiPo, "w20115"));
exportList.add(cellList);
}
ExportUtil.export(exportList, "内涝点匹配摄像头", os);6.导入示例
代码如下（示例）：
String url = "D:\\（2019市数据）顺德区主干河涌市监测数据.xlsx";
try {
url = URLDecoder.decode(url, "UTF-8");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
File f = new File(url);
if(!f.exists()){
return;
}
List<DictionaryConfPo> extDictList = new ArrayList<>();
DictionaryConfPo dictionary = new DictionaryConfPo();
dictionary.setDictGroup("standard_level");
dictionary.setCode("1"); //对应数据库保存数据
dictionary.setValue("Ⅰ类"); //对应转义数据
extDictList.add(dictionary);
DictionaryConfPo dictionary2 = new DictionaryConfPo();
dictionary2.setDictGroup("standard_level");
dictionary2.setCode("2"); //对应数据库保存数据
dictionary2.setValue("Ⅱ类"); //对应转义数据
extDictList.add(dictionary2);
DictionaryConfPo dictionary3 = new DictionaryConfPo();
dictionary3.setDictGroup("standard_level");
dictionary3.setCode("3"); //对应数据库保存数据
dictionary3.setValue("Ⅲ类"); //对应转义数据
extDictList.add(dictionary3);
DictionaryConfPo dictionary4 = new DictionaryConfPo();
dictionary4.setDictGroup("standard_level");
dictionary4.setCode("4"); //对应数据库保存数据
dictionary4.setValue("Ⅳ类"); //对应转义数据
extDictList.add(dictionary4);
List<RiverSectionDataEntity> excelList = null;
try {
excelList = ReadExcelUtil.readExcel(f, RiverSectionDataEntity.class, extDictList, 2, 1);
if(excelList == null || excelList.size() < 1) {
return;
}
} catch (IOException e) {
LOG.error(e.getStackTrace());
e.printStackTrace();
}RiverSectionDataEntity 实体类
@Data
public class RiverSectionDataEntity {
private String sectionId;
// 断面编码
private String sectionCode;
// 断面名称，因河涌和断面一对一关系，只提供了河涌的采样点经纬度，没名称等其他信息，所以断面，即采样点的名称命名跟河涌一致
@ExcelColumn(value="河涌名称")
private String sectionName;
// 经度
@ExcelColumn(value="经度")
private String lon;
// 纬度
@ExcelColumn(value="纬度")
private String lat;
// 年
@ExcelColumn(value="年")
private String year;
// 月
@ExcelColumn(value="月")
private String month;
// 监测日期
@ExcelColumn(value="监测日期")
private String dataTime;
// 类型
private String[] type;
// 水温
@ExcelColumn(value="水温(℃)")
private String w01010;
// pH值
@ExcelColumn(value="pH值")
private String w01001;
// 溶解氧
@ExcelColumn(value="溶解氧(mg/L)")
private String w01009;
// 高锰酸盐指数
@ExcelColumn(value="高猛酸盐指数(mg/L)")
private String w01019;
// 水质类别
@ExcelColumn(value="水质类别", translationCode="standard_level")
private Integer stationLevel;
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
