JavaでExcel(xlsx)をOOXMLで読み込む方法

Office Open XML(OOXML)とは、Excel 2007 以降で標準となった形式です。Excel 2003(.xls) までのバイナリではなく、XML として仕様が定められています。

.xlsx のファイルを解凍してみれば、下図のようになっています。

xl フォルダ配下には、ブック内の共有する文字列を集中管理する sharedStrings.xml が配置されています。

xl/worksheets フォルダ配下に シートに関する XML ファイルが配置されています。

つまり、これからやるのは XML を DOM 解析して Excel ブックを操作しようということです。

早速、Java で Excel(xlsx) を読み込むサンプルを作りましょう。

手順としては、xlsx を ZipInputStream で読み込み、sheet(N).xml と sharedString.xml を解析して該当セル値とマッチさせる方法となります。

既にこちらのサイトでまとめてくれておりましたので、参考にさせていただいております。

・Using Java inner classes in Pega inline activity to parse Excel XLSX files Chris' Creative Musings

構成はこうなります。

以下はサンプルソースです。

・XLSXCell.java

package xlsxparse;

/**
 * Container for a single cell of data in the spreadsheet.
 */
public class XLSXCell {

  private XLSXSharedStrings _sharedStringLookup;
  private int _columnNumber;
  private String _contents;
  private int _sharedIndex = -1;

  public XLSXCell(XLSXSharedStrings SharedStringLookup,int ColumnNumber, String Contents,boolean isSharedString) {
    _sharedStringLookup = SharedStringLookup;
    _columnNumber = ColumnNumber;
    _contents = Contents == null ? "" : Contents;
    if (isSharedString && _contents.length() > 0) {
      boolean isNumber = true;
      for (int count = 0; count < _contents.length(); count++) {
        if (!Character.isDigit(_contents.charAt(count))) {
          isNumber = false;
          break;
        }
      }
      if (isNumber)
        _sharedIndex = Integer.parseInt(_contents);
    }
  }

  /**
   * Retrieves the 0-based column number of this cell in the row.
   *
   * @return the 0-based column number.
   */
  public int getColumnNumber() {
    return _columnNumber;
  }

  /**
   * Retrieves the contents string of this cell.
   *
   * @return the contents of the cell.
   */
  public String getContents() {
    if (_sharedIndex >= 0)
      return _sharedStringLookup.getSharedString(_sharedIndex);

    return _contents;
  }
}

・XLSXRow.java

package xlsxparse;

import java.util.HashMap;

/**
 * Container for a row of data
 */
public class XLSXRow {

  private int _rowNumber;
  private HashMap<Integer,XLSXCell> _cells = new HashMap<Integer,XLSXCell>();
  private int _totalColumns = 0;

  public XLSXRow(int RowNumber) {
    _rowNumber = RowNumber;
  }

  /**
   * Retrieves the 0-based row number for this row.
   *
   * @return the number of this row.
   */
  public int getRowNumber() {
    return _rowNumber;
  }

  /**
   * Retrieves the total number of columns in this row.
   *
   * @return the number of columns.
   */
  public int getTotalColumns() {
    return _totalColumns;
  }

  /**
   * Package-private method which inserts a cell in
   * the specific column spot of a row.
   *
   * @param Cell the cell to insert.
   */
  public void addCell(XLSXCell Cell) {
    _cells.put(new Integer(Cell.getColumnNumber()),Cell);
    if (Cell.getColumnNumber() >= _totalColumns)
      _totalColumns = Cell.getColumnNumber() + 1;
  }

  /**
   * Retrieves the cell from the specific cell location.
   *
   * @param ColumnNumber the 0-based column number.
   * @return the value of a cell.
   */
  public XLSXCell getCellAt(int ColumnNumber) {
    if (ColumnNumber < _totalColumns) {
      Integer key = new Integer(ColumnNumber);
      if (_cells.containsKey(key))
        return _cells.get(key);
    }
    return new XLSXCell(null,ColumnNumber,"",false);
  }
}

・XLSXSheet.java

package xlsxparse;

import java.util.HashMap;

