Excel import gadgets

Source: Internet
Author: User

I made an Excel import tool and put it up for drying. I encountered many difficulties and exceptions in the middle, but they were all solved by me.

 

Basic interface:

 

Layout your interface and add the openfiledailog control.

 

1) set relevant attributes in form load time to enable ad hoc distributed queries. If not enabled, an error is displayed.

Private void formateload (Object sender, eventargs e) <br/>{< br/> panel2.visible = false; <br/> This. size = new size (710,200); <br/> string cmd = "Exec sp_configure 'show advanced options', 1 reconfigure exec sp_configure 'ad hoc distributed queries ', 1 reconfigure "; <br/> executenonquery (CMD); <br/>}

 

2) Disable ad hoc distributed queries in the form closing event, so you have to start and finish yourself.

// Disable ad hoc distributed queries <br/> private void form=formclosing (Object sender, formclosingeventargs E) <br/>{</P> <p> string cmd = "Exec sp_configure 'ad hoc distributed queries ', 0 reconfigure exec sp_configure 'show advanced options', 0 reconfigure "; <br/> executenonquery (CMD); <br/>}

 

3) Browse File Code, open the file dialog box in the Click Event of the Browse button, and select the corresponding Excel File// Browse and select an Excel file <br/> private void button#click (Object sender, eventargs e) <br/>{< br/> openfiledialog1.filter = "xls Format (*. XLS) | *. xls "; <br/> openfiledialog1.title =" Open Excel file "; <br/> If (openfiledialog1.showdialog () = dialogresult. OK) </P> <p> textbox1.text = openfiledialog1.filename; </P> <p>}

4) code for importing a new table

// Import the Excel file to the database as a new table <br/> private void button2_click (Object sender, eventargs E) <br/>{</P> <p> string xls_path = textbox1.text; <br/> string table_name = textbox2. text; </P> <p> string cmd = "select * into" + table_name + "from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "+ xls_path +"; extended properties = Excel 5.0 ')... [sheet1 $]; "; <br/> If (executenonquery (CMD )! = NULL) <br/> MessageBox. Show ("imported successfully"); <br/>}

5) code imported to an existing table

// Import the Excel file to the existing table <br/> private void button4_click (Object sender, eventargs e) <br/>{< br/> string xls_path = textbox1.text; <br/> string table_name = textbox3.text; </P> <p> string cmd = "insert into" + table_name + "select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "+ xls_path +"; extended properties = Excel 5.0 ')... [sheet1 $]; "; <br/> If (executenonquery (CMD )! = NULL) <br/> MessageBox. Show ("imported successfully"); <br/>}< br/>

6) The preview function is to add an axwebbrowser control to the page. This is not available in the toolbar. You must add a web browser or other components to the COM component and then reference the Microsoft Office Excel component. Otherwise, you cannot preview the component.

// Specify the Excel file to open <br/> private void axwebbrowser1_navigatecomplete2 (Object sender, axshdocvw. dwebbrowserevents2_navigatecomplete2event e) <br/>{< br/> Object o = E. pdisp; <br/> Object odocument = O. getType (). invokemember ("document", bindingflags. getproperty, null, O, null); <br/> Object oapplication = O. getType (). invokemember ("application", bindingflags. getproperty, null, odocument, null); <br/> // Because the Excel file is opened, the oapplication here is actually excel. application <br/> Microsoft. office. interOP. excel. application eapp = (Microsoft. office. interOP. excel. application) oapplication; // you can operate Excel as described above. <Br/>}< br/> // open the Excel file <br/> private void button3_click (Object sender, eventargs E) <br/>{< br/> panel2.visible = true; <br/> This. size = new size (710,750); <br/> string strfilename = textbox1.text; <br/> Object refmissing = system. reflection. missing. value; <br/> axwebbrowser1.navigate (strfilename, ref refmissing); </P> <p>}

 

7) Finally, for convenience, I encapsulated the code for reading the database into the executenonquery function for all execution statements to call.

Private object executenonquery (string command) <br/>{< br/> string constr = @ "Data Source = wangxin/sqlexpress; initial catalog = test; Integrated Security = true "; <br/> sqlconnection conn = new sqlconnection (constr); <br/> string cmd = command; </P> <p> sqlcommand COM = new sqlcommand (CMD, Conn ); <br/> Conn. open (); <br/> object I = com. executenonquery (); </P> <p> Conn. close (); <br/> return I; <br/>}

 

So far, a small import tool with the preview function is generated, and the interface is a bit simple. It looks simple, but there are hundreds of errors. We hope you can ensure that

 

1) closed when importing an Excel file

2) The Field Names in the Excel file match those in the database.

3) Check the database and access version to ensure the configuration is complete.

4) Enable OpenDataSource

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.