logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Benbabu  
#1 Posted : Wednesday, September 11, 2019 5:13:54 AM(UTC)
Benbabu

Rank: Advanced Member

Groups: Registered
Joined: 8/9/2019(UTC)
Posts: 45
India

Thanks: 13 times
Hi,

We are using the Evaluation version of the Excel/ PDF converter for now.

However Excel does not show custom colors, HTML text or images defined in the reports.

Is it a limitation of the Evaluation version ?

Edited by user Wednesday, September 11, 2019 5:21:23 AM(UTC)  | Reason: Not specified

epf  
#2 Posted : Wednesday, September 11, 2019 6:55:07 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
The Excel conversion implementation offers the tables, the charts (with conversion to excel chart when possible).
All other options like colors, fonts, tec. have to be configured manually in the scripts...

Please check https://sealreport.org/Reports#lineConverter_2 and play with the EPPlus library.
Benbabu  
#3 Posted : Tuesday, September 17, 2019 4:10:43 AM(UTC)
Benbabu

Rank: Advanced Member

Groups: Registered
Joined: 8/9/2019(UTC)
Posts: 45
India

Thanks: 13 times
But I suppose, Advanced cell script (conditional formatting) cannot be included in the Excel conversion template for the model.
Its showing the errors -
"ReportCell not found"
"cell not recognized in current context"

Is there an alternate for this ?
epf  
#4 Posted : Tuesday, September 17, 2019 5:53:44 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
You can use ResultCell and all the objects of Seal Report: make sure that you included:
@using Seal.Model

so you may have in the Data Table script:
Seal.Model.ResultCell rc = page.DataTable[row, col];

Then change your cell formatting based on the cell values...
check: https://github.com/JanKa...i/Formatting-and-styling

Edited by user Tuesday, September 17, 2019 5:54:38 AM(UTC)  | Reason: Not specified

thanks 1 user thanked epf for this useful post.
Benbabu on 10/21/2019(UTC)
Benbabu  
#5 Posted : Wednesday, December 11, 2019 9:26:52 AM(UTC)
Benbabu

Rank: Advanced Member

Groups: Registered
Joined: 8/9/2019(UTC)
Posts: 45
India

Thanks: 13 times
Hi Eric,

From the help links shared in the script editors, we were unable to code the necessary scripts to format the color, font, colspan etc. for the Excel converter.

We would require your urgent assistance , as to where and how these can be applied. Request you to please guide us.

Please confirm if you need more info on our requirements.

Edited by user Wednesday, December 11, 2019 12:40:04 PM(UTC)  | Reason: Not specified

Benbabu  
#6 Posted : Thursday, December 12, 2019 11:20:38 AM(UTC)
Benbabu

Rank: Advanced Member

Groups: Registered
Joined: 8/9/2019(UTC)
Posts: 45
India

Thanks: 13 times
Hi Team,

Could you please help us with this subject? We need your expertise on it, as it has kind of developed into a road-blocker for us towards project delivery.

Thank you!!
epf  
#7 Posted : Thursday, December 12, 2019 12:57:41 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Here are the samples got from https://github.com/JanKa...i/Formatting-and-styling you can use in your scripts:
Code:
    //Value
    converter.Sheet.Cells[1,1].Value = "a value";
    //Format
    converter.Sheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
    using (var range = converter.Sheet.Cells[1, 1, 1, 5])  //Address "A1:A5"
    {
        range.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkBlue);
        range.Style.Font.Color.SetColor(System.Drawing.Color.White);
    }
    //Add a HyperLink to the statistics sheet. 
    var namedStyle = converter.Book.Styles.CreateNamedStyle("HyperLink");  
    namedStyle.Style.Font.UnderLine = true;
    namedStyle.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
    converter.Sheet.Cells["K13"].Hyperlink = new OfficeOpenXml.ExcelHyperLink("Statistics!A1", "Statistics");
    converter.Sheet.Cells["K13"].StyleName = "HyperLink";

