Pour the data in SQL Server into Excel
Author: Ouyang76cn ()
Although the DTS in SQL Server can pour the data into Excel, but it is better to use the program, the primary code is mainly within the button function. Can be adapted to the reading data part of the report development :) I delete a lot of garbage code for the original program, leaving only the primary role of the code
/ / Add name space Using system.data; use system.data.sqlclient;
/ / Define method getData (), return a data table private system.data.dataable getData () {sqlConnection conn = new sqlconnection (@ "server = pxgd2; initial catalog = pingxiang; uid = sa; pwd =;"); SqlDataAdapter Adapter = new SqldataAdapter ("SELECT Username User Name, Catalyst_port occupies port, home_address residential address, IP_ADDRESS
IP address, Phone, ADDTIME Open Date from UserInfo WHERE CATALYST_PORT = 1 or Catalyst_port = '' Order By IP_ADDRESS DESC ", CONN);
DataSet DS = New Dataset (); try {adapter.Fill (DS, "Customer");} catch (exception ex) {messagebox.show (ex. scroll ());} return ds.tables [0];}
// Button Private Void Button1_Click (Object Sender, System.EventArgs E) {Excel.Application Excel = New Excel.Application (); int RowIndex = 1; int coLINDEX = 0;
Excel.Application.Workbooks.add (true); DataTable Table = getData (); // Assign the column name of the obtained table to cell Foreach (Datacolumn col in table.columns) {Colindex ; Excel.cells [1 , Colindex] = col.columnname;}
// The same method for processing data foreach (DataRow row in table.Rows) {rowIndex ; colIndex = 0; foreach (DataColumn col in table.Columns) {colIndex ; excel.Cells [rowIndex, colIndex] = row [col.ColumnName]. TOSTRING ();}} // is invisible, that is, the background processing Excel.visible = true;}
