DataSet ds = new DataSet("Excel");
string cmd="Select * From [Sheet1$]";
OleDbDataAdapter ds_adapter = new OleDbDataAdapter(cmd, conn_open);其中表名后面为什么要加个$?
string cmd="Select * From [Sheet1$]";
OleDbDataAdapter ds_adapter = new OleDbDataAdapter(cmd, conn_open);其中表名后面为什么要加个$?
是不是excel中导过来的
private System.Data.DataTable GetExcelTable(string uploadPath)
{
DataSet ds = new DataSet();
string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1 F2 F3
OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
try
{
Conn.Open();
string sql_str = "select * from [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
da.Fill(ds, "excel_data");
Conn.Close();
}
catch
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
return null;
}
finally
{
Conn.Dispose();
} if (ds == null)
{
return null;
} if (ds.Tables.Count < 1)
{
return null;
} return ds.Tables[0];
}
Microsoft.Office.Interop.Excel.DataTable tbl = ds.Tables[0];
……
……
>_<后面的不知道怎么写了。对Microsoft.Office.Interop.Excel不了解,该怎么写啊?貌似有个Excel.Add?
using Excel = Microsoft.Office.Interop.Excel;Excel.DataTable tbl = ds.Tables[0]; 这一句出错错误 1 无法将类型“System.Data.DataTable”隐式转换为“Microsoft.Office.Interop.Excel.DataTable”。存在一个显式转换(是否缺少强制转换?)
Excel导入到内存中,会产生一张虚表你可以看做是一个DataTable,这张虚表就会记录你Excel的一些信息
比如表名tablename
DataTable dt = olecon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string tableName = dt.Rows[0][2].ToString().Trim();
那么你读取数据就从这个tablename中去读取当你把Excel里面的内容读取到一个OleDbDataAdapter对象中之后,那么你就需要把DataSet去填充。。填充后,你才有一个DataSet对象去获取数据
你这个ds.Tables[0]肯定是上面读取Excel内容返回的table
这个table和Excle里面的是不同的
那么只需要用流的形式把内容读取出来就OK我一般是读取服务器控件的内容,比如DataGrid
Public Function SaveFiles()
Dim files As System.Web.HttpFileCollection = System.Web.HttpContext.Current.Request.Files
'状态信息 Dim strMsg As New System.Text.StringBuilder("上传的文件是:<hr color=#a4afd0>")
Dim iFile As System.Int32
If Directory.Exists(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd")) = False Then '建立一个专门存放Excel文件的目录 Directory.CreateDirectory(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd"))
End If Try
For iFile = 0 To files.Count - 1
'检查文件扩展名字 Dim postedFile As System.Web.HttpPostedFile = files(iFile)
Dim fileName, fileExtension As System.String
fileName = Now.ToString("HHmmss") & System.IO.Path.GetFileName(postedFile.FileName)
fileExtension = System.IO.Path.GetExtension(fileName).ToUpper
Dim FileLen As Integer
FileLen = postedFile.ContentLength If Not (fileExtension = String.Empty Or (fileExtension = ".EXE" Or fileExtension = ".DLL")) Then strMsg.Append("上传的文件类型:" + postedFile.ContentType.ToString() + "<br>")
strMsg.Append("客户端文件地址:" + postedFile.FileName + "<br>")
strMsg.Append("上传文件的文件名:" + fileName + "<br>")
strMsg.Append("上传文件的扩展名:" + fileExtension + "<br><hr color=#a4afd0>") '可根据扩展名字的不同保存到不同的文件夹
If fileExtension = ".XLS" Then
postedFile.SaveAs(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\" & fileName)
SSO.WriteLog.UploadFileLog(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\", fileName, "人力上传", FileLen)
Me.txtFile_name.Text = Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\" & fileName
in_data = PublicFunction.myPublic.ReadExcelFileToDataSet(Me.txtFile_name.Text, txtTable_s.Text.Trim).Tables(0) Session("upload_data") = in_data '存到session中
strMsg.Append("总记录数== 共" & in_data.Rows.Count & "条<br>")
If in_data.Rows.Count <= 10000 Then
Me.drgdUpload.DataSource = in_data
Me.drgdUpload.DataBind()
Me.drgdUpload.Visible = True
Else
Me.drgdUpload.Visible = False
End If
If in_data.Rows.Count > 0 Then
Me.btnanalyze.Enabled = True
Else
Me.btnanalyze.Enabled = False
End If ElseIf fileExtension = ".CSV" Then
postedFile.SaveAs(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\" & fileName)
SSO.WriteLog.UploadFileLog(Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\", fileName, "人力上传", FileLen)
Me.txtFile_name.Text = Page.Server.MapPath("..") & "\ExcelFolder\" & Now.ToString("yyMMdd") & "\" & fileName
in_data = PublicFunction.myPublic.ReadCsvFileToDataSet(Me.txtFile_name.Text, txtTable_s.Text.Trim).Tables(0) strMsg.Append("总记录数== 共" & in_data.Rows.Count & "条<br>")
If in_data.Rows.Count <= 10000 Then
Me.drgdUpload.DataSource = in_data
Me.drgdUpload.DataBind()
Me.drgdUpload.Visible = True
Else
Me.drgdUpload.Visible = False
End If
If in_data.Rows.Count > 0 Then
Me.btnanalyze.Enabled = True
Else
Me.btnanalyze.Enabled = False
End If Else
strMsg.Append("错误信息:该文件非系统所需,请核对!" + "<br><hr>")
End If
Me.lblMessage.Text = ""
End If
Next
strStatus.Text = strMsg.ToString()
Return True
Catch Ex As System.Exception
Me.drgdUpload.Visible = False
strStatus.Text = "<IMG src='../images/@warn.gif'>" & Ex.Message
Return False
End Try
End Function
string tableName = schemaTable.Rows[0][2].ToString().Trim();