C# dataGridView导出Excel

总结DataGridView导出Excel代码

 

 private void DataGridViewToExcel(DataGridView dgv)
        {
            #region   验证可操作性

            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多(最多不能超过255列)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.DefaultExt = "xls ";
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            if (dlg.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }
            string fileNameString = dlg.FileName;
            if (fileNameString.Trim() == " ")
            {
                return;
            }
            //验证以fileNameString命名的文件是否存在，如果存在删除它  
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
            #endregion
            //建立Excel对象
            Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook xBk = excel.Workbooks.Add(true);
            Excel.Worksheet xSt = (Excel.Worksheet)xBk.Worksheets[1];
            excel.Visible = false;
            #region 填充表头和表体
            int displayColumnsCount = 1;
            for (int i = 0; i < colscount; i++)
            {
                if (dgv.Columns[i].Visible)
                {
                    excel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
                    displayColumnsCount++;
                }
            }
            tempProgressBar.Refresh();
            tempProgressBar.Visible = true;
            tempProgressBar.Minimum = 1;
            tempProgressBar.Maximum = rowscount;
            tempProgressBar.Step = 5;
            for (int row = 0; row < rowscount; row++)
            {
                if (row % 5 == 0)
                {
                    tempProgressBar.PerformStep();
                }
                displayColumnsCount = 1;
                for (int col = 0; col < colscount; col++)
                {
                    if (dgv.Columns[col].Visible)
                    {
                        try
                        {
                            excel.Cells[row + 2, displayColumnsCount] = "'" + dgv.Rows[row].Cells[col].Value.ToString().Trim();
                            displayColumnsCount++;
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
            }
            excel.Cells.Borders.LineStyle = 1;
            excel.Columns.AutoFit();
            tempProgressBar.Visible = false;
            #endregion
            xBk.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                           Missing.Value, XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value,
                           Missing.Value,
                           Missing.Value, Missing.Value);
            if (DialogResult.Yes.Equals(MessageBox.Show(fileNameString + "/n/n导出完毕! 要打开导出的文件么？ ", "提示 ", MessageBoxButtons.YesNo, MessageBoxIcon.Question)))
            {
                excel.Visible = true;
                return;
            }
            else
            {
                if (xBk != null)
                {
                    xBk.Close(Missing.Value, Missing.Value, Missing.Value);
                }
                if (excel.Workbooks != null)
                {
                    excel.Workbooks.Close();
                }
                if (excel != null)
                {
                    excel.Quit();
                }
                xSt = null;
                xBk = null;
                excel = null;
            }
        }