欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-21 14:01:34
复制代码 代码如下:[dllimport("user32.dll", charset = charset.auto)]    &nb...

复制代码 代码如下:

[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();


            }

        }