Achraf Ben Alaya
No Result
View All Result
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy
SUBSCRIBE
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy
No Result
View All Result
Achraf Ben Alaya
No Result
View All Result
ADVERTISEMENT
Home Blog Cloud Azure

Reading Excel file in Azure Web Apps

The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine

achraf by achraf
December 1, 2020
in Azure, Blog, c#, Cloud
4 min read
0
Reading Excel file in Azure Web Apps
0
SHARES
1.8k
VIEWS
Share on FacebookShare on Twitter

Earlier this month , I was working on a small project for a client ,everyday I receive a couple of excel files that contain some data  ,I have to .

  • 1-Insert this data to a table.
    2-Clean the data(empty rows removed ,rows missing data moved to another table).
    3-check for duplicated data in this table and delete them.
    4-check for duplicated data between this table and the original table and if there is duplication and move them to another ‘duplication table’.

Now , those steps are only for one received file ,if I have more I loose a lot of time .
So ,I said to my self  ,why not build a small application or website that do all this work for me .

The requirements for my application is to browse for an excel file, Upload the file , read each row from the excel file and insert into a table , when it finish inserting a stored procedure will run a sql query that I have created ,
and of course I will host the web application on azure app service .

So , I developed the web application and tested on my machine and then publish it to azure and at this time ,
I run into the “It works on my machine”

I mean I tested the app , the connection string everything was fine , I even hosted on my local IIS and it worked smoothly .
So I said it’s time to investigate so I went to my app service , looked for “Diagnose and solve problems” and than found out this error : ‘The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine‘.

Well  , I’m using the OleDbDataAdapterto read the data from the excel file into the dataset.
And it seems that the Microsoft.ACE.OLEDB.12.0 Provider is only installed on my machine and can not be available on the azure app service .

So , I had to quickly find a new way to read my excel file without using the OleDbDataAdapter and that when I discovered a plugin : NPOI .

What is NPOI ?

NPOI is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.

For example, you can use it to

  • generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background;
  • extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines).
  • extract images from Office documents
  • generate Excel sheets that contains formulas .

You can install the package by running this command :

Install-Package DotNetCore.NPOI

 

Now , this is the way I was reading my excel file using the OleDbDataAdapter :

//get extension
string extension = Path.GetExtension(filename);           
string conString = string.Empty;
switch (extension)
{
   case ".xls": //Excel 97-03.
       conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES'";
       break;
   case ".xlsx": //Excel 07 and above.
       conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES'";
       break;
}

DataTable dt = new DataTable();
conString = string.Format(conString, filePath);

using (OleDbConnection connExcel = new OleDbConnection(conString))
{
   using (OleDbCommand cmdExcel = new OleDbCommand())
   {
       using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
       {
           cmdExcel.Connection = connExcel;
           connExcel.Open();
           DataTable dtExcelSchema;
           dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
           string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
           connExcel.Close();
           connExcel.Open();
           cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
           odaExcel.SelectCommand = cmdExcel;
           odaExcel.Fill(dt);
           var x = dt.Columns.AsParallel();
           connExcel.Close();
       }
   }
}

 

Now using the NPOI package we can do the same reading and clear out our problem and we don’t have to worry about it again .

     private static DataTable GetRequestsDataFromExcel(string fullFilePath)
     {
         try
         {
             var sh = GetFileStream(fullFilePath);
             var dtExcelTable = new DataTable();
             dtExcelTable.Rows.Clear();
             dtExcelTable.Columns.Clear();
             var headerRow = sh.GetRow(0);
             int colCount = headerRow.LastCellNum;
             for (var c = 0; c < colCount; c++)
                 dtExcelTable.Columns.Add(headerRow.GetCell(c).ToString());
             var i = 1;
             var currentRow = sh.GetRow(i);
             while (currentRow != null)
             {
                 var dr = dtExcelTable.NewRow();
                 for (var j = 0; j < currentRow.Cells.Count; j++)
                 {
                     var cell = currentRow.GetCell(j);

                     if (cell != null)
                         switch (cell.CellType)
                         {
                             case CellType.Numeric:
                                 dr[j] = DateUtil.IsCellDateFormatted(cell)
                                     ? cell.DateCellValue.ToString(CultureInfo.InvariantCulture)
                                     : cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                                 break;
                             case CellType.String:
                                 dr[j] = cell.StringCellValue;
                                 break;
                             case CellType.Blank:
                                 dr[j] = string.Empty;
                                 break;
                         }
                 }
                 dtExcelTable.Rows.Add(dr);
                 i++;
                 currentRow = sh.GetRow(i);
             }
             return dtExcelTable;
         }
         catch (Exception e)
         {
             throw;
         }
     }


