POI导入Excel2007大量数据的xlsx文件

最新推荐文章于 2022-03-03 17:32:47 发布

用户昵称1

最新推荐文章于 2022-03-03 17:32:47 发布

阅读量1.5k

点赞数

文章标签： java poi

本文链接：https://blog.csdn.net/qq_42382818/article/details/121662330

版权

POI导入Excel2007大量数据的xlsx文件

记录web前端导入excel2007大型xlsx文件时出现的OOM错误，excel2007的Sheet是xml文件，采用处理xml的Sax ContentHandler后解决该问题。

POIUtils类

前端上传的excel xlsx文件

package com.bs.common.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.springframework.web.multipart.MultipartFile;
import com.bs.core.po.Dbat2002;

public class POIUtils {

	public static List<Dbat2002> excel2Record(MultipartFile file) throws Exception {
		List<Dbat2002> list = null;
        try {
            //创建workbook对象
        	Excel2007Reader ered = new Excel2007Reader();	
        	InputStream is = file.getInputStream();
        	OPCPackage opk = OPCPackage.open(is);
        	list = ered.processOneSheet(opk);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

}

Excel2007Reader类

Dbat2002s存放读取出的数据，业务处理主要放在startElement和endElement这两个方法中。

package com.bs.common.utils;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import com.bs.core.po.Dbat2002;

public class Excel2007Reader
{
	
	public List<Dbat2002> processOneSheet(OPCPackage pkg) throws Exception {

		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();
		XMLReader parser = fetchSheetParser(sst);
		// 获得第一个sheet
		InputStream sheet2 = r.getSheet("rId1");
		InputSource sheetSource = new InputSource(sheet2);
		parser.parse(sheetSource);
		sheet2.close();
		return SheetHandler.dbat2002s;
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser =
			XMLReaderFactory.createXMLReader(
				"org.apache.xerces.parsers.SAXParser"
			);
		ContentHandler handler = (ContentHandler) new SheetHandler(sst);
		parser.setContentHandler(handler);
		
		return parser;
	}

	private static class SheetHandler extends DefaultHandler {
		//存放读取的excel数据
		private static List<Dbat2002> dbat2002s = new ArrayList<Dbat2002>(); 
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		private Dbat2002 db; 
		//用来单元格记录是第几列数据
		private int k;  

		private SheetHandler(SharedStringsTable sst) {
			this.db = new Dbat2002();
			this.sst = sst;
			this.k = 1;
		}

		//元素开始时的handler
		public void startElement(String uri, String localName, String name,
		                         Attributes attributes) throws SAXException {
			if(name.equals("c")) {
				System.out.print(attributes.getValue("r") + " - ");
				// 获取单元格类型
				String cellType = attributes.getValue("t");
				if(cellType != null && cellType.equals("s")) {
					nextIsString = true;
				} else {
					nextIsString = false;
				}
			}
			lastContents = "";
		}

		//元素结束时的handler
		public void endElement(String uri, String localName, String name)
			throws SAXException {

			if(nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getItemAt(idx).toString()).toString();
				nextIsString = false;
				         	
            }
			// v => 单元格内容
			if(name.equals("v")) {
				switch (k) {
				case 1:
					db.setP01(lastContents);
					break;
				case 2:
					db.setP02(lastContents);
					break;
				case 3:
            		//excel存储的日期时间为double类型，需要转为date类型
					double date = Double.parseDouble(lastContents);
            		db.setP03(double2Date(date));
            		break;
				case 4:
                    db.setP04(Double.parseDouble(lastContents));
                    break;
                case 5:
                    db.setP05(Double.parseDouble(lastContents));
                    break;
                case 6:
                    db.setP06(Double.parseDouble(lastContents));
                    break;
                case 7:
                    db.setP07(Double.parseDouble(lastContents));
                    break;
                case 8:
                    db.setP08(Double.parseDouble(lastContents));
                    break;
                case 9:
                    db.setP09(Double.parseDouble(lastContents));
                    break;
                case 10:
                    db.setP10(Double.parseDouble(lastContents));
                    break;
                case 11:
                    db.setP11(Double.parseDouble(lastContents));
                    break;
				case 12:
					db.setP12(lastContents);
					break;
				case 13:
					db.setP13(lastContents);
					break;
				case 14:
                    db.setP14(Double.parseDouble(lastContents));
                    break;
                case 15:
                    db.setP15(Double.parseDouble(lastContents));
                    break;
				case 16:
					db.setP16(lastContents);
					break;
				case 17:
                    db.setP17(Double.parseDouble(lastContents));
                    break;
				case 18:
					db.setP18(lastContents);
					break;
				case 19:
					db.setP19(lastContents);
					break;
				case 20:
					db.setP20(lastContents);
					break;
				}
				k++;
			}
			//判断单元格为行末的操作
			if(localName.equals("row")) {
				dbat2002s.add(db);
				db = new Dbat2002();
				k = 1;
			}
		}
		
		//读取元素间内容时的handler
		public void characters(char[] ch, int start, int length)
			throws SAXException {
			lastContents += new String(ch, start, length);
		}
		
		//double类型转日期类型
		public String double2Date(Double d){
		    String t;
		    Calendar base = Calendar.getInstance();   
			SimpleDateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
			base.set(1899, 11, 30, 0, 0, 0);
			base.add(Calendar.DATE, d.intValue());   
			base.add(Calendar.MILLISECOND,(int)((d % 1) * 24 * 60 * 60 * 1000));   
			t = outFormat.format(base.getTime());
		    return t;

		}

	}
}