C# 讲解五种导出access数据到Excel文件格式中

1.首先声明，这些方法也都是本人搜集的资料，然后为已所用，程序中不足之处，还请高手指点. 这些方法都没有关闭Excel进程。 2.网上有好多关于用SQL语句导入导出的例子，这里不再重复写了。 方法1：调用com组件,导出access数据到Excel,就是直接调用access的导出功能,此方法速度超级快 using Access; Access.ApplicationClass oAccess = new Access.ApplicationClass(); oAccess.Visible = false; try {        //ACCESS9:       oAccess.OpenCurrentDatabase("d:\\wcf.mdb",false,"");       //导出到excel oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Acce      ss.AcSpreadSheetType.acSpreadsheetTypeExcel9,"工作表名","d:\\wcf.xls",true,null,null);      //导入txt //oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim,"","Enterprise","d:\\wcf.txt",true,"",0); oAccess.CloseCurrentDatabase(); oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone ); System.Runtime.InteropServices.Marshal.ReleaseComObject (oAccess); oAccess = null; MessageBox.Show("导入成功"); } catch(Exception ex) { MessageBox.Show(ex.ToString()); } finally { GC.Collect(); }   方法2：此方法速度也是超级快，只不过导出的格式非标准的Excel格式，默认工作表名与文件名相同 string FileName="d:\\abc.xls"; System.Data.DataTable dt=new System.Data.DataTable(); FileStream objFileStream; StreamWriter objStreamWriter; string strLine=""; objFileStream = new FileStream(FileName,FileMode.OpenOrCreate,FileAccess.Write); objStreamWriter = new StreamWriter(objFileStream,System.Text.Encoding.Unicode); for(int i=0;i<dt.Columns.Count ;i ) { strLine=strLine dt.Columns[i].ColumnName.ToString() Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine=""; for(int i=0;i<dt.Rows.Count;i ) { strLine=strLine (i 1) Convert.ToChar(9); for(int j=1;j<dt.Columns.Count;j ) { strLine=strLine dt.Rows[i][j].ToString() Convert.ToChar(9); } objStreamWriter.WriteLine(strLine); strLine=""; } objStreamWriter.Close(); objFileStream.Close ();   方法3：用Ado.net 此方法速度较以上两个显得慢了一些，数据量越大越明显 int Id=0; string Name="测试"; string FileName="d:\\abc.xls"; System.Data.DataTable dt=new System.Data.DataTable(); long totalCount=dt.Rows.Count ; long rowRead=0; float percent=0; OleDbParameter[] parm=new OleDbParameter[dt.Columns.Count]; string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" FileName ";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); OleDbCommand objCmd = new OleDbCommand(); objCmd.Connection = objConn; objConn.Open(); //建立表结构 objCmd.CommandText = @"CREATE TABLE Sheet1(序号 Integer,名称 varchar)"; objCmd.ExecuteNonQuery(); //建立插入动作的Command objCmd.CommandText = "INSERT INTO Sheet1(" Id "," Name ")"; parm[0]=new OleDbParameter("@Id", OleDbType.Integer); objCmd.Parameters.Add (parm[0]); parm[1]=new OleDbParameter("@Company", OleDbType.VarChar); objCmd.Parameters.Add(parm[1]); //遍历DataTable将数据插入新建的Excel文件中 for(int i=0;i<dt.Rows.Count;i ) {  parm[0].Value=i 1; for(int j=1;j<parm.Length;j ) { parm[j].Value =dt.Rows[i][j]; } objCmd.ExecuteNonQuery(); rowRead ; percent=((float)(100*rowRead))/totalCount;  //this.FM.CaptionText.Text = "正在导出数据，已导出[" percent.ToString("0.00") "%]..."; if(i==dt.Rows.Count-1) //this.FM.CaptionText.Text = "请稍后......"; System.Windows.Forms .Application.DoEvents(); } objConn.Close(); //this.FM.CaptionText.Text = "";   方法4：此方法调用com组件，速度都慢于以上3个方法 using Excel; System.Data.DataTable dt=new System.Data.DataTable(); string FileName="d:\\abc.xls"; long totalCount=dt.Rows.Count; long rowRead=0; float percent=0; Excel.Application xlApp=null; xlApp=new Excel.Application(); Excel.Workbooks workbooks=xlApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet )workbook.Worksheets[1]; //取得sheet1 Excel.Range range; //写入字段 for(int i=0;i<dt.Columns.Count;i ) { worksheet.Cells[1,i 1]=dt.Columns[i].ColumnName; range=(Excel.Range)worksheet.Cells[1,i 1]; } for(int r=0;r<dt.Rows.Count;r ) { worksheet.Cells[r 2,1]=r 1; for(int i=0;i<dt.Columns.Count;i ) { //worksheet.Cells[r 2,i 1]=dt.Rows[r][i]; if(i 1!=dt.Columns.Count) worksheet.Cells[r 2,i 2]= dt.Rows[r][i 1]; } rowRead ; percent=((float)(100*rowRead))/totalCount;  //this.FM.CaptionText.Text = "正在导出数据，已导出[" percent.ToString("0.00") "%]..."; System.Windows.Forms .Application.DoEvents(); } range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count 2,dt.Columns.Count]); workbook.Saved =true; workbook.SaveCopyAs(FileName); //this.FM.CaptionText.Text = "";   方法5：利用剪贴板 ,有人说此方法很快，但是我用时，这种方法最慢，请高手指点. System.Data.DataTable dt=new System.Data.DataTable(); string filePath=@"d:\abc.xls"; object oMissing = System.Reflection.Missing.Value; Excel.ApplicationClass xlApp = new Excel.ApplicationClass(); try { xlApp.Visible = false; xlApp.DisplayAlerts = false; Excel.Workbooks oBooks = xlApp.Workbooks; Excel._Workbook xlWorkbook = null; xlWorkbook = oBooks.Open(filePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing, oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); Excel.Worksheet xlWorksheet; // 添加入一个新的Sheet页。 xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing); // 以TableName作为新加的Sheet页名。 xlWorksheet.Name ="企业名录"; // 取出这个DataTable中的所有值,暂存于stringBuffer中。 string stringBuffer = ""; for( int j=0; j<dt.Rows.Count; j ) { for( int k=0; k<dt.Columns.Count; k ) { stringBuffer = dt.Rows[j][k].ToString(); if( k < dt.Columns.Count - 1 ) stringBuffer = "\t"; } stringBuffer = " "; } // 利用系统剪切板 System.Windows.Forms.Clipboard.SetDataObject(""); // 将stringBuffer放入剪切板。 System.Windows.Forms.Clipboard.SetDataObject(stringBuffer); // 选中这个sheet页中的第一个单元格 ((Excel.Range)xlWorksheet.Cells[1,1]).Select(); // 粘贴！ xlWorksheet.Paste(oMissing,oMissing); // 清空系统剪切板。 System.Windows.Forms.Clipboard.SetDataObject(""); // 保存并关闭这个工作簿。 xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges , oMissing, oMissing ); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook); xlWorkbook = null;

 

 

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace Property_management
{
class Export
{
private static System.Windows.Forms.DataGridView gridView;
private static System.Windows.Forms.ProgressBar toolStripProgressBar1;
private static Timer time;
private static DataSet objSet = new DataSet();
private static SaveFileDialog saveFileDialog = new SaveFileDialog();
private static SaveFileDialog saveFileDialog2 = new SaveFileDialog();

public static System.Windows.Forms.DataGridView _gridView
{
get { return gridView; }
set { gridView = value; }
}
public static System.Windows.Forms.ProgressBar _toolStripProgressBar1
{
get { return toolStripProgressBar1; }
set { toolStripProgressBar1 = value; }
}
public static Timer _time
{
get { return time; }
set { time = value; }
}
public static DataSet _objSet
{
get { return objSet; }
set { objSet.Clear(); objSet = value; }
}

public static void ExportTOExcel2()
{
if (gridView.Rows.Count == 0)
{
MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
else
{
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
toolStripProgressBar1.Visible = true;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true); ;
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";

//生成字段名称
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText.ToString();
}
//填充数据
for (int i = 0; i < gridView.RowCount; i++)
{
for (int j = 0; j < gridView.ColumnCount; j++)
{
if (gridView[j, i].Value.GetType() == typeof(string))
{
excel.Cells[i + 2, j + 1] ="'"+gridView[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();


}
}
toolStripProgressBar1.Value += 100 / gridView.RowCount;
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已经成功导出!!");
toolStripProgressBar1.Value = 0;
toolStripProgressBar1.Visible = false;

System.Diagnostics.Process.Start(strName);
}
}
}


}
}

我把功能放在这个类里了
需要添加一个引用
右边的资源管理器右键单击“引用”，选择添加引用-》com，找到microsoft excel 11 那个，添加进去
如果有个黄色的叹号，建议重装一次office

下面是如何使用
Export._gridView = GV;//这是指定DatagridView
Export._toolStripProgressBar1 = PB;//如果有滚动条的话加上,没有不设定也行
Export._objSet = this.propertyDataSet5.Fee_already.DataSet;//指定你的DatagridView使用的dataset
Export.ExportTOExcel2();//调用函数导出