通过SQL语句，把查询之后返回的结果打印成EXCEL表（二）

要先引入相应的EXCEL类。然后打印









//Print Into Excel SpreadSheet

Excel.Application excel = new Excel.Application();

int rowindex = 1;

int colindex = 0;

excel.Application.Workbooks.Add(true);

System.Data.DataTable table = GetPrintData();

foreach (DataColumn col in table.Columns)

{

colindex++;

excel.Cells[1, colindex] = col.ColumnName;

}

foreach (DataRow row in table.Rows)

{

rowindex++;

colindex = 0;

foreach (DataColumn col in table.Columns)

{

colindex++;

excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();

}

}

Excel.Worksheet ws = (Excel.Worksheet)excel.ActiveSheet;

excel.Visible = true;











protected DataTable GetPrintData()

{

string selval = "";

for (int i = 0; i < this.chlColumnsList.Items.Count; i++)

{

if (chlColumnsList.Items[i].Selected)

{

selval += chlColumnsList.Items[i].Value + ",";

}

}

if (selval.Length > 1)

{

selval = selval.Substring(0, selval.Length - 1);

string SqlStr = "select " + selval.ToString() + " from tbl_Company where 1=1 " + ReturnTotalCondition().ToString()+" order by company";

SqlDataAdapter da = new SqlDataAdapter(SqlStr, cn);

DataSet ds = new DataSet();

try

{

da.Fill(ds, "Company");

}

catch (Exception ex)

{

Response.Write(ex.Message.ToString());

}

return ds.Tables[0];

}

else

{

string SqlStr = "select * from tbl_Company where 1=1 " + ReturnTotalCondition().ToString()+" order by company";

SqlDataAdapter da = new SqlDataAdapter(SqlStr, cn);

DataSet ds = new DataSet();

try

{

da.Fill(ds, "Company");

}

catch (Exception ex)

{

Response.Write(ex.Message.ToString());

}

return ds.Tables[0];

}

}







//public static void AutoFitColumn(Excel.Worksheet ws, int col)

//{

// //((Excel.Range)ws.Cells[1, col]).EntireColumn.ColumnWidth = width;

// ((Excel.Range)ws.Cells[1, col]).EntireColumn.AutoFit();

//}