Ejemplo n.º 1
0
    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");
    }
Ejemplo n.º 2
0
    static void Main()
    {
        // If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();

        // Always print 1st row.
        var worksheet1 = workbook.Worksheets.Add("Sheet1");

        worksheet1.NamedRanges.SetPrintTitles(worksheet1.Rows[0], 1);

        // Set print area (from A1 to I120):
        worksheet1.NamedRanges.SetPrintArea(worksheet1.Cells.GetSubrange("A1", "I120"));

        // Always print columns from A to F.
        var worksheet2 = workbook.Worksheets.Add("Sheet2");

        worksheet2.NamedRanges.SetPrintTitles(worksheet2.Columns[0], 6);

        // Always print columns from A to F and first row.
        var worksheet3 = workbook.Worksheets.Add("Sheet3");

        worksheet3.NamedRanges.SetPrintTitles(worksheet3.Rows[0], 1, worksheet3.Columns[0], 6);

        // Fill Sheet1 with some data.
        for (int i = 0; i < 9; i++)
        {
            worksheet1.Cells[0, i].Value = "Column " + ExcelColumnCollection.ColumnIndexToName(i);
        }

        for (int i = 1; i < 120; i++)
        {
            for (int j = 0; j < 9; j++)
            {
                worksheet1.Cells[i, j].SetValue(i + j);
            }
        }

        workbook.Save("Print Titles and Area.xlsx");
    }
Ejemplo n.º 3
0
        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);
        }