/// <summary>
/// save a generic CSV string to an Excel file
/// </summary>
public static bool CSV2ExcelStream(string ACSVData, MemoryStream AStream, string ASeparator = ",", string ATableName = "data")
{
try
{
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add(ATableName);
Int32 rowCounter = 1;
Int16 colCounter = 1;
// we don't have headers for the columns
List <String> Lines = ACSVData.Split(Environment.NewLine).ToList();
Int32 LineCounter = 0;
while (LineCounter < Lines.Count)
{
string line = Lines[LineCounter];
while (line.Trim().Length > 0)
{
string value = StringHelper.GetNextCSV(ref line, Lines, ref LineCounter, ASeparator);
TVariant v = new TVariant(value);
if (v.TypeVariant == eVariantTypes.eDecimal)
{
worksheet.Cells[rowCounter, colCounter].Value = v.ToDecimal();
}
else if (v.TypeVariant == eVariantTypes.eInteger)
{
worksheet.Cells[rowCounter, colCounter].Value = v.ToInt32();
}
else if (v.TypeVariant == eVariantTypes.eDateTime)
{
worksheet.Cells[rowCounter, colCounter].Value = v.ToDate();
}
else
{
worksheet.Cells[rowCounter, colCounter].Value = value;
}
colCounter++;
}
LineCounter++;
rowCounter++;
colCounter = 1;
}
pck.SaveAs(AStream);
return(true);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(false);
}
}
/// <summary>
/// Create a Calc file from the HTML
/// </summary>
public static ExcelPackage HTMLToCalc(HtmlDocument html)
{
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add("Data Export");
// write the column headings
var elements = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='column_headings']/div");
int colCounter = 1;
int rowCounter = 3;
foreach (var element in elements)
{
worksheet.Cells[rowCounter, colCounter].Value = element.InnerText;
worksheet.Cells[rowCounter, colCounter].Style.Font.Bold = true;
colCounter++;
}
rowCounter += 2;
var rows = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='content']//div[contains(@class, 'row')]");
foreach (var row in rows)
{
colCounter = 1;
elements = HTMLTemplateProcessor.SelectNodes(row, ".//div[contains(@class, 'col-')]");
foreach (var element in elements)
{
string value = element.InnerText;
if (value == " ")
{
value = String.Empty;
}
if (element.HasClass("currency"))
{
TVariant v = new TVariant(value);
worksheet.Cells[rowCounter, colCounter].Value = v.ToDecimal();
}
else if (element.HasClass("date"))
{
TVariant v = new TVariant(value);
worksheet.Cells[rowCounter, colCounter].Value = v.ToDate();
worksheet.Cells[rowCounter, colCounter].Style.Numberformat.Format = "dd/mm/yyyy";
}
else
{
worksheet.Cells[rowCounter, colCounter].Value = value;
}
if (element.InnerHtml.Contains("<strong>"))
{
worksheet.Cells[rowCounter, colCounter].Style.Font.Bold = true;
}
colCounter++;
}
rowCounter++;
}
worksheet.Cells.AutoFitColumns();
return(pck);
}
private static void CalculateData(ref HtmlDocument html, DataTable balances, DataTable transactions, HTMLTemplateProcessor templateProcessor)
{
var balanceTemplate = HTMLTemplateProcessor.SelectSingleNode(html.DocumentNode, "//div[@id='costcentreaccount_template']");
var balanceParentNode = balanceTemplate.ParentNode;
int countBalanceRow = 0;
int countTransactionRow = 0;
foreach (DataRow balance in balances.Rows)
{
// skip account/costcentre combination if there are no transations and the balance is 0
if ((Decimal)balance["end_balance"] == 0.0m)
{
bool transactionExists = false;
foreach (DataRow transaction in transactions.Rows)
{
if ((transaction["a_account_code_c"].ToString() == balance["a_account_code_c"].ToString()) &&
(transaction["a_cost_centre_code_c"].ToString() == balance["a_cost_centre_code_c"].ToString()))
{
transactionExists = true;
break;
}
}
if (!transactionExists)
{
continue;
}
}
templateProcessor.AddParametersFromRow(balance);
var newBalanceRow = balanceTemplate.Clone();
string balanceId = "acccc" + countBalanceRow.ToString();
newBalanceRow.SetAttributeValue("id", balanceId);
balanceParentNode.AppendChild(newBalanceRow);
countBalanceRow++;
var FooterDiv = HTMLTemplateProcessor.SelectSingleNode(newBalanceRow, ".//div[@class='row']");
Decimal TotalCredit = 0.0m;
Decimal TotalDebit = 0.0m;
foreach (DataRow transaction in transactions.Rows)
{
if (!((transaction["a_account_code_c"].ToString() == balance["a_account_code_c"].ToString()) &&
(transaction["a_cost_centre_code_c"].ToString() == balance["a_cost_centre_code_c"].ToString())))
{
continue;
}
TVariant amount = new TVariant(transaction["a_transaction_amount_n"]);
if ((bool)transaction["a_debit_credit_indicator_l"])
{
TotalDebit += amount.ToDecimal();
}
else
{
TotalCredit += amount.ToDecimal();
}
countTransactionRow++;
}
templateProcessor.SetParameter("total_debit", new TVariant(TotalDebit));
templateProcessor.SetParameter("total_credit", new TVariant(TotalCredit));
FooterDiv.InnerHtml = templateProcessor.InsertParameters("{", "}", FooterDiv.InnerHtml,
HTMLTemplateProcessor.ReplaceOptions.NoQuotes);
}
balanceTemplate.Remove();
}
/// <summary>
/// save a generic CSV string to an Excel file
/// </summary>
public static bool CSV2ExcelStream(string ACSVData, MemoryStream AStream, string ASeparator = ",", string ATableName = "data")
{
try
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet worksheet = workbook.CreateSheet(ATableName);
IRow wsrow = null;
ICell wscell = null;
Int32 rowCounter = 1;
Int16 colCounter = 1;
// we don't have headers for the columns
List <String> Lines = ACSVData.Split(Environment.NewLine).ToList();
Int32 LineCounter = 0;
while (LineCounter < Lines.Count)
{
wsrow = worksheet.CreateRow(rowCounter);
string line = Lines[LineCounter];
while (line.Trim().Length > 0)
{
wscell = wsrow.CreateCell(colCounter);
string value = StringHelper.GetNextCSV(ref line, Lines, ref LineCounter, ASeparator);
TVariant v = new TVariant(value);
if (v.TypeVariant == eVariantTypes.eDecimal)
{
wscell.SetCellValue((double)v.ToDecimal());
}
else if (v.TypeVariant == eVariantTypes.eInteger)
{
wscell.SetCellValue(v.ToInt32());
}
else if (v.TypeVariant == eVariantTypes.eDateTime)
{
wscell.SetCellValue(v.ToDate());
}
else
{
wscell.SetCellValue(value);
}
colCounter++;
}
LineCounter++;
rowCounter++;
colCounter = 1;
}
workbook.Write(AStream);
return(true);
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return(false);
}
}
/// <summary>
/// Create an Excel file from the HTML
/// </summary>
public static XSSFWorkbook HTMLToCalc(HtmlDocument html)
{
XSSFWorkbook xssWorkbook = new XSSFWorkbook();
IRow wsrow = null;
ICell wscell = null;
ISheet worksheet = xssWorkbook.CreateSheet("Data Export");
ICellStyle wsstyle_bold = xssWorkbook.CreateCellStyle();
IFont wsfont = wsstyle_bold.GetFont(xssWorkbook);
wsfont.IsBold = true;
wsstyle_bold.SetFont(wsfont);
ICellStyle wsstyle_dateformat = xssWorkbook.CreateCellStyle();
ICreationHelper createHelper = xssWorkbook.GetCreationHelper();
wsstyle_dateformat.DataFormat = createHelper.CreateDataFormat().GetFormat("dd/mm/yyyy");
// write the column headings
var elements = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='column_headings']/div");
int colCounter = 1;
int rowCounter = 3;
wsrow = worksheet.CreateRow(rowCounter);
foreach (var element in elements)
{
wscell = wsrow.CreateCell(colCounter);
wscell.SetCellValue(element.InnerText);
wscell.CellStyle = wsstyle_bold;
colCounter++;
}
rowCounter += 2;
var rows = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='content']//div[contains(@class, 'row')]");
foreach (var row in rows)
{
wsrow = worksheet.CreateRow(rowCounter);
colCounter = 1;
elements = HTMLTemplateProcessor.SelectNodes(row, ".//div[contains(@class, 'col-')]");
foreach (var element in elements)
{
wscell = wsrow.CreateCell(colCounter);
string value = element.InnerText;
if (value == " ")
{
value = String.Empty;
}
if (element.HasClass("currency"))
{
TVariant v = new TVariant(value);
wscell.SetCellValue((double)v.ToDecimal());
}
else if (element.HasClass("date"))
{
TVariant v = new TVariant(value);
wscell.SetCellValue(v.ToDate());
wscell.CellStyle = wsstyle_dateformat;
}
else
{
wscell.SetCellValue(value);
}
if (element.InnerHtml.Contains("<strong>"))
{
wscell.CellStyle = wsstyle_bold;
}
colCounter++;
}
rowCounter++;
}
for (int colIndex = 1; colIndex < colCounter; colIndex++)
{
worksheet.AutoSizeColumn(colIndex);
}
return(xssWorkbook);
}
