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.
Unfortunately, activation email could not send to your email. Please try again.

Cell formatting, Retaining formatting in Excel/PDF export

Thread ID:

Created:

Updated:

Platform:

Replies:

128833 Feb 11,2017 07:15 PM Feb 15,2017 07:57 AM ASP.NET Web Forms 3
loading
Tags: Grid
Balaji Pitchaimani
Asked On February 11, 2017 07:15 PM

1. Based on value in Data Column - B, I want to change color of cell C - I want to access C by header text or field name, not by index. Should I use the row data bound event - if so, how to get to C. I did not see an example.

2. All these formatting done in the front end should be available when exported to Excel or PDF.

Please advise.

Tomasz
Replied On February 12, 2017 05:53 AM


Tomasz
Replied On February 12, 2017 06:05 AM

Look here maybe this help You

javascript finaly look like this

function cellQueryInfo(args) {

var DateA = new Date(args.data.CheckOut).format("dd/MM/yyyy");

var DateC = new Date(args.data.CheckIn).format("dd/MM/yyyy");

var DateN = new Date();

var Dzis = new Date(DateN.setDate(DateN.getDate() + 0)).format("dd/MM/yyyy");//Today

var Jutro = new Date(DateN.setDate(DateN.getDate() + 1)).format("dd/MM/yyyy");//Tomorrow

var Price = args.data.TotalPrice;

if (args.column.field == "TotalPrice" && Price == 0.00) {

$($(args.cell)).css("backgroundColor", "red").css("color", "white");

}

if (args.column.field == "CheckOut" && DateA == Dzis)

$($(args.cell)).css("backgroundColor", "Yellow");

if (args.column.field == "CheckOut" && DateA == Jutro)

$($(args.cell)).css("backgroundColor", "Blue").css("color", "white");

if (args.column.field == "CheckIn" && DateC == Dzis)

$($(args.cell)).css("backgroundColor", "Green").css("color", "#ffffff");

};


Seeni Sakthi Kumar Seeni Raj [Syncfusion]
Replied On February 15, 2017 07:57 AM

Hi Balaji, 
 
Thanks for contacting Syncfusion Support. 
 
Query #1: Based on value in Data Column - B, I want to change color of cell C - I want to access C by header text or field name, not by index. Should I use the row data bound event - if so, how to get to C. 
 
You can use the QueryCellInfo event of the Grid to modify any cells in the Grid. Therefore, you can access other cell values in a particular row and modify the format the current cells. We have already discussed about this in the following KB. 
 
 
Refer to the following code example and API Reference.  
 
 
    <ej:Grid ID="FlatGrid" runat="server" OnServerPdfExporting="FlatGrid_ServerPdfExporting" 
        OnServerExcelExporting="FlatGrid_ServerExcelExporting" AllowPaging="True"> 
        <ToolbarSettings ShowToolbar="true" ToolbarItems="excelExport,wordExport,pdfExport"></ToolbarSettings> 
        <Columns> 
            <ej:Column Field="OrderID" HeaderText="Order ID" /> 
               .. .  
                    . . . 
            <ej:Column Field="CustomerID" HeaderText="Customer ID" /> 
        </Columns> 
        <ClientSideEvents QueryCellInfo="onQuery" /> 
    </ej:Grid> 
 
    <script> 
        function onQuery(args) { 
            if (args.column.field == "CustomerID" && args.data.OrderID % 2) 
                $(args.cell).css("background-color", "red"); 
        } 
    </script> 
 
 
 
Query #2: All these formatting done in the front end should be available when exported to Excel or PDF. 
 
For Excel Exporting: 
 
Initially, the Excel WorkBook is saved as a IWorkbook and late the Conditional formatting has been applied to the CustomerID column based on their Column Index. Refer to the following code example. 
 
        protected void FlatGrid_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) 
        { 
            ExcelExport exp = new ExcelExport(); 
 
            IWorkbook book = exp.Export(FlatGrid.Model, (IEnumerable)FlatGrid.DataSource, "Export.xlsx", ExcelVersion.Excel2010, true, false, "flat-lime", true); 
            IWorksheet worksheet = book.Worksheets[0]; 
            //Get the index of the column 
            int inx = GetColIndex("CustomerID") + 1; 
 
            //Formatting applied to mentioned index values 
            //1- row start, inx- column start, worksheet.UsedRange.LastRow - last row in used range, 3 - column end 
            IConditionalFormats condition = worksheet[1, inx, worksheet.UsedRange.LastRow, inx].ConditionalFormats; 
 
            IConditionalFormat condition1 = condition.AddCondition(); 
 
            condition1.FormatType = ExcelCFType.Formula; 
            //A1 refers to the first Column value i.e. OrderID 
            //We have taken mod value 
            condition1.FirstFormula = "=MOD($A1,2)"; 
 
            condition1.BackColor = ExcelKnownColors.Red; 
            book.SaveAs("Export.xlsx", ExcelSaveType.SaveAsXLS, System.Web.HttpContext.Current.Response, ExcelDownloadType.Open); 
        } 
 
        public int GetColIndex(string field) 
        { 
            Column col = FlatGrid.Model.Columns.Find(c => c.Field == field); 
            var inx = FlatGrid.Model.Columns.IndexOf(col); 
            return inx; 
        } 
 
For PDF Exporting: 
 
In this, ServerPdfRowInfo event will be used which is similar to the QueryCellInfo event of the Grid. For each row binding, ServerPdfRowInfo will be triggered. So we can traverse nearby cells and apply the styles to the particular based on the values of the other cells. Refer to the following code example. 
 
        protected void FlatGrid_ServerPdfExporting(object sender, Syncfusion.JavaScript.Web.GridEventArgs e) 
        { 
            PdfExport exp = new PdfExport(); 
            FlatGrid.Model.ServerPdfRowInfo = querRow; 
            exp.Export(FlatGrid.Model, (IEnumerable)FlatGrid.DataSource, "Export.pdf", true, true, "flat-lime"); 
        } 
        public void querRow(object sender) 
        { 
            PdfGridRow row = (PdfGridRow)sender; 
            int inx = GetColIndex("OrderID"); 
            if (int.Parse(row.Cells[inx].Value.ToString()) % 2 == 0) 
            { 
                int ix = GetColIndex("CustomerID"); 
                PdfGridCellStyle pdfGridCellStyle = new PdfGridCellStyle(); 
                row.Cells[ix].Style.BackgroundBrush = PdfBrushes.Red; 
            } 
        } 
        public int GetColIndex(string field) 
        { 
            Column col = FlatGrid.Model.Columns.Find(c => c.Field == field); 
            var inx = FlatGrid.Model.Columns.IndexOf(col); 
            return inx; 
        } 
 
We have prepared a sample that can be downloaded from the following location. 
 
                                                                    
Regards, 
Seeni Sakthi Kumar S. 


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.

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.

;