Determine used range in excel using c#

Question

I am reading data from excel sheet using c#.I want to increase the speed of reading data in each excel by limiting the c# to read only rows that have data.How can i achieve this especially with some excel that comes with rows that are formatted the same way even with no data.

Karthik Elumalai · Answer

Hi Austin, Introduction : As we know For reading data from excel sheet using C#, can be done mainly in two ways.these are 1.http://www.c-sharpcorner.com/blogs/read-excel-files-using-microsoft-office-interop-assemblies-in-asp-net-c-sharp 2.http://www.c-sharpcorner.com/article/read-excel-files-using-open-xml-sdk-in-asp-net-c-sharp/ Difference: Since In our project, the main scope is to read the plenty of client excel files,Personally i have used both and got to know the difference. Mainly the difficulty i faced ,when i used interop assemblies was , 1. i need to install the Excel application on theserver side . 2.And most of the time i get an error like your application used by an another process. so both my problem was resolved by OPENXML SDK and it was pretty good and speed compare to interop. In the above Links , I have explained both in detail and provided the simple example to take the basic values. if you want to read the entire workbook with multiple worksheet,then the below code will be useful: C# code:--- button Click event: protected void insertBoqElements_Click ( object sender, EventArgs e ) { try { OpenXml ( ); } catch(Exception Ex) { lbldisplayerrors.Text = Ex.Message; } } public void OpenXml ( ) { try { //specify the file name where its actually exist string filepath = @"D:\TPMS\Uploaded_Boq\Official_Details.xlsx"; //open the excel using openxml sdk using(SpreadsheetDocument doc = SpreadsheetDocument.Open ( filepath, false )) { //create the object for workbook part WorkbookPart wbPart = doc.WorkbookPart; Sheets thesheetcollection = wbPart.Workbook.GetFirstChild ( ); //Sheet thesheet = (Sheet)thesheetcollection.FirstChild; //using for each loop to get the sheet from the sheetcollection foreach(Sheet thesheet in thesheetcollection) { lbldisplayerrors.Text += "Excel Sheet Name : " + thesheet.Name + " "; lbldisplayerrors.Text += "----------------------------------------------- " + " "; //statement to get the worksheet object by using the sheet id Worksheet theWorksheet = ((WorksheetPart)wbPart.GetPartById ( thesheet.Id )).Worksheet; SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild ( ); foreach(Row thecurrentrow in thesheetdata) { foreach(Cell thecurrentcell in thecurrentrow) { //statement to take the integer value string currentcellvalue = string.Empty; if(thecurrentcell.DataType != null) { if(thecurrentcell.DataType == CellValues.SharedString) { int id; if(Int32.TryParse ( thecurrentcell.InnerText, out id )) { SharedStringItem item = GetSharedStringItemById ( wbPart, id ); if(item.Text != null) { //code to take the string value lbldisplayerrors.Text += item.Text.Text + " "; } else if(item.InnerText != null) { currentcellvalue = item.InnerText; } else if(item.InnerXml != null) { currentcellvalue = item.InnerXml; } } //end of if (Int32.TryParse ( thecurrentcell.InnerText, out id ) } //end of if(thecurrentcell.DataType == CellValues.SharedString) } // end of if(thecurrentcell.DataType != null) else { lbldisplayerrors.Text += Convert.ToInt16 ( thecurrentcell.InnerText ) + " "; } } // end of foreach(Cell thecurrentcell in thecurrentrow) lbldisplayerrors.Text += " "; } //end of foreach(Row thecurrentrow in thesheetdata) lbldisplayerrors.Text += " "; lbldisplayerrors.Text += " "; }//end of foreach(Sheet thesheet in thesheetcollection) } //end of using }//end of try catch(Exception Ex) { lbldisplayerrors.Text = Ex.Message; } } Hope the above informations was litte bit helpful and kindly let me know your thoughts thanks karthik