WinForm DataGrid>>Excel

using office    =Microsoft.Office .Interop ;

  /**/ /// <summary>
        /// 将DataGrid中的数据导入Excel中,并显示Excel应用程序，
        /// 注意调用该方法必须有安装Excel 2000应用程序,并且假定DataGrid中绑定的是一DataSet
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="ReportTitle"></param>
         public   static   void  ExportDataGridToExcel(DataGrid grid, string  ReportTitle)
         ... {
            DataTable myTable = ((DataSet)grid.DataSource).Tables[0];

            try
            ...{
                Excel.Application xlApp = new Excel.ApplicationClass();

                int rowIndex;
                int colIndex;

                rowIndex = 2;
                colIndex = 0;

                Excel.Workbook xlBook =xlApp.Workbooks.Add(true);               

                if (grid.TableStyles.Count >0 ) 
                ...{
                    Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]);
                    range.MergeCells = true;
                    xlApp.ActiveCell.FormulaR1C1  = ReportTitle;
                    xlApp.ActiveCell.Font.Size  = 18;
                    xlApp.ActiveCell.Font.Bold = true; 

                    foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
                    ...{
                        colIndex=colIndex +1;
                        xlApp.Cells[2,colIndex] = colu.HeaderText ;
                    }

                    //得到的表所有行,赋值给单元格
                    for (int row = 0;row < myTable.Rows.Count;row++)
                    ...{
                        rowIndex = rowIndex + 1;
                        colIndex = 0;
                        for (int col=0;col<grid.TableStyles[0].GridColumnStyles.Count;col++)
                        ...{
                            colIndex = colIndex + 1;
                            xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
                        }                    
                    }
                }
                else
                ...{ 
                    Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,myTable.Columns.Count]);
                    range.MergeCells = true;
                    xlApp.ActiveCell.FormulaR1C1  = ReportTitle;
                    xlApp.ActiveCell.Font.Size  = 18;
                    xlApp.ActiveCell.Font.Bold = true;
                    
                    //将表中的栏位名称填到Excel的第一行
                    foreach(DataColumn Col in myTable.Columns)
                    ...{
                        colIndex = colIndex + 1;
                        xlApp.Cells[2, colIndex] = Col.ColumnName;                
                    }

                    //得到的表所有行,赋值给单元格
                    for (int row = 0;row < myTable.Rows.Count;row++)
                    ...{
                        rowIndex = rowIndex + 1;
                        colIndex = 0;
                        for (int col=0;col<myTable.Columns.Count;col++)
                        ...{
                            colIndex = colIndex + 1;
                            xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
                        }                    
                    }
                } 
            
                xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
                xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;          

                xlApp.Cells.EntireColumn.AutoFit();
                xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter ;
                xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter ;        

                xlApp.Visible = true;            
            }
            catch(Exception e)
            ...{
                throw e;
            }
            


        }

/**/ ///
// 仅仅导出看到的数据
            myTable     =  DT.DefaultView.Table .Clone ();

             for    ( int    i = 0 ;   i    <     DT.DefaultView.Count;   i ++ )  
             ... {  
                myTable.ImportRow( DT.DefaultView[i].Row);  
            }    

 

格式化文本框

... #region
        public  void ExportDataGridToExcel(DataGrid grid )
        ...{
            System.Data.DataTable myTable = new DataTable ();

            //仅仅导出看到的数据
            myTable    = DT.DefaultView.Table .Clone ();

            for   (int   i=0;   i   <    DT.DefaultView.Count;   i++)  
            ...{  
                myTable.ImportRow( DT.DefaultView[i].Row);  
            }   
                
 
            /**////
            try
            ...{
                office.Excel.Application xlApp = new   office.Excel.Application ();

                int rowIndex;
                int colIndex;

                rowIndex = 1;
                colIndex = 0;

                office.Excel.Workbook xlBook =xlApp.Workbooks.Add(true);               

                if (grid.TableStyles.Count >0 ) 
                ...{
                    //写标题
//                    office.Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]);
//                    range.MergeCells = true;

//                    xlApp.ActiveCell.FormulaR1C1  = ReportTitle;
//                    xlApp.ActiveCell.Font.Name  ="ＭＳ Ｐゴシック";
//                    xlApp.ActiveCell.Font.Size  = 18;
//                    xlApp.ActiveCell.Font.Bold = true; 

                    foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
                    ...{
                        if( colu.Width>0 )
                        ...{
                            colIndex=colIndex +1;
                            xlApp.Cells[1,colIndex] = colu.HeaderText ;
                            xlApp.Cells.Font.Name  ="ＭＳ Ｐゴシック";
                            xlApp.Cells.Font.Size  =10;
                            
                        }
                    
                    }

                    //得到的表所有行,赋值给单元格
                    for (int row = 0;row < myTable.Rows.Count;row++)
                    ...{
                        
                            rowIndex = rowIndex + 1;
                            colIndex = 0;
                            int    colCount    =    grid.TableStyles[0].GridColumnStyles.Count;
                            for (int col=0;col<colCount;col++)
                            ...{
                                if(grid.TableStyles[0].GridColumnStyles[col].HeaderText =="JobID.")
                                    ...{

                                        string    CellA    =    Convert.ToChar (    col    +64)+rowIndex.ToString();
                                        string    CellB    =    Convert.ToChar (    col    +64)+rowIndex.ToString();
                                        office.Excel.Range  ra = xlApp.get_Range ( CellA,CellB);
                                                                                     
                                        ra.NumberFormatLocal = "@"; 
 
                                     }

                                if(  grid.TableStyles[0].GridColumnStyles[col].Width>0)

                                ...{
                                    colIndex = colIndex + 1;

                                    
                                    
                                    xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString(); 

                                
                                    xlApp.Cells.Font.Name  ="ＭＳ Ｐゴシック";
                                    xlApp.Cells.Font.Size  =10;
                                }
                            
                            }     
                        
                    }
                }

            
                xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colIndex]).Font.Bold = true;
                xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;          

                xlApp.Cells.EntireColumn.AutoFit();
                xlApp.Cells.VerticalAlignment =office. Excel.Constants.xlCenter ;
                xlApp.Cells.HorizontalAlignment = office.Excel.Constants.xlCenter ;        

                xlApp.Visible = true;            
            }
            catch(Exception e)
            ...{
                throw e;
            }
            


        }
 
        #endregion