Blog for .net, SharePoint: Office

Showing posts with label Office. Show all posts

Tuesday, February 5, 2019

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.

Thursday, January 31, 2019

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.

Friday, July 11, 2014

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.

Tuesday, May 15, 2012

How to remove your Outlook Add-In from Outlook

Here is the steps how to remove your Outlook Add-In from your Outlook 2007/2010

(A) Outlook 2010
(1) File –> Options it will open up with Options Dialog shown below in screenshot.
Select “Add-Ins” from Left Hand side of Options Dialog

(2) From the above screenshot at the bottom, select “Go” button, and it will open up with dialog shown below in screenshot.

(3) Select "My First Add-In Friendly Name" and click on "Remove" button, it will automatically remove your Outlook Add-In from your Outlook.

(B) Outlook 2007
(1) File –> Tools --> Trust Center, it will open up with Trust Center Dialog shown below in screenshot.
Select “Add-Ins” from Left Hand side of Trust Center Dialog