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);
}
}
}
Apache POI3.9解析Excel
最新推荐文章于 2024-07-23 00:19:19 发布