将Excel目录插入数据集中 [英] Inserting Excel Contents into dataset

问题描述

朋友们，
我正在尝试上传excel文件并将excel内容添加到数据集中.尝试了以下解决方案，但它给了我错误:外部表的格式不正确."请帮帮我.

Hi friends,
I am trying to upload excel file and adding excel content into dataset. have tried following solution but it gives me error:"External table is not in the expected format.". please help me.

string Extension = "";
      string FileName = "";
      string fileExtension = "";
      string fileLocation = "";

      if (FuplAssetPath.HasFile)
      {
          FileName = FuplAssetPath.FileName;
          Directory.CreateDirectory(Server.MapPath(".") + "/UploadedCSV/");
          FuplAssetPath.PostedFile.SaveAs(Server.MapPath(".") + "/UploadedCSV/" + FileName);

          FileName = Path.GetFileName(FuplAssetPath.PostedFile.FileName);
          fileExtension = Path.GetExtension(FuplAssetPath.PostedFile.FileName);
          fileLocation = Server.MapPath("UploadedExcel/" + FileName);
          FuplAssetPath.SaveAs(fileLocation);




          DataSet ds = new DataSet();


          string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='"+fileLocation+"';Extended Properties= 'Excel 12.0;HDR=Yes;IMEX=1'";

          OleDbConnection con = new OleDbConnection(SourceConstr);

          string query = "Select * from [e_1_2_3]";

          OleDbDataAdapter data = new OleDbDataAdapter(query, con);

          data.Fill(ds);

推荐答案

查看下面的链接是否对您有帮助:
> excel-external-table-is-not-in-the-预期格式 [ ^ ]

See if below link helps you:
excel-external-table-is-not-in-the-expected-format[^]

我添加了exceldatareader.dll.我从
下载 http://exceldatareader.codeplex.com/releases/view/31934 [ 字符串FileName =";
字符串fileExtension =";
字符串fileLocation =";

如果(FuplAssetPath.HasFile)
{
FileName = FuplAssetPath.FileName;
Directory.CreateDirectory(Server.MapPath(.")+"/UploadedExcel/");
FuplAssetPath.PostedFile.SaveAs(Server.MapPath(.")+"/UploadedExcel/" + FileName);

FileName = Path.GetFileName(FuplAssetPath.PostedFile.FileName);
fileExtension = Path.GetExtension(FuplAssetPath.PostedFile.FileName);
fileLocation = Server.MapPath("UploadedExcel/" + FileName);
FuplAssetPath.SaveAs(fileLocation);

//GetExcelSheets(fileLocation，fileExtension，是");

/////////////.........
FileStream oStream;

oStream =新的FileStream(fileLocation，System.IO.FileMode.Open，System.IO.FileAccess.Read，System.IO.FileShare.Read);
IExcelDataReader iExcelDataReader = null;
FileInfo fileInfo = new FileInfo(FuplAssetPath.PostedFile.FileName);
字符串文件= fileInfo.Name;
//if(file.Split(''.'')[1] .Equals("xls"))
//{
iExcelDataReader = ExcelReaderFactory.CreateBinaryReader(oStream);

iExcelDataReader.IsFirstRowAsColumnNames = true;
DataSet dsUnUpdated = new DataSet();
dsUnUpdated = iExcelDataReader.AsDataSet();
}
}

I added exceldatareader.dll. i download it from
http://exceldatareader.codeplex.com/releases/view/31934[^]

protected void lnkDispaly_Click(object sender, EventArgs e)
{
string Extension = "";
string FileName = "";
string fileExtension = "";
string fileLocation = "";

if (FuplAssetPath.HasFile)
{
FileName = FuplAssetPath.FileName;
Directory.CreateDirectory(Server.MapPath(".") + "/UploadedExcel/");
FuplAssetPath.PostedFile.SaveAs(Server.MapPath(".") + "/UploadedExcel/" + FileName);

FileName = Path.GetFileName(FuplAssetPath.PostedFile.FileName);
fileExtension = Path.GetExtension(FuplAssetPath.PostedFile.FileName);
fileLocation = Server.MapPath("UploadedExcel/" + FileName);
FuplAssetPath.SaveAs(fileLocation);

//GetExcelSheets(fileLocation, fileExtension, "Yes");

///////////////............
FileStream oStream;

oStream = new FileStream(fileLocation, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read);
IExcelDataReader iExcelDataReader = null;
FileInfo fileInfo = new FileInfo(FuplAssetPath.PostedFile.FileName);
string file = fileInfo.Name;
//if (file.Split(''.'')[1].Equals("xls"))
//{
iExcelDataReader = ExcelReaderFactory.CreateBinaryReader(oStream);

iExcelDataReader.IsFirstRowAsColumnNames = true;
DataSet dsUnUpdated = new DataSet();
dsUnUpdated = iExcelDataReader.AsDataSet();
}
}

1.构建通用的ConnString方法:

1. Build a universal ConnString method:

public string GetConnectionString(string FileNamePath, bool HasHeader)
        {
            string ConnectionString = "";
            string Extension = Path.GetExtension(FileNamePath).ToLower();

            string BinaryExcelProvider = "Microsoft.Jet.OLEDB.4.0";
            string XmlExcelProvider = "Microsoft.ACE.OLEDB.12.0";
            string BinaryExcelExtProperties = "Excel 8.0";
            string XmlExcelExtProperties = "Excel 12.0";
            string XmlMacroExcelExtProperties = "EXCEL 12.0 Macro";

            string Provider = "";
            string ExtendedProperties = "";

            switch (Extension)
            {
                case ".xls":
                    Provider = BinaryExcelProvider;
                    ExtendedProperties = BinaryExcelExtProperties;
                    break;

                case ".xlsx":
                    Provider = XmlExcelProvider;
                    ExtendedProperties = XmlExcelExtProperties;
                    break;

                case ".xlsm":
                    Provider = XmlExcelProvider;
                    ExtendedProperties = XmlMacroExcelExtProperties;
                    break;
            }

            string Header = ";HDR=NO";
            if (HasHeader)
                Header = ";HDR=YES";
            string ConnectionStringFormat = "Provider={0};Data Source={1};Extended Properties=\"{2}{3}\";";

            ConnectionString = string.Format(ConnectionStringFormat, Provider, FileNamePath, ExtendedProperties, Header);
            return ConnectionString;
        }

2.创建数据集

2. Create DataSets

  private DataSet GetDataSet(string excelFile, string WorkSheet, out bool Success)
        {
            Success = false;
            System.Data.DataSet excelDataSet = new DataSet();
            string connectionString = this.GetConnectionString(excelFile, true);


            using (OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(connectionString))
            {
                try
                {
                    objConn.Open();
                    OleDbDataAdapter cmd = new OleDbDataAdapter("select * from " + WorkSheet, objConn);
                    cmd.Fill(excelDataSet, WorkSheet);
                    cmd.Dispose();
                }
                catch { }
                finally
                {
                    if (objConn != null)
                    {
                        objConn.Close();
                        objConn.Dispose();
                    }

                }
            }
return excelDataSet;
}

这篇关于将Excel目录插入数据集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！