怎么由DataSet将数据导入Excel?

         /// <summary>
        
/// 将DataSet里所有数据导入Excel
        
/// 需要添加COM: Microsoft Excel Object Library
        
/// using Excel;
        
/// </summary>
        
/// <param name="filePath">文件路径</param>
        
/// <param name="ds">需要转存的DataSet</param>

         public   static   void  ExportToExcel( string  filePath, DataSet ds)
        
{
            
object oMissing = System.Reflection.Missing.Value;
            Excel.ApplicationClass xlApp 
= new Excel.ApplicationClass();
            
try
            
{
                
// 打开Excel文件。以下为Office 2000.
                Excel.Workbook xlWorkbook =
                    xlApp.Workbooks.Open(filePath, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing, oMissing, oMissing,
                                          oMissing);
 
                Excel.Worksheet xlWorksheet;
                
// 循环所有DataTable
                forint i=0; i<ds.Tables.Count; i++ )
                
{
                    
// 添加入一个新的Sheet页
                    xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
 
                    
// 以TableName作为新加的Sheet页名
                    xlWorksheet.Name = ds.Tables[i].TableName;
 
                    
// 取出这个DataTable中的所有值,暂存于stringBuffer中
                    System.Text.StringBuilder stringBuffer = new System.Text.StringBuilder();
 
                    
forint j=0; j<ds.Tables[i].Rows.Count; j++ )
                    
{
                        
forint k=0; k<ds.Tables[i].Columns.Count; k++ )
                        
{
                                stringBuffer.Append((string)ds.Tables[i].Rows[j][k]);
                            
if( k < ds.Tables[i].Columns.Count - 1 )
                                stringBuffer.Append(
"/t");
                        }

                        stringBuffer.Append("/n")
;
                    }

                    
// 利用系统剪切板
                    System.Windows.Forms.Clipboard.SetDataObject("");
 
                    
// 将stringBuffer放入剪切板
                    System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
 
                    
// 选中这个sheet页中的第一个单元格(下标从1开始)
                    ((Excel.Range)xlWorksheet.Cells[1,1]).Select();
 
                    
// 将剪切板里的内容粘贴在Sheet中
                    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
            
{
                
// 释放COM对象
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                GC.Collect();
            }

        }
  • 0
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 8
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值