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;