Open And Display Excel File In C#

Excel creates XLS and XLSX files. These files are hard to read in C# programs. They are handled with the Microsoft.Office.Interop.Excel assembly

STEP – 1

Add the Microsoft.Office.Interop.Excel assembly by going to Project -> Add Reference as shown in below image

ref

Now in design take one button, textbox or textblock and datagrid to display excel. textbox or textblock is use only to show path of excel file.

STEP – 2

Add namespace in code

using System.Windows.Interop;

using Microsoft.Office.Interop.Excel;

STEP – 3

Now use interop classes to open excel file as given below. I am using this code in button click event:

private void BtnOpenExcel_Click(object sender, RoutedEventArgs e)

{

OpenFileDialog openfile = new OpenFileDialog();

openfile.DefaultExt = “.xlsx”;

openfile.Filter = “(.xlsx)|*.xlsx”;

openfile.Filter = “Excel Files|*.xls;*.xlsx;*.xlsm”;

openfile.ShowDialog();

var browsefile = openfile.ShowDialog();

if (browsefile == true)

{

txtFilePath.Text = openfile.FileName;

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open(txtFilePath.Text.ToString(), 0, true, 5, “”, “”, true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, “\t”, false, false, 0, true, 1, 0);

Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.Worksheets.get_Item(1);

Microsoft.Office.Interop.Excel.Range excelRange = excelSheet.UsedRange;

string strCellData = “”;

double douCellData;

int rowCnt = 0;

int colCnt = 0;

System.Data.DataTable dt = new System.Data.DataTable();

for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)

{

string strColumn = “”;

strColumn = (string)(excelRange.Cells[1, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;

dt.Columns.Add(strColumn, typeof(string));

}

for (rowCnt = 2; rowCnt <= excelRange.Rows.Count; rowCnt++)

{

string strData = “”;

for (colCnt = 1; colCnt <= excelRange.Columns.Count; colCnt++)

{

try

{

strCellData = (string)(excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;

strData += strCellData + “|”;

}

catch (Exception ex)

{

douCellData = (excelRange.Cells[rowCnt, colCnt] as Microsoft.Office.Interop.Excel.Range).Value2;

strData += douCellData.ToString() + “|”;

}

}

strData = strData.Remove(strData.Length – 1, 1);

dt.Rows.Add(strData.Split(‘|’));

}

datagrid1.ItemsSource = dt.DefaultView;

excelBook.Close(true, null, null);

excelApp.Quit();

}

}

Below screen shot show process of opening excel file in c#

Click on button and select excel file

ref1

ref2

Now excel file is ready to show !!!!

ref3

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s