Last active
December 20, 2015 11:00
-
-
Save ishisaka/6120267 to your computer and use it in GitHub Desktop.
Open XML SDK 2.0を使ってExcel 2010のファイルからセルの値を取り出すサンプル
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using DocumentFormat.OpenXml.Packaging; | |
using DocumentFormat.OpenXml.Spreadsheet; | |
namespace AccessSheetAndCell | |
{ | |
class Program | |
{ | |
private static List<NumberingFormat> _numFormats; | |
static void Main(string[] args) { | |
string filePath = args[0]; //ファイルパス | |
string sheetName = args[1]; //データを読み込むセルがあるワークシートの名前 | |
string addressName = args[2]; //ワークシートのセル位置 | |
//書式化文字列リストの初期化 | |
_numFormats = new List<NumberingFormat>(); | |
InitializeStandardFormats(_numFormats); | |
try { | |
//以下のように文書のオブジェクトをusingしておく | |
using (var document = SpreadsheetDocument.Open(filePath, true)) { | |
//ワークシートを取り出す | |
var wbPart = document.WorkbookPart; | |
if (wbPart != null) { | |
var sheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName); | |
if (sheet == null) { | |
Console.Error.WriteLine("ワークシートが見つかりません"); | |
return; | |
} | |
//セルのオブジェクトを取り出す | |
var wsPart = (WorksheetPart) (wbPart.GetPartById(sheet.Id)); | |
var cell = | |
wsPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == addressName); | |
if (cell != null) { | |
string value = cell.InnerText; | |
//セルにDataTypeが設定されているときの処理 | |
if (cell.DataType != null) { | |
switch (cell.DataType.Value) { | |
case CellValues.SharedString: | |
var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault(); | |
if (stringTable != null) { | |
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText; | |
} | |
break; | |
case CellValues.Boolean: | |
switch (value) { | |
case "0": | |
value = "FALSE"; | |
break; | |
default: | |
value = "TRUE"; | |
break; | |
} | |
break; | |
} | |
} | |
//セルに書式が設定さえている場合の対応。(日付とか) | |
if (cell.StyleIndex != null) { | |
var stylePart = wbPart.GetPartsOfType<WorkbookStylesPart>().FirstOrDefault(); | |
if (stylePart != null) { | |
var cellFormats = stylePart.Stylesheet.CellFormats.Elements(); | |
if (cell.StyleIndex > int.MaxValue) { | |
Console.Error.WriteLine("StyleIndesがintの上限を超えています。"); | |
return; | |
} | |
int i = int.Parse(cell.StyleIndex.ToString()); | |
var cellFormat = cellFormats.OfType<CellFormat>().ElementAt(i); | |
var formatId = cellFormat.NumberFormatId; | |
//FormatIdとフォーマット文字列との対応が日本語版Excelを使うとECMA-376と違う! | |
//ユーザー定義の書式を確認し、あればStandardFormatsリストに追加する | |
var numFormatsParentNodes = | |
stylePart.Stylesheet.NumberingFormats.OfType<NumberingFormat>(); | |
foreach (var ft in numFormatsParentNodes) { | |
_numFormats.Add(ft); | |
} | |
string format = | |
_numFormats.First(s => s.NumberFormatId.ToString() == formatId.ToString()) | |
.FormatCode; | |
//FormatId 14~22は日付型 | |
if (formatId >= 14 && formatId <= 22) { | |
value = DateTime.FromOADate(double.Parse(value)).ToString(format); | |
} | |
//45~47は期間 | |
else if (formatId >= 45 && formatId <= 47) { | |
value = TimeSpan.FromDays(double.Parse(value)).ToString(format); | |
} | |
//それ以外は数値と見なす。書式化せず、書式文字列をそのまま表示する。 | |
else { | |
value = double.Parse(value) + " FormatCode:" + format; | |
} | |
} | |
} | |
Console.WriteLine(addressName + " Value :" + value); | |
} | |
else { | |
Console.Error.WriteLine("セルが見つかりません。"); | |
} | |
} | |
//閉じる | |
document.Close(); | |
Console.Read(); | |
} | |
} | |
catch { | |
Console.Error.WriteLine("Excelのファイルが見つかりません。"); | |
} | |
} | |
/// <summary> | |
/// Open XMLのECMA標準で指定されている書式文字列のリストを初期化する。 | |
/// 以下の書式文字列はECMAの仕様書にあわせているが、Excel自体はローカライズにより書式文字列が | |
/// 言語エディションごとに異なっているようだ。 | |
/// </summary> | |
/// <param name="formats">標準の書式文字列のIDとFormatCodeを格納するNumberingFormatのリスト</param> | |
private static void InitializeStandardFormats(List<NumberingFormat> formats) { | |
formats.Add(new NumberingFormat() { NumberFormatId = 1, FormatCode = "0" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 2, FormatCode = "0.00" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 3, FormatCode = "#,##0" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 4, FormatCode = "#,##0.00" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 9, FormatCode = "0%" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 10, FormatCode = "0.00%" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 11, FormatCode = "0.00E+00" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 12, FormatCode = "# ?/?" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 13, FormatCode = "# ??/??" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 14, FormatCode = "d/M/yyyy" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 15, FormatCode = "d-MMM-yy" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 16, FormatCode = "d-MMM" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 17, FormatCode = "MMM-yy" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 18, FormatCode = "h:mm tt" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 19, FormatCode = "h:mm:ss tt" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 20, FormatCode = "H:mm" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 21, FormatCode = "H:mm:ss" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 22, FormatCode = "m/d/yyyy H:mm" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 37, FormatCode = "#,##0 ;(#,##0)" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 38, FormatCode = "#,##0 ;[Red](#,##0)" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 39, FormatCode = "#,##0.00;(#,##0.00)" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 40, FormatCode = "#,##0.00;[Red](#,##0.00)" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 45, FormatCode = "mm:ss" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 46, FormatCode = "[h]:mm:ss" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 47, FormatCode = "mmss.0" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 48, FormatCode = "##0.0E+0" }); | |
formats.Add(new NumberingFormat() { NumberFormatId = 49, FormatCode = "@" }); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment