トッカンソフトウェア

メモリ抑えめのExcel読み込み(Apache POI SAX)

今回はSAXによるExcel読み込みをやります。

Excelファイル(.xlsx)は、実はXMLファイルなどを圧縮したファイルです (拡張子を.zipに変更すると解凍できます)。
このXMLファイルを直接読み込んでみます。

XMLファイルを取り扱うにはDOMとSAXの方式があります。

DOMはまず全体を読み込み、その後にアクセスしたいデータの場所を指定してアクセスします。
SAXはファイルを読み込みながら、要素の開始、終了などのイベントごとにメソッドが呼ばれ、該当する要素が読み込まれたときに処理を
行うことができます。

今回のサンプルはXMLを読み込みながら、セル情報をマップに入れて全部読み終わった後にマップに対し処理を行っています。
本来の使い方ではないかもしれませんが、それでもメモリ消費量はDOMより少なくなっていました。

環境構築はExcel出力を参照して下さい。

サンプル

Excelファイルパスとシート名を渡すとList<Map<Integer, String>>を返すメソッドを作ってみました(XssfSax.read())。
Mapには列番号、セル値をキー、バリューにセットし、Listは行番号に対応します。両方とも0から始まります。

Poi ver4のときに作成しましたが、ver5ではコンパイルエラーが出ました。
それぞれのバージョンに向けたものを載せます。

XssfSax.java(Poi ver5)

				
package test;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
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;

public class XssfSax {

	private HashMap<String, String> workMap = null;
	private String workKey = "";

	public List<Map<Integer, String>> read(String fileName, String sheetName) throws Exception {
		workMap = new HashMap<>();
		List<Map<Integer, String>> ret = new ArrayList<>();

		OPCPackage pkg = OPCPackage.open(fileName, PackageAccess.READ);
		try {
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sst = r.getSharedStringsTable();

			XMLReader parser = fetchSheetParser(sst);

			Iterator<InputStream> sheets = r.getSheetsData();
			while (sheets.hasNext()) {
				InputStream sheet = sheets.next();
				if (sheetName.equals(((XSSFReader.SheetIterator) sheets).getSheetName())) {
					InputSource sheetSource = new InputSource(sheet);
					parser.parse(sheetSource);
					sheet.close();
				}
			}
		} finally {
			pkg.close();
		}

		for (String colRow : workMap.keySet()) {

			int sepIdx = 0;
			int colRowLen = colRow.length();

			for (int i = 0; i < colRowLen; i++) {
				if (colRow.charAt(i) < 'A') {
					sepIdx = i;
					break;
				}
			}

			String colStr = colRow.substring(0, sepIdx);
			String rowStr = colRow.substring(sepIdx);
			int x = 0;
			int y = Integer.parseInt(rowStr);
			int colStrLen = colStr.length();
			for (int i = 0; i < colStrLen; i++) {
				int colIdx = colStr.charAt(i) - 'A' + 1;
				for (int j = 1; j < colStrLen - i; j++) {
					colIdx = colIdx * 26;
				}
				x += colIdx;
			}
			x--;
			y--;

			Map<Integer, String> map = null;
			int retSize = ret.size();
			if (y < retSize) {
				map = ret.get(y);
			} else {
				int addCnt = y - retSize + 1;
				for (int i = 0; i < addCnt; i++) {
					map = new HashMap<>();
					ret.add(map);
				}
			}
			map.put(x, workMap.get(colRow));
		}

		workMap.clear();
		workMap = null;
		return ret;
	}

	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader();
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	private class SheetHandler extends DefaultHandler {
		private SharedStringsTable sst;
		private String lastContents;
		private boolean nextIsString;
		private boolean inlineStr;
		String cellType = null;

		private SheetHandler(SharedStringsTable sst) {
			this.sst = sst;
		}

		@Override
		public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
			// c => cell
			if (name.equals("c")) {
				// Print the cell reference
				// System.out.print(attributes.getValue("r") + " - ");
				workKey = attributes.getValue("r");
				// Figure out if the value is an index in the SST
				cellType = attributes.getValue("t");
				if (cellType != null && cellType.equals("s")) {
					nextIsString = true;
				} else {
					nextIsString = false;
				}

				inlineStr = cellType != null && cellType.equals("inlineStr");
			}
			// Clear contents cache
			lastContents = "";
		}

		@Override
		public void endElement(String uri, String localName, String name) throws SAXException {
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if (nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = sst.getItemAt(idx).getString();
				nextIsString = false;
			}
			// v => contents of a cell
			// Output after we've seen the string contents
			// if (name.equals("v")) {
			// System.out.println(lastContents);
			// }
			if (name.equals("v") || (inlineStr && name.equals("c"))) {
				// 0.14が0.14000000000000001になる場合を考慮
				if (lastContents.contains(".") && cellType == null) {
					try {
						double d = Double.parseDouble(lastContents);
						lastContents = BigDecimal.valueOf(d).toPlainString();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				workMap.put(workKey, lastContents);
			}
		}

		@Override
		public void characters(char[] ch, int start, int length) throws SAXException { // NOSONAR
			lastContents += new String(ch, start, length);
		}
	}
}

XssfSax.java(Poi ver4)

				
package test;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
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;

public class XssfSax {

	private HashMap<String, String> workMap = null;
	private String workKey = "";

