万能导出数据到Excel

?

class CommanPrint

   {

        /// <summary>

        /// 导出Excel

 

         /// 版权所有 : 光之翼 QQ：272912705         www.hi.baidu.com/jcomet

 

        /// </summary>

        /// <param name="mydgv">控件 DataGridView</param>

        /// <param name="dic">中英文对照的标题</param>

        public static void ExportTasks(DataGridView mydgv, Dictionary<string, string> dic)

        {

            // 定义要使用的Excel 组件接口

            // 定义Application 对象,此对象表示整个Excel 程序

            Microsoft.Office.Interop.Excel.Application excelApp = null;

            // 定义Workbook对象,此对象代表工作薄

            Microsoft.Office.Interop.Excel.Workbook workBook;

            // 定义Worksheet 对象,此对象表示Execel 中的一张工作表

            Microsoft.Office.Interop.Excel.Worksheet ws = null;

            //定义Range对象,此对象代表单元格区域

            Microsoft.Office.Interop.Excel.Range range;

 

            int dcell = 1;

            int rowindex = 0; int colindex = 0;

 

            int rowcount = mydgv.Rows.Count;

            int colcount = mydgv.Columns.Count;

            int dispcolcount = dic.Count;

            try

            {

                //初始化 Application 对象 excelApp

                excelApp = new Microsoft.Office.Interop.Excel.Application();

                //在工作薄的第一个工作表上创建任务列表

                workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

                ws = (Worksheet)workBook.Worksheets[1];

 

                // 命名工作表的名称为

                ws.Name = "Sheet1";

                //创建缓存

                Object[,] objdata = new object[rowcount + 1, colcount];

                //创建标题

                foreach (string s in dic.Keys)

                {

                    objdata[rowindex, colindex++] = dic[s].ToString();

                }

                //获取数据

                for (int i = 0; i < rowcount; i++)

                {

                    dcell = 0;

                    foreach (string ss in dic.Keys)

                    {

                        for (int j = 0; j < colcount; j++)

                        {

                            if (mydgv.Columns[j].Name == ss)

                            {

                                objdata[i + 1, dcell++] = mydgv.Rows[i].Cells[j].FormattedValue.ToString(); //得到样式之后的值

                            }

                        }

                    }

                }

                //写入Excel           

                range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[rowcount, dispcolcount]);

                range.Value2 = objdata;

                System.Windows.Forms.Application.DoEvents();

                //设置格式

                excelApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //全局左对齐

                excelApp.Cells.EntireColumn.AutoFit();

                range = ws.get_Range(excelApp.Cells[1, 1], excelApp.Cells[1, colcount]);

                range.Font.Bold = true; //标题粗体

                //显示 Excel

                excelApp.Visible = true;

 

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

}