edu-saas/edu-util/src/main/java/com/keao/edu/util/excel/POIUtil.java

package com.keao.edu.util.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.beanutils.NestedNullException;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.keao.edu.util.exception.UtilException;

public class POIUtil {

	private final static Logger LOGGER = LoggerFactory.getLogger(POIUtil.class);

	// 能导出的最大数据条数
	private final static int MAX_DATA_SIZE = 50000;

	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 将数据集dataset导出到fileName文件中(只支持.xlsx格式)
	 * @param headColumns 导出文件的列名
	 * @param dataset 数据源
	 * @return
	 * @throws IOException 
	 */
	public static SXSSFWorkbook exportBigExcel(String[] headColumns, List<Map<String, Object>> dataset) throws IOException {
		if (headColumns == null) {
			throw new UtilException("excel列名不能为空");
		}
		if (dataset == null) {
			throw new UtilException("数据集不能为空");
		}
		SXSSFWorkbook workbook = new SXSSFWorkbook();
		CellStyle style = workbook.createCellStyle(); // 获取单元格样式
		/************** 设置单元格样式 *************/
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		style.setWrapText(true);
		Font font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		Sheet sheet = workbook.createSheet(); // 创建sheet
		// 设置表头
		// 创建第一行
		Row row1 = sheet.createRow(0);
		Cell cell = null;
		for (int i = 0; i < headColumns.length; i++) {
			// 创建列
			cell = row1.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 设置单元格的样式
			cell.setCellStyle(style);
			// 设置单元格的值
			cell.setCellValue(headColumns[i]);
		}
		Row row = null;
		// 添加数据
		Map<String, Object> data = null;
		for (int i = 1; i <= dataset.size(); i++) {
			data = dataset.get(i - 1);
			if (data != null) {
				row = sheet.createRow(i);
				int index = 0;
				for (Entry<String, Object> entry : data.entrySet()) {
					row.createCell(index).setCellValue(entry.getValue().toString());
					index++;
				}
			}
		}
		return workbook;
	}

	/**
	 * 将数据集dataset导出到fileName文件中
	 * @param headColumns 导出文件的列名
	 * @param dataset 数据源
	 * @return
	 * @throws IOException 
	 */
	public static HSSFWorkbook exportExcel(String[] headColumns, List<Map<String, Object>> dataset) throws IOException {
		if (headColumns == null) {
			throw new UtilException("excel列名不能为空");
		}
		if (dataset == null) {
			throw new UtilException("数据集不能为空");
		}
		if (dataset.size() > MAX_DATA_SIZE) {
			throw new UtilException("数据集太大，不能导出.最大数据集不能超过" + MAX_DATA_SIZE);
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle style = workbook.createCellStyle(); // 获取单元格样式
		/************** 设置单元格样式 *************/
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		HSSFSheet sheet = workbook.createSheet(); // 创建sheet
		// 设置表头
		// 创建第一行
		HSSFRow row1 = sheet.createRow(0);
		HSSFCell cell = null;
		for (int i = 0; i < headColumns.length; i++) {
			// 创建列
			cell = row1.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 设置单元格的样式
			cell.setCellStyle(style);
			// 设置单元格的值
			cell.setCellValue(headColumns[i]);
		}
		HSSFRow row = null;
		// 添加数据
		Map<String, Object> data = null;
		for (int i = 1; i <= dataset.size(); i++) {
			data = dataset.get(i - 1);
			if (data != null) {
				row = sheet.createRow(i);
				int index = 0;
				for (Entry<String, Object> entry : data.entrySet()) {
					row.createCell(index).setCellValue(entry.getValue().toString());
					index++;
				}
			}
		}
		return workbook;
	}

	/**
	 * 将数据集dataset导出到fileName文件中
	 * @param headColumns 导出文件的列名
	 * @param fieldColumns 
	 * @param dataset 数据源
	 * @return
	 * @throws IOException 
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	public static <T> HSSFWorkbook exportExcel(String[] headColumns, String[] fieldColumns, List<T> dataset) throws IOException, IllegalAccessException,
			InvocationTargetException, NoSuchMethodException {
		if (headColumns == null) {
			throw new UtilException("excel列名不能为空");
		}
		if (dataset == null) {
			throw new UtilException("数据集不能为空");
		}
		/*if (dataset.size() > MAX_DATA_SIZE) {
			throw new UtilException("数据集太大，不能导出.最大数据集不能超过" + MAX_DATA_SIZE);
		}*/
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFCellStyle style = workbook.createCellStyle(); // 获取单元格样式
		/************** 设置单元格样式 *************/
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		style.setWrapText(true);
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		HSSFSheet sheet = workbook.createSheet(); // 创建sheet
		// 设置表头
		// 创建第一行
		HSSFRow row1 = sheet.createRow(0);
		HSSFCell cell = null;
		for (int i = 0; i < headColumns.length; i++) {
			// 创建列
			cell = row1.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 设置单元格的样式
			cell.setCellStyle(style);
			// 设置单元格的值
			cell.setCellValue(headColumns[i]);
		}
		HSSFRow row = null;
		// 添加数据
		T data = null;
		Object obj = null;
		for (int i = 1; i <= dataset.size(); i++) {
			data = dataset.get(i - 1);
			if (data != null) {
				row = sheet.createRow(i);
				for (int j = 0; j < fieldColumns.length; j++) {
					cell = row.createCell(j);
					try {
						obj = PropertyUtils.getNestedProperty(data, fieldColumns[j]);
						if (obj instanceof Date) {
							obj = sdf.format(obj);
						}
					} catch (NestedNullException e) {
						LOGGER.warn(e.getMessage());
						obj = null;
					}
					if (obj != null) {
						cell.setCellValue(obj.toString());
					} else {
						cell.setCellValue("");
					}
				}
			}
		}
		return workbook;
	}