/**
 * Container for the data contained in
 * a single worksheet.
 */
public class XLSXSheet {

  private String _sheetName;
  private HashMap<Integer,XLSXRow> _rows = new HashMap<Integer,XLSXRow>();
  private int _totalRows = 0;

  public XLSXSheet(String SheetName) {
    _sheetName = SheetName;
  }

  /**
   * Retrieves the name of this sheet.
   *
   * @return the name of the sheet.
   */
  public String getSheetName() {
    return _sheetName;
  }

  /**
   * Retrieves the maximum number of rows in the sheet.
   *
   * @return total rows in the sheet.
   */
  public int getTotalRows() {
    return _totalRows;
  }

  /**
   * Package-private method used to add a row to this worksheet.
   *
   * @param Row the row to add.
   */
  public void addRow(XLSXRow Row) {
    _rows.put(new Integer(Row.getRowNumber()),Row);
    if (Row.getRowNumber() >= _totalRows)
      _totalRows = Row.getRowNumber() + 1;
  }

  /**
   * Package-private method used to add a cell in the specified
   * row in this worksheet.  If the row does not exist, it is
   * added.
   *
   * @param RowIndex the 0-based index of the row to uinsert into.
   * @param Cell the cell to insert.
   */
  public void addCell(int RowIndex,XLSXCell Cell) {
    Integer key = new Integer(RowIndex);
    if (_rows.containsKey(key))
      _rows.get(key).addCell(Cell);
    else {
      XLSXRow row = new XLSXRow(RowIndex);
      addRow(row);
      row.addCell(Cell);
    }
  }

  /**
   * Retrieves the contents of the specified row number.
   *
   * @param RowNumber the 0-based row number.
   * @return the contents of the row which could be empty.
   */
  public XLSXRow getRowAt(int RowNumber) {
    if (RowNumber < _totalRows) {
      Integer key = new Integer(RowNumber);
      if (_rows.containsKey(key))
        return _rows.get(key);
    }
    return new XLSXRow(RowNumber);
  }
}

・XLSXSharedStrings.java

package xlsxparse;

import java.util.HashMap;

/**
 * Provides the string lookup capabilities for an
 * xlsx xml-format excel worksheet.
 */
public class XLSXSharedStrings {

  private HashMap<Integer,String> _sharedStrings = new HashMap<Integer,String>();

  /**
   * Package-private method which inserts a shared string.
   *
   * @param Index the 0-based key for this string
   * @param SharedString the value of the string.
   */
  public void addSharedString(int Index,String SharedString) {
    _sharedStrings.put(new Integer(Index),SharedString);
  }

  /**
   * Package-private method which decodes a shared string by its index.
   *
   * @param Index the 0-based key for this string.
   * @return a string value.
   */
  public String getSharedString(int Index) {
    Integer key = new Integer(Index);
    if (_sharedStrings.containsKey(key))
      return _sharedStrings.get(key);

    return "";
  }
}

・XLSXWorkbook.java

package xlsxparse;

import java.util.ArrayList;
import java.util.List;

/**
 * Contains a workbook of sheets from an XLSX file.
 */
public class XLSXWorkbook {

  private List<XLSXSheet> _sheets = new ArrayList<XLSXSheet>();

  /**
   * Retrieves the total number of sheets in the workbook.
   *
   * @return total sheets.
   */
  public int getTotalSheets() {
    return _sheets.size();
  }

  /**
   * Package-private method which inserts a new sheet.
   *
   * @return the index of the new sheet.
   * @param SheetName
   */
  public int addSheet(String SheetName) {
    int index = _sheets.size();
    _sheets.add(new XLSXSheet(SheetName));
    return index;
  }

  /**
   * Retrieves the named sheet if it is in the workbook
   * or returns a null.  The name is checked in a case-
   * insensitive manner.
   *
   * @param SheetName the name of the sheet to find.
   * @return the sheet if gound or null if not.
   */
  public XLSXSheet getSheet(String SheetName) {
    for (XLSXSheet sheet : _sheets) {
      if (sheet.getSheetName().compareToIgnoreCase(SheetName) == 0)
        return sheet;
    }
    return null;
  }

