[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
private void ExportToExcel(string fielName)
{
// instantiation 1 a Excel.Application object
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
try
{
if (dgv_Result.DataSource == null)
return;
if (dgv_Result.Rows.Count == 0)
return;
// The new increase 1 A workbook, Workbook I'm going to save it directly, and I'm not going to pop up the save dialog, plus Application The save dialog box will pop up with a value of false complains
Microsoft.Office.Interop.Excel.Workbook xlBook = excel.Workbooks.Add(true);
//1. Add a header
excel.Cells[1, 1] = tyclass;
for (int i = 0; i < dgv_Result.Columns.Count; i++)
{
excel.Cells[2, i + 1] = dgv_Result.Columns[i].Name;
}
#region 2. implementation Excel Multidimensional header Take the approach of merging cells
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range excelRange = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[1, 2]);
Microsoft.Office.Interop.Excel.Range excelRange1 = sheet.get_Range(sheet.Cells[1, 3], sheet.Cells[1, 4]);
Microsoft.Office.Interop.Excel.Range excelRange2 = sheet.get_Range(sheet.Cells[1,5], sheet.Cells[1, 6]);
Microsoft.Office.Interop.Excel.Range excelRange3 = sheet.get_Range(sheet.Cells[1,7], sheet.Cells[1, 8]);
Microsoft.Office.Interop.Excel.Range excelRange4 = sheet.get_Range(sheet.Cells[1, 2], sheet.Cells[1, 3]);
Microsoft.Office.Interop.Excel.Range excelRange5 = sheet.get_Range(sheet.Cells[1, 6], sheet.Cells[1, 7]);
Microsoft.Office.Interop.Excel.Range excelRange6 = sheet.get_Range(sheet.Cells[1, 4], sheet.Cells[1,5]);
excelRange.Merge(excelRange.MergeCells);
excelRange1.Merge(excelRange1.MergeCells);
excelRange4.Merge(excelRange4.MergeCells);
excelRange2.Merge(excelRange2.MergeCells);
excelRange3.Merge(excelRange3.MergeCells);
excelRange5.Merge(excelRange5.MergeCells);
excelRange6.Merge(excelRange6.MergeCells);
Microsoft.Office.Interop.Excel.Range columnRange = sheet.get_Range("A1", "H2"); // get Range The scope of A-H said 1-8 The column, 1-2 It means across several lines
columnRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
columnRange.Font.Size = 10;
columnRange.Font.Bold = true;
#endregion
#region 3. Add row data directly to it Range Assignment improves efficiency
Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A3", "H" + (dgv_Result.Rows.Count + 2).ToString()); // get Range The scope of
string[,] AryData = new string[dgv_Result.Rows.Count-1, dgv_Result.Columns.Count];
for (int i = 0; i < dgv_Result.Rows.Count - 1; i++)
{
for (int j = 0; j < dgv_Result.Columns.Count; j++)
{
AryData[i,j] = dgv_Result.Rows[i].Cells[j].Value.ToString();
}
}
range.Value2 = AryData;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
#endregion
sheet.Cells.Columns.AutoFit();// Set up the Excel Of the table Column width
excel.SheetsInNewWorkbook = 1;// Set up the Excel Cell alignment
excel.DisplayAlerts = false; // Set to disable the popup save and overwrite question box
excel.AlertBeforeOverwriting = false;
// save excel file
xlBook.SaveAs(fielName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
MessageBox.Show(" Export successful! ", " prompt ");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, " Error message ");
}
finally
{
IntPtr pt = new IntPtr(excel.Hwnd);
int k = 0;
GetWindowThreadProcessId(pt, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
}