C # Import and Export (Excel) data

C # Import and Export (Excel) data

Today I got an example of importing and exporting Excel Data. First, the namespace should reference Microsoft. Office. InterOP. Excel and system. Io. The following is my code (for reference only ):

Using system;

Using system. Collections. Generic;

Using system. componentmodel;

Using system. Data;

Using system. drawing;

Using system. LINQ;

Using system. text;

Using system. Windows. forms;

Using Microsoft. Office. InterOP. Excel;

Using system. Data. sqlclient;

Using system. IO;


Namespace importexcel

{

Public partial class form1: Form

{

Public form1 ()

{

Initializecomponent ();

}


System. Data. datatable dt = new system. Data. datatable ();


// Import data from Excel to Windows Forms

Private void btn_import_click (Object sender, eventargs E)

{

Openfiledialog ofd = new openfiledialog ();

Ofd. Filter = "Excel files | *. xls ";

If (OFD. showdialog () = dialogresult. OK)

{

String filename = ofd. filename;

Microsoft. Office. InterOP. Excel. Application Excel = new Microsoft. Office. InterOP. Excel. Application ();

Microsoft. Office. InterOP. Excel. Workbook workbook;

Microsoft. Office. InterOP. Excel. worksheet;

Object omissing = system. reflection. Missing. value;

Workbook = excel. workbooks. Open (filename, omissing, omissing );

Worksheet = (worksheet) Workbook. worksheets [1];

Int rowcount = worksheet. usedrange. Rows. count;

Int colcount = worksheet. usedrange. Columns. count;

Microsoft. Office. InterOP. Excel. Range range1;




Int I;

For (I = 0; I <colcount; I ++)

{

Range1 = worksheet. Range [worksheet. cells [1, I + 1], Worksheet. cells [1, I + 1];

DT. Columns. Add (range1.value2. tostring ());

}


Int J;

For (j = 1; j <rowcount; j ++)

{

Datarow DR = DT. newrow ();

For (I = 0; I <colcount; I ++)

{

Range1 = worksheet. Range [worksheet. cells [J + 1, I + 1], Worksheet. cells [J + 1, I + 1];

Dr [I] = range1.value2;

}

DT. Rows. Add (DR );

}

Dgv_import.datasource = DT;

Excel. Quit ();

}

Else

{

MessageBox. Show ("file path error! ");

}

}


// Save the imported windows form data to the corresponding database

Private void btn_save_click (Object sender, eventargs E)

{

String connstr = "Server =.; database = sdsystem; trusted_connection = true ";

Sqlconnection conn = new sqlconnection (connstr );

Sqlcommand cmd = new sqlcommand (connstr, Conn );

Cmd. commandtext = "insert into student (studentid, studentname, sex, academyid, majorid, dormid, Memo) values (@ student ID, @ name, @ gender, @, @ dormitory, @ remarks )";

Cmd. Parameters. Add (New sqlparameter ("@ student ID", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ name", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ gender", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ ", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ ", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ ", sqldbtype. varchar ));

Cmd. Parameters. Add (New sqlparameter ("@ remarks", sqldbtype. varchar ));

If (conn. State = connectionstate. Closed)

{

Conn. open ();

}

Try

{

For (INT I = 0; I <DT. Rows. Count; I ++)

{

Cmd. Parameters ["@ student ID"]. value = parameters ();

Cmd. Parameters ["@ name"]. value = parameters ();

Cmd. Parameters ["@ gender"]. value = parameters ();

Cmd. Parameters ["@ "]. value = parameters ();

Cmd. Parameters ["@ "]. value = parameters ();

Cmd. Parameters ["@ "]. value = parameters ();

Cmd. Parameters ["@ remarks"]. value = parameters ();

Cmd. executenonquery (); // each time an insert operation is executed, the insert operation cannot be left out of the

}

Conn. Close ();

MessageBox. Show ("data is successfully saved to the database! ");

Dgv_import.datasource = NULL;

Dgv_import.readonly = true;

}

Catch (exception ex)

{

MessageBox. Show ("An error occurred while saving the data! "+ Ex. Message. tostring ());

}

}


// Export data from windows to excel

Private void btn_output_click (Object sender, eventargs E)

{

Savefiledialog DLG = new savefiledialog (); // instantiate a savefiledialog save file dialog box

DLG. Filter = "Excel files (*. xls) | *. xls ";

DLG. filterindex = 0;

DLG. restoredirectory = true;

DLG. createprompt = true;



If (DLG. showdialog () = dialogresult. OK)

{

Stream mystream;

Mystream = DLG. openfile ();

Streamwriter Sw = new streamwriter (mystream, system. Text. encoding. getencoding (-0 ));

String columntitle = "";


Try

{

// Write the column title

For (INT I = 0; I <dgv_import.columncount; I ++)

{

If (I> 0)

{

Columntitle + = "\ t ";

}

Columntitle + = dgv_import.columns [I]. headertext;

}

Sw. writeline (columntitle );


// Write column content

For (Int J = 0; j <dgv_import.rows.count; j ++)

{

String columnvaluehttp: // www.cnblogs.com/wsl-william am-love/archive/2012/04/23/= "";

For (int K = 0; k <dgv_import.columns.count; k ++)

{

If (k> 0)

{

Columnvalue + = "\ t ";

}

If (dgv_import.rows [J]. cells [K]. value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/= null)

Columnvalue + = "";

Else

Columnvalue + = dgv_import.rows [J]. cells [K]. value. tostring (). Trim ();

}

Sw. writeline (columnvalue );

}

Sw. Close ();

Mystream. Close ();

}

Catch

{

MessageBox. Show ("Export failed ");

}

Finally

{

Sw. Close ();

Mystream. Close ();

}

}

}

}

}