  /**
   * Retrieves the sheet at the specified index of the
   * sheets in the workbook.  If the index is out of range,
   * an exception is thrown.
   *
   * @param SheetIndex the 0-based index for the sheet to fetch.
   * @throws IndexOutOfBoundsException if the sheet index is invalid.
   * @return the sheet in question.
   */
  public XLSXSheet getSheetAt(int SheetIndex) throws IndexOutOfBoundsException {
    if (SheetIndex < 0 || SheetIndex >= _sheets.size())
      throw new IndexOutOfBoundsException("Sheet index is invalid");

    return _sheets.get(SheetIndex);
  }

  /**
   * Package-private method which adds a new cell to the spreadsheet.
   *
   * @param SheetIndex the index of the sheet to locate.
   * @param RowNumber the 0-based number of the row to insert in to.
   * @param Cell the cell to insert.
   * @throws IndexOutOfBoundsException if the sheet index does not exist.
   */
  public void addCell(int SheetIndex,int RowIndex,XLSXCell Cell) throws IndexOutOfBoundsException {
    if (SheetIndex < 0 || SheetIndex >= _sheets.size())
      throw new IndexOutOfBoundsException("Sheet index is invalid");

    _sheets.get(SheetIndex).addCell(RowIndex,Cell);
  }
}

・XLSXContentReader.java

package xlsxparse;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

/**
 * Permits reading the basic contents of an
 * xlsx xml-format excel worksheet.
 */
public class XLSXContentReader {

  private String WORKSHEETPATH = "xl/worksheets/";
  private String SHAREDSTRINGPATH = "xl/sharedstrings.xml";
  private XLSXWorkbook _workbook = new XLSXWorkbook();
  private XLSXSharedStrings _sharedStrings = new XLSXSharedStrings();

  /**
   * Attempts to load the contents of the named XLSX
   * Excel file.
   *
   * @param Filename
   * @throws IOException if an error occurs opening or reading the file.
   */
  public XLSXContentReader(String Filename) throws IOException {
    this(new File(Filename));
  }

  /**
   * Attempts to load the contents of the XLSX Excel
   * file in the specified file object.
   *
   * @param File the file object pointing to the xlsx file.
   * @throws IOException if an error occurs opening or reading the file.
   */
  public XLSXContentReader(File File) throws IOException {
    this(new FileInputStream(File));
  }

  /**
   * Retrieves the workbook object for traversal.
   *
   * @return a workbook containing 0 or more sheets.
   */
  public XLSXWorkbook getWorkbook() {
    return _workbook;
  }

  /**
   * Attempts to load the contents of the XLSX Excel
   * file that has been opened for input.
   *
   * @param Stream the input stream of the newly open file.
   * @throws IOException if an error occurs opening or reading the file.
   */
  public XLSXContentReader(InputStream Stream) throws IOException {
    ZipInputStream zip = new ZipInputStream(Stream);

    ZipEntry entry;
    while ((entry = zip.getNextEntry()) != null) {
      if (entry.isDirectory())
        continue;

      if (entry.getName().compareToIgnoreCase(SHAREDSTRINGPATH) == 0) {
        // read the contents of the entry
        ByteArrayOutputStream buffer = new ByteArrayOutputStream(8192);
        while (true) {
          int b = zip.read();
          if (b == -1)
            break;
          buffer.write(b);
        }
        parseSharedStrings(buffer.toByteArray());
        continue;
      }

      if (!entry.getName().toLowerCase().startsWith(WORKSHEETPATH))
        continue;

      String filename = entry.getName().substring(WORKSHEETPATH.length());
      if (filename.contains("/"))
        continue;

      // read the contents of the entry
      ByteArrayOutputStream buffer = new ByteArrayOutputStream(8192);
      while (true) {
        int b = zip.read();
        if (b == -1)
          break;
        buffer.write(b);
      }
      // uncomment to see the xml itself: String contents = new String(buffer.toByteArray(),Charset.forName("UTF-8"));

      parseSpreadsheet(filename,buffer.toByteArray());
    }
    zip.close();
  }

