我把sql数据绑定到gridview控件中。然后导出excel 下面是导出方法.
但是我想做一个格式已经定好的。导出数据时,直接把数据放到一个定义好的模板中.比如。第一行是什么,结尾是什么。请大家介绍一种好的方法。或者有没有第三方控件能实现些功能Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
//下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc .xls .txt .htm
Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
//Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
// 定义一个输入流
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.RenderControl(oHtmlTextWriter);
//this 表示输出本页,你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
Response.Write(oStringWriter.ToString());
Response.End();
但是我想做一个格式已经定好的。导出数据时,直接把数据放到一个定义好的模板中.比如。第一行是什么,结尾是什么。请大家介绍一种好的方法。或者有没有第三方控件能实现些功能Response.Clear();
Response.Buffer= true;
Response.Charset="GB2312";
//下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开
//filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc .xls .txt .htm
Response.AppendHeader("Content-Disposition","attachment;filename=FileFlow.xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
//Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
// 定义一个输入流
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.RenderControl(oHtmlTextWriter);
//this 表示输出本页,你也可以绑定datagrid,或其他支持obj.RenderControl()属性的控件
Response.Write(oStringWriter.ToString());
Response.End();
/// 生成EXCEL文件
/// _til是表格的标题,_row是表格的行数,_col是表格的列数,_dt是数据表,_filename是保存的EXCEL表的名称
/// </summary>
public void SaveToExcel(string _til, int _row, int _col, DataTable _dt, string _filename)
{
Microsoft.Office.Interop.Owc11.SpreadsheetClass xlsheet = new Microsoft.Office.Interop.Owc11.SpreadsheetClass();
//合并单元格
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).set_MergeCells(true);
// xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[3, 2]).set_MergeCells(true);
xlsheet.ActiveSheet.Cells[1, 1] = _til;
//字体加粗
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Bold(true);
//单元格文本水平居中对齐
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).set_HorizontalAlignment(Microsoft.Office.Interop.Owc11.XlHAlign.xlHAlignCenter);
//设置字体大小
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, _col]).Font.set_Size(14);
//设置列宽
//xlsheet.get_Range(xlsheet.Cells[1, 3], xlsheet.Cells[1, 3]).set_ColumnWidth(50); //画边框线
xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[_row + 2, _col]).Borders.set_LineStyle(Microsoft.Office.Interop.Owc11.XlLineStyle.xlContinuous); //定义一个2维数组用来存储DATATABLE里的数据
object[,] dataArray = new object[_row, _col];
//添加列名
for (int k = 0; k < _dt.Columns.Count; k++)
{
xlsheet.ActiveSheet.Cells[2, k + 1] = _dt.Columns[k].Caption.ToString();
}
//把DATATABLE里的数据导到2维数组中
for (int i = 0; i < _row; i++)
{
for (int j = 0; j < _col; j++)
{
dataArray[i, j] = _dt.Rows[i][j];
}
}
//把2维数组中的数据导到EXCEL中
xlsheet.get_Range("A3", xlsheet.Cells[_row + 2, _col]).Value2 = dataArray;
xlsheet.Export(_filename, Microsoft.Office.Interop.Owc11.SheetExportActionEnum.ssExportActionNone, Microsoft.Office.Interop.Owc11.SheetExportFormat.ssExportXMLSpreadsheet);
}
/// <summary>
/// 导入EXCEL表
/// </summary>
/// <param name="strFileName"></param>
/// <returns></returns>
public DataSet CreateDataSource(string strFileName, string Sqlstr)
{
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);
DataSet myds = new DataSet();
myda.Fill(myds);
return myds;
}
你看看对你有用吗??
public void CreateEmailListExcel(DataSet Allds, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); DataTable Allmaildt = Allds.Tables[0];
string colHeaders = "", ls_item = ""; string sql = @"SELECT id,[name] FROM [tb_Category] where dept='" + ddlDept.SelectedValue + "'"; DataSet ds = com.innoways.common.clsCommon.getDataSet(sql); //定义表对象与行对象,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[0]; int i = 0; //取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
string[,] head ={
{"Unsubscribe_id","Unsubscribe ID"},
{"Campaign_Email","Email address"},
{"Unsubscribe_date","Unsubscribe Date"},
}; for (i = 0; i < head.GetLength(0); i++)
{ colHeaders += head[i, 1].ToString() + "\t"; }
for (i = 0; i < dt.Rows.Count; i++)
{
colHeaders += dt.Rows[i]["name"] + "\t";
} resp.Write(colHeaders);
DataRow[] myRow = Allmaildt.Select();
int cl = Allmaildt.Columns.Count-1;
int Unsubscribe_id = 0;
for (int n = 0; n < Allmaildt.Rows.Count; n++)
{ //向HTTP输出流中写入取得的数据信息 //逐行处理数据 Unsubscribe_id = Unsubscribe_id + 1; ls_item += Unsubscribe_id.ToString() + "\t";
for (int kcl = 0; kcl < cl; kcl++)
{
ls_item += (Allmaildt.Rows[n][kcl] == System.DBNull.Value ? "" : Allmaildt.Rows[n][kcl].ToString()) + "\t";
}
for (int d = 0; d < dt.Rows.Count; d++) //输出tb_Category的Y/N
{
if (CheckCategory(dt.Rows[d]["id"].ToString(),Allmaildt.Rows[n]["category"].ToString()))
{
ls_item += "Y" + "\t";
}
else
{
ls_item += "N" + "\t";
} } resp.Write("\n");
resp.Write(ls_item.Replace("\r\n", ""));
ls_item = "";
} resp.End();
}
参考地址www.cnblogs.com/mail-ricklee