目前個人認為比較好用，比較簡單的生成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>", "");
}
![C#基礎——與C#的第一次邂逅聲明：IDE：第一個C#果實：Console淺析：總結：[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)