方法一: 1.首先声明,这些方法也都是本人搜集的资料,然后为已所用,程序中不足之处,还请高手指点. 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,Access.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 += "\n"; } // 利用系统剪切板 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;
这些方法都没有关闭Excel进程,这种资料很多,在此不多写了,希望这些能对一些人带来方便.
方法二: private void btnExport_Click(object sender, System.EventArgs e) { if(this.savFileExcel.ShowDialog()==DialogResult.OK) { string zgh=""; string xm=""; string xbmc=""; string yhid=""; string FileName=savFileExcel.FileName; DataTable dt=new 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(职工号 varchar,姓名 varchar,性别 varchar,用户名 varchar)"; objCmd.ExecuteNonQuery(); //建立插入动作的Command try { objCmd.CommandText = "insert into Sheet1("+zgh+","+xm+","+xbmc+","+yhid+")"; parm[0]=new OleDbParameter("@zgh", OleDbType.VarChar); objCmd.Parameters.Add(parm[0]); parm[1]=new OleDbParameter("@xm", OleDbType.VarChar); objCmd.Parameters.Add(parm[1]); parm[2]=new OleDbParameter("@xbmc",OleDbType.VarChar); objCmd.Parameters.Add(parm[2]); parm[3]=new OleDbParameter("@yhid",OleDbType.VarChar); objCmd.Parameters.Add(parm[3]); } catch(Exception ex) { MessageBox.Show(ex.ToString()); } //遍历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 = ""; }
方法三:
public void ToExcel(System.Web.UI.Control ctl) { HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls"); HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); } |