当前位置:  编程技术>.net/c#/asp.net

c#高效率导出多维表头excel的实例代码

    来源: 互联网  发布时间:2014-10-24

    本文导语:  代码如下:[DllImport("User32.dll", CharSet = CharSet.Auto)]        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);        private void ExportToExcel(string fielName)        {            //实例化一个Excel.Application对象    ...

代码如下:

[DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private void ExportToExcel(string fielName)
        {
            //实例化一个Excel.Application对象   
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                if (dgv_Result.DataSource == null)
                    return;
                if (dgv_Result.Rows.Count == 0)
                    return;


                //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错   
                Microsoft.Office.Interop.Excel.Workbook xlBook = excel.Workbooks.Add(true);

                //1.添加表头
                excel.Cells[1, 1] = tyclass;
                for (int i = 0; i < dgv_Result.Columns.Count; i++)
                {

                    excel.Cells[2, i + 1] = dgv_Result.Columns[i].Name;
                }


                #region 2.实现Excel多维表头 采用合并单元格的方式
                Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.ActiveSheet;
                Microsoft.Office.Interop.Excel.Range excelRange = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 2]);
                Microsoft.Office.Interop.Excel.Range excelRange1 = sheet.get_Range(sheet.Cells[1, 3], sheet.Cells[1, 4]);
                Microsoft.Office.Interop.Excel.Range excelRange2 = sheet.get_Range(sheet.Cells[1,5], sheet.Cells[1, 6]);
                Microsoft.Office.Interop.Excel.Range excelRange3 = sheet.get_Range(sheet.Cells[1,7], sheet.Cells[1, 8]);
                Microsoft.Office.Interop.Excel.Range excelRange4 = sheet.get_Range(sheet.Cells[1, 2], sheet.Cells[1, 3]);
                Microsoft.Office.Interop.Excel.Range excelRange5 = sheet.get_Range(sheet.Cells[1, 6], sheet.Cells[1, 7]);
                Microsoft.Office.Interop.Excel.Range excelRange6 = sheet.get_Range(sheet.Cells[1, 4], sheet.Cells[1,5]);
                excelRange.Merge(excelRange.MergeCells);
                excelRange1.Merge(excelRange1.MergeCells);
                excelRange4.Merge(excelRange4.MergeCells);
                excelRange2.Merge(excelRange2.MergeCells);
                excelRange3.Merge(excelRange3.MergeCells);
                excelRange5.Merge(excelRange5.MergeCells);
                excelRange6.Merge(excelRange6.MergeCells);
                Microsoft.Office.Interop.Excel.Range columnRange = sheet.get_Range("A1", "H2");   //得到  Range 范围  A-H 表示1-8列,1-2表示跨几行
                 columnRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                 columnRange.Font.Size = 10;
                 columnRange.Font.Bold = true;

                #endregion

               
                #region 3.添加行数据,直接给Range赋值可提高效率

                 Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A3", "H" + (dgv_Result.Rows.Count + 2).ToString());   //得到  Range 范围
                string[,] AryData = new string[dgv_Result.Rows.Count-1, dgv_Result.Columns.Count];
                for (int i = 0; i < dgv_Result.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dgv_Result.Columns.Count; j++)
                    {

                        AryData[i,j] = dgv_Result.Rows[i].Cells[j].Value.ToString();
                    }
                }
                range.Value2 = AryData;
                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                #endregion

                sheet.Cells.Columns.AutoFit();//设置Excel表格的  列宽
                excel.SheetsInNewWorkbook = 1;//设置Excel单元格对齐方式
                excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框   
                excel.AlertBeforeOverwriting = false;
                //保存excel文件   
                xlBook.SaveAs(fielName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                MessageBox.Show("导出成功!", "提示");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }
            finally
            {
                IntPtr pt = new IntPtr(excel.Hwnd);
                int k = 0;
                GetWindowThreadProcessId(pt, out k);
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                p.Kill();


            }

        }


    
 
 

您可能感兴趣的文章:

 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • 如何高效率的实现类似于csdn上的发送短信息功能??
  • 高效率的nio框架 nio java raptor
  • 作为Mysql Server, 哪个Linux 较高效率?
  • MySQL大表中重复字段的高效率查询方法
  • mysql分页原理和高效率的mysql分页查询语句


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3