工具:asp.net,C#,做了一个页面。
在网上看了很多例子,其中以下的例子比较简单,我就模仿来做了
string strcon = "Data Source=(local);database=pubs;Trusted_Connection=yes";
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", conn);
DataSet dp = new DataSet();
da.Fill(dp, "table1");
System.Data.DataTable dt = dp.Tables["table1"];
StringWriter sw = new StringWriter();
sw.WriteLine("emp_id\tfname\tminit\tlname\tjob_id\tjob_lvl\tpub_id\thire_date");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(dr["emp_id"] + "\t" + dr["fname"] + "\t" + dr["minit"] + "\t" + dr["lname"] + dr["job_id"] + dr["job_lvl"] + dr["pub_id"] + dr["hire_date"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();问题:字段长度长短不一时,不会放在一个excel格子里面,而我文本头就按空格分开,但数据就各自按自己的长度排列,\t是不能解决问题的。那怎么处理呢?请各位大虾指教。谢谢。完整代码:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" EnableEventValidation = "false" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="membergrid" runat="server" HeaderStyle-BackColor="AliceBlue"
AlternatingItemStyle-BackColor="#aaaadd" AllowPaging="True"
AutoGenerateColumns="False" PageSize="14" Height="1px" Width="531px"
OnPageIndexChanged="membergrid_PageIndexChanged" >
<AlternatingItemStyle BackColor="#AAAADD" />
<HeaderStyle BackColor="AliceBlue" />
<Columns>
<asp:BoundColumn DataField="emp_id" HeaderText="emp_id"></asp:BoundColumn>
<asp:BoundColumn DataField="fname" HeaderText="fname"></asp:BoundColumn>
<asp:BoundColumn DataField="minit" HeaderText="minit"></asp:BoundColumn>
<asp:BoundColumn DataField="lname" HeaderText="lname"></asp:BoundColumn>
<asp:BoundColumn DataField="job_id" HeaderText="job_id"></asp:BoundColumn>
<asp:BoundColumn DataField="job_lvl" HeaderText="job_lvl"></asp:BoundColumn>
<asp:BoundColumn DataField="pub_id" HeaderText="pub_id"></asp:BoundColumn>
<asp:BoundColumn DataField="hire_date" HeaderText="hire_date"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</div>
<asp:Button ID="Button1" runat="server" Text="转换成EXCEL" OnClick="Button1_Click" Height="30px" Width="135px" />
</form>
</body>
</html>using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
string strconn = "server=(local);database=pubs; Trusted_Connection=yes";
SqlConnection cn = new SqlConnection(strconn);
String strSQL = "select * from employee";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
ds = new DataSet();
da.Fill(ds);
membergrid.DataSource = ds;
membergrid.DataBind();
}
//分页
protected void membergrid_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
membergrid.CurrentPageIndex = e.NewPageIndex; //显示当前DataGrid的数据集ds
membergrid.DataSource = ds;
membergrid.DataBind();
}
//导出excel
protected void Button1_Click(object sender, EventArgs e)
{
string strcon = "Data Source=(local);database=pubs;Trusted_Connection=yes";
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", conn);
DataSet dp = new DataSet();
da.Fill(dp, "table1");
System.Data.DataTable dt = dp.Tables["table1"];
StringWriter sw = new StringWriter();
sw.WriteLine("emp_id\tfname\tminit\tlname\tjob_id\tjob_lvl\tpub_id\thire_date");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(dr["emp_id"] + "\t" + dr["fname"] + "\t" + dr["minit"] + "\t" + dr["lname"] + dr["job_id"] + dr["job_lvl"] + dr["pub_id"] + dr["hire_date"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
}
在网上看了很多例子,其中以下的例子比较简单,我就模仿来做了
string strcon = "Data Source=(local);database=pubs;Trusted_Connection=yes";
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", conn);
DataSet dp = new DataSet();
da.Fill(dp, "table1");
System.Data.DataTable dt = dp.Tables["table1"];
StringWriter sw = new StringWriter();
sw.WriteLine("emp_id\tfname\tminit\tlname\tjob_id\tjob_lvl\tpub_id\thire_date");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(dr["emp_id"] + "\t" + dr["fname"] + "\t" + dr["minit"] + "\t" + dr["lname"] + dr["job_id"] + dr["job_lvl"] + dr["pub_id"] + dr["hire_date"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();问题:字段长度长短不一时,不会放在一个excel格子里面,而我文本头就按空格分开,但数据就各自按自己的长度排列,\t是不能解决问题的。那怎么处理呢?请各位大虾指教。谢谢。完整代码:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" EnableEventValidation = "false" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>test</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="membergrid" runat="server" HeaderStyle-BackColor="AliceBlue"
AlternatingItemStyle-BackColor="#aaaadd" AllowPaging="True"
AutoGenerateColumns="False" PageSize="14" Height="1px" Width="531px"
OnPageIndexChanged="membergrid_PageIndexChanged" >
<AlternatingItemStyle BackColor="#AAAADD" />
<HeaderStyle BackColor="AliceBlue" />
<Columns>
<asp:BoundColumn DataField="emp_id" HeaderText="emp_id"></asp:BoundColumn>
<asp:BoundColumn DataField="fname" HeaderText="fname"></asp:BoundColumn>
<asp:BoundColumn DataField="minit" HeaderText="minit"></asp:BoundColumn>
<asp:BoundColumn DataField="lname" HeaderText="lname"></asp:BoundColumn>
<asp:BoundColumn DataField="job_id" HeaderText="job_id"></asp:BoundColumn>
<asp:BoundColumn DataField="job_lvl" HeaderText="job_lvl"></asp:BoundColumn>
<asp:BoundColumn DataField="pub_id" HeaderText="pub_id"></asp:BoundColumn>
<asp:BoundColumn DataField="hire_date" HeaderText="hire_date"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</div>
<asp:Button ID="Button1" runat="server" Text="转换成EXCEL" OnClick="Button1_Click" Height="30px" Width="135px" />
</form>
</body>
</html>using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
DataSet ds;
protected void Page_Load(object sender, EventArgs e)
{
string strconn = "server=(local);database=pubs; Trusted_Connection=yes";
SqlConnection cn = new SqlConnection(strconn);
String strSQL = "select * from employee";
SqlDataAdapter da = new SqlDataAdapter(strSQL, cn);
ds = new DataSet();
da.Fill(ds);
membergrid.DataSource = ds;
membergrid.DataBind();
}
//分页
protected void membergrid_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
membergrid.CurrentPageIndex = e.NewPageIndex; //显示当前DataGrid的数据集ds
membergrid.DataSource = ds;
membergrid.DataBind();
}
//导出excel
protected void Button1_Click(object sender, EventArgs e)
{
string strcon = "Data Source=(local);database=pubs;Trusted_Connection=yes";
SqlConnection conn = new SqlConnection(strcon);
SqlDataAdapter da = new SqlDataAdapter("select * from employee", conn);
DataSet dp = new DataSet();
da.Fill(dp, "table1");
System.Data.DataTable dt = dp.Tables["table1"];
StringWriter sw = new StringWriter();
sw.WriteLine("emp_id\tfname\tminit\tlname\tjob_id\tjob_lvl\tpub_id\thire_date");
foreach (DataRow dr in dt.Rows)
{
sw.WriteLine(dr["emp_id"] + "\t" + dr["fname"] + "\t" + dr["minit"] + "\t" + dr["lname"] + dr["job_id"] + dr["job_lvl"] + dr["pub_id"] + dr["hire_date"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
}
dg.DataSource=dt;
dg.DataBind();
Response.Clear();
Response.Buffer=true;
Response.Charset="UTF-8";
Response.AddHeader("Content-Disposition","attachment;filename="+DateTime.Today.Year+DateTime.Today.Month+DateTime.Today.Day+".xls");
Response.ContentEncoding=System.Text.Encoding.GetEncoding("UTF-7");
Response.ContentType="application/ms-excel";
EnableViewState=false;
System.IO.StringWriter sw=new System.IO.StringWriter();
HtmlTextWriter tw=new HtmlTextWriter(sw);
dg.RenderControl(tw);
Response.Write(sw.ToString());
Response.End();如果想要让导出的Excel数据没格式需多添加一个DataGrid和DataTable重新绑定下,然后设置其中的ItemStyle中FONT的是属性,这样导出的Excel表就能没有格式,并且所有数据都居中 如何你还要对导出的EXCEL表进行操作,如下
private void btnSummary_Click(object sender, System.EventArgs e)
{
try
{
WebbUpload summaryUpLoad=new WebbUpload();
summaryUpLoad.RegisterProgressBar(this.btnSummary);
summaryUpLoad.SetTempPath(Server.MapPath(".."));
UploadFile upSummary=new UploadFile("upSummary");
//判断是否已存在
bool exist=System.IO.File.Exists(Server.MapPath("..")+"\\Files\\"+"phs_expense_summary.xls");
if(exist==true)
{
System.IO.File.Delete(Server.MapPath("..")+"\\Files\\"+"phs_expense_summary.xls");
}
upSummary.SaveAs(Server.MapPath("..")+"\\Files\\"+"phs_expense_summary.xls"); string sourceFile="phs_expense_summary.xls";
string templatePath=Server.MapPath("..")+"\\Files\\";
string downloadPath=Server.MapPath("..");
string tempFileName="Test"+".xls";
Excel.Application myExcel=new Excel.Application(); myExcel.Visible=true;
myExcel.Application.Workbooks.Open(templatePath+"\\"+sourceFile,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet curSheet = (Excel.Worksheet)myBook.Sheets[1]; string downloadFilePath=downloadPath+"\\"+tempFileName; //Sheet重命名
curSheet.Name="Sheet1";
Excel.Range range; // 取第一行,第一列这个格的值 range = curSheet.get_Range("C1",Type.Missing); range = range.get_Resize(1, 1); string s = range.get_Value(Type.Missing).ToString(); if(s!="C"&&s!="c")
{
//删除Excel文件第一行数据
Range row=(Range)curSheet.Cells[1,Type.Missing];
row.EntireRow.Delete(Excel.XlDirection.xlUp); //将现在第一行每列赋值
myExcel.Cells[1,1]="A";
myExcel.Cells[1,2]="B";
myExcel.Cells[1,3]="C";
myExcel.Cells[1,4]="D";
myExcel.Cells[1,5]="E";
myExcel.Cells[1,6]="F";
} int t=curSheet.UsedRange.Rows.Count;
Excel.Range r;
for(int i=1;i<=curSheet.UsedRange.Rows.Count;i++)
{
r=curSheet.get_Range(curSheet.Cells[i,3],curSheet.Cells[i,3]);
string str=r.Text.ToString();
if(str==""||str==null||str=="aaaaaaa"||str=="bbbbbbb"||str=="ccccccc"||str=="ddddddd"||str=="eeeeeee"||str=="fffffff")
{
((Excel.Range)curSheet.Rows[i, Missing.Value]).Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
i=i-1;
}
} foreach (Excel.Workbook book in myExcel.Application.Workbooks)
{
book.Save();
} myBook.Close(false,Type.Missing, Type.Missing);
GC.Collect();
this.lblSummaryStatue.Text="Ready";
this.lblSummaryStatue.ForeColor=Color.FromName("Green");
this.btnSummary.Enabled=false;
}
catch(Exception ex)
{
ComponentFactory.GetLogger(System.Reflection.MethodBase.GetCurrentMethod(),"").Error(ex.ToString());
UIUtility.Alert("phs_expense_detailSummary.xls上傳失敗!",this);
} }