Openxml 读取指定列的数据

来源：互联网发布：深度解析淘宝运营pdf 编辑：程序博客网时间：2024/05/29 17:32
在这个示例中指定"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;        }    }}