///<summary> ///将DataSet里所有数据导入Excel. ///需要添加COM:MicrosoftExcelObjectLibrary. ///usingExcel; ///</summary> ///<paramname="filePath"></param> ///<paramname="ds"></param> privatevoidExportToExcel(stringfilePath,DataSetds) { objectoMissing=System.Reflection.Missing.Value; Excel.ApplicationClassxlApp=newExcel.ApplicationClass(); try { //打开EXCEL文件 Excel.WorkbookxlWorkbook=xlApp.Workbooks.Open(filePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); //Excel.WorkbookxlWorkbook=xlApp.Workbooks.只有Open属性,没有Write属性 Excel.WorksheetxlWorksheet; //循环所有DataTable for(inti=0;i<ds.Tables.Count;i++) { //添加入一个新的Sheel页 xlWorksheet=(Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing); //以TableName作为新加的sheel页名 xlWorksheet.Name=ds.Tables[i].TableName; //取出这个DataTable中的所有值,暂时存于stringBuffer中 stringstringBuffer=""; //for(intm=0;m<ds.Tab for(intj=0;j<ds.Tables[i].Rows.Count;j++) { for(intk=0;k<ds.Tables[i].Columns.Count;k++) { stringBuffer+=ds.Tables[i].Rows[j][k].ToString(); if(k<ds.Tables[i].Columns.Count-1) stringBuffer+="/t"; } stringBuffer+="/n"; } //利用系统剪贴板 System.Windows.Forms.Clipboard.SetDataObject(""); //将stringBuffer放入剪贴板 System.Windows.Forms.Clipboard.SetDataObject(stringBuffer); //选中这个sheel页中的第一个单元格 ((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; } catch(Exceptionex) { MessageBox.Show(ex.Message); } finally { //释放... xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp=null; GC.Collect(); } } } |
DataSet数据导入Excel的c#代码
最新推荐文章于 2024-05-08 23:52:14 发布