Creating an Excel application in Dot Net.

We can do the creation of excel application in Dot Net by using the interoperability services. The interoperability services make it easy to work with COM Capable Applications such as Word and Excel.



First of all we need to create a reference in our project to Excel object library by using add reference. You can get an “Add Reference” dialog box when clicking the add reference, choose com tab and select “Microsoft Excel 9.0 Object Library” as follows.



After adding this library we can get Interop.Excel.dll and Interop.Office.dll files in our bin folder. Using these dll files we can manipulate excel like excel file creation, opening and updating.

When we ready to write code, first create an object of Excel.Application, Excel._Workbook and Excel._Worksheet. These are going to be used in creation of excel application.

How to create excel application?

This is the way to create an excel application. See the following code for help us to create an excel application.

protected void btnCreateExcel_Click(object sender, EventArgs e)
{
try
{
file = @"C:\temp\test.xls";
ex = new Excel.Application();
Excel._Workbook wb;
Excel._Worksheet ws;
wb = (Excel._Workbook)(ex.Workbooks.Add(Missing.Value));
ws = (Excel._Worksheet)(wb.Sheets["Sheet1"]);
ws.Name = "MyFirstSheet";
ws.Cells[1, 1] = "Test1";
ws.Cells[1, 2] = "Test2";
ws.Cells[2, 1] = "Test3";
ws.Cells[2, 2] = "Test4";
ws.Cells[3, 1] = "Test5";
ws.Cells[3, 2] = "Test6";
ws.get_Range("A1", "A3").Font.Bold = true;
ws.get_Range("A1", "C3").EntireColumn.AutoFit();
//wb.SaveAs(file, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveCopyAs(file);
wb.Close(null, null, null);
ex.Workbooks.Close();
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
}
catch (Exception ex)
{
Response.Write("Error: \n" + ex.ToString());
}
}

By using the above code, now we have created an excel application test.xls.

How to open an excel application and how can update with that application?

Workbooks.Open method is used to open an excel application. Then we can assign the values of some particular cells and will save that application by using the save method. For more, see the following code.

protected void btnChangeExcel_Click(object sender, EventArgs e)
{
try
{
file = @"C:\temp\test.xls";
ex = new Excel.ApplicationClass();
Excel._Workbook wbObj = null;
Excel._Worksheet wsObj = null;
Excel.Range rngObj = null;
wbObj = ex.Workbooks.Open(file, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true);
wsObj = (Excel._Worksheet)wbObj.Worksheets[1];
wsObj.Cells[1, 1] = "1";
wsObj.Cells[1, 2] = "2";
wsObj.Cells[1, 3] = "3";
wsObj.Cells[2, 1] = "A";
wsObj.Cells[2, 2] = "B";
wsObj.Cells[2, 3] = "C";
rngObj = wsObj.get_Range("A1", "C2");
rngObj.EntireRow.AutoFit();
rngObj.EntireColumn.AutoFit();
wbObj.Save();
wbObj.Close(null, null, null);
ex.Workbooks.Close();
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rngObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
}
catch (Exception ex)
{
Response.Write("Error: \n" + ex.ToString());
}
}


That’s it. Hence we have created an excel application and updating some particular cells in that application.

...S.VinothkumaR.