c#生成Excel

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using MyExcel = Microsoft.Office.Interop;
using System.Reflection;
using System.Data.SqlClient;
namespace Weblinq
{
public partial class _Excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;
MyExcel.Excel._Application myexcel;
myexcel = new MyExcel.Excel.ApplicationClass();
MyExcel.Excel._Workbook wk;
MyExcel.Excel._Worksheet st;
wk = myexcel.Workbooks.Add(true);
DataTable dt = Loaddata();
for (int i = 0; i < 5; i++)
{
st = (MyExcel.Excel._Worksheet)wk.ActiveSheet;
st.Name = "chsnm" + i.ToString();
if (i == 4)
{
int colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
myexcel.Cells[4, colIndex] = col.ColumnName;
st.get_Range(myexcel.Cells[4, colIndex], myexcel.Cells[4, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
int rowIndex = 4;
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
myexcel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
st.get_Range(myexcel.Cells[rowIndex, colIndex], myexcel.Cells[rowIndex, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
myexcel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
st.get_Range(myexcel.Cells[rowIndex, colIndex], myexcel.Cells[rowIndex, colIndex]).HorizontalAlignment = MyExcel.Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
myexcel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
int rowSum = rowIndex + 1;
int colSum = 2;
myexcel.Cells[rowSum, 2] = "合计";
st.get_Range(myexcel.Cells[rowSum, 2], myexcel.Cells[rowSum, 2]).HorizontalAlignment = MyExcel.Excel.XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
st.get_Range(myexcel.Cells[rowSum, colSum], myexcel.Cells[rowSum, colIndex]).Select();
st.get_Range(myexcel.Cells[rowSum, colSum], myexcel.Cells[rowSum, colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
//
//取得整个报表的标题
//
myexcel.Cells[2, 2] = "Orders";
//
//设置整个报表的标题格式
//
st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, 2]).Font.Bold = true;
st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, 2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Select();
st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, colIndex]).Select();
st.get_Range(myexcel.Cells[2, 2], myexcel.Cells[2, colIndex]).HorizontalAlignment = MyExcel.Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[rowSum, 2]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeLeft].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置左边线加粗
st.get_Range(myexcel.Cells[4, 2], myexcel.Cells[4, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeTop].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置上边线加粗
st.get_Range(myexcel.Cells[4, colIndex], myexcel.Cells[rowSum, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeRight].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置右边线加粗
st.get_Range(myexcel.Cells[rowSum, 2], myexcel.Cells[rowSum, colIndex]).Borders[MyExcel.Excel.XlBordersIndex.xlEdgeBottom].Weight = MyExcel.Excel.XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
myexcel.Visible = true;
}
if (i < 3)
{
myexcel.Cells[2, 2] = "chh2h" + i.ToString();
myexcel.Sheets.Add(missing, missing, 1, MyExcel.Excel.XlSheetType.xlWorksheet);
}
}
wk.SaveAs(Server.MapPath("cms.xls"), missing, missing, missing, missing, missing, MyExcel.Excel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);

myexcel.Quit();
// Response.ClearContent();
// Response.ClearHeaders();
// Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.MapPath("cms.xls"));
// Response.Buffer = true;
// Response.ContentType = "application/ms-excel";
// //将报表文件存入本地
// Response.WriteFile(Server.MapPath("cms.xls"));
//// Response.Flush();
// Response.Close();
}
private DataTable Loaddata()
{
SqlConnection conn = new SqlConnection("Data Source=XUELIANG-PC;Initial Catalog=Northwind;User ID=sa");
string sqlstr = "select * from orders";
SqlDataAdapter sa = new SqlDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
sa.Fill(ds, "admin");
return ds.Tables[0];
}
}
}