excel在日常办公生活中应用广泛,使用C#导出Excel的方法有很多种,网上介绍的资料也很多。
这篇文章属于个人多年来导出Excel的经验总结,包括各种生成excel的方法,输出excel,释放资源以及使用开源项目等。
1、csv结构(用英文逗号分割列,用换行符分割行)
遍历数据,构造一个csv文件,然后可以用Excel打开。
代码示例:
/// <summary> /// 将DataSet导出成CSV格式 /// </summary> /// <param name="tb">DataTable结果集</param> /// <returns>CSV字符串数据</returns> public static string ExportToCSV(DataTable tb) { string data = ""; //写出列名 foreach (DataColumn column in tb.Columns) { data += column.ColumnName + ","; } data += "\n"; //写出数据 foreach (DataRow row in tb.Rows) { foreach (DataColumn column in tb.Columns) { data += "\t" + row[column].ToString() + ","; } data += "\n"; } data += "\n"; return data; } protected void Button2_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.Add("编号"); dt.Columns.Add("姓名"); dt.Columns.Add("性别"); dt.Rows.Add("001", "张三", "男"); dt.Rows.Add("002", "李四", "女"); string data = ExportToCSV(dt); string temp = string.Format("attachment;filename={0}", "ExportData.csv"); Response.ClearHeaders(); Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.UTF8; Response.HeaderEncoding = System.Text.Encoding.UTF8; Response.ContentType = "text/csv"; //在文件输出流前加一段二进制流的输出,解决中文乱码问题 Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF }); Response.AppendHeader("Content-disposition", temp); Response.Write(data); Response.Flush(); Response.Close(); }
注意事项:
1)导出数据时,凡是单元格里面全是数字的单元格,在用Excel打开的时候,都被自动转义成数据格式,数据查看极其不方便。
解决方法:
1.对于导出CSV文件,在禁止转义的内容前,加上一个TAB符号,C#里面是\t;
2.对于EXCEL文件,在禁止转义的内容前,加上一个单引号符号,C#里面是'。
2、制表符或者html
遍历数据,用制表符分割数据到不同的单元,然后可以用Excel打开。
(1)制表符结构:\t 分割列,\n 分割行。
代码示例:
public void CreateExcel(DataTable dt, string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); resp.ContentType = "application/vnd.ms-excel"; string colHeaders = "", ls_item = ""; //可以类似dt.Select("id>10")之形式达到数据筛选目的 DataRow[] myRow = dt.Select(); int i = 0; int cl = dt.Columns.Count; //各列之间以\t分割,最后一个列标题后加回车符 for (i = 0; i < cl; i++) { //最后一列,加\n if (i == (cl - 1)) { colHeaders += dt.Columns[i].Caption.ToString() + "\n"; } else { colHeaders += dt.Columns[i].Caption.ToString() + "\t"; } } //向HTTP输出流中写入取得的数据信息 resp.Write(colHeaders); //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流,并且置空ls_item以便下行数据 for (i = 0; i < cl; i++) { //最后一列,加\n if (i == (cl - 1)) { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } resp.Write(ls_item); ls_item = ""; } resp.End(); } protected void Button1_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.Add("编号"); dt.Columns.Add("姓名"); dt.Columns.Add("性别"); dt.Rows.Add("001", "张三", "男"); dt.Rows.Add("002", "李四", "女"); CreateExcel(dt, "class.xls"); }
(2)html table结构:<table><tr><td></td></tr></table>
代码示例:
/// <summary> /// 导出HTML格式的EXCEL /// </summary> /// <param name="strFileName">文件名称</param> /// <param name="excelHtml">html内容</param> public static void ExportExcelHtml(string strFileName, string excelHtml) { string ExcelExt= ".xls"; //清除Response缓存内容 HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; strFileName = !strFileName.ToLower().Trim().EndsWith(ExcelExt) ? strFileName + ExcelExt : strFileName; //确定字符的编码格式 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" + HttpUtility.UrlEncode(strFileName)); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //消除乱码特别设定 string strExcel = ""; strExcel += excelHtml; HttpContext.Current.Response.Write(strExcel); HttpContext.Current.Response.End(); } protected void Button1_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.Add("编号"); dt.Columns.Add("姓名"); dt.Columns.Add("性别"); dt.Rows.Add("001", "张三", "男"); dt.Rows.Add("002", "李四", "女"); StringBuilder sbl=new StringBuilder(); sbl.Append("<table id=\"tableExcel\" width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">"); foreach(DataRow dr in dt.Rows) { sbl.Append("<tr>"); foreach (DataColumn column in dt.Columns) { sbl.Append("<td>"+dr[column].ToString()+"</td>"); } sbl.Append("</tr>"); } sbl.Append("</table>"); ExportExcelHtml("test", sbl.ToString()); }