public void Excel_Context_Menu_Tests()
{
IExcelApp app = new ExcelApp();
app.CreateNewInstance();
IExcelWorkbook workbook = app.CreateAndActivateNewWorkbook();
IExcelSheet sheet = workbook.CreateSheet("Test");
MenuItem contextMenu = new MenuItem {
ItemType = MenuItemType.ContextMenu
};
contextMenu.Children.Add(new MenuItem {
Caption = "First Command", CommandId = "FC01"
});
contextMenu.Children.Add(new MenuItem {
ItemType = MenuItemType.Separator
});
contextMenu.Children.Add(new MenuItem {
Caption = "Second Command", CommandId = "FC01"
});
sheet.SetContextMenu(2, 2, contextMenu, "Application.Id");
}
public void InsertReportToNewSheet(List <TransactionReport> reports, IComparer <Transaction> transactionComparer)
{
//string code = Properties.Resources.VBAFunctionCode;
//workBook.AddVbaModule(code);
_app.SetScreenUpdating(false);
try
{
IExcelWorkbook workBook = _app.GetActiveWorkbook();
foreach (TransactionReport report in reports)
{
InsertSingleReport(workBook, report, transactionComparer);
}
_app.SetDisplayGridlines(false);
}
catch (Exception)
{
throw;
}
finally
{
_app.SetScreenUpdating(true);
}
}
public bool ApplyAnalysisAndSaveData(IEnumerable <int> data,
ExcelContextForPhaseOne <IAnalysisPhaseOnePartTwo> excelContext)
{
data.ThrowIfNullOrEmpty(nameof(data));
excelContext.ThrowIfNull(nameof(excelContext));
using IExcelWorkbook workbook =
ExcelHelper.GetOrCreateWorkbook(excelContext.OutputExcelFile);
IExcelSheet sheet = workbook.GetOrCreateSheet(excelContext.SheetName);
FillSheetHeader(sheet, excelContext.Args);
IAnalysisPhaseOnePartTwo analysis = excelContext.CreatePartNAnalysis();
int rowCounter = 2;
foreach (int item in data)
{
int currentRow = rowCounter++;
sheet[ExcelColumnIndex.A, currentRow].SetValue(item);
analysis.ApplyAnalysisToSingleLaunch(sheet, currentRow, item);
}
analysis.ApplyAnalysisToDataset(sheet);
workbook.SaveToFile(excelContext.OutputExcelFile);
excelContext.OutputExcelFile.Refresh();
return(analysis.CheckH0Hypothesis(sheet));
}
public ExcelWriter(IExcelWorkbook workbook)
{
if (workbook == null)
{
throw new ArgumentNullException("workbook");
}
this._workbook = workbook;
}
public void RefreshPivotTable()
{
_app.Connect();
IExcelWorkbook workbook = _app.GetActiveWorkbook();
IExcelSheet sheet = workbook.GetActiveSheet();
sheet.RefreshPivotRable("Test");
}
public void UpdateReportCategories(List <TransactionReport> reports)
{
IExcelWorkbook workBook = _app.GetActiveWorkbook();
foreach (TransactionReport report in reports)
{
UpdateSingleReport(workBook, report);
}
}
public void InsertPivotTableToSheet(int year, int month)
{
_app.Connect();
IExcelWorkbook workbook = _app.GetActiveWorkbook();
IExcelSheet sheet = workbook.GetActiveSheet();
ExcelSheetData sheetData = sheet.GetSheetData();
Transaction transaction = new Transaction();
int firstCol = Transaction.GetIndex(() => transaction.Owner);
int lastCol = Transaction.GetIndex(() => transaction.Amount);
int numberOfRows = sheetData.Rows.Count;
ExcelRangeInfo dataRange = new ExcelRangeInfo(1, firstCol, numberOfRows, lastCol + 1);
ExcelRangeInfo insertRange = new ExcelRangeInfo(numberOfRows + 2, firstCol);
string[] rows =
{
transaction.GetName(() => transaction.TransactionType),
transaction.GetName(() => transaction.Category),
transaction.GetName(() => transaction.SubCategory)
};
string[] columns =
{
transaction.GetName(() => transaction.Owner)
};
string[] values =
{
transaction.GetName(() => transaction.Amount)
};
PivotTableData data = new PivotTableData
{
Range = dataRange,
InsertCell = insertRange,
Columns = columns.ToList(),
Rows = rows.ToList(),
Values = values.ToList(),
Name = "Test01",
StyleName = "PivotStyleMedium2"
};
sheet.InsertPivotTable(data);
}
public void ApplyAnalysisAndSaveData(
ExcelContextForPhaseTwo <IAnalysisPhaseTwo> excelContext)
{
excelContext.ThrowIfNull(nameof(excelContext));
using IExcelWorkbook workbook =
ExcelHelper.GetOrCreateWorkbook(excelContext.OutputExcelFile);
IExcelSheet sheet = workbook.GetOrCreateSheet(excelContext.SheetName);
int currentColumnIndex = FillSheetHeader(sheet, excelContext.Args);
IAnalysisPhaseTwo analysis = excelContext.CreateAnalysis();
analysis.ApplyAnalysisToDataset(sheet, currentColumnIndex);
workbook.SaveToFile(excelContext.OutputExcelFile);
excelContext.OutputExcelFile.Refresh();
}
private void UpdateSingleReport(IExcelWorkbook workBook, TransactionReport report)
{
ExcelSheetData sheetData = new ExcelSheetData();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
ColorConverter converter = new ColorConverter();
int col_LAST = COL_FIRST + headers.Count - 1;
Transaction template = new Transaction();
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.Category));
int col_SUB_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.SubCategory));
int iTransactionRow = 2;
foreach (var transaction in report.Transactions)
{
ExcelCellData categoryData = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryData.Value = transaction.Category;
sheetData.Cells.Add(categoryData);
ExcelCellData subCategoryData = new ExcelCellData(iTransactionRow, col_SUB_CATEGORY);
subCategoryData.Value = transaction.SubCategory;
sheetData.Cells.Add(subCategoryData);
Color color = Color.White;
if (transaction.Color.IsNotNull())
{
color = (Color)converter.ConvertFromString(transaction.Color);
}
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = color;
sheetData.Formats.Add(colorFormat);
iTransactionRow++;
}
IExcelSheet sheet = workBook.GetSheet(report.Name);
sheet.InsertSheetData(sheetData);
}
public void ApplyAnalysisAndSaveDataOneIteration(IEnumerable <int> data,
ExcelContextForPhaseTwo <IAnalysisPhaseTwo> excelContext, string dataFilename)
{
data.ThrowIfNullOrEmpty(nameof(data));
excelContext.ThrowIfNull(nameof(excelContext));
dataFilename.ThrowIfNullOrWhiteSpace(nameof(dataFilename));
using IExcelWorkbook workbook =
ExcelHelper.GetOrCreateWorkbook(excelContext.OutputExcelFile);
IExcelSheet sheet = workbook.GetOrCreateSheet(excelContext.SheetName);
IAnalysisPhaseTwo analysis = excelContext.CreateAnalysis();
int iterationNumber = excelContext.Args.GetNumberOfIterationByFilename(dataFilename);
var currentColumn = iterationNumber.AsEnum <ExcelColumnIndex>();
int rowCounter = GetFirstDataRowIndex();
foreach (int item in data)
{
int currentRow = rowCounter++;
sheet[currentColumn, currentRow].SetValue(item);
analysis.ApplyAnalysisToSingleLaunch(sheet, currentColumn, currentRow, item);
}
if (rowCounter - 1 != GetLastDataRowIndex(excelContext.Args))
{
string message = "Too much data. Exceeded predefined place.";
throw new ArgumentException(message, nameof(data));
}
workbook.SaveToFile(excelContext.OutputExcelFile);
excelContext.OutputExcelFile.Refresh();
}
private void InsertSingleReport(IExcelWorkbook workBook, TransactionReport report, IComparer <Transaction> transactionComparer)
{
ExcelSheetData sheetData = new ExcelSheetData();
TransactionManager transactionManager = new TransactionManager();
ColorConverter converter = new ColorConverter();
var firstTransaction = report.Transactions.First();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, ROW_HEADERS, headers.ToArray()));
int col_LAST = COL_FIRST + headers.Count - 1;
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Category));
int col_AMOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Amount));
int col_ACCOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.AccountNumber));
int col_TRANSACTION_NUMBER = COL_FIRST +
headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.TransactionNumber));
int col_TRANSACTION_COMMENT = COL_FIRST + headers.IndexOf(
ReflectionTools.GetName(() => firstTransaction.Comment));
int iTransactionRow = 1;
foreach (var transaction in report.Transactions)
{
iTransactionRow++;
object[] values = ReflectionTools
.GetPropertyValues(transaction, headers).ToArray();
sheetData.Rows.Add(new ExcelRowData(COL_FIRST, iTransactionRow, values));
if (transaction.Color.IsNotNull())
{
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = (Color)converter.ConvertFromString(transaction.Color);
sheetData.Formats.Add(colorFormat);
}
}
int row_LAST_TRANSACTION = ROW_FIRST_TRANSACTION + report.Transactions.Count - 1;
int row_SUBTOTAL = row_LAST_TRANSACTION + 1;
int row_SummaryTable = row_SUBTOTAL + 2;
iTransactionRow = row_SummaryTable;
Tree <string> categoryTree = GetCategoryTree(report.Transactions);
foreach (var categoryItem in categoryTree.Children)
{
string category = categoryItem.Value;
var subCategories = categoryItem.Children.Select(i => i.Value);
foreach (string subCategory in subCategories)
{
ExcelCellData categoryCell = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryCell.Value = category;
ExcelCellData subCategoryCell = new ExcelCellData(iTransactionRow, COLUMN_SUB_CATEGORY);
subCategoryCell.Value = subCategory;
}
}
ExcelRangeInfo transactionsRange =
new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_CATEGORY, row_LAST_TRANSACTION, col_AMOUNT);
ExcelRangeInfo subTotalRange = new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_AMOUNT, row_LAST_TRANSACTION, col_AMOUNT);
ExcelFormula subTotalFormula = ExcelFormulaHelper.GetSubTotalSum(row_SUBTOTAL, col_AMOUNT, subTotalRange);
sheetData.Formulas.Add(subTotalFormula);
ExcelBorderData subTotalBorderData = new ExcelBorderData(subTotalFormula.Range);
subTotalBorderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(subTotalBorderData);
ExcelBorderData borderTable = new ExcelBorderData(COL_FIRST, ROW_HEADERS, col_LAST, row_LAST_TRANSACTION);
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlHairline, ExcelLineStyle.xlContinuous));
borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous));
sheetData.Borders.Add(borderTable);
ExcelRangeInfo columnAccountRange = ExcelRangeInfo.CreateColumnRange(col_ACCOUNT);
ExcelFormatData columnAccountFormat = new ExcelFormatData(columnAccountRange);
columnAccountFormat.NumberFormat = "@";
sheetData.Formats.Add(columnAccountFormat);
ExcelRangeInfo columnTransactionNumber = ExcelRangeInfo.CreateColumnRange(col_TRANSACTION_NUMBER);
ExcelFormatData columnTransactionFormat = new ExcelFormatData(columnTransactionNumber);
columnTransactionFormat.NumberFormat = "0";
sheetData.Formats.Add(columnTransactionFormat);
IExcelSheet sheet = workBook.CreateSheet(report.Name);
sheet.InsertSheetData(sheetData);
int columnCount = headers.Count;
sheet.SetColumnsAutoFit(1, columnCount);
sheet.SetAutoFilter(ROW_HEADERS, COL_FIRST, ROW_HEADERS, col_LAST);
}
public IExcelSheet GetSheet(string sheetName)
{
IExcelWorkbook workbook = _app.GetActiveWorkbook();
return(workbook.GetSheet(sheetName));
}
public List <string> GetSheetNames()
{
IExcelWorkbook workbook = _app.GetActiveWorkbook();
return(workbook.GetWorksheetNames());
}
public void GenerateSheets()
{
int YEAR = 2017;
object[] headers =
{
@"Project/Activity", "Feature", "Employee", "Comment", "Business Line (BL)",
"Time(date)", "Location", "Work Units"
};
string PATH = @"c:\Maciek\Timesheets\2017";
int COL_FIRST = 1;
int COL_LAST = 8;
int ROW_FIRST = 1;
int ROW_DATA_FIRST = 2;
ExcelApp excel = new ExcelApp();
excel.CreateNewInstance();
for (int m = 12; m >= 1; m--)
{
IExcelWorkbook workbook = excel.CreateAndActivateNewWorkbook();
string workBookName = $"Protokol_MSzczudlo_{YEAR}_{m:D2}.xlsx";
string workBookPath = Path.Combine(PATH, workBookName);
int daysInMonth = DateTime.DaysInMonth(YEAR, m);
int ROW_LAST = daysInMonth + 1;
int ROW_SUM = ROW_LAST + 1;
ExcelSheetData sheetData = new ExcelSheetData();
ExcelRowData headerRowData = new ExcelRowData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowData.Values = headers.ToList();
sheetData.Rows.Add(headerRowData);
ExcelFormatData headerRowFormat = new ExcelFormatData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowFormat.Background = Color.LightGray;
headerRowFormat.IsFontBold = true;
headerRowFormat.HorizontalAlignment = ExcelHorizontalAlignmentType.Center;
sheetData.Formats.Add(headerRowFormat);
for (int d = 1; d <= daysInMonth; d++)
{
object[] values = new object[8];
int row = d + 1;
DateTime iDay = new DateTime(YEAR, m, d);
values[5] = iDay.ToShortDateString();
bool isFreeDay = iDay.DayOfWeek == DayOfWeek.Saturday || iDay.DayOfWeek == DayOfWeek.Sunday;
if (isFreeDay)
{
ExcelFormatData iFormatData = new ExcelFormatData(COL_FIRST, row, COL_LAST, row);
iFormatData.Background = Color.Salmon;
sheetData.Formats.Add(iFormatData);
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
else
{
values[0] = "IS Treasury";
values[2] = "Maciej Szczudło";
values[7] = 8;
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
ExcelBorderData borderData = new ExcelBorderData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_LAST);
borderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
borderData.Borders.AddBorder(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderData);
ExcelFormatData fontFormatData = new ExcelFormatData(borderData.Range);
fontFormatData.FontSize = 10;
sheetData.Formats.Add(fontFormatData);
}
string monthName = DateTimeTools.GetMonthName(m);
string sheetName = $"{monthName} {YEAR}";
IExcelSheet newSheet = workbook.CreateSheet(sheetName);
ExcelRangeInfo sumRange = new ExcelRangeInfo(ROW_DATA_FIRST, COL_LAST, ROW_LAST, COL_LAST);
ExcelFormula formula = ExcelFormulaHelper.GetSum(ROW_SUM, COL_LAST, sumRange);
sheetData.Formulas.Add(formula);
ExcelBorderData borderSumData = new ExcelBorderData(formula.Range);
borderSumData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderSumData);
newSheet.InsertSheetData(sheetData);
newSheet.SetColumnsAutoFit(COL_FIRST, COL_LAST);
newSheet.SetCellName(ROW_SUM, COL_LAST, "godziny");
excel.SetDisplayGridlines(false);
workbook.Save(workBookPath);
}
}
/// <summary>
/// Initialize a new <see cref="T:Dt.Xls.ExcelWorksheetCollection" /> with specified <see cref="T:Dt.Xls.IExcelWorkbook" /> owner.
/// </summary>
/// <param name="owner"></param>
public ExcelWorksheetCollection(IExcelWorkbook owner)
{
this._owner = owner;
}
