Exemplo n.º 1
0
        /// <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 == "&nbsp;")
                    {
                        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);
        }
Exemplo n.º 3
0
        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();
        }
Exemplo n.º 4
0
        /// <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);
            }
        }
Exemplo n.º 5
0
        /// <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 == "&nbsp;")
                    {
                        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);
        }