Related to upload multiple file

Bhupendra Gupta

I having a web application using asp.net c#,and that application upload multiple Excel files and Doing the Excel sheet split based on country code and doning the ascending order based on your price column and finally i want to show all the records in girdview ,fornt of all records show the excel sheet name.

9 Replies

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

Sundaram Subramanian9y ago
Accepted answer
For a Single Excel Upload I show the Code

Excel to Datatable
==============================

public static DataTable exceldata(string filePath)
{
DataTable dtexcel = new DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//Looping Total Sheet of Xl File
/*foreach (DataRow schemaRow in schemaTable.Rows)
{
}*/
//Looping a first Sheet of Xl File
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [" + sheet3 + "]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}

conn.Close();
return dtexcel;

}

Excel Sheet Name to Datatable Column
=====================================
OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0 xml;HDR=YES;'");
connection.Open();
DataTable Sheets = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in Sheets.Rows)
{
string sht = dr[2].ToString().Replace("'", "");
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + sht + "]", connection);
}

ascending order the datatable column
===================================

DataView view = date.DefaultView;
view.Sort = "date1 ASC";
DataTable sortedDate = view.ToTable();

Datatable to Gridview
========================

gridview.datasource= DatatableName ;
gridview.databind();
Bhupendra Gupta9y ago
Good Noon Sunder sir,
sir I want contact you.
Bhupendra Gupta9y ago
Sunder sir ,can my system take on teamviewer?
Bhupendra Gupta9y ago
Thanku sir
Bhupendra Gupta9y ago
sir,Can you send me source code of this query?
Sundaram Subramanian9y ago
Then keep one column as Sheet Name, then show.
Bhupendra Gupta9y ago
thanks for this solution Sundar sir,bt sir i want to all showing records fornt of Ecxcel sheet name.
Sundaram Subramanian9y ago
Logical Work

I will give you a logical work.

1) Create a datable which can hold all the columns in all Excel File. in that excel it should contain columns in all excel sheet.

2) Upload your all excels. Store the excel values into the Datatable.

3) apply split using linq in datatable (Based on Counrty and Price)

4) Bind that Datatable to Grid. (Final result's Datatable)
Bhupendra Gupta9y ago
I having a web application using asp.net c#,and that application upload multiple Excel files and Doing the Excel sheet split based on country code and doning the ascending order based on your price column and finally i want to show all the records in girdview ,fornt of all records show the excel sheet name.