Exporting to Excell

[Dayne]

Hi,  everyone

1. I want to format a currency column in the grid with the accounting format used in Excell, so, all the dollar sing are aling to the left of the celd and all the numbers are aling to the rigth of the celd.

For Example:

Currency Format	Accounting Format
$250.00	$                  250.00

2. Keep this format while exporting to Excell, so the currency has the accounting format.

3. I need that the font family and size are set diferently when exporting to Excell, for example, in the Grid I use "Sans Serif 8", and I want that the excell use "Arial Narrow 11".

It's all these possible?

Thanks in advance

[Venkatesh Ayothi Raman]

Hi Dayne, 

Thanks for contacting Syncfusion support. 

Query #1:  “I want to format a currency column in the grid with the accounting format used in Excell, so, all the dollar sing are aling to the left of the celd and all the numbers are aling to the rigth of the celd.” 

We have achieved your requirement using QueryCellInfo event in Grid. Please refer to the code example and help document, 

Code example: 

<Grid> 

@(Html.EJ().Grid<object>("FlatGrid") 

        .Datasource((IEnumerable<object>)ViewBag.datasource) 

                    .  . . 

                          .Mappers(map => map.ExportToExcelAction("/Grid/ExportToExcel")) 

                              .ClientSideEvents(e => e.QueryCellInfo("QueryCellInfo")) 

                               

            .Columns(col => 

            { 

                . . .                col.Field("CurrencyFormat").HeaderText("CurrencyFormat").TextAlign(TextAlign.Right).Format("{0:C}").Width(25).Add(); 

                col.Field("AccountingFormat").HeaderText("AccountingFormat").TextAlign(TextAlign.Left).Width(75).Format("{0:C}").Add(); 

                 

 

            }) 

        ) 

 

<Oureycell info event> 

 

<script type="text/javascript"> 

 

    function QueryCellInfo(args) { 

 

        

        if (args.column.field == "AccountingFormat") 

        { 

            var text = args.text; 

            var symbol = text.charAt(0); 

            var value = text.split('$').join(''); 

            var accountFormat = symbol + " "+" " + value; 

            args.cell.innerHTML = accountFormat; //Customize the cell text 

        } 

    } 

</script> 

Help document: https://help.syncfusion.com/js/api/ejgrid#events:querycellinfo 

Query #2:” Keep this format while exporting to Excell, so the currency has the accounting format” 

We have achieved your requirement using custom formatting feature in Export. Please refer to the code example and Help document, 

Code example: 

<Export method> 

public void ExportToExcel(string GridModel) 

        { 

            ExcelExport exp = new ExcelExport(); 

            var DataSource = ord; 

            var count = ord.Count;             

            GridProperties gridProperty = (GridProperties)Utils.DeserializeToModel(typeof(GridProperties), GridModel); 

                .  .   . 

            IWorkbook book = exp.Export(gridProperty, ord, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron", true); 

            book.ActiveSheet.Range[1, 4, count,4].NumberFormat = "$             #,##0.00"; //Customize the formatting for account formatting and here range specifices [int row, int column, int lastRow, int column] 

            book.SaveAs("Export.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open); 

            

        } 

         

Help document: https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#apply-number-formats 

Query #3:” I need that the font family and size are set diferently when exporting to Excell, for example, in the Grid I use "Sans Serif 8", and I want that the excell use "Arial Narrow 11” 

Grid have feature like Auto formatting while export. In this feature, we can customize the whole grid content like as follows, 

public void ExportToExcel(string GridModel) 

        { 

             . . . 

            GridProperties gridProperty = (GridProperties)Utils.DeserializeToModel(typeof(GridProperties), GridModel); 

            GridExtensions ext = new GridExtensions(); 

            AutoFormat auto = new AutoFormat(); 

            ext.SetTheme(auto, "flat-saffron"); 

            auto.FontFamily = "Arial Narrow";                //Customize the formatting while grid exporting 

            auto.ContentBorderColor = Color.Brown;           //Customize the formatting while grid exporting 

            auto.ContentFontSize = 11;                       //Customize the formatting while grid exporting 

            auto.GCaptionBorderColor = Color.Cornsilk;       //Customize the formatting while grid exporting 

            auto.GContentFontColor = Color.DarkBlue;         //Customize the formatting while grid exporting 

            auto.HeaderFontSize = 12;                        //Customize the formatting while grid exporting 

            auto.HeaderBorderColor = Color.Red;              //Customize the formatting while grid exporting 

            auto.ContentBgColor = Color.Wheat;               //Customize the formatting while grid exporting 

            auto.GHeaderBgColor = Color.Crimson;             //Customize the formatting while grid exporting 

            auto.AltRowBgColor = Color.LightCyan;            //Customize the formatting while grid exporting 

            gridProperty.AutoFormat = auto; 

           

  IWorkbook book = exp.Export(gridProperty, ord, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron", true); 

            book.ActiveSheet.Range[1, 4, count,4].NumberFormat = "$             #,##0.00"; //Customize the formatting for account formatting  

            

 book.SaveAs("Export.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open);//Export the Grid 

            

        } 

We have also created a sample for your requirement which can be download from following link, 

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/7z/Export711446908 

 

Regards, 

Venkatesh Ayothiraman. 

[Dayne]

Hi,

Thanks for the information, but the requirement number 2, didn't work exactly as I wanted

With the format described: book.ActiveSheet.Range[1, 4, count,4].NumberFormat = "$            #,##0.00"; 

You get the following result:

  $   46,440.48

      $   9,446.9

  $   60,212.21

$   268,387.34

And the desired result is:

$     46,440.48

$         9,446.9

$     60,212.21

$   268,387.34

Thanks

[Dayne]

Hi, 

I just found the desired format

 book.ActiveSheet.Range[1, 84 count, 4].NumberFormat = "_($* #,##0_)";

Thanks for the help

[Venkatesh Ayothi Raman]

Hi Dayne, 

Thanks for the update. 

We are happy to hear that your requirement is achieved. 

Regards, 

Venkatesh Ayothiraman. 

[Dayne]

But I have another issue

I have two sumary rows in my grid

Code Example

ShowSummary()

                .SummaryRow(row =>

                {

                    row.TitleColumn("Cliente").Title("Total MXN:").SummaryColumns(col =>

                    {

                        col.SummaryType(SummaryType.Sum)

                        .Format("{0:C2}")

                        .DisplayColumn("BalanceRemainingHomeCurrency")

                        .DataMember("BalanceRemainingHomeCurrency")

                        .Add();

                    }).Add();

                    row.ShowTotalSummary(false).ShowCaptionSummary(true).SummaryColumns(col =>

                    {

                        col.SummaryType(SummaryType.Sum)

                        .Format("{0:C2}")

                        .DisplayColumn("BalanceRemainingHomeCurrency")

                        .DataMember("BalanceRemainingHomeCurrency")

                        .Add();

                    }).Add();

                })

 

But when I export to the excell it only exports the summary row that is shown in the header caption of the Grouping, the  ("Total MXN:") row, is not exported. 

So, the request is, I want to export all sumary rows to the excel file.

[Venkatesh Ayothi Raman]

Hi Dayne, 

Thank you for the update. 

We considered this requirement “Provide support for Exporting the Grid with Caption summary and Group summary rows” as improvement task and a support incident has been created under your account to track the status of this requirement. Please log on to our support website to check for further updates.  

   

https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents  

 

Regards, 

Venkatesh Ayothiraman.