OpenXML 读写Excel文件

【实例简介】C# 使用DocumentFormat.OpenXml 和 DocumentFormat.OpenXml.Extensions 这个扩展读写Excel文件的例子

【实例截图】

【核心代码】

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;


namespace OpenXmlExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            args = new string[] { @"D:\PS\DEV\OpenXmlExcel\Template.xlsx" };

            WriteToExcel(args[0]);
        }

        private static void WriteToExcel(string ExcelFile)
        {
            //New
            //From SDK
            //SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))

            //Open
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(ExcelFile, true))
            {
                //string version = string.Empty;
                WorkbookPart wbPart = document.WorkbookPart;
                List<Sheet> sheets = wbPart.Workbook.Descendants<Sheet>().ToList();
                var dataSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(c => c.Name == "Trans ###");
                WorksheetPart worksheetPart = (WorksheetPart)wbPart.GetPartById(dataSheet.Id);

                if (dataSheet != null)
                {
                    Cell cell = worksheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference.Value == "E5").FirstOrDefault();
                    string content=string.Empty;

                    if (cell != null)
                    {
                        //Read
                        var dataType = cell.DataType;
                        if (dataType == CellValues.SharedString)
                        {
                            var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                            if (stringTable != null)
                            {
                                content = stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText;
                            }
                        }
                        //Write - change cause it exist, else need create brfore write or use Extensions
                        if (content == "A")
                        {
                            cell.CellValue = new CellValue("Test");
                            cell.DataType = CellValues.String;
                        }
                    }
                        //...
                        SharedStringTable sstringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                        IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();//获得Excel中得数据行
                        bool IsFirstRow = true;
                        List<data> datas = new List<data>();
                        foreach (Row row in rows)
                        {
                            if (IsFirstRow)
                            { 
                                IsFirstRow = false; 
                                //CheckHeader();
                            } //Skip Header
                            else
                            {
                                data d = new data();
                                int counter = 0;
                                foreach (Cell c in row)
                                {
                                    switch (counter)
                                    {
                                        case 0:
                                            d.ID = GetValue(c, sstringTable);
                                            break;
                                        case 1:
                                            d.Name = GetValue(c, sstringTable);
                                            break;
                                        case 2:
                                            d.score = int.Parse(GetValue(c, sstringTable));
                                            break;
                                        default:
                                            break;
                                    }
                                    counter  ;
                                }
                                datas.Add(d);
                            }
                        }

                        
                    
                    worksheetPart.Worksheet.Save();
                }
                else
                {
                    Console.WriteLine("Worksheet named 'Trans ###' not found!");
                }
            }

        }

        /// <summary>
        /// 获取单位格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="stringTablePart"></param>
        /// <returns></returns>
        private static string GetValue(Cell cell, SharedStringTable stringTable)
        {
            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
            }
            catch (Exception)
            {
                value = "N/A";
            }
            return value;
        }
    }

    public class data
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public int score { get; set; }
    }
}