How to Import Sheets and Ranges from an Excel File Using .NET C#
Tags: dotnet, webdev, tutorial, devops
Published: 2022-05-19 20:02:00

---
canonical_url: https://www.grapecity.com/blogs/how-to-import-sheets-ranges-from-excel-file-using-net-c-sharp
description: Learn how to import sheets and ranges from an Excel file using .NET C#. See more from GrapeCity Documents today.
---

**Grapecity Documents for Excel** 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 GcExcel.

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

<div>

<table border="0">

<thead>

<tr>

<th scope="col" data-column="0" aria-disabled="false" aria-sort="none" aria-label="Open Function: No sort applied, activate to apply an ascending sort">

<div>Open Function</div>

</th>

<th scope="col" data-column="1" aria-disabled="false" aria-sort="none" aria-label="ImportData Function: No sort applied, activate to apply an ascending sort">

<div>ImportData Function</div>

</th>

</tr>

</thead>

<tbody aria-live="polite" aria-relevant="all">

<tr>

<td>Loads the Excel object model.</td>

<td>Does not load the Excel object model.</td>

</tr>

<tr>

<td>It opens the spreadsheet and loads all data types, including values and formulas.</td>

<td>It opens the spreadsheet and gets only the data.</td>

</tr>

<tr>

<td>Formulas are loaded, and CalcEngine works.</td>

<td>CalcEngine can't work without the object model. Hence formulas are ignored.</td>

</tr>

<tr>

<td colspan="1">It comparatively takes more time, as it needs to load the entire object model.</td>

<td colspan="1">It is much faster as it does not load the object model, instead only gets the data.</td>

</tr>

</tbody>

</table>

The above differences clearly explain that the **ImportData** function is a much quicker way of fetching data from an Excel file if data is strickly data points. However, if you need to import functions and calculations, the **Open** function is the better choice.

</div>

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 **ImportD****ata** 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 GcExcel 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<span> </span>**'\'', '!' ,' '**, 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](https://en.staging.onboarding.grapecity.com/documents-api-excel/demos/importdatafortable) 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](https://www.grapecity.com/documents-api-excel/docs/online/GrapeCity.Documents.Excel~GrapeCity.Documents.Excel.Workbook~ImportData.html?highlight=importdata%2C) 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 for Excel v5.1 package using Nuget package manager.    ![](https://wiki.grapecity.io/download/attachments/1190500809/Image1.png?version=1&modificationDate=1649452421687&api=v2)

![NuGet Solution](https://files.grapecity.com/gc-website-media/ailjwsc2/image1.png?width=500&height=163.46153846153845)

   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](https://files.grapecity.com/gc-website-media/x0lecxn1/useimportdata.zip) 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](https://www.grapecity.com/documents-api-excel/demos/importdata).

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

Refer to [demos](https://www.grapecity.com/documents-api-excel/demos/) and [documentation](https://www.grapecity.com/documents-api-excel/docs/online/overview.html) to explore other exciting features provided by Grapecity Documents for Excel.