关于C#,ASP.NET 生成Excel表格的两种方式（Datatable转Excel）

个人认为比较好用，比较简单的生成Excel表格的方式有两种，一种为直接写方法，一种为引用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;
}
原文地址：https://blog.csdn.net/u013542549/article/details/71173184