using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace ExcelApp
{
public partial class Form_GetCell : Form
{
public Form_GetCell()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
SpreadsheetDocument doc = SpreadsheetDocument.Open(openFileDialog1.FileName, false);
WorkbookPart wbPart = doc.WorkbookPart;
Sheets sheets = wbPart.Workbook.GetFirstChild<Sheets>();
foreach (Sheet sheet in sheets)
{
textBox1.Text += "Excel Sheet Name : " + sheet.Name + "\r\n";
Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetdata = Worksheet.GetFirstChild<SheetData>();
foreach (Row crow in sheetdata)
{
foreach (Cell ccell in crow)
{
if (ccell.DataType != null)
{
int index = Convert.ToInt32(ccell.InnerText);
SharedStringTablePart sstp = wbPart.GetPartsOfType<SharedStringTablePart>().ElementAt<SharedStringTablePart>(0);
SharedStringItem item = sstp.SharedStringTable.Elements<SharedStringItem>().ElementAt<SharedStringItem>(index);
Text t = item.Elements<Text>().ElementAt<Text>(0);
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, t.Text);
}
else
{
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, ccell.InnerText);
}
}
}
}
}
}
}
}
SpreadsheetDocument.Open()
メソッドに与えて、SpreadsheetDocument オブジェクトを作成します。
SpreadsheetDocument オブジェクトから、WorkbookPartオブジェクト Sheetsオブジェクトを取得します
SpreadsheetDocument doc = SpreadsheetDocument.Open(openFileDialog1.FileName, false);
WorkbookPart wbPart = doc.WorkbookPart;
Sheets sheets = wbPart.Workbook.GetFirstChild<Sheets>();
foreach (Sheet sheet in sheets)
{
}
textBox1.Text += "Excel Sheet Name : " + sheet.Name + "\r\n";
Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetdata = Worksheet.GetFirstChild<SheetData>();
foreach (Row crow in sheetdata)
{
}
foreach (Cell ccell in crow)
{
}
InnerText
プロパティの値をキーにして、SharedStringTable
からセルの値を取得します。
取得した値をセルのアドレスを表現するCellオブジェクトの CellReference
プロパティと合わせてテキストボックスに表示します。InnerText
プロパティに設定されているため、セルのアドレスと合わせて、
InnerText
プロパティの値をテキストボックスに表示します。 if (ccell.DataType != null)
{
int index = Convert.ToInt32(ccell.InnerText);
SharedStringTablePart sstp = wbPart.GetPartsOfType<SharedStringTablePart>().ElementAt<SharedStringTablePart>(0);
SharedStringItem item = sstp.SharedStringTable.Elements<SharedStringItem>().ElementAt<SharedStringItem>(index);
Text t = item.Elements<Text>().ElementAt<Text>(0);
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, t.Text);
}
else
{
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, ccell.InnerText);
}
Sheets
SheetData
Row
のデフォルトプロパティを利用していますが、デフォルトプロパティを利用しない場合の記述として
以下のコードでも同じ動作になります。 private void button2_Click(object sender, EventArgs e)
{
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
SpreadsheetDocument doc = SpreadsheetDocument.Open(openFileDialog1.FileName, false);
WorkbookPart wbPart = doc.WorkbookPart;
Sheets sheets = wbPart.Workbook.GetFirstChild<Sheets>();
foreach (Sheet sheet in sheets.Elements<Sheet>())
{
textBox1.Text += "Excel Sheet Name : " + sheet.Name + "\r\n";
Worksheet Worksheet = ((WorksheetPart)wbPart.GetPartById(sheet.Id)).Worksheet;
SheetData sheetdata = Worksheet.GetFirstChild<SheetData>();
foreach (Row crow in sheetdata.Elements<Row>())
{
foreach (Cell ccell in crow.Elements<Cell>())
{
if (ccell.DataType != null)
{
int index = Convert.ToInt32(ccell.InnerText);
SharedStringTablePart sstp = wbPart.GetPartsOfType<SharedStringTablePart>().ElementAt<SharedStringTablePart>(0);
SharedStringItem item = sstp.SharedStringTable.Elements<SharedStringItem>().ElementAt<SharedStringItem>(index);
Text t = item.Elements<Text>().ElementAt<Text>(0);
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, t.Text);
}
else
{
textBox1.Text += string.Format("{0} : {1}\r\n", ccell.CellReference, ccell.InnerText);
}
}
}
}
}
}