/// <summary> /// 将DataSet里所有数据导入Excel. /// 需要添加COM: Microsoft Excel Object Library. /// using Excel; /// </summary> /// <param name="filePath"></param> /// <param name="ds"></param> private void ExportToExcel(string filePath, DataSet ds) { object oMissing = System.Reflection.Missing.Value; Excel.ApplicationClass xlApp = new Excel.ApplicationClass(); try { //打开EXCEL文件 Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,oMissing,oMissing); // Excel.Workbook xlWorkbook=xlApp.Workbooks.只有Open属性,没有Write属性 Excel.Worksheet xlWorksheet; //循环所有DataTable for (int i = 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中 string stringBuffer = ""; // for(int m=0;m<ds.Tab for (int j = 0; j < ds.Tables[i].Rows.Count; j++) { for (int k = 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 (Exception ex) { MessageBox.Show(ex.Message); } finally { //释放... xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; GC.Collect(); } } } |
DataSet数据导入Excel的c#代码
最新推荐文章于 2024-08-09 14:22:03 发布