Apache POI3.9解析Excel

秒速五厘米 2022-08-05 11:19 278阅读 0赞

import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.text.ParseException;
    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 java.util.UUID;
    
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.apache.poi.ss.usermodel.Font;
    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.ss.usermodel.WorkbookFactory;
    import org.apache.struts2.ServletActionContext;
    
    import com.sf.framework.server.core.context.UserContext;
    import com.sf.module.tcascommon.cfg.SysConfigProperties;
    
    /**
     * 基于Apache POI3.9的Excel文件解析工具类
     * @param <T> 解析数据的封装类型
     * @author zhangzikui
     */
    public class ExcelParser<T>
    {
    	private static final String EMPTY_STRING = "";
    	
    	private Class<T> entityClass = null;
    	private Workbook workBook = null;
    	private CellStyle errorCellStyle = null;
    	private final List<ColumnMapper> columnMapperList = new ArrayList<ColumnMapper>();
    	private final Map<Integer, String> errorMessage = new HashMap<Integer, String>();
    	
    	private int startRowIndex = 1;
    	private int promptColumnIndex = 1;
    	
    	private int lastRowNum = 0;
    	private int successCount = 0;
    	private int errorCount = 0;
    	
    	/**
    	 * 构造方法
    	 * @param excelFile Excel文件对象
    	 * @param clazz 解析数据的封装类型
    	 * @throws InvalidFormatException
    	 * @throws IOException
    	 */
    	public ExcelParser(File excelFile, Class<T> clazz, int startRowIndex, int promptColumnIndex) throws InvalidFormatException, IOException {
    		this.entityClass = clazz;
    		this.startRowIndex = startRowIndex;
    		this.promptColumnIndex = promptColumnIndex;
    		this.createWorkbook(excelFile);
    	}
    	
    	/**
    	 * 创建poi的workbook对象
    	 * @param excelFile
    	 * @throws InvalidFormatException
    	 * @throws IOException
    	 */
    	private void createWorkbook(File excelFile) throws InvalidFormatException, IOException
    	{
    		FileInputStream fis = null;
    
    		try
    		{
    			fis = new FileInputStream(excelFile);
    			this.workBook = WorkbookFactory.create(fis);
    		}
    		finally
    		{
    			if(fis != null)
    			{
    				try
    				{
    					fis.close();
    				}
    				catch(IOException e)
    				{
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		if(this.workBook != null)
    		{
    			Sheet firstSheet = this.workBook.getSheetAt(0);
    			
    			if(firstSheet != null)
    			{
    				this.lastRowNum = firstSheet.getLastRowNum();
    			}
    			
    			//设置错误提示列的样式、字体、颜色
    			Font font = this.workBook.createFont();
    			font.setColor(Font.COLOR_RED);
    			this.errorCellStyle = this.workBook.createCellStyle();
    			this.errorCellStyle.setFont(font);
    		}
    	}
    	
    	/**
    	 * 解析Excel文件，将数据封装为List<T>并返回
    	 * @param startRowIndex 解析起始行的索引，从0开始
    	 * @return 解析结果集合对象
    	 * @throws NoSuchFieldException
    	 */
    	public List<T> parseExcel() throws NoSuchFieldException
    	{
    		List<T> resultList = new ArrayList<T>();
    		
    		if(this.columnMapperList == null || this.columnMapperList.size() < 1 || this.startRowIndex < 0)
    		{
    			return resultList;
    		}
    		
    		if(this.workBook != null)
    		{
    			Sheet firstSheet = this.workBook.getSheetAt(0);
    			
    			if(firstSheet != null)
    			{
    				Row titleRow = firstSheet.getRow(this.startRowIndex - 1);
    				for(ColumnMapper mapper : this.columnMapperList)
    				{
    					mapper.setField(getFieldByName(this.entityClass, mapper.getFieldName()));
    					
    					//获取标题列名称
    					if(titleRow != null && titleRow.getCell(mapper.getColumnIndex()) != null)
    					{
    						mapper.setColumnTitle(titleRow.getCell(mapper.getColumnIndex()).getStringCellValue().trim());
    					}
    				}
    				
    				Cell cell = null;
    				Row row = null;
    				Object fieldValue = null;
    				T entityObject = null;
    				
    				for(int rowIndex = this.startRowIndex; rowIndex <= this.lastRowNum; rowIndex++)
    				{
    					row = firstSheet.getRow(rowIndex);
    					if(row != null)
    					{
    						try
    						{
    							entityObject = this.entityClass.newInstance();
    						}
    						catch(Exception e1)
    						{
    							e1.printStackTrace();
    							break;
    						}
    						
    						boolean hasError = false;
    						for(ColumnMapper mapper : this.columnMapperList)
    						{
    							cell = row.getCell(mapper.getColumnIndex());
    							
    							if(mapper.getValidator() != null)
    							{
    								try
    								{
    									mapper.getValidator().validate(cell);
    								}
    								catch(Exception e1)
    								{
    									hasError = true;
    									this.errorMessage.put(rowIndex, mapper.getColumnTitle() + "[第" + (mapper.getColumnIndex()+1) + "列]：" + e1.getMessage());
    									++this.errorCount;
    									break;
    								}
    							}
    							
    							try
    							{
    								fieldValue = changeCellToObject(cell, mapper);
    							}
    							catch(Exception e)
    							{
    								hasError = true;
    								this.errorMessage.put(rowIndex, mapper.getColumnTitle() + "[第" + (mapper.getColumnIndex()+1) + "列]：" + e.getMessage());
    								++this.errorCount;
    								break;
    							}
    							
    							try
    							{
    								mapper.getField().set(entityObject, fieldValue);
    							}
    							catch(Exception e)
    							{
    								hasError = true;
    								this.errorMessage.put(rowIndex, mapper.getColumnTitle() + "[第" + (mapper.getColumnIndex()+1) + "列]：" + e.getMessage());
    								++this.errorCount;
    								break;
    							}
    						}
    						
    						if(!hasError)
    						{
    							resultList.add(entityObject);
    							++this.successCount;
    						}
    					}
    				}
    			}
    		}
    		
    		return resultList;
    	}
    	
    	/**
    	 * 通过行和列的索引，获取对应单元格对象
    	 * @param rowNum 行的索引，从0开始
    	 * @param columnNum 列的索引，从0开始
    	 * @return Cell 单元格对象
    	 */
        public Cell getCellByIndex(int rowNum, int columnNum)
        {
        	if(this.workBook != null)
    		{
    			Sheet firstSheet = this.workBook.getSheetAt(0);
    			
    			if(firstSheet != null && firstSheet.getRow(rowNum) != null)
    			{
    				return firstSheet.getRow(rowNum).getCell(columnNum);
    			}
    		}
        	
        	return null;
        }
        
        /**
         * 获取错误提示文件的文件名
         * @param messageCellIndex Excel中错误提示列的索引，从1开始
         * @return 提示信息文件路径字符串
         * @throws Exception
         */
        public String getPromptFileName() throws Exception 
        {
        	if(this.promptColumnIndex < 1)
        	{
        		return null;
        	}
        	
        	for(Map.Entry<Integer, String> entry : this.errorMessage.entrySet())
        	{
        		addErrorMessage(entry.getKey(), this.promptColumnIndex, entry.getValue());
        	}
        	
        	File file = null;
        	OutputStream os = null;
    		try
    		{
    			file = new File(getTemporaryFilePath(), getTemporaryFileName());
    			os = new FileOutputStream(file);
    			this.workBook.write(os);
    		}
    		catch(Exception e)
    		{
    			throw new Exception("创建错误提示文件出错", e);
    		}
    		finally
    		{
    			if(os != null)
    			{
    				try
    				{
    					os.close();
    				}
    				catch(IOException e)
    				{
    					e.printStackTrace();
    				}
    			}
    		}
    		
    		return file.getName();
        }
        
        /**
         * 增加错误提示信息
         * @param rowIndex 行的索引，从0开始
         * @param cellIndex 列的索引，从0开始
         * @param message 提示信息
         */
        private void addErrorMessage(int rowIndex, int cellIndex, String message)
        {
        	if(this.workBook != null)
    		{
    			Sheet firstSheet = this.workBook.getSheetAt(0);
    			
    			if(firstSheet != null && firstSheet.getRow(rowIndex) != null)
    			{
    				Cell msgCell = firstSheet.getRow(rowIndex).createCell(cellIndex, Cell.CELL_TYPE_STRING);
    				msgCell.setCellStyle(this.errorCellStyle);
    				msgCell.setCellValue(message);
    			}
    		}
        }
        
        /**
         * 添加列映射对象
         * @param mapper
         */
        public void addColumnMapper(ColumnMapper mapper)
        {
        	this.columnMapperList.add(mapper);
        }
        
        /**
         * 解析成功的记录条数
         * @return 解析成功的记录条数
         */
    	public int getSuccessCount()
    	{
    		return successCount;
    	}
    
    	/**
         * 解析失败的记录条数
         * @return 解析失败的记录条数
         */
    	public int getErrorCount()
    	{
    		return errorCount;
    	}
    
    	/**
    	 * 获取Excel文件最后一行的索引，从0开始
    	 * @return 最后一行的索引，从0开始
    	 */
    	public int getLastRowNum()
    	{
    		return lastRowNum;
    	}
        
    	/**
    	 * 将单元格转换为Java对象的工具方法
    	 * @param cell 单元格对象
    	 * @param mapper 列映射器
    	 * @return 转换后的Java对象
    	 * @throws Exception
    	 */
    	private static Object changeCellToObject(Cell cell, ColumnMapper mapper) throws Exception 
    	{
    		Object fieldValue = null;
    		Class<?> fieldType = mapper.getField().getType();
    		String cellValueString = changeCellToString(cell, fieldType);
    		
    		if(cellValueString != null && !(EMPTY_STRING.equals(cellValueString.trim())))
    		{
    			try
    			{
    				if(String.class.equals(fieldType))
    				{
    					fieldValue = cellValueString;
    				}
    				else if(Integer.class.equals(fieldType) || int.class.equals(fieldType))
    				{
    					fieldValue = Integer.valueOf(cellValueString);
    				}
    				else if(Long.class.equals(fieldType) || long.class.equals(fieldType))
    				{
    					fieldValue = Long.valueOf(cellValueString);
    				}
    				else if(Float.class.equals(fieldType) || float.class.equals(fieldType))
    				{
    					fieldValue = Float.valueOf(cellValueString);
    				}
    				else if(Double.class.equals(fieldType) || double.class.equals(fieldType))
    				{
    					fieldValue = Double.valueOf(cellValueString);
    				}
    				else if(Date.class.equals(fieldType))
    				{
    					switch(cell.getCellType())
    					{
    						case Cell.CELL_TYPE_STRING:
    						{
    							fieldValue = new SimpleDateFormat(mapper.getDateFormatString().trim()).parse(cellValueString);
    							break;
    						}
    						case Cell.CELL_TYPE_NUMERIC:
    						{
    							if(DateUtil.isCellDateFormatted(cell))
    							{
    								fieldValue = cell.getDateCellValue();
    							}
    							break;
    						}
    					}
    				}
    			}
    			catch(NumberFormatException ne)
    			{
    				throw new Exception("数字解析出错");
    			}
    			catch(ParseException pe)
    			{
    				throw new Exception("日期解析出错");
    			}
    			catch(IllegalArgumentException ae)
    			{
    				throw new Exception("日期格式参数无效");
    			}
    		}
    		
    		return fieldValue;
    	}
    	
    	/**
    	 * 将单元格转换为合适的字符串形式
    	 * @param cell 单元格对象
    	 * @param fieldType Java属性的类型
    	 * @return 单元格转换后的字符串
    	 */
    	private static String changeCellToString(Cell cell, Class<?> fieldType)
    	{
    		String cellValueString = "";
    		
    		if(cell != null)
    		{
    			switch(cell.getCellType())
    			{
    				case Cell.CELL_TYPE_STRING:
    				{
    					cellValueString = cell.getStringCellValue().trim();
    					break;
    				}
    				
    				case Cell.CELL_TYPE_NUMERIC:
    				{
    					if(DateUtil.isCellDateFormatted(cell))
    					{
    						cellValueString = EMPTY_STRING;
    					}
    					else
    					{
    						if(Float.class.equals(fieldType) || float.class.equals(fieldType)
    						  || Double.class.equals(fieldType) || double.class.equals(fieldType))
    						{
    							cellValueString = String.valueOf(cell.getNumericCellValue());
    						}
    						else
    						{
    							cell.setCellType(Cell.CELL_TYPE_STRING);
    							cellValueString = cell.getStringCellValue().trim();
    						}
    					}
    					break;
    				}
    				
    				case Cell.CELL_TYPE_BLANK:
    				{
    					cellValueString = EMPTY_STRING;
    					break;
    				}
    				
    				case Cell.CELL_TYPE_FORMULA:
    				{
    					try
    					{
    						cellValueString = String.valueOf(cell.getNumericCellValue());
    					}
    					catch(Exception e)
    					{
    						cellValueString = cell.getRichStringCellValue().toString().trim();
    					}
    					break;
    				}
    				
    				default:
    				{
    					cellValueString = EMPTY_STRING;
    				}
    			}
    		}
    		else
    		{
    			cellValueString = EMPTY_STRING;
    		}
    		
    		return cellValueString;
    	}
    	
        private static Field getFieldByName(Class<?> clazz, String fieldName) throws NoSuchFieldException
        {
        	Field field = null;
        	try
    		{
    			field = clazz.getDeclaredField(fieldName);
    		}
    		catch(NoSuchFieldException e)
    		{
    			field = clazz.getSuperclass().getDeclaredField(fieldName);
    		}
    		
    		field.setAccessible(true);
    		
    		return field;
        }
        
        /**
         * 获取临时文件保存的路径
         * @return 临时文件路径的File对象
         * @throws IOException
         */
        private static File getTemporaryFilePath() throws IOException
        {
        	File tempPath =new File((File)(ServletActionContext.getServletContext().getAttribute("javax.servlet.context.tempdir")),
        									SysConfigProperties.EXCEL_CHILD_DIR);
        	
    		if(!tempPath.exists())
    		{
    			tempPath.mkdirs();
    		}
    
    		return tempPath.getCanonicalFile();
        }
        
        /**
         * 获取临时文件的名称
         * @return 临时文件的名称
         * @throws IOException
         */
        private static String getTemporaryFileName()
    	{
    		StringBuilder fileName = new StringBuilder(UserContext.getContext()
    															  .getCurrentUser()
    															  .getUsername());
    		
    		fileName.append("_")
    				.append(UUID.randomUUID().toString());
    		
    		return fileName.toString();
    	}
        
        /**
         * Excel列与JAVA对象属性的映射器，
         * 每一列对应一个属性
         */
        public static class ColumnMapper
        {
        	private int columnIndex;
        	private String columnTitle = "";
        	private String fieldName;
        	private String dateFormatString;
        	private Field field;
        	private ColumnValidator validator;
        	
        	/**
        	 * 映射器构造方法
        	 * @param columnIndex 列在Excel文件的中的索引，从0开始
        	 * @param fieldName 列对应的Java属性名称
        	 * @param dateFormatString 日期格式（如“yyyy-MM-dd”），如需要解析为Java日期对象，则需设置该属性
        	 * @param validator 列对应的数据校验接口
        	 */
        	public ColumnMapper(int columnIndex, String fieldName, String dateFormatString, ColumnValidator validator){
    			this.columnIndex = columnIndex;
    			this.fieldName = fieldName;
    			this.dateFormatString = dateFormatString;
    			this.validator = validator;
    		}
    
    		public int getColumnIndex()
        	{
        		return columnIndex;
        	}
        	
        	public void setColumnIndex(int columnIndex)
    		{
    			this.columnIndex = columnIndex;
    		}
        	
        	public String getColumnTitle()
    		{
    			return columnTitle;
    		}
    
        	private void setColumnTitle(String columnTitle)
    		{
    			this.columnTitle = columnTitle;
    		}
        	
        	public String getFieldName()
    		{
    			return fieldName;
    		}
        	
        	public void setFieldName(String fieldName)
    		{
    			this.fieldName = fieldName;
    		}
        	
        	public String getDateFormatString()
    		{
    			return dateFormatString;
    		}
    
    		public void setDateFormatString(String dateFormatString)
    		{
    			this.dateFormatString = dateFormatString;
    		}
    		
    		private void setField(Field field)
    		{
    			this.field = field;
    		}
    		
    		private Field getField()
        	{
        		return field;
        	}
        	
        	public ColumnValidator getValidator()
        	{
        		return validator;
        	}
        	
        	public void setValidator(ColumnValidator validator)
    		{
    			this.validator = validator;
    		}
        }
        
        /**
         * Excel数据校验回调接口。
         * 校验失败时，将提示信息以异常形式抛出
         */
    	public interface ColumnValidator
    	{
    		/**
    		 * 数据校验回调方法。
    		 * 校验失败时，将提示信息以异常形式抛出
    		 * @param cell 单元格对象
    		 * @throws Exception 校验提示信息
    		 */
    		public void validate(Cell cell) throws Exception;
    	}
    	
    	
    	/**
    	 * 工具类使用示例
    	 * @param args
    	 * @throws Exception
    	 */
    	public static void main(String[] args) throws Exception
    	{
    		//要解析的Excel文件对象
    		File file = new File("E:/Download/DeptPriceTemplate_import.xls");
    		
    		//创建解析器（Object是用于封装解析数据的Java类，自行创建）
    		//第三个参数为解析的起始行（从0开始），第四个参数为提示信息的列索引（从1开始）
    		ExcelParser<Object> parser = new ExcelParser<Object>(file, Object.class, 1, 10);
    		
    		//获取Excel文档最后一行索引（如果行数超限，则返回提示，无需继续解析）
    		System.out.println("最后一行索引：" + parser.getLastRowNum());
    		
    		//创建列校验对象
    		ColumnValidator cValidator = new ColumnValidator(){
    			public void validate(Cell cell) throws Exception{
    				if(cell==null || "".equals(cell.toString().trim()))
    				{
    					throw new Exception("该字段不能为空！");
    				}
    			}
    		};
    		
    		//添加列映射器（索引从0开始）
    		parser.addColumnMapper(new ColumnMapper(0, "empCode", null, null));
    		parser.addColumnMapper(new ColumnMapper(1, "workDate", "yyyy-MM-DD", null));
    		parser.addColumnMapper(new ColumnMapper(2, "workTime", null, cValidator));
    		
    		//解析文件
    		List<Object> list = parser.parseExcel();
    		
    		//获取正确条数和错误条数
    		System.out.println("成功条数：" + list.size() + "失败条数：" + parser.getErrorCount());
    		
    		//获取错误提示信息文件名
    		if(parser.getErrorCount() > 0)
    		{
    			String errorFilePath = parser.getPromptFileName();
    			System.out.println(errorFilePath);
    		}
    	}
    }