All Downloads are FREE. Search and download functionalities are using the official Maven repository.
  • Log In
  • Register

    • com.yuweix.tripod.core.office.ExcelUtil Maven / Gradle / Ivy

    package com.yuweix.tripod.core.office;


import java.beans.PropertyDescriptor;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yuweix.tripod.core.json.JsonUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import org.springframework.util.Assert;


/**
 * @author yuwei
 */
public abstract class ExcelUtil {
	private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
	
	
	
	/**
	 * 读入excel工作簿文件，只读取第一个sheet
	 */
	public static List> read(byte[] fileData) {
		InputStream is = null;
		try {
			is = new ByteArrayInputStream(fileData);
			Workbook wb = WorkbookFactory.create(is);
			Sheet sheet = wb.getSheetAt(0);
			/**
			 * 读取头部，第一行
			 **/
			List headList = getInputHeadList(sheet.getRow(0));
			/**
			 * 读取数据部分，从第二行开始
			 **/
			return getInputDataList(sheet, headList);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					log.error("", e);
				}
			}
		}
	}

	public static List read(byte[] fileData, Class clz) {
		List list = new ArrayList();

		Field[] fields = clz.getDeclaredFields();
		if (fields == null || fields.length <= 0) {
			return list;
		}

		List> mapList = read(fileData);
		if (mapList == null || mapList.size() <= 0) {
			return list;
		}
		for (Map map: mapList) {
			Map fieldValueMap = new HashMap<>();
			for (Field field: fields) {
				ExcelKey excelKeyAno = field.getAnnotation(ExcelKey.class);
				if (excelKeyAno == null) {
					continue;
				}

				String key = excelKeyAno.title() == null || "".equals(excelKeyAno.title().trim()) ? field.getName() : excelKeyAno.title().trim();
				Object v = map.get(key);
				if (v == null) {
					continue;
				}
				fieldValueMap.put(field.getName(), v);
			}
			T t = JsonUtil.parseObject(JsonUtil.toJSONString(fieldValueMap), clz);
			list.add(t);
		}
		return list;
	}

	/**
	 * 导出数据
	 * @param dataList 数据
	 */
	public static byte[] export(List dataList) {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		export(out, dataList);
		byte[] data = out.toByteArray();

		try {
			out.close();
		} catch (IOException e) {
			log.error("", e);
		}
		return data;
	}

	/**
	 * 导出数据到输出流
	 * @param dataList 数据
	 */
	public static void export(OutputStream out, List dataList) {
		Assert.notEmpty(dataList, "[dataList] is required.");
		log.info("list size: {}", dataList.size());
		SXSSFWorkbook workbook = null;
		try {
			workbook = new SXSSFWorkbook();
			workbook.setCompressTempFiles(true);
			/**
			 * 表头样式
			 */
			CellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setAlignment(HorizontalAlignment.CENTER);
			Font titleFont = workbook.createFont();
			titleFont.setFontHeightInPoints((short) 20);
			titleFont.setBold(true);
			titleStyle.setFont(titleFont);

			SXSSFSheet sheet = workbook.createSheet();
			sheet.trackAllColumnsForAutoSizing();

			/**
			 * 输出头部
			 **/
			List headList = getOutputHeadList(dataList.get(0));
			SXSSFRow headRow = sheet.createRow(0);
			for (int i = 0; i < headList.size(); i++) {
				String head = headList.get(i);
				SXSSFCell cell = headRow.createCell(i);
				cell.setCellValue(head);
				cell.setCellStyle(titleStyle);
				sheet.autoSizeColumn(i, true);
			}

			List keyList = getOutputKeyList(dataList.get(0));
			/**
			 * 输出数据部分
			 **/
			for (int i = 0; i < dataList.size(); i++) {
				T t = dataList.get(i);
				SXSSFRow dataRow = sheet.createRow(i + 1);
				List dList = getOutputDataList(keyList, t);
				for (int j = 0; j < dList.size(); j++) {
					SXSSFCell cell = dataRow.createCell(j);
					setCellValue(cell, dList.get(j));
				}
			}

			workbook.write(out);
		} catch (Exception e) {
			log.error("", e);
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					log.error("", e);
				}
			}
		}
	}

	private static List getOutputHeadList(T t) {
		List list = new ArrayList<>();
		if (Map.class.isAssignableFrom(t.getClass())) {
			Map map = (Map) t;
			for (Object o : map.keySet()) {
				list.add(o.toString());
			}
		} else {
			Field[] fields = t.getClass().getDeclaredFields();
			if (fields != null && fields.length > 0) {
				for (Field field: fields) {
					ExcelKey excelKeyAno = field.getAnnotation(ExcelKey.class);
					if (excelKeyAno != null) {
						list.add(excelKeyAno.title() == null || "".equals(excelKeyAno.title().trim()) ? field.getName() : excelKeyAno.title().trim());
					}
				}
			}
		}

		return list;
	}

	private static List getOutputKeyList(T t) {
		List list = new ArrayList<>();
		if (Map.class.isAssignableFrom(t.getClass())) {
			Map map = (Map) t;
			for (Object o : map.keySet()) {
				list.add(o.toString());
			}
		} else {
			Field[] fields = t.getClass().getDeclaredFields();
			if (fields != null && fields.length > 0) {
				for (Field field: fields) {
					ExcelKey excelKeyAno = field.getAnnotation(ExcelKey.class);
					if (excelKeyAno != null) {
						list.add(field.getName());
					}
				}
			}
		}

		return list;
	}

	private static List getOutputDataList(List keyList, T t) {
		Assert.notEmpty(keyList, "[keyList] is required.");
		List list = new ArrayList<>();

		if (Map.class.isAssignableFrom(t.getClass())) {
			Map map = (Map) t;
			for (String key: keyList) {
				list.add(map.get(key));
			}
		} else {
			for (String key: keyList) {
				PropertyDescriptor pd = null;
				try {
					pd = new PropertyDescriptor(key, t.getClass());
					Method getMethod = pd.getReadMethod();
					Object o = getMethod.invoke(t);
					list.add(o);
				} catch (Exception e) {
					log.error("", e);
				}
			}
		}
		return list;
	}
	
	private static List getInputHeadList(Row row) {
		List list = new ArrayList<>();
		for (Cell cell: row) {
			String head = cell.toString();
			if (head == null || "".equals(head.trim())) {
				continue;
			}
			list.add(head.trim());
		}
		return list;
	}
	
	private static List> getInputDataList(Sheet sheet, List keyList) {
		List> list = new ArrayList<>();
		for (Row row: sheet) {
			if (row.getRowNum() <= 0) {
				continue;
			}
			
			Map map = new HashMap<>();
			int i = 0;
			for (Cell cell: row) {
				map.put(keyList.get(i++), getCellValue(cell));
			}
			list.add(map);
		}
		return list;
	}

	private static void setCellValue(Cell cell, Object value) {
		if (value instanceof Number) {
			cell.setCellValue(value == null ? 0 : Double.parseDouble(value.toString()));
		} else if (value instanceof Boolean) {
			cell.setCellValue(value != null && Boolean.parseBoolean(value.toString()));
		} else if (value instanceof Date) {
			cell.setCellValue(value == null ? "" : new SimpleDateFormat(DEFAULT_DATE_FORMAT).format(value));
		} else {
			cell.setCellValue(value == null ? "" : value.toString());
		}
	}

	private static Object getCellValue(Cell cell) {
		CellType ct = cell.getCellType();
		if (CellType.NUMERIC == ct) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			}

			double d = cell.getNumericCellValue();
			int i = (int) d;
			if (d == i) {
				return i;
			} else {
				return d;
			}
//			return NumberToTextConverter.toText(cell.getNumericCellValue());
		}
		if (CellType.STRING == ct) {
			return cell.getRichStringCellValue().getString();
		}
		if (CellType.FORMULA == ct) {
			Workbook wb = cell.getSheet().getWorkbook();
			CreationHelper crateHelper = wb.getCreationHelper();
			FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
			return getCellValue(evaluator.evaluateInCell(cell));
		}
		if (CellType.BLANK == ct) {
			return null;
		}
		if (CellType.BOOLEAN == ct) {
			return cell.getBooleanCellValue();
		}
		if (CellType.ERROR == ct) {
			return null;
		}
		return null;
	}
}

    
    
    
    

    




    
    
    © 2015 - 2025 Weber Informatics LLC | Privacy Policy