把datagridview的数据输出为Excel,Word的二种应用

 

public static void ExportData(DataGridView srcDgv,string fileName)//导出数据,传入一个datagridview和一个文件路径

        {

            string type = fileName.Substring(fileName.IndexOf(".")+1);//获得数据类型

            if (type.Equals("xls",StringComparison.CurrentCultureIgnoreCase))//Excel文档

            {

                Excel.Application excel = new Excel.Application();

                try

                {

                    excel.DisplayAlerts = false;

                    excel.Workbooks.Add(true);

                    excel.Visible = false;

                    for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题

                    {

                        excel.Cells[2, i+1] = srcDgv.Columns[i].HeaderText;

                    }

                    for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据

                    {

                        for (int j = 0; j < srcDgv.Columns.Count; j++)

                        {

                            excel.Cells[i + 3, j + 1] = srcDgv[j, i].Value;

                        }

                    }

                    excel.Workbooks[1].SaveCopyAs(fileName);//保存

                }

                finally

                {

                    excel.Quit();

                }

                return;

            }

            //保存Word文件

            if (type.Equals("doc", StringComparison.CurrentCultureIgnoreCase))

            {

                object path = fileName;

                Object none=System.Reflection.Missing.Value;

                Word.Application wordApp = new Word.Application();

                Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none);

                //建立表格

                Word.Table table= document.Tables.Add(document.Paragraphs.Last.Range, srcDgv.Rows.Count+1, srcDgv.Columns.Count, ref none, ref none);

                try

                {

                    

                    for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题

                    {

                        table.Cell(1, i + 1).Range.Text = srcDgv.Columns[i].HeaderText;

                    }

                    for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据

                    {

                        for (int j = 0; j < srcDgv.Columns.Count; j++)

                        {

                            

                            table.Cell(i + 2, j + 1).Range.Text = srcDgv[j, i].Value.ToString();

                        }

                    }

                    document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none);

                    document.Close(ref none, ref none, ref none);

                }

                finally

                {

                    wordApp.Quit(ref none, ref none, ref none);

                }

            }

        }

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