How to Import Sheets and Ranges from an Excel File Using .NET C#

Document Solutions for Excel (DsExcel, previously GcExcel) now includes another powerful function, ImportData, in its feature-rich API. As the name suggests, this function imports data from an Excel file. The question that arises is, how is this function any different from the Open method used to load an Excel file into DsExcel.

Let's dig in and examine the differences between the two functions to learn when it's most appropriate to use each of them.

This blog discusses and demonstrates the syntax and functionality of the ImportData function and provides time trial data to verify the speed differences between the Open and ImportData. You will gain a deep understanding of the functions, which will allow you to decide which functions are right for you, based on your unique use cases or needs.

ImportData Function

The ImportData function imports all the data from a specified source, wherein the source can refer to a worksheet, a table, or a cell range in a workbook. The data fetched from the source is returned as an array by the ImportData method.

The basic syntax for this static function is:

Imports all data of the specified source:

public static System.object[,] ImportData(System.string fileName, System.string sourceName)

The first parameter in this method accepts the workbook name, and the second parameter accepts the name of a worksheet, table name, or cell range from the workbook. In case the user is aware of a valid sheet name or a table name, they can directly pass the name.

However, if the user is not sure about the name, then another method GetNames from the DsExcel API comes to the rescue. The static method GetNames of Workbook class accepts a workbook name or stream as a parameter to return all the sheet names and table names from the workbook. If the name is a table name, the worksheet name is qualified before the table name, such as "Sheet1!Table1". And if the worksheet name contains special characters such as '\'', '!' ,' ', the worksheet name will be enclosed in single quotes "'Sheet!1'!Table1".

The names returned by this method can be used for the source parameter of the ImportData method.

The code snippet below depicts the use of both the ImportData and GetNames methods to retrieve all the data from a table in the workbook:   

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
 
// Open an excel file
var fileStream = GetResourceStream("xlsx\\AgingReport.xlsx");
 
// Get the possible import names in the file.
// The names[0] and names[1] are sheet names: "Aging Report", "Invoices".
// The names[2] and names[3] are table names: "'Aging Report'!tblAging", "Invoices!tblInvoices".
var names = GrapeCity.Documents.Excel.Workbook.GetNames(fileStream);
 
// Import the data of a table "'Aging Report'!tblAging" from the fileStream.
var data = GrapeCity.Documents.Excel.Workbook.ImportData(fileStream, names[2]);
 
// Assign the data to current workbook.
workbook.Worksheets[0].Range[0, 0, data.GetLength(0), data.GetLength(1)].Value = data;
 
// Save to an excel file
workbook.Save("importdatafortable.xlsx");

Download the demo to get you familiar with the implementation and working of both these methods.

Another overload of the ImportData function, imports data from a specific cell range in the specified worksheet:

public static System.object[,] ImportData(
   System.string fileName,
   System.string worksheetName,
   System.int row,
   System.int column,
   System.int rowCount,
   System.int columnCount
)

Refer to the following topic to explore all overloads of the ImportData method.

Use Case: Using C# .NET to Compare Speed of Processing

In order to test speed differentials, let's look at an implementation of loading a workbook with a large number of formulas using both Open and ImportData methods.

Measuring the time used to load the workbook using the Open method, it is observed that it took close to 11ms to accomplish this process. However, with the ImportData method, the same task is accomplished in 4ms. Thus demonstrating how efficient the code becomes just by using the ImportData function in place of Open.

The steps ahead describe how to use the ImportData function and Open function to load the data from a workbook having a large number of formulas:

1. Create a new .NET Core Console application for C#.
2. Install GrapeCity.Documents.Excel package using NuGet package manager.

   3. Add the following method, which uses both the ImportData function as well as the Open function to fetch data from a workbook with a large number of formulas and loads the returned data into respective workbooks. Later, it appends the time consumed to load the data using both these methods, to a workbook saved as an Excel file to showcase the performance results.

The code comments explain how to create a new workbook, get data using the ImportData function, Open the function and populate the workbook using the imported data:

static void TestPerformance()
{
   //create a new workbook
   var workbook = new GrapeCity.Documents.Excel.Workbook();
 
   workbook.ActiveSheet.Range["A:B"].ColumnWidth = 35;
   workbook.ActiveSheet.Range["A2:B2"].HorizontalAlignment = HorizontalAlignment.Right;
 
   //Number of times to read data.
   int count = 10;
 
   #region ImportData
   //Create a new workbook.        
   var importDataWorkbook = new GrapeCity.Documents.Excel.Workbook();
   importDataWorkbook.Worksheets.Add();
 
   //Total time consumed to import data "count-2" times.
   long importDataTotalTimeConsumed = 0;
 
   for (int i = 0; i < count; i++)
   {
      var importDataFileStream = GetResourceStream("xlsx\\AgingReport1.xlsx");
 
      //Start time to read Excel data.
      var importDatasw = new System.Diagnostics.Stopwatch();
      importDatasw.Start();
 
      //Import data of the range from the fileStream.              
      var data1 = GrapeCity.Documents.Excel.Workbook.ImportData(importDataFileStream, "Aging Report");
      var data2 = GrapeCity.Documents.Excel.Workbook.ImportData(importDataFileStream, "Invoices");             
 
      //End time to read Excel data.
      importDatasw.Stop();
 
      // Assign the data to current workbook
      importDataWorkbook.Worksheets[0].Range[0, 0, data1.GetLength(0), data1.GetLength(1)].Value = data1;
      importDataWorkbook.Worksheets[1].Range[0, 0, data2.GetLength(0), data2.GetLength(1)].Value = data2;    
      
      //The operating system has a cache to open the file, and it takes time to open the file for the first time.
      //For the accuracy of statistics, the time of the first and last time is removed.
      if (i > 0 && i < count - 1)
      {
          importDataTotalTimeConsumed += importDatasw.ElapsedMilliseconds;
      }
   }
 
   //Average time consumed to read data using "importData" method.
   double importDataTimeConsumed = (double)importDataTotalTimeConsumed / (count - 2);
 
   //Save to an excel file.
   importDataWorkbook.Save("Data1.xlsx");
   #endregion
 
   #region Open
   //Create a new workbook.
   var openWorkbook = new GrapeCity.Documents.Excel.Workbook();
 
   //Total time consumed to open Excel file "count-2" times.
   long openTotalTimeConsumed = 0;
 
   for (int i = 0; i < count; i++)
   {
       var openFileStream = GetResourceStream("xlsx\\AgingReport1.xlsx");
 
       //Start time to read Excel data.
       var opensw = new System.Diagnostics.Stopwatch();
       opensw.Start();
 
       //Import Excel data using "Open" Method.
       openWorkbook.Open(openFileStream);
 
       //End time to read Excel data.
       opensw.Stop();
 
       //The operating system has a cache to open the file, and it takes time to open the file for the first time.
       //For the accuracy of statistics, the time of the first and last time is removed.
       if (i > 0 && i < count - 1)
       {
           openTotalTimeConsumed += opensw.ElapsedMilliseconds;
       }
    }
 
    //Average time consumed to import Excel data using "Open" method.
    double openTimeConsumed = (double)openTotalTimeConsumed / (count - 2);
 
    //Save to an excel file
    openWorkbook.Save("Data2.xlsx");
 
    #endregion
 
    workbook.ActiveSheet.Range["A1"].Value = "Time consumed by ImportData method";
    workbook.ActiveSheet.Range["B1"].Value = "Time consumed by Open method";
 
    //Store the time information of ImportData mehod in cell A2.
    workbook.ActiveSheet.Range["A2"].Value = importDataTimeConsumed.ToString() + "ms";
 
    //Store the time information of Open mehod in cell B2.
    workbook.ActiveSheet.Range["B2"].Value = openTimeConsumed.ToString() + "ms";
 
    // Save to an excel file
    workbook.Save("importdataforrange.xlsx");
}

Download the sample implementing the above method. You can execute the sample to observe the time difference and the results. You can also find the performance sample demo here.

NOTE: The speed of the processing will vary based on system setup and resources.

Refer to demos and documentation to explore other exciting features provided by Document Solutions for Excel, .NET Edition.