Csharp:read Excel file using Open XML SDK 2.5

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 Microsof T 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 Class Library Reference https://msdn.microsoft.com/ZH-CN/library/gg278315.aspx//Http://openxmldevelope r.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>//Generate Excel files///</summary>//<param name= "Sender" ></p aram>//<param name= "E" ></param> private voidButton1_Click (object sender, EventArgs e) {string timemark = DateTime.Now.ToString ("Yyyymmddhhmmss"); String excelpath = Path.Combine (Environment.getfolderpath (Environment.SpecialFolder.Desktop), "Geovindu" + Timem            Ark + ". xlsx"); list<package> packages = new List<package> {New Package {company = "C                          Oho 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, Trackingnumb ER = 299456122L, dateorder = Datetime.today, hascompleted = false}, new package {company = "Adv Enture Works ", Weight = 33.8, Trackingnumber = 4665518773L, DateOrder = DateTime.Today.AddDays ( -4), hascompleted = trUE}, new package {company = "Test Works", Weight = 35.8, Trackingnumber = 4665518774L, Dateorde R = DateTime.Today.AddDays ( -2), hascompleted = true}, new package {company = "good Works", Wei                        Ght = 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>///Read worksheets///</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 = getsheets (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 (environm Ent.             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>//reading worksheets///EXCEL 2007 +//</summary>//<param NA Me= "strFileName" ></param>///<returns></returns> public static LIST&LT;SHEETNAMEINFO&G T            Getsheets (String strFileName) {//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;                    Sheetnameinfo.sheetid = k;                    Sheetnameinfo.sheetname = SheetName; Sheets.                    ADD (Sheetnameinfo);                k++;      }            }      return sheets;        }//<summary>////</summary>/<param name= "cell" ></param> <param name= "Stringtablepart" ></param>///<returns></returns> public Stati C 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 the 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&gt        ; <returns></returns> Public Static DataTable Readasdatatable (String fileName) {datatable datatable = new DataTable (); using (spreadsheetdocument spreadsheetdocument = Spreadsheetdocument.open (FileName, False)) {Wo                Rkbookpart Workbookpart = Spreadsheetdocument.workbookpart; ienumerable<sheet> sheets = spreadsheetdocument.workbookpart.workbook.getfirstchild<sheets> ().                Elements<sheet> (); String relationshipid = Sheets. First ().                Id.value;                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>///</summary>//<param name= "document" ></param&gt        ; <param name= "Cell" ></param>///<returns></returns> private static string Getcel LValue (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.ChildEleme Nts[int32.parse (value)].            InnerText;            } else {return value; }        }

　　

Csharp:read Excel file using Open XML SDK 2.5