方法一：(拷貝直接可以使用，適合大批量資料, 上萬筆)
Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();
SaveFileDialog savefiledialog = new SaveFileDialog();
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;
//設置對象不可見
appexcel.Visible = false;
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();
System.Windows.Forms.Application.DoEvents();
}
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;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrang);
xlrang = null;
//調用方法關閉excel進程
appexcel.Visible = true;
方法二：(自己建函數，適合大批量資料, 上萬筆)
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文件
www.2cto.com
方法三:(可以自己調整單元格的格式,適合小批量的數量)
try
{
//沒有數據的話就不往下執行
if (dataGridView1.Rows.Count == 0)
return;
//實例化一個Excel.Application對象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//讓後台執行設置為不可見，為true的話會看到打開一個Excel，然後數據在往裡寫
//excel.Visible = false;
excel.Visible = true;
//新增加一個工作簿，Workbook是直接保存，不會彈出保存對話框，加上Application會彈出保存對話框，值為false會報錯
excel.Application.Workbooks.Add(true);
//生成Excel中列頭名稱
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//把DataGridView當前頁的數據保存在Excel中
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
//設置禁止彈出保存和覆蓋的詢問提示框
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
////保存工作簿
//excel.Application.Workbooks.Add(true).Save();
////保存excel文件
//excel.Save("D:" + "\\KKHMD.xls");
////確保Excel進程關閉
//excel.Quit(); //可以直接打開文件
//excel = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤提示");
}
Excel.output((DataTable)dataGridView1.DataSource);
摘自 brian0031的專欄
