如何从excel中读取整个工作表

本文关键字：工作 读取 excel | 更新日期: 2023-09-27 17:58:27

我正在从许多excel工作表中读取数据，但由于我直接获取每个列，所以性能很慢。有没有一种方法可以通过一次调用将整个UsedRange读取到内存中？然后本地处理行/列？

我的代码基本上是这样的：

xlWorkSheet = (Worksheet)_xlWorkBook.Worksheets.get_Item(1);
var range = xlWorkSheet.UsedRange;
for (var rCnt = 2; rCnt <= range.Rows.Count; rCnt++)
{
    // Process column entries
}

如何从excel中读取整个工作表

我在处理非常大的excel时也遇到了同样的问题我设法将其读取为范围，然后在每行使用AsParallel（）将其转换为List>它使运行得更快

这是代码：

private List<List<string>> ReadExcelFile(string fileName)
    {
        Excel.Application xlApp = null;
        Workbook xlWorkbook = null;
        Sheets xlSheets = null;
        Worksheet xlSheet = null;
        var results = new List<List<string>>();
        try
        {
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkbook = xlApp.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, true, XlPlatform.xlWindows, Type.Missing,false, false, Type.Missing, false, Type.Missing, Type.Missing);
            xlSheets = xlWorkbook.Sheets as Sheets;
            xlSheet = xlSheets[1];
            // Let's say your range is from A1 to DG5200
            var cells = xlSheet.get_Range("A1", "DG5200");
            results = ExcelRangeToListsParallel(cells); 
        }
        catch (Exception)
        {
            results = null;
        }
        finally
        {
            xlWorkbook.Close(false);
            xlApp.Quit();
            if (xlSheet != null)
                Marshal.ReleaseComObject(xlSheet);
            if (xlSheets != null)
                Marshal.ReleaseComObject(xlSheets);
            if (xlWorkbook != null)
                Marshal.ReleaseComObject(xlWorkbook);
            if (xlApp != null)
                Marshal.ReleaseComObject(xlApp);
            xlApp = null;                
        }
        return results;
    }
    private List<List<String>> ExcelRangeToListsParallel(Excel.Range cells)
    {            
        return cells.Rows.Cast<Excel.Range>().AsParallel().Select(row =>
        {         
            return row.Cells.Cast<Excel.Range>().Select(cell =>
            {
                var cellContent = cell.Value2;                    
                return (cellContent == null) ? String.Empty : cellContent.ToString(); 
            }).Cast<string>().ToList();                
        }).ToList();
    }
相关文章：