Import/Upload Excel Data to Asp.net Gridview in C#.net

Question

0.00/5 (No votes)

See more:

Hello Friends!!
I want to read an excel file and show in to the grid view but i face error when run the code,
and error is:The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

C#

    //My code is:
    string constr="";
    string strFileType = Path.GetExtension(dataread.FileName).ToLower();
    string path = dataread.PostedFile.FileName;
    if (strFileType.Trim() == ".xls")
    {
        constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path +
                    ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    }
    else if (strFileType.Trim() == ".xlsx")
    {
        constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
                   "; Extended  Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
        //OleDbConnection con = new OleDbConnection(constr);
    }

    string query = "SELECT [Name],[Data] FROM [Sheet1$]";
    OleDbConnection con = new OleDbConnection(constr);
    con.Open();

    OleDbCommand cmd = new OleDbCommand(query, con);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    grvex.DataSource = ds.Tables[0];
    grvex.DataBind();

    da.Dispose();
    con.Close();
    con.Dispose();
}

Thanks in advance..

Posted 31-Jul-13 21:52pm

Dev parmar

Updated 31-Jul-13 22:06pm

Joezer BH

v3

Add a Solution

Comments

Sushil Mate 1-Aug-13 3:57am

I think Exception is much self explanatory?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Code-Hunt · Answer 1 · 2013-07-31T22:00:00

Solution 1

http://www.aspdotnet-suresh.com/2012/12/how-to-import-data-from-excel-to-aspnet.html[^]

check this link..

Posted 31-Jul-13 22:00pm

Code-Hunt

Jameel VM · Answer 2 · 2013-07-31T22:06:00

I Recommend you to use OpenXml.You can download the sdk from here http://www.microsoft.com/en-in/download/details.aspx?id=5124[^]

After downloading create a class ExcelFile like below

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.IO;


namespace Wishes.Core
{
        public class ExcelFile
        {
            public static DataTable Read(int startRow, int lastRow, string path)
            {
                const int columnNeed = 4;
                var dt = new DataTable {TableName = "Template"};
                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(path, false))
                {
                    IEnumerable<sheet> sheets =
                        spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<sheets>().Elements<sheet>();
                    string relationshipId = sheets.First().Id.Value;
                    var worksheetPart = (WorksheetPart) spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                    Worksheet workSheet = worksheetPart.Worksheet;
                    var sheetData = workSheet.GetFirstChild<sheetdata>();
                    IEnumerable<row> rows = sheetData.Descendants<row>();
                    var enumerable = rows as Row[] ?? rows.ToArray();
                    if (enumerable.Any())
                    {
                        if (dt.Columns.Count == 0)
                        {
                            dt.Columns.Add("SINO");
                            dt.Columns.Add("EmployeeName");
                            dt.Columns.Add("DOB");
                            dt.Columns.Add("EmailId");
                            dt.Columns.Add("Photo");
                        }
                        string cellName = enumerable.ElementAt(0).Cast<cell>().Aggregate(string.Empty,
                                                                                         (current, cell) =>
                                                                                         current +
                                                                                         (cell.CellReference + ","));

                        //for checking whether the first row is there or not.
                        //if first row is not there and startrow is 1, then we have to read from 0th row
                        if (!cellName.Contains("1") && startRow == 1)
                        {
                            startRow = 0;
                        }
                        if (lastRow == 0 || lastRow >= enumerable.Count())
                        {
                            lastRow = enumerable.Count() - 1;
                        }

                        for (int rowNumber = startRow + 1; rowNumber <= lastRow; rowNumber++)
                        {
                            Row row = enumerable.ElementAt(rowNumber);
                            DataRow tempRow = dt.NewRow();
                            string value = string.Empty;

                            tempRow[0] = rowNumber;
                            for (int i = 0; i < row.Descendants<cell>().Count(); i++)
                            {
                                if (i < columnNeed)
                                    tempRow[i + 1] = GetCellValue(spreadSheetDocument, row,
                                                                  row.Descendants<cell>().ElementAt(i));
                            }
                            dt.Rows.Add(tempRow);
                        }
                    }
                }

                return dt;
            }

            private static string GetCellValue(SpreadsheetDocument document, Row row, Cell cell)
            {

                string value = string.Empty;
                if (cell.DataType != null && cell.DataType.Value == CellValues.InlineString)
                {
                    WorksheetPart workSheetPart = document.WorkbookPart.WorksheetParts.Last();
                    var t = workSheetPart.Worksheet.GetFirstChild<sheetdata>().ToList();
                    var f = t.Find(k => k.Equals(row)).FirstOrDefault(k => k.Equals(cell));
                    if (f != null) value = f.InnerText;
                }
                else
                {
                    SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
                    if (cell.CellValue != null)
                    {
                        value = cell.CellValue.InnerXml;
                    }
                    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                    {
                        value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
                    }
                }
                return value;
            }
        }
    
}

