目前个人认为比较好用,比较简单的生成Excel表格的方式有两种,一种为直接写方法,一种为引用Excel库
关于引用 Microsoft.Office.Interop.Excel; 出现问题的解决方案
一. 这种方式生成速度比较快,适合大数据量对样式没什么要求的项目,下面上代码
/// <summary> /// Datatable生成Excel表格并返回路径 /// </summary> /// <param name="m_DataTable">Datatable</param> /// <param name="s_FileName">文件名</param> /// <returns></returns> public string DataToExcel(System.Data.DataTable m_DataTable, string s_FileName) { string FileName = AppDomain.CurrentDomain.BaseDirectory + ("/Upload/Excel/") + s_FileName + ".xls"; //文件存放路径 if (System.IO.File.Exists(FileName)) //存在则删除 { System.IO.File.Delete(FileName); } System.IO.FileStream objFileStream; System.IO.StreamWriter objStreamWriter; string strLine = ""; objFileStream = new System.IO.FileStream(FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write); objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode); for (int i = 0; i < m_DataTable.Columns.Count; i++) { strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题 } objStreamWriter.WriteLine(strLine); strLine = ""; for (int i = 0; i < m_DataTable.Rows.Count; i++) { for (int j = 0; j < m_DataTable.Columns.Count; j++) { if (m_DataTable.Rows[i].ItemArray[j] == null) strLine = strLine + " " + Convert.ToChar(9); //写内容 else { string rowstr = ""; rowstr = m_DataTable.Rows[i].ItemArray[j].ToString(); if (rowstr.IndexOf("\r\n") > 0) rowstr = rowstr.Replace("\r\n", " "); if (rowstr.IndexOf("\t") > 0) rowstr = rowstr.Replace("\t", " "); strLine = strLine + rowstr + Convert.ToChar(9); } } objStreamWriter.WriteLine(strLine); strLine = ""; } objStreamWriter.Close(); objFileStream.Close(); return FileName; //返回生成文件的绝对路径 }二.这种方式速度可能比较慢,但是可以精确定义每行每列数据的样式(在这里行列顺序和Datatable中是相反的,注意一下)
首先要引用命名空间
using Microsoft.Office.Interop.Excel; /// <summary> /// 将数据表保存到Excel表格中 /// </summary> /// <param name="addr">Excel表格存放地址</param> /// <param name="dt">要输出的DataTable</param> public string SaveToExcel(string addr, System.Data.DataTable dt) { //0.注意: // * Excel中形如Cells[x][y]的写法,前面的数字是列,后面的数字是行! // * Excel中的行、列都是从1开始的,而不是0 //1.制作一个新的Excel文档实例 Application xlsApp = new Application(); xlsApp.DisplayAlerts = false; xlsApp.Workbooks.Add(true); //2.设置Excel分页卡标题 xlsApp.ActiveSheet.Name = "明细报表"; //3.合并第一行的单元格 string temp = ""; if (dt.Columns.Count < 26) { temp = ((char)('A' + dt.Columns.Count)).ToString(); } else if (dt.Columns.Count <= 26 + 26 * 26) { temp = ((char)('A' + (dt.Columns.Count - 26) / 26)).ToString() + ((char)('A' + (dt.Columns.Count - 26) % 26)).ToString(); } else throw new Exception("列数过多"); Range range = xlsApp.get_Range("A1", temp + "1"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //4.填写第一行:表名,对应DataTable的TableName xlsApp.Cells[1][1] = "明细报表"; xlsApp.Cells[1][1].Font.Name = "黑体"; xlsApp.Cells[1][1].Font.Size = 25; xlsApp.Cells[1][1].Font.Bold = true; xlsApp.Cells[1][1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 xlsApp.Rows[1].RowHeight = 60; //第一行行高为60(单位:磅) //5.合并第二行单元格,用于书写表格生成日期 range = xlsApp.get_Range("A2", temp + "2"); range.ClearContents(); //清空要合并的区域 range.MergeCells = true; //合并单元格 //6.填写第二行:生成时间 xlsApp.Cells[1][2] = "报表生成于:" + DateTime.Now.ToString(); xlsApp.Cells[1][2].Font.Name = "宋体"; xlsApp.Cells[1][2].Font.Size = 15; //xlsApp.Cells[1][2].HorizontalAlignment = 4;//右对齐 xlsApp.Cells[1][2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 xlsApp.Rows[2].RowHeight = 30; //第一行行高为60(单位:磅) //7.填写各列的标题行。从Datatable中拉出来的列标题为数据库中字段,我们把他改成自己的 xlsApp.Cells[1][3] = ""; xlsApp.Cells[0 + 2][3] = "序号"; xlsApp.Cells[1 + 2][3] = "订单号"; xlsApp.Cells[1 + 2][3].ColumnWidth = 20; xlsApp.Cells[2 + 2][3] = "店铺账号"; xlsApp.Cells[3 + 2][3] = "会员账号"; xlsApp.Cells[4 + 2][3] = "商品编号"; xlsApp.Cells[5 + 2][3] = "商品名称"; xlsApp.Cells[5 + 2][3].ColumnWidth = 20; xlsApp.Cells[6 + 2][3] = "商品规格"; xlsApp.Cells[7 + 2][3] = "型号"; xlsApp.Cells[7 + 2][3].ColumnWidth = 20; xlsApp.Cells[8 + 2][3] = "单价"; xlsApp.Cells[9 + 2][3] = "数量"; xlsApp.Cells[10 + 2][3] = "总价"; xlsApp.Cells[11 + 2][3] = "时间"; xlsApp.Cells[11 + 2][3].ColumnWidth = 45; xlsApp.Rows[3].Font.Name = "宋体"; xlsApp.Rows[3].Font.Size = 13; //设置字号 xlsApp.Rows[3].Font.Bold = true; //粗体 xlsApp.Rows[3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中 range = xlsApp.get_Range("A3", temp + "3"); range.Interior.ColorIndex = 33;//背景颜色 //8.填写DataTable中的数据 for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { switch (j) { case 1: xlsApp.Cells[j + 2][i + 4] = "'" + dt.Rows[i][j]; break; case 2: xlsApp.Cells[j + 2][i + 4] = "'" + dt.Rows[i][j]; break; case 3: xlsApp.Cells[j + 2][i + 4] = "'" + dt.Rows[i][j]; break; case 4: xlsApp.Cells[j + 2][i + 4] = "'" + dt.Rows[i][j]; break; case 7: xlsApp.Cells[j + 2][i + 4] = "'" + dt.Rows[i][j]; break; case 11: xlsApp.Cells[j + 2][i + 4] = "'" + Convert.ToDateTime(dt.Rows[i][j]).ToString("yyyy年MM月dd日 hh时mm分ss秒ffff毫秒"); break; default: xlsApp.Cells[j + 2][i + 4] = dt.Rows[i][j]; break; } } } range = xlsApp.get_Range("A4", temp + (dt.Rows.Count + 3).ToString()); range.Interior.ColorIndex = 2; //修改颜色 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //9.描绘边框 range = xlsApp.get_Range("A1", temp + (dt.Rows.Count + 3).ToString()); range.Borders.LineStyle = 1; range.Borders.Weight = 3; //10.打开制作完毕的表格 //xlsApp.Visible = true; //11.保存表格到根目录下指定名称的文件中 string path = AppDomain.CurrentDomain.BaseDirectory + ("Upload\\Excel\\" + addr + ".xls"); xlsApp.ActiveWorkbook.SaveAs(path); xlsApp.Quit(); xlsApp = null; GC.Collect(); return path; }四.对于Asp.net 服务端生成表格后需要回发送回客户端来下载,那么可能会用到绝对路径转相对路径的方法,下面上代码
/// <summary> /// 绝对路径转换为URL相对路径 /// </summary> /// <param name="imageurl1"></param> /// <returns></returns> private string UrlConvertToR(string imageurl1) { string tmpRootDir = Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString()); //获取程序根目录 string imageurl2 = imageurl1.Replace(tmpRootDir, ""); //转换成相对路径 imageurl2 = imageurl2.Replace(@"\", @"/"); return imageurl2; }五.如果Asp.Net 服务器回发的数据为 Json 格式,那么我们可能需要把 Json 数据转换成 Datatable 对象。下面上代码
/// <summary> /// 将json转换为DataTable /// </summary> /// <param name="strJson">得到的json</param> /// <returns></returns> private System.Data.DataTable JsonToDataTable(string strJson) { //转换json格式 strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString(); //取出表名 var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase); string strName = rg.Match(strJson).Value; System.Data.DataTable tb = null; //去除表名 strJson = strJson.Substring(strJson.IndexOf("[") + 1); strJson = strJson.Substring(0, strJson.IndexOf("]")); //获取数据 rg = new Regex(@"(?<={)[^}]+(?=})"); MatchCollection mc = rg.Matches(strJson); for (int i = 0; i < mc.Count; i++) { string strRow = mc[i].Value; string[] strRows = strRow.Split('*'); //创建表 if (tb == null) { tb = new System.Data.DataTable(); tb.TableName = strName; foreach (string str in strRows) { var dc = new DataColumn(); string[] strCell = str.Split('#'); if (strCell[0].Substring(0, 1) == "\"") { int a = strCell[0].Length; dc.ColumnName = strCell[0].Substring(1, a - 2); } else { dc.ColumnName = strCell[0]; } tb.Columns.Add(dc); } tb.AcceptChanges(); } //增加内容 DataRow dr = tb.NewRow(); for (int r = 0; r < strRows.Length; r++) { dr[r] = strRows[r].Split('#')[1].Trim().Replace(",", ",").Replace(":", ":").Replace("\"", ""); } tb.Rows.Add(dr); tb.AcceptChanges(); } return tb; }六.如果我们我们要处理的数据中包含Html代码,那么我们可能会用到去除Html标签的方法,下面上代码
/// <summary> /// 替换Html标签 /// </summary> /// <param name="html"></param> /// <returns></returns> public string ReplaceHtmlTag(string html) { string strText = System.Text.RegularExpressions.Regex.Replace(html, "<[^>]+>", ""); strText = System.Text.RegularExpressions.Regex.Replace(strText, "&[^;]+;", ""); return strText.Replace("/p>", "").Replace("<", "").Replace("p>", ""); }