/// <summary> /// Evaluates, expands and exports an <see cref="IEnumerable"/> of a specified type contained in <see cref="excelOutputItem"/>into Excel. If the specified type is a complex type, /// all properties and fields are exported into columns. If the specified type is a simple type, the data is exported into a /// single column. /// </summary> /// <param name="excelOutputItem">An object containing all the necessary parameters to create an Excel export into a single worksheet.</param> public static void ToExcel(this ExcelOutputItem excelOutputItem) { var excelOutputCollection = new ExcelOutputCollection(excelOutputItem.PostCreationActions, excelOutputItem.Path) { excelOutputItem, }; excelOutputCollection.ToExcel(); }
private static void CreateWorkbook(Application app, ExcelOutputCollection excelOutputCollection) { using (var wb = app.Workbooks.Add()) { var count = 1; foreach (var item in excelOutputCollection) { using (var ws = (Worksheet)wb.Worksheets[count]) { var enumerator = item.Enumerable.GetEnumerator(); var arrayList = new ArrayList(); while (enumerator.MoveNext()) { arrayList.Add(enumerator.Current); } var inputArray = arrayList.ToArray(); var rowCount = inputArray.Length; ws.Name = item.WorksheetName; if (item.Type.IsSimpleType()) { SimpleTypeProcessing(item.Type, inputArray, rowCount, ws); } else { ComplexTypeProcessing(item.Trim, item.Type, inputArray, rowCount, ws); } ws.Columns.AutoFit(); } count++; } } }
/// <summary> /// Evaluates, expands and exports a collection of <see cref="ExcelOutputItem"/> objects, with their respective types defined in each <see cref="ExcelOutputItem"/> object, /// into Excel. A new sheet is created for each <see cref="ExcelOutputItem"/> object. /// </summary> /// <param name="excelOutputCollection">An object containing all the necessary parameters to create an Excel export into a multiple worksheets.</param> public static void ToExcel(this ExcelOutputCollection excelOutputCollection) { Exception exception = null; ExcelOutputConfiguration.DefaultCulture = Thread.CurrentThread.CurrentCulture; ExcelOutputConfiguration.DefaultDateTimeFormat = ExcelOutputConfiguration.DefaultCulture.DateTimeFormat.ShortDatePattern + " " + Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern; ExcelOutputConfiguration.DefaultDateFormat = ExcelOutputConfiguration.DefaultCulture.DateTimeFormat.ShortDatePattern; ExcelOutputConfiguration.DefaultTimeFormat = ExcelOutputConfiguration.DefaultCulture.DateTimeFormat.LongTimePattern; // HACK: Workaround for Excel bug on machines which are set up in the English language, but not an English region. var enusCultureInfo = CultureInfo.GetCultureInfo("en-US"); Thread.CurrentThread.CurrentCulture = enusCultureInfo; AbortIfPathIsEmptyAndSaveIsRequired(excelOutputCollection.Path, excelOutputCollection.PostCreationAction); using (var app = new Application()) { var sheetsInNewWorkbook = app.SheetsInNewWorkbook; app.SheetsInNewWorkbook = excelOutputCollection.Count; try { CreateWorkbook(app, excelOutputCollection); ExecutePostCreationActions(app, excelOutputCollection.PostCreationAction, excelOutputCollection.Path); } catch (Exception ex) { exception = ex; } finally { app.SheetsInNewWorkbook = sheetsInNewWorkbook; Thread.CurrentThread.CurrentCulture = ExcelOutputConfiguration.DefaultCulture; if (exception != null) { if (app.Workbooks.Any()) { foreach (var workbook in app.Workbooks.Where(x => !x.IsDisposed)) { workbook.Close(false, Missing.Value, Missing.Value); workbook.Dispose(); } } if (app.IsDisposed) { throw exception; } app.Quit(); app.Dispose(); throw exception; } } } }