Cell formatting, Retaining formatting in Excel/PDF export

[Balaji Pitchaimani]

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]

Look here maybe this help You

https://www.syncfusion.com/forums/125610/style-format-and-get-data-from-grid

[Tomasz]

Look here maybe this help You

https://www.syncfusion.com/forums/125610/style-format-and-get-data-from-grid

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]

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. 

 

https://www.syncfusion.com/kb/2979/how-to-select-or-highlight-rows-based-on-data 

 

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> 

 

 

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

 

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. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/128833/ze/ASPGrid-817867291.zip 

                                                                    

Regards, 

Seeni Sakthi Kumar S.