In addition, here is the current implementation of the SetValue() function of the converter:
Code:
       public void SetValue(int row, int col, ResultCell cell)
        {
            string format = null;
            if (cell.Element != null && !cell.Element.IsEnum && !cell.IsTitle && cell.Element.IsNumeric && UseElementFormat)
            {
                format = cell.Element.GetExcelFormat(View.CultureInfo);
                if (cell.DoubleValue != null)
                {
                    Sheet.Cells[row, col].Value = cell.DoubleValue.Value;
                }
            }
            else if (cell.Element != null && !cell.Element.IsEnum && !cell.IsTitle && cell.Element.IsDateTime && UseElementFormat)
            {
                format = cell.Element.GetExcelFormat(View.CultureInfo);
                if (cell.DateTimeValue != null)
                {
                    Sheet.Cells[row, col].Value = cell.DateTimeValue.Value;
                }
            }
            else
            {
                if (UseElementFormat)
                {
                    Sheet.Cells[row, col].Value = cell.DisplayValue;
                }
                else if (cell.Value != null)
                {
                    Sheet.Cells[row, col].Value = cell.Value.ToString();
                }
            }
            if (UseCellStyle)
            {
                string style = style_cell_value;
                if (cell.IsTitle) style = style_cell_title;
                else if (cell.IsTotal) style = style_cell_value_total;

                Sheet.Cells[row, col].StyleName = style;
            }
            //Apply format at the end to make it work
            if (!string.IsNullOrEmpty(format))
            {
                Sheet.Cells[row, col].Style.Numberformat.Format = format;
            }

        }

Edited by user Thursday, December 12, 2019 1:15:03 PM(UTC)  | Reason: Not specified

epf  
#8 Posted : Tuesday, December 17, 2019 12:41:16 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
In addition here is a sample to add the logo and a textbox having the title in your Excel file
(got from https://github.com/JanKa...es,-Pictures-and-Charts)

This replaces the 'Root script: Header'

Code:
@using Seal.Converter
@using System.IO
@using System.Drawing
@{	
    //Full documentation of the EPPlus OpenXml converter at https://github.com/JanKallman/EPPlus
    ExcelConverter converter = Model;
    var report = converter.View.Report;
    var sheet = converter.Sheet;

    //rowStart and colStart can be modified here to set an absolute or relative position of the items displayed...
    int rowStart = converter.CurrentRow;
    int colStart = converter.CurrentCol;
    var styleName = ExcelConverter.style_cell_title;
    
    
    var repository = converter.View.Report.Repository;
    var logoPath = Path.Combine(repository.ViewImagesFolder, repository.Configuration.LogoName);
    var img = Image.FromFile(logoPath);
    var pic = sheet.Drawings.AddPicture("0", img);
    pic.SetPosition(0, 0, 0, 0);   
    
    //Add the textbox
    var shape = sheet.Drawings.AddShape("myShape", eShapeStyle.Rect);
    shape.SetPosition(0, 0, 4, 0);       //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
    shape.SetSize(500, 20);             //Size in pixels
    shape.Text = report.ExecutionName;           
    
    rowStart+=3;
    
    if (converter.ShowTitle)
    {
		sheet.Cells[rowStart, colStart].Value = report.ExecutionName;
		sheet.Cells[rowStart, colStart].StyleName = styleName;
        rowStart++;
    }
    if (converter.ShowMessage)
    {
        sheet.Cells[rowStart, colStart++].Value = report.ExecutionMessages;
        rowStart++;
    }
    if (converter.ShowInformation)
    {
        sheet.Cells[rowStart, colStart++].Value = report.Translate("Model");
        sheet.Cells[rowStart, colStart++].Value = report.Translate("Records");
        sheet.Cells[rowStart, colStart++].Value = report.Translate("Pages");
        sheet.Cells[rowStart, colStart++].Value = report.Translate("Duration");
        sheet.Cells[rowStart, colStart++].Value = report.Translate("Restrictions");
        sheet.Cells[rowStart, colStart - 5, rowStart, colStart].StyleName = styleName;
        colStart = converter.CurrentCol;
        rowStart++;
        foreach (var item in report.Models.Where(i => i.ResultTable != null && i.Pages != null))
        {
            sheet.Cells[rowStart, colStart++].Value = item.Name  + "("+ item.Connection.Name + ")";
            sheet.Cells[rowStart, colStart++].Value = item.ResultTable.Rows.Count;
            sheet.Cells[rowStart, colStart++].Value = item.Pages.Count;
            sheet.Cells[rowStart, colStart++].Value = item.ExecutionDuration;
            sheet.Cells[rowStart, colStart++].Value = item.RestrictionText;
            colStart = converter.CurrentCol;
            rowStart++;
        }
        rowStart++;
    }

    converter.CurrentRow = rowStart;
}
thanks 2 users thanked epf for this useful post.
Sujeet Kumar T on 12/17/2019(UTC), Benbabu on 12/19/2019(UTC)
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.