Today, I solved two issues about. Net reading Excel files. It is very useful to remember!

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:

You cannot open macro 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: Macro storage cannot be enabled.
Solution:
Enter dcomcnfg
In the component service ==> computer ==> my computer DCOM find:

The following interface is displayed,

Click access permission,

After seeing the interface above, you should know how to do it. After adding the ASPNET user, the permission will be OK.