Ejemplo n.º 1
0
 /// <summary>
 /// 將GridView 轉成EXCEL
 /// </summary>
 /// <param name="pdgv"></param>
 public static void GridView_To_Excel(DataGridView pdgv)
 {
     if (pdgv.RowCount > 0)
     {
         ExpToExcel.DataGridViewToExcel(pdgv);
     }
 }
Ejemplo n.º 2
0
        public void SaveToExcel(DataGridView _dgv, BackgroundWorker _worker)
        {
            string strFileName = ExpToExcel.GetFileName(_dgv);

            if (strFileName == "" || strFileName == null)
            {
                return;
            }

            int rowscount = _dgv.Rows.Count;
            int colscount = _dgv.Columns.Count;
            int intValue  = 0;

            //创建空EXCEL对象
            Microsoft.Office.Interop.Excel.Application objExcel    = null;
            Microsoft.Office.Interop.Excel.Workbook    objWorkbook = null;
            Microsoft.Office.Interop.Excel.Worksheet   objSheet    = null;
            Microsoft.Office.Interop.Excel.Range       rg          = null;

            try
            {
                //申明对象
                objExcel    = new Microsoft.Office.Interop.Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objSheet    = (Microsoft.Office.Interop.Excel.Worksheet)objWorkbook.Worksheets[1];//强制类型转换
                //设置EXCEL不可见(后台运行)
                objExcel.Visible = false;

                //向Excel中写入表格的表头
                int displayColumnsCount = 1;
                for (int i = 0; i <= _dgv.ColumnCount - 1; i++)
                {
                    if (_dgv.Columns[i].Visible == true)
                    {
                        objExcel.Cells[1, displayColumnsCount] = _dgv.Columns[i].HeaderText.Trim();
                        displayColumnsCount++;
                    }
                }

                //向Excel中逐行逐列写入表格中的数据
                for (int row = 0; row <= _dgv.RowCount - 1; row++)
                {
                    displayColumnsCount = 1;
                    for (int col = 0; col < colscount; col++)
                    {
                        if (_dgv.Columns[col].Visible == true)
                        {
                            if (col == 4 || col == 5)//_dgv.Columns[col].HeaderText == "size" || _dgv.Columns[col].HeaderText == "size_desc"
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = "'" + _dgv.Rows[row].Cells[col].Value.ToString().Trim();
                            }
                            else
                            {
                                objExcel.Cells[row + 2, displayColumnsCount] = _dgv.Rows[row].Cells[col].Value.ToString().Trim();
                            }

                            if (_dgv.Rows[row].Cells[col].Value.ToString() == "Sub-Total" || _dgv.Rows[row].Cells[col].Value.ToString() == "Sum-Total")
                            {
                                rg = (Microsoft.Office.Interop.Excel.Range)
                                     objSheet.Range[objSheet.Cells[row + 2, 1], objSheet.Cells[row + 2, colscount]];
                                rg.Font.Bold           = true;
                                rg.Font.Size           = 15;
                                rg.Interior.ColorIndex = 35;
                                rg.Interior.Pattern    = -4105;
                            }

                            displayColumnsCount++;
                        }
                    }
                    intValue++;

                    string strMessage = ((100 * intValue) / rowscount).ToString() + "%";
                    Thread.Sleep(100);
                    _worker.ReportProgress(((100 * intValue) / rowscount), strMessage); //注意:这里向子窗体返回两个信息值,一个用于进度条,一个用于label的。
                    System.Windows.Forms.Application.DoEvents();
                }

                //objSheet.Columns.EntireColumn.AutoFit();//列宽自适应
                //保存文件
                objWorkbook.SaveAs(strFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                                   Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            //关闭Excel应用
            if (objWorkbook != null)
            {
                objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
            }
            if (objExcel.Workbooks != null)
            {
                objExcel.Workbooks.Close();
            }
            if (objExcel != null)
            {
                objExcel.Quit();
            }
            //清空工作表
            objSheet = null;
            //清空工作薄
            objWorkbook = null;
            objExcel    = null;

            //强行杀死最近打开的excel进程
            KillProcess("EXCEL");

            MessageBox.Show(strFileName + "\n\n导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }