http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=9aeaa970-f281-4fb0-aba1-d59d7ed09772&displayLang=en

http://msdn.microsoft.com/library/bb429476

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
<assemblyIdentity version="1.0.0.0" processorArchitecture="X86"
name="Microsoft.Winweb.xxx.exe" type="win32" />
<description>Your application description here.</description>
<dependency>
<dependentAssembly>
<assemblyIdentity type="win32" name="Microsoft.Windows.Common-Controls" version="6.0.0.0"
processorArchitecture="X86" publicKeyToken="6595b64144ccf1df" language="*" />
</dependentAssembly>
</dependency>
</assembly>

最近在做一个导入Excel数据到数据库表中对应字段的功能,一开始做的时候,也没有什么困难,做的还比较顺利,但是在接下去测试过程中,却遇到了一些很棘手的问题,疯狂的试验,在网上不停的搜索,有不少人也有相同的问题,但却没有一个把问题解决清楚了,即然自己解决了,就把解决问题的过程和原因记下来,与大家一同分享.
   导入EXCEL的思路来自己客户的需求,客户需要从EXCEL中导入一些基础资料,来初始化系统中一些基础数据,而这些基础数据表中每个表都与几个不同的表相关联,即要保证数据的有效性,又要把相关性也导入,还真是把我们难倒了.如果不用程序导入,基础资料怎么导入呢,反过来一想,那就用SQL的方式导入方式吧.
  首先不管EXCEL表中的数据是什么样的,创建一个临时用的物理表,把EXCEL中的数据导入;然后用SQL语句对这个临时表和相关的表进行SQL语句开发,这样确实简单灵活了,以后客户再有什么其它变态需求时,大不了给他写一个存储过程,要多复杂就可以多复杂,一切问题解决,真爽,呵呵.
  做完后测试发现几个棘手的问题.
  问题:数据驱动问题,Excel中有一些列是数值,虽然把这个列改为文本存储方式或其它方式,用这种方式读取就是读取不到这些列的数据(string strCon = @" Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";),真是奇怪了.试验过修改EXCEL存储方式,格式,然后修改连接串,最后发现原因是数据驱动有问题,改为string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + Path + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'"; 就正确了,这里需要大家注意的一个问题'Excel 8.0;HDR=NO;IMEX=1',与Excel 8.0;HDR=NO;IMEX=1的区别,没有'号会一直出现找不到可安装的 ISAM错误!
   参数说明:

Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中，Extended Properties 关键

字设置 Excel 特定的属性。“HDR=Yes;”指示第一行中包含列名，而不是数据，“IMEX=1;”通知驱动程

序始终将“互混”数据列作为文本读取。注意

http://msdn2.microsoft.com/zh-cn/library/ms254978.aspx
默认情况下，系统认为 Excel 数据源的第一行包含可用作字段名的列标题。如果不是这种情况，则必须

将该设置关闭，否则，第一行数据将会“消失”，而被用作字段名称。这可通过向连接字符串的扩展属性

添加可选的 HDR= 设置来完成。默认情况下（无需指定）是 HDR=Yes。如果没有列标题，则需要指定

HDR=No；提供程序将字段命名为 F1、F2 等等。因为扩展属性字符串现在包含了多个值，所以必须用引号

单独包起来，如下例所示（为便于看清楚，添加了额外的空格）。

如第1个中hdr=no的话，where时就会报错

ConnStr ="Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\Inetpub\wwwroot\test.xls;          

                Extended Properties='Excel 8.0;HDR=No;IMEX=1'"

Extended Properties参数属性这样写 Excel 5.0;HDR=YES;IMEX=1
　　IMEX=1就是指混合型转换为文本
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0′
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls’,[sheet1$])

1)HDR=yes时可以把xls的第1行作为字段看待，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES

2)[]和美圆$必须要，否则M$可不认这个账 

The possible settings of IMEX are:
        0 is Export mode
        1 is Import mode
        2 is Linked mode (full update capabilities)
IMEX有3个值，
当IMEX=2 时, Excel文档中同时含有字符型和数字型时，比如第C列有3个值，2个为数值型 123，1个为字符型 ABC，当导入时，页面不报错了，但库里只显示数值型的123，而字符型的ABC则呈现为空值。当IMEX=1时，无上述情况发生，库里可正确呈现 123 和 ABC.我用的是1,在导入时，库里数字和字符类型的都导入了。

