Web开发经常要用到导出Excel表单,因为写了个简单的通用函数
public class ShareFunctions{
public static string GridViewToExcel(DataTable _dt,string[] _dataFields,string[] _dataFieldTexts)
{
if (_dataFields.Length != _dataFieldTexts.Length)
throw (new Exception("字段列和字段名不符"));
if (_dt == null || _dt.Rows.Count==0)
throw (new Exception("数据源为空"));
System.Web.UI.WebControls.GridView _exportExcel = new GridView();
_exportExcel.AutoGenerateColumns = false;
List<int> _cellIdx = new List<int>();//记录数据源中 数据类型为Sring 的行号
for (int i = 0; i < _dataFields.Length; i++)
{
BoundField f = new BoundField();
f.DataField = _dataFields[i];
f.HeaderText = _dataFieldTexts[i];
f.HtmlEncode = false;
f.HeaderStyle.BackColor = System.Drawing.Color.FromArgb(0xff, 0xff, 0xcc);
_exportExcel.Columns.Add(f);
if (_dt.Rows[0][_dataFields[i]].GetType().Equals(Type.GetType("System.String")))
{
_cellIdx.Add(i);
}
}
_exportExcel.DataSource = _dt;
_exportExcel.DataBind();
foreach(GridViewRow _dr in _exportExcel.Rows)
{
foreach (int i in _cellIdx)
{
_dr.Cells[i].Attributes["style"] += "vnd.ms-excel.numberformat:@";
}
}
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
_exportExcel.RenderControl(hw);
return "<meta http-equiv=Content-Type content=/"text/html; charset=UTF-8/">" + tw.ToString();
}
public static void ExportGridViewToExcel(DataTable _dt, string[] _dataFields, string[] _dataFieldTexts,
HttpResponse Response, string FileType, string FileName)
{
string _exportStr = GridViewToExcel(_dt, _dataFields, _dataFieldTexts);
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
Response.Write(_exportStr);
Response.End();
}
}
页面放个Button控件
<asp:Button ID="btnGetExcel" CssClass="button" runat="server" OnClick="ButGetExcel_Click" Text="导出Excel" />
后台代码
protected void ButGetExcel_Click(object sender, EventArgs e)
{
DataTable dt = GetDataSoc("生产明细");
ShareFunctions.ExportGridViewToExcel(dt,
new string[] { "IndetPkNum","ModName","PNumber","Total_Weight","Weight","Amount_All","StorageCount","ReMark" },
new string[] { "明细编号","商品名称","款式编号","单项总重","单件重量","商品数量","库存数量","订单备注" }
, Response, "application/ms-excel", "CustomerInStorageExcel.xls");
}