使用C＃.net将Excel数据导入/上传到Asp.net Gridview [英] Import/Upload Excel Data to Asp.net Gridview in C#.net

问题描述

你好朋友!!

我想阅读一个excel文件并显示在网格视图中但是我在运行代码时遇到错误，

并且错误是： Microsoft Office Access数据库引擎无法打开或写入文件''。它已由其他用户专门打开，或者您需要获得查看和写入其数据的权限。

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.

    //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..

推荐答案

;
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（）;
}

"; 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..

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



查看此链接..

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

check this link..

我建​​议你使用OpenXml。你可以从这里下载sdk http://www.microsoft.com/en-in/download/details.aspx?id=5124 [ ^ ]



下载后创建一个类ExcelFile，如下面

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

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

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

希望这有帮助

Hope this helps

这篇关于使用C＃.net将Excel数据导入/上传到Asp.net Gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！