  /**
   * Parses out the shared strings used in the encoded sheets.
   *
   * @param Contents the contents of the shared strings xml file.
   */
  private void parseSharedStrings(byte [] Contents) {
    try
      {
      DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
      Document dom = builder.parse(new ByteArrayInputStream(Contents));

      int index = 0;
      NodeList shared = dom.getElementsByTagName("si");
      for (int current = 0; current < shared.getLength(); current++) {
        Element si = (Element)shared.item(current);

        NodeList textData = si.getElementsByTagName("t");
        if (textData.getLength() > 0) {
          Element el = (Element)textData.item(0);
            if ( el.getFirstChild() != null ) { // Add by Ken.
              String value = el.getFirstChild().getNodeValue();
              _sharedStrings.addSharedString(index++,value);
            } else {
              _sharedStrings.addSharedString(index++,null);// Add by Ken.
            }
        }
      }
    } catch (Exception ee) {
      // do something appropriate here
    }
  }

  /**
   * Handles parsing the contents of the spreadsheet
   * from the file and attempts to create a new spreadsheet.
   */
  private void parseSpreadsheet(String Filename,byte [] Contents) {
    String sheetName = Filename;
    if (Filename.contains("."))
      sheetName = Filename.substring(0,Filename.lastIndexOf("."));

    int sheetIndex = _workbook.addSheet(sheetName);

    try {
      DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
      Document dom = builder.parse(new ByteArrayInputStream(Contents));


      NodeList sheetData = dom.getElementsByTagName("sheetData");
      for (int current = 0; current < sheetData.getLength(); current++)
        parseSheetData((Element)sheetData.item(current),sheetIndex);
    } catch (Exception ee) {
      // do something appropriate here
    }
  }

  /**
   * Handles parsing the contents of a sheet data node in
   * the spreadsheet data.
   *
   * @param SheetData the DOM node of the sheet data tree.
   * @param SheetIndex the index of this sheet in the workbook.
   */
  private void parseSheetData(Element SheetData,int SheetIndex) throws Exception {
    NodeList rowData = SheetData.getElementsByTagName("row");
    for (int curRow = 0; curRow < rowData.getLength(); curRow++) {
      Element row = (Element)rowData.item(curRow);

      try {
        int rowIndex = Integer.parseInt(row.getAttribute("r")) - 1;    // 0-based row number
        NodeList colData = row.getElementsByTagName("c");
        for (int curCol = 0; curCol < colData.getLength(); curCol++) {
          Element col = (Element)colData.item(curCol);
          int colIndex = DecodeColumnNumber(col.getAttribute("r"));
          boolean isSharedString = false;
          String typeString = col.getAttribute("t");       // t="s" for shared string
          if (typeString != null && typeString.compareToIgnoreCase("s") == 0)
            isSharedString = true;

          NodeList valueData = col.getElementsByTagName("v");
          if (valueData.getLength() == 0) // Add by Ken
            valueData = col.getElementsByTagName("t"); // Add by Ken for t="inlineStr"
          if (valueData.getLength() > 0) {
            Element el = (Element)valueData.item(0);
            //String value = el.getFirstChild().getNodeValue();// Mod by Ken
            String value = (el.getFirstChild() != null) ? el.getFirstChild().getNodeValue() : "";
            _workbook.addCell(SheetIndex,rowIndex,
                             new XLSXCell(_sharedStrings,colIndex,value,isSharedString));
          }
        }
      } catch (Exception ee) {
        // do something appropriate here
        System.out.println(ee.getCause());
      }
    }
  }

