Open XML读取Excel表格

1、读取特定行列的单元格

private static void ReadExcel(string filePath,string sheetName,string colName,uint rowIndex)
        {
            using (SpreadsheetDocument spreadsheetDocument=SpreadsheetDocument.Open(filePath,false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                Workbook workbook = workbookPart.Workbook;

                IEnumerable<Sheet> sheets = workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    return;
                }
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
                Cell cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
                if (cell == null)
                {
                    return;
                }
                Console.WriteLine(cell.CellValue.Text);
            }
        }

private static Cell GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
        {
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>().Where(r => r.RowIndex == rowIndex);
            if (rows.Count() == 0)
            {
                // A cell does not exist at the specified row.
                return null;
            }

            IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0);
            if (cells.Count() == 0)
            {
                // A cell does not exist at the specified column, in the specified row.
                return null;
            }

            return cells.First();
        }

2、读取对应Sheet里的所有值

private static void ReadExcel(string filePath, string sheetName)
        {
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filePath, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                Workbook workbook = workbookPart.Workbook;

                IEnumerable<Sheet> sheets = workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    return;
                }
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
                foreach (var dic in GetAllCell(worksheetPart.Worksheet))
                {
                    Console.WriteLine($"单元格：{dic.Key}的值：{dic.Value}");
                }
            }
        }
private static Dictionary<string,string> GetAllCell(Worksheet worksheet)
        {
            Dictionary<string, string> dicResult = new Dictionary<string, string>();
            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Elements<Row>();
            if (rows.Count() == 0)
            {
                return null;
            }

            foreach(Row row in rows)
            {
                IEnumerable<Cell> cells = row.Elements<Cell>();
                if (cells.Count() == 0)
                {
                    continue;
                }
                else
                {
                    foreach (Cell cell in cells)
                    {
                        dicResult.Add(cell.CellReference, cell.CellValue.Text);
                    }
                }
            }
            return dicResult;
        }