/// <summary> /// Adds given list of objects to the worksheet /// </summary> /// <typeparam name="T"></typeparam> /// <param name="worksheet"></param> /// <param name="items"></param> /// <param name="startRowIndex"></param> /// <param name="startColumnIndex"></param> /// <param name="configureCells"></param> /// <returns></returns> public static ExcelWorksheet AddObjects <T>(this ExcelWorksheet worksheet, IEnumerable <T> items, int startRowIndex, int startColumnIndex = 1, Action <ExcelRange> configureCells = null) { for (var i = 0; i < items.Count(); i++) { for (int j = startColumnIndex; j < startColumnIndex + typeof(T).GetProperties().Length; j++) { worksheet.AddLine(i + startRowIndex, j, configureCells, items.ElementAt(i).GetPropertyValue(typeof(T).GetProperties()[j - startColumnIndex].Name)); } } return(worksheet); }
/// <summary> /// Adds given list of objects to the worksheet with propery selectors /// </summary> /// <typeparam name="T"></typeparam> /// <param name="worksheet"></param> /// <param name="items"></param> /// <param name="startRowIndex"></param> /// <param name="startColumnIndex"></param> /// <param name="configureCells"></param> /// <param name="propertySelectors"></param> /// <returns></returns> public static ExcelWorksheet AddObjects <T>(this ExcelWorksheet worksheet, IEnumerable <T> items, int startRowIndex, int startColumnIndex, Action <ExcelRange> configureCells = null, params Func <T, object>[] propertySelectors) { NotNull(propertySelectors, nameof(propertySelectors)); for (var i = 0; i < items.Count(); i++) { for (int j = startColumnIndex; j < startColumnIndex + propertySelectors.Length; j++) { worksheet.AddLine(i + startRowIndex, j, configureCells, propertySelectors[j - startColumnIndex](items.ElementAt(i))); } } return(worksheet); }
/// <summary> /// Inserts a header line to the top of the worksheet /// </summary> /// <param name="worksheet"></param> /// <param name="configureHeader"></param> /// <param name="headerTexts"></param> /// <returns></returns> public static ExcelWorksheet AddHeader(this ExcelWorksheet worksheet, Action <ExcelRange> configureHeader = null, params string[] headerTexts) { if (!headerTexts.Any()) { return(worksheet); } worksheet.InsertRow(1, 1); for (var i = 0; i < headerTexts.Length; i++) { worksheet.Cells[1, i + 1].Style.Font.Bold = true; worksheet.AddLine(1, i + 1, configureHeader, headerTexts[i]); } return(worksheet); }
/// <summary> /// Adds given list of objects to the worksheet with propery selectors /// </summary> /// <typeparam name="T"></typeparam> /// <param name="worksheet"></param> /// <param name="items"></param> /// <param name="startRowIndex"></param> /// <param name="startColumnIndex"></param> /// <param name="configureCells"></param> /// <param name="propertySelectors"></param> /// <returns></returns> public static ExcelWorksheet AddObjects <T>(this ExcelWorksheet worksheet, IEnumerable <T> items, int startRowIndex, int startColumnIndex, Action <ExcelRange> configureCells = null, params Func <T, object>[] propertySelectors) { if (propertySelectors == null) { throw new ArgumentException($"{nameof(propertySelectors)} cannot be null"); } for (var i = 0; i < items.Count(); i++) { for (int j = startColumnIndex; j < (startColumnIndex + propertySelectors.Length); j++) { worksheet.AddLine(i + startRowIndex, j, configureCells, propertySelectors[j - startColumnIndex](items.ElementAt(i))); } } return(worksheet); }
public void Should_add_line_without_configuration() { //----------------------------------------------------------------------------------------------------------- // Arrange //----------------------------------------------------------------------------------------------------------- ExcelWorksheet worksheet = excelPackage1.GetWorksheet("TEST5"); //----------------------------------------------------------------------------------------------------------- // Act //----------------------------------------------------------------------------------------------------------- worksheet.AddLine(5, "barcode123", 5, DateTime.UtcNow); IEnumerable <StocksNullable> list = worksheet.ToList <StocksNullable>(); //----------------------------------------------------------------------------------------------------------- // Assert //----------------------------------------------------------------------------------------------------------- list.Count().Should().Be(4); }
public void Should_AddLine_method_work() { //----------------------------------------------------------------------------------------------------------- // Arrange //----------------------------------------------------------------------------------------------------------- ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["TEST5"]; //----------------------------------------------------------------------------------------------------------- // Act //----------------------------------------------------------------------------------------------------------- worksheet.AddLine(5, "barcode123", 5, DateTime.UtcNow); IEnumerable <StocksNullable> list = worksheet.ToList <StocksNullable>(true); //----------------------------------------------------------------------------------------------------------- // Assert //----------------------------------------------------------------------------------------------------------- list.Count().Should().Be(4); }
public void Should_add_line_with_configuration() { //----------------------------------------------------------------------------------------------------------- // Arrange //----------------------------------------------------------------------------------------------------------- ExcelWorksheet worksheet = excelPackage1.GetWorksheet("TEST5"); //----------------------------------------------------------------------------------------------------------- // Act //----------------------------------------------------------------------------------------------------------- worksheet.AddLine(5, configureCells => configureCells.SetBackgroundColor(Color.Yellow), "barcode123", 5, DateTime.UtcNow); IEnumerable <StocksNullable> list = worksheet.ToList <StocksNullable>(configuration => configuration .WithoutHeaderRow() .SkipCastingErrors()); //----------------------------------------------------------------------------------------------------------- // Assert //----------------------------------------------------------------------------------------------------------- worksheet.Cells[5, 1].Style.Fill.BackgroundColor.Rgb.Should().Be($"{Color.Yellow.ToArgb() & 0xFFFFFFFF:X8}"); list.Count().Should().Be(5); }
public void Should_AddLine_method_work() { //----------------------------------------------------------------------------------------------------------- // Arrange //----------------------------------------------------------------------------------------------------------- ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets["TEST5"]; //----------------------------------------------------------------------------------------------------------- // Act //----------------------------------------------------------------------------------------------------------- worksheet.AddLine(5, "barcode123", 5, DateTime.UtcNow); IEnumerable <StocksNullable> list = worksheet.ToList <StocksNullable>(null, configuration => { configuration.SkipCastingErrors = false; configuration.HasHeaderRow = true; }); //----------------------------------------------------------------------------------------------------------- // Assert //----------------------------------------------------------------------------------------------------------- list.Count().Should().Be(4); }
/// <summary> /// Adds a line to the worksheet /// </summary> /// <param name="worksheet"></param> /// <param name="rowIndex"></param> /// <param name="configureCells"></param> /// <param name="values"></param> /// <returns></returns> public static ExcelWorksheet AddLine(this ExcelWorksheet worksheet, int rowIndex, Action <ExcelRange> configureCells = null, params object[] values) => worksheet.AddLine(rowIndex, 1, configureCells, values);
/// <summary> /// Adds a line to the worksheet /// </summary> /// <param name="worksheet"></param> /// <param name="rowIndex"></param> /// <param name="values"></param> /// <returns></returns> public static ExcelWorksheet AddLine(this ExcelWorksheet worksheet, int rowIndex, params object[] values) => worksheet.AddLine(rowIndex, 1, null, values);
private void initFile(string path) { Microsoft.Office.Interop.Excel.Application excelApplication = null; Workbook excelWorkbook = null; try { excelApplication = new Microsoft.Office.Interop.Excel.Application(); excelWorkbook = excelApplication.Workbooks.Open(path); for (int i = 1; i <= excelWorkbook.Worksheets.Count; i++) { dynamic dynamicWorksheet = excelWorkbook.Worksheets[i]; Worksheet worksheet = excelWorkbook.Worksheets[i]; ExcelWorksheet excelWorksheet = new ExcelWorksheet(dynamicWorksheet.Name); Range lastRow = dynamicWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing); Range workingRange = worksheet.get_Range("A1", lastRow); int numberOfRows = workingRange.Rows.Count; for (int j = 2; j <= numberOfRows; j++) { dynamic currentRow = workingRange.Rows[j]; dynamic cell1 = currentRow.Cells[1]; dynamic val1 = cell1.Value; dynamic cell2 = currentRow.Cells[2]; dynamic val2 = cell2.Value; dynamic cell5 = currentRow.Cells[5]; dynamic val5 = cell5.Value; dynamic cell8 = currentRow.Cells[8]; dynamic val8 = cell8.Value; try { int noCrt = Convert.ChangeType(val1, typeof(int)); DateTime date = val2 == null ? DateTime.MinValue : this.getDateTime(val2); double factorDePutere = val5 == null ? 0 : Convert.ChangeType(val5, typeof(double)); double putereReactiva = val8 == null ? 0 : Convert.ChangeType(val8, typeof(double)); if (date >= this.startInterval && date <= this.endInterval) { ExcelLine excelLine = new ExcelLine(noCrt, date, factorDePutere, putereReactiva); excelWorksheet.AddLine(excelLine); this.appendLine(excelLine); this.sum(excelLine); } } catch (Exception ex) { Console.WriteLine(ex); } processWorker.ReportProgress(j * 100 / numberOfRows); } myWorkbook.AddWorkshets(excelWorksheet); if (i == 1) { break; } } } catch (Exception ex) { Console.WriteLine(ex); } finally { try { excelWorkbook.Close(SaveChanges: true); } catch { } excelApplication.Quit(); if (excelWorkbook != null) { Marshal.ReleaseComObject(excelWorkbook); } if (excelApplication != null) { Marshal.ReleaseComObject(excelApplication); } excelWorkbook = null; excelApplication = null; GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }