Yesterday, the customer asked to import data from the Excel file to the program. I was depressed and never touched the Excel file to import the data. So Google gave me a try, after about half an hour, I found two methods, one of which is Excel.. Net Com component to access. net ADO to access, consider it, ready to use Com to parse it. First, add a reference in the program, Microsoft Office InterOp Excel. After adding the reference, you can introduce the Excel space.
Using Excel = Microsoft. Office. Interop. Excel, but after the call is completed, the Excel process cannot be completely closed. The ghost program cannot be released immediately, so an exception is reported and the file is occupied. I checked it on the Internet and found the method for terminating the process. I used the method of garbage collection, System. GC, which can be forcibly recycled, but cannot be killed, because they may have no harm. If two users import data at the same time, one user first enters the process and kills the process, another user fails to import the file, but deleting the file is depressing. When I debug the file, it is okay to delete the file. When I delete the breakpoint, the file will be deleted, I found that the file may be released at a time. So, when I deleted the file, I blocked the current thread for 0.5 seconds. OK. No error is returned for deletion. Haha, really nice ~~~
This is part of the code
- Excel. Application m_xlsApp = null;
- Excel. Workbook m_Workbook = null;
- Excel. Worksheet m_Worksheet = null;
- Try
- {
- Object objOpt = System. Reflection. Missing. Value;
- M_xlsApp = new Excel. Application ();
- M_Workbook = require (s_FileName, objOpt, objOpt );
- M_Worksheet = (Excel. Worksheet) m_Workbook.Worksheets.get_Item (sheetIndex );
- DataRow newRow;
- For (int j = 2; j <= m_Worksheet.UsedRange.Rows.Count; j ++)
- {
- NewRow = dtTemp. NewRow ();
- For (int I = 1; I <= m_Worksheet.UsedRange.Columns.Count; I ++)
- {
- If (Excel. Range) (m_Worksheet.Cells [j, I])! = Null & (Excel. Range) (m_Worksheet.Cells [j, I]). Text. ToString ()! = "")
- {
- NewRow [I-1] = (Excel. Range) (m_Worksheet.Cells [j, I]). Value2.ToString ();
- }
- }
- DtTemp. Rows. Add (newRow );
- }
- }
- Catch (Exception exc)
- {
- Alert ("Import failed ~! ");
- }
- Finally
- {
- M_Worksheet = null;
- M_Workbook = null;
- M_xlsapp.quit ();
- Int Generation = system. gc. getgeneration (m_xlsapp );
- M_xlsapp = NULL;
- System. gc. Collect (generation );
- }
- Return dttemp;