private static ISheet GetFileStream(string fullFilePath)
     {
         var fileExtension = Path.GetExtension(fullFilePath);
         string sheetName;
         ISheet sheet = null;
         switch (fileExtension)
         {
             case ".xlsx":
                 using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
                 {
                     var wb = new XSSFWorkbook(fs);
                     sheetName = wb.GetSheetAt(0).SheetName;
                     sheet = (XSSFSheet)wb.GetSheet(sheetName);
                 }
                 break;
             case ".xls":
                 using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
                 {
                     var wb = new HSSFWorkbook(fs);
                     sheetName = wb.GetSheetAt(0).SheetName;
                     sheet = (HSSFSheet)wb.GetSheet(sheetName);
                 }
                 break;
         }
         return sheet;
     }

 

After this , I republished the project and everything worked fine .

well hope this was helpful ,time to get some food .
have a good day 🙂

 

ShareTweet
Previous Post

Migration from Asp.Net Core 3.1 to 5.0 and publish to azure

Next Post

Kubernetes is deprecating Docker runtime support !

Related Posts

AI

Model Context Protocol (MCP): The Future of AI Integration

April 21, 2025
133
Azure

Step-by-Step Guide: Azure Front Door + Storage Account Static Website + Custom Domain with Terraform

March 11, 2025
272
Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet
Azure

Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

February 3, 2025
141
Understanding Generative AI and RAG Benefits
AI

Understanding Generative AI and RAG Benefits

January 12, 2025
98
Azure Communication Services Email Sending Simplified: From Setup to Execution and Monitoring
Azure

Azure Communication Services Email Sending Simplified: From Setup to Execution and Monitoring

December 8, 2024
1.6k
PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis
Azure

PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis

November 2, 2024
508
Next Post
Kubernetes is deprecating Docker runtime support !

Kubernetes is deprecating Docker runtime support !

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Terraform

Certifications

Microsoft certified trainer (MCT)

Recommended

Auto Post and Schedule Tweets & Linkedin using Azure Logic Apps

Auto Post and Schedule Tweets & Linkedin using Azure Logic Apps

April 14, 2021
615
My Trip to Turkey

My Trip to Turkey

February 5, 2022
347
Microsoft Ignite Cloud Skills Challenge March 2021

Microsoft Ignite Cloud Skills Challenge March 2021

March 2, 2021
788
Font Awesome ,Bootstrap and Material Font Icons For Xamarin.Forms

Font Awesome ,Bootstrap and Material Font Icons For Xamarin.Forms

April 26, 2020
896
How to make the most of each day

How to make the most of each day

February 2, 2021
318
Sql tips and tricks

Sql tips and tricks

April 26, 2020
219
Facebook Twitter LinkedIn Youtube

Model Context Protocol (MCP): The Future of AI Integration

April 21, 2025

Step-by-Step Guide: Azure Front Door + Storage Account Static Website + Custom Domain with Terraform

March 11, 2025
Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

Network Security & Route Tables – Checking NSGs, route tables, and service endpoints for a targeted VNET or Subnet

February 3, 2025

Categories

  • AI (2)
  • Apps (1)
  • Azure (63)
  • blazor (2)
  • Blog (91)
  • c# (7)
  • Cloud (65)
  • Courses (3)
  • Dapr (4)
  • docker (4)
  • Games (1)
  • General Tips & Fix (1)
  • Home (1)
  • Kubernetes Service (AKS) (1)
  • motivation (2)
  • Motivation (3)
  • News (9)
  • Resume (1)
  • sql (4)
  • Terrafrom (1)
  • Tricks, Tips and Fixes (4)
  • xamarin (5)
No Result
View All Result
  • Home
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
    • General Tips & Fix
  • AI
  • Cloud
  • Motivation
  • Courses
  • About
    • Resume
    • Privacy Policy