OpenXML读取Excel数据以及处理Excel大文件

读取Excel大文件,这是根据官方读取大文件的方法改写的,可以读取到指定worksheet的值,但是这个方式有缺点,Excel表格本身不能有空单元格,否则读取的时候自动会跳过这个单元格

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);
            }

        }

 

读取一个单元格的值,这个是官方的写法

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;
        }