	/**
	 * 将数据集dataset导出到fileName文件中(只支持.xlsx格式)
	 * @param headColumns 导出文件的列名
	 * @param fieldColumns 
	 * @param dataset 数据源
	 * @return
	 * @throws IOException 
	 * @throws NoSuchMethodException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	public static <T> SXSSFWorkbook exportBigExcel(String[] headColumns, String[] fieldColumns, List<T> dataset) throws IOException, IllegalAccessException,
			InvocationTargetException, NoSuchMethodException {
		if (headColumns == null) {
			throw new UtilException("excel列名不能为空");
		}
		if (dataset == null) {
			throw new UtilException("数据集不能为空");
		}
		SXSSFWorkbook workbook = new SXSSFWorkbook();
		CellStyle style = workbook.createCellStyle(); // 获取单元格样式
		/************** 设置单元格样式 *************/
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平
		style.setWrapText(true);
		Font font = workbook.createFont();
		font.setColor(HSSFFont.COLOR_NORMAL);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		Sheet sheet = workbook.createSheet(); // 创建sheet
		// 设置表头
		// 创建第一行
		Row row1 = sheet.createRow(0);
		Cell cell = null;
		for (int i = 0; i < headColumns.length; i++) {
			// 创建列
			cell = row1.createCell(i);
			// 定义单元格为字符串类型
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// 设置单元格的样式
			cell.setCellStyle(style);
			// 设置单元格的值
			cell.setCellValue(headColumns[i]);
		}
		Row row = null;
		// 添加数据
		T data = null;
		Object obj = null;
		for (int i = 1; i <= dataset.size(); i++) {
			data = dataset.get(i - 1);
			if (data != null) {
				row = sheet.createRow(i);
				for (int j = 0; j < fieldColumns.length; j++) {
					cell = row.createCell(j);
					try {
						obj = PropertyUtils.getNestedProperty(data, fieldColumns[j]);
						if (obj instanceof Date) {
							obj = sdf.format(obj);
						}
					} catch (NestedNullException e) {
						LOGGER.warn(e.getMessage());
						obj = null;
					}
					if (obj != null) {
						cell.setCellValue(obj.toString());
					} else {
						cell.setCellValue("");
					}
				}
			}
		}
		return workbook;
	}

	/**
	 * 导入指定的excel文件
	 * @param excelFile excel文件
	 * @param startRowNum 从第几行数据开始导入
	 * @return
	 * @throws IOException
	 */
	public static Map<String, List<Map<String, Object>>> importExcel(File excelFile, int startRowNum) throws IOException {
		Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

		if (!excelFile.exists()) {
			LOGGER.error("文件" + excelFile.getAbsolutePath() + "不存在");
			return result;
		}

		Workbook workbook = null;
		FileInputStream fis = new FileInputStream(excelFile);
		int index = excelFile.getName().lastIndexOf('.');
		if (index <= 0) {
			fis.close();
			throw new UtilException("excel文件的扩展名是.xls or .xlsx!");
		}
		String ext = excelFile.getName().substring(index).toLowerCase();
		try {
			if (".xlsx".equals(ext.toLowerCase())) {
				// 支持excel2007 xlsx格式
				workbook = new XSSFWorkbook(fis);
			} else if (".xls".equals(ext)) {
				// 支持excel2003以前 xls格式
				workbook = new HSSFWorkbook(fis);
			} else {
				throw new UtilException("excel文件的扩展名是.xls or .xlsx!");
			}
		} catch (Exception ex) {
			LOGGER.error("excel open error.", ex);
			return result;
		} finally {
			if (fis != null) {
				fis.close();
			}
		}
		int sheetCount = workbook.getNumberOfSheets();
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		int currentRowNum = 0, currentCellNum = 0;
		// Object fieldValue = null;
		Iterator<Row> rowIter = null;
		Iterator<Cell> cellIter = null;

		for (int i = 0; i < sheetCount; i++) {
			String fieldsName[] = null;
			// 行号清零
			currentRowNum = 0;
			// 顺序取sheet
			sheet = workbook.getSheetAt(i);
			List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
			rowIter = sheet.iterator();
			while (rowIter.hasNext()) {
				Map<String, Object> obj = null;
				// 获取当前行
				row = rowIter.next();
				if (row != null) {
					currentRowNum++;

					if (currentRowNum == 1) {// 第一列表示英文名称对应表字段
						cellIter = row.iterator();
						// 列号清零
						currentCellNum = 0;
						List<String> names = new ArrayList<String>();
						while (cellIter.hasNext()) {
							cell = cellIter.next();
							names.add(cell.getStringCellValue());
						}
						fieldsName = names.toArray(new String[names.size()]);
						continue;
					}

					// 跳过指定的行
					if (currentRowNum < startRowNum) {
						continue;
					}

					// 实例化对象
					obj = new HashMap<String, Object>();// clazz.newInstance();

					cellIter = row.iterator();
					// 列号清零
					currentCellNum = 0;
					while (cellIter.hasNext()) {
						cell = cellIter.next();
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String fieldValue = cell.getStringCellValue();
						obj.put(fieldsName[currentCellNum], fieldValue);
						currentCellNum++;
					}
				}
				if (obj != null)
					datas.add(obj);

			}
			if (!datas.isEmpty())
				result.put(sheet.getSheetName(), datas);
		}
		if (workbook != null) {
			workbook.close();
		}
		return result;
	}

	/**
	 * 导入指定的excel文件
	 * @param inputStream excel文件流
	 * @param startRowNum 从第几行数据开始导入
	 * @param extName 文件扩展名，仅支持.xls 或 .xlsx
	 * @return
	 * @throws IOException
	 */
	public static Map<String, List<Map<String, Object>>> importExcel(InputStream inputStream, int startRowNum, String extName) throws IOException {

		Map<String, List<Map<String, Object>>> result = new HashMap<String, List<Map<String, Object>>>();

		Workbook workbook = null;

		try {
			if (extName.endsWith(".xlsx")) {
				// 支持excel2007 xlsx格式
				workbook = new XSSFWorkbook(inputStream);
			} else if (extName.endsWith(".xls")) {
				// 支持excel2003以前 xls格式
				workbook = new HSSFWorkbook(inputStream);
			} else {
				throw new UtilException("excel文件的扩展名是.xls or .xlsx!");
			}
		} catch (Exception ex) {
			LOGGER.error("excel open error.", ex);
			return result;
		} finally {
			if (inputStream != null) {
				inputStream.close();
			}
		}

		int sheetCount = workbook.getNumberOfSheets();
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		int currentRowNum = 0, currentCellNum = 0;
		// Object fieldValue = null;
		Iterator<Row> rowIter = null;
		Iterator<Cell> cellIter = null;

		for (int i = 0; i < sheetCount; i++) {
			String fieldsName[] = null;
			// 行号清零
			currentRowNum = 0;
			// 顺序取sheet
			sheet = workbook.getSheetAt(i);
			List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
			rowIter = sheet.iterator();
			while (rowIter.hasNext()) {
				Map<String, Object> obj = null;
				boolean hasVal = false;
				// 获取当前行
				row = rowIter.next();
				if (row != null) {
					currentRowNum++;

					if (currentRowNum == 1) {// 第一列表示英文名称对应表字段
						cellIter = row.iterator();
						// 列号清零
						currentCellNum = 0;
						List<String> names = new ArrayList<String>();
						while (cellIter.hasNext()) {
							cell = cellIter.next();
							names.add(cell.getStringCellValue());
						}
						fieldsName = names.toArray(new String[names.size()]);
						continue;
					}

					// 跳过指定的行
					if (currentRowNum < startRowNum) {
						continue;
					}

					// 实例化对象
					obj = new HashMap<String, Object>();// clazz.newInstance();

					cellIter = row.iterator();
					// 列号清零
					currentCellNum = 0;
					while (cellIter.hasNext()) {
						cell = cellIter.next();
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String fieldValue = cell.getStringCellValue();
						if(StringUtils.isNotBlank(fieldValue)){
							hasVal  = true;
						}
						obj.put(fieldsName[currentCellNum], fieldValue);
						currentCellNum++;
					}
				}
				if (obj != null && hasVal)
					datas.add(obj);

			}
			if (!datas.isEmpty())
				result.put(sheet.getSheetName(), datas);
		}
		if (workbook != null) {
			workbook.close();
		}
		return result;
	}

	/**
	 * 创建excel文档
	 *
	 * @param getters list中map的key数组集合
	 * @param headers excel的列名
	 */
	public static Workbook createWorkBook(List list, String[] getters, String[] headers, Class clazz) {

		List<Method> methods = getMethodsByStrs(getters, clazz);

		// 创建.xlsx工作簿
		Workbook wb = new XSSFWorkbook();
		// 创建第一个sheet（页），并命名
		Sheet sheet = wb.createSheet("sheet1");
		// 手动设置列宽.第一个参数表示要为第几列设,第二个参数表示列的宽度,n为列高的像素数.

		for (int i = 0; i < getters.length; i++) {
			sheet.setColumnWidth((short) i, (short) (35.7 * 200));
		}

		// 创建第一行
		Row header = sheet.createRow(0);

		// 创建两种单元格格式
		CellStyle cellStyle1 = wb.createCellStyle();
		CellStyle cellStyle2 = wb.createCellStyle();

		// 创建两种字体
		Font font1 = wb.createFont(); // 标题字体
		Font font2 = wb.createFont(); // 正文字体

		// 标题加粗
		font1.setBoldweight(Font.BOLDWEIGHT_BOLD);

		// 设置两种单元格的样式
		setCellStype(cellStyle1, font1);
		setCellStype(cellStyle2, font2);

		//设置header
		for (int i = 0; i < headers.length; i++) {
			Cell cell = header.createCell(i);
			cell.setCellValue(headers[i]);
			cell.setCellStyle(cellStyle1);
		}

		//设置data
		int headersNum = 1;
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + headersNum);
			for (int j = 0; j < methods.size(); j++) {
				try {
					Object invoke = methods.get(j).invoke(list.get(i));
					if (invoke != null) {
						row.createCell(j).setCellValue(invoke.toString());
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return wb;
	}

	private static void setCellStype(CellStyle cellStyle, Font font) {
		font.setFontHeightInPoints((short) 10);
		font.setColor(IndexedColors.BLACK.getIndex());
		cellStyle.setFont(font);
		cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyle.setBorderRight(CellStyle.BORDER_THIN);
		cellStyle.setBorderTop(CellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
	}

	private static List<Method> getMethodsByStrs(String[] getters, Class clazz) {
		List<Method> list = new ArrayList<>();
		for (String getter : getters) {
			try {
				list.add(clazz.getDeclaredMethod(getter));
			} catch (NoSuchMethodException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

}