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 ();
}
}
}
}
}