Advertisement
Guest User

Untitled

a guest
Dec 13th, 2018
66
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.12 KB | None | 0 0
  1. private void saveToolStripMenuItem_Click(object sender, EventArgs e)
  2. {
  3. Microsoft.Office.Interop.Excel.Application ExcelApp =
  4. new Microsoft.Office.Interop.Excel.Application();
  5. Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
  6. Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
  7. //Книга.
  8. ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
  9. //Таблица.
  10. ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);
  11. for (int i = 0; i < dgvCustomer.ColumnCount; i++)
  12. {
  13. ExcelApp.Cells[1, i + 1] = Convert.ToString(dgvCustomer.Columns[i].HeaderText);
  14. }
  15. for (int i = 0; i < dgvCustomer.Rows.Count; i++)
  16. {
  17. for (int j = 0; j < dgvCustomer.ColumnCount; j++)
  18. {
  19. if (i==8)
  20. {
  21. Image data = ConvertBinaryToImage((byte[])dgvCustomer.Rows[4].Cells[0].Value);
  22. Clipboard.SetImage(data);
  23. }
  24. ExcelApp.Cells[i + 2, j + 1] = Convert.ToString(dgvCustomer.Rows[i].Cells[j].Value);
  25. }
  26. }
  27. int iLastRow = ExcelWorkSheet.Cells[ExcelWorkSheet.Rows.Count, "A"].End[Excel.XlDirection.xlUp].Row;
  28. for (int i = iLastRow; i >= 0; i--)
  29. {
  30. //ExcelWorkSheet.Cells[7].Delete();
  31. ExcelWorkSheet.Cells[8].Delete();
  32. ExcelWorkSheet.Cells[9].Delete();
  33. }
  34. for (int c = 1; c < 8; c++)
  35. {
  36. (ExcelWorkSheet.Rows[1].Cells[c] as Microsoft.Office.Interop.Excel.Range).Interior.Color = Color.LightGray;
  37. ExcelWorkSheet.Cells[1, c].HorizontalAlignment = 3;
  38. }
  39. for (int k = 2; k <= dgvCustomer.RowCount + 1; k++)
  40. {
  41. ExcelWorkSheet.Cells[k, 6].NumberFormat = ("+###\" \"(00) 000\"-\"00\"-\"00");
  42. // экранирование для формата номера
  43. // +###" "(00) 000"-"00"-"00
  44. ExcelWorkSheet.Hyperlinks.Add(ExcelWorkSheet.Cells[k, 7], ExcelWorkSheet.Cells[k, 7], Type.Missing, "Гиперссылка на картинку");
  45. }
  46. //ExcelWorkSheet.Shapes.AddPicture("D:\\test.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 80, 70);
  47.  
  48. //label4.Text = string.Format("Total rows: {0}", dgvCustomer.RowCount.ToString());
  49. ExcelWorkSheet.Columns.AutoFit();
  50.  
  51. Microsoft.Office.Interop.Excel.Range tRange = ExcelWorkSheet.UsedRange;
  52. tRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
  53. tRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
  54.  
  55. for (int t = dgvCustomer.RowCount + 2; t <= dgvCustomer.RowCount + 2; t++)
  56. {
  57. ExcelWorkSheet.Cells[t, 7] = string.Format("Total rows: {0}", dgvCustomer.RowCount.ToString());
  58. ExcelWorkSheet.Cells[t, 7].Font.Bold = true;
  59. ExcelWorkSheet.Cells[t, 7].HorizontalAlignment = Excel.Constants.xlRight;
  60. //ExcelWorkSheet.Cells[t, 7].Style.Font.Size = 12;
  61. }
  62.  
  63. SaveFileDialog sfd = new SaveFileDialog()
  64. {
  65. Filter = "MS Excel dosuments (*.xlsx)|*.xlsx",
  66. DefaultExt = "*.xlsx",
  67. Title = "Укажите директорию и имя файла для сохранения"
  68. };
  69. if (sfd.ShowDialog() == DialogResult.OK)
  70. {
  71. ExcelWorkBook.SaveAs(sfd.FileName);
  72. ExcelApp.DisplayAlerts = true;
  73. }
  74. ExcelWorkBook.Close();
  75. ExcelApp.Application.Quit();
  76. ExcelApp.Quit();
  77. ExcelApp = null;
  78. ExcelWorkBook = null;
  79. ExcelWorkSheet = null;
  80. GC.Collect();
  81. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement