DATATABLE,DATAGRIDVIEW导入EXCEL中

来源：互联网发布：韶关市仁化县网络问政编辑：程序博客网时间：2024/06/06 02:49

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Core;
using System.Data;
using Microsoft;

namespace Recon.Log
{
    class GridViewToExcel
    {
        /// <summary>
        /// 把一个内在表的数据导入到EXCEL中去
        /// </summary>
        /// <param name="ReportTitle"></param>
        /// <param name="myTable"></param>
        public static void ExportDataGridToExcel(string ReportTitle, DataTable myTable)
        {
            try
            {
                 Excel.Application xlApp = new Excel.ApplicationClass();

int rowIndex;
int colIndex;

rowIndex = 2;
colIndex = 0;

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

                if (myTable.Rows.Count > 0)
                {
                   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;

                    foreach (DataColumn colu in myTable.Columns)
                    {
                        colIndex = colIndex + 1;
                        xlApp.Cells[2, colIndex] = colu.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] = myTable.Rows[row][col].ToString().Trim();
                        }
                    }
                }
                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] = myTable.Rows[row][col].ToString().Trim();
                        }
                    }
                }
                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;
            }

        }

        /// <summary>
        /// 把DataGridView中的数据导入到EXCEL中,如果DataGridView具有分页的话,只能导入当前页面记录到期          EXCEL中
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="ReportTitle"></param>
        /// <param name="myTable"></param>
        public static void ExportDataGridToExcel(DataGridView grid, string ReportTitle, DataTable myTable)
        {
            try
            {
                Excel.Application xlApp = new Excel.ApplicationClass();

int rowIndex;
int colIndex;

rowIndex = 2;
colIndex = 0;

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

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

                    foreach (DataGridViewColumn colu in grid.Columns)
                    {
                        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.Columns.Count; col++)
                        {
                            colIndex = colIndex + 1;
                            xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value.ToString().Trim();
                        }
                    }
                }
                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.Rows[row].Cells[col].Value.ToString().Trim();
                        }
                    }
                }
                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;
            }

}
}

}