you can take the results like below

C#

string path = Application.StartupPath + "\\bTemplate.xlsx";
         DataTable dTable = ExcelFile.Read(0, 0, path);

Hope this helps

abhi sharma · Answer 3 · 2013-08-05T03:05:00

default.asox

XML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">

        <asp:GridView ID="GridView1" runat="server"
          AutoGenerateColumns = "False"
          Font-Names = "Arial"
          Font-Size = "11pt"
          AlternatingRowStyle-BackColor = "#C2D69B"
          HeaderStyle-BackColor = "green"
          AllowPaging ="True" DataSourceID="SqlDataSource1"
          >
<AlternatingRowStyle BackColor="#C2D69B"></AlternatingRowStyle>



            <Columns>
                <asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id" />
                <asp:BoundField DataField="A" HeaderText="A" SortExpression="A" />
                <asp:BoundField DataField="B" HeaderText="B" SortExpression="B" />
                <asp:BoundField DataField="C" HeaderText="C" SortExpression="C" />
                <asp:BoundField DataField="D" HeaderText="D" SortExpression="D" />
                <asp:BoundField DataField="E" HeaderText="E" SortExpression="E" />
                <asp:BoundField DataField="F" HeaderText="F" SortExpression="F" />
                <asp:BoundField DataField="G" HeaderText="G" SortExpression="G" />
                <asp:BoundField DataField="H" HeaderText="H" SortExpression="H" />
                <asp:BoundField DataField="I" HeaderText="I" SortExpression="I" />
                <asp:BoundField DataField="J" HeaderText="J" SortExpression="J" />
                <asp:BoundField DataField="K" HeaderText="K" SortExpression="K" />
                <asp:BoundField DataField="L" HeaderText="L" SortExpression="L" />
                <asp:BoundField DataField="M" HeaderText="M" SortExpression="M" />
                <asp:BoundField DataField="N" HeaderText="N" SortExpression="N" />
                <asp:BoundField DataField="O" HeaderText="O" SortExpression="O" />
                <asp:BoundField DataField="P" HeaderText="P" SortExpression="P" />
                <asp:BoundField DataField="Q" HeaderText="Q" SortExpression="Q" />
                <asp:BoundField DataField="R" HeaderText="R" SortExpression="R" />
                <asp:BoundField DataField="S" HeaderText="S" SortExpression="S" />
                <asp:BoundField DataField="T" HeaderText="T" SortExpression="T" />
                <asp:BoundField DataField="U" HeaderText="U" SortExpression="U" />
                <asp:BoundField DataField="V" HeaderText="V" SortExpression="V" />
                <asp:BoundField DataField="W" HeaderText="W" SortExpression="W" />
            </Columns>



<HeaderStyle BackColor="Green"></HeaderStyle>
        </asp:GridView>


        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT * FROM [Sheet]"></asp:SqlDataSource>


        <br /><br />

        <div style="width: 521px; height: 25px; float: left; background-color: #808080">
            <asp:Button ID="BtnExportToWord" runat="server" Text="Export To Word"
                Width="120px" onclick="BtnExportToWord_Click" />&nbsp;&nbsp;
            <asp:Button ID="BtnExportToExcel" runat="server" Text="Export To Excel"
                Width="120px" onclick="BtnExportToExcel_Click" />&nbsp;&nbsp;
            <asp:Button ID="BtnExportToCSV" runat="server" Text="Export To CSV"
                Width="120px" onclick="BtnExportToCSV_Click" />&nbsp;&nbsp;
        </div>

   <br /><br />

   <iframe width="420" height="315" src="//www.youtube.com/embed/FPDjOngX2cw" frameborder="0" allowfullscreen></iframe>
    </form>
</body>
</html>

_________________________________________________________________________________________
cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Text;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void BtnExportToWord_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.doc");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-word ";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
GridView1.DataBind();
GridView1.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void BtnExportToExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;

Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;
GridView1.DataBind();

//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");

for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];

//Change Color back to white
row.BackColor = System.Drawing.Color.White;

//Apply text style to each Row
row.Attributes.Add("class", "textmode");

//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
}
}
GridView1.RenderControl(hw);

//style to format numbers to string
string style = @" .textmode { mso-number-format:\@; } ";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void BtnExportToCSV_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition",
"attachment;filename=GridViewExport.csv");
Response.Charset = "";
Response.ContentType = "application/text";

GridView1.AllowPaging = false;
GridView1.DataBind();

StringBuilder sb = new StringBuilder();
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Columns[k].HeaderText + ',');
}
//append new line
sb.Append("\r\n");
for (int i = 0; i < GridView1.Rows.Count; i++)
{
for (int k = 0; k < GridView1.Columns.Count; k++)
{
//add separator
sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
}
//append new line
sb.Append("\r\n");
}
Response.Output.Write(sb.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
}

Import/Upload Excel Data to Asp.net Gridview in C#.net

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0