Excel工具类

最新推荐文章于 2022-08-01 18:04:53 发布

小丁丐

最新推荐文章于 2022-08-01 18:04:53 发布

阅读量266

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/DJYDFT2831djydft/article/details/112372911

版权

java 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

1、依赖：

<dependency>
    <groupId>com.github.crab2died</groupId>
    <artifactId>Excel4J</artifactId>
    <version>3.0.0-Alpha</version>
</dependency>

2、工具类：

package com.zhangmen.universe.porsche.msg.market.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ReflectionUtils;

import com.alibaba.fastjson.JSON;
import com.zhangmen.universe.porsche.msg.market.common.BizException;
import com.zhangmen.universe.porsche.msg.market.module.executorImport.annotation.ExcelField;

/**
 * author: yang.ke
 * Description: Excel工具类
 */
public final class ExcelUtils {

	public static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);

	private static boolean excel2003 = false;

	private ExcelUtils() {
	}

	/**
	 * 写入Excel（导入模板）
	 *
	 * @param dataList  源数据集合
	 * @param excelFile Excel文件
	 * @param <T>
	 * @return
	 */
	public static <T> File writeTemple(List<T> dataList, File excelFile) {
		if (CollectionUtils.isEmpty(dataList)) {
			return excelFile;
		}
		try {
			List<HeaderField> headerFields = resolveImportAnnotions(dataList.get(0));
			return write(dataList, headerFields, excelFile);
		} catch (IllegalAccessException | IOException e) {
			log.error(e.getMessage(), e);
			return excelFile;
		}

	}


	/**
	 * 写入Excel
	 *
	 * @param dataList  源数据集合
	 * @param excelFile Excel文件
	 * @param <T>
	 * @return
	 */
	public static <T> File write(List<T> dataList, File excelFile) {
		if (CollectionUtils.isEmpty(dataList)) {
			return excelFile;
		}
		try {
			log.info("write dataOne:{}", JSON.toJSONString(dataList.get(0)));
			List<HeaderField> headerFields = resolveAnnotions(dataList.get(0));
			log.info("write headerFields:{}", JSON.toJSONString(headerFields));
			return write(dataList, headerFields, excelFile);
		} catch (IllegalAccessException | IOException e) {
			log.error(e.getMessage(), e);
			return excelFile;
		}

	}

	/**
	 * 写入Excel
	 *
	 * @param dataList     源数据集合
	 * @param headerFields Excel头部，也可以用注解 {@link ExcelField}
	 * @param excelFile    生成的Excel文件
	 * @param <T>
	 * @return
	 * @throws IOException
	 */
	public static <T> File write(List<T> dataList, List<HeaderField> headerFields, File excelFile) throws IOException {
		excelFile = createAsNeeded(excelFile);
		excel2003 = excelFile.getName().endsWith(".xls");
		Workbook workbook;
		if (excel2003) {
			workbook = new HSSFWorkbook();
		} else {
			workbook = new XSSFWorkbook();
		}
		writeContent(createSheet(workbook), dataList, headerFields);
		workbook2File(workbook, excelFile);
		return excelFile;
	}

	/**
	 * 
	* @Title: importExcel
	* @Description: 导入excel
	* @param includeHeader:实际数据是否包含首行  
	* @param clazz
	* @param is
	* @param checkHeader:是否检查头部信息
	* @param headerArr:头部信息
	* @return
	* @throws Exception
	* @throws
	 */
	public static <T> List<T> importExcel(boolean includeHeader, Class<T> clazz, InputStream is, boolean checkHeader,
			String[] headerArr) throws Exception {
		List<T> resultList = new ArrayList<>();
		XSSFWorkbook wb = new XSSFWorkbook(is);
		XSSFSheet sheet = wb.getSheetAt(0);
		List<Field> fields = getExcelField(clazz, null);
		//总行数
		int totalRows = sheet.getLastRowNum();
		//首行
		int startRow = includeHeader ? 0 : 1;
		for (int i = startRow; i <= totalRows; i++) {
			XSSFRow row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			if (checkHeader && i == 0) {
				if (headerArr == null || headerArr.length == 0) {
					throw new BizException("头部信息不合法");
				}
				// 校验头部信息
				for (int j = 0; j < headerArr.length; j++) {
					XSSFCell cell = row.getCell(j);
					if (!StringUtils.equals(getCellValue(cell), headerArr[j])) {
						throw new BizException("导入模板不合法");
					}
				}
				continue;
			}
			T obj = clazz.newInstance();
			int col = 0;
			for (Field field : fields) {
				XSSFCell cell = row.getCell(col++);
				setObjField(obj, field, cell);
			}
			
			resultList.add(obj);
		}
		return resultList;
	}

	public static <T> void setObjField(T obj, Field field, XSSFCell cell) throws InvocationTargetException, IllegalAccessException {
		String cellValue = getCellValue(cell);
		Object fieldValue = getFieldValue(field, cellValue);
		BeanUtils.setProperty(obj, field.getName(), fieldValue);
	}

	public static <T> void setObjStringField(T obj, Field field, XSSFCell cell) throws InvocationTargetException, IllegalAccessException {
		cell.setCellType(CellType.STRING);
		String cellValue = cell.getStringCellValue();
		Object fieldValue = getFieldValue(field, cellValue);
		BeanUtils.setProperty(obj, field.getName(), fieldValue);
	}

	private static Sheet createSheet(Workbook workbook) {
		Sheet sheet = workbook.createSheet();
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);
		return sheet;
	}

	/**
	 * write content.
	 *
	 * @param sheet
	 * @param datas
	 * @param headerFields
	 * @param <T>
	 */
	private static <T> void writeContent(Sheet sheet, List<T> datas, List<HeaderField> headerFields) {
		if (CollectionUtils.isEmpty(datas)) {
			return;
		}
		CellStyle headStyle = ExcelStyle.getHeadStyle(sheet.getWorkbook());

		AtomicInteger currentRow = new AtomicInteger(0);
		writeHeader(sheet, currentRow, headerFields, headStyle);
		writeBody(sheet, datas, currentRow, headerFields);
	}

	/**
	 * write excel body.
	 *
	 * @param sheet
	 * @param dataList
	 * @param currentRow
	 * @param headerFields
	 * @param <T>
	 */
	private static <T> void writeBody(Sheet sheet, List<T> dataList, AtomicInteger currentRow, List<HeaderField> headerFields) {
		if (CollectionUtils.isEmpty(dataList)) {
			return;
		}
		Iterator<T> it = dataList.iterator();
		Row row;
		while (it.hasNext()) {
			T t = it.next();
			CellStyle bodyStyle = getCellStyle(sheet, t);
			row = sheet.createRow(currentRow.getAndIncrement());
			int i = 0;
			for (HeaderField headerField : headerFields) {
				Class<?> clazz = t.getClass();
				try {
					Field field = clazz.getDeclaredField(headerField.getFieldName());
					if (!field.isAccessible()) {
						field.setAccessible(true);
					}
					Cell cell = row.createCell(i++);
					cell.setCellStyle(bodyStyle);
					cell.setCellValue(convertValue(field, t));
				} catch (NoSuchFieldException | IllegalAccessException e) {
					log.error(e.getMessage(), e);
				}
			}
		}
	}

	private static <T> CellStyle getCellStyle(Sheet sheet, T t) {
		try {
			Class<?> clazz = t.getClass().getSuperclass();
			if (clazz == Object.class) {
				return ExcelStyle.getBodyStyle(sheet.getWorkbook());
			}
			Field field = clazz.getDeclaredField("fontColor");
			ReflectionUtils.makeAccessible(field);
			ExcelField.FontColor fontColor = (ExcelField.FontColor)field.get(t);
			switch (fontColor) {
				case RED:
					return ExcelStyle.getBodyStyleRed(sheet.getWorkbook());
				case GREEN:
					return ExcelStyle.getBodyStyleGreen(sheet.getWorkbook());
				default:
					return ExcelStyle.getBodyStyle(sheet.getWorkbook());
			}
		} catch (Exception e) {
			return ExcelStyle.getBodyStyle(sheet.getWorkbook());
		}
	}

	/**
	 * format field value. and date will be format
	 *
	 * @param field
	 * @param obj
	 * @return
	 * @throws IllegalAccessException
	 */
	private static String convertValue(Field field, Object obj) throws IllegalAccessException {
		Class type = field.getType();
		Object value = field.get(obj);
		if (type == Date.class) {
			return value == null ? setDefault(field) : DateFormatUtils.format((Date) value, "yyyy-MM-dd HH:mm:ss");
		} else {
			return value == null ? setDefault(field) : value.toString();
		}
	}

	private static String setDefault(Field field) {
		if (!field.isAccessible())
			field.setAccessible(true);
		if (field.isAnnotationPresent(ExcelField.class)) {
			ExcelField excelField = field.getDeclaredAnnotation(ExcelField.class);
			return excelField.defaultIfNull();
		}
		return "";
	}

	private static void writeHeader(Sheet sheet, AtomicInteger currentRow, List<HeaderField> headerFields, CellStyle headStyle) {
		if (CollectionUtils.isNotEmpty(headerFields)) {
			Row headerRow = sheet.createRow(currentRow.getAndIncrement());
			int size = headerFields.size();
			for (int i = 0; i < size; i++) {
				HeaderField headerField = headerFields.get(i);
				Cell cell = headerRow.createCell(i);
				cell.setCellStyle(headStyle);
				if (excel2003) {
					RichTextString text = new HSSFRichTextString(headerField.getHeaderName());
					cell.setCellValue(text);
				} else {
					RichTextString text = new XSSFRichTextString(headerField.getHeaderName());
					cell.setCellValue(text);
				}
			}
		}
	}

	/**
	 * create excel file if not exists
	 *
	 * @param file
	 * @return
	 * @throws IOException
	 */
	private static File createAsNeeded(File file) throws IOException {
		if (null == file) {
			return null;
		}
		if (!file.exists()) {
			file.createNewFile();
		}
		return file;
	}

	private static void workbook2File(Workbook workbook, File file) {
		OutputStream out = null;
		try {
			out = new FileOutputStream(file);
			workbook.write(out);
			out.flush();
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		} finally {
			if (out != null) {
				IOUtils.closeQuietly(out);
			}
		}
	}


	/**
	 * Excel表头字段
	 */
	public static final class HeaderField {
		/**
		 * 表头字段名
		 */
		private String headerName;
		/**
		 * 对应实体属性名
		 */
		private String fieldName;

		public HeaderField(String headerName, String fieldName) {
			this.headerName = headerName;
			this.fieldName = fieldName;
		}

		public String getHeaderName() {
			return headerName;
		}

		public void setHeaderName(String headerName) {
			this.headerName = headerName;
		}

		public String getFieldName() {
			return fieldName;
		}

		public void setFieldName(String fieldName) {
			this.fieldName = fieldName;
		}
	}

	/**
	 * 根据注解 {@link ExcelField} 解析需要导出的属性
	 *
	 * @param obj
	 * @param <T>
	 * @return
	 * @throws IllegalAccessException
	 */
	private static <T> List<HeaderField> resolveAnnotions(T obj) throws IllegalAccessException {
		if (null == obj) {
			return null;
		}
		Field[] fields = obj.getClass().getDeclaredFields();
		if (null == fields || fields.length == 0) {
			return null;
		}
		List<HeaderField> headerFields = new ArrayList<>();
		for (Field field : fields) {
			if (!field.isAccessible())
				field.setAccessible(true);
			if (field.isAnnotationPresent(ExcelField.class)) {
				ExcelField excelField = field.getDeclaredAnnotation(ExcelField.class);
				String alias = excelField.alias();
				String name = field.getName();
				headerFields.add(new HeaderField(alias, name));
			}
		}
		return headerFields;
	}

	/**
	 * 根据注解 {@link ExcelField} 解析需要导出的属性
	 *
	 * @param obj
	 * @param <T>
	 * @return
	 * @throws IllegalAccessException
	 */
	private static <T> List<HeaderField> resolveImportAnnotions(T obj) throws IllegalAccessException {
		if (null == obj) {
			return null;
		}
		Field[] fields = obj.getClass().getDeclaredFields();
		if (null == fields || fields.length == 0) {
			return null;
		}
		List<HeaderField> headerFields = new ArrayList<>();
		for (Field field : fields) {
			if (!field.isAccessible())
				field.setAccessible(true);
			if (field.isAnnotationPresent(ExcelField.class)) {
				ExcelField excelField = field.getDeclaredAnnotation(ExcelField.class);
				if(excelField.isImport()) {
					String alias = excelField.alias();
					String name = field.getName();
					headerFields.add(new HeaderField(alias, name));
				}
			}
		}
		return headerFields;
	}

	private static String getCellValue(XSSFCell cell) {
		String cellValue = "";
		if (null != cell) {
			switch (cell.getCellType()) {
				case XSSFCell.CELL_TYPE_NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						Date theDate = cell.getDateCellValue();
						cellValue = com.zhangmen.universe.porsche.msg.market.utils.DateUtil.format(theDate, "yyyy-MM-dd HH:mm:ss");
					} else {
						double tmp = cell.getNumericCellValue();
						//判断小数位是否有值
						if (tmp % 1 == 0) {
							cellValue = String.valueOf((int) tmp);
						} else {
							cellValue = String.valueOf(tmp);
						}
					}
					break;
				case XSSFCell.CELL_TYPE_STRING:
					cellValue = cell.getStringCellValue();
					break;
				case XSSFCell.CELL_TYPE_BOOLEAN:
					cellValue = cell.getBooleanCellValue() + "";
					break;
				case XSSFCell.CELL_TYPE_ERROR:
					cellValue = "ILLEGAL_VALUE";
					break;
				case XSSFCell.CELL_TYPE_FORMULA:
					cellValue = cell.getCellFormula() + "";
					break;
				case XSSFCell.CELL_TYPE_BLANK:
				default:
					break;
			}
		}
		return cellValue;
	}

	public static List<Field> getExcelField(Class<?> clazz, List<Field> fieldList) {
		if (fieldList == null) {
			fieldList = new ArrayList<>();
		}
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelField.class)) {
				fieldList.add(field);
			}
		}
		if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
			getExcelField(clazz.getSuperclass(), fieldList);
		}
		return fieldList;
	}

	public static List<Field> getExcelFieldIsImport(Class<?> clazz, List<Field> fieldList) {
		if (fieldList == null) {
			fieldList = new ArrayList<>();
		}
		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelField.class)) {
				if(field.getAnnotation(ExcelField.class).isImport()) { //是导入的属性
					fieldList.add(field);
				}
			}
		}
		if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
			getExcelFieldIsImport(clazz.getSuperclass(), fieldList);
		}
		return fieldList;
	}

	private static Object getFieldValue(Field field, String cellValue) {
		if (StringUtils.isBlank(cellValue)) {
			return null;
		}
		Object obj = null;
		String fieldType = field.getType().getName();
		switch (fieldType) {
			case "char":
			case "java.lang.Character":
			case "java.lang.String":
				obj = cellValue;
				break;
			case "java.util.Date":
				obj = com.zhangmen.universe.porsche.msg.market.utils.DateUtil.parseStr2Date("yyyy-MM-dd HH:mm:ss", cellValue);
				break;
			case "java.time.LocalDateTime":
				obj = LocalDateTime.parse(cellValue, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
				break;
			case "java.lang.Integer":
				obj = Integer.valueOf(cellValue);
				break;
			case "int":
			case "float":
			case "double":
			case "java.lang.Double":
			case "java.lang.Float":
			case "java.lang.Long":
			case "java.lang.Short":
			case "java.math.BigDecimal":
				obj = new BigDecimal(cellValue);
				break;
			default:
				break;
		}
		return obj;
	}


	public static class ExcelStyle {
		public ExcelStyle() {
		}

		public static CellStyle getTitleStyle(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_BLUE.getIndex());
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderLeft(BorderStyle.THIN);
			style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderRight(BorderStyle.THIN);
			style.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderTop(BorderStyle.THIN);
			style.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			return style;
		}

		public static CellStyle getHeadStyle(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLUE_GREY.getIndex());
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderLeft(BorderStyle.THIN);
			style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderRight(BorderStyle.THIN);
			style.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setBorderTop(BorderStyle.THIN);
			style.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			return style;
		}

		public static CellStyle getBodyStyle(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			style.setWrapText(false);
			return style;
		}

		public static CellStyle getBodyStyleRed(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			return style;
		}

		public static CellStyle getBodyStyleGreen(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			Font font = workbook.createFont();
			font.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			return style;
		}

		public static CellStyle getTotalStyle(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.LEFT);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			style.setWrapText(true);
			return style;
		}

		public static CellStyle getTotal1Style(Workbook workbook) {
			CellStyle style = workbook.createCellStyle();
			style.setAlignment(HorizontalAlignment.LEFT);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short) 11);
			font.setBold(false);
			style.setFont(font);
			style.setWrapText(true);
			return style;
		}
	}

}

小丁丐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Excel工具类

1、依赖：<dependency> <groupId>com.github.crab2died</groupId> <artifactId>Excel4J</artifactId> <version>3.0.0-Alpha</version></dependency>2、工具类：package com.zhangmen.universe.porsche.msg.marke
复制链接

扫一扫