(经典问题）服务器生成数据在客户端保存为EXCEL

[huangxiaoben]

我的服务器不允许安装EXCEL，但要实现用户点击统计报表，能够以EXCEL类型保存在客户端
该如何实现！！

[pangming051380]

/*
* 将Grid表格的内容转换为Excel文件
* 使用注意:不能有linkButton之类的列，linkButton将会导致程序寻找别的链接从而引进异常
* */
public static void gridToExcel(System.Web.UI.WebControls.DataGrid grid, string excelName)
{
HttpContext.Current.Response.Charset ="GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" + excelName + ".xls");
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");

grid.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
grid.RenderControl(hw);

HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}

[ALong_Yue]

Public Sub Export2Excel(ByVal ctl As System.Web.UI.Control, ByVal ExcelFileName As String)
Response.Charset = "GB2312"
Response.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelFileName + ".xls")
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
Response.ContentType = "application/ms-excel"

ctl.Page.EnableViewState = False
Dim tw As System.IO.StringWriter = New System.IO.StringWriter()
Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(tw)

ctl.RenderControl(hw)

Response.Write(tw.ToString())
Response.End()
End Sub

[cuike519]

http://expert.csdn.net/Expert/TopicView1.asp?id=2928057

www.foxhis.com/powermjtest/

这个控件可以实现你说的要求！你可以看看例子行不行！

[huangxiaoben]

但我的数据是有 weekTable 动态提供
private System.Web.UI.WebControls.Table weekTable ;


// TableRow row = new TableRow() ;
TableCell cell = new TableCell() ;
cell= new TableCell() ;
cell.Text = "机台名" ;
row.Cells.Add(cell ) ;
cell = new TableCell() ;
cell.Text = "机种";
row.Cells.Add(cell) ;

[shoutor]

public void Export2Excel(Object Sender, EventArgs E)
{
// 将当前查询的数据导入Excel中
SqlConnection SqlConn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

// 设置为true,则在当前的数据上追加数据,否则,重写当前数据
StreamWriter w = new StreamWriter(Server.MapPath("Data\\EmpDimissionResult.xls"), false, Encoding.Default);
SqlConn.Open();

try
{
SqlCommand SqlCmd = SqlConn.CreateCommand();
SqlCmd.CommandText = 你的SQL语句;
SqlDataReader Reader = SqlCmd.ExecuteReader();
for (int i = 0; i < Reader.FieldCount; ++i)
{
w.Write(Reader.GetName(i));
w.Write('\t');
}
w.Write("\r\n");
object[] values = new object[Reader.FieldCount];
while (Reader.Read())
{
Reader.GetValues(values);
for (int i = 0; i < values.Length; ++i)
{
w.Write( values[i].ToString());
w.Write('\t');
}
w.Write("\r\n");
}
w.Flush();
w.Close();
Reader.Close();
SqlConn.Close();
Response.Redirect ("Data\\EmpDimissionResult.xls");
}
catch
{
w.Close();
SqlConn.Close();
return;
}
}

[huangxiaoben]

如何给分———— shoutor(土人制造) (

[win_feng]

都是高人，学习

[xielongfei]

学习

[shoutor]

如果要设置颜色,

public void OutputExcel(DataView dv,string str)
{
//
// TODO: 在此处添加构造函数逻辑
//
//dv为要输出到Excel的数据，str为标题名称
GC.Collect();
Application excel;// = new Application();
int rowIndex=4;
int colIndex=1;

_Workbook xBk;
_Worksheet xSt;

excel= new ApplicationClass();

xBk = excel.Workbooks.Add(true);

xSt = (_Worksheet)xBk.ActiveSheet;

//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}

//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if(col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum,2] = "合计";
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
//
//取得整个报表的标题
//
excel.Cells[2,2] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
excel.Visible=true;

//xSt.Export(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath(".")+"\\"+this.xlfile.Text+".xls");

ds = null;
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();
string path = Server.MapPath(this.xlfile.Text+".xls");

System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset="GB2312";
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());

// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";

// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行

Response.End();
}

[shoutor]

要给分很简单的,单击"管理"就可以了

[Thanks4Help]

学习！

[vbcb]

good

[vzxq]

学习UP :）

[maowenchang]

我给一个简单执行又快的吧.(客户端)
function fun_output_data()
on error resume next
Set xlApp = CreateObject("EXCEL.APPLICATION")
If Err.number > 0 Then
msgbox "导出数据失败,请正确安装Excel并设置启用ActiveX!"
else
Set xlBook = xlApp.Workbooks.Add
Set xlSheet1 = xlBook.Worksheets(1)
'xlSheet1.cells(1,1).value ="the job table "
'xlSheet1.range("A1:D1").merge
xlSheet1.cells(1,1).value = "a1"
xlSheet1.cells(1,2).value = "a1"
xlSheet1.cells(1,3).value = "a1"
xlSheet1.cells(1,4).value = "a1"
xlSheet1.cells(1,5).value = "a1"
xlSheet1.cells(1,6).value = "a1"
xlSheet1.cells(1,7).value = "a1"
xlSheet1.cells(1,8).value = "a1"
xlSheet1.cells(1,9).value = "a1"
xlSheet1.cells(1,10).value = "a1"
xlSheet1.cells(1,11).value = "a1"
xlSheet1.cells(1,12).value = "a1"
xlSheet1.Application.Visible = True
c=2
for k=0 to cint(form1.excel_no.value)-1
r=0
for i=asc("A") to asc("L")
str2=chr(i)&c
str3="form1.input"&r&"("&k&")"
xlSheet1.Range(str2).value=eval(str3).value
r=r+1
next
c=c+1
next
end if
end function

[poliu0186]

当文件名字为常量时“文件名.doc”没有问题，但是如果是 变量 &“文件名一部分.doc”不对。请问高人如何解决。

[ILoveProgramer]

高手如云！学习!

[summerboy]

mark