Csharp: read excel file using Open XML SDK 2.5，csharpsdk
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml.Spreadsheet;
namespace OpenXmlOficeDemo
{
/// <summary>
/// Open XML SDK 2.0 for Microsoft Office http://www.microsoft.com/en-us/download/details.aspx?id=5124
/// Open XML SDK 2.5 for Microsoft Office http://www.microsoft.com/en-us/download/details.aspx?id=30425
/// Open XML SDK open source https://github.com/officedev/open-xml-sdk
/// Open XML SDK 2.5 類庫參考 https://msdn.microsoft.com/ZH-CN/library/gg278315.aspx
/// http://openxmldeveloper.org/
/// https://github.com/OfficeDev/Open-Xml-PowerTools
/// https://msdn.microsoft.com/en-us/library/office/bb448854.aspx
/// https://github.com/OfficeDev
/// </summary>
public partial class Form1 : Form
{
/// <summary>
///
/// </summary>
public Form1()
{
InitializeComponent();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Load(object sender, EventArgs e)
{
}
/// <summary>
///
/// </summary>
public class Package
{
public string Company { get; set; }
public double Weight { get; set; }
public long TrackingNumber { get; set; }
public DateTime DateOrder { get; set; }
public bool HasCompleted { get; set; }
}
/// <summary>
/// 生成EXCEL文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
string excelPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
List<Package> packages =
new List<Package>
{ new Package { Company = "Coho Vineyard", Weight = 25.2, TrackingNumber = 89453312L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Lucerne Publishing", Weight = 18.7, TrackingNumber = 89112755L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Wingtip Toys", Weight = 6.0, TrackingNumber = 299456122L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Adventure Works", Weight = 33.8, TrackingNumber = 4665518773L, DateOrder = DateTime.Today.AddDays(-4), HasCompleted = true },
new Package { Company = "Test Works", Weight = 35.8, TrackingNumber = 4665518774L, DateOrder = DateTime.Today.AddDays(-2), HasCompleted = true },
new Package { Company = "Good Works", Weight = 48.8, TrackingNumber = 4665518775L, DateOrder = DateTime.Today.AddDays(-1), HasCompleted = true },
};
List<string> headerNames = new List<string> { "Company", "Weight", "Tracking Number", "Date Order", "Completed" };
ExcelFacade excelFacade = new ExcelFacade();
excelFacade.Create<Package>(excelPath, packages, "Packages", headerNames);
}
/// <summary>
/// 讀取工作表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
string file = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");
List<SheetNameInfo> sheets = new List<SheetNameInfo>();
sheets = GetSheetsDu(file);
this.comboBox1.DataSource = sheets;
comboBox1.DisplayMember = "SheetName";
comboBox1.ValueMember = "SheetID";
//1
//OpenXmlOficeDemo.SLExcelUtility.SLExcelReader read = new SLExcelUtility.SLExcelReader();
//var data = (new OpenXmlOficeDemo.SLExcelUtility.SLExcelReader()).ReadExcel(file);
//this.dataGridView1.DataSource = data.DataRows;
//2
//FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read);
//DataTable dt = ReadAsDataTable(file);
//this.dataGridView1.DataSource = dt;
// fs.Close();
// fs.Dispose();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
string file = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");
//FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read);
DataTable dt = ReadAsDataTable(file);// ReadExcel(this.comboBox1.SelectedText, fs);
this.dataGridView1.DataSource = dt;
//fs.Close();
//fs.Dispose();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
string filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");
SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false);
// var sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
var sheets = document.WorkbookPart.Workbook.Sheets;
foreach (Sheet sheet in sheets)
{
//sheet.Id
// sheet.Name
// sheet.SheetId
foreach (var attr in sheet.GetAttributes())
{
Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value);//工作表名
}
}
WorkbookPart wbPart = document.WorkbookPart; ;
//SharedStringTable sharedStringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault().SharedStringTable;
// SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;
foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
//foreach (Sheet sheet in sheets)
foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
{
DataTable dataTable = new DataTable();
if (sheetData.HasChildren)
{
foreach (Row row in sheetData.Elements<Row>())
{
//foreach (Cell cell in row.ElementAt(0))
//{
// dataTable.Columns.Add(GetCellValue(document, cell)); //標題
// string tile= GetCellValue(document, cell); //標題
// MessageBox.Show(tile);
//}
foreach (Cell cell in row.Elements<Cell>())
{
//string tile= GetCellValue(document, cell); //標題
// MessageBox.Show(tile);
cellValue = cell.InnerText;
if (cell.DataType == CellValues.SharedString)
{
Console.WriteLine("cell val: " );//+ sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
else
{
Console.WriteLine("cell val: " + cellValue);
}
}
}
}
}
}
document.Close();
}
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button5_Click(object sender, EventArgs e)
{
try
{
string file = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");
DataTable dt = new DataTable();
string sheename = this.comboBox1.Text;
dt = ReadIdDataTable(file, sheename);
this.dataGridView1.DataSource = dt;
}
catch (Exception ex)
{
ex.Message.ToString();
}
}
/// <summary>
/// 讀取工作表名
/// 塗聚文
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public static List<SheetNameInfo> GetSheetsDu(String strFileName)
{
List<SheetNameInfo> sheetinfo = new List<SheetNameInfo>();
using (SpreadsheetDocument document = SpreadsheetDocument.Open(strFileName, false))
{
var sheets = document.WorkbookPart.Workbook.Sheets;
int k = 0;
foreach (Sheet sheet in sheets)
{
SheetNameInfo sheetNameInfo = new SheetNameInfo();
sheetNameInfo.SheetName = sheet.Name;
sheetNameInfo.Rid = sheet.Id;
sheetNameInfo.SheetID = k;//
sheetinfo.Add(sheetNameInfo);
k++;
}
}
return sheetinfo;
}
/// <summary>
/// 讀取工作表名
/// EXCEL 2007版以上
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public static List<SheetNameInfo> GetSheets(String strFileName)
{
string id = string.Empty;
// Fill this collection with a list of all the sheets.
List<SheetNameInfo> sheets = new List<SheetNameInfo>();
using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(strFileName, false))
{
WorkbookPart workbook = xlPackage.WorkbookPart;
Stream workbookstr = workbook.GetStream();
XmlDocument doc = new XmlDocument();
doc.Load(workbookstr);
XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
XmlNodeList nodelist = doc.SelectNodes("//default:sheets/default:sheet", nsManager);
int k = 0;
foreach (XmlNode node in nodelist)
{
SheetNameInfo sheetNameInfo = new SheetNameInfo();
String sheetName = String.Empty;
sheetName = node.Attributes["name"].Value;
// id = node.Attributes["id"].Value;
sheetNameInfo.SheetID = int.Parse(node.Attributes["sheetId"].Value.ToString());
sheetNameInfo.Rid = node.Attributes["r:id"].Value;
sheetNameInfo.SheetName = sheetName;
sheets.Add(sheetNameInfo);
k++;
}
}
return sheets;
}
/// <summary>
///
/// </summary>
/// <param name="cell"></param>
/// <param name="stringTablePart"></param>
/// <returns></returns>
public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if (cell.ChildElements.Count == 0)
return null;
//get cell value
String value = cell.CellValue.InnerText;
//Look up real value from shared string table
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable
.ChildElements[Int32.Parse(value)]
.InnerText;
return value;
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static DataTable ReadAsDataTable(string fileName)
{
int numID = 0;
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
//spreadSheetDocument.WorkbookPart.Workbook.Sheets;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();//第一個工作表
string relationshipId = sheets.First().Id.Value; //工作表
numID = sheets.Count();
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);//第一個工作表
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell)); //標題
}
foreach (Row row in rows)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
}
dataTable.Rows.Add(dataRow);
}
}
dataTable.Rows.RemoveAt(0);
return dataTable;
}
/// <summary>
/// 塗聚文
/// 20150820
/// 七夕節
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">工作表名</param>
/// <returns></returns>
public static DataTable ReadIdDataTable(string fileName, string sheetName)
{
DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
//spreadSheetDocument.WorkbookPart.Workbook.Sheets;
Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
//IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();//第一個工作表
//string relationshipId = theSheet.FirstOrDefault().ExtendedAttributes.ElementAt(0); //工作表
// numID = sheets.Count();
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(theSheet.Id);//第一個工作表
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell)); //標題
}
foreach (Row row in rows)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
}
dataTable.Rows.Add(dataRow);
}
}
dataTable.Rows.RemoveAt(0);
return dataTable;
}
/// <summary>
///
/// </summary>
/// <param name="document"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
string value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
}
else
{
return value;
}
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="addressName"></param>
/// <returns></returns>
private static string GetCellValue(string fileName, string sheetName, string addressName)
{
string value = null;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that Sheet
// object to retrieve a reference to the appropriate worksheet.
Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part, and then use its
// Worksheet property to get a reference to the cell whose
// address matches the address you supplied:
WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string:
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents a numeric value, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and Booleans
// individually. For shared strings, the code looks up the
// corresponding value in the shared string table. For Booleans,
// the code converts the value into the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared
// strings table.
var stringTable = wbPart.
GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something is
// wrong. Return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
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;
}
}
}
}
return value;
}
/// <summary>
///
/// </summary>
public class SheetNameInfo
{
private int _sheetId;
private string _sheetName;
private string _rid;
/// <summary>
///
/// </summary>
public int SheetID
{
get{return _sheetId;}
set{_sheetId= value;}
}
/// <summary>
///
/// </summary>
public string SheetName
{
get { return _sheetName; }
set { _sheetName = value; }
}
/// <summary>
///
/// </summary>
public string Rid
{
get { return _rid; }
set { _rid = value; }
}
}
