Import data from SQL server to Excel (c #)

Import data from SQL server to Excel

Author: ouyang76cn ()

Although DTS in SQL server can also import data into Excel, it is not as flexible as using a program,
The main code of this program is in the button function. Applicable to the read data section of report development :)
I deleted a lot of junk code from the original program, leaving only the code that mainly works.

// Add a namespace
Using System. Data;
Using System. Data. SqlClient;

// Define the method GetData () and return a data table
Private System. Data. DataTable GetData ()
{
SqlConnection conn = new SqlConnection (@ "Server = PXGD2; Initial Catalog = pingxiang; Uid = sa; Pwd = ;");
SqlDataAdapter adapter = new SqlDataAdapter ("select username, occupation port of catalyst_port, home_address residential address, ip_address

IP address, phone number, addtime activation date from userinfo where region st_port = 1 or region st_port = order by ip_address desc ", conn );

DataSet ds = new DataSet ();
Try
{
Adapter. Fill (ds, "Customer ");
}
Catch (Exception ex)
{
MessageBox. Show (ex. ToString ());
}
Return ds. Tables [0];
}

// Button
Private void button#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 the cell
Foreach (DataColumn col in table. Columns)
{
ColIndex ++;
Excel. Cells [1, colIndex] = col. ColumnName;
}

// Process data in the same way
Foreach (DataRow row in table. Rows)
{
RowIndex ++;
ColIndex = 0;
Foreach (DataColumn col in table. Columns)
{
ColIndex ++;
Excel. Cells [rowIndex, colIndex] = row [col. ColumnName]. ToString ();
}
}
// Invisible, that is, background processing
Excel. Visible = true;
}