Export the excel file using Browse Option in asp.net using c

narasiman rao

12y3.3kC#

I am exporting the excel file data into gridview.

for that code as follows

protected void btnimport_Click(object sender, EventArgs e)
{
import();
}

private void import()
{
string path = @"C:\FACULTY TT N.xlsx";
GvSch.DataSource = ImportExcelXLS(path, false);
GvSch.DataBind();
GvSch.Visible = true;
}

public static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (FileName.Substring(FileName.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";

DataSet output = new DataSet();

using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();

if (!sheet.EndsWith("_"))
{
try
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;

DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);

DataTable table = new DataTable();
table.Columns.Add("Date", typeof(string));
table.Columns.Add("Course", typeof(string));
table.Columns.Add("Session", typeof(string));
table.Columns.Add("Subject", typeof(string));
table.Columns.Add("Faculty", typeof(string));

int irow = 0;
foreach (DataRow row in outputTable.Rows)
{
if (row[1].ToString() != "Course" && row[1].ToString() != "" && row[1].ToString() != null)
{
DataRow row1 = table.NewRow();
row1["Date"] = row[0].ToString();

row1["Course"] = row[1].ToString();
row1["Session"] = "1";
row1["Subject"] = row[2].ToString();
row1["Faculty"] = row[3].ToString();

if (row[2].ToString().Trim() != "" && row[3].ToString().Trim() != "" && row[2].ToString().Trim() != null && row[3].ToString().Trim() != null)
table.Rows.Add(row1);
row1 = table.NewRow();
row1["Date"] = row[0].ToString();
row1["Course"] = row[1].ToString();
row1["Session"] = "2";
row1["Subject"] = row[4].ToString();
row1["Faculty"] = row[5].ToString();

if (row[4].ToString().Trim() != "" && row[5].ToString().Trim() != "" && row[4].ToString().Trim() != null && row[5].ToString().Trim() != null)
table.Rows.Add(row1);

}
irow++;
}
output.Tables.Clear();
output.Tables.Add(table);
}
catch (Exception ex)
{
throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, FileName), ex);
}
finally
{

}
}
}
}
return output;
}


In run mode when i click the Import Button, in Gridview the excel records are displayed in the gridview.


But i want the user to select the excel file, when user click the browse button and click ok .

Then user click the Import button then only excel record to be displayed in the gridview.



In coding i specifically mention the Path manually
string path = @"C:\FACULTY TT N.xlsx";

i want user to select the excel file and click ok.then click the import button in gridview record to be displayed.

for that how can i do in asp.net using c#

Regards,
Narasiman P.

3 Replies

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

Richard Porter11y ago

narasiman try this:

protected void Button1_Click(object sender, EventArgs e)
{
    if (this.FileUpload1.HasFile)
    {
        string extension = Path.GetExtension(this.FileUpload1.FileName.ToUpperInvariant());
        LoadOptions options;
        switch(extension)
        {
            case ".XLSX":
                options = LoadOptions.XlsxDefault;
                break;
            case ".XLS":
                options = LoadOptions.XlsDefault;
                break;
            case ".CSV":
                options = LoadOptions.CsvDefault;
                break;
            default:
                return;
        }
 
        ExcelFile excel = ExcelFile.Load(this.FileUpload1.FileContent, options);
        DataSet output = new DataSet();
        foreach (var sheet in excel.Worksheets)
            output.Tables.Add(
                sheet.CreateDataTable(
                    new CreateDataTableOptions()));
 
        this.GvSch.DataSource = output;
        this.GvSch.DataBind();
        this.GvSch.Visible = true;
    }
}

I added a button control (in above it's named 'Button1') and file upload control (in above it's named 'FileUpload1'). Also I used this .NET's DLL for excel processing which provides a direct way for exporting Excel file to DataSet object.

Nitesh Kejriwal12y ago
If ti is a Windows application, then what Farhan told is the correct way. if its a web application, then you will need to upload the excel file using FileUpload control 1st and then handle the import functionality in handler of Import button's click.
Farhan Shariff12y ago
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
// To list only csv files, we need to add this filter
openFileDialog.Filter = "|*.xls";

DialogResult result = openFileDialog.ShowDialog();

if (result == DialogResult.OK)
{
tbCSVPath1.Text = openFileDialog.FileName;

}

}

private void import()
{
string path = tbCSVPath1.Text;
GvSch.DataSource = ImportExcelXLS(path, false);
GvSch.DataBind();
GvSch.Visible = true;
}