I am going to share a code sample where I am going to read data from excell and use Linq Query to process that data.
//declaring all variables that I will use
Workbook workBook;
IEnumerable<Sheet> workSheets;
WorksheetPart excelSheet;
string excellsheetid;
List<FormTag> tags;
SharedStringTable sharedStrings;
//Code to open Excel File
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(
@"C:\Temp\TestExcel.xlsx",
true))
{
//References to the workbook and Shared String Table.
workBook = document.WorkbookPart.Workbook;
workSheets = workBook.Descendants<Sheet>();
sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
//Reference to Excel Worksheet with Customer data.
excellsheetid =
workSheets.First(s => s.Name == "Sheet1").Id;
//Reading Excell Sheet
excelSheet =
(WorksheetPart)document.WorkbookPart.GetPartById(excellsheetid);
//LINQ query to skip first row with column names.
IEnumerable<Row> dataRows =
from row in excelSheet.Worksheet.Descendants<Row>()
where row.RowIndex > 1
select row;
//Looping through all rows after eliminating first row..assuming first row is a header row
foreach (Row row in dataRows)
{
//LINQ query to return the row's cell values.
//Where clause filters out any cells that do not contain a value.
//Select returns the value of a cell unless the cell contains
// a Shared String.
//If the cell contains a Shared String, its value will be a
// reference id which will be used to look up the value in the
// Shared String table.
IEnumerable<String> textValues =
from cell in row.Descendants<Cell>()
where cell.CellValue != null
select
(cell.DataType != null
&& cell.DataType.HasValue
&& cell.DataType == CellValues.SharedString
? sharedStrings.ChildElements[
int.Parse(cell.CellValue.InnerText)].InnerText
: cell.CellValue.InnerText)
;
//Check to verify the row contained data.
if (textValues.Count() > 0)
{
//Create your object for eg. a customer object and and manipulate it.
var textArray = textValues.ToArray();
Cutomer cust = new Cutomer();
cust.CustId = textArray[0];
cust.CustName = textArray[1];
}
else
{
//If no cells, then you have reached the end of the table.
break;
}
}
}
No comments:
Post a Comment