Excel Operations Using Excel Interop Assembly

Here is code to read excel file which will read file and converts its cell to specific format like date format or number format or so and then save new version of file to take effect of the new file converted with provided format and leave original file as it is.

For the excel file to read, it is 2010pia which is installed to read excel file and in reference there will be version with 15.0 for excel is used to read excel file. The link to download is below

Download Microsoft Office 2010: Primary Interop Assemblies Redistributable

Below code to read excel file using office interop excel assembly and perform changes in cell and save that file
Microsoft.Office.Interop.Excel.Application objexcel = new Microsoft.Office.Interop.Excel.Application();

objexcel.DisplayAlerts = false;

Microsoft.Office.Interop.Excel.Workbook workbook = objexcel.Workbooks.Open("your file path", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);

rangeoutput = sheet.UsedRange;

int lastRow = sheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;

int lastColumn = sheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;

Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range)sheet.Cells[lastRow, lastColumn]);

oRange.EntireColumn.AutoFit();

for (int i = 0; i < oRange.Columns.Count; i++)
{
    dt.Columns.Add("a" + i.ToString());
}

object[,] cellValues = (object[,])oRange.Value2;

object[] values = new object[lastColumn];

//Loop through the all rows of the excel file and operate to change the type of the cell
for (int i = 1; i <= lastRow; i++)
{
    //Loop through the columns and get the each cell value from rows
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        //This implementation for selecting columns names from excel file
        if (i == 1)
        {
    //If you know the column name then from here set for the entire column format
            sheet.get_Range("B1").EntireColumn.NumberFormat = "dd/MM/yyyy";
        }
        else
        {
    //if entire column format is not get set then go with this else part where if you know the cell index then go with it
            if (j == 13)
            {
//for me it was date operation to perform and i checked statically with 13 which when comes picks the value for cell and convert it
                string str = Convert.ToString(cellValues[i, j + 2]);
                DateFormat numFormat = strColumns.Where(x => x.ColumnIndex == j + 2).FirstOrDefault();
                if (!string.IsNullOrWhiteSpace(str))
                {
                    double dblResult = 0;
                    bool blnIsDbl = double.TryParse(str, out dblResult);
                    if (blnIsDbl)
                    {
(rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).NumberFormat = numFormat.ColumnFormat;
                        double d = double.Parse(str);
                        DateTime conv = DateTime.FromOADate(d);
                        values[j] = conv.ToShortDateString();// cellValues[i, j + 1];
                        string strNewValue = conv.ToString(numFormat.ColumnFormat);
                        (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value = strNewValue;
                        (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = strNewValue;
                    }
                    else
                    {
                        //if cell value are correct then fine but if it is wrong then here in else part checking its value then if it is wrong then keep it as it is
                        try
                        {
                            DateTime dt1;
                            bool IsValidDate = DateTime.TryParseExact(str, numFormat.ColumnFormat, null, DateTimeStyles.None, out dt1);
                            if (IsValidDate)
                            {
                                string strNewValue = dt1.ToString(numFormat.ColumnFormat);
                                (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value = strNewValue;
                                (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = strNewValue;
                            }
                            else
                            {
                                (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value = str;
                                (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = str;
                                intInvalidRows.Add(i);
                            }
                        }
                        catch (Exception Exc)
                        {
                            Exc.Message.ToString();
                            (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value = str;
                            (rangeoutput.Cells[i, j + 2] as Microsoft.Office.Interop.Excel.Range).Value2 = str;
                        }
                    }
                }
            }
        }
    }
}

//Saves the changed excel file and then closes the file along with current workbook
objexcel.DisplayAlerts = false;

objexcel.ActiveWorkbook.SaveAs(Filename: "file path to save", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, CreateBackup: false);

objexcel.ActiveWorkbook.Close(SaveChanges: false);

objexcel.DisplayAlerts = true;

objexcel.Quit();

There are multiple operations has been performed here like opening file/changing file content/assigning cell format/save file using Excel Interop Assembly.

Error: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

While working with Excel Interop Assembly for reading an excel file, I come up with error "Error: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". Found below error resolution for the error and performing steps in below links resolve my problem.

(1) System.UnauthorizedAccessException: Retrieving the COM class factory for Word Interop fails with error 80070005

(2) Access Denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) - Powered by Kayako Help Desk Software

Performing steps provided in above links resolve my problem. 

Step to reproduce this error: While reading an excel file using Interop assembly, on below line of code

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

It throws above error right from this line of code.

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

Here i found another error generated over working with importing excel file. So, my idea behind importing an excel file is simple shown below.

Code to import excel file is shown below.
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties='Excel 12.0;HDR=YES';";
DataTable dt = new DataTable();
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
        dataAdapter.Fill(ds);
}

but, doing this stuff i found error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."


Googling around this error help me out by two cases. For the solution, i found following two links useful.
(1) 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

(2) 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine error

So, that's it for this error.

Some Links about Excel

Here is some links about various topics in excel.

- Named Range and Data Validations

(1) Excel 2007 Named Ranges and Data Validation : Steve Smith's Blog

(2) editing a data validation list in Excel Miscellaneous

(3) AJP Excel Information

- Excel Shortcut Keys
(1) Excel shortcut and function keys - Excel - Microsoft Office Online

(2) Windows Keyboard Shortcuts » Microsoft Excel

- How to add controls to Excel file
(1) Embed a SWF file in Excel

(2) How to: Add Controls to a Document [Excel 2007 Developer Reference]

(3) How To Embed SWF In Microsoft Office 2007 Document
 
- Show Calender control for cell and assign selected value
(1) Ms-office- Add The Microsoft Excel Calendar Control To Any Excel Workbook Tutorials

(2) VBA Tips: A Pop-up Calendar for Excel

(3) Insert a Drop Down Calendar Menu In Excel – Choose a Date!