ExcelExporter.cs 3.4 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Diagnostics;
  4. using System.Linq;
  5. using InABox.Core;
  6. using Syncfusion.XlsIO;
  7. using MessageBox = System.Windows.MessageBox;
  8. using SaveFileDialog = Microsoft.Win32.SaveFileDialog;
  9. namespace InABox.WPF
  10. {
  11. public static class ExcelExporter
  12. {
  13. private static void ExportSheet(int i, IWorkbook workbook, Type? T, CoreTable data)
  14. {
  15. var sheet = workbook.Worksheets[i];
  16. sheet.Name = T?.EntityName().Split('.').Last() ?? data.TableName;
  17. for (var iCol = 0; iCol < data.Columns.Count; iCol++)
  18. {
  19. var field = data.Columns[iCol].ColumnName;
  20. var editor = T != null ? DatabaseSchema.Property(T, field)?.Editor : null;
  21. sheet.Range[1, iCol + 1].Text = field;
  22. for (var iRow = 0; iRow < data.Rows.Count; iRow++)
  23. {
  24. var val = data.Rows[iRow].Get<object>(field);
  25. if (editor is NotesEditor)
  26. sheet.Range[iRow + 2, iCol + 1].Text = string.Join("\n", val as string[]);
  27. else if (editor is RichTextEditor)
  28. sheet.Range[iRow + 2, iCol + 1].Text = CoreUtils.StripHTML(val as string);
  29. else
  30. sheet.Range[iRow + 2, iCol + 1].Value2 = val;
  31. }
  32. }
  33. sheet.UsedRange.AutofitColumns();
  34. foreach (var col in sheet.UsedRange.Columns)
  35. col.ColumnWidth += 5;
  36. foreach (var row in sheet.UsedRange.Rows)
  37. {
  38. row.RowHeight += 5;
  39. row.VerticalAlignment = ExcelVAlign.VAlignCenter;
  40. }
  41. }
  42. public static void DoExport(IEnumerable<Tuple<Type?, CoreTable>> data, string filename)
  43. {
  44. var excelEngine = new ExcelEngine();
  45. var application = excelEngine.Excel;
  46. var dataArr = data.ToArray();
  47. var myWorkbook = application.Workbooks.Create(dataArr.Length);
  48. myWorkbook.Version = ExcelVersion.Excel2007;
  49. int i = 0;
  50. foreach(var (tableType, table) in dataArr)
  51. {
  52. ExportSheet(i, myWorkbook, tableType, table);
  53. i++;
  54. }
  55. var dlg = new SaveFileDialog();
  56. dlg.Filter = "Excel Files (*.xlsx)|*.xlsx";
  57. dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
  58. dlg.FileName = string.Format("{0} {1:yyyy-MM-dd hh-mm-ss}.xlsx", CoreUtils.SanitiseFileName(filename), DateTime.Now);
  59. if (dlg.ShowDialog() == true)
  60. try
  61. {
  62. myWorkbook.SaveAs(dlg.FileName, ExcelSaveType.SaveAsXLS);
  63. Process.Start(new ProcessStartInfo(dlg.FileName) { UseShellExecute = true });
  64. }
  65. catch (Exception e2)
  66. {
  67. MessageBox.Show("Error saving spreadsheet!\n\n" + e2.Message);
  68. }
  69. }
  70. public static void DoExport<T>(CoreTable data, string filename)
  71. {
  72. DoExport(new[] { new Tuple<Type?, CoreTable>(typeof(T), data) }, filename);
  73. }
  74. public static void DoExport(CoreTable data, string filename)
  75. {
  76. DoExport(new[] { new Tuple<Type?, CoreTable>(null, data) }, filename);
  77. }
  78. }
  79. }