/// <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 ;
}
}
/// 将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 ;
}
}