ASP.NET Core 8 API: Opening Excel File On The Server and Return its contents in JSON format
ASP.NET Core is the best technology for building WEB Apps and API Services. The ASP.NET Core API offers features for building Data Communication services. We can connect various data providers to read their data and return it to the client applications. One such capability is to open an Excel file on the server and read its contents so that the client can respond to them in JSON format. Excel files play a very important role in storing data so that this data can be used for various business intelligence processes. We can develop ASP.NET Core API to directly open the Excel file on the server and return its contents to the client without downloading the file on the client. Figure 1 shows the application development process.
Figure 1: Reading Excel file using ASP.NET Core API app
The advantage of the above approach is that the file is available on the server and its data is sent to the client.
To implement the solution we will be using the ExcelDataReader.DataSet package. This package is an extension for the ExcelDataReader Library. This is a lightweight and fast library in C# for reading Excel file data. This library has the ExcelDataReaderFactory class that contains the CreateReader() method. This method accepts the Stream object. This stream object is created using the Excel file passed to it. The CreateReader() method returns the IExcelDataReader object. This object is used to read contents from the Excel file. The contents from the file are added to the DataSet object using the AsDataSet() method of the IExcelDataReader object. The ExcelDataReader library also contains the ExcelDataSetConfiguration class. One of the properties of the ExcelDataSetConfiguration class is ConfigureDataTable. This property is of the type ExcelDataTableConfiguration class. This class has the UseHeaderRow boolean property. When this property is set to true, it uses the first row of the Excel worksheet as the DataTable Columns in DataSet.
Step 1: Open Visual Studio and create an ASP.NET Core API Application as a minimal API. Name this application as Core_API_ReadExcel. In this project add the ExcelDataReader.DataSet package.
Step 2: In this project add a new folder named Files. In this folder add two Excel files named Customer.xlsx and Employee.xlsx with data in it. (You can download these files from the Git Link provided at the end of the article)
Step 3: In the Program.cs lets create a new Minimal API as shown in Listing 1.
app.MapGet("/readexcel/{file}", async (HttpContext context, IWebHostEnvironment env, string file) =>
{
// 1. Get the directory path
string dirPath = Path.Combine(env.ContentRootPath, "Files");
// 2. Read Files
var files = Directory.GetFiles(dirPath);
// 3. Get The FileName from the received 'file'
var fileName = files.FirstOrDefault(f => f.Contains(file));
// 4. If file is not found return NotFound
if (fileName == null)
{
return Results.NotFound($"File {file}.xlsx is not available");
}
// 5. Register the Provider
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
// 6. Define the DataSet
DataSet dataSet;
// 7. Read the Excel File on the server
using (var stream = File.OpenRead(fileName))
{
// Create a reader for the Excel file
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
// 8. Use the first row as the header
UseHeaderRow = true
}
};
// 9. Convert the worksheet to DataSet
dataSet = reader.AsDataSet(conf);
}
}
var dataResult = new List<Dictionary<string, object>>();
// 10. Loop through the tables
foreach (DataTable table in dataSet.Tables)
{
// 11. Loop through the rows
foreach (DataRow row in table.Rows)
{
var rowDataDict = new Dictionary<string, object>();
foreach (DataColumn col in table.Columns)
{
//12 Add the column name and value to the dictionary
rowDataDict[col.ColumnName] = row[col];
}
// 13 Add the dictionary to the JSON Result
dataResult.Add(rowDataDict);
}
}
return Results.Ok(dataResult);
});
Listing 1: The Minimal API
As shown in the code of Listing 1, the readexcel endpoint is created. This accepts the string parameter. This string parameter accepts the filename as the value. The code checks if the file is available in the Files folder. If the file is not found the File Not Found response will be returned. The System.Text.Encoding.RegisterProvider class is an important part of the code. This is used to register the code page encoding provider in .NET. This is particularly useful when our code needs to use legacy encodings that are not included in.NET 5 onwards. When the code needs to use Excel files then it is important to use the RegisterProvider class. The code opens the Excel file in the Stream. This stream is used by the CreateReader() method of the ExcelReaderFactory class to read the contents of the Excel file and put the data from the Excel into the DataSet.
Each worksheet from the Excel file is added to the DataSet as DataTables. The code iterates over each DataTable from the DataSet and each row from the DataTable. The code reads data for each column row and adds it to the Dictionary. This dictionary is further returned from the API.
Run the application and make the HTTP GET call as shown in the URL
http://localhost:5191/readexcel/Customer
The Customer is the URL Parameter passed. The API returns the Customer data from the Customer.xlxs file as shown in Figure 2
Figure 2: The result
The Code for this article can be downloaded from this link.
Conclusion: The ExclDataReader Library is the best way to open the Excel Files on the server and read its data in a simpler way.
