外部表格不是预期的格式excel 2010。 [英] external table is not in expected format excel 2010.

问题描述

我必须在文档库中上传excel表，并且必须在网格视图中显示列表的所有详细信息。但我收到错误外部表不是预期的格式：



我的代码是

I have to upload a excel sheet in document library and have to show all the details of list in grid view. But I am getting error "external table is not in expected format:.

My code is

void uploadDocument()
{

    try
    {
        string publicFSdocLibrary = "PublicFSdoc";

        if (uploadDoc.HasFile)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite oSite = new SPSite("http://chdsez301298d:1000/sites/Test/"))
                //using (SPSite oSite = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb myWeb = oSite.OpenWeb())
                    {
                        SPListItem lstItem = SPContext.Current.ListItem;
                        myWeb.AllowUnsafeUpdates = true;

                        //SPList docList = myWeb.Lists[docLibrary];
                        SPFolder finalDocument = myWeb.Folders[publicFSdocLibrary];
                        // Prepare to upload
                        Boolean replaceExistingFiles = true;
                        string fileName = Path.GetFileName(uploadDoc.FileName);
                        filePath = Path.GetFullPath(uploadDoc.PostedFile.FileName);
                        // string newFilePath = getFilePath(filePath);
                        FileStream fileStream = System.IO.File.OpenRead(filePath);
                        // Upload document
                        SPFile spfile = finalDocument.Files.Add(getFileName(), fileStream, replaceExistingFiles);
                        SPListItem item = spfile.Item;
                        item["RequestNo"] = generateRequestNo();
                        item["AssessmentType"] = "Financial Solvency";
                        // Commit all changes
                        item.Update();
                        //Update document url to Assessment request list                               
                        myWeb.AllowUnsafeUpdates = false;
                    }
                }
            });
        }

    }
    catch (Exception ex)
    {
        ex.Message.ToString();

    }

}

string getFileName()
{
    string newFileName = generateRequestNo() + ".xlsx";
    return newFileName;
}


void btnUpload_Click(object sender, EventArgs e)
{
    uploadDocument();
    getExcelData();
    //savePublicFS();
    //addAssesmentWfRequest();
    //CloseForm();
    //Page.ClientScript.RegisterStartupScript(this.GetType(), "close", "window.close();", true);
    
}

void getExcelData()
{
    try
    {
        if (uploadDoc.HasFile)
        {

            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            //string sheetName = "Sourcing Questionnaire";
            string query = null;
            string connString = "";
            string fileName = Path.GetFileName(uploadDoc.FileName);
            string strFileType = System.IO.Path.GetExtension(uploadDoc.FileName).ToString().ToLower();
            string strNewPath = filePath;

            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
           // query = "SELECT * FROM [" + sheetName + "$]";
            query = "SELECT * FROM [abc$]";
            //Create the connection object
            conn = new OleDbConnection(connString);
            //Open connection
            if (conn.State == ConnectionState.Closed) conn.Open();
            //Create the command object
            cmd = new OleDbCommand(query, conn);
            da = new OleDbDataAdapter(cmd);
            ds = new DataSet();
            da.Fill(ds);
            gvdetails.DataSource = ds.Tables[0];
            gvdetails.DataBind();
            da.Dispose();
            conn.Close();
            conn.Dispose();
            //return ds;

        }            

    }
    catch (Exception)
    {

        throw;
    }
}

private String generateRequestNo()
{
    try
    {
        String requestNo = String.Empty;
        if (txtSupplierName.Text.Length <= 4)
        {
            requestNo = txtSupplierName.Text
                          + "-" + requestNumber()
                          + DateTime.Today.ToString("ddMMyyyy");
        }
        else
        {
            requestNo = txtSupplierName.Text.Substring(0, 4)
                              + "-" + requestNumber()
                              + DateTime.Today.ToString("ddMMyyyy");
        }
        return requestNo;
    }
    catch (Exception)
    {
        throw;
    }
}
private String requestNumber()
{
    try
    {
        String number = String.Empty;
        number = "FS-";
        return number;
    }
    catch (Exception)
    {
        throw;
    }
}

我在线路cmd = new OleDbCommand的getExcelData（）方法中遇到错误查询，conn）;



请帮助我，因为我上周来到这里。

and I am getting error in getExcelData() method at line cmd = new OleDbCommand(query, conn);

Please help me as I struck here since last week.

推荐答案

;
query = SELECT * FROM [abc

"; query = "SELECT * FROM [abc

;
// 创建连接对象
conn = new OleDbConnection（connString）;
// 打开连接<如果（conn.State == ConnectionState.Closed）conn.Open（）;
// 创建命令对象
cmd = new OleDbCommand（查询，conn）;
da = new OleDbDataAdapter（cmd）;
ds = < span class =code-keyword> new DataSet（）;
da.Fill（ds）;
gvdetails.DataSource = ds.Tables [ 0 ];
gvdetails.DataBind（）;
da.Dispose（）;
conn.Close（）;
conn.Dispose（）;
// return ds;

}

}
catch （例外）
{

抛;
}
}

private String generateRequestNo（ ）
{
try
{
String requestNo = 字符串 .Empty;
if （txtSupplierName.Text.Length < = 4 ）
{
requestNo = txtSupplierName.Text
+ - + requestNumber（）
+ DateTime.Today.ToString（ ddMMyyyy ）;
}
else
{
requestNo = txtSupplierName.Text.Substring（ 0 ， 4 ）
+ - + requestNumber（）
+ DateTime.Today.ToString（ ddMMyyyy ）;
}
return requestNo;
}
catch （例外）
{
throw ;
}
}
私有 字符串 requestNumber（）
{
尝试
{
字符串 number = 字符串 .Empty;
number = FS - ;
返回号码;
}
catch （例外）
{
throw ;
}
}

"; //Create the connection object conn = new OleDbConnection(connString); //Open connection if (conn.State == ConnectionState.Closed) conn.Open(); //Create the command object cmd = new OleDbCommand(query, conn); da = new OleDbDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); gvdetails.DataSource = ds.Tables[0]; gvdetails.DataBind(); da.Dispose(); conn.Close(); conn.Dispose(); //return ds; } } catch (Exception) { throw; } } private String generateRequestNo() { try { String requestNo = String.Empty; if (txtSupplierName.Text.Length <= 4) { requestNo = txtSupplierName.Text + "-" + requestNumber() + DateTime.Today.ToString("ddMMyyyy"); } else { requestNo = txtSupplierName.Text.Substring(0, 4) + "-" + requestNumber() + DateTime.Today.ToString("ddMMyyyy"); } return requestNo; } catch (Exception) { throw; } } private String requestNumber() { try { String number = String.Empty; number = "FS-"; return number; } catch (Exception) { throw; } }

我在线路cmd = new OleDbCommand的getExcelData（）方法中遇到错误查询，conn）;



请帮助我，因为我上周来到这里。

and I am getting error in getExcelData() method at line cmd = new OleDbCommand(query, conn);

Please help me as I struck here since last week.

删除HDR =是; IMEX = 2 \\ \\和更新部分代码如：

Remove HDR=Yes;IMEX=2\ and Update Part of code like:

else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;"";
}

希望这可以解决您的问题



和如果您的问题得到解决，请将其标记为已解决

Hope this solve your problem

and mark itas solved if your problem is solved

这篇关于外部表格不是预期的格式excel 2010。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！