public static System.Data.DataTable GetTable(SpreadsheetDocument doc, string sheetName)
{
var book = doc.WorkbookPart;
var sheet = book.Workbook.Descendants<Sheet>().First<Sheet>(s => s.Name == sheetName);
if (sheet != null)
{
var sp = book.GetPartById(sheet.Id.Value) as WorksheetPart;
if (sp != null)
{
////データ範囲の確認
//ExcelRange dim =
// new ExcelRange(sp.Worksheet.Descendants<SheetDimension>().First<SheetDimension>().Reference.Value);
//保存用テーブル
DataTable dt = new DataTable();
////仮のカラム
//for (int i = 1; i <= dim.End.ColIndex; i++)
//{
// dt.Columns.Add(String.Format("Column{0}", i));
//}
//共有文字取得
var ss = book.SharedStringTablePart.SharedStringTable.ChildElements;
string[] s_string = new string[ss.Count];
var t_ss = ss[0];
for (int i = 0; i < s_string.Length; i++)
{
s_string[i] = t_ss.InnerText;
t_ss = t_ss.NextSibling();
}
//データ取得
var sd = sp.Worksheet.Descendants<SheetData>().First<SheetData>();
var rows = sd.ChildElements;
var row = rows[0] as Row;
while (row != null)
{
if (row.RowIndex.Value == 1)
{
//1行目はカラム名
#region カラム設定
for (int i = 0; i < row.ChildElements.Count; i++)
{
var c = row.ChildElements[i] as Cell;
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
int sst_index = Convert.ToInt32(c.CellValue.InnerText);
dt.Columns.Add(s_string[sst_index]);
}
else if(c.CellValue != null )
{
dt.Columns.Add(c.CellValue.InnerText);
}
}
#endregion
}
else
{
//2行目以降はデータ
#region データ設定
var dr = dt.NewRow();
Cell c = row.FirstChild as Cell;
while (c != null )
{
ExcelReference celRef = new ExcelReference(c.CellReference.Value);
int i = celRef.ColIndex - 1;
if( i < dt.Columns.Count)
{
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
int sst_index = Convert.ToInt32(c.CellValue.InnerText);
dr[i] = s_string[sst_index];
}
else if (c.CellValue == null)
{
dr[i] = null;
}
else
{
dr[i] = c.CellValue.InnerText;
}
}
c = c.NextSibling<Cell>();
}
dt.Rows.Add(dr);
//System.Diagnostics.Debug.WriteLine(dr[0]);
#endregion
}
row = row.NextSibling<Row>();
}
return dt;
}
}
return null;
}
public ExcelRange(string r)
{
int index = r.IndexOf(':');
Begin = new ExcelReference(r.Substring(0, index));
End = new ExcelReference(r.Substring(index+1));
}
public static object[,] GetMap(SpreadsheetDocument doc, string sheetName)
{
var book = doc.WorkbookPart;
var sheet = book.Workbook.Descendants<Sheet>().First<Sheet>(s => s.Name == sheetName);
if (sheet != null)
{
var sp = book.GetPartById(sheet.Id.Value) as WorksheetPart;
if (sp != null)
{
////データ範囲の確認
ExcelRange dim =
new ExcelRange(sp.Worksheet.Descendants<SheetDimension>().First<SheetDimension>().Reference.Value);
//保存用マップ
object[,] map = new object[dim.End.ColIndex, dim.End.RowIndex];
//共有文字取得
var ss = book.SharedStringTablePart.SharedStringTable.ChildElements;
string[] s_string = new string[ss.Count];
var t_ss = ss[0];
for (int i = 0; i < s_string.Length; i++)
{
var txel = t_ss.Descendants<Text>().Where( s => !(s.Parent is PhoneticRun));
var texts = String.Join("", txel.Select( s => s.Text).ToArray());
s_string[i] = texts;
//if (texts != t_ss.FirstChild.InnerText)
//{
// System.Diagnostics.Debug.WriteLine(String.Format("Warn: {0} != {1}", texts, t_ss.FirstChild.InnerText));
//}
t_ss = t_ss.NextSibling();
}
//データ取得
var sd = sp.Worksheet.Descendants<SheetData>().First<SheetData>();
var rows = sd.ChildElements;
var row = rows[0] as Row;
while (row != null)
{
#region データ設定
Cell c = row.FirstChild as Cell;
while (c != null)
{
ExcelReference celRef = new ExcelReference(c.CellReference.Value);
int i = celRef.ColIndex - 1;
int j = celRef.RowIndex - 1;
if (i < map.GetLength(0) && j < map.GetLength(1))
{
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
int sst_index = Convert.ToInt32(c.CellValue.InnerText);
map[i,j] = s_string[sst_index];
}
else if (c.CellValue == null)
{
map[i,j] = null;
}
else
{
map[i, j] = c.CellValue.InnerText;
}
}
c = c.NextSibling<Cell>();
}
#endregion
row = row.NextSibling<Row>();
}
return map;
}
}
return null;
}
