Openxml 读取指定列的数据
在这个示例中指定"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; } } }
征诛志异,三让两家王朝;功同开辟,一桮万古江南。