/// <summary>
/// Get Spreadsheet
/// </summary>
private static Spreadsheet _GetSpreadsheet(InvoiceData oInvoiceData)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.Workbook.AutoCalculation = true;
document.Workbook.DefaultFont = new SpreadsheetFont("Arial", 10);
// Add new worksheet
Worksheet Sheet = document.Workbook.Worksheets.Add("Invoice");
int startRow = 0;
// 1. Write Company Name
Sheet.Cell(startRow, 0).Value = oInvoiceData.CompanyName;
Sheet.Cell(startRow, 0).Font = new Font("Arial", 15, FontStyle.Bold | FontStyle.Italic);
// 2. Write Company Address
Sheet.Cell((++startRow), 0).Value = oInvoiceData.CompanyAddress1;
Sheet.Cell((++startRow), 0).Value = oInvoiceData.CompanyAddress2;
startRow++;
// 3. Write Invoice Info
Sheet.Cell((++startRow), 2).Value = "Invoice No.";
Sheet.Cell(startRow, 2).Font = new Font("Arial", 10, FontStyle.Bold);
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
Sheet.Cell(startRow, 3).Value = oInvoiceData.InvoiceNo;
Sheet.Cell((++startRow), 2).Value = "Invoice Date.";
Sheet.Cell(startRow, 2).Font = new Font("Arial", 10, FontStyle.Bold);
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
Sheet.Cell(startRow, 3).ValueAsDateTime = oInvoiceData.InvoiceDate;
Sheet.Cell(startRow, 3).NumberFormatString = "mm/dd/yyyy";
// 4. Write Client Info
Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientName;
Sheet.Cell(startRow, 0).Font = new Font("Arial", 12, FontStyle.Bold);
Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientAddress1;
Sheet.Cell((++startRow), 0).Value = oInvoiceData.ClientAddress2;
startRow++;
// 5. Write Notes
Sheet.Cell((++startRow), 0).Value = "Notes";
Sheet.Cell(startRow, 0).Font = new Font("Arial", 12, FontStyle.Bold);
Sheet.Cell((++startRow), 0).Value = oInvoiceData.Notes;
startRow += 2;
// 6. Add Product Listing
string startAddress = "";
string endAddress = "";
Sheet.Cell((++startRow), 0).Value = "Item";
_AddAllBorders(Sheet.Cell(startRow, 0));
Sheet.Cell(startRow, 0).Font = new Font("Arial", 11, FontStyle.Bold);
Sheet.Cell(startRow, 1).Value = "Quantity";
_AddAllBorders(Sheet.Cell(startRow, 1));
Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
Sheet.Cell(startRow, 1).Font = new Font("Arial", 11, FontStyle.Bold);
Sheet.Cell(startRow, 2).Value = "Price";
_AddAllBorders(Sheet.Cell(startRow, 2));
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
Sheet.Cell(startRow, 2).Font = new Font("Arial", 11, FontStyle.Bold);
Sheet.Cell(startRow, 3).Value = "Total";
_AddAllBorders(Sheet.Cell(startRow, 3));
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
Sheet.Cell(startRow, 3).Font = new Font("Arial", 11, FontStyle.Bold);
for (int i = 0; i < oInvoiceData.lstProducts.Count; i++)
{
Sheet.Cell((++startRow), 0).Value = oInvoiceData.lstProducts[i].ProductName;
_AddAllBorders(Sheet.Cell(startRow, 0));
Sheet.Cell(startRow, 1).Value = oInvoiceData.lstProducts[i].Quantity;
Sheet.Cell(startRow, 1).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
_AddAllBorders(Sheet.Cell(startRow, 1));
Sheet.Cell(startRow, 2).Value = oInvoiceData.lstProducts[i].Price;
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
_AddAllBorders(Sheet.Cell(startRow, 2));
// Calculated cell
string formulaMultiplication = string.Format("={0}*{1}", Sheet.Cell(startRow, 1).GetAddress().ToString(), Sheet.Cell(startRow, 2).GetAddress().ToString());
Sheet.Cell(startRow, 3).Formula = formulaMultiplication;
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
_AddAllBorders(Sheet.Cell(startRow, 3));
// Address to be used for total sum
if (i == 0)
{
startAddress = Sheet.Cell(startRow, 3).GetAddress().ToString();
}
endAddress = Sheet.Cell(startRow, 3).GetAddress().ToString();
}
Sheet.Cell((++startRow), 2).Value = "TOTAL";
Sheet.Cell(startRow, 2).Font = new Font("Arial", 11, FontStyle.Bold);
Sheet.Cell(startRow, 2).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
string formulaTotalSum = string.Format("=SUM({0}:{1})", startAddress, endAddress);
Sheet.Cell(startRow, 3).Formula = formulaTotalSum;
Sheet.Cell(startRow, 3).Font = new Font("Arial", 11, FontStyle.Bold);
Sheet.Cell(startRow, 3).AlignmentHorizontal = Bytescout.Spreadsheet.Constants.AlignmentHorizontal.Right;
// AutoFit all columns
Sheet.Columns[0].AutoFit();
Sheet.Columns[1].AutoFit();
Sheet.Columns[2].AutoFit();
Sheet.Columns[3].AutoFit();
// Return all formatted document
return(document);
}
