Popular Posts

There was an error in this gadget

Feb 19, 2014

Excel to Gridview





In .aspx file:  

<form id="form1" runat="server">
    <div>
        <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <br />
    <table>
    <tr><td><asp:FileUpload ID="testFileUpload" runat="server" /></td><td><asp:Button ID="btnUpload" runat="server" Height="21px" Text="Upload" Width="92px" onclick="btnUpload_Click" /></td></tr>
    </table>
    <br /> 
    <asp:GridView ID="dataGridView" runat="server">
    </asp:GridView>
    </div>
    </form>



In .cs file:


    string fileName = string.Empty;
    string fileExtension = string.Empty;
    string fileLocation = string.Empty;
    string getExcelSheetName = string.Empty;
    string connectionString = string.Empty;

    DataTable dtExcelSheetName = null;
    DataTable dtExcelRecords = null;

    OleDbConnection con = null;
    OleDbCommand cmd = null;
    OleDbDataAdapter dAdapter = null;


    protected void Page_Load(object sender, EventArgs e)
    {
        cmd = new OleDbCommand();
        dtExcelRecords = new DataTable();
    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        try
        {
            if (testFileUpload.HasFile)
            {
                fileName = Path.GetFileName(testFileUpload.PostedFile.FileName);
                fileExtension = Path.GetExtension(testFileUpload.PostedFile.FileName);
                fileLocation = Server.MapPath("~/App_Data/" + fileName);

                GetData(fileLocation);
                Label1.Text = "File is ok to upload.";
            }
        }
        catch (Exception ex)
        {
            Label1.Text = "File is not expected formated.";
        }
    }


    public void GetData(string loc)
    {
        testFileUpload.SaveAs(fileLocation);
        //Check whether file extension is xls or xslx

        if (fileExtension == ".xls")
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + loc + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        }
        else if (fileExtension == ".xlsx")
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + loc + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        }

        dtExcelRecords.Clear();
        dtExcelRecords.Reset();

        //Create OleDB Connection and OleDb Command

        con = new OleDbConnection(connectionString);

        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Connection = con;
        dAdapter = new OleDbDataAdapter(cmd);

        con.Open();
        dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
        cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
        dAdapter.SelectCommand = cmd;
        dAdapter.Fill(dtExcelRecords);
        con.Close();

        dataGridView.DataSource = dtExcelRecords;
        dataGridView.DataBind();

    }

No comments:

Post a Comment