Openxml 读取指定列的数据

最新推荐文章于 2024-06-26 08:00:00 发布

勾吴江南

最新推荐文章于 2024-06-26 08:00:00 发布

阅读量2.6k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： OpenXml Office Development Spreadsheet Document C# 文章标签： openxml object regex null string path

本文链接：https://blog.youkuaiyun.com/TX_OfficeDev/article/details/7568251

Office Development 同时被 3 个专栏收录

134 篇文章

订阅专栏

63 篇文章

订阅专栏

OpenXml

26 篇文章

订阅专栏

本文提供了一个使用C#语言通过OpenXml库读取并解析Excel文件的示例，重点关注如何读取指定列的数据，并通过正则表达式匹配单元格引用来获取所需数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在这个示例中指定"A","B","C"为需要读取的列。

using System.Windows.Forms;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;

namespace ConsoleApplication13
{
    class Program
    {
        [STAThread]
        static void Main(string[] args)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Document|*.xlsx";
            ofd.Multiselect = false;
            ofd.ShowDialog();
            string path = ofd.FileName;
            List<string> C = new List<string>();
            C.Add("A");
            C.Add("B");
            C.Add("C");
            Dictionary<string, List<object>> result = new Dictionary<string, List<object>>();
            result.Add("A", new List<object>());
            result.Add("B", new List<object>());
            result.Add("C", new List<object>());
            using (SpreadsheetDocument sd = SpreadsheetDocument.Open(path, false))
            {
                WorkbookPart wp = sd.WorkbookPart;
                Sheet sheet = wp.Workbook.Descendants<Sheet>()
                    .Where(s => s.Name == "Sheet1").FirstOrDefault();
                WorksheetPart wsp = wp.GetPartById(sheet.Id) as WorksheetPart;
                SharedStringTablePart sstp = wp.GetPartsOfType<SharedStringTablePart>()
                    .FirstOrDefault();
                SharedStringTable sst = sstp.SharedStringTable;
                List<SharedStringItem> alph = sst.Descendants<SharedStringItem>()
                    .ToList();
                if (wsp != null)
                {
                    Worksheet ws = wsp.Worksheet;
                    SheetData sda = ws.Descendants<SheetData>().FirstOrDefault();
                    List<Row> rows = sda.Descendants<Row>().ToList();
                    foreach (Row row in rows)
                    {
                        List<Cell> cells = row.Descendants<Cell>().ToList();
                        foreach (Cell cell in cells)
                        {
                            Regex rege = new Regex("([A-Z]{1,3})");
                            MatchCollection Matchs = rege
                                .Matches(cell.CellReference.Value);
                            switch (Matchs[0].Value)
                            {
                                case "A":
                                    result["A"].Add(GetValue(cell, ref alph));
                                    break;
                                case "B":
                                    result["B"].Add(GetValue(cell, ref alph));
                                    break;
                                case "C":
                                    result["C"].Add(GetValue(cell, ref alph));
                                    break;
                            }
                        }
                    }
                    Console.WriteLine("=====================A=================");
                    ShowValue(result["A"]);
                    Console.WriteLine("=====================B=================");
                    ShowValue(result["B"]);
                    Console.WriteLine("=====================C=================");
                    ShowValue(result["C"]);
                    Console.ReadKey();
                }
            }
        }

        private static void ShowValue(List<object> list)
        {
            foreach (object i in list)
            {
                Console.WriteLine(string.Format("{0}", i));
            }
        }

        private static object GetValue(Cell cell, ref List<SharedStringItem> alph)
        {
            object result = null;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                result = alph[int.Parse(cell.CellValue.Text)].Text.Text;
            }
            else
            {
                result = cell.CellValue.Text;
            }
            return result;
        }
    }
}