. Net questions about reading and writing Excel

today, a colleague asked me a question. He is using C # "Microsoft. jet. oledb.4.0 "found a problem when reading the Excel file. This is the case. When the Excel Data he read is displayed in the DataGrid, only the text data can be read, for example, if the data in a cell is a pure number, nothing is displayed in the DataGrid. He asked me to see why I had not found it for a long time, later, there was no way to read it. I used the COM component and referenced the Excel component, Excel. applicatioin Ea = new excel. applicationclass (); an error is reported during execution. "access is denied." The two problems were discovered. I found a solution for a long time this evening and finally solved it.

1. Use Microsoft. Jet. oledb.4.0 to read Excel DataCodeYes:
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 ();

Simple code, but the problem lies in the connection string. You must add extended properties = 'excel 8.0; HDR = no; IMEX = 1 ', HDR and IMEX must also be used together. Haha, honestly, I don't know why. This is the best result of my hard debugging. IMEX = 1 should regard all columns as text, and I forgot a bit. as for HDR, It was just said whether a line of title header would appear, but the result would lead to loss of some field values, so I still don't understand why, it is probably a driver issue...

2,

Because ASPnet is a network user, if the network user wants to call a local process or Program For example, to create a folder, you must add local user permissions to ASPnet. Therefore, the error "ASP. net has a base process ID that is used when the application is not simulated ", the solution is in the web. add <identity impersonate = "true"/> to the config file.
Node. For the purpose of this node, see ms-help: // Ms. vscc.2003/ms. msdnqtr.2003feb. 2052/cpgenref/html/gngrfidentitysection.htm.

Another problem is that the Excel file is added to the page. When you need to save the file, when creating a new file or saving a modified file:

Code: Test. saveas (configurationsettings. etettings ["Excel"] + docname. Text + ". xls ");

The following error occurs:

Cannot openMacro storage.

Note: An unhandled exception occurs during the execution of the current Web request. Check the stack trace information for details about the error and the source of the error in the code.

Exception details:System. runtime. interopservices. comexception: Cannot openMacro storage.
Solution:
Login ing 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.	ClickStart, ClickRun, And then typeDcomcnfg. 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 ClickPropertiesTo open the Property dialog box for this application.
6.	ClickSecurityTab. Verify thatUse default access permissionsAndUse default launch permissionsAre selected.
7.	ClickIdentityTab and then selectThe interactive user.
8.	ClickOKTo close the Property dialog box and return to the main applications List dialog box.
9.	InDCOM ConfigurationDialog box, clickDefault SecurityTab.
10.	ClickEdit defaultsFor 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 clickOK.
12.	ClickEdit defaultsFor 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 clickOK.
14.	ClickOKTo 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-00aa00425a9f} 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 }" NoteThe sample. reg file is for Access 2000 or Access 2002. If you are using Access 97, change the appid key: "Appid" = "{8cc49940-3146-11cf-97a1-00aa00425a9f }"
16.	Restart the system. This is required.

Reference: http://support.microsoft.com/default.aspx? SCID = KB; en-US; 288366