希望对做导入EXCEL功能的人有所帮助!

<A href=http://www.go4expert.com/forums/showthread.php?t=18330>VB Script. to Compare Two Excel Files with Multiple Tabs</A>

'Provide the File details here
xlfile1 = "C:\File1.xls"
xlfile2 = "C:\File2.xls"
resfile = "C:\ResFile.xls"
Res = ExcelCmp(xlfile1,xlfile2,resfile)
' Function to compare the two excel files
Public Function ExcelCmp(firstFile,secondFile,resultFile)
' Declaring varaibles 
Dim  objExcel1,objExcel2,objSpread1,objSpread2
Dim  strCount,x1,x2,y1,y2,maxR,maxC,DiffCount,PDiffCount,limit
Dim  cf1,cf2,fOffset,resOffSet,sMsg
Dim  returnVal
'As Boolean returnVal = False limit = 1
' Creates object of the two Excel files
Set objExcel1 = CreateObject("Excel.Application")
objExcel1.displayalerts = false
Set objSpread1 = objExcel1.Workbooks.Open(firstFile)
Set objSpread2 = objExcel1.Workbooks.Open(secondFile)
Set resBook = objExcel1.Workbooks.Add resBook.Sheets(1).Name = "Result"
Set resWorkSheet = resBook.WorkSheets("Result")
 'Preparing the Headers and details in the Result File
resWorkSheet.Cells(1,1) = "This is a result file which highlights the differences between the Files ..."
resWorkSheet.Cells(2,1) = "File 1 : " + firstFile resWorkSheet.Cells(3,1) = "File 2 : " + secondFile
resWorkSheet.Cells(4,1) = "'==========================================================================================="
resWorkSheet.Range(resWorkSheet.Cells(1,1), resWorkSheet.Cells(1,12)).Merge
resWorkSheet.Range(resWorkSheet.Cells(2,1), resWorkSheet.Cells(2,12)).Merge
resWorkSheet.Range(resWorkSheet.Cells(3,1), resWorkSheet.Cells(3,12)).Merge
resWorkSheet.Range(resWorkSheet.Cells(4,1), resWorkSheet.Cells(4,12)).Merge 

resWorkSheet.Cells(6,1) = "Item Name"
resWorkSheet.Cells(6,1).Font.Bold = TRUE
resWorkSheet.Cells(6,2) = "Location"
resWorkSheet.Cells(6,2).Font.Bold = TRUE
resWorkSheet.Cells(6,3) = "Data in File 1"
resWorkSheet.Cells(6,3).Font.Bold = TRUE
resWorkSheet.Cells(6,4) = "Data in File 2"
resWorkSheet.Cells(6,4).Font.Bold = TRUE
resOffSet = 7
' Get the number of worksheets(workFont) used strCount = objSpread1.Worksheets.Count
DiffCount = 0
PDiffCount = 0
'MsgBox strCount  
'Loop to identify the differences per worksheet
For i = 1 To strCount 
'Get the row and column count of the first worksheet  
Set objWorksheet1 = objSpread1.Worksheets(i) 
With objWorksheet1.UsedRange  
  x1 = .Rows.Count  
  y1 = .Columns.Count 
End With 
'MsgBox x1 & " >> " & y1 
For tOff = 1 to x1   
  If  (objWorksheet1.Cells(tOff,1) <> "")Then   
     fOffset = tOff   
     Exit For  
     End If 
   Next
 'Get the row and column count of the the secound worksheet  
Set objWorksheet2 = objSpread2.Worksheets(i) 
With objWorksheet2.UsedRange  
  x2 = .Rows.Count  
  y2 = .Columns.Count 
End With 
maxR = x1 
maxC = y1 
If maxR < x2 Then  
 maxR = x2 
