458 lines
22 KiB
C#
458 lines
22 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Reflection;
|
|
|
|
namespace bdf
|
|
{
|
|
public class Row //Generic Type 2
|
|
{
|
|
public Int32 Row_Number { get; set; } = 1;
|
|
public Int32 Record_Type { get; set; } = 2;
|
|
public Int32 Contractor_Billing_Account_Number { get; set; } = 0; // This will change for each service type
|
|
public String Invoice_Number { get; set; } = "INV X";
|
|
public Int32 Invoice_Period { get; set; } = 0;
|
|
public String Canada_Contract_Identifier { get; set; } = "X";
|
|
public String Contractor { get; set; } = "ROGERS";
|
|
public Int32 SSC_Order_Number { get; set; } = 0;
|
|
public Int32 SSC_Version { get; set; } = 0;
|
|
public Int32 SSC_Seq_No { get; set; } = 1;
|
|
public String SDPID { get; set; } = "X";
|
|
public String Department { get; set; } = "X";
|
|
public String Prov { get; set; } = "X";
|
|
public String Service_Project_ID { get; set; } = "";
|
|
public Int32 Billing_Effective_Date { get; set; } = 0;
|
|
public Int32 Billing_End_Date { get; set; } = 0;
|
|
public Int32 Billing_Cancel_Date { get; set; } = 0;
|
|
public Int32 Period_of_Service { get; set; } = 0;
|
|
public String SCID { get; set; } = "X";
|
|
public Int32 Quantity { get; set; } = 1;
|
|
public Double Recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Double Total_Recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Double Non_recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Double Total_Non_recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Double Total_Service_Credits { get; set; } = 0;
|
|
public Double Total_Other_Charges_and_Credits { get; set; } = 0;
|
|
public Double Total_GST_Amount { get; set; } = 0;
|
|
//public Double Total_PST_Amount { get; set; } = 0;
|
|
public Double Total_HST_Amount { get; set; } = 0;
|
|
public Double Total_QST_Amount { get; set; } = 0; // to be used for both PST and QST per call with Diana Aboud Feb 9, 2026
|
|
public Double Total_Taxes { get; set; } = 0;
|
|
public Double Total_Amount_including_taxes { get; set; } = 0;
|
|
public String CI_Name { get; set; } = "X";
|
|
public String CI_Name_2 { get; set; } = "";
|
|
public String TCID { get; set; } = "X";
|
|
public String PO_Number { get; set; } = "";
|
|
public String TA_Number { get; set; } = "X";
|
|
|
|
//var copy = original.Clone<MyClass>();
|
|
public T Clone<T>() where T : class
|
|
{
|
|
return (T)this.MemberwiseClone();
|
|
}
|
|
}
|
|
|
|
public class Row2 // Generic Type 1
|
|
{
|
|
public Int32 Record_Type { get; set; } = 1;
|
|
public Int32 Invoice_Date { get; set; } = 0;
|
|
public Int32 Invoice_Period { get; set; } = 0;
|
|
public String Canada_Contract_Identifier { get; set; } = "X";
|
|
public String Contractor_Name { get; set; } = "X";
|
|
public Int32 Total_Recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Int32 Total_Non_Recurring_Charges_for_the_Month { get; set; } = 0;
|
|
public Int32 Total_Charges_Excluding_Taxes { get; set; } = 0;
|
|
public Int32 Total_Other_Charges_and_Credits { get; set; } = 0;
|
|
public Int32 Total_Service_Credits { get; set; } = 0;
|
|
public Int32 Total_Late_Payment_Amount { get; set; } = 0;
|
|
public Int32 Total_GST_Amount { get; set; } = 0;
|
|
//public Int32 Total_PST_Amount { get; set; } = 0;
|
|
public Int32 Total_HST_Amount { get; set; } = 0;
|
|
public Int32 Total_QST_Amount { get; set; } = 0;
|
|
public Int32 Total_Taxes { get; set; } = 0;
|
|
public Int32 Total_Amount_Including_Taxes { get; set; } = 0;
|
|
public Int32 Total_Usage_Charges { get; set; } = 0; // Added because of SIP
|
|
}
|
|
|
|
public class Row3 // SIP Type 3
|
|
{
|
|
public Int32 Line_Number { get; set; } = 1;
|
|
public Int32 Record_Type { get; set; } = 0;
|
|
public Int32 Contractor_Billling_Account_Number { get; set; } = 0;
|
|
public Int32 Invoice_Number { get; set; } = 0;
|
|
public Int32 Invoice_Period { get; set; } = 0;
|
|
public String Canada_Contract_Identifier { get; set; } = "X";
|
|
public String Contractor { get; set; } = "X";
|
|
public String Period_of_Service { get; set; } = "X";
|
|
public String Department { get; set; } = "X";
|
|
public String SCID { get; set; } = "X";
|
|
public Int32 Total_Usage_Charges_for_the_Month { get; set; } = 0;
|
|
public Int32 Total_Other_Charges_and_Credits { get; set; } = 0;
|
|
public Int32 Total_GST_Amount { get; set; } = 0;
|
|
//public Int32 Total_PST_Amount { get; set; } = 0;
|
|
public Int32 Total_HST_Amount { get; set; } = 0;
|
|
public Int32 Total_QST_Amount { get; set; } = 0;
|
|
public Int32 Total_Taxes { get; set; } = 0;
|
|
public Int32 Total_Amount_Including_Taxes { get; set; } = 0;
|
|
public String PO_Number { get; set; } = "X";
|
|
}
|
|
|
|
public class BDF
|
|
{
|
|
public static Spire.Xls.Workbook CreateBDF(string type)
|
|
{
|
|
if (type == "LDF" || type == "WAV")
|
|
return S_CreateBDF();
|
|
else if (type == "SIP")
|
|
return S_CreateBDFSIP();
|
|
else
|
|
return null;
|
|
}
|
|
|
|
public static Spire.Xls.Workbook S_CreateBDFSIP()
|
|
{
|
|
var workbook = new Spire.Xls.Workbook();
|
|
var worksheet1 = workbook.Worksheets.Add("BDF Type 1");
|
|
cleanSheets(ref workbook);
|
|
|
|
{
|
|
//Headers "SIP BDF Type 1"
|
|
|
|
worksheet1.Range[1, 1].Value = "Record Type";
|
|
worksheet1.Range[1, 2].Value = "Invoice Date";
|
|
worksheet1.Range[1, 3].Value = "Invoice Period";
|
|
worksheet1.Range[1, 4].Value = "Canada Contract Identifier";
|
|
worksheet1.Range[1, 5].Value = "Contractor";
|
|
worksheet1.Range[1, 6].Value = "Total Recurring Charges for the Month";
|
|
worksheet1.Range[1, 7].Value = "Total Non-Recurring Charges for the Month";
|
|
worksheet1.Range[1, 8].Value = "Total Charges Excluding Taxes";
|
|
worksheet1.Range[1, 9].Value = "Total Other Charges and Credits";
|
|
worksheet1.Range[1, 10].Value = "Total Service Credits";
|
|
worksheet1.Range[1, 11].Value = "Total Late Payment Amount";
|
|
worksheet1.Range[1, 12].Value = "Total GST Amount";
|
|
worksheet1.Range[1, 13].Value = "Total HST Amount";
|
|
worksheet1.Range[1, 14].Value = "Total QST Amount";
|
|
worksheet1.Range[1, 15].Value = "Total Taxes";
|
|
worksheet1.Range[1, 16].Value = "Total Amount Including Taxes";
|
|
worksheet1.Range[1, 17].Value = "Total Usage Charges"; // this line was the only delta
|
|
|
|
var worksheet2 = workbook.Worksheets.Add("BDF Type 2");
|
|
//Headers "BDF Type 2"
|
|
|
|
worksheet2.Range[1, 1].Value = "Line Number"; //slight change
|
|
worksheet2.Range[1, 2].Value = "Record Type";
|
|
worksheet2.Range[1, 3].Value = "Contractor Billing Account Number";
|
|
worksheet2.Range[1, 4].Value = "Invoice Number";
|
|
worksheet2.Range[1, 5].Value = "Invoice Period";
|
|
worksheet2.Range[1, 6].Value = "Canada Contract Identifier";
|
|
worksheet2.Range[1, 7].Value = "Contractor";
|
|
worksheet2.Range[1, 8].Value = "SSC Reference Number"; //slight change
|
|
worksheet2.Range[1, 9].Value = "SSC Version";
|
|
worksheet2.Range[1, 10].Value = "SSC Seq No";
|
|
worksheet2.Range[1, 11].Value = "SDPID";
|
|
worksheet2.Range[1, 12].Value = "Department";
|
|
worksheet2.Range[1, 13].Value = "Prov";
|
|
worksheet2.Range[1, 14].Value = "Service Project ID";
|
|
worksheet2.Range[1, 15].Value = "Billing Effective Date";
|
|
worksheet2.Range[1, 16].Value = "Billing End Date";
|
|
worksheet2.Range[1, 17].Value = "Billing Cancel Date";
|
|
worksheet2.Range[1, 18].Value = "Period of Service";
|
|
worksheet2.Range[1, 19].Value = "SCID";
|
|
worksheet2.Range[1, 20].Value = "Qty"; //slight change
|
|
worksheet2.Range[1, 21].Value = "Recurring Charges for the Month";
|
|
worksheet2.Range[1, 22].Value = "Total Recurring Charges for the Month";
|
|
worksheet2.Range[1, 23].Value = "Non-recurring Charges for the Month";
|
|
worksheet2.Range[1, 24].Value = "Total Non-recurring Charges for the Month";
|
|
worksheet2.Range[1, 25].Value = "Total Service Credits";
|
|
worksheet2.Range[1, 26].Value = "Total Other Charges and Credits";
|
|
worksheet2.Range[1, 27].Value = "Total GST Amount";
|
|
worksheet2.Range[1, 28].Value = "Total HST Amount";
|
|
worksheet2.Range[1, 29].Value = "Total QST Amount";
|
|
worksheet2.Range[1, 30].Value = "Total Taxes";
|
|
worksheet2.Range[1, 31].Value = "Total Amount including taxes";
|
|
worksheet2.Range[1, 32].Value = "CI Name";
|
|
worksheet2.Range[1, 33].Value = "CI Name 2";
|
|
worksheet2.Range[1, 34].Value = "TCID";
|
|
worksheet2.Range[1, 35].Value = "PO #"; //slight change
|
|
// worksheet2.Range[1, 36].Value = "TA Number"; //this line removed for SIP-2
|
|
|
|
var worksheet3 = workbook.Worksheets.Add("BDF Type 3");
|
|
//Headers "SIP BDF Type 3"
|
|
|
|
worksheet3.Range[1, 1].Value = "Line Number";
|
|
worksheet3.Range[1, 2].Value = "Record Type";
|
|
worksheet3.Range[1, 3].Value = "Contractor Billing Account Number";
|
|
worksheet3.Range[1, 4].Value = "Invoice Number";
|
|
worksheet3.Range[1, 5].Value = "Invoice Period";
|
|
worksheet3.Range[1, 6].Value = "Canada Contract Identifier";
|
|
worksheet3.Range[1, 7].Value = "Contractor";
|
|
worksheet3.Range[1, 8].Value = "Period of Service";
|
|
worksheet3.Range[1, 9].Value = "Department";
|
|
worksheet3.Range[1, 10].Value = "SCID";
|
|
worksheet3.Range[1, 11].Value = "Total Usage Charges for the Month";
|
|
worksheet3.Range[1, 12].Value = "Total Other Charges and Credits";
|
|
worksheet3.Range[1, 13].Value = "Total GST Amount";
|
|
worksheet3.Range[1, 14].Value = "Total HST Amount";
|
|
worksheet3.Range[1, 15].Value = "Total QST Amount";
|
|
worksheet3.Range[1, 16].Value = "Total Taxes";
|
|
worksheet3.Range[1, 17].Value = "Total Amount including taxes";
|
|
worksheet3.Range[1, 18].Value = "PO Number";
|
|
}
|
|
|
|
Logger.Log(0, "Excel BDF template file created! {0}", bdf.output_path + bdf.filename);
|
|
return workbook;
|
|
}
|
|
|
|
public static Spire.Xls.Workbook S_CreateBDF()
|
|
{
|
|
var workbook = new Spire.Xls.Workbook();
|
|
var worksheet1 = workbook.Worksheets.Add("BDF Type 1");
|
|
cleanSheets(ref workbook);
|
|
|
|
{
|
|
//Headers "BDF Type 1"
|
|
|
|
worksheet1.Range[1, 1].Value = "Record Type";
|
|
worksheet1.Range[1, 2].Value = "Invoice Date";
|
|
worksheet1.Range[1, 3].Value = "Invoice Period";
|
|
worksheet1.Range[1, 4].Value = "Canada Contract Identifier";
|
|
worksheet1.Range[1, 5].Value = "Contractor Name";
|
|
worksheet1.Range[1, 6].Value = "Total Recurring Charges for the Month";
|
|
worksheet1.Range[1, 7].Value = "Total Non-Recurring Charges for the Month";
|
|
worksheet1.Range[1, 8].Value = "Total Charges Excluding Taxes";
|
|
worksheet1.Range[1, 9].Value = "Total Other Charges and Credits";
|
|
worksheet1.Range[1, 10].Value = "Total Service Credits";
|
|
worksheet1.Range[1, 11].Value = "Total Late Payment Amount";
|
|
worksheet1.Range[1, 12].Value = "Total GST Amount";
|
|
worksheet1.Range[1, 13].Value = "Total HST Amount";
|
|
worksheet1.Range[1, 14].Value = "Total QST Amount";
|
|
worksheet1.Range[1, 15].Value = "Total Taxes";
|
|
worksheet1.Range[1, 16].Value = "Total Amount Including Taxes";
|
|
|
|
|
|
var worksheet2 = workbook.Worksheets.Add("BDF Type 2");
|
|
//Headers "BDF Type 2"
|
|
|
|
worksheet2.Range[1, 1].Value = "Row Number";
|
|
worksheet2.Range[1, 2].Value = "Record Type";
|
|
worksheet2.Range[1, 3].Value = "Contractor Billing Account Number";
|
|
worksheet2.Range[1, 4].Value = "Invoice Number";
|
|
worksheet2.Range[1, 5].Value = "Invoice Period";
|
|
worksheet2.Range[1, 6].Value = "Canada Contract Identifier";
|
|
worksheet2.Range[1, 7].Value = "Contractor";
|
|
worksheet2.Range[1, 8].Value = "SSC Order Number";
|
|
worksheet2.Range[1, 9].Value = "SSC Version";
|
|
worksheet2.Range[1, 10].Value = "SSC Seq No";
|
|
worksheet2.Range[1, 11].Value = "SDPID";
|
|
worksheet2.Range[1, 12].Value = "Department";
|
|
worksheet2.Range[1, 13].Value = "Prov";
|
|
worksheet2.Range[1, 14].Value = "Service Project ID";
|
|
worksheet2.Range[1, 15].Value = "Billing Effective Date";
|
|
worksheet2.Range[1, 16].Value = "Billing End Date";
|
|
worksheet2.Range[1, 17].Value = "Billing Cancel Date";
|
|
worksheet2.Range[1, 18].Value = "Period of Service";
|
|
worksheet2.Range[1, 19].Value = "SCID";
|
|
worksheet2.Range[1, 20].Value = "Quantity";
|
|
worksheet2.Range[1, 21].Value = "Recurring Charges for the Month";
|
|
worksheet2.Range[1, 22].Value = "Total Recurring Charges for the Month";
|
|
worksheet2.Range[1, 23].Value = "Non-recurring Charges for the Month";
|
|
worksheet2.Range[1, 24].Value = "Total Non-recurring Charges for the Month";
|
|
worksheet2.Range[1, 25].Value = "Total Service Credits";
|
|
worksheet2.Range[1, 26].Value = "Total Other Charges and Credits";
|
|
worksheet2.Range[1, 27].Value = "Total GST Amount";
|
|
worksheet2.Range[1, 28].Value = "Total HST Amount";
|
|
worksheet2.Range[1, 29].Value = "Total QST Amount";
|
|
worksheet2.Range[1, 30].Value = "Total Taxes";
|
|
worksheet2.Range[1, 31].Value = "Total Amount including taxes";
|
|
worksheet2.Range[1, 32].Value = "CI Name";
|
|
worksheet2.Range[1, 33].Value = "CI Name 2";
|
|
worksheet2.Range[1, 34].Value = "TCID";
|
|
worksheet2.Range[1, 35].Value = "PO Number";
|
|
worksheet2.Range[1, 36].Value = "TA Number";
|
|
|
|
}
|
|
|
|
Logger.Log(0, "Excel BDF template file created! {0}", bdf.output_path + bdf.filename);
|
|
return workbook;
|
|
}
|
|
|
|
|
|
public static void S_InsertData<T>(Spire.Xls.Worksheet worksheet, List<T> items)
|
|
{
|
|
/// <summary>
|
|
/// Inserts a list of objects (T) into a worksheet with headers.
|
|
/// Each property becomes a column, each object becomes a row.
|
|
/// </summary>
|
|
|
|
if (worksheet == null)
|
|
throw new ArgumentNullException(nameof(worksheet));
|
|
if (items == null)
|
|
throw new ArgumentNullException(nameof(items));
|
|
if (items.Count == 0)
|
|
return;
|
|
|
|
// Get properties of T
|
|
PropertyInfo[] props = typeof(T).GetProperties(
|
|
BindingFlags.Public | BindingFlags.Instance);
|
|
|
|
// No properties → nothing to insert
|
|
if (props.Length == 0)
|
|
return;
|
|
|
|
|
|
int startRow = 1; // Header row
|
|
int startCol = 1;
|
|
|
|
// -----------------------------------------
|
|
// Write header row
|
|
// -----------------------------------------
|
|
/*
|
|
for (int col = 0; col < props.Length; col++)
|
|
{
|
|
Spire.Xls.CellRange cell = worksheet.Range[startRow, startCol + col];
|
|
cell.Text = props[col].Name;
|
|
cell.Style.Font.IsBold = true;
|
|
}
|
|
*/
|
|
|
|
// -----------------------------------------
|
|
// Write data rows
|
|
// -----------------------------------------
|
|
for (int row = 0; row < items.Count; row++)
|
|
{
|
|
T item = items[row];
|
|
int excelRow = startRow + 1 + row;
|
|
|
|
for (int col = 0; col < props.Length; col++)
|
|
{
|
|
Spire.Xls.CellRange cell = worksheet.Range[excelRow, startCol + col];
|
|
object value = props[col].GetValue(item);
|
|
|
|
if (value == null)
|
|
{
|
|
cell.Text = string.Empty;
|
|
continue;
|
|
}
|
|
|
|
// Write numeric values as numbers. either Int32 or Double
|
|
if (
|
|
value is byte || value is short || value is int // ||
|
|
//value is long || value is float || value is double || value is decimal
|
|
)
|
|
{
|
|
cell.NumberValue = Convert.ToInt32(value);
|
|
}
|
|
else if (
|
|
//value is byte || value is short || value is int ||
|
|
value is long || value is float || value is double || value is decimal
|
|
)
|
|
{
|
|
cell.NumberValue = Convert.ToDouble(value);
|
|
}
|
|
else
|
|
{
|
|
cell.Text = value.ToString();
|
|
}
|
|
}
|
|
}
|
|
// Total number of rows written (header + items)
|
|
int totalRows = 1 + items.Count;
|
|
int totalCols = props.Length;
|
|
|
|
// ------------------------------------------------------
|
|
// Create a table format
|
|
// ------------------------------------------------------
|
|
|
|
Spire.Xls.CellRange tableRange = worksheet.Range[startRow, startCol, totalRows, totalCols];
|
|
ApplyTableFormat(tableRange);
|
|
|
|
// ------------------------------------------
|
|
// Autofit column widths to contents
|
|
// ------------------------------------------
|
|
//worksheet.AllocatedRange.AutoFitColumns();
|
|
|
|
}
|
|
|
|
/*
|
|
/// Applies "table-style" formatting to a specified range:
|
|
/// - Bold gray header row
|
|
/// - Bordered cells
|
|
/// - Alternating (banded) row background colors
|
|
/// Fully compatible with FreeSpire.XLS.
|
|
/// </summary>
|
|
/// <param name="range">Spire.Xls.CellRange to format</param>
|
|
///
|
|
// Spire.Xls.Worksheet sheet = wb.Worksheets[0];
|
|
|
|
// Define a range with header + rows
|
|
// Spire.Xls.CellRange tableRange = sheet.Range["A1:D10"];
|
|
|
|
// Apply table styling
|
|
// TableFormatter.ApplyTableFormat(tableRange);
|
|
*/
|
|
|
|
public static void ApplyTableFormat(Spire.Xls.CellRange range)
|
|
{
|
|
if (range == null)
|
|
throw new System.ArgumentNullException("range");
|
|
|
|
int rowCount = range.RowCount;
|
|
int colCount = range.ColumnCount;
|
|
|
|
if (rowCount == 0 || colCount == 0)
|
|
return;
|
|
|
|
// ---------------------------
|
|
// 1. Format Header Row
|
|
// ---------------------------
|
|
for (int col = 0; col < colCount; col++)
|
|
{
|
|
Spire.Xls.CellRange headerCell =
|
|
range[1, col + 1]; // 1-based indexing inside CellRange segment
|
|
|
|
headerCell.Style.Font.IsBold = true;
|
|
headerCell.Style.Color = System.Drawing.Color.LightGray;
|
|
headerCell.Style.Borders.LineStyle = Spire.Xls.LineStyleType.Thin;
|
|
|
|
headerCell.Borders[Spire.Xls.BordersLineType.DiagonalDown].LineStyle = Spire.Xls.LineStyleType.None;
|
|
headerCell.Borders[Spire.Xls.BordersLineType.DiagonalUp].LineStyle = Spire.Xls.LineStyleType.None;
|
|
}
|
|
|
|
// ---------------------------
|
|
// 2. Format Data Rows (banded)
|
|
// ---------------------------
|
|
for (int row = 2; row <= rowCount; row++)
|
|
{
|
|
bool isEven = (row % 2 == 0);
|
|
System.Drawing.Color rowColor = isEven
|
|
? System.Drawing.Color.FromArgb(245, 245, 245) // light gray band
|
|
: System.Drawing.Color.White;
|
|
|
|
for (int col = 1; col <= colCount; col++)
|
|
{
|
|
Spire.Xls.CellRange cell = range[row, col];
|
|
|
|
cell.Style.Color = rowColor;
|
|
cell.Style.Borders.LineStyle = Spire.Xls.LineStyleType.Thin;
|
|
|
|
cell.Borders[Spire.Xls.BordersLineType.DiagonalDown].LineStyle = Spire.Xls.LineStyleType.None;
|
|
cell.Borders[Spire.Xls.BordersLineType.DiagonalUp].LineStyle = Spire.Xls.LineStyleType.None;
|
|
}
|
|
}
|
|
|
|
// ---------------------------
|
|
// 3. Auto-fit column widths
|
|
// ---------------------------
|
|
range.AutoFitColumns();
|
|
}
|
|
|
|
public static void cleanSheets(ref Spire.Xls.Workbook workbook)
|
|
{
|
|
// Spire worksheets come with 3 default sheets, Sheet1, Sheet2 and Sheet3
|
|
// So we pop them off the stack, one at a time, the first time we add our own sheet to the Workbook
|
|
workbook.Worksheets.RemoveAt(0);
|
|
workbook.Worksheets.RemoveAt(0);
|
|
workbook.Worksheets.RemoveAt(0);
|
|
}
|
|
|
|
}
|
|
} |