/// <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);
}