We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Exporting to Excell

Thread ID:

Created:

Updated:

Platform:

Replies:

126390 Oct 12,2016 08:24 PM UTC Oct 19,2016 09:17 AM UTC ASP.NET MVC 6
loading
Tags: Grid
Dayne
Asked On October 12, 2016 08:24 PM UTC


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 [Syncfusion]
Replied On October 13, 2016 12:36 PM UTC

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> 


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); 
            
        } 
         


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, 
 

Regards, 
Venkatesh Ayothiraman. 


Dayne
Replied On October 13, 2016 05:13 PM UTC

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
Replied On October 13, 2016 05:19 PM UTC

Hi, 

I just found the desired format

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

Thanks for the help

Venkatesh Ayothi Raman [Syncfusion]
Replied On October 14, 2016 04:07 AM UTC

Hi Dayne, 

Thanks for the update. 

We are happy to hear that your requirement is achieved. 

Regards, 
Venkatesh Ayothiraman. 


Dayne
Replied On October 14, 2016 02:08 PM UTC

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 [Syncfusion]
Replied On October 19, 2016 09:17 AM UTC

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.  
  
 
Regards, 
Venkatesh Ayothiraman. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;