Question

C# sql中的数据导出到excel中

表A ：id name text class
1 张三 web(1)
2 李四 web（1）
3 王五 web(2)
4 赵六 web（3）
5 钱二 web(1)
6 孙大 web（3）

将表A的数据导出到Exel中 并且按班级（class）划分到不同的工作表sheet中
救急啊~~~分可以再追加

Answer 1

......................
using System.Data.OleDb; //操作EXECL表要用的命名空间
using Microsoft.Office.Interop.Excel; // Excel 下的名称空间
using System.Reflection; //反射名称空间
using System.IO;
using System.Data;
using System.Data.SqlClient;

public partial class test_Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click1(object sender, EventArgs e)
{
try
{
Excel.Application xApp = new Excel.ApplicationClass();
xApp.Visible = true;
Excel.Workbook xBook = xApp.Workbooks.Add(Missing.Value);
for (int a = 1; a < 4; a++)
{
Excel.Worksheet sheet = (Excel.Worksheet)xBook.Worksheets[a];
sheet.Cells[1, 1] = "编号";
sheet.Cells[1, 2] = "姓名";
sheet.Cells[1, 3] = "备注";
sheet.Cells[1, 4] = "班级";

string consql = "连接字符串";
SqlConnection myConnection = new SqlConnection(consql);
myConnection.Open();
string sql = "查询语句+a";
DataSet set = new DataSet();
SqlDataAdapter sda = new SqlDataAdapter(sql, myConnection);
sda.Fill(set, "ha_2009");
for (int i = 1; i <= set.Tables[0].Rows.Count; i++)
{

for (int j = 0; j < set.Tables[0].Columns.Count; j++)
{
int k = i + 1;
int n = j + 1;
sheet.Cells[k, n] = set.Tables[0].Rows[i - 1][j].ToString();
}
}
}
xBook.SaveAs(@"G:\EXCEL\2010.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
xApp = null;

MessageBox.Show("导出数据成功!", "系统信息");

}

catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
}

}

Answer 2

CreateExcel(ds, "电子表格名.xls"); public void CreateExcel(DataSet ds, string FileName) { HttpResponse resp; resp = Page.Response; Response.Charset = ""; Response.Buffer = true; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlPathEncode(FileName)); string colHeaders = "", ls_item = ""; int i = 0; DataTable dt = ds.Tables[0]; DataRow[] myRow = dt.Select(""); for (i = 0; i < dt.Columns.Count - 1; i++) colHeaders += dt.Columns[i].Caption.ToString() + "\t"; colHeaders += dt.Columns[i].Caption.ToString() + "\n"; resp.Write(colHeaders); foreach (DataRow row in myRow) { for (i = 0; i < dt.Columns.Count - 1; i++) ls_item += row[i].ToString() + "\t"; ls_item += row[i].ToString() + "\n"; resp.Write(ls_item); ls_item = ""; } resp.End(); } }