C# 导出excel
/// <summary> /// DataTable导出到Excel /// </summary> /// <param name="dt">DataTable类型的数据源</param> /// <param name="FileType">文件类型</param> /// <param name="FileName">文件名</param> public void CreateExcel(DataTable dt, string FileType, string FileName) { Response.Clear(); Response.Charset = "UTF-8"; Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\""); Response.ContentType = FileType; string colHeaders = string.Empty; string ls_item = string.Empty; DataRow[] myRow = dt.Select(); int i = 0; int cl = dt.Columns.Count; for (int j = 0; j < dt.Columns.Count; j++) { ls_item += dt.Columns[j].ColumnName + "\t"; //栏位:自动跳到下一单元格 } ls_item = ls_item.Substring(0, ls_item.Length - 1) + "\n"; foreach (DataRow row in myRow) { for (i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } Response.Output.Write(ls_item); ls_item = string.Empty; } Response.Output.Flush(); Response.End(); }
调用方法:
string sql = "SELECT hy_no '促销员编号',UserName '促销员姓名',in_date '日期',(SELECT jpname FROM BasJp WHERE fid=t1.Jp_id) '奖品名称',jf '积分',amount '数量',linktell '联系方式',linkaddr '联系地址',CASE WHEN status = 1 THEN '待确认' WHEN status = 4 THEN '已确认' ELSE '其他' END '状态' FROM hy_djjl t1 order BY in_date DESC"; DataSet ds = DbHelperSQL.Query(sql); if (ds != null && ds.Tables[0].Rows.Count > 0) { DataTable dt = ds.Tables[0]; CreateExcel(dt, "application/ms-excel", "兑奖记录-" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xls");//调用函数 }