读取一个单元格的值,这个是官方的写法
public string GetCellValue(string fileName, string sheetName, string addressName) { string value = null; try { using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName" + sheetName); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); DocumentFormat.OpenXml.Spreadsheet.Cell theCell = wsPart.Worksheet.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>(). Where(c => c.CellReference == addressName).FirstOrDefault(); if (theCell != null && theCell.InnerText.Length > 0) { value = theCell.InnerText; if (theCell.DataType != null) { switch (theCell.DataType.Value) { case CellValues.SharedString: var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); if (stringTable != null) { value = stringTable.SharedStringTable .ElementAt(int.Parse(value)).InnerText; } else { value = null; } break; case CellValues.Boolean: switch (value) { case "0": value = "FALSE"; break; default: value = "TRUE"; break; } break; } } } else { value = null; } } } catch (Exception ex) { value = null; AppGloab.SysLogger.Error(ex); } return value; }
读取Excel大文件,这是根据官方读取大文件的方法改写的,可以读取到指定worksheet的值
public virtual void ReadExcelFileDOM(string fileName, string worksheet = "") { try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false)) { WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = new SheetData(); Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheet).FirstOrDefault(); if (theSheet != null) { sheetData = ((WorksheetPart)workbookPart.GetPartById(theSheet.Id)).Worksheet.GetFirstChild<SheetData>(); } int irow = 0; foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>()) { irow = irow + 1; AppGloab.SysLogger.Info("Worksheet Row:" + irow); int icoloum = 0; foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>()) { icoloum = icoloum + 1; AppGloab.SysLogger.Info("Worksheet Row:" + irow + ", Column:" + icoloum); string value = null; if (c.InnerText.Length > 0) { value = c.InnerText; if (c.DataType != null) { switch (c.DataType.Value) { case CellValues.SharedString: var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>() .FirstOrDefault(); 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; }
Console.WriteLine(value); } } } } } } catch (Exception ex) { AppGloab.SysLogger.Error(ex); } }