这是代码
string Role = Request.QueryString["Role"].ToString();
int i = 0;
int j = 1;
System.Data.DataTable dt = data.Tables[0];
DataRow[] myRow = dt.Select();
int cl = dt.Columns.Count;
Excel.Application excel = new Excel.Application();
if (excel == null)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "alert('系统检测到您的Excel没有安装或无法正常启动')", true);
return;
}
excel.Application.Workbooks.Add(true);
for (i = 0; i < cl; i++)
{
excel.Cells[j, i + 1] = dt.Columns[i].Caption.ToString();
excel.get_Range(excel.Cells[1, i + 1], excel.Cells[1, i + 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
j++;
foreach (DataRow row in myRow)
{
for (i = 0; i < cl; i++)
{
excel.Cells[j, i + 1] = row[i].ToString();
}
if (i > 9&&Role=="Expert")
{
excel.get_Range(excel.Cells[1, 9], excel.Cells[j + 1, 9]).NumberFormatLocal = "yyyy-mm-dd";
}
excel.get_Range(excel.Cells[1, 2], excel.Cells[j + 1, 2]).NumberFormatLocal = "@";
j++;
}
excel.Visible = true;
ds.Clear();
GC.Collect();
在本机上运行一点问题都没有
但是如果 加到服务器上面就什么错误提示也没有,什么反应也没有,excel程序的权限也已经设置了怎么回事啊
string Role = Request.QueryString["Role"].ToString();
int i = 0;
int j = 1;
System.Data.DataTable dt = data.Tables[0];
DataRow[] myRow = dt.Select();
int cl = dt.Columns.Count;
Excel.Application excel = new Excel.Application();
if (excel == null)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "alert('系统检测到您的Excel没有安装或无法正常启动')", true);
return;
}
excel.Application.Workbooks.Add(true);
for (i = 0; i < cl; i++)
{
excel.Cells[j, i + 1] = dt.Columns[i].Caption.ToString();
excel.get_Range(excel.Cells[1, i + 1], excel.Cells[1, i + 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
j++;
foreach (DataRow row in myRow)
{
for (i = 0; i < cl; i++)
{
excel.Cells[j, i + 1] = row[i].ToString();
}
if (i > 9&&Role=="Expert")
{
excel.get_Range(excel.Cells[1, 9], excel.Cells[j + 1, 9]).NumberFormatLocal = "yyyy-mm-dd";
}
excel.get_Range(excel.Cells[1, 2], excel.Cells[j + 1, 2]).NumberFormatLocal = "@";
j++;
}
excel.Visible = true;
ds.Clear();
GC.Collect();
在本机上运行一点问题都没有
但是如果 加到服务器上面就什么错误提示也没有,什么反应也没有,excel程序的权限也已经设置了怎么回事啊
{
object omissing = system.reflection.missing.value;
excel.applicationclass xlapp = new excel.applicationclass();
try
{
// 打开excel文件。以下为office 2000.
excel.workbook xlworkbook = xlapp.workbooks.open(filepath, omissing, omissing, omissing, omissing, omissing,
omissing, omissing, omissing, omissing, omissing, omissing,
omissing);
excel.worksheet xlworksheet;
// 循环所有datatable
for( int i=0; i<ds.tables.count; i++ )
{
// 添加入一个新的sheet页。
xlworksheet = (excel.worksheet)xlworkbook.worksheets.add(omissing,omissing,1,omissing);
// 以tablename作为新加的sheet页名。
xlworksheet.name = ds.tables[i].tablename;
// 取出这个datatable中的所有值,暂存于stringbuffer中。
string stringbuffer = "";
for( int j=0; j<ds.tables[i].rows.count; j++ )
{
for( int k=0; k<ds.tables[i].columns.count; k++ )
{ stringbuffer += ds.tables[i].rows[j][k].tostring();
if( k < ds.tables[i].columns.count - 1 )
stringbuffer += "\t";
}
stringbuffer += "\n";
}
// 利用系统剪切板
system.windows.forms.clipboard.setdataobject("");
// 将stringbuffer放入剪切板。
system.windows.forms.clipboard.setdataobject(stringbuffer);
// 选中这个sheet页中的第一个单元格
((excel.range)xlworksheet.cells[1,1]).select();
// 粘贴!
xlworksheet.paste(omissing,omissing);
// 清空系统剪切板。
system.windows.forms.clipboard.setdataobject("");
}
// 保存并关闭这个工作簿。
xlworkbook.close( excel.xlsaveaction.xlsavechanges, omissing, omissing );
system.runtime.interopservices.marshal.releasecomobject(xlworkbook);
xlworkbook = null;
}
catch(exception ex)
{
messagebox.show(ex.message);
}
finally
{
// 释放...
xlapp.quit();
system.runtime.interopservices.marshal.releasecomobject(xlapp);
xlapp = null;
gc.collect();
}
}
楼主可以参考一下……
Dim j As Integer
Dim sqtjzhi As String = ""
'权限判断
If m01 = "7" Then '只有权限到社区的时候才提示要随访的人数
sqtjzhi = " and x.sq=" & m02 & " and jd='" + ljd + "'"
'sql = "select x.name,case x.H_bzid when 'wz_tjxx_zh_djb.aspx' then 'tj/wz_tjxx_zh_djb.aspx' when '../hebeitj/hb_tjxx_djb.aspx' then 'hebeitj/hb_tjxx_djb.aspx' when '../wsbxm/tjxx_hch.aspx' then 'wsbxm/tjxx_hch.aspx' end as H_bzid,d.presj ,d.nextsj,d.bz,d.jcid,d.Avgsbp,d.Avgdbp from ddsf d left join xyjcb x on d.jcid=x.id where d.nextsj <='" & Now.ToShortDateString.ToString & " ' and x.status=0 " & sqtjzhi & " order by d.nextsj"
sql = "select x.name,d.Avgsbp,d.Avgdbp,convert(varchar(10),d.presj,121) as presj ,d.nextsj from ddsf d left join xyjcb x on d.jcid=x.id where d.nextsj <='" & Today.AddDays(14).ToString & " ' and x.status=0 " & sqtjzhi & " " + strssrq + " order by d.nextsj" Dim adapter As New SqlDataAdapter(sql, con)
Dim ds As New DataSet
adapter.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
DataGrid1.DataBind()
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False Dim objSW As New System.IO.StringWriter
Dim objHTW As New System.Web.UI.HtmlTextWriter(objSW)
DataGrid1.RenderControl(objHTW)
Response.Write(objSW.ToString)
Response.End()
'Response.AddHeader("content-disposition", "attachment;filename=myfile.xls")
End If
<asp:datagrid id="DataGrid1" runat="server" Font-Names="宋体" Font-Size="9pt" Height="100%" Width="100%"
BorderStyle="None" BorderWidth="1px" BorderColor="#CC9966" BackColor="White" CellPadding="4">
<SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66"> </SelectedItemStyle>
<AlternatingItemStyle BackColor="#FFCC99"> </AlternatingItemStyle>
<ItemStyle BorderWidth="2px" ForeColor="#330099" BorderStyle="Solid" BorderColor="Black" BackColor="White"> </ItemStyle>
<HeaderStyle Font-Bold="True" HorizontalAlign="Center" BorderWidth="2px" ForeColor="#FFFFCC"
BorderStyle="Solid" BorderColor="Black" BackColor="#990000"> </HeaderStyle>
</asp:datagrid>
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("User", System.Text.Encoding.UTF8) + ".xls"); string colHeaders = "", ls_item = ""; //定义表对象与行对象,同时用DataSet对其值进行初始化
System.Data.DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count; //取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
} }
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息 //逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列,加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
} }
resp.Write(ls_item);
ls_item = "";
可是当密码那列前面有0的时候,0就会省略掉,怎么加样式啊
就像html一样,输出到excel
至于0省略了,是因为把它当成数字了.
可以这样:<td style='mso-numer-format:\@'>000111111</td>
==>
colHeaders += "<td>"+dt.Columns[i].Caption.ToString() + "</td></tr>";
其他的地方也是如此.
就是画个table