Determine used range in excel using c#

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 += "
";

}//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

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Karthik Elumalai10y ago
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