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
95
Azure

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

March 11, 2025
212
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
132
Understanding Generative AI and RAG Benefits
AI

Understanding Generative AI and RAG Benefits

January 12, 2025
95
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.5k
PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis
Azure

PowerShell Automation for Azure Networks: Detailed VNET and Subnet Analysis

November 2, 2024
495
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

Understanding Generative AI and RAG Benefits

Understanding Generative AI and RAG Benefits

January 12, 2025
95
Dapr – State management (redis)  Part 1/2

Dapr – State management (redis) Part 1/2

August 17, 2021
751

How To host a Next.js app on Azure

October 5, 2020
1.5k
Background Tasks With Hangfire And .Net 5

Background Tasks With Hangfire And .Net 5

January 25, 2021
3k
Migrate and modernize your applications on Azure

Migrate and modernize your applications on Azure – Part 2.0 (Azure Functions)

April 3, 2021
542
Part 5-A : Using Azure DevOps, Automate Your CI/CD Pipeline and Your Deployments

Part 5-A : Using Azure DevOps, Automate Your CI/CD Pipeline and Your Deployments

April 17, 2023
465
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