static void Main()
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Formula Utility Methods");
// Fill first column with values.
for (int i = 0; i < 10; i++)
{
worksheet.Cells[i, 0].Value = i + 1;
}
// Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
for (int i = 0; i < 10; i++)
{
worksheet.Cells[i, 1].Formula = String.Format("={0}*2", CellRange.RowColumnToPosition(i, 0));
}
// Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
for (int i = 0; i < 10; i++)
{
worksheet.Cells[i, 2].Formula = String.Format("=SUM(A{0}:B{0})", ExcelRowCollection.RowIndexToName(i));
}
// Cell A12 contains sum of all values from the first row.
worksheet.Cells["A12"].Formula = String.Format("=SUM(A1:{0}1)", ExcelColumnCollection.ColumnIndexToName(worksheet.Rows[0].AllocatedCells.Count - 1));
workbook.Save("Formula Utility Methods.xlsx");
}
public List <ExcelProcessorMessage> ProcessExcelFile(ExcelFile ef)
{
var errorMessages = new List <ExcelProcessorMessage>();
var activeWorksheet = ef.Worksheets.ActiveWorksheet;
string output = string.Empty;
CellRange range = activeWorksheet.GetUsedCellRange(true);
var rowProcessed = 0;
for (int j = range.FirstRowIndex; j <= range.LastRowIndex; j++)
{
var transaction = new TransactionInput();
for (int i = range.FirstColumnIndex; i <= range.LastColumnIndex; i++)
{
ExcelCell cell = range[j - range.FirstRowIndex, i - range.FirstColumnIndex];
string cellName = CellRange.RowColumnToPosition(j, i);
string cellRow = ExcelRowCollection.RowIndexToName(j);
string cellColumn = ExcelColumnCollection.ColumnIndexToName(i);
if (cellColumn == "A")
{
transaction.Account = (cell.Value == null)? string.Empty : cell.Value.ToString();
}
if (cellColumn == "B")
{
transaction.Description = (cell.Value == null) ? string.Empty : cell.Value.ToString();
}
if (cellColumn == "C")
{
transaction.CurrencyCode = (cell.Value == null) ? string.Empty : cell.Value.ToString();
}
if (cellColumn == "D")
{
transaction.Amount = (cell.Value == null) ? string.Empty : cell.Value.ToString();
}
output += string.Format("Cell name: {1}{0}Cell row: {2}{0}Cell column: {3}{0}Cell value: {4}{0}",
Environment.NewLine, cellName, cellRow, cellColumn, (cell.Value) ?? "Empty");
}
rowProcessed++;
//ignore first transaction as that it caption
if (rowProcessed > 1)
{
var transactionStatus = _transactionProcessor.Process(transaction);
if (transactionStatus.Error)
{
errorMessages.Add(
new ExcelProcessorMessage()
{
Key = $"Record_{j}",
Message = transactionStatus.ErrorMessage,
IsErrored = true
}
);
}
else
{
errorMessages.Add(
new ExcelProcessorMessage()
{
Key = $"Record_{j}",
Message = "Record successfully processed",
IsErrored = false
}
);
}
}
}
return(errorMessages);
}