一、基于eventusermodel的excel解析工具类
这一部分和网上大部分的差不多,变化在二和三
import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import org.apache.log4j.LogManager;import org.apache.log4j.Logger;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.openxml4j.exceptions.OpenXML4JException;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.model.StylesTable;import org.xml.sax.InputSource;import org.xml.sax.SAXException;import org.xml.sax.XMLReader;import org.xml.sax.helpers.XMLReaderFactory;public class ImportantUtil{private static final Logger logger = LogManager.getLogger(ImportantUtil.class);public ImportantUtil(ISheetContentHandler handler) {contentHandler=handler;}/*** 表格默认处理器*/private ISheetContentHandler contentHandler = null;/*** 读取数据*/private List<Object[]> data=new ArrayList<Object[]>();/*** 转换表格,默认为转换第一个表格* @param stream* @return* @throws InvalidFormatException* @throws IOException* @throws ParseException*/public List<Object[]> parse(InputStream stream)throws InvalidFormatException, IOException, ParseException {return parse(stream, 1);}/**** @param stream* @param sheetId:为要遍历的sheet索引，从1开始* @return* @throws InvalidFormatException* @throws IOException* @throws ParseException*/public synchronized List<Object[]> parse(InputStream stream, int sheetId)throws InvalidFormatException, IOException, ParseException {// 每次转换前都清空数据data.clear();// 打开表格文件输入流OPCPackage pkg = OPCPackage.open(stream);try {// 创建表阅读器XSSFReader reader;try {reader = new XSSFReader(pkg);} catch (OpenXML4JException e) {logger.error("读取表格出错");throw new ParseException(e.fillInStackTrace());}// 转换指定单元表InputStream sheetStream = reader.getSheet("rId"+sheetId);try {InputSource sheetSource = new InputSource(sheetStream);StylesTable styles = reader.getStylesTable();ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);getContentHandler().init(data);// 设置读取出的数据// 获取转换器XMLReader parser = getSheetParser(styles, strings);parser.parse(sheetSource);} catch (SAXException e) {logger.error("读取表格出错");throw new ParseException(e.fillInStackTrace());} finally {sheetStream.close();}} finally {pkg.close();}return data;}/*** 获取表格读取数据,获取数据前，需要先转换数据<br>* 此方法不会获取第一行数据** @return 表格读取数据*/public List<Object[]> getData() {return data;}/*** 获取读取表格的转换器** @return 读取表格的转换器* @throws SAXException* SAX错误*/protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {XMLReader parser = XMLReaderFactory.createXMLReader();parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));return parser;}public ISheetContentHandler getContentHandler(){return contentHandler;}public void setContentHandler(ISheetContentHandler contentHandler) {this.contentHandler = contentHandler;}/*** 表格转换错误*/public class ParseException extends Exception {private static final long serialVersionUID = -2451526411018517607L;public ParseException(Throwable t) {super("表格转换错误", t);}}}
二、为了方便开发使用,下面新增一个接口和一个abstract类
import java.util.List;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;public interface ISheetContentHandler extends SheetContentsHandler {/*** 设置转换后的数据集，用于存放转换结果* @param datas* 转换结果*/public void init(List<Object []> datas);}
之所以创建这么一个abstract类,就是为了方便在以后的开发的时候可以不用重复添加这些属性和方法,只抛出 cell 方法让子类去继承实现就行了。
其中，fieldsMap是我个人使用的一个用来翻译后台字段和前台展示字段的一个map集合，大家可按自己的需求删减。
import java.util.ArrayList;import java.util.List;import java.util.Map;import org.apache.poi.xssf.usermodel.XSSFComment;import org.springframework.beans.BeanUtils;public abstract class SheetContentHandler implements ISheetContentHandler {protected List<Object[]> data=null;protected ArrayList<String> columns = new ArrayList<String>();protected Map<String,String> fieldsMap;protected Object [] readRow=null;protected int columsLength;protected int rowIndex;public SheetContentHandler(Map<String,String> fieldsMap) {this.fieldsMap=fieldsMap;}@Overridepublic void init(List<Object []> datas) {this.data=datas;}@Overridepublic void startRow(int rowNum) {rowIndex=rowNum;//logger.info("开始解析第"+rowNum+"行。");//readRow=null;if (rowNum != 0) {readRow=new Object[columsLength];}}@Overridepublic void endRow(int rowNum) {//System.out.println("第"+rowNum+"行解析结束。");if (rowNum == 0) {//表头 列columsLength=columns.size();}else {Object [] row=new Object[columsLength];BeanUtils.copyProperties(readRow, row);data.add(row);}readRow = null;}@Overridepublic void cell(String cellReference, String formattedValue, XSSFComment comment) {int colIndex = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置handleCell(rowIndex,colIndex,formattedValue);}public abstract void handleCell(int rowIndex,int cellIndex, String cellValue);@Overridepublic void headerFooter(String text, boolean isHeader, String tagName) {}/*** 转换表格引用为列编号** @param cellReference* 列引用* @return 表格列位置，从0开始算*/protected int getCellIndex(String cellReference) {String ref = cellReference.replaceAll("\\d+", "");int num = 0;int result = 0;for (int i = 0; i < ref.length(); i++) {char ch = cellReference.charAt(ref.length() - i - 1);num = (int) (ch - 'A' + 1);num *= Math.pow(26, i);result += num;}return result - 1;}}
三、实现abstract类和最终调用
import java.io.File;import java.io.FileInputStream;import java.util.Date;import java.util.List;import java.util.Map;import org.apache.poi.xssf.usermodel.XSSFComment;import com.anjubao.advert.monUtil;public class Test {public static void main(String[] args) {Test test=new Test();test.test();}public void test() {try {String date1=CommonUtil.getFormat("yyyy-MM-dd HH:mm:ss").format(new Date());String filePath = "F:\\0624XXX.xlsx";File file = new File(filePath);//log.info("需要读取的文件fileName:{}", file.getName());FileInputStream inputStream = new FileInputStream(file);Map<String, String> fieldsMap=null;ImportantUtil util=new ImportantUtil(new TestSheetHandler(fieldsMap));List<Object []> datas=util.parse(inputStream);String date2=CommonUtil.getFormat("yyyy-MM-dd HH:mm:ss").format(new Date());System.out.println("开始时间:"+date1+"\t结束时间:"+date2+"\t读取行数:"+datas.size());} catch (Exception e) {e.printStackTrace();}}class TestSheetHandler extends SheetContentHandler{public TestSheetHandler(Map<String, String> fieldsMap) {super(fieldsMap);}@Overridepublic void handleCell(int rowIndex, int cellIndex, String cellValue) {String field=null;try {String colName=columns.get(cellIndex);if(colName!=null) {if(fieldsMap!=null) {field=fieldsMap.get(colName);}else {field=colName;}}if(readRow!=null) {//在这里可以开始处理数据了,符合要求的可以添加到data里面,不符合的可以在ResourcesSheetHandler添加集合收集起来另行处理}else {columns.set(cellIndex, field);}} catch (IndexOutOfBoundsException e) {int size = columns.size();for (int i = cellIndex - size+1;i>0;i--){columns.add(null);}columns.set(cellIndex,field);//logger.info(e.getMessage()+" "+e.getCause());}}}}
做好上面的步骤之后,就可以在需要导入EXCEL文件的地方进行简单调用了，data的数据类型大家可以自行调节。
执行结果
开始时间:-06-24 17:50:54结束时间:-06-24 17:51:36读取行数:564780
如果这篇文章对你有用,可以点个赞再走吗。
