Csharp: read excel file using Open XML SDK 2.5
来源：互联网 发布：塞班s60v5 软件下载 编辑：程序博客网 时间：2024/06/01 09:35
/// <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; } } }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; } 0 0
- Csharp: read excel file using Open XML SDK 2.5
- Csharp: read excel file using Open XML SDK 2.5
- csharp XmlTextReader read xml file
- csharp XmlTextReader read xml file
- csharp: read excel using Aspose.Cells
- csharp: read excel using Aspose.Cells
- csharp read excel file get sheetName list
- csharp read excel file get sheetName list
- 用Open XML SDK读取Excel
- 用Open XML SDK读取Excel
- 用Open XML SDK读取Excel
- 用Open XML SDK 读取Excel
- 使用Open XML SDK读取Excel
- Read / Write Excel file in Java using Apache POI
- Read / Write Excel file in Java using Apache POI
- Using C# Generics read & update XML file .
- csharp: using using System.Web.Script.Serialization read json
- csharp: using using System.Web.Script.Serialization read json
- IOS 去除webView中长按出现文本选取框
- iOS项目开发实战——设置视图的透明度改变动画
- jQuery 1.8版本后不支持toggle()事件，只支持显示隐藏-------解决方案
- POJ 2367 Genealogical tree（简单拓扑排序）
- Java反射
- Csharp: read excel file using Open XML SDK 2.5
- Csharp: read excel file using Open XML SDK 2.5
- 使用java8轻松处理日期
- CentOS系统下的Hadoop集群（第5期副刊）_JDK和SSH无密码配置
- iOS优化内存的横向ScrollView
- Janus the Architect
- 第一次写博客，学会分享，乐于奉献
- 谷歌刚发布了一款时尚“垃圾桶”
- 【Java】Eclipse最实用的快捷键(持续更新)
