excel导入sql Server！错误：外部表不是预期的格式。
“/”应用程序中的服务器错误。
--------------------------------------------
外部表不是预期的格式。
说明: 执行当前 Web 请求期间，出现未经处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。
异常详细信息: System.Data.OleDb.OleDbException: 外部表不是预期的格式。
网上查了好多！都说复制里面的内容到新建的Excel文件中再导入！
这样用户试用多麻烦！
我导出Excel的方法是通过HTTP输出流！
- C# code
//http文件流 public void CreateExcel(DataSet ds, string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls"); string colHeaders = "", ls_item = ""; //定义表对象与行对象，同时用DataSet对其值进行初始化 DataTable dt = ds.Tables[0]; DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = dt.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { colHeaders += dt.Columns[i].Caption.ToString() + "\n"; } else { colHeaders += dt.Columns[i].Caption.ToString() + "\t"; } } resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } resp.Write(ls_item); ls_item = ""; } resp.End(); }[解决办法]
有人，不会。
[解决办法]
- C/C++ code
public DataSet ds(){ string type2 = FileUpload1.FileName; var type3 = type2.Substring(type2.LastIndexOf('.')); if(type3.Equals(".xls") || type3.Equals(".xlsx")) { string newName = Server.MapPath("..//App_Data//Uploads//") + DateTime.Now.ToString("hhmmss") + ".xls"; FileUpload1.SaveAs(newName); string connStr = string.Empty; if (type3.Equals(".xlsx")) { connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + newName + ";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'"; } else if (type3.Equals(".xls")) { connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + newName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; } OleDbConnection conn = new OleDbConnection(connStr); if (conn.State.ToString() == "Closed") { conn.Open(); } DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); var tableName = dt.Rows[0][2].ToString().Trim(); var str1 = string.Format("Select * from [{0}]", tableName); OleDbDataAdapter oda = new OleDbDataAdapter(str1, conn); DataSet ds = new DataSet(); oda.Fill(ds); conn.Close(); File.Delete( newName );return ds;}
[解决办法]
你那个方法不是很看的出问题 上面那个方式是我一直用的...看看吧 或许有帮助
[解决办法]
Public Function ImportDataToTempTable(ByVal ds As DataSet) As TaskResultType
Dim UploadFacilityID As Integer = 0
Dim UserID As Integer = 1
Dim Facility As Integer = 2
Dim Curr As Integer = 3
Dim TotalAmount As Integer = 4
Dim MaturityDate As Integer = 5
Dim IssueDate As Integer = 6
Dim Curr_Alt As Integer = 7
Dim TotalAmount_Alt As Integer = 8
Dim Comp_ID As Integer = 9
Dim Role As Integer = 10
Dim ParticipationAmt As Integer = 11
Dim IntStatus As Integer = 12
Dim ContNo As Integer = 13
Dim strInsertData As String = "Insert Into intUploadFacility(UploadFacilityID,UserID,Facility,Curr,TotalAmount,MaturityDate,IssueDate,Curr_Alt,TotalAmount_Alt,Comp_ID,Role,ParticipationAmt,IntStatus,ContNo) " & _
"values(" & _
"@UploadFacilityID,@UserID,@Facility,@Curr,@TotalAmount,@MaturityDate,@IssueDate,@Curr_Alt," & _
"@TotalAmount_Alt,@Comp_ID,@Role,@ParticipationAmt,@IntStatus,@ContNo)"
Dim intRecordID As Integer = 0
Dim tr As SqlTransaction
tr = Conn.BeginTransaction("IMPORT_DATA")
Try
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
Dim row As DataRow = ds.Tables(0).Rows(i)
Dim arrParam() As SqlParameter = _
{ _
New SqlParameter("@UploadFacilityID", SqlDbType.Int), _
New SqlParameter("@UserID", SqlDbType.VarChar), _
New SqlParameter("@Facility", SqlDbType.NVarChar), _
New SqlParameter("@Curr", SqlDbType.VarChar), _
New SqlParameter("@TotalAmount", SqlDbType.Decimal), _
New SqlParameter("@MaturityDate", SqlDbType.Date), _
New SqlParameter("@IssueDate", SqlDbType.Date), _
New SqlParameter("@Curr_Alt", SqlDbType.VarChar), _
New SqlParameter("@TotalAmount_Alt", SqlDbType.Decimal), _
New SqlParameter("@Comp_ID", SqlDbType.Int), _
New SqlParameter("@Role", SqlDbType.NVarChar), _
New SqlParameter("@ParticipationAmt", SqlDbType.Decimal), _
New SqlParameter("@IntStatus", SqlDbType.VarChar), _
New SqlParameter("@ContNo", SqlDbType.NVarChar)}
intRecordID = intRecordID + 1
arrParam(UploadFacilityID).Value = intRecordID
' todo: user id
Dim useId As String = Parameter(USER_ID).ToString()
arrParam(UserID).Value = TruncString(useId.ToString(), 50)
arrParam(Facility).Value = TruncString(row("Facility").ToString(), 200)
arrParam(Curr).Value = TruncString(row("Curr").ToString(), 3)
arrParam(TotalAmount).Value = row("Amount")
arrParam(MaturityDate).Value = row("MaturityDate")
arrParam(IssueDate).Value = row("IssueDate")
arrParam(Curr_Alt).Value = TruncString(row("CurrAlt").ToString(), 3)
arrParam(TotalAmount_Alt).Value = row("AmountAlt")
arrParam(Comp_ID).Value =row("Comp_ID")
arrParam(Role).Value = TruncString(row("Role").ToString(), 100)
arrParam(ParticipationAmt).Value = row("ParticipationAmt")
arrParam(IntStatus).Value = ""
arrParam(ContNo).Value = TruncString(row("ContNo").ToString(), 10)
'
SqlHelper.ExecuteNonQuery(tr, CommandType.Text, strInsertData, arrParam)
Next
Catch ex As Exception
tr.Rollback()
Throw ex
End Try
tr.Commit()
tr.Dispose()
Return TaskResultType.Success
End Function
[解决办法]
是不是跟版本有关系呀
[解决办法]
导出的是2003还是2010啊