excel文件读取,不依靠OLEDB,靠Excel.dll实现,或者其他办法,请问谁了解?????

a1981panwei 2005-10-10 05:06:47

各位好:
我想通过先上传客户端Excel文档到服务器,然后读取上传上来的excel文件,然后插入数据库,实现客户端数据的批量导入,首先我使用OLEDB读取上传的excel文档,在2003系统可以,但是在2000系统的服务器上,在填充数据集DataSet的时候,报错"未知的错误",所以改用Excel.dll读取,用worksheet的open方法,但是不知道是没有读取成功,还是不能转化,读出来的object进行tostring()以后,显示是comobject,请问哪位知道如何正确读取,或者还有其他的什么办法读取Excel文档数据???

...全文

247 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

a1981panwei 2005-10-14

打赏
举报

谢谢大家,其实用OleDB读取的操作步骤很简单,我本机运行也没有问题,但是不知道为什么在服务器上跑就不行,出"未知的错误",具体原因还不知道,在填充数据集时出错,还请各位指点,另行加分,我还有几百分呢,:)

8u9 2005-10-11

打赏
举报

我原来也做过的，用的是oledb，2000系统，上传数据没有问题，权限配置了就可以了啊。方法也是大同小异。

huangkc 2005-10-11

打赏
举报

若直接读取Excel肯定会存在版本问题
所以可用其它替代方法,如存入数据库、利用xml、或直接打开excel

心晴Sunheart 2005-10-11

打赏
举报

楼上的说的这种方法我也试过,上传到服务器就会出错,(权限都配置好了,还是出错!),所以我用了用dcom读excel的方法,我把代码给列出来供参考:

我用的是office2003,其中的Open()的参数和在office2000中不同,取掉两个就ok了,我把原来的程序修改了一下,可能有一些错误,改一下就ok了

要用到的命名空间
using System.Reflection; // For Missing.Value and BindingFlags
using System.Runtime.InteropServices; // For COMException
using Office = Microsoft.Office;(office2000为 using Microsoft.Office;)
//using Excel = Microsoft.Office.Interop.Excel;(office2000为 using Microsoft.Office.Interop.Excel;)

/// <summary>
/// 返回Excel文件数据
/// </summary>
/// <returns></returns>
private DataTable ReadExcelData()
{
HttpFileCollection files = HttpContext.Current.Request.Files;

if(excelFile.Value == "")
{
Response.Write("<script language=\"JavaScript\">alert('请选择要导入的文件！');</script>");
return null;
}
if(files[0].FileName.Substring(files[0].FileName.Length-4) != ".xls")
{
Response.Write("<script language=\"JavaScript\">alert('导入文件的格式有误！');</script>");
return null;
}

string thePath = Server.MapPath("\\");
string theName = files[0].FileName;

excelFile.PostedFile.SaveAs(thePath + theName);

Excel.Application excelApp = new Excel.Application();
if(excelApp == null)
{
return null;
}

Excel.Workbook excelWorkbook;
Excel.Worksheet excelWorkSheet;

object objMissing = System.Reflection.Missing.Value;

excelWorkbook = excelApp.Workbooks.Open(savedExcelPath, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing,objMissing, objMissing);

excelWorkSheet = (Excel.Worksheet)excelWorkbook.Worksheets.get_Item(1);
if(excelWorkSheet == null)
{
return null;
}
DataTable dt = new DataTable();
string cellContent;
int iRowCount = excelWorkSheet.UsedRange.Rows.Count;
int iColCount = excelWorkSheet.UsedRange.Columns.Count;
Excel.Range range;
for(int iRow = 1; iRow <= iRowCount; iRow ++)
{
DataRow dr = dt.NewRow();

for(int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Excel.Range)excelWorkSheet.Cells[iRow,iCol];

cellContent = (range.Value2 == null) ? "" : range.Value2.ToString();

if(iRow == 1)
{
dt.Columns.Add(cellContent);
}
else
{
dr[iCol - 1] = cellContent;
}
}

if(iRow != 1)
dt.Rows.Add(dr);
}
excelWorkbook.Close(false, objMissing, objMissing);

System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;

excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;

GC.Collect();
GC.WaitForPendingFinalizers();

if(dt==null)
{
Response.Write("<script>alert('读取文件失败')</script>");
return null;
}
return dt;
}

liangliang_0327 2005-10-11

打赏
举报

这是我以前写过的代码,运行过没问题的,你参考一下:
Dim strFileName() As String
Dim strFile As String
Dim strFilePath As String
Dim ExeclFile As HttpPostedFile
Dim cnString As String
Dim x_conn As OdbcConnection
Dim x_cmd As OdbcCommand
Dim x_dr As OdbcDataReader

'Upload Excel File to Server
ExeclFile = XlsFile.PostedFile

If ExeclFile.ContentLength = 0 Or LCase(Right(ExeclFile.FileName, 3)) <> "xls" Then
message.Text = "<script>alert('請上傳Excel文檔!')</script>"
Exit Sub
End If

If ExeclFile.ContentLength <> 0 Then
'strFileName = Split(ExeclFile.FileName, "\")
strFile = Format(Now, "yyyyMMddhhmmss") & ".xls" 'strFileName(strFileName.Length - 1)
strFilePath = Request.PhysicalApplicationPath & "Excel\" & strFile
ExeclFile.SaveAs(strFilePath)
End If

cnString = "driver={Microsoft excel Driver (*.XLS)};DBQ=" & Request.PhysicalApplicationPath & "Excel\" & strFile

x_conn = New OdbcConnection(cnString)
x_conn.Open()
x_cmd = New OdbcCommand
x_cmd.Connection = x_conn

Dim sql1 As String

'Create the Recordset
'Read Execl file
sql1 = "select * from [US$]"
x_cmd.CommandText = sql1
x_dr = x_cmd.ExecuteReader
'
Dim i, j As Integer
i = 0
Do While x_dr.Read()
'Read Excel about 基本資料
If Not IsDBNull(x_dr.Item(0)) Then
If InStr(x_dr.Item(0), "-") > 0 Then
ReDim Preserve aryForm2(i)
aryForm2(i).Item = IIf(IsDBNull(x_dr.Item(0)), "", x_dr.Item(0).ToString.Trim())
aryForm2(i).Description = IIf(IsDBNull(x_dr.Item(1)), "", x_dr.Item(1).ToString.Trim())
aryForm2(i).PartNo = IIf(IsDBNull(x_dr.Item(2)), "", x_dr.Item(2).ToString.Trim())
aryForm2(i).ModelNo = IIf(IsDBNull(x_dr.Item(3)), "", x_dr.Item(3).ToString.Trim())
aryForm2(i).Quantity = IIf(IsDBNull(x_dr.Item(4)), "", x_dr.Item(4).ToString.Trim())
aryForm2(i).Inventory = IIf(IsDBNull(x_dr.Item(5)), "", x_dr.Item(5).ToString.Trim())
aryForm2(i).Sold = IIf(IsDBNull(x_dr.Item(6)), "", x_dr.Item(6).ToString.Trim())
aryForm2(i).Category = IIf(IsDBNull(x_dr.Item(7)), "", x_dr.Item(7).ToString.Trim())
aryForm2(i).Price = IIf(IsDBNull(x_dr.Item(8)), "", x_dr.Item(8).ToString.Trim())
i = i + 1
End If

End If
Loop

x_dr.Close()
x_cmd.Cancel()
x_conn.Close()

a1981panwei 2005-10-11

打赏
举报

这个方法我试过,不知道是什么原因,单步跟踪到 Adpt.Fill(DataSet)的时候,出异常,报错说"未知的错误",真是一头雾水

smile9961 2005-10-10

打赏
举报

oledb的方法很好用的,我在win2000/win2003上都作过,没有什么问题啊.
不知道你所说的:在填充数据集DataSet的时候,报错"未知的错误"是怎么一回事?单步跟踪一下.

给你个思路:
1.连接上传的excel文件;
2.open()
3.dim da=new oledbdataadapter(sql,strconn)
'sql="select * from [xxx$];" 其中xxx为工作表的名字
'strconn为oledb方式连接excel的字符串.
4.dim ds as new dataset
5.da.fill(ds,"tab")

sql="select * from [xxx$];" '其中xxx为工作表的名字

sword222 2005-10-10