excel.cs

用ADO.net查询数据库

#region 代码1
//快速导入Excel 
  private void button3_Click(object sender, EventArgs e) 
        { 
            if (this.datagridview1.RowCount < 1) 
            { 
                MessageBox.Show("没有可以导出的数据！"); 
                return; 
            } 

            Microsoft.Office.Interop.Excel.Application appExcel; 

            appExcel = new Microsoft.Office.Interop.Excel.Application(); 
            Workbook workbookData; 
            Worksheet worksheetData; 
            Range rangedata; 

            appExcel.Visible = true; 

            // set culture to US 
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; 
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); 


            workbookData = appExcel.Workbooks.Add(Missing.Value); 
            worksheetData = (Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); 
            worksheetData.Name = "Saved"; 


            for (int i = 0; i < this.datagridview1.Columns.Count; i++) 
            { 
                worksheetData.Cells[1, i + 1] = this.datagridview1.Columns[i].HeaderText; 
            } 

            rangedata = worksheetData.get_Range("A2", Missing.Value); 

            Range xlRang = null; 
            int iRowCount = this.datagridview1.RowCount - 1; 
            int iParstedRow = 0, iCurrSize = 0; 
            int iEachSize = 1000;  // each time you 
            int iColumnAccount = this.datagridview1.Columns.Count; 
            object[,] objVal = new object[iEachSize, iColumnAccount]; 
            try 
            { 
                iCurrSize = iEachSize; 
                while (iParstedRow < iRowCount) 
                { 
                    if ((iRowCount - iParstedRow) < iEachSize) 
                        iCurrSize = iRowCount - iParstedRow; 
                    for (int i = 0; i < iCurrSize; i++) 
                    { 
                        for (int j = 0; j < iColumnAccount; j++) 
                            objVal[i, j] = this.datagridview1.Rows[i].Cells[j].Value.ToString(); 
                    } 
                    // Get Save Range from Excel WorkSheet 
                    // such as  A1 H10, means From A to H Columns, and 1 to 10 rows 
                    xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString()); 

                    xlRang.Value2 = objVal; 

                    iParstedRow = iParstedRow + iCurrSize; 
                } 

              
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang); 
                xlRang = null; 

            } 
            catch (Exception ex) 
            { 
                MessageBox.Show(ex.Message); 
                return; 
            } 

            // return to previous culture 
            System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; 

        } 

	#endregion

#region 代码2
 using Excel;
  /// <summary>
  /// 将DataSet里所有数据导入Excel.
  /// 需要添加COM: Microsoft Excel Object Library
  ///引用Excel
  /// </summary>
  /// <param name="filePath"></param>
  /// <param name="ds"></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
    for( int 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中。
     string stringBuffer = "";
     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);
     // 选中这个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;
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    // 释放...
    xlApp.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
    xlApp = null;
    GC.Collect();
   }
  } 

	#endregion
#region 代码3
  public class ImportExportToExcel
  2     {
  3         private string strConn ;
  4         
  5         private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
  6         private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
  7         
  8         public ImportExportToExcel()
  9         {
 10             //
 11             // TODO: 在此处添加构造函数逻辑
 12             //
 13             this.openFileDlg.DefaultExt = "xls";
 14             this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
 15 
 16             this.saveFileDlg.DefaultExt="xls";
 17             this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
 18 
 19         }
 20 
 21         从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
 22         //        /// <summary>
 23         //        /// 从Excel导入文件
 24         //        /// </summary>
 25         //        /// <param name="strExcelFileName">Excel文件名</param>
 26         //        /// <returns>返回DataSet</returns>
 27         //        public DataSet ImportFromExcel(string strExcelFileName)
 28         //        {
 29         //            return doImport(strExcelFileName);
 30         //        }
 31         /**//// <summary>
 32         /// 从选择的Excel文件导入
 33         /// </summary>
 34         /// <returns>DataSet</returns>
 35         public DataSet ImportFromExcel()
 36         {
 37             DataSet ds=new DataSet();
 38             if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
 39                 ds=doImport(openFileDlg.FileName);
 40             return ds;
 41         }
 42         /**//// <summary>
 43         /// 从指定的Excel文件导入
 44         /// </summary>
 45         /// <param name="strFileName">Excel文件名</param>
 46         /// <returns></returns>
 47         public DataSet ImportFromExcel(string strFileName)
 48         {
 49             DataSet ds=new DataSet();
 50             ds=doImport(strFileName);
 51             return ds;
 52         }
 53         /**//// <summary>
 54         /// 执行导入
 55         /// </summary>
 56         /// <param name="strFileName">文件名</param>
 57         /// <returns>DataSet</returns>
 58         private DataSet doImport(string strFileName)
 59         {
 60             if (strFileName=="") return null;
 61               
 62             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 63                 "Data Source=" +  strFileName + ";" +
 64                 "Extended Properties=Excel 8.0;";
 65             OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
 66 
 67             DataSet ExcelDs = new DataSet();
 68             try
 69             {
 70                 ExcelDA.Fill(ExcelDs, "ExcelInfo");
 71                 
 72             }
 73             catch(Exception err)
 74             {
 75                 System.Console.WriteLine( err.ToString() );
 76             }
 77             return ExcelDs;
 78             
 79             
 80         
 81         }
 82         #endregion
 83 
 84         从DataSet到出到Excel#region 从DataSet到出到Excel
 85         /**//// <summary>
 86         /// 导出指定的Excel文件
 87         /// </summary>
 88         /// <param name="ds">要导出的DataSet</param>
 89         /// <param name="strExcelFileName">要导出的Excel文件名</param>
 90         public void ExportToExcel(DataSet ds,string strExcelFileName)
 91         {
 92             if (ds.Tables.Count==0 || strExcelFileName=="") return;
 93             doExport(ds,strExcelFileName);
 94     
 95 
 96         }
 97         /**//// <summary>
 98         /// 导出用户选择的Excel文件
 99         /// </summary>
100         /// <param name="ds">DataSet</param>
101         public void ExportToExcel(DataSet ds)
102         {
103             if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
104                 doExport(ds,saveFileDlg.FileName);
105             
106         }
107         /**//// <summary>
108         /// 执行导出
109         /// </summary>
110         /// <param name="ds">要导出的DataSet</param>
111         /// <param name="strExcelFileName">要导出的文件名</param>
112         private void doExport(DataSet ds,string strExcelFileName)
113         {
114             
115             Excel.Application excel= new Excel.Application();
116             
117             //            Excel.Workbook obj=new Excel.WorkbookClass();
118             //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
119 
120             int rowIndex=1;
121             int colIndex=0;
122 
123             excel.Application.Workbooks.Add(true);
124             
125     
126             System.Data.DataTable table=ds.Tables[0] ;
127             foreach(DataColumn col in table.Columns)
128             {