.net关于读写excel的问题（转）

今天有个同事问我个问题，他在用C#“Microsoft.Jet.OLEDB.4.0”读取EXCEL文件的时候，发现了一个问题，是这样的，他读出来的EXCEL数据在DATAGRID中显示的时候，只有文本的数据能够读出来，比如某一个单元格里面的数据是纯数字的话，那么在DATAGRID里面显示的空的，什么都没有，他让我看看，我找了好半天的原因没有找到，后来实在没有办法，就换个方法读，用COM组件，引用了EXCEL组件之后，Excel.Applicatioin ea=new Excel.ApplicationClass();执行的时候就报错，“访问被拒绝”，发现了这样两个问题，今天晚上找了好半天的方法，终于解决了。

1，用Microsoft.Jet.OLEDB.4.0读取EXCEL数据的代码是这样的：
     string ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/aa.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
    OleDbConnection Conn=new OleDbConnection(ConnStr);
   Conn.Open();
   string SQL="select * from [sheet1$]";
   OleDbDataAdapter da=new OleDbDataAdapter(SQL,ConnStr);
   DataSet ds=new DataSet();
   da.Fill(ds);
   DataGrid1.DataSource=ds;
   DataGrid1.DataBind();

很简单的代码，但是问题就出在连接字符串上面，后面一定要加上Extended Properties='Excel 8.0;HDR=NO;IMEX=1'，HDR和IMEX也一定要配合使用，哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...


2，

由于ASPNET属于网络用户，所以如果网络用户要调用本地的进程或者程序，比如创建文件夹等，都需要给ASPNET加上本地用户的权限，所以报错中“ASP.NET 有一个在应用程序没有模拟时使用的基进程标识”，解决方法是在web.config文件中加上<identity impersonate="true"/>
结点，这个结点的用途见ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.2052/cpgenref/html/gngrfIdentitySection.htm

还有个问题，加入在页面中对EXCEL进行了操作，需要保存的时候，当创建一个新的文档或者保存一个修改过的文档时：

代码是：test.SaveAs (ConfigurationSettings.AppSettings["Excel"] + DocName.Text + ".xls");

出现如下错误：

无法打开宏储存。

说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。

异常详细信息: System.Runtime.InteropServices.COMException: 无法打开宏储存。
解决方法：

Configuring Office as the interactive user

To set up an Office Automation server under the interactive user account, follow these steps:

1.	Log on to the computer as the Administrator and install (or reinstall) Office using a complete install. For system robustness, it is recommended that you copy the contents of the Office CD-ROM to a local drive and install Office from this location.
2.	Start the Office application that you intend to automate. This forces the application to register itself.
3.	After the application is running, press ALT+F11 to load the Microsoft Visual Basic for Applications (VBA) editor. This forces VBA to initialize itself.
4.	Close the applications, including VBA.
5.	Click Start, click Run, and then type DCOMCNFG. Select the application that you want to automate. The application names are listed below: Microsoft Access 97 - Microsoft Access Database Microsoft Access 2000/2002 - Microsoft Access Application Microsoft Excel 97/2000/2002 - Microsoft Excel Application Microsoft Word 97 - Microsoft Word Basic Microsoft Word 2000/2002 - Microsoft Word Document Click Properties to open the property dialog box for this application.
6.	Click the Security tab. Verify that Use Default Access Permissions and Use Default Launch Permissions are selected.
7.	Click the Identity tab and then select The Interactive User.
8.	Click OK to close the property dialog box and return to the main applications list dialog box.
9.	In the DCOM Configuration dialog box, click the Default Security tab.
10.	Click Edit Defaults for access permissions. Verify that the following users are listed in the access permissions, or add the users if they are not listed: SYSTEM INTERACTIVE Everyone Administrators IUSR_<machinename>* IWAM_<machinename>* * These accounts only exist if Internet Information Server (IIS) is installed on the computer.
11.	Make sure that each user is allowed access and click OK.
12.	Click Edit Defaults for launch permissions. Verify that the following users are listed in the launch permissions, or add the users if they are not listed: SYSTEM INTERACTIVE Everyone Administrators IUSR_<machinename>* IWAM_<machinename>* * These accounts exist only if IIS is installed on the computer.
13.	Make sure that each user is allowed access, and then click OK.
14.	Click OK to close DCOMCNFG.
15.	Start REGEDIT and verify that the following keys and string values exist for the Office application that you want to automate: Microsoft Access 2000/2002: Key: HKEY_CLASSES_ROOT\AppID\MSACCESS.EXE AppID: {73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9} Microsoft Access 97: Key: HKEY_CLASSES_ROOT\AppID\MSACCESS.EXE AppID: {8CC49940-3146-11CF-97A1-00AA00424A9F} Microsoft Excel 97/2000/2002: Key: HKEY_CLASSES_ROOT\AppID\EXCEL.EXE AppID: {00020812-0000-0000-C000-000000000046} Microsoft Word 97/2000/2002: Key: HKEY_CLASSES_ROOT\AppID\WINWORD.EXE AppID: {00020906-0000-0000-C000-000000000046} If these keys do not exist, you can create them by running the following .reg file on your system: REGEDIT4 [HKEY_CLASSES_ROOT\AppID\WINWORD.EXE] "AppID"="{00020906-0000-0000-C000-000000000046}" [HKEY_CLASSES_ROOT\AppID\EXCEL.EXE] "AppID"="{00020812-0000-0000-C000-000000000046}" [HKEY_CLASSES_ROOT\AppID\MSACCESS.EXE] "AppID"="{73A4C9C1-D68D-11D0-98BF-00A0C90DC8D9}" Note The sample .reg file is for Access 2000 or Access 2002. If you are using Access 97, change the AppID key to: "AppID"="{8CC49940-3146-11CF-97A1-00AA00424A9F}"
16.	Restart the system. This is required.

参考：http://support.microsoft.com/default.aspx?scid=kb;EN-US;288366