怎么由DataSet将数据导入Excel?

/// <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();
            }
        }

}

  • 0
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值