DataTable 或 DataSet 导出 Execl

[thinkYs]

我是 根据 时间查询出数据 放到DataTable 或 DataSet 导出 Execl

[xpcxpy]

这个问题可以百度

[herolg]

我们公司做的导出项目都是把dataset给gridview做为数据源，然后把gridview导出这样比较简单，用response输出流的方式，如果dataset直接导出的话都是加入excel控件，比较麻烦

[kingdom_0]

百度一大堆，白白浪费了20分.

[thinkYs]

百度了 N多的 就是 用不起 作死的 报错

[thinkYs]

[Quote=引用 2 楼 herolg 的回复:]
我们公司做的导出项目都是把dataset给gridview做为数据源，然后把gridview导出这样比较简单，用response输出流的方式，如果dataset直接导出的话都是加入excel控件，比较麻烦
[/Quote]

我用了 datagrid 导出的 的 只是 当前页 的
不可以 全部导出

[一片冰心在玉壶]

有个GridView的，照着写吧：

private void BindData()
{
　string query = "SELECT * FROM customers";
　SqlConnection myConnection = new SqlConnection(ConnectionString);
　SqlDataAdapter ad = new SqlDataAdapter(query, myConnection);
　DataSet ds = new DataSet();
　ad.Fill(ds, "customers");
　GridView1.DataSource = ds;
　GridView1.DataBind();
}


//这句话必须,否则出现异常：类型“GridView”的控件“XXXX”必须放在具有 runat=server 的窗体标记内
public override void VerifyRenderingInServerForm(Control control)
{
　// Confirms that an HtmlForm control is rendered for
}

protected void Button1_Click(object sender, EventArgs e)
{
　Response.Clear();
　Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
　Response.Charset = "gb2312";
　Response.ContentType = "application/vnd.xls";
　System.IO.StringWriter stringWrite = new System.IO.StringWriter();
　System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);

　GridView1.AllowPaging = false;
　BindData();
　GridView1.RenderControl(htmlWrite);

　Response.Write(stringWrite.ToString());
　Response.End();
　GridView1.AllowPaging = true;
　BindData();
}
protected void paging(object sender,GridViewPageEventArgs e)
{
　GridView1.PageIndex = e.NewPageIndex;
　BindData();
} 

[insus]

参考：
http://www.cnblogs.com/insus/articles/1400266.html

[xpcxpy]

首先在解决方案资源管理器里添加引用，在.NET选项卡里找到
Microsoft.Office.Interop.Excel 11.0.0.0

[hl362496158]

本来想贴段代码给你看，可惜啊，这鬼公司贴代码就提交不了，你还是去我博客里面吧，有一个完整的导出！一定记得结贴给分啊！！！

[thinkYs]

[Quote=引用 6 楼 mockqi 的回复:]
有个GridView的，照着写吧：


C# code
private void BindData()
{
　string query = "SELECT * FROM customers";
　SqlConnection myConnection = new SqlConnection(ConnectionString);
　SqlDataAdapter ad = new Sql……
[/Quote]
peging() 用在哪里 拿来 做什么的 ？？？
看不太明白

[thinkYs]

[Quote=引用 9 楼 hl362496158 的回复:]
本来想贴段代码给你看，可惜啊，这鬼公司贴代码就提交不了，你还是去我博客里面吧，有一个完整的导出！一定记得结贴给分啊！！！
[/Quote]

连接 地址在哪里 没看到 啊

[pwxcomer]

/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcel(System.Data.DataTable dt, System.Data.DataTable head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
Application excel = null;
_Workbook xBk = null;
_Worksheet xSt = null;
try
{
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);

//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}

//创建一个Sheet
if (null == xSt)
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}

//题头导出
int rowCount = head.Rows.Count;
int colCount = head.Columns.Count;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
xSt.Columns.AutoFit();

//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
xSt.Columns.AutoFit();

}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true;//注意一定要加上这句

object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;

xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();

}
catch (Exception e)
{
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
}
}

[thinkYs]

string year = this.droYear.Text;
        string month = this.droMonth.Text;
        DataSet ds = ILS.Com.BLLs.KaKouBLLs.DateKaKou(year,month);
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "UTF-8";
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件,ContentType指定文件类型 可以为application/ms-excel 　　 application/ms-word 　　 application/ms-txt 　　 application/ms-html 　。  
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
      //.RenderControl(oHtmlTextWriter);
        
        Response.Output.Write(oStringWriter.ToString());
        Response.Flush();
        Response.End();

怎么导出来 啊