	public List<Map<Integer, String>> read(String fileName, String sheetName) throws Exception {
		workMap = new HashMap<>();
		List<Map<Integer, String>> ret = new ArrayList<>();

		OPCPackage pkg = OPCPackage.open(fileName, PackageAccess.READ);
		try {
			XSSFReader r = new XSSFReader(pkg);
			SharedStringsTable sst = r.getSharedStringsTable();

			XMLReader parser = fetchSheetParser(sst);

			Iterator<InputStream> sheets = r.getSheetsData();
			while (sheets.hasNext()) {
				InputStream sheet = sheets.next();
				if (sheetName.equals(((XSSFReader.SheetIterator) sheets).getSheetName())) {
					InputSource sheetSource = new InputSource(sheet);
					parser.parse(sheetSource);
					sheet.close();
				}
			}
		} finally {
			pkg.close();
		}

		for (String colRow : workMap.keySet()) {

			int sepIdx = 0;
			int colRowLen = colRow.length();

			for (int i = 0; i < colRowLen; i++) {
				if (colRow.charAt(i) < 'A') {
					sepIdx = i;
					break;
				}
			}

			String colStr = colRow.substring(0, sepIdx);
			String rowStr = colRow.substring(sepIdx);
			int x = 0;
			int y = Integer.parseInt(rowStr);
			int colStrLen = colStr.length();
			for (int i = 0; i < colStrLen; i++) {
				int colIdx = colStr.charAt(i) - 'A' + 1;
				for (int j = 1; j < colStrLen - i; j++) {
					colIdx = colIdx * 26;
				}
				x += colIdx;
			}
			x--;
			y--;

			Map<Integer, String> map = null;
			int retSize = ret.size();
			if (y < retSize) {
				map = ret.get(y);
			} else {
				int addCnt = y - retSize + 1;
				for (int i = 0; i < addCnt; i++) {
					map = new HashMap<>();
					ret.add(map);
				}
			}
			map.put(x, workMap.get(colRow));
		}

		workMap.clear();
		workMap = null;
		return ret;
	}

	private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader();
		ContentHandler handler = new SheetHandler(sst);
		parser.setContentHandler(handler);
		return parser;
	}

	private class SheetHandler extends DefaultHandler {
		private SharedStringsTable sst;
		private String lastContents;
		private String cellType;
		private boolean nextIsString;
		private boolean inlineStr;

		private SheetHandler(SharedStringsTable sst) {
			this.sst = sst;
		}

		@Override
		public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
			// c => cell
			if (name.equals("c")) {
				workKey = attributes.getValue("r");
				// Print the cell reference
				// System.out.print(attributes.getValue("r") + " - ");
				// Figure out if the value is an index in the SST
				cellType = attributes.getValue("t");
				nextIsString = cellType != null && cellType.equals("s");
				inlineStr = cellType != null && cellType.equals("inlineStr");
			}
			// Clear contents cache
			lastContents = "";
		}

		@Override
		public void endElement(String uri, String localName, String name) throws SAXException {
			// Process the last contents as required.
			// Do now, as characters() may be called more than once
			if (nextIsString) {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
				nextIsString = false;
			}

			// v => contents of a cell
			// Output after we've seen the string contents
			if (name.equals("v") || (inlineStr && name.equals("c"))) {

				// 0.14が0.14000000000000001になる場合を考慮
				if (lastContents.contains(".") && cellType == null) {
					try {
						double d = Double.parseDouble(lastContents);
						lastContents = BigDecimal.valueOf(d).toPlainString();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				// System.out.println(lastContents);
				workMap.put(workKey, lastContents);
			}
		}

		@Override
		public void characters(char[] ch, int start, int length) throws SAXException { // NOSONAR
			lastContents += new String(ch, start, length);
		}
	}
}

PoiTest.java

				

package test;

import java.util.List;
import java.util.Map;

public class PoiTest {

	public static void main(String[] args) {
		// ファイルに保存
		String filename = "C:\\work\\hp\\workbook.xlsx";

		XssfSax xs = new XssfSax();

		try {
			List<Map<Integer, String>> ret = xs.read(filename, "しーと");
			for (Map<Integer, String> map : ret) {
				System.out.println(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
サンプルのXssfSaxクラスはApache POIのサンプルコードを元に作っています。 POIのサンプルコードは標準出力にExcelの内容を出力していますが、
ここではExcelの内容をMapに格納し、その後List <Map>の形に変更しています

プログラム中に"c"とか"r"とか出てきますが、以下の意味になります。
記号 タイプ 説明
c 要素 セルの要素。
r 属性 c要素の属性。セルの位置。
t 属性 c要素の属性。セルタイプ。sの場合、sharedStrings.xmlにデータあり。inlineStrの場合、リッチテキストを含む。
v 要素 c要素の子要素。セルの値。セルタイプがsの場合、sharedStrings.xmlのインデックス


データを取得するにはシートのベース情報となるXMLファイル(sheet1.xmlなど)と文字列を抜き出したXMLファイル(sharedStrings.xml)の
2種類のファイルにアクセスします。全ての文字列がsharedStrings.xmlにあるのではなく、シートのXMLで完結する場合もあります。


■sharedStrings.xmlよりデータを取得する例
→ セルの位置：A1、セル値：てすと 
				
sheet1.xml
<c r="A1" s="1" t="s">
<v>0</v>
</c>

sharedStrings.xml
<si>
<t xml:space="preserve">てすと</t>
</si>

■sharedStrings.xmlよりデータを取得しない例
→ セルの位置：C4、セル値：123 
				
sheet1.xml
<c r="C4" s="0" t="n">
<v>123</v>
</c>


ページのトップへ戻る