inabox/inabox.wpf/Excel/ExcelExporter.cs

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