Excel Workbook operation example

Using system;
Using system. Data;
Using system. drawing;
Using system. Windows. forms;
Using Microsoft. visualstudio. Tools. Applications. runtime;
Using Excel = Microsoft. Office. InterOP. Excel;
Using Office = Microsoft. Office. core;
Using system. Data. sqlclient;

Namespace excelworkbook1
{
Public partial class sheet1
{
Private void sheetaskstartup (Object sender, system. eventargs E)
{
}

Private void sheet1_shutdown (Object sender, system. eventargs E)
{
}

# Region vsto designer generated code

/// <Summary>
/// Required method for designer support-do not modify
/// The contents of this method with the code editor.
/// </Summary>
Private void internalstartup ()
{
This. button1.click + = new system. eventhandler (this. button#click );
This. Shutdown + = new system. eventhandler (this. sheetaskshutdown );
This. startup + = new system. eventhandler (this. sheetaskstartup );

}

# Endregion

Private void button#click (Object sender, eventargs E)
{
// Import ();
Export ();
}

# Import the region database to XSL

Void import ()
{
Excel. Application appxsl = new Microsoft. Office. InterOP. Excel. Application ();
If (null = appxsl)
{
MessageBox. Show ("can not open excel! ");
Return;
}
Sqlconnection sqlcon = new sqlconnection ("Server =.; uid = sa; Pwd =; database = article ;");
Try
{
Appxsl. application. workbooks. Add (true );
Sqlcon. open ();
String SQL = @ "select * From articledetails ";
Sqlcommand cmd = new sqlcommand (SQL, sqlcon );
Sqldatareader SDR = cmd. executereader ();
Int rowcount = SDR. fieldcount;
For (INT I = 0; I <rowcount; I ++)
{
Appxsl. cells [1, I + 1] = SDR. getname (I );
}
Int currentrownumber = 2;
While (SDR. Read ())
{
For (INT I = 0; I <rowcount; I ++)
{
Appxsl. cells [currentrownumber, I + 1] = SDR. getvalue (I). tostring ();
}
Currentrownumber ++;
}
Appxsl. Visible = true;
}
Catch (exception ex)
{
MessageBox. Show (ex. tostring ());
}
Finally
{
If (sqlcon. State = connectionstate. open)
{
Sqlcon. Close ();
}
Appxsl. Quit ();
}
}
# Endregion

# Region XSL import to database
Void Export ()
{
Excel. Application appxsl = new Microsoft. Office. InterOP. Excel. Application ();
Try
{
Excel. workbook workbook = appxsl. workbooks. open (@ "D: \ 2.xls", system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing, system. type. missing );
Excel. worksheet sheet = appxsl. Sheets [1] as Excel. worksheet;
System. Text. stringbuilder sbsql = new system. Text. stringbuilder ();
Int rowcount = 5;
Int colcount = 8;

String sqltemp = @ "insert into articledetails (";
For (INT I = 2; I <= colcount; I ++)
{
Sqltemp = sqltemp + (Excel. Range) sheet. cells [1, I]). Text. tostring () + ",";
}
Sqltemp = sqltemp. substring (0, sqltemp. Length-1) + ") values (";

For (INT I = 2; I <= rowcount; I ++)
{
Sbsql. append (sqltemp );
For (Int J = 2; j <colcount; j ++)
{
Sbsql. append ("'");
Sbsql. append (Excel. Range) sheet. cells [I, j]). Text. tostring ());
Sbsql. append ("',");
}
Sbsql. append ("'");
Sbsql. append (Excel. Range) sheet. cells [I, colcount]). Text. tostring () + "')");
}
Opencon ();
Sqlcommand cmd = new sqlcommand (sbsql. tostring (), sqlcon );
Cmd. executenonquery ();
}
Catch (exception ex)
{
MessageBox. Show (ex. tostring ());
}
Finally
{
MessageBox. Show ("Export OK! ");
Closecon ();
Appxsl. Quit ();
Appxsl = NULL;
}
}
# Endregion

# Region Database
Sqlconnection sqlcon = new sqlconnection ("Server =.; uid = sa; Pwd =; database = article ;");
Void opencon ()
{
If (sqlcon. State = connectionstate. Closed)
{
Sqlcon. open ();
}
}
Void closecon ()
{
If (sqlcon. State = connectionstate. open)
{
Sqlcon. Close ();
}
}
# Endregion
}
}