In this sample we will read an excel file using ExcelLite library and display file contents in a Textbox control. Column values will be separated by COMMA and rows will be separated by NEW LINE.
Add a button to open file dialog for selecting excel file. To display file contents add a textbox control.
Add reference to both of the library DLLS, refer “Lite.ExcelLibrary.SpreadSheet” on the top of your page
using Lite.ExcelLibrary.SpreadSheet;
Write the following code in the click event of your button. Code is commented heavily for demonstration.
//To access file system we need to use Silverlight file dialogs
OpenFileDialog oFile = new OpenFileDialog();
// .xls filter specified to select only .xls file.
oFile.Filter = "Excel (*.xls)|*.xls";
if (oFile.ShowDialog() == true)
{
// Get the stream of the selected file
FileStream fs = oFile.File.OpenRead();
// Simply call the Open method of Workbook and you are done
Workbook book = Workbook.Open(fs);
// All of the worksheet will be populated with data
// currently we will read only first for this sample
Worksheet sheet = book.Worksheets[0];
/// itrating through worksheet object to get values
/// Worksheet.Cells.FirstRowIndex tells the First row index of data
/// Worksheet.Cells.LastRowIndex tells the last of data
/// Worksheet.Cells.FirstColIndex has first index of column value
/// Worksheet.Cells.LastColIndex has last index of column
/// So itrating using these properties will traverse all data of the sheet
///
for (int i = sheet.Cells.FirstRowIndex; i < sheet.Cells.LastRowIndex; i++)
{
for (int j = sheet.Cells.FirstColIndex; j < sheet.Cells.LastColIndex; j++)
{
/// value of each cell is separated by a coma
this.textBox1.Text += sheet.Cells[i, j].StringValue;
this.textBox1.Text += ",";
}
// New row will be displayed on next line
this.textBox1.Text += Environment.NewLine;
}
}
