Csharp:readexcelfileusingOpenXMLSDK2.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 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; }        }    }