C#中导出Excel问题

你导Excel的时候是不是在客户端运行的还是在服务器运行的啊？

to： ruanxiaoyong(汇亦得) 采用你的方法无法精确控制表格尺寸，我想通过调用Excel 组件创建好表格，然后再将其转换成Xml或Html，然后再按照你的Reponse.Write(string strHtml)。可行吗？

页面如下：
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
<LINK href="css/style_pub.css" type="text/css" rel="stylesheet">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<input class="notPrint" onclick="AutomateExcel();" type="hidden" value="导出到excel" name="out_excel"><FONT face="宋体">
<TABLE class="Table" id="Table1" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 8px"
cellSpacing="1" cellPadding="6" width="96%" align="center" border="0">
<TR>
<TD bgColor="#dce5fe">
<TABLE class="border0" id="Table2" cellSpacing="0" cellPadding="3" width="98%" align="center"
border="0">
<TR align="left">
<TD> 时间： 
<asp:textbox id="TextBox1" runat="server"></asp:textbox> （格式：YYYY-MM-DD） 到 
<asp:textbox id="TextBox2" runat="server"></asp:textbox><asp:button id="btOrder_search" runat="server" Width="48px" Text="查询"></asp:button><input id="T1" style="VISIBILITY: hidden" type="text" size="20" name="T1" runat="server"><FONT face="宋体">
<input id="T2" style="VISIBILITY: hidden" type="text" size="20" name="T2" runat="server"></FONT> 
</TD>
</TR>
</TABLE>
</TD>
</TR>
<tr>
<td>
<table class="border0" id="data" borderColor="gray" cellSpacing="0" cellPadding="4" width="100%"
align="center" border="1">
<tr>
<td align="center" bgColor="#dce5fe">订单号</td>
<td align="center" width="120" bgColor="#dce5fe">订单日期</td>
<td align="center" width="80" bgColor="#dce5fe">客户姓名</td>
<td align="center" width="100" bgColor="#dce5fe">总商品售价($)</td>
<td align="center" width="100" bgColor="#dce5fe">总运费($)</td>
<td align="center" width="100" bgColor="#dce5fe">总代购费($)</td>
<td align="center" bgColor="#dce5fe">总金额($)</td>
<td align="center" bgColor="#dce5fe" colSpan="3">商品明细</td>
<td align="center" bgColor="#dce5fe">是否付款</td>
<td align="center" bgColor="#dce5fe">付款方式</td>
</tr>
<%=content%>
</table>
</td>
</tr>
<tr>
<td>
<%=pagenumber%>
<input class="notPrint" onclick="javascript:AutomateExcel() " type="button" value="导出到excel"
name="out_word1"> 
</td>
</tr>
</TABLE>
</FONT>
</form>
<SCRIPT language="JavaScript">

/*function isDate(TextBox1)
{
var datetime;
var year,month,day;
var gone,gtwo;
if(Trim(TextBox1.Text)!="")
{
datetime=Trim(TextBox1.Text);
if(datetime.length==10)
{
year=datetime.substring(0,4);
   if(isNaN(year)==true)
{
    alert("请输入日期!格式为(yyyy-mm-dd) \n例(2001-01-01)！");
    TextBox1.focus();
   return false;
   }
   gone=datetime.substring(4,5);
month=datetime.substring(5,7);
   if(isNaN(month)==true){
    alert("请输入日期!格式为(yyyy-mm-dd) \n例(2001-01-01)！");
    TextBox1.focus();
   return false;
   }
gtwo=datetime.substring(7,8);
   day=datetime.substring(8,10);
   if(isNaN(day)==true){
   alert("请输入日期!格式为(yyyy-mm-dd) \n例(2001-01-01)！");
    TextBox1.focus();
    return false;
}
if((gone=="-")&&(gtwo=="-"))
{
    if(month<1||month>12) {
    alert("月份必须在01和12之间!");
    TextBox1.focus();
    return false;
}
if(day<1||day>31)
{
alert("日期必须在01和31之间!");
TextBox1.focus();
return false;
}
else
{
if(month==2)
{
if(isLeapYear(year)&&day>29)
{
alert("二月份日期必须在01到29之间!");
TextBox1.focus();
return false;
}
if(!isLeapYear(year)&&day>28){
alert("二月份日期必须在01到28之间!");
TextBox1.focus();
return false;
}
}
if((month==4||month==6||month==9||month==11)&&(day>30)){
alert("在四，六，九，十一月份 \n日期必须在01到30之间!");
TextBox1.focus();
return false;
}
}
}else{
alert("请输入日期!格式为(yyyy-mm-dd) \n例(2001-01-01)");
TextBox1.focus();
return false;
}
}else{
alert("请输入日期!格式为(yyyy-mm-dd) \n例(2001-01-01)");
TextBox1.focus();
return false;
}
}else{
return true;
}
return true;
}*/
</SCRIPT>
</body>
</HTML>

