博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
c# 高效率导出多维表头excel
阅读量:6156 次
发布时间:2019-06-21

本文共 4597 字,大约阅读时间需要 15 分钟。

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

 

转载于:https://www.cnblogs.com/bile/p/3401538.html

你可能感兴趣的文章
树莓派是如何免疫 Meltdown 和 Spectre 漏洞的
查看>>
雅虎瓦片地图切片问题
查看>>
HTML 邮件链接,超链接发邮件
查看>>
HDU 5524:Subtrees
查看>>
手机端userAgent
查看>>
pip安装Mysql-python报错EnvironmentError: mysql_config not found
查看>>
http协议组成(请求状态码)
查看>>
怎样成为一个高手观后感
查看>>
[转]VC预处理指令与宏定义的妙用
查看>>
MySql操作
查看>>
python 解析 XML文件
查看>>
MySQL 文件导入出错
查看>>
java相关
查看>>
由一个异常开始思考springmvc参数解析
查看>>
向上扩展型SSD 将可满足向外扩展需求
查看>>
虚机不能启动的特例思考
查看>>
SQL Server编程系列(1):SMO介绍
查看>>
在VMware网络测试“专用VLAN”功能
查看>>
使用Formik轻松开发更高质量的React表单(三)<Formik />解析
查看>>
也问腾讯:你把用户放在什么位置?
查看>>