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.