C# – Working with Excel

Working with Excel

Library

Include

using Excel = Microsoft.Office.Interop.Excel

Instantiating the excel application class

Excel.Application excelApp = new Excel.Application();

To make the object visible

excelApp.Visible = true; //false to turnoff visibility

Add a workbook

Excel.Workbook openExcel = excelApp.Workbooks.Add(Type.Missing);

or to add an existing excel file

var path = @"C:\\excel.xlsx";
Excel.Workbook openExcel = excelApp.Workbooks.Open(path);

To add blank sheets

Excel.Sheets sheets = openExcel.Worksheets;
Excel._Worksheet worksheet = (Excel.Worksheet)sheets.Add(Type.Missing);

To select active sheet

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

or To select sheet by name

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetname];

Writing to the cell

workSheet.Cells[1, "A"] = "Fund";

Set the column width

workSheet.Columns.ColumnWidth = 18;

Using get_Range and format header

Excel.Range headerColumnRange = workSheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
headerColumnRange.WrapText = true;
headerColumnRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

Name of the worksheet

workSheet.Name = name.Substring(0, len);

Format cells

workSheet.Cells[startCell, strAlpha].NumberFormat = "0.00";

Autofit columns

workSheet.Columns.AutoFit();

Save

openExcel.Save();

SaveAs .xlsx

openExcel.SaveAs(strFullFilePathNoExt, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
    Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, 
    Excel.XlSaveConflictResolution.xlUserResolution, true, 
    Missing.Value, Missing.Value, Missing.Value);

Close excel

openExcel.Close();

Copy or Cloning

foreach(Worksheet sheet in workBook.Worksheets)
{
    var newbook = app.Workbooks.Add(1);
    sheet.Copy(newbook.Sheets[1]);

    newbook.SaveAs(FileDropLocation + "\\" + sheet.Name);
    newbook.Close();
}

workBook.Close();

Delete worksheet

workSheet.Worksheets[2].Delete();

Border

Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;  
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
border.Weight = 2d;