  /**
   * Decodes a column-row attribute into a valid 0-based
   * column number.  Column-row attributes are like A5, AB10,
   * or HZ1091.  Columns number from A-Z, then AA-AZ, then BA-BZ, etc.
   *
   * @param Attribute the letter-number column/row.
   * @return a 0-based index.
   */
  private int DecodeColumnNumber(String Attribute) { // Mod by Ken.
    // 2バイト目が英数の場合
    if (Character.isLetter(Attribute.charAt(1))) {
      // 3バイト目が英数の場合
      if ( Character.isLetter(Attribute.charAt(2) )) {  // AAA-XFD
        int multiplier1 = (Character.toUpperCase(Attribute.charAt(0)) - 'A') + 1;
        int multiplier2 = (Character.toUpperCase(Attribute.charAt(1)) - 'A') + 1;
        int column = Character.toUpperCase(Attribute.charAt(2)) - 'A';
        return ((26 * 26) * multiplier1) + (multiplier2 * 26) + column;
      } else { //AA-ZZ
        int multiplier = (Character.toUpperCase(Attribute.charAt(0)) - 'A') + 1;
        int column = Character.toUpperCase(Attribute.charAt(1)) - 'A';
        return multiplier * 26 + column;
      }
    }
    return Character.toUpperCase(Attribute.charAt(0)) - 'A';
  }
}

今回、色々と試している時に、下図のような sharedStrings.xml が存在しました。

t タグの終わりだけです。これが原因で値のマッチングがおこなえないケースがあったので、XLSXContentReader クラスを手入れをしています。

また、sharedStrings.xml を 使わない inlineStr の場合にも読み込めないケースがありましたので同クラスを手入れしました。

他にもZZ列までしか読み込んでくれなかったものをXDF列まで読み込めるように改善したり、ヌルポ対応したりと、ちょこちょこ手入れをしています。

早速テストしてみましょう。実測値も図ります。

・ParseXLSXTestbed.java

package xlsxparse;

import java.util.Date;

public class ParseXLSXTestbed {

  public static void main(String [] args) throws Exception {
    System.out.println(new Date());
    // now for the code itself proper -- see how the inline code
    // can use the classes and interact with them.
    XLSXWorkbook workbook = (new XLSXContentReader("C:/temporary/test.xlsx")).getWorkbook();

    for (int curSheet = 0; curSheet < workbook.getTotalSheets(); curSheet++) {
      XLSXSheet sheet = workbook.getSheetAt(curSheet);

      System.out.println("Sheet name is " + sheet.getSheetName());
      System.out.println("Sheet has " + sheet.getTotalRows() + " rows");
      for (int curRow = 0; curRow < sheet.getTotalRows(); curRow++) {
        XLSXRow row = sheet.getRowAt(curRow);
        System.out.println("  Row " + curRow + " has " + row.getTotalColumns() + " columns");

        for (int curCol = 0; curCol < row.getTotalColumns(); curCol++) {
          XLSXCell cell = row.getCellAt(curCol);
          System.out.println("    Col " + curCol + " contains value of "" + cell.getContents() + """);
        }
      }
    }
    System.out.println(new Date());
  }
}

サンプルファイルはこんな感じです。5000行で30列に文字列が埋まった状態です。

結果はこうなりました。

Tue Oct 24 13:28:05 JST 2017
Sheet name is sheet1
Sheet has 5001 rows
(・・・中略・・・)
Tue Oct 24 13:28:12 JST 2017

ふむふむ、わずか 7 秒。いい感じですね＾＾

今回は参考にしたサイトのほぼコピーとなってしまいましたが、うまくできてよかったです。

既に Excel 2003, Excel 2007 は Microsoft のサポートが切れておりますので、これからは xlsx だけを利用対象としても問題ないでしょう。

ただ OOXML の操作が必ずしも正しいわけではありません。ファイルが破損するリスクも存在します。そのため、できるだけ POI のようなライブラリを使うことをおすすめします(ダメってクライアントもあるから不思議・・・)。ただ、こんな方法もあるよってことは頭の中に入れておくといいかと思います。

くれぐれも java.lang.OutOfMemoryError の発生にはご注意を＾＾；

最近はJETTというPOIのラッパーライブラリがお気に入りです。Mavenリポジトリにも登録されているためライブラリ管理も楽チン。コーディング量も少ないし、レスポンスも良い。しかも割と頻繁にバージョンアップしている感じがあります。こちらのページで紹介しているのでチェックしてみてくださいね。