Click here to Skip to main content
15,846,145 members
Please Sign up or sign in to vote.
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
Updated 31-Jul-13 23:06pm
v3
Comments
Sushil Mate 1-Aug-13 3:57am    
I think Exception is much self explanatory?

 
Share this answer
 
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
 
Share this answer
 
v2
Comments
Jameel VM 1-Aug-13 4:11am    
after downloading and installing the sdk you will get the dll from the path C:\Program Files\Open XML SDK\V2.0\lib
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 */
}
}
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900