C#中DataTabel导出excel(三种方法)
(拷贝直接可以使用，适合大批量资料, 上万笔)
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt"></param>
public void SaveExcel(System.Data.DataTable dt)
{
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
System.Reflection.Missing miss = System.Reflection.Missing.Value;
appexcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookdata;
Microsoft.Office.Interop.Excel.Worksheet worksheetdata;
Microsoft.Office.Interop.Excel.Range rangedata;
//设置对象不可见
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookdata = appexcel.Workbooks.Add(miss);
worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);
//给工作表赋名称
worksheetdata.Name = "saved";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetdata.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangedata = worksheetdata.get_Range("a2", miss);
Microsoft.Office.Interop.Excel.Range xlrang = null;
//irowcount为实际行数，最大行
int irowcount = dt.Rows.Count;
int iparstedrow = 0, icurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int ieachsize = 1000;
//icolumnaccount为实际列数，最大列数
int icolumnaccount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[ieachsize, icolumnaccount];
icurrsize = ieachsize;
while (iparstedrow < irowcount)
{
if ((irowcount - iparstedrow) < ieachsize)
icurrsize = irowcount - iparstedrow;
//用for循环给数组赋值
for (int i = 0; i < icurrsize; i++)
{
for (int j = 0; j < icolumnaccount; j++)
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString();
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (icolumnaccount <= 26)
{
col = ((char)('A' + icolumnaccount - 1)).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (icolumnaccount / 26 - 1))).ToString() + ((char)('A' + (icolumnaccount % 26 - 1))).ToString() + ((int)(iparstedrow + icurrsize + 1)).ToString();
}
xlrang = worksheetdata.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
xlrang.Value2 = objval;
iparstedrow = iparstedrow + icurrsize;
}
appexcel.DisplayAlerts = true;
appexcel.AlertBeforeOverwriting = true;
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//调用方法关闭excel进程
}
方法二：(自己建函数，适合大批量资料, 上万笔)
using System.IO;
private void dataTableToCsv(DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
//FileStream fs1 = File.Open(file, FileMode.Open, FileAccess.Read);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = 0; i < table.Columns.Count; i++)
{
title += table.Columns[i].ColumnName + "\t"; //栏位：自动跳到下一单元格
}
title = title.Substring(0, title.Length - 1) + "\n";
sw.Write(title);
foreach (DataRow row in table.Rows)
{
string line = "";
for (int i = 0; i < table.Columns.Count; i++)
{
line += row[i].ToString().Trim() + "\t"; //内容：自动跳到下一单元格
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
dataTableToCsv(dt, @"c:\1.xls"); //调用函数
System.Diagnostics.Process.Start(@"c:\1.xls"); //打开excel文件
方法三：适合小批量数据
public bool DoExport(System.Data.DataTable dt)
{
Application app = new ApplicationClass();
if (app == null)
{
throw new Exception("Excel无法启动");
}
app.Visible = true;
Workbooks wbs = app.Workbooks;
Workbook wb = wbs.Add(Missing.Value);
Worksheet ws = (Worksheet)wb.Worksheets[1];
int cnt = dt.Rows.Count;
int columncnt = dt.Columns.Count;
// *****************获取数据********************
object[,] objData = new Object[cnt + 1, columncnt]; // 创建缓存数据
// 获取列标题
for (int i = 0; i < columncnt; i++)
{
objData[0, i] = dt.Columns[i].ColumnName;
}
// 获取具体数据
for (int i = 0; i < cnt; i++)
{
System.Data.DataRow dr = dt.Rows[i];
for (int j = 0; j < columncnt; j++)
{ objData[i + 1, j] = dr[j]; }
}
//********************* 写入Excel******************
Range r = ws.get_Range(app.Cells[1, 1], app.Cells[cnt + 1, columncnt]);
r.NumberFormat = "@";
//r = r.get_Resize(cnt+1, columncnt);
r.Value2 = objData;
r.EntireColumn.AutoFit();
app = null;
return true;
}
///方法四：使用IO写入Excel
private void dataTableToCsv(System.Data.DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
//FileStream fs1 = File.Open(file, FileMode.Open, FileAccess.Read);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = 0; i < table.Columns.Count; i++)
{
title += table.Columns[i].ColumnName + "\t"; //栏位：自动跳到下一单元格
}
title = title.Substring(0, title.Length - 1) + "\n";
sw.Write(title);
foreach (System.Data.DataRow row in table.Rows)
{
string line = "";
for (int i = 0; i < table.Columns.Count; i++)
{
line += row[i].ToString().Trim() + "\t"; //内容：自动跳到下一单元格
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}
