How to read data from excel file in windows azure cloud service?

4
votes

I have a web application that builds using Visual Studio 2010 and uses the Windows Azure SDK. It asks the user to upload an Excel file and then read its data to the database.

When I run the application on localhost it works perfectly, but when I run it using Windows Azure SDK emulator (using my application as a web role) it gives this error:

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

How can I resolve this (other than using OpenXml)?

1
asp.netexcelvisual-studio-2010azureazure-cloud-services
In configuration manager change the debug CPU to x86 ,also try Install Microsoft Access Database Engine 2010 Redistributable,facilitate transfer of data between 2010 Microsoft Office System files and non-Microsoft Office applications. – Tharif

1 Answers

3
votes

You apparently don't use OpenXML, or use feature that proxies the read through the Office COM objects. The reason is because you probably try to read an Office 2003 or older file format (.XLS). OpenXML only supports direct read of Office 2007 or newer formats (.XLSX).

There is no way to read XLS files in Azure with OpenXML. Because for those file formats OpenXML relies on Microsoft Office Suite to be installed. And Microsoft licensing does not allow you to install Office on Cloud Server VMs. If you want to read those file formats you have to find another third party library that does not rely on OleDB providers.

To read XSLX file with OpenXML on Azure, make sure you simply open the file with the SpreadsheetDocument.Open like in:

  static void ReadExcelFileDOM(string fileName)
    {
        using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
        {
            WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
            SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
            string text;

            int rowCount= sheetData.Elements<Row>().Count();

            foreach (Row r in sheetData.Elements<Row>())
            {
                foreach (Cell c in r.Elements<Cell>())
                {
                    text = c.CellValue.Text;
                    Console.Write(text + " ");
                }
            }
            Console.WriteLine();
            Console.ReadKey();
        }