最近在做WEB导出Excel档的,总结如下:
public int flags = 0;
protected void dgvStockDetail_RowDataBound(object sender, GridViewRowEventArgs e)
{
//导出时隐藏GridView的某一列
if (flags == 1)
{
e.Row.Cells[18].Visible = false;
}
//禁止导出时显示科学计数法
for (int i = 0; i < e.Row.Cells.Count; i++)
{
if (e.Row.RowType == DataControlRowType.DataRow)
e.Row.Cells[i].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
#region BindPageNum 绑定分页控件
/// <summary>
/// 绑定分页控件
/// </summary>
/// <param name="dt">数据源</param>
public void BindPageNum(DataTable dt)
{
AspNetPager.RecordCount = dt.Rows.Count;//获得总行数
PagedDataSource pds = new PagedDataSource();
pds.DataSource = dt.DefaultView;
pds.AllowPaging = true;
pds.CurrentPageIndex = AspNetPager.CurrentPageIndex - 1;
pds.PageSize = AspNetPager.PageSize;
if (flags != 0)
{ //若要导出,则设置pagesize,以避免分页。
dgvStockDetail.PageSize = 100000;
}
else
{
dgvStockDetail.PageSize = 20;
}
dgvStockDetail.DataSource = pds;
dgvStockDetail.DataBind();
getstr = Server.UrlEncode(txtRMANum.Text.Trim() + "|" + txtInvoiceNum.Text.Trim() + "|" + txtCartonNum.Text.Trim() + "|" + txtBookNum.Text.Trim() + "|" + txtPartNum.Text.Trim() + "|" + txtGoodsName.Text.Trim() + "|" + txtMadeIn.Text.Trim() + "|" + AspNetPager.CurrentPageIndex.ToString());
//显示记录信息
AspNetPager.CustomInfoHTML = "记录总数:<b>" + AspNetPager.RecordCount.ToString() + "</b> ";
AspNetPager.CustomInfoHTML += " 总页数:<b>" + AspNetPager.PageCount.ToString() + "</b> ";
AspNetPager.CustomInfoHTML += " 当前页:<font color=\"red\"><b>" + AspNetPager.CurrentPageIndex.ToString() + "</b></font>";
}
#endregion
#region btnToExcel_Click 导出按钮
protected void btnToExcel_Click(object sender, EventArgs e)
{
//GetDt(this.dgvTransport);
//return;
flags = 1;
AspNetPager.PageSize = 999;
BindData(1);
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
Page page = new Page();
HtmlForm form = new HtmlForm();
dgvStockDetail.EnableViewState = false;
// Deshabilitar la validación de eventos, sólo asp.net 2
page.EnableEventValidation = false;
// Realiza las inicializaciones de la instancia de la clase Page que requieran los diseñadores RAD.
page.DesignerInitialize();
page.Controls.Add(form);
form.Controls.Add(dgvStockDetail);
page.RenderControl(htw);
//Response.Clear();
//Response.Buffer = true;
//Response.ContentType = "application/vnd.ms-excel";
//Response.AddHeader("Content-Disposition", "attachment;filename=data.xls");
//Response.Charset = "UTF-7";
//Response.ContentEncoding = Encoding.UTF7;
Response.Clear();
Response.Buffer = true;
Response.Charset = "gb2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("content-disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode("data", System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = "Application/ms-excel";
Response.Write(sb.ToString());
Response.End();
flags = 0;
AspNetPager.PageSize = 20;
BindData(1);
}
#endregion
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
在winform程序开发时,处理的办法就是在导出的过程中,开始试了 处理excel对象的格式 mysheet.Cells.NumberFormat = "#";
后来没有成功。最后还是用了逐条纪录进行字符格式转化的方法,即添加“ ' ”.
我写得代码主要部分如下
#region 执行数据导出
try
{
//到导出excel
Excel.ApplicationClass my = new Excel.ApplicationClass();
if (my == null)
{
MessageBox.Show("无法创建excel对象,可能您的系统没有安装excel");
return;
}
my.Visible = false;
Excel.Workbook mybook = (Excel.Workbook)my.Workbooks.Add(1);
((Excel.Worksheet)mybook.Worksheets[1]).Name = "sheet1";
Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];
// mysheet.Cells.NumberFormat = "#";
//导出列名
for (int j = 0; j < this.dgvShow.Columns.Count; j++)
{
if (this.dgvShow.Columns[j].Visible == true)
{
mysheet.Cells[1, j + 1] = "'" + Convert.ToString(this.dgvShow.Columns[j].HeaderText);//加"'"防止科 学计数法
}
}
//导出数据
for (int i = 0; i < this.dgvShow.Rows.Count; i++)
{
for (int j = 0; j < this.dgvShow.Columns.Count; j++)
{
mysheet.Cells[i + 2, j + 1] = "'" + Convert.ToString(this.dgvShow.Rows[i].Cells[j].Value);
}
}
if (savefilename != "")
{
try
{
//mybook.Save();
mybook.SaveCopyAs(savefilename);
MessageBox.Show("excel文件导出成功!");
}
catch (Exception ex)
{
MessageBox.Show("导出文件出现错误,文件可能正被打开!/n" + ex.Message);
}
}
GC.Collect();
}
catch (Exception ex)
{
MessageBox.Show("数据导出时出现错误,一下是详细错误信息:/n" + ex.Message);
return;
}
#endregion