Billion-Mes.git

admin

2024-11-12 a6316ee0ab82a0f3fc2691f8b5ddbd79e1567086

提交 \| 用户 \| 时间
a6316e	1	package com.billion.common.utils.poi;
A	2
	3	import java.io.File;
	4	import java.io.FileOutputStream;
	5	import java.io.IOException;
	6	import java.io.InputStream;
	7	import java.io.OutputStream;
	8	import java.lang.reflect.Field;
	9	import java.lang.reflect.Method;
	10	import java.lang.reflect.ParameterizedType;
	11	import java.math.BigDecimal;
	12	import java.text.DecimalFormat;
	13	import java.time.LocalDate;
	14	import java.time.LocalDateTime;
	15	import java.util.ArrayList;
	16	import java.util.Arrays;
	17	import java.util.Collection;
	18	import java.util.Comparator;
	19	import java.util.Date;
	20	import java.util.HashMap;
	21	import java.util.List;
	22	import java.util.Map;
	23	import java.util.Set;
	24	import java.util.UUID;
	25	import java.util.stream.Collectors;
	26	import javax.servlet.http.HttpServletResponse;
	27	import org.apache.commons.lang3.ArrayUtils;
	28	import org.apache.commons.lang3.RegExUtils;
	29	import org.apache.commons.lang3.reflect.FieldUtils;
	30	import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
	31	import org.apache.poi.hssf.usermodel.HSSFPicture;
	32	import org.apache.poi.hssf.usermodel.HSSFPictureData;
	33	import org.apache.poi.hssf.usermodel.HSSFShape;
	34	import org.apache.poi.hssf.usermodel.HSSFSheet;
	35	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	36	import org.apache.poi.ooxml.POIXMLDocumentPart;
	37	import org.apache.poi.ss.usermodel.BorderStyle;
	38	import org.apache.poi.ss.usermodel.Cell;
	39	import org.apache.poi.ss.usermodel.CellStyle;
	40	import org.apache.poi.ss.usermodel.CellType;
	41	import org.apache.poi.ss.usermodel.ClientAnchor;
	42	import org.apache.poi.ss.usermodel.DataFormat;
	43	import org.apache.poi.ss.usermodel.DataValidation;
	44	import org.apache.poi.ss.usermodel.DataValidationConstraint;
	45	import org.apache.poi.ss.usermodel.DataValidationHelper;
	46	import org.apache.poi.ss.usermodel.DateUtil;
	47	import org.apache.poi.ss.usermodel.Drawing;
	48	import org.apache.poi.ss.usermodel.FillPatternType;
	49	import org.apache.poi.ss.usermodel.Font;
	50	import org.apache.poi.ss.usermodel.HorizontalAlignment;
	51	import org.apache.poi.ss.usermodel.IndexedColors;
	52	import org.apache.poi.ss.usermodel.Name;
	53	import org.apache.poi.ss.usermodel.PictureData;
	54	import org.apache.poi.ss.usermodel.Row;
	55	import org.apache.poi.ss.usermodel.Sheet;
	56	import org.apache.poi.ss.usermodel.VerticalAlignment;
	57	import org.apache.poi.ss.usermodel.Workbook;
	58	import org.apache.poi.ss.usermodel.WorkbookFactory;
	59	import org.apache.poi.ss.util.CellRangeAddress;
	60	import org.apache.poi.ss.util.CellRangeAddressList;
	61	import org.apache.poi.util.IOUtils;
	62	import org.apache.poi.xssf.streaming.SXSSFWorkbook;
	63	import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
	64	import org.apache.poi.xssf.usermodel.XSSFDataValidation;
	65	import org.apache.poi.xssf.usermodel.XSSFDrawing;
	66	import org.apache.poi.xssf.usermodel.XSSFPicture;
	67	import org.apache.poi.xssf.usermodel.XSSFShape;
	68	import org.apache.poi.xssf.usermodel.XSSFSheet;
	69	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	70	import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
	71	import org.slf4j.Logger;
	72	import org.slf4j.LoggerFactory;
	73	import com.billion.common.annotation.Excel;
	74	import com.billion.common.annotation.Excel.ColumnType;
	75	import com.billion.common.annotation.Excel.Type;
	76	import com.billion.common.annotation.Excels;
	77	import com.billion.common.config.RuoYiConfig;
	78	import com.billion.common.core.domain.AjaxResult;
	79	import com.billion.common.core.text.Convert;
	80	import com.billion.common.exception.UtilException;
	81	import com.billion.common.utils.DateUtils;
	82	import com.billion.common.utils.DictUtils;
	83	import com.billion.common.utils.StringUtils;
	84	import com.billion.common.utils.file.FileTypeUtils;
	85	import com.billion.common.utils.file.FileUtils;
	86	import com.billion.common.utils.file.ImageUtils;
	87	import com.billion.common.utils.reflect.ReflectUtils;
	88
	89	/**
	90	* Excel相关处理
	91	*
	92	* @author ruoyi
	93	*/
	94	public class ExcelUtil<T>
	95	{
	96	private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	97
	98	public static final String FORMULA_REGEX_STR = "=\|-\|\\+\|@";
	99
	100	public static final String[] FORMULA_STR = { "=", "-", "+", "@" };
	101
	102	/**
	103	* 用于dictType属性数据存储，避免重复查缓存
	104	*/
	105	public Map<String, String> sysDictMap = new HashMap<String, String>();
	106
	107	/**
	108	* Excel sheet最大行数，默认65536
	109	*/
	110	public static final int sheetSize = 65536;
	111
	112	/**
	113	* 工作表名称
	114	*/
	115	private String sheetName;
	116
	117	/**
	118	* 导出类型（EXPORT:导出数据；IMPORT：导入模板）
	119	*/
	120	private Type type;
	121
	122	/**
	123	* 工作薄对象
	124	*/
	125	private Workbook wb;
	126
	127	/**
	128	* 工作表对象
	129	*/
	130	private Sheet sheet;
	131
	132	/**
	133	* 样式列表
	134	*/
	135	private Map<String, CellStyle> styles;
	136
	137	/**
	138	* 导入导出数据列表
	139	*/
	140	private List<T> list;
	141
	142	/**
	143	* 注解列表
	144	*/
	145	private List<Object[]> fields;
	146
	147	/**
	148	* 当前行号
	149	*/
	150	private int rownum;
	151
	152	/**
	153	* 标题
	154	*/
	155	private String title;
	156
	157	/**
	158	* 最大高度
	159	*/
	160	private short maxHeight;
	161
	162	/**
	163	* 合并后最后行数
	164	*/
	165	private int subMergedLastRowNum = 0;
	166
	167	/**
	168	* 合并后开始行数
	169	*/
	170	private int subMergedFirstRowNum = 1;
	171
	172	/**
	173	* 对象的子列表方法
	174	*/
	175	private Method subMethod;
	176
	177	/**
	178	* 对象的子列表属性
	179	*/
	180	private List<Field> subFields;
	181
	182	/**
	183	* 统计列表
	184	*/
	185	private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
	186
	187	/**
	188	* 数字格式
	189	*/
	190	private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
	191
	192	/**
	193	* 实体对象
	194	*/
	195	public Class<T> clazz;
	196
	197	/**
	198	* 需要显示列属性
	199	*/
	200	public String[] includeFields;
	201
	202	/**
	203	* 需要排除列属性
	204	*/
	205	public String[] excludeFields;
	206
	207	public ExcelUtil(Class<T> clazz)
	208	{
	209	this.clazz = clazz;
	210	}
	211
	212	/**
	213	* 仅在Excel中显示列属性
	214	*
	215	* @param fields 列属性名示例[单个"name"/多个"id","name"]
	216	*/
	217	public void showColumn(String... fields)
	218	{
	219	this.includeFields = fields;
	220	}
	221
	222	/**
	223	* 隐藏Excel中列属性
	224	*
	225	* @param fields 列属性名示例[单个"name"/多个"id","name"]
	226	*/
	227	public void hideColumn(String... fields)
	228	{
	229	this.excludeFields = fields;
	230	}
	231
	232	public void init(List<T> list, String sheetName, String title, Type type)
	233	{
	234	if (list == null)
	235	{
	236	list = new ArrayList<T>();
	237	}
	238	this.list = list;
	239	this.sheetName = sheetName;
	240	this.type = type;
	241	this.title = title;
	242	createExcelField();
	243	createWorkbook();
	244	createTitle();
	245	createSubHead();
	246	}
	247
	248	/**
	249	* 创建excel第一行标题
	250	*/
	251	public void createTitle()
	252	{
	253	if (StringUtils.isNotEmpty(title))
	254	{
	255	subMergedFirstRowNum++;
	256	subMergedLastRowNum++;
	257	int titleLastCol = this.fields.size() - 1;
	258	if (isSubList())
	259	{
	260	titleLastCol = titleLastCol + subFields.size() - 1;
	261	}
	262	Row titleRow = sheet.createRow(rownum == 0 ? rownum++ : 0);
	263	titleRow.setHeightInPoints(30);
	264	Cell titleCell = titleRow.createCell(0);
	265	titleCell.setCellStyle(styles.get("title"));
	266	titleCell.setCellValue(title);
	267	sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
	268	}
	269	}
	270
	271	/**
	272	* 创建对象的子列表名称
	273	*/
	274	public void createSubHead()
	275	{
	276	if (isSubList())
	277	{
	278	subMergedFirstRowNum++;
	279	subMergedLastRowNum++;
	280	Row subRow = sheet.createRow(rownum);
	281	int excelNum = 0;
	282	for (Object[] objects : fields)
	283	{
	284	Excel attr = (Excel) objects[1];
	285	Cell headCell1 = subRow.createCell(excelNum);
	286	headCell1.setCellValue(attr.name());
	287	headCell1.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
	288	excelNum++;
	289	}
	290	int headFirstRow = excelNum - 1;
	291	int headLastRow = headFirstRow + subFields.size() - 1;
	292	if (headLastRow > headFirstRow)
	293	{
	294	sheet.addMergedRegion(new CellRangeAddress(rownum, rownum, headFirstRow, headLastRow));
	295	}
	296	rownum++;
	297	}
	298	}
	299
	300	/**
	301	* 对excel表单默认第一个索引名转换成list
	302	*
	303	* @param is 输入流
	304	* @return 转换后集合
	305	*/
	306	public List<T> importExcel(InputStream is)
	307	{
	308	List<T> list = null;
	309	try
	310	{
	311	list = importExcel(is, 0);
	312	}
	313	catch (Exception e)
	314	{
	315	log.error("导入Excel异常{}", e.getMessage());
	316	throw new UtilException(e.getMessage());
	317	}
	318	finally
	319	{
	320	IOUtils.closeQuietly(is);
	321	}
	322	return list;
	323	}
	324
	325	/**
	326	* 对excel表单默认第一个索引名转换成list
	327	*
	328	* @param is 输入流
	329	* @param titleNum 标题占用行数
	330	* @return 转换后集合
	331	*/
	332	public List<T> importExcel(InputStream is, int titleNum) throws Exception
	333	{
	334	return importExcel(StringUtils.EMPTY, is, titleNum);
	335	}
	336
	337	/**
	338	* 对excel表单指定表格索引名转换成list
	339	*
	340	* @param sheetName 表格索引名
	341	* @param titleNum 标题占用行数
	342	* @param is 输入流
	343	* @return 转换后集合
	344	*/
	345	public List<T> importExcel(String sheetName, InputStream is, int titleNum) throws Exception
	346	{
	347	this.type = Type.IMPORT;
	348	this.wb = WorkbookFactory.create(is);
	349	List<T> list = new ArrayList<T>();
	350	// 如果指定sheet名,则取指定sheet中的内容否则默认指向第1个sheet
	351	Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
	352	if (sheet == null)
	353	{
	354	throw new IOException("文件sheet不存在");
	355	}
	356	boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
	357	Map<String, PictureData> pictures;
	358	if (isXSSFWorkbook)
	359	{
	360	pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);
	361	}
	362	else
	363	{
	364	pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);
	365	}
	366	// 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1
	367	int rows = sheet.getLastRowNum();
	368	if (rows > 0)
	369	{
	370	// 定义一个map用于存放excel列的序号和field.
	371	Map<String, Integer> cellMap = new HashMap<String, Integer>();
	372	// 获取表头
	373	Row heard = sheet.getRow(titleNum);
	374	for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
	375	{
	376	Cell cell = heard.getCell(i);
	377	if (StringUtils.isNotNull(cell))
	378	{
	379	String value = this.getCellValue(heard, i).toString();
	380	cellMap.put(value, i);
	381	}
	382	else
	383	{
	384	cellMap.put(null, i);
	385	}
	386	}
	387	// 有数据时才处理得到类的所有field.
	388	List<Object[]> fields = this.getFields();
	389	Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();
	390	for (Object[] objects : fields)
	391	{
	392	Excel attr = (Excel) objects[1];
	393	Integer column = cellMap.get(attr.name());
	394	if (column != null)
	395	{
	396	fieldsMap.put(column, objects);
	397	}
	398	}
	399	for (int i = titleNum + 1; i <= rows; i++)
	400	{
	401	// 从第2行开始取数据,默认第一行是表头.
	402	Row row = sheet.getRow(i);
	403	// 判断当前行是否是空行
	404	if (isRowEmpty(row))
	405	{
	406	continue;
	407	}
	408	T entity = null;
	409	for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet())
	410	{
	411	Object val = this.getCellValue(row, entry.getKey());
	412
	413	// 如果不存在实例则新建.
	414	entity = (entity == null ? clazz.newInstance() : entity);
	415	// 从map中得到对应列的field.
	416	Field field = (Field) entry.getValue()[0];
	417	Excel attr = (Excel) entry.getValue()[1];
	418	// 取得类型,并根据对象类型设置值.
	419	Class<?> fieldType = field.getType();
	420	if (String.class == fieldType)
	421	{
	422	String s = Convert.toStr(val);
	423	if (StringUtils.endsWith(s, ".0"))
	424	{
	425	val = StringUtils.substringBefore(s, ".0");
	426	}
	427	else
	428	{
	429	String dateFormat = field.getAnnotation(Excel.class).dateFormat();
	430	if (StringUtils.isNotEmpty(dateFormat))
	431	{
	432	val = parseDateToStr(dateFormat, val);
	433	}
	434	else
	435	{
	436	val = Convert.toStr(val);
	437	}
	438	}
	439	}
	440	else if ((Integer.TYPE == fieldType \|\| Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
	441	{
	442	val = Convert.toInt(val);
	443	}
	444	else if ((Long.TYPE == fieldType \|\| Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
	445	{
	446	val = Convert.toLong(val);
	447	}
	448	else if (Double.TYPE == fieldType \|\| Double.class == fieldType)
	449	{
	450	val = Convert.toDouble(val);
	451	}
	452	else if (Float.TYPE == fieldType \|\| Float.class == fieldType)
	453	{
	454	val = Convert.toFloat(val);
	455	}
	456	else if (BigDecimal.class == fieldType)
	457	{
	458	val = Convert.toBigDecimal(val);
	459	}
	460	else if (Date.class == fieldType)
	461	{
	462	if (val instanceof String)
	463	{
	464	val = DateUtils.parseDate(val);
	465	}
	466	else if (val instanceof Double)
	467	{
	468	val = DateUtil.getJavaDate((Double) val);
	469	}
	470	}
	471	else if (Boolean.TYPE == fieldType \|\| Boolean.class == fieldType)
	472	{
	473	val = Convert.toBool(val, false);
	474	}
	475	if (StringUtils.isNotNull(fieldType))
	476	{
	477	String propertyName = field.getName();
	478	if (StringUtils.isNotEmpty(attr.targetAttr()))
	479	{
	480	propertyName = field.getName() + "." + attr.targetAttr();
	481	}
	482	if (StringUtils.isNotEmpty(attr.readConverterExp()))
	483	{
	484	val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
	485	}
	486	else if (StringUtils.isNotEmpty(attr.dictType()))
	487	{
	488	if (!sysDictMap.containsKey(attr.dictType() + val))
	489	{
	490	String dictValue = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());
	491	sysDictMap.put(attr.dictType() + val, dictValue);
	492	}
	493	val = sysDictMap.get(attr.dictType() + val);
	494	}
	495	else if (!attr.handler().equals(ExcelHandlerAdapter.class))
	496	{
	497	val = dataFormatHandlerAdapter(val, attr, null);
	498	}
	499	else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))
	500	{
	501	PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());
	502	if (image == null)
	503	{
	504	val = "";
	505	}
	506	else
	507	{
	508	byte[] data = image.getData();
	509	val = FileUtils.writeImportBytes(data);
	510	}
	511	}
	512	ReflectUtils.invokeSetter(entity, propertyName, val);
	513	}
	514	}
	515	list.add(entity);
	516	}
	517	}
	518	return list;
	519	}
	520
	521	/**
	522	* 对list数据源将其里面的数据导入到excel表单
	523	*
	524	* @param list 导出数据集合
	525	* @param sheetName 工作表的名称
	526	* @return 结果
	527	*/
	528	public AjaxResult exportExcel(List<T> list, String sheetName)
	529	{
	530	return exportExcel(list, sheetName, StringUtils.EMPTY);
	531	}
	532
	533	/**
	534	* 对list数据源将其里面的数据导入到excel表单
	535	*
	536	* @param list 导出数据集合
	537	* @param sheetName 工作表的名称
	538	* @param title 标题
	539	* @return 结果
	540	*/
	541	public AjaxResult exportExcel(List<T> list, String sheetName, String title)
	542	{
	543	this.init(list, sheetName, title, Type.EXPORT);
	544	return exportExcel();
	545	}
	546
	547	/**
	548	* 对list数据源将其里面的数据导入到excel表单
	549	*
	550	* @param response 返回数据
	551	* @param list 导出数据集合
	552	* @param sheetName 工作表的名称
	553	* @return 结果
	554	*/
	555	public void exportExcel(HttpServletResponse response, List<T> list, String sheetName)
	556	{
	557	exportExcel(response, list, sheetName, StringUtils.EMPTY);
	558	}
	559
	560	/**
	561	* 对list数据源将其里面的数据导入到excel表单
	562	*
	563	* @param response 返回数据
	564	* @param list 导出数据集合
	565	* @param sheetName 工作表的名称
	566	* @param title 标题
	567	* @return 结果
	568	*/
	569	public void exportExcel(HttpServletResponse response, List<T> list, String sheetName, String title)
	570	{
	571	response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	572	response.setCharacterEncoding("utf-8");
	573	this.init(list, sheetName, title, Type.EXPORT);
	574	exportExcel(response);
	575	}
	576
	577	/**
	578	* 对list数据源将其里面的数据导入到excel表单
	579	*
	580	* @param sheetName 工作表的名称
	581	* @return 结果
	582	*/
	583	public AjaxResult importTemplateExcel(String sheetName)
	584	{
	585	return importTemplateExcel(sheetName, StringUtils.EMPTY);
	586	}
	587
	588	/**
	589	* 对list数据源将其里面的数据导入到excel表单
	590	*
	591	* @param sheetName 工作表的名称
	592	* @param title 标题
	593	* @return 结果
	594	*/
	595	public AjaxResult importTemplateExcel(String sheetName, String title)
	596	{
	597	this.init(null, sheetName, title, Type.IMPORT);
	598	return exportExcel();
	599	}
	600
	601	/**
	602	* 对list数据源将其里面的数据导入到excel表单
	603	*
	604	* @param sheetName 工作表的名称
	605	* @return 结果
	606	*/
	607	public void importTemplateExcel(HttpServletResponse response, String sheetName)
	608	{
	609	importTemplateExcel(response, sheetName, StringUtils.EMPTY);
	610	}
	611
	612	/**
	613	* 对list数据源将其里面的数据导入到excel表单
	614	*
	615	* @param sheetName 工作表的名称
	616	* @param title 标题
	617	* @return 结果
	618	*/
	619	public void importTemplateExcel(HttpServletResponse response, String sheetName, String title)
	620	{
	621	response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	622	response.setCharacterEncoding("utf-8");
	623	this.init(null, sheetName, title, Type.IMPORT);
	624	exportExcel(response);
	625	}
	626
	627	/**
	628	* 对list数据源将其里面的数据导入到excel表单
	629	*
	630	* @return 结果
	631	*/
	632	public void exportExcel(HttpServletResponse response)
	633	{
	634	try
	635	{
	636	writeSheet();
	637	wb.write(response.getOutputStream());
	638	}
	639	catch (Exception e)
	640	{
	641	log.error("导出Excel异常{}", e.getMessage());
	642	}
	643	finally
	644	{
	645	IOUtils.closeQuietly(wb);
	646	}
	647	}
	648
	649	/**
	650	* 对list数据源将其里面的数据导入到excel表单
	651	*
	652	* @return 结果
	653	*/
	654	public AjaxResult exportExcel()
	655	{
	656	OutputStream out = null;
	657	try
	658	{
	659	writeSheet();
	660	String filename = encodingFilename(sheetName);
	661	out = new FileOutputStream(getAbsoluteFile(filename));
	662	wb.write(out);
	663	return AjaxResult.success(filename);
	664	}
	665	catch (Exception e)
	666	{
	667	log.error("导出Excel异常{}", e.getMessage());
	668	throw new UtilException("导出Excel失败，请联系网站管理员！");
	669	}
	670	finally
	671	{
	672	IOUtils.closeQuietly(wb);
	673	IOUtils.closeQuietly(out);
	674	}
	675	}
	676
	677	/**
	678	* 创建写入数据到Sheet
	679	*/
	680	public void writeSheet()
	681	{
	682	// 取出一共有多少个sheet.
	683	int sheetNo = Math.max(1, (int) Math.ceil(list.size() * 1.0 / sheetSize));
	684	for (int index = 0; index < sheetNo; index++)
	685	{
	686	createSheet(sheetNo, index);
	687
	688	// 产生一行
	689	Row row = sheet.createRow(rownum);
	690	int column = 0;
	691	// 写入各个字段的列头名称
	692	for (Object[] os : fields)
	693	{
	694	Field field = (Field) os[0];
	695	Excel excel = (Excel) os[1];
	696	if (Collection.class.isAssignableFrom(field.getType()))
	697	{
	698	for (Field subField : subFields)
	699	{
	700	Excel subExcel = subField.getAnnotation(Excel.class);
	701	this.createHeadCell(subExcel, row, column++);
	702	}
	703	}
	704	else
	705	{
	706	this.createHeadCell(excel, row, column++);
	707	}
	708	}
	709	if (Type.EXPORT.equals(type))
	710	{
	711	fillExcelData(index, row);
	712	addStatisticsRow();
	713	}
	714	}
	715	}
	716
	717	/**
	718	* 填充excel数据
	719	*
	720	* @param index 序号
	721	* @param row 单元格行
	722	*/
	723	@SuppressWarnings("unchecked")
	724	public void fillExcelData(int index, Row row)
	725	{
	726	int startNo = index * sheetSize;
	727	int endNo = Math.min(startNo + sheetSize, list.size());
	728	int rowNo = (1 + rownum) - startNo;
	729	for (int i = startNo; i < endNo; i++)
	730	{
	731	rowNo = isSubList() ? (i > 1 ? rowNo + 1 : rowNo + i) : i + 1 + rownum - startNo;
	732	row = sheet.createRow(rowNo);
	733	// 得到导出对象.
	734	T vo = (T) list.get(i);
	735	Collection<?> subList = null;
	736	if (isSubList())
	737	{
	738	if (isSubListValue(vo))
	739	{
	740	subList = getListCellValue(vo);
	741	subMergedLastRowNum = subMergedLastRowNum + subList.size();
	742	}
	743	else
	744	{
	745	subMergedFirstRowNum++;
	746	subMergedLastRowNum++;
	747	}
	748	}
	749	int column = 0;
	750	for (Object[] os : fields)
	751	{
	752	Field field = (Field) os[0];
	753	Excel excel = (Excel) os[1];
	754	if (Collection.class.isAssignableFrom(field.getType()) && StringUtils.isNotNull(subList))
	755	{
	756	boolean subFirst = false;
	757	for (Object obj : subList)
	758	{
	759	if (subFirst)
	760	{
	761	rowNo++;
	762	row = sheet.createRow(rowNo);
	763	}
	764	List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(obj.getClass(), Excel.class);
	765	int subIndex = 0;
	766	for (Field subField : subFields)
	767	{
	768	if (subField.isAnnotationPresent(Excel.class))
	769	{
	770	subField.setAccessible(true);
	771	Excel attr = subField.getAnnotation(Excel.class);
	772	this.addCell(attr, row, (T) obj, subField, column + subIndex);
	773	}
	774	subIndex++;
	775	}
	776	subFirst = true;
	777	}
	778	this.subMergedFirstRowNum = this.subMergedFirstRowNum + subList.size();
	779	}
	780	else
	781	{
	782	this.addCell(excel, row, vo, field, column++);
	783	}
	784	}
	785	}
	786	}
	787
	788	/**
	789	* 创建表格样式
	790	*
	791	* @param wb 工作薄对象
	792	* @return 样式列表
	793	*/
	794	private Map<String, CellStyle> createStyles(Workbook wb)
	795	{
	796	// 写入各条记录,每条记录对应excel表中的一行
	797	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
	798	CellStyle style = wb.createCellStyle();
	799	style.setAlignment(HorizontalAlignment.CENTER);
	800	style.setVerticalAlignment(VerticalAlignment.CENTER);
	801	Font titleFont = wb.createFont();
	802	titleFont.setFontName("Arial");
	803	titleFont.setFontHeightInPoints((short) 16);
	804	titleFont.setBold(true);
	805	style.setFont(titleFont);
	806	DataFormat dataFormat = wb.createDataFormat();
	807	style.setDataFormat(dataFormat.getFormat("@"));
	808	styles.put("title", style);
	809
	810	style = wb.createCellStyle();
	811	style.setAlignment(HorizontalAlignment.CENTER);
	812	style.setVerticalAlignment(VerticalAlignment.CENTER);
	813	style.setBorderRight(BorderStyle.THIN);
	814	style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	815	style.setBorderLeft(BorderStyle.THIN);
	816	style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	817	style.setBorderTop(BorderStyle.THIN);
	818	style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	819	style.setBorderBottom(BorderStyle.THIN);
	820	style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	821	Font dataFont = wb.createFont();
	822	dataFont.setFontName("Arial");
	823	dataFont.setFontHeightInPoints((short) 10);
	824	style.setFont(dataFont);
	825	styles.put("data", style);
	826
	827	style = wb.createCellStyle();
	828	style.setAlignment(HorizontalAlignment.CENTER);
	829	style.setVerticalAlignment(VerticalAlignment.CENTER);
	830	Font totalFont = wb.createFont();
	831	totalFont.setFontName("Arial");
	832	totalFont.setFontHeightInPoints((short) 10);
	833	style.setFont(totalFont);
	834	styles.put("total", style);
	835
	836	styles.putAll(annotationHeaderStyles(wb, styles));
	837
	838	styles.putAll(annotationDataStyles(wb));
	839
	840	return styles;
	841	}
	842
	843	/**
	844	* 根据Excel注解创建表格头样式
	845	*
	846	* @param wb 工作薄对象
	847	* @return 自定义样式列表
	848	*/
	849	private Map<String, CellStyle> annotationHeaderStyles(Workbook wb, Map<String, CellStyle> styles)
	850	{
	851	Map<String, CellStyle> headerStyles = new HashMap<String, CellStyle>();
	852	for (Object[] os : fields)
	853	{
	854	Excel excel = (Excel) os[1];
	855	String key = StringUtils.format("header_{}_{}", excel.headerColor(), excel.headerBackgroundColor());
	856	if (!headerStyles.containsKey(key))
	857	{
	858	CellStyle style = wb.createCellStyle();
	859	style.cloneStyleFrom(styles.get("data"));
	860	style.setAlignment(HorizontalAlignment.CENTER);
	861	style.setVerticalAlignment(VerticalAlignment.CENTER);
	862	style.setFillForegroundColor(excel.headerBackgroundColor().index);
	863	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	864	Font headerFont = wb.createFont();
	865	headerFont.setFontName("Arial");
	866	headerFont.setFontHeightInPoints((short) 10);
	867	headerFont.setBold(true);
	868	headerFont.setColor(excel.headerColor().index);
	869	style.setFont(headerFont);
	870	// 设置表格头单元格文本形式
	871	DataFormat dataFormat = wb.createDataFormat();
	872	style.setDataFormat(dataFormat.getFormat("@"));
	873	headerStyles.put(key, style);
	874	}
	875	}
	876	return headerStyles;
	877	}
	878
	879	/**
	880	* 根据Excel注解创建表格列样式
	881	*
	882	* @param wb 工作薄对象
	883	* @return 自定义样式列表
	884	*/
	885	private Map<String, CellStyle> annotationDataStyles(Workbook wb)
	886	{
	887	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
	888	for (Object[] os : fields)
	889	{
	890	Field field = (Field) os[0];
	891	Excel excel = (Excel) os[1];
	892	if (Collection.class.isAssignableFrom(field.getType()))
	893	{
	894	ParameterizedType pt = (ParameterizedType) field.getGenericType();
	895	Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
	896	List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
	897	for (Field subField : subFields)
	898	{
	899	Excel subExcel = subField.getAnnotation(Excel.class);
	900	annotationDataStyles(styles, subField, subExcel);
	901	}
	902	}
	903	else
	904	{
	905	annotationDataStyles(styles, field, excel);
	906	}
	907	}
	908	return styles;
	909	}
	910
	911	/**
	912	* 根据Excel注解创建表格列样式
	913	*
	914	* @param styles 自定义样式列表
	915	* @param field 属性列信息
	916	* @param excel 注解信息
	917	*/
	918	public void annotationDataStyles(Map<String, CellStyle> styles, Field field, Excel excel)
	919	{
	920	String key = StringUtils.format("data_{}_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor(), excel.cellType());
	921	if (!styles.containsKey(key))
	922	{
	923	CellStyle style = wb.createCellStyle();
	924	style.setAlignment(excel.align());
	925	style.setVerticalAlignment(VerticalAlignment.CENTER);
	926	style.setBorderRight(BorderStyle.THIN);
	927	style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	928	style.setBorderLeft(BorderStyle.THIN);
	929	style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	930	style.setBorderTop(BorderStyle.THIN);
	931	style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	932	style.setBorderBottom(BorderStyle.THIN);
	933	style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
	934	style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	935	style.setFillForegroundColor(excel.backgroundColor().getIndex());
	936	Font dataFont = wb.createFont();
	937	dataFont.setFontName("Arial");
	938	dataFont.setFontHeightInPoints((short) 10);
	939	dataFont.setColor(excel.color().index);
	940	style.setFont(dataFont);
	941	if (ColumnType.TEXT == excel.cellType())
	942	{
	943	DataFormat dataFormat = wb.createDataFormat();
	944	style.setDataFormat(dataFormat.getFormat("@"));
	945	}
	946	styles.put(key, style);
	947	}
	948	}
	949
	950	/**
	951	* 创建单元格
	952	*/
	953	public Cell createHeadCell(Excel attr, Row row, int column)
	954	{
	955	// 创建列
	956	Cell cell = row.createCell(column);
	957	// 写入列信息
	958	cell.setCellValue(attr.name());
	959	setDataValidation(attr, row, column);
	960	cell.setCellStyle(styles.get(StringUtils.format("header_{}_{}", attr.headerColor(), attr.headerBackgroundColor())));
	961	if (isSubList())
	962	{
	963	// 填充默认样式，防止合并单元格样式失效
	964	sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));
	965	if (attr.needMerge())
	966	{
	967	sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));
	968	}
	969	}
	970	return cell;
	971	}
	972
	973	/**
	974	* 设置单元格信息
	975	*
	976	* @param value 单元格值
	977	* @param attr 注解相关
	978	* @param cell 单元格信息
	979	*/
	980	public void setCellVo(Object value, Excel attr, Cell cell)
	981	{
	982	if (ColumnType.STRING == attr.cellType() \|\| ColumnType.TEXT == attr.cellType())
	983	{
	984	String cellValue = Convert.toStr(value);
	985	// 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。
	986	if (StringUtils.startsWithAny(cellValue, FORMULA_STR))
	987	{
	988	cellValue = RegExUtils.replaceFirst(cellValue, FORMULA_REGEX_STR, "\t$0");
	989	}
	990	if (value instanceof Collection && StringUtils.equals("[]", cellValue))
	991	{
	992	cellValue = StringUtils.EMPTY;
	993	}
	994	cell.setCellValue(StringUtils.isNull(cellValue) ? attr.defaultValue() : cellValue + attr.suffix());
	995	}
	996	else if (ColumnType.NUMERIC == attr.cellType())
	997	{
	998	if (StringUtils.isNotNull(value))
	999	{
	1000	cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
	1001	}
	1002	}
	1003	else if (ColumnType.IMAGE == attr.cellType())
	1004	{
	1005	ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);
	1006	String imagePath = Convert.toStr(value);
	1007	if (StringUtils.isNotEmpty(imagePath))
	1008	{
	1009	byte[] data = ImageUtils.getImage(imagePath);
	1010	getDrawingPatriarch(cell.getSheet()).createPicture(anchor,
	1011	cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));
	1012	}
	1013	}
	1014	}
	1015
	1016	/**
	1017	* 获取画布
	1018	*/
	1019	public static Drawing<?> getDrawingPatriarch(Sheet sheet)
	1020	{
	1021	if (sheet.getDrawingPatriarch() == null)
	1022	{
	1023	sheet.createDrawingPatriarch();
	1024	}
	1025	return sheet.getDrawingPatriarch();
	1026	}
	1027
	1028	/**
	1029	* 获取图片类型,设置图片插入类型
	1030	*/
	1031	public int getImageType(byte[] value)
	1032	{
	1033	String type = FileTypeUtils.getFileExtendName(value);
	1034	if ("JPG".equalsIgnoreCase(type))
	1035	{
	1036	return Workbook.PICTURE_TYPE_JPEG;
	1037	}
	1038	else if ("PNG".equalsIgnoreCase(type))
	1039	{
	1040	return Workbook.PICTURE_TYPE_PNG;
	1041	}
	1042	return Workbook.PICTURE_TYPE_JPEG;
	1043	}
	1044
	1045	/**
	1046	* 创建表格样式
	1047	*/
	1048	public void setDataValidation(Excel attr, Row row, int column)
	1049	{
	1050	if (attr.name().indexOf("注：") >= 0)
	1051	{
	1052	sheet.setColumnWidth(column, 6000);
	1053	}
	1054	else
	1055	{
	1056	// 设置列宽
	1057	sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
	1058	}
	1059	if (StringUtils.isNotEmpty(attr.prompt()) \|\| attr.combo().length > 0 \|\| attr.comboReadDict())
	1060	{
	1061	String[] comboArray = attr.combo();
	1062	if (attr.comboReadDict())
	1063	{
	1064	if (!sysDictMap.containsKey("combo_" + attr.dictType()))
	1065	{
	1066	String labels = DictUtils.getDictLabels(attr.dictType());
	1067	sysDictMap.put("combo_" + attr.dictType(), labels);
	1068	}
	1069	String val = sysDictMap.get("combo_" + attr.dictType());
	1070	comboArray = StringUtils.split(val, DictUtils.SEPARATOR);
	1071	}
	1072	if (comboArray.length > 15 \|\| StringUtils.join(comboArray).length() > 255)
	1073	{
	1074	// 如果下拉数大于15或字符串长度大于255，则使用一个新sheet存储，避免生成的模板下拉值获取不到
	1075	setXSSFValidationWithHidden(sheet, comboArray, attr.prompt(), 1, 100, column, column);
	1076	}
	1077	else
	1078	{
	1079	// 提示信息或只能选择不能输入的列内容.
	1080	setPromptOrValidation(sheet, comboArray, attr.prompt(), 1, 100, column, column);
	1081	}
	1082	}
	1083	}
	1084
	1085	/**
	1086	* 添加单元格
	1087	*/
	1088	public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
	1089	{
	1090	Cell cell = null;
	1091	try
	1092	{
	1093	// 设置行高
	1094	row.setHeight(maxHeight);
	1095	// 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
	1096	if (attr.isExport())
	1097	{
	1098	// 创建cell
	1099	cell = row.createCell(column);
	1100	if (isSubListValue(vo) && getListCellValue(vo).size() > 1 && attr.needMerge())
	1101	{
	1102	CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);
	1103	sheet.addMergedRegion(cellAddress);
	1104	}
	1105	cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));
	1106
	1107	// 用于读取对象中的属性
	1108	Object value = getTargetValue(vo, field, attr);
	1109	String dateFormat = attr.dateFormat();
	1110	String readConverterExp = attr.readConverterExp();
	1111	String separator = attr.separator();
	1112	String dictType = attr.dictType();
	1113	if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
	1114	{
	1115	cell.setCellValue(parseDateToStr(dateFormat, value));
	1116	}
	1117	else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
	1118	{
	1119	cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
	1120	}
	1121	else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))
	1122	{
	1123	if (!sysDictMap.containsKey(dictType + value))
	1124	{
	1125	String lable = convertDictByExp(Convert.toStr(value), dictType, separator);
	1126	sysDictMap.put(dictType + value, lable);
	1127	}
	1128	cell.setCellValue(sysDictMap.get(dictType + value));
	1129	}
	1130	else if (value instanceof BigDecimal && -1 != attr.scale())
	1131	{
	1132	cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).doubleValue());
	1133	}
	1134	else if (!attr.handler().equals(ExcelHandlerAdapter.class))
	1135	{
	1136	cell.setCellValue(dataFormatHandlerAdapter(value, attr, cell));
	1137	}
	1138	else
	1139	{
	1140	// 设置列类型
	1141	setCellVo(value, attr, cell);
	1142	}
	1143	addStatisticsData(column, Convert.toStr(value), attr);
	1144	}
	1145	}
	1146	catch (Exception e)
	1147	{
	1148	log.error("导出Excel失败{}", e);
	1149	}
	1150	return cell;
	1151	}
	1152
	1153	/**
	1154	* 设置 POI XSSFSheet 单元格提示或选择框
	1155	*
	1156	* @param sheet 表单
	1157	* @param textlist 下拉框显示的内容
	1158	* @param promptContent 提示内容
	1159	* @param firstRow 开始行
	1160	* @param endRow 结束行
	1161	* @param firstCol 开始列
	1162	* @param endCol 结束列
	1163	*/
	1164	public void setPromptOrValidation(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow,
	1165	int firstCol, int endCol)
	1166	{
	1167	DataValidationHelper helper = sheet.getDataValidationHelper();
	1168	DataValidationConstraint constraint = textlist.length > 0 ? helper.createExplicitListConstraint(textlist) : helper.createCustomConstraint("DD1");
	1169	CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
	1170	DataValidation dataValidation = helper.createValidation(constraint, regions);
	1171	if (StringUtils.isNotEmpty(promptContent))
	1172	{
	1173	// 如果设置了提示信息则鼠标放上去提示
	1174	dataValidation.createPromptBox("", promptContent);
	1175	dataValidation.setShowPromptBox(true);
	1176	}
	1177	// 处理Excel兼容性问题
	1178	if (dataValidation instanceof XSSFDataValidation)
	1179	{
	1180	dataValidation.setSuppressDropDownArrow(true);
	1181	dataValidation.setShowErrorBox(true);
	1182	}
	1183	else
	1184	{
	1185	dataValidation.setSuppressDropDownArrow(false);
	1186	}
	1187	sheet.addValidationData(dataValidation);
	1188	}
	1189
	1190	/**
	1191	* 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.
	1192	*
	1193	* @param sheet 要设置的sheet.
	1194	* @param textlist 下拉框显示的内容
	1195	* @param promptContent 提示内容
	1196	* @param firstRow 开始行
	1197	* @param endRow 结束行
	1198	* @param firstCol 开始列
	1199	* @param endCol 结束列
	1200	*/
	1201	public void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)
	1202	{
	1203	String hideSheetName = "combo_" + firstCol + "_" + endCol;
	1204	Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储下拉菜单数据
	1205	for (int i = 0; i < textlist.length; i++)
	1206	{
	1207	hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);
	1208	}
	1209	// 创建名称，可被其他单元格引用
	1210	Name name = wb.createName();
	1211	name.setNameName(hideSheetName + "_data");
	1212	name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);
	1213	DataValidationHelper helper = sheet.getDataValidationHelper();
	1214	// 加载下拉列表内容
	1215	DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");
	1216	// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
	1217	CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
	1218	// 数据有效性对象
	1219	DataValidation dataValidation = helper.createValidation(constraint, regions);
	1220	if (StringUtils.isNotEmpty(promptContent))
	1221	{
	1222	// 如果设置了提示信息则鼠标放上去提示
	1223	dataValidation.createPromptBox("", promptContent);
	1224	dataValidation.setShowPromptBox(true);
	1225	}
	1226	// 处理Excel兼容性问题
	1227	if (dataValidation instanceof XSSFDataValidation)
	1228	{
	1229	dataValidation.setSuppressDropDownArrow(true);
	1230	dataValidation.setShowErrorBox(true);
	1231	}
	1232	else
	1233	{
	1234	dataValidation.setSuppressDropDownArrow(false);
	1235	}
	1236
	1237	sheet.addValidationData(dataValidation);
	1238	// 设置hiddenSheet隐藏
	1239	wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
	1240	}
	1241
	1242	/**
	1243	* 解析导出值 0=男,1=女,2=未知
	1244	*
	1245	* @param propertyValue 参数值
	1246	* @param converterExp 翻译注解
	1247	* @param separator 分隔符
	1248	* @return 解析后值
	1249	*/
	1250	public static String convertByExp(String propertyValue, String converterExp, String separator)
	1251	{
	1252	StringBuilder propertyString = new StringBuilder();
	1253	String[] convertSource = converterExp.split(",");
	1254	for (String item : convertSource)
	1255	{
	1256	String[] itemArray = item.split("=");
	1257	if (StringUtils.containsAny(propertyValue, separator))
	1258	{
	1259	for (String value : propertyValue.split(separator))
	1260	{
	1261	if (itemArray[0].equals(value))
	1262	{
	1263	propertyString.append(itemArray[1] + separator);
	1264	break;
	1265	}
	1266	}
	1267	}
	1268	else
	1269	{
	1270	if (itemArray[0].equals(propertyValue))
	1271	{
	1272	return itemArray[1];
	1273	}
	1274	}
	1275	}
	1276	return StringUtils.stripEnd(propertyString.toString(), separator);
	1277	}
	1278
	1279	/**
	1280	* 反向解析值男=0,女=1,未知=2
	1281	*
	1282	* @param propertyValue 参数值
	1283	* @param converterExp 翻译注解
	1284	* @param separator 分隔符
	1285	* @return 解析后值
	1286	*/
	1287	public static String reverseByExp(String propertyValue, String converterExp, String separator)
	1288	{
	1289	StringBuilder propertyString = new StringBuilder();
	1290	String[] convertSource = converterExp.split(",");
	1291	for (String item : convertSource)
	1292	{
	1293	String[] itemArray = item.split("=");
	1294	if (StringUtils.containsAny(propertyValue, separator))
	1295	{
	1296	for (String value : propertyValue.split(separator))
	1297	{
	1298	if (itemArray[1].equals(value))
	1299	{
	1300	propertyString.append(itemArray[0] + separator);
	1301	break;
	1302	}
	1303	}
	1304	}
	1305	else
	1306	{
	1307	if (itemArray[1].equals(propertyValue))
	1308	{
	1309	return itemArray[0];
	1310	}
	1311	}
	1312	}
	1313	return StringUtils.stripEnd(propertyString.toString(), separator);
	1314	}
	1315
	1316	/**
	1317	* 解析字典值
	1318	*
	1319	* @param dictValue 字典值
	1320	* @param dictType 字典类型
	1321	* @param separator 分隔符
	1322	* @return 字典标签
	1323	*/
	1324	public static String convertDictByExp(String dictValue, String dictType, String separator)
	1325	{
	1326	return DictUtils.getDictLabel(dictType, dictValue, separator);
	1327	}
	1328
	1329	/**
	1330	* 反向解析值字典值
	1331	*
	1332	* @param dictLabel 字典标签
	1333	* @param dictType 字典类型
	1334	* @param separator 分隔符
	1335	* @return 字典值
	1336	*/
	1337	public static String reverseDictByExp(String dictLabel, String dictType, String separator)
	1338	{
	1339	return DictUtils.getDictValue(dictType, dictLabel, separator);
	1340	}
	1341
	1342	/**
	1343	* 数据处理器
	1344	*
	1345	* @param value 数据值
	1346	* @param excel 数据注解
	1347	* @return
	1348	*/
	1349	public String dataFormatHandlerAdapter(Object value, Excel excel, Cell cell)
	1350	{
	1351	try
	1352	{
	1353	Object instance = excel.handler().newInstance();
	1354	Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class, Cell.class, Workbook.class });
	1355	value = formatMethod.invoke(instance, value, excel.args(), cell, this.wb);
	1356	}
	1357	catch (Exception e)
	1358	{
	1359	log.error("不能格式化数据 " + excel.handler(), e.getMessage());
	1360	}
	1361	return Convert.toStr(value);
	1362	}
	1363
	1364	/**
	1365	* 合计统计信息
	1366	*/
	1367	private void addStatisticsData(Integer index, String text, Excel entity)
	1368	{
	1369	if (entity != null && entity.isStatistics())
	1370	{
	1371	Double temp = 0D;
	1372	if (!statistics.containsKey(index))
	1373	{
	1374	statistics.put(index, temp);
	1375	}
	1376	try
	1377	{
	1378	temp = Double.valueOf(text);
	1379	}
	1380	catch (NumberFormatException e)
	1381	{
	1382	}
	1383	statistics.put(index, statistics.get(index) + temp);
	1384	}
	1385	}
	1386
	1387	/**
	1388	* 创建统计行
	1389	*/
	1390	public void addStatisticsRow()
	1391	{
	1392	if (statistics.size() > 0)
	1393	{
	1394	Row row = sheet.createRow(sheet.getLastRowNum() + 1);
	1395	Set<Integer> keys = statistics.keySet();
	1396	Cell cell = row.createCell(0);
	1397	cell.setCellStyle(styles.get("total"));
	1398	cell.setCellValue("合计");
	1399
	1400	for (Integer key : keys)
	1401	{
	1402	cell = row.createCell(key);
	1403	cell.setCellStyle(styles.get("total"));
	1404	cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
	1405	}
	1406	statistics.clear();
	1407	}
	1408	}
	1409
	1410	/**
	1411	* 编码文件名
	1412	*/
	1413	public String encodingFilename(String filename)
	1414	{
	1415	filename = UUID.randomUUID() + "_" + filename + ".xlsx";
	1416	return filename;
	1417	}
	1418
	1419	/**
	1420	* 获取下载路径
	1421	*
	1422	* @param filename 文件名称
	1423	*/
	1424	public String getAbsoluteFile(String filename)
	1425	{
	1426	String downloadPath = RuoYiConfig.getDownloadPath() + filename;
	1427	File desc = new File(downloadPath);
	1428	if (!desc.getParentFile().exists())
	1429	{
	1430	desc.getParentFile().mkdirs();
	1431	}
	1432	return downloadPath;
	1433	}
	1434
	1435	/**
	1436	* 获取bean中的属性值
	1437	*
	1438	* @param vo 实体对象
	1439	* @param field 字段
	1440	* @param excel 注解
	1441	* @return 最终的属性值
	1442	* @throws Exception
	1443	*/
	1444	private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
	1445	{
	1446	Object o = field.get(vo);
	1447	if (StringUtils.isNotEmpty(excel.targetAttr()))
	1448	{
	1449	String target = excel.targetAttr();
	1450	if (target.contains("."))
	1451	{
	1452	String[] targets = target.split("[.]");
	1453	for (String name : targets)
	1454	{
	1455	o = getValue(o, name);
	1456	}
	1457	}
	1458	else
	1459	{
	1460	o = getValue(o, target);
	1461	}
	1462	}
	1463	return o;
	1464	}
	1465
	1466	/**
	1467	* 以类的属性的get方法方法形式获取值
	1468	*
	1469	* @param o
	1470	* @param name
	1471	* @return value
	1472	* @throws Exception
	1473	*/
	1474	private Object getValue(Object o, String name) throws Exception
	1475	{
	1476	if (StringUtils.isNotNull(o) && StringUtils.isNotEmpty(name))
	1477	{
	1478	Class<?> clazz = o.getClass();
	1479	Field field = clazz.getDeclaredField(name);
	1480	field.setAccessible(true);
	1481	o = field.get(o);
	1482	}
	1483	return o;
	1484	}
	1485
	1486	/**
	1487	* 得到所有定义字段
	1488	*/
	1489	private void createExcelField()
	1490	{
	1491	this.fields = getFields();
	1492	this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
	1493	this.maxHeight = getRowHeight();
	1494	}
	1495
	1496	/**
	1497	* 获取字段注解信息
	1498	*/
	1499	public List<Object[]> getFields()
	1500	{
	1501	List<Object[]> fields = new ArrayList<Object[]>();
	1502	List<Field> tempFields = new ArrayList<>();
	1503	tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
	1504	tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
	1505	if (StringUtils.isNotEmpty(includeFields))
	1506	{
	1507	for (Field field : tempFields)
	1508	{
	1509	if (ArrayUtils.contains(this.includeFields, field.getName()) \|\| field.isAnnotationPresent(Excels.class))
	1510	{
	1511	addField(fields, field);
	1512	}
	1513	}
	1514	}
	1515	else if (StringUtils.isNotEmpty(excludeFields))
	1516	{
	1517	for (Field field : tempFields)
	1518	{
	1519	if (!ArrayUtils.contains(this.excludeFields, field.getName()))
	1520	{
	1521	addField(fields, field);
	1522	}
	1523	}
	1524	}
	1525	else
	1526	{
	1527	for (Field field : tempFields)
	1528	{
	1529	addField(fields, field);
	1530	}
	1531	}
	1532	return fields;
	1533	}
	1534
	1535	/**
	1536	* 添加字段信息
	1537	*/
	1538	public void addField(List<Object[]> fields, Field field)
	1539	{
	1540	// 单注解
	1541	if (field.isAnnotationPresent(Excel.class))
	1542	{
	1543	Excel attr = field.getAnnotation(Excel.class);
	1544	if (attr != null && (attr.type() == Type.ALL \|\| attr.type() == type))
	1545	{
	1546	field.setAccessible(true);
	1547	fields.add(new Object[] { field, attr });
	1548	}
	1549	if (Collection.class.isAssignableFrom(field.getType()))
	1550	{
	1551	subMethod = getSubMethod(field.getName(), clazz);
	1552	ParameterizedType pt = (ParameterizedType) field.getGenericType();
	1553	Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];
	1554	this.subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);
	1555	}
	1556	}
	1557
	1558	// 多注解
	1559	if (field.isAnnotationPresent(Excels.class))
	1560	{
	1561	Excels attrs = field.getAnnotation(Excels.class);
	1562	Excel[] excels = attrs.value();
	1563	for (Excel attr : excels)
	1564	{
	1565	if (StringUtils.isNotEmpty(includeFields))
	1566	{
	1567	if (ArrayUtils.contains(this.includeFields, field.getName() + "." + attr.targetAttr())
	1568	&& (attr != null && (attr.type() == Type.ALL \|\| attr.type() == type)))
	1569	{
	1570	field.setAccessible(true);
	1571	fields.add(new Object[] { field, attr });
	1572	}
	1573	}
	1574	else
	1575	{
	1576	if (!ArrayUtils.contains(this.excludeFields, field.getName() + "." + attr.targetAttr())
	1577	&& (attr != null && (attr.type() == Type.ALL \|\| attr.type() == type)))
	1578	{
	1579	field.setAccessible(true);
	1580	fields.add(new Object[] { field, attr });
	1581	}
	1582	}
	1583	}
	1584	}
	1585	}
	1586
	1587	/**
	1588	* 根据注解获取最大行高
	1589	*/
	1590	public short getRowHeight()
	1591	{
	1592	double maxHeight = 0;
	1593	for (Object[] os : this.fields)
	1594	{
	1595	Excel excel = (Excel) os[1];
	1596	maxHeight = Math.max(maxHeight, excel.height());
	1597	}
	1598	return (short) (maxHeight * 20);
	1599	}
	1600
	1601	/**
	1602	* 创建一个工作簿
	1603	*/
	1604	public void createWorkbook()
	1605	{
	1606	this.wb = new SXSSFWorkbook(500);
	1607	this.sheet = wb.createSheet();
	1608	wb.setSheetName(0, sheetName);
	1609	this.styles = createStyles(wb);
	1610	}
	1611
	1612	/**
	1613	* 创建工作表
	1614	*
	1615	* @param sheetNo sheet数量
	1616	* @param index 序号
	1617	*/
	1618	public void createSheet(int sheetNo, int index)
	1619	{
	1620	// 设置工作表的名称.
	1621	if (sheetNo > 1 && index > 0)
	1622	{
	1623	this.sheet = wb.createSheet();
	1624	this.createTitle();
	1625	wb.setSheetName(index, sheetName + index);
	1626	}
	1627	}
	1628
	1629	/**
	1630	* 获取单元格值
	1631	*
	1632	* @param row 获取的行
	1633	* @param column 获取单元格列号
	1634	* @return 单元格值
	1635	*/
	1636	public Object getCellValue(Row row, int column)
	1637	{
	1638	if (row == null)
	1639	{
	1640	return row;
	1641	}
	1642	Object val = "";
	1643	try
	1644	{
	1645	Cell cell = row.getCell(column);
	1646	if (StringUtils.isNotNull(cell))
	1647	{
	1648	if (cell.getCellType() == CellType.NUMERIC \|\| cell.getCellType() == CellType.FORMULA)
	1649	{
	1650	val = cell.getNumericCellValue();
	1651	if (DateUtil.isCellDateFormatted(cell))
	1652	{
	1653	val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
	1654	}
	1655	else
	1656	{
	1657	if ((Double) val % 1 != 0)
	1658	{
	1659	val = new BigDecimal(val.toString());
	1660	}
	1661	else
	1662	{
	1663	val = new DecimalFormat("0").format(val);
	1664	}
	1665	}
	1666	}
	1667	else if (cell.getCellType() == CellType.STRING)
	1668	{
	1669	val = cell.getStringCellValue();
	1670	}
	1671	else if (cell.getCellType() == CellType.BOOLEAN)
	1672	{
	1673	val = cell.getBooleanCellValue();
	1674	}
	1675	else if (cell.getCellType() == CellType.ERROR)
	1676	{
	1677	val = cell.getErrorCellValue();
	1678	}
	1679
	1680	}
	1681	}
	1682	catch (Exception e)
	1683	{
	1684	return val;
	1685	}
	1686	return val;
	1687	}
	1688
	1689	/**
	1690	* 判断是否是空行
	1691	*
	1692	* @param row 判断的行
	1693	* @return
	1694	*/
	1695	private boolean isRowEmpty(Row row)
	1696	{
	1697	if (row == null)
	1698	{
	1699	return true;
	1700	}
	1701	for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++)
	1702	{
	1703	Cell cell = row.getCell(i);
	1704	if (cell != null && cell.getCellType() != CellType.BLANK)
	1705	{
	1706	return false;
	1707	}
	1708	}
	1709	return true;
	1710	}
	1711
	1712	/**
	1713	* 获取Excel2003图片
	1714	*
	1715	* @param sheet 当前sheet对象
	1716	* @param workbook 工作簿对象
	1717	* @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
	1718	*/
	1719	public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)
	1720	{
	1721	Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
	1722	List<HSSFPictureData> pictures = workbook.getAllPictures();
	1723	if (!pictures.isEmpty())
	1724	{
	1725	for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())
	1726	{
	1727	HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
	1728	if (shape instanceof HSSFPicture)
	1729	{
	1730	HSSFPicture pic = (HSSFPicture) shape;
	1731	int pictureIndex = pic.getPictureIndex() - 1;
	1732	HSSFPictureData picData = pictures.get(pictureIndex);
	1733	String picIndex = anchor.getRow1() + "_" + anchor.getCol1();
	1734	sheetIndexPicMap.put(picIndex, picData);
	1735	}
	1736	}
	1737	return sheetIndexPicMap;
	1738	}
	1739	else
	1740	{
	1741	return sheetIndexPicMap;
	1742	}
	1743	}
	1744
	1745	/**
	1746	* 获取Excel2007图片
	1747	*
	1748	* @param sheet 当前sheet对象
	1749	* @param workbook 工作簿对象
	1750	* @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
	1751	*/
	1752	public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)
	1753	{
	1754	Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
	1755	for (POIXMLDocumentPart dr : sheet.getRelations())
	1756	{
	1757	if (dr instanceof XSSFDrawing)
	1758	{
	1759	XSSFDrawing drawing = (XSSFDrawing) dr;
	1760	List<XSSFShape> shapes = drawing.getShapes();
	1761	for (XSSFShape shape : shapes)
	1762	{
	1763	if (shape instanceof XSSFPicture)
	1764	{
	1765	XSSFPicture pic = (XSSFPicture) shape;
	1766	XSSFClientAnchor anchor = pic.getPreferredSize();
	1767	CTMarker ctMarker = anchor.getFrom();
	1768	String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
	1769	sheetIndexPicMap.put(picIndex, pic.getPictureData());
	1770	}
	1771	}
	1772	}
	1773	}
	1774	return sheetIndexPicMap;
	1775	}
	1776
	1777	/**
	1778	* 格式化不同类型的日期对象
	1779	*
	1780	* @param dateFormat 日期格式
	1781	* @param val 被格式化的日期对象
	1782	* @return 格式化后的日期字符
	1783	*/
	1784	public String parseDateToStr(String dateFormat, Object val)
	1785	{
	1786	if (val == null)
	1787	{
	1788	return "";
	1789	}
	1790	String str;
	1791	if (val instanceof Date)
	1792	{
	1793	str = DateUtils.parseDateToStr(dateFormat, (Date) val);
	1794	}
	1795	else if (val instanceof LocalDateTime)
	1796	{
	1797	str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDateTime) val));
	1798	}
	1799	else if (val instanceof LocalDate)
	1800	{
	1801	str = DateUtils.parseDateToStr(dateFormat, DateUtils.toDate((LocalDate) val));
	1802	}
	1803	else
	1804	{
	1805	str = val.toString();
	1806	}
	1807	return str;
	1808	}
	1809
	1810	/**
	1811	* 是否有对象的子列表
	1812	*/
	1813	public boolean isSubList()
	1814	{
	1815	return StringUtils.isNotNull(subFields) && subFields.size() > 0;
	1816	}
	1817
	1818	/**
	1819	* 是否有对象的子列表，集合不为空
	1820	*/
	1821	public boolean isSubListValue(T vo)
	1822	{
	1823	return StringUtils.isNotNull(subFields) && subFields.size() > 0 && StringUtils.isNotNull(getListCellValue(vo)) && getListCellValue(vo).size() > 0;
	1824	}
	1825
	1826	/**
	1827	* 获取集合的值
	1828	*/
	1829	public Collection<?> getListCellValue(Object obj)
	1830	{
	1831	Object value;
	1832	try
	1833	{
	1834	value = subMethod.invoke(obj, new Object[] {});
	1835	}
	1836	catch (Exception e)
	1837	{
	1838	return new ArrayList<Object>();
	1839	}
	1840	return (Collection<?>) value;
	1841	}
	1842
	1843	/**
	1844	* 获取对象的子列表方法
	1845	*
	1846	* @param name 名称
	1847	* @param pojoClass 类对象
	1848	* @return 子列表方法
	1849	*/
	1850	public Method getSubMethod(String name, Class<?> pojoClass)
	1851	{
	1852	StringBuffer getMethodName = new StringBuffer("get");
	1853	getMethodName.append(name.substring(0, 1).toUpperCase());
	1854	getMethodName.append(name.substring(1));
	1855	Method method = null;
	1856	try
	1857	{
	1858	method = pojoClass.getMethod(getMethodName.toString(), new Class[] {});
	1859	}
	1860	catch (Exception e)
	1861	{
	1862	log.error("获取对象异常{}", e.getMessage());
	1863	}
	1864	return method;
	1865	}
	1866	}