End If 
If maxC < y2 Then  
maxC = y2 
End If 
'Loop to find the differences between the two files (cell by cell )
cf1 = "" 
cf2 = "" 
For c = 1 To maxC   
  For r = 1 To (maxR+fOffset)   
  On Error Resume Next  
  cf1 = LTrim(RTrim(objWorksheet1.Cells(r,c).Value))   
  cf2 = LTrim(RTrim(objWorksheet2.Cells(r,c).Value))   
  PDiffCount = DiffCount   
  If Isnumeric(cf1) And Isnumeric(cf2) Then    
    If Abs(cf1-cf2) > limit Then     
     DiffCount = DiffCount+1    
     End If  
   Else    
    If cf1 <> cf2 Then    
    DiffCount = DiffCount+1    
   End If   
   End If       
If DiffCount >= (PDiffCount+1) Then    
objWorksheet1.Cells(r,c).Interior.ColorIndex = 3    
objWorksheet2.Cells(r,c).Interior.ColorIndex = 3    
resWorkSheet.Cells(resOffSet,1) = objWorksheet1.Cells(fOffset,c).Value    resWorkSheet.Cells(resOffSet,2).Formula = "=Address("&r&","&c&",4)"     resWorkSheet.Cells(resOffSet,3) = objWorksheet1.Cells(r,c).Value     resWorkSheet.Cells(resOffSet,4) = objWorksheet2.Cells(r,c).Value     resOffSet = resOffSet + 1   
End If       
cf1 = ""  
cf2 = ""  
  Next 
 Next
Next
  If DiffCount=0 Then 
  sMsg = "No Errors Found !!!" 
  returnVal = True
  Else 
   resBook.SaveAs resultFile 
   sMsg =  "Error in Validation : " & DiffCount & " Items Mismatches!!!" & vbLF & "Results File available at : " & resultFile
End If 
resBook.Close
objSpread1.Close
objSpread2.Close 

objExcel1.displayalerts = True
objExcel1.Quit
Set objSpread1 = Nothing
Set objSpread2 = Nothing
Set objExcel1 = Nothing
Set resBook = Nothing

Excelcmp = sMsg
End Function

二进制比较，md5是二进制比较的一种，会得到一个32位的字符串，如果2个文件即使有再小的差别，得到的md5会完全不一样，所以只要2个文件md5是一样的，2个文件就完全一样

表示 MD5 哈希算法的所有实现均从中继承的抽象类。

命名空间：  System.Security.Cryptography
程序集：  mscorlib（在 mscorlib.dll 中）

语法
Visual Basic（声明）
<ComVisibleAttribute(True)> _
Public MustInherit Class MD5 _
 Inherits HashAlgorithm
 
Visual Basic (用法)
Dim instance As MD5
 
C#
[ComVisibleAttribute(true)]
public abstract class MD5 : HashAlgorithm
 
Visual C++
[ComVisibleAttribute(true)]
public ref class MD5 abstract : public HashAlgorithm
 
J#
/** @attribute ComVisibleAttribute(true) */
public abstract class MD5 extends HashAlgorithm
 
JScript.
public abstract class MD5 extends HashAlgorithm
 

备注
哈希函数将任意长度的二进制字符串映射为固定长度的小型二进制字符串。加密哈希函数有这样一个属性：在计算上不大可能找到散列为相同的值的两个不同的输入；也就是说，两组数据的哈希值仅在对应的数据也匹配时才会匹配。数据的少量更改会在哈希值中产生不可预知的大量更改。

MD5 算法的哈希值大小为 128 位。

MD5 类的 ComputeHash 方法将哈希作为 16 字节的数组返回。请注意，某些 MD5 实现会生成 32 字符的十六进制格式哈希。若要与此类实现进行互操作，请将 ComputeHash 方法的返回值格式化为十六进制值。

示例
下面的代码示例计算字符串的 MD5 哈希值，并将该哈希作为 32 字符的十六进制格式字符串返回。此代码示例中创建的哈希字符串与能创建 32 字符的十六进制格式哈希字符串的任何 MD5 哈希函数（在任何平台上）兼容。

Visual Basic  复制代码
Imports System
Imports System.Security.Cryptography
Imports System.Text

Module Example

    ' Hash an input string and return the hash as
    ' a 32 character hexadecimal string.
    Function getMd5Hash(ByVal input As String) As String
        ' Create a new instance of the MD5 object.
        Dim md5Hasher As MD5 = MD5.Create()

        ' Convert the input string to a byte array and compute the hash.
        Dim data As Byte() = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input))

        ' Create a new Stringbuilder to collect the bytes
        ' and create a string.
        Dim sBuilder As New StringBuilder()

        ' Loop through each byte of the hashed data
        ' and format each one as a hexadecimal string.
        Dim i As Integer
        For i = 0 To data.Length - 1
            sBuilder.Append(data(i).ToString("x2"))
        Next i

        ' Return the hexadecimal string.
        Return sBuilder.ToString()

    End Function

    ' Verify a hash against a string.
    Function verifyMd5Hash(ByVal input As String, ByVal hash As String) As Boolean
        ' Hash the input.
        Dim hashOfInput As String = getMd5Hash(input)

        ' Create a StringComparer an comare the hashes.
        Dim comparer As StringComparer = StringComparer.OrdinalIgnoreCase

        If 0 = comparer.Compare(hashOfInput, hash) Then
            Return True
        Else
            Return False
        End If

    End Function

    Sub Main()
        Dim source As String = "Hello World!"

        Dim hash As String = getMd5Hash(source)

        Console.WriteLine("The MD5 hash of " + source + " is: " + hash + ".")

        Console.WriteLine("Verifying the hash...")

        If verifyMd5Hash(source, hash) Then
            Console.WriteLine("The hashes are the same.")
        Else
            Console.WriteLine("The hashes are not same.")
        End If

    End Sub
End Module
' This code example produces the following output:
'
' The MD5 hash of Hello World! is: ed076287532e86365e841e92bfc50d8c.
' Verifying the hash...
' The hashes are the same.

 
C#  复制代码
using System;
using System.Security.Cryptography;
using System.Text;

class Example
{
    // Hash an input string and return the hash as
    // a 32 character hexadecimal string.
    static string getMd5Hash(string input)
    {
        // Create a new instance of the MD5CryptoServiceProvider object.
        MD5 md5Hasher = MD5.Create();

        // Convert the input string to a byte array and compute the hash.
        byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

        // Create a new Stringbuilder to collect the bytes
        // and create a string.
        StringBuilder sBuilder = new StringBuilder();

        // Loop through each byte of the hashed data
        // and format each one as a hexadecimal string.
        for (int i = 0; i < data.Length; i++)
        {
            sBuilder.Append(data[i].ToString("x2"));
        }

        // Return the hexadecimal string.
        return sBuilder.ToString();
    }

    // Verify a hash against a string.
    static bool verifyMd5Hash(string input, string hash)
    {
        // Hash the input.
        string hashOfInput = getMd5Hash(input);

        // Create a StringComparer an comare the hashes.
        StringComparer comparer = StringComparer.OrdinalIgnoreCase;

        if (0 == comparer.Compare(hashOfInput, hash))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    static void Main()
    {
        string source = "Hello World!";

        string hash = getMd5Hash(source);

        Console.WriteLine("The MD5 hash of " + source + " is: " + hash + ".");

        Console.WriteLine("Verifying the hash...");

        if (verifyMd5Hash(source, hash))
        {
            Console.WriteLine("The hashes are the same.");
        }
        else
        {
            Console.WriteLine("The hashes are not same.");
        }

    }
}
// This code example produces the following output:
//
// The MD5 hash of Hello World! is: ed076287532e86365e841e92bfc50d8c.
// Verifying the hash...
// The hashes are the same.

 

继承层次结构
System..::.Object
  System.Security.Cryptography..::.HashAlgorithm
    System.Security.Cryptography..::.MD5
      System.Security.Cryptography..::.MD5Cng
      System.Security.Cryptography..::.MD5CryptoServiceProvider

线程安全
此类型的任何公共 static（在 Visual Basic 中为 Shared） 成员都是线程安全的。但不保证所有实例成员都是线程安全的。
平台
Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98, Windows CE, Windows Mobile for Smartphone, Windows Mobile for Pocket PC

.NET Framework 和 .NET Compact Framework 并不是对每个平台的所有版本都提供支持。有关支持的版本的列表，请参见.NET Framework 系统要求。

版本信息
.NET Framework
受以下版本支持：3.5、3.0、2.0、1.1、1.0

.NET Compact Framework
受以下版本支持：3.5、2.0

另请参见
参考
MD5 成员
System.Security.Cryptography 命名空间
其他资源
加密服务