Achraf Ben Alaya
No Result
View All Result
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
  • Cloud
  • Motivation
  • General Tips & Fix
  • Exam Preparation
    • AZ-104
    • AZ-400
  • About
    • Resume
SUBSCRIBE
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
  • Cloud
  • Motivation
  • General Tips & Fix
  • Exam Preparation
    • AZ-104
    • AZ-400
  • About
    • Resume
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.1k
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

Switch to Azure Container Registry from Docker Hub
Azure

From Docker Hub, switch to Azure Container Registry & AKS

January 16, 2023
132
How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)
Azure

How to configure an ingress controller using TLS/SSL for the Azure Kubernetes Service (AKS)

November 25, 2022
229
How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)
Azure

How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)

November 23, 2022
102
How to setup nginx reverse proxy for aspnet core apps with and without  Docker compose
Azure

How to setup nginx reverse proxy for aspnet core apps with and without Docker compose

November 23, 2022
153
Win free certifications at the Microsoft Build Cloud Skills Challenge | May 2022 🎁
Blog

Win free certifications at the Microsoft Build Cloud Skills Challenge | May 2022 🎁

May 28, 2022
107
Generating report for SSL Certificates for Websites with PowerShell
Azure

Generating report for SSL Certificates for Websites with PowerShell

April 10, 2022
386
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

Azure Static Web App Service

Azure Static Web App Service

September 27, 2020
548
My 2020 Review

My 2020 Review

December 31, 2020
168
Background Tasks With Hangfire And .Net 5

Background Tasks With Hangfire And .Net 5

January 25, 2021
2.4k
My Trip to Turkey

My Trip to Turkey

February 5, 2022
223
Deploy azure function from Docker Hub CI/CD

Deploy azure function from Docker Hub CI/CD

April 27, 2020
256
DevOps : Deploy infrastructure using Terraform and Azure DevOps pipelines

DevOps : Deploy infrastructure using Terraform and Azure DevOps pipelines

June 2, 2021
3.4k
Facebook Twitter LinkedIn Youtube
Switch to Azure Container Registry from Docker Hub

From Docker Hub, switch to Azure Container Registry & AKS

January 16, 2023
How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)

How to configure an ingress controller using TLS/SSL for the Azure Kubernetes Service (AKS)

November 25, 2022
How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)

How to setup nginx reverse proxy && load balancer for aspnet core apps with Docker and azure kubernetes service (AKS)

November 23, 2022

Categories

  • Apps (1)
  • Azure (39)
  • blazor (2)
  • Blog (62)
  • c# (7)
  • Cloud (37)
  • Dapr (4)
  • docker (3)
  • Games (1)
  • General Tips & Fix (1)
  • motivation (2)
  • Motivation (3)
  • News (9)
  • Resume (1)
  • sql (4)
  • Tricks, Tips and Fixes (3)
  • xamarin (5)
No Result
View All Result
  • News
  • Blog
    • blazor
    • c#
    • Cloud
      • Azure
    • docker
    • sql
    • xamarin
    • Dapr
    • Tricks, Tips and Fixes
  • Cloud
  • Motivation
  • General Tips & Fix
  • Exam Preparation
    • AZ-104
    • AZ-400
  • About
    • Resume