/// <summary> /// 將GridView 轉成EXCEL /// </summary> /// <param name="pdgv"></param> public static void GridView_To_Excel(DataGridView pdgv) { if (pdgv.RowCount > 0) { ExpToExcel.DataGridViewToExcel(pdgv); } }
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); }