.NET(VB,C#)导出DATAGRIDVIEW到EXCEL中

来源：互联网发布：一个域名对应多个ip 编辑：程序博客网时间：2024/05/17 21:57

引用EXCEL库就不说了,直接贴上代码

c#:

        #region 导出DataGridView中数据为EXCEL文件 
        public Boolean OutExcel(DataGridView grView, string sPath, string exName)
        {
            //函数说明:导出DataGridView中数据为EXCEL文件 
            //输入参数:grView-一个DtaGridView对象；sPath-需要存放的路径;exName-导出的EXCEL文件中的表名 
            //返回：True-成功；False-失败 
            // 创建Excel对象                     
            Excel.Application xlExcel = new Excel.ApplicationClass();
            if (xlExcel == null)
            {
                MessageBox.Show("Excel无法启动");
                return false;
            }
            // 创建Excel工作薄 
            Excel.Workbook xlBook = xlExcel.Workbooks.Add(true);
            Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
            Excel.Range range = xlSheet.get_Range(xlExcel.Cells[1, 1], xlExcel.Cells[1, grView.ColumnCount]);
            // 设置标题 
            if (exName != "")
            {
                range.MergeCells = true;//合并单元格 
                xlExcel.ActiveCell.FormulaR1C1 = exName;
                xlExcel.ActiveCell.Font.Size = 20;
                xlExcel.ActiveCell.Font.Bold = true;
                xlExcel.ActiveCell.HorizontalAlignment = Excel.Constants.xlCenter;
            }
            // 列索引，行索引，总列数，总行数 
            int colIndex = 0;
            int RowIndex = 0;
            int colCount = grView.ColumnCount;
            int RowCount = grView.Rows.Count;
            // 创建缓存数据 
            object[,] objData = new object[RowCount + 1, colCount - 1];
            // 获取列标题 
            for (int i = 1; i < colCount; i++)
            {
                objData[0, i - 1] = grView.Columns[i].HeaderCell.Value;
            }
            // 获取数据 
            for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
            {
                for (colIndex = 0; colIndex < colCount - 1; colIndex++)
                {
                    objData[RowIndex, colIndex] = grView.Rows[RowIndex - 1].Cells[colIndex + 1].Value.ToString();
                }
                Application.DoEvents();
            }
            // 写入Excel 
            xlExcel.get_Range(xlExcel.Cells[2, 1], xlExcel.Cells[2, colIndex]).Font.Bold = true;
            range = xlSheet.get_Range(xlExcel.Cells[2, 1], xlExcel.Cells[RowCount + 2, colCount - 1]);
            range.Value2 = objData;
            // 保存 
            try
            {
                xlExcel.Cells.EntireColumn.AutoFit();
                xlExcel.Cells.VerticalAlignment = Excel.Constants.xlCenter;
                xlExcel.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
                //xlExcel.Visible   =   true;    
                xlBook.Saved = true;
                xlBook.SaveCopyAs(sPath);
                return true;
            }
            catch
            {
                MessageBox.Show("导出EXCEL文件失败！");
                return false;
            }
            finally
            {
                xlExcel.Quit();
                GC.Collect();
                KillProcess("excel");
            }
        } 
        #endregion 
        #region 杀死进程 
        public void KillProcess(string processName)
        {
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程  
            try
            {
                foreach (Process thisproc in Process.GetProcessesByName(processName))
                {
                    thisproc.Kill();
                }
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        } 
  #endregion 

VB.NET:

Private Sub CmdExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdExcel.Click
        '函数说明：导出网格中数据为EXCEL文件 
        Dim VBExcel As Microsoft.Office.Interop.Excel.Application
        Dim ExBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim ExeclArray As Object
        Dim sPath As String
        Dim I As Object
        Dim J As Short
        Me.Cursor = Cursors.WaitCursor
        SvFlg.Filter = "Excel文件(*.xls)|*.xls"
        SvFlg.ShowDialog()
        sPath = SvFlg.FileName
        If sPath = "" Then
            Me.Cursor = Cursors.AppStarting
            Exit Sub
        End If
        VBExcel = CreateObject("Excel.Application")
        ExBook = VBExcel.Workbooks.Add
        ExSheet = ExBook.Worksheets(1)
        Try
            ReDim ExeclArray(Msfg.Rows, Msfg.Cols)
            VBExcel.ScreenUpdating = False
            For I = 0 To Msfg.Rows - 1
                For J = 0 To Msfg.Cols - 1
                    ExeclArray(I + 1, J + 1) = Msfg.get_TextMatrix(I, J)
                Next
            Next
            ExSheet.Columns._Default("B:B").ColumnWidth = 17
            '从第一行第一列开始 
            With ExSheet
                .Range(.Cells._Default(1, 1), .Cells._Default(Msfg.Rows, Msfg.Cols + 1)).Value = ExeclArray
            End With
            ExSheet.SaveAs(sPath)
            VBExcel.Quit()
            VBExcel = Nothing
            ExBook = Nothing
            ExSheet = Nothing
            Me.Cursor = Cursors.AppStarting
            MessageBox.Show("导出报表成功!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            VBExcel.Quit()
            VBExcel = Nothing
            ExBook = Nothing
            ExSheet = Nothing
            Me.Cursor = Cursors.AppStarting
            MessageBox.Show("导出报表失败!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try
    End Sub