1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889 |
- using System;
- using System.Collections.Generic;
- using System.Diagnostics;
- using System.Linq;
- using InABox.Core;
- using Syncfusion.XlsIO;
- using MessageBox = System.Windows.MessageBox;
- using SaveFileDialog = Microsoft.Win32.SaveFileDialog;
- namespace InABox.WPF
- {
- public static class ExcelExporter
- {
- private static void ExportSheet(int i, IWorkbook workbook, Type? T, CoreTable data)
- {
- var sheet = workbook.Worksheets[i];
- sheet.Name = T?.EntityName().Split('.').Last() ?? data.TableName;
- for (var iCol = 0; iCol < data.Columns.Count; iCol++)
- {
- var field = data.Columns[iCol].ColumnName;
- var editor = T != null ? DatabaseSchema.Property(T, field)?.Editor : null;
- sheet.Range[1, iCol + 1].Text = field;
- for (var iRow = 0; iRow < data.Rows.Count; iRow++)
- {
- var val = data.Rows[iRow].Get<object>(field);
- if (editor is NotesEditor)
- sheet.Range[iRow + 2, iCol + 1].Text = string.Join("\n", val as string[]);
- else if (editor is RichTextEditor)
- sheet.Range[iRow + 2, iCol + 1].Text = CoreUtils.StripHTML(val as string);
- else
- sheet.Range[iRow + 2, iCol + 1].Value2 = val;
- }
- }
- sheet.UsedRange.AutofitColumns();
- foreach (var col in sheet.UsedRange.Columns)
- col.ColumnWidth += 5;
- foreach (var row in sheet.UsedRange.Rows)
- {
- row.RowHeight += 5;
- row.VerticalAlignment = ExcelVAlign.VAlignCenter;
- }
- }
- public static void DoExport(IEnumerable<Tuple<Type?, CoreTable>> data, string filename)
- {
- var excelEngine = new ExcelEngine();
- var application = excelEngine.Excel;
- var dataArr = data.ToArray();
- var myWorkbook = application.Workbooks.Create(dataArr.Length);
- myWorkbook.Version = ExcelVersion.Excel2007;
- int i = 0;
- foreach(var (tableType, table) in dataArr)
- {
- ExportSheet(i, myWorkbook, tableType, table);
- i++;
- }
- var dlg = new SaveFileDialog();
- dlg.Filter = "Excel Files (*.xlsx)|*.xlsx";
- dlg.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
- dlg.FileName = string.Format("{0} {1:yyyy-MM-dd hh-mm-ss}.xlsx", CoreUtils.SanitiseFileName(filename), DateTime.Now);
- if (dlg.ShowDialog() == true)
- try
- {
- myWorkbook.SaveAs(dlg.FileName, ExcelSaveType.SaveAsXLS);
- Process.Start(new ProcessStartInfo(dlg.FileName) { UseShellExecute = true });
- }
- catch (Exception e2)
- {
- MessageBox.Show("Error saving spreadsheet!\n\n" + e2.Message);
- }
- }
- public static void DoExport<T>(CoreTable data, string filename)
- {
- DoExport(new[] { new Tuple<Type?, CoreTable>(typeof(T), data) }, filename);
- }
- public static void DoExport(CoreTable data, string filename)
- {
- DoExport(new[] { new Tuple<Type?, CoreTable>(null, data) }, filename);
- }
- }
- }
|