DataGrid导出到Ex el, 自动获取列标识

最新推荐文章于 2025-04-14 10:36:03 发布

原创最新推荐文章于 2025-04-14 10:36:03 发布 · 541 阅读

0 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

20 篇文章

订阅专栏

本文介绍了一种将DataGridView中的数据导出到Excel的方法。通过使用Microsoft Office Interop Excel组件，可以实现在C#应用程序中创建并填充Excel工作表的功能。文章详细展示了如何计算最后一列的标识符、初始化Excel应用、设置单元格范围以及写入表头和数据。

 #region [ 导出到Excel ]
        public void Export()
        {
            
            #region range
            int count = this.Columns.Count;
            if (count > 256 || count == 0)//超出Excel的最大行数，或者为零
            {
                MessageBox.Show("Invalid Row Count", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            string cell = string.Empty;
            int first = count / 26;
            int second = count - (first * 26);
            if (first > 1)
            {
                if (second > 0)
                    cell = ((char)(64 + first)).ToString() + ((char)(64 + second)).ToString();
                else if (second == 0)
                    cell = ((char)(64 + first - 1)).ToString() + ((char)(90)).ToString();
                else
                {
                    MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
            else if (first == 1)
            {
                if (second > 0)
                    cell = ((char)(64 + first)).ToString() + ((char)(64 + second)).ToString();
                else if (second == 0)
                    cell = ((char)(90)).ToString();
                else
                {
                    MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }
            else if (first == 0)
            {
                cell = ((char)(64 + second)).ToString();
            }
            else
            {
                MessageBox.Show("Interial Error", "Error Information", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            cell += "1";
            #endregion
            #region Start Excel
            Excel.Application app = new Excel.ApplicationClass();
            if (app == null)
            {
                MessageBox.Show("Excel无法启动，请确认本机已经正确安装Microsoft Excel。","启动错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
                return;
            }
            app.Visible = false;
            Excel.Workbooks wbs = app.Workbooks;
            Excel.Workbook wb = wbs.Add(Missing.Value);
            Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
            #endregion
            Excel.Range r = ws.get_Range("A1", cell); //MessageBox.Show(cell);
            //r.Select();
            r.NumberFormatLocal = "@";
            
            
            #region header
            object[] objHeader = new object[this.Columns.Count];
            foreach (DataGridViewColumn c in this.Columns)
                objHeader[c.Index] = c.Name;
            #endregion
            #region write data
            r.Value2 = objHeader;
            if (this.Rows.Count > 1)
            {
                r = ws.get_Range("A2", Missing.Value);
                object[,] objData = new Object[this.Rows.Count, this.Columns.Count];
                for (int i = 0; i < this.Rows.Count - 1; i++)
                {
                    foreach (DataGridViewColumn col in this.Columns)
                    {
                        if (this.Rows[i].Cells[col.Index].Value != null)
                            objData[i, col.Index] = this.Rows[i].Cells[col.Index].Value;
                    }
                }
                
                r = r.get_Resize(this.Rows.Count, this.Columns.Count);
                r.Select(); //选中
                r.NumberFormatLocal = "@";  //文本格式
                r.Value2 = objData;
                r.EntireColumn.AutoFit();   //自动调整列宽
                r.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;    //居中
            }
            #endregion
            app.Visible = true;
             
        }//导出到Microsoft Excel
        #endregion