导出excel、word、csv文件方法汇总

http://www.woaic.com/2012/06/64

excel文件主要是输出html代码。以xls的文本格式保存文件。

生成excel格式的代码：

/// <summary>
 /// 输出excel格式的html代码
 /// </summary>
 /// <param name="dt"></param>
 /// <param name="fileName"></param>
 /// <param name="tableHeader"></param>
 /// <param name="UseDefinedHeader"></param>
 /// <returns></returns>
 public string MakeExcelStr(DataTable dt, string fileName, string[] tableHeader,bool UseDefinedHeader)
 {
 StringBuilder strResult = new StringBuilder();
 int iColCount = dt.Columns.Count;
 if (dt.Columns.Count == 0)
 return strResult.ToString();
 strResult.Append("<table cellspacing="0" cellpadding="5" rules="all" border="1">");
 strResult.Append("<tr style="font-weight: bold; white-space: nowrap;">");
 if (UseDefinedHeader)//是否是用户自定义表头
 {
 for (int i = 0; i < tableHeader.Length; i++)
 {
 strResult.Append("<td style="text-align:center">" + tableHeader[i] + "</td>");//让文本居中显示
 }
 }
 else
 {
 for (int i = 0; i < iColCount; i++)
 {
 strResult.Append("<td style="text-align:center">" + (dt.Columns[i] + "").ToString() + "</td>");
 }
 }
 strResult.Append("</tr>");
 foreach (DataRow dr in dt.Rows)
 {
 strResult.Append("<tr>");
 for (int i = 0; i < iColCount; i++)
 {
 // style="vnd.ms-excel.numberformat:@"
 strResult.Append("<td style="vnd.ms-excel.numberformat:@">'" + (dr[i] + "").ToString() + "</td>");//设置单元格是文本格式（防止文本是科学计算法，即E+***这种情况）
 }
 strResult.Append("</tr>");
 }
 strResult.Append("</table>");
 
return strResult.ToString();
 }

把生成的excel格式的代码保存成excel文件的方法

/// <summary>
 /// 导出excel表格
 /// </summary>
 /// <param name="dt"></param>
 /// <param name="fileName"></param>
 /// <param name="tableHeader">表头</param>
 /// <param name="Defined">是否自定义表头</param>
 private void ExprotToExcel(DataTable dt, string fileName, string[] tableHeader, bool Defined)
 {
 DogManage.Common.ExcelHelper excelHelper = new DogManage.Common.ExcelHelper();
 string result = excelHelper.MakeExcelStr(dt, fileName, tableHeader,Defined);
 
 HttpContext.Current.Response.Clear();
 System.IO.StringWriter sw = new System.IO.StringWriter();
 sw.Write(result);
 sw.Close();
 string str2 = HttpUtility.UrlEncode(fileName + "_" + DateTime.Now.ToString("yyyy-MM-dd"));
 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +str2 + ".xls");
 HttpContext.Current.Response.ContentType = "application/ms-excel";
 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
 HttpContext.Current.Response.Write(sw);
 HttpContext.Current.Response.End();
 }

生成word的代码：

private void exportword()
 {
 Response.Clear();
 Response.Buffer = true;
 Response.Charset = "utf-8";
 Response.AppendHeader("Content-Disposition", "attachment;filename=tmp.doc");
 Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
 
Response.ContentType = "application/ms-word";
 this.EnableViewState = false;
 System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
 System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
 oHtmlTextWriter.WriteLine(hf.Value);
 Response.Write(oStringWriter.ToString());
 Response.End();
 }

csv文件就是一个有特定格式的字符串拼接出来的文件，每个列通过,分割开来。换行则两外输出一行文本就行了

生成csv文件的代码：

/// <summary>
 /// 输出csv文件的代码
 /// </summary>
 /// <param name="dt"></param>
 /// <param name="fileName"></param>
 /// <param name="tableHeader">表头</param>
 /// <returns></returns>
 public string MakeCSVStr(DataTable dt, string fileName, string[] tableHeader)
 {
 StringBuilder strResult = new StringBuilder();
 string temp = string.Empty;
 int iColCount = dt.Columns.Count;
 if (iColCount == 0)
 return strResult.ToString();
 for (int i = 0; i < iColCount; i++)
 {
 temp+=""" + dt.Columns[i] + """;
 if (i < iColCount - 1)
 temp+=",";
 }
 strResult.AppendLine(temp);
 foreach (DataRow dr in dt.Rows)
 {
 temp = "";
 for (int i = 0; i < iColCount; i++)
 {
 if (!Convert.IsDBNull(dr[i]))
 temp+=""" + dr[i].ToString() + """;
 else
 temp += """";
 if (i < iColCount - 1)
 temp+=",";
 }
 strResult.AppendLine(temp);
 }
 return strResult.ToString();
 }

导出csv文件的方法：

/// <summary>
 /// 导出csv文件
 /// </summary>
 /// <param name="dt"></param>
 /// <param name="fileName"></param>
 /// <param name="tableHeader">表头</param>
 /// <param name="Defined">是否自定义表头</param>
 private void ExprotToCSV(DataTable dt,string fileName, string[] tableHeader)
 {
 DogManage.Common.ExcelHelper excelHelper = new DogManage.Common.ExcelHelper();
 string result=excelHelper.MakeCSVStr(dt, fileName, tableHeader);
 
HttpContext.Current.Response.Clear();
 System.IO.StringWriter sw = new System.IO.StringWriter();
 sw.Write(result);
 sw.Close();
 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");
 HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
 HttpContext.Current.Response.Write(sw);
 HttpContext.Current.Response.End();
 }