namespace WebApplication1
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btOrder_search;
protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.TextBox TextBox2;
protected string content;
protected System.Web.UI.HtmlControls.HtmlInputText T1;
protected System.Web.UI.HtmlControls.HtmlInputText T2;
protected string pagenumber;
protected string OrderCount="";

private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
if (Request.QueryString.Count!=0)
{
string date_1=Request.QueryString["date_1"].ToString();
string date_2=Request.QueryString["date_2"].ToString();
string pn=Request.QueryString["pc"].ToString();
TextBox1.Text=date_1;
TextBox2.Text=date_2;
GetContent(Int32.Parse(pn));
}
}
}
Web窗体设计器生成的代码接下面的代码

private void GetContent(int c)
{
string Strsearchsql="";
if(TextBox1.Text=="" && TextBox2.Text!="")
{
Strsearchsql="select order_id from tbl_order where order_time between '"+TextBox2.Text.Trim()+"' and '"+TextBox2.Text.Trim()+"'";
}
if (TextBox1.Text!="" && TextBox2.Text=="")
{
Strsearchsql="select order_id from tbl_order where order_time between '"+TextBox1.Text.Trim()+"' and '"+TextBox1.Text.Trim()+"'";
}
if (TextBox1.Text!="" && TextBox2.Text!="")
{
Strsearchsql="select order_id from tbl_order where order_time between '"+TextBox1.Text.Trim()+"' and '"+TextBox2.Text.Trim()+"'";
}
if (TextBox2.Text=="" && TextBox1.Text=="")
{
Strsearchsql="select order_id from tbl_order";
}

SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["admin"]);
SqlCommand myCommand = new SqlCommand("spAll_ReturnRows",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

SqlParameter parameter0 = new SqlParameter("@SQL",SqlDbType.NVarChar,4000);
parameter0.Value = Strsearchsql;
myCommand.Parameters.Add(parameter0); SqlParameter parameter1 = new SqlParameter("@Page",SqlDbType.Int);
parameter1.Value = c;
myCommand.Parameters.Add(parameter1); SqlParameter parameter2 = new SqlParameter("@RecsPerPage",SqlDbType.Int);
parameter2.Value = 15;
myCommand.Parameters.Add(parameter2); SqlParameter parameter3 =new SqlParameter("@ID",SqlDbType.VarChar,255);
parameter3.Value = "order_id";
myCommand.Parameters.Add(parameter3); SqlParameter parameter4 = new SqlParameter("@Sort",SqlDbType.VarChar,255);
parameter4.Value = "order_id";
myCommand.Parameters.Add(parameter4);

myConnection.Open();
SqlDataReader dr=myCommand.ExecuteReader();
MakeTable(dr);
myConnection.Close();
int k=PageCut(Strsearchsql);
if((k/15+1)==c)
{
T2.Value =(k-(k/15*15)).ToString();
}
else
{
T2.Value ="15";
}
T1.Value=OrderCount;
}接下面的代码

请问楼主，你是怎样杀掉EXCEL.EXE进程的，我综合了几个帖子写了以下的代码，但还是不能杀掉，请楼主指点。private void Button1_Click(object sender, System.EventArgs e)
{
string filename1=Server.MapPath(".")+"\\prtfile\\test1.xls"; Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet; //Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = false; //Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(true));
oSheet = (Excel._Worksheet)oWB.ActiveSheet; //Add table headers going cell by cell.
oSheet.Cells[5, 1] = "First Name";
oSheet.Cells[5, 2] = "Last Name";
oSheet.Cells[5, 3] = "Full Name";
oSheet.Cells[5, 4] = "Salary"; oWB.SaveAs(filename1,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,Type.Missing, Type.Missing, Type.Missing);

oWB.Close(false,Type.Missing,Type.Missing);
oXL.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL); oSheet=null;
oWB=null;
oXL=null; GC.Collect();
}

接上面代码
private int PageCut(string str)
{
str=str.Replace("order_id","COUNT(*) AS Expr");
SqlConnection myConnection=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["admin"]);
SqlCommand myCommand=new SqlCommand(str,myConnection);
myConnection.Open();
SqlDataReader dr=myCommand.ExecuteReader();
int temp=new int();
if (dr.Read())
{
temp=Int32.Parse(dr["Expr"].ToString());
}
myConnection.Close();
int k = temp;
temp=temp/15+1;
string strPage="";
int j=0;
for (int i=0;i<temp;i++)
{
j=i+1;
strPage=strPage+"<a href=ExcelView.aspx?date_1="+TextBox1.Text+"&Date_2="+TextBox2.Text+"&pc="+j.ToString()+">"+j.ToString()+" "+"</a>";
}
myConnection.Close();
pagenumber=strPage;
return k;
}
private void MakeTable(SqlDataReader dr)
{
content="";
while(dr.Read())
{
int orderID=Int32.Parse(dr["order_id"].ToString());
content=content+GenOrder(orderID);
}
}
private string GenOrder(int Getid)
{
string strSQL=@"SELECT a.order_id,a.order_time,a.order_name,(a.order_amount-a.order_transportcost-a.Order_Commission) as cost
,a.order_transportcost,a.Order_Commission,a.order_amount,a.Order_Re,
case when isnull(((select sum(payment_amount) from tbl_payment where order_id=a.order_id) - (select exchangerate_value from tbl_exchangeRate where active_flag=1)*(select order_Amount from tbl_order where order_id=a.order_id)),0)>0 then '是' else '否' end as payment_amount,
isnull((select top 1 (select property_text from tbl_lookup where property_name='payment_type' and property_value=tbl_payment.payment_type) as paymentName from tbl_payment where tbl_payment.order_id=a.order_id),'邮政') as product_name
from tbl_order a
where a.order_id =' "+Getid+"'";
SqlConnection myConnection =  new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["admin"]);
SqlCommand myCommand = new SqlCommand(strSQL,myConnection);
myConnection.Open();
SqlDataReader dr=myCommand.ExecuteReader();
int count=Getcount(Getid);
int id=0;
string time="";
string name="";
string cost="";
string transportcost="";
string Commission="";
string amount="";
string re="";
string payment_amount="";
string product_name="";
string [,]thing = Goods(Getid);
if (dr.Read())
{
id=Int32.Parse(dr["order_id"].ToString());
time=DateTime.Parse(dr["order_time"].ToString()).ToString("yyyy-MM-dd");
name=dr["order_name"].ToString();
cost=dr["cost"].ToString();
transportcost=dr["order_transportcost"].ToString();
Commission=dr["Order_Commission"].ToString();
amount=dr["order_amount"].ToString();
re=dr["Order_Re"].ToString();
payment_amount=dr["payment_amount"].ToString();
product_name=dr["product_name"].ToString();
}
myConnection.Close();
string str="";
OrderCount=OrderCount+count.ToString()+"|";
if(count==1)
{
//if (thing[0,1].Length<20)
///{
str=str+"<tr><td> "+id+"</td><td > "+time+"</td><td > "+name+"</td><td > "+cost+"</td><td > "+transportcost+"</td><td > "+Commission+"</td><td > "+amount+"</td><td > <a target=_blank href = "+thing[0,1]+">"+thing[0,0]+"</a></td><td > "+thing[0,2]+"</td><td >"+thing[0,3]+"</td><td > "+payment_amount+"</td><td >"+product_name+"</td></tr>";
//}
//else
//{
// str=str+"<tr><td> "+id+"</td><td > "+time+"</td><td > "+name+"</td><td > "+cost+"</td><td > "+transportcost+"</td><td > "+Commission+"</td><td > "+amount+"</td><td > "+thing[0,0]+"</td><td > "+thing[0,1].Substring(0,20)+"..."+"</td><td > "+thing[0,2]+"</td><td >"+thing[0,3]+"</td><td > "+payment_amount+"</td><td >"+product_name+"</td></tr>";
//}
}
else
{
//if (thing[0,1].Length<20)
//{
str = str+"<tr><td rowspan=\""+count+"\"> "+id+"</td><td rowspan=\""+count+"\"> "+time+"</td><td rowspan=\""+count+"\"> "+name+"</td><td rowspan=\""+count+"\"> "+cost+"</td><td rowspan=\""+count+"\"> "+transportcost+"</td><td rowspan=\""+count+"\"> "+Commission+"</td><td rowspan=\""+count+"\"> "+amount+"</td><td> <a target=_blank href="+thing[0,1]+">"+thing[0,0]+"</a></td><td> "+thing[0,2]+"</td><td>"+thing[0,3]+"</td><td rowspan=\""+count+"\"> "+payment_amount+"</td><td rowspan=\""+count+"\">"+product_name+"</td></tr>";

// }
// else
// {
/// str = str+"<tr><td rowspan=\""+count+"\"> "+id+"</td><td rowspan=\""+count+"\"> "+time+"</td><td rowspan=\""+count+"\"> "+name+"</td><td rowspan=\""+count+"\"> "+cost+"</td><td rowspan=\""+count+"\"> "+transportcost+"</td><td rowspan=\""+count+"\"> "+Commission+"</td><td rowspan=\""+count+"\"> "+amount+"</td><td> <a href="+thing[0,1]+">"+thing[0,0]+"</a></td><td> "+thing[0,2]+"</td><td>"+thing[0,3]+"</td><td rowspan=\""+count+"\"> "+payment_amount+"</td><td rowspan=\""+count+"\">"+product_name+"</td></tr>";
//}
for(int j = 0 ;j<count-1;j++)
{
//if (thing[j+1,1].Length<20)
///{
str= str+"<tr border=1><td> <a target=_blank href="+thing[j+1,1]+">"+thing[j+1,0]+"</a></td><td> "+thing[j+1,2]+"</td><td>"+thing[j+1,3]+"</td></tr>";
//}
//else
//{
// str= str+"<tr border=1><td> "+thing[j+1,0]+"</td><td> "+thing[j+1,1].Substring(0,20)+"..."+"</td><td> "+thing[j+1,2]+"</td><td>"+thing[j+1,3]+"</td></tr>";
//}
}
}
return str;
}
private string [,]Goods(int id)
{
string strSQL="select orderitem_productname,orderitem_url,orderitem_re,Case OrderItem_SourceType when '1' then '站外' when '0' then '站内' end as OrderItem_SourceType from tbl_orderitem  where order_id = '"+id+"'";
SqlConnection myConnection =  new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["admin"]);
SqlCommand myCommand = new SqlCommand(strSQL,myConnection);
myConnection.Open();
SqlDataReader dr=myCommand.ExecuteReader();
string [,]array=new string[30,4];
for (int j=0;j<30;j++)
{
array[j,0]="";
array[j,1]="";
array[j,2]="";
array[j,3]="";
}
int i = 0;
while(dr.Read())
{
if(i<30)
{
array[i,0]=dr["orderitem_productname"].ToString();
array[i,1]=dr["orderitem_url"].ToString();
array[i,2]=dr["orderitem_re"].ToString();
array[i,3]=dr["OrderItem_SourceType"].ToString();
}
else
{
break;
}
i++;
}
myConnection.Close();
return array;
}

public int Getcount(int orderid)
{
string strSQL="SELECT COUNT(*) AS Expr FROM tbl_orderitem WHERE Order_ID="+orderid;
SqlConnection myConnection =new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["admin"]);
SqlCommand myCommand=new SqlCommand(strSQL,myConnection);
myConnection.Open();
SqlDataReader dr=myCommand.ExecuteReader();
int t=new int();
if (dr.Read())
{
t=Int32.Parse(dr["Expr"].ToString());
}
myConnection.Close();
return t;
}
private void btOrder_search_Click(object sender, System.EventArgs e)
{
GetContent(1);
} }
}

to lyq123456() ( ) 信誉：100 2004-11-25 12:27:00
看一下这端代码：是在客户端的
ruanxiaoyong(汇亦得) ( ) 信誉：100 2004-11-25 10:33:00

Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(tw); this.DataGrid1.RenderControl(hw); Response.Write(tw.ToString());
Response.Flush();
Response.End();

回复人： ruanxiaoyong(汇亦得) :
好象没有这方面的哦，能具体点吗？

private void OutputExcel1(DataSet ds,string str)
{
//
// TODO: 在此处添加构造函数逻辑
//
Excel.Application excel=new Excel.Application( );
int rowIndex=4;
int colIndex=1; Excel._Workbook xBk;
Excel._Worksheet xSt; excel.Visible=true;
xBk = excel.Application.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.Application.ActiveSheet;
xSt.Name="japy";
//
//取得标题
//
foreach(DataColumn col in ds.Tables["custs"].Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
} //
//取得表格中的数据
//
foreach(DataRow row in ds.Tables["custs"].Rows)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in ds.Tables["custs"].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 = Excel.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 = Excel.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 = Excel.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 = Excel.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[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
xBk.Saved=true;
excel.UserControl=false;
excel.ActiveWorkbook.SaveCopyAs(Server.MapPath( ".")+ "\\" +"filename.xls");
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)excel);
GC.Collect();
}

我在上面的代码中有发过客户端的代码的，都在HTML里，你自己好好看一下~

to:ruanxiaoyong(汇亦得) ( ) 信誉：100
感谢啦！
在问一个问题,假如把一个Excel文件上传到数据库（以二进制流的方式存取），我怎么在程序中直接取得该字段的值，然后在程序中打开操作。

为什么导出EXCEL时那么慢，能不能提高速度，如果数据量大时有可能导不出来

调试易

C#中导出Excel问题

解决方案 »

C#中 导出Excel问题

解决方案 »

C#中导出Excel问题