OpenXML SDKを使って大量のセルの値を高速で取得する方法は？
質問
2013年9月25日水曜日 5:16
[方法] スプレッドシート ドキュメントのセルの値を取得する (Open XML SDK)
http://msdn.microsoft.com/ja-jp/library/office/hh498534.aspx
以上のページに記述されている方法でセルの値を取得したのですが、大量のセルの値を取得しようとした場合、処理時間がかなりかかります。
たとえば、上記ページの以下のコードでは約10ミリ秒の処理速度でした。
Cell theCell = wsPart.Worksheet.Descendants<Cell>().
* Where(c => c.CellReference == addressName).FirstOrDefault();*
1000行40列の40000セルの値を取得する場合は400秒、すなわち6分以上の時間を要します。
大量のセルの値を高速に取得する場合、どのような方法があるのでしょうか？
すべての返信 (7)
2013年9月25日水曜日 12:39 ✅回答済み
セルのアドレス(A1とかAZ1000とか)をいちいち探し回っていたら遅くなる。
特に未編集のセルはファイルに一切情報が入っていないのでWhereで探すのは全セルを調べてようやく存在しないことが判定できるという非効率な手段です。
OpenXMLのWorksheetの構造は、Worksheetには複数の行が含まれていて、行には複数のセルが含まれるという構造になってます。
先に目的の行を探し、行の中からセルを探すと速くなります。
using System;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string fileName = @"C:\Book1.xlsx";
string sheetName = "Sheet1";
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();
object[,] values = XLSXTool.GetValues(fileName, sheetName, 1, 10000, 1, 40);
watch.Stop();
Console.WriteLine(watch.Elapsed.ToString());
}
}
class XLSXTool
{
static XLSXTool()
{
reg = new System.Text.RegularExpressions.Regex("[A-Z];", System.Text.RegularExpressions.RegexOptions.Compiled);
}
private static System.Text.RegularExpressions.Regex reg;
private static uint GetColumnIndex(Cell c)
{
return ToColumnIndex_Base1(c.CellReference.ToString());
}
/// <summary>行の名前 AとかAZとかから行番号に変換</summary>
/// <param name="columnName"></param>
/// <returns></returns>
private static uint ToColumnIndex_Base1(string columnName)
{
columnName = columnName.ToUpper();
var match = reg.Match(columnName);
columnName = match.Value;
uint columnIndex = 0;
foreach (char c in columnName)
{
columnIndex = columnIndex * 26 ; ((uint)c - (uint)'A' ; 1);
}
return columnIndex;
}
private static Dictionary<int, string> GetStringTable(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
return GetStringTable(wbPart);
}
}
/// <summary>共有文字列参照用の辞書を作る</summary>
/// <param name="wbPart"></param>
/// <returns></returns>
private static Dictionary<int, string> GetStringTable(WorkbookPart wbPart)
{
Dictionary<int, string> dic = new Dictionary<int, string>();
foreach (SharedStringTablePart tablePart in wbPart.GetPartsOfType<SharedStringTablePart>())
{
if (tablePart != null)
{
SharedStringTable table = tablePart.SharedStringTable;
int count = int.Parse(table.Count);
int index = 0;
foreach (SharedStringItem item in table)
{
dic.Add(index, item.InnerText);
index;;;
}
}
}
return dic;
}
private static Sheet GetSheet(WorkbookPart wbPart, string sheetName)
{
foreach (Sheet sheet in wbPart.Workbook.Descendants<Sheet>())
{
if (sheet.Name == sheetName)
{
return sheet;
}
}
return null;
}
/// <summary>指定行番号からRowを指定行数列挙する</summary>
/// <param name="r_base1">開始行番号</param>
/// <param name="height">行数</param>
/// <returns>何も操作していない行の場合はnullが返る</returns>
private static IEnumerable<Row> GetRows(string fileName, string sheetName, uint r_base1, uint height)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
Sheet sheet = GetSheet(wbPart, sheetName);
if (sheet.Name == sheetName)
{
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
foreach (Row row in GetRows(wsPart, r_base1, height))
{
yield return row;
}
}
}
}
/// <summary>指定行番号からRowを指定行数列挙する</summary>
/// <param name="r_base1">開始行番号</param>
/// <param name="height">行数</param>
/// <returns>何も操作していない行の場合はnullが返る</returns>
private static IEnumerable<Row> GetRows(WorksheetPart wsPart, uint r_base1, uint height)
{
uint rmax = r_base1 ; height - 1;
uint nextIndex = r_base1;
foreach (Row row in wsPart.Worksheet.Descendants<Row>())
{
uint rowIndex = row.RowIndex;
if (rowIndex < r_base1)
{
}
else if (rowIndex > rmax)
{
for (uint ui = nextIndex; ui < rmax; ui;;)
{
yield return null;
}
break;
}
else
{
for (uint ui = nextIndex; ui < rowIndex; ui;;)
{
yield return null;
}
yield return row;
nextIndex;;;
if (nextIndex > rmax)
{
break;
}
}
}
for (uint ui = nextIndex; ui < rmax; ui;;)
{
yield return null;
}
}
/// <summary>行に含まれるセルを指定列番号から指定列数列挙する</summary>
/// <param name="row">列挙するRow</param>
/// <param name="c_base1">開始列番号</param>
/// <param name="width">列数</param>
/// <returns>何も操作していないセルの場合はnullが返る</returns>
private static IEnumerable<Cell> GetCells(Row row, uint c_base1, uint width)
{
if (row == null)
{
while (width-- > 0)
{
yield return null;
}
}
else
{
uint cmax = c_base1 ; width - 1;
uint nextIndex = c_base1;
foreach (Cell cell in row.Elements<Cell>())
{
uint columnIndex = XLSXTool.GetColumnIndex(cell);
if (columnIndex < c_base1)
{
}
else if (columnIndex > cmax)
{
for (uint ui = nextIndex; ui < cmax; ui;;)
{
yield return null;
}
break;
}
else
{
for (uint ui = nextIndex; ui < columnIndex; ui;;)
{
yield return null;
}
yield return cell;
nextIndex;;;
if (nextIndex > cmax)
{
break;
}
}
}
for (uint ui = nextIndex; ui < cmax; ui;;)
{
yield return null;
}
}
}
/// <summary>セルの値を取得する</summary>
/// <param name="cell"></param>
/// <param name="stringDictionary">共有文字列の一覧</param>
/// <returns></returns>
private static object GetValue(Cell cell, Dictionary<int, string> stringDictionary)
{
if (cell == null)
{
return null;
}
else if (cell.DataType == null)
{
return cell.CellValue.InnerText;
}
else
{
string text = cell.CellValue.InnerText;
switch (cell.DataType.Value)
{
case CellValues.Boolean:
return text.Trim() == "0";
case CellValues.Date:
return DateTime.Parse(text);
case CellValues.Error:
return new Exception(text);
case CellValues.InlineString:
return cell.CellValue.InnerText;
case CellValues.Number:
return double.Parse(text);
case CellValues.SharedString:
return stringDictionary == null ? string.Empty : stringDictionary[int.Parse(text)];
case CellValues.String:
return cell.CellValue.InnerText;
default:
break;
}
}
return null;
}
/// <summary>XLSXから範囲を指定して値を取り出す</summary>
/// <param name="fileName">XLSXのファイルパス</param>
/// <param name="sheetName">シート名</param>
/// <param name="r_base1">取り出し開始すつ行番号</param>
/// <param name="height">取り出す行数</param>
/// <param name="c_base1">取り出し開始する列番号</param>
/// <param name="width">取り出す列数</param>
/// <returns></returns>
public static object[,] GetValues(string fileName, string sheetName, uint r_base1, uint height, uint c_base1, uint width)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
object[,] retval = new object[height, width];
WorkbookPart wbPart = document.WorkbookPart;
Sheet sheet = GetSheet(wbPart, sheetName);
if (sheet != null)
{
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id));
Dictionary<int, string> dic = XLSXTool.GetStringTable(wbPart);
uint r = 0;
foreach (Row row in XLSXTool.GetRows(fileName, sheetName, r_base1, height))
{
uint c = 0;
foreach (Cell cell in XLSXTool.GetCells(row, c_base1, width))
{
if (cell != null)
{
object value = XLSXTool.GetValue(cell, dic);
retval[r, c] = value;
}
c;;;
}
r;;;
}
}
return retval;
}
}
}
}
10000x40の全セルに簡単な数値が入っている場合で1秒未満。
10000x40の全セルに重複しない8文字程度の文字入っている場合で10秒未満。
#もっと早くしたい場合はxlsxをzipとして展開して、入っているxmlファイルをパース
個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)
2013年9月25日水曜日 7:20
Where()は検索処理です。40000回ひたすら検索を繰り返す処理をするつもりなのでしょうか？
2013年9月25日水曜日 8:09
Where()は検索処理です。40000回ひたすら検索を繰り返す処理をするつもりなのでしょうか？
それは避けたいです。検索してセルを特定せずに、大量のセルの値を高速に取得することは可能でしょうか。
たとえば、"A1:AZ1000"という範囲のセルの値を高速に取得したい場合、Where()を使わずにどうすればいいのでしょうか。
よろしくご教授のほど、お願いいたします。
2013年9月25日水曜日 8:47
試してませんが、Excel側でテーブルを定義することが可能なら、Table クラスを使って高速に読込が出来そうな気がします。
2013年9月25日水曜日 10:45
FirstOrDefault()は検索結果の中から先頭の１つだけを取得するメソッドです。"A1:AZ1000"など複数のセル範囲を取得する用途には向きません。
この辺りはほとんどOpenXML SDKと関係ない話題です。Where()もFirstOrDefault()もLINQメソッドです。LINQの扱い方がわからないのならforeachでもなんでも理解できるアクセス手段を検討してください。
2013年9月25日水曜日 19:48
具体的なコードを示してのご説明、ありがとうございます。
列単位でセルの値を取り出すように修正してみます。
2013年9月25日水曜日 22:55
ごめんなさい、説明文の行と列を全部書き間違えてました。
OpenXMLのWorksheetの構造は、Worksheetには複数の行が含まれていて、行には複数のセルが含まれるという構造になってます。
先に目的の行を探し、行の中からセルを探すと速くなります。
が正しいです。WorkSheet->Row->Cellの順です
#strikeタグが使えない…
個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)