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