Working with Excel file in C#
It is easy to work with Microsoft Excel files in C# by using Microsoft.Office.Interop.Excel dll. In this article we discuss about how to create Excel file, how to read Excel file and how to write Excel file in C#.
First we have to add reference to the Microsoft.Office.Interop.Excel dll. To add reference, right click on solution explorer, select Add Reference and select Microsoft.Office.Interop.Excel.dll as shown below.
First we create Excel file and write some data to Excel file. In Microsoft.Office.Interop.Excel namespace we have the Application class for which we have to create the object to create Excel file. Workbook class is used to create workbook for the Excel file. By using Application and Workbook classes we can create the Excel file and we can write the data to excel file as shown below.
///<summary>
/// Create and Write to Excel file
///</summary>
///<param name="sender"></param>
///<param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook oWB = oXL.Workbooks.Add(Missing.Value);
Excel.Worksheet oWS = oWB.Worksheets[1] as Excel.Worksheet;
//rename the Sheet name
oWS.Name = "Sample Excel Sheet";
for (int i = 1; i < 10; i++)
{
oWS.Cells[i, 1] = "Sample Excel Sheet Cell - " + i.ToString();
}
oWB.SaveAs(Application.StartupPath + "\\ExcelSample.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
oWB.Close(true, Missing.Value, Missing.Value);
}
In the same way by using Application and Workbook classes under Microsoft.Office.Interop.Excel name space we can open and read the content of Excel file as shown below.
///<summary>
/// Open and Read Excel file
///</summary>
///<param name="sender"></param>
///<param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook oWB = oXL.Workbooks.Open(Application.StartupPath + "\\ExcelSample.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Excel.Worksheet oWS = oWB.Worksheets[1] as Excel.Worksheet;
Excel.Range range;
range = oWS.UsedRange;
//read first row, first cell value
MessageBox.Show((string)(range.Cells[1, 1] as Excel.Range).Value2);
}
For Microsoft Office Promo Codes, Microsoft Excel Codes Click here
