Export Excel DateTime Field Display Seconds

[Felipe Saldana]

I have a grid and I am exporting to excel.

On the web page, I have a DateTime field that displays using the following format:

 

<telerik:GridBoundColumn HeaderText="Message Date" DataField="messageDate" UniqueName="messageDate" SortExpression="messageDate" ShowSortIcon="false" DataFormatString="{0:MM/dd/yyyy hh:mm:ss tt}" DataType="System.DateTime" ></telerik:GridBoundColumn>

On the web page the seconds get displayed. When I export the grid to Excel the seconds are no longer displayed.
The field is displayed on the spreadsheet like the following : 07/21/10 10:06 AM.

If I click on the cell and look at the data for the cell the seconds are indeed there.
How do I force Excel to use my specific DateTime format?

Thanks for the help.

Felipe

[Daniel]

Hello Felipe,

It depends on the Excel format that is used in your scenario - ExcelML or HTML.

I recommend that you examine the following links:
ExcelML export
(Number formats; Custom styles)

Word/Excel export (HTML-based)
(Number formats / Formulas section)

Best regards,
Daniel
the Telerik team

[ArtiCut]

What about Xlsx format? ( ExcelFormat .Xlsx)

[Daniel]

Hello,

Here is the corresponding topic for the XLSX format:
XLSX and DOCX - Formatting

Regards,
Daniel
Telerik by Progress

[Vasssek]

Hello,

 I'm trying to export data from radgrid in ExcelFormat.Xlsx, but I don't know, how to add formula to current column / row. I need to set up VLOOKUP function.

Here is the part of my code:

protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)
    {
        if (blnIsExport)
        {
            if (Request.Params.Get("__EVENTARGUMENT") == "confirmCallBack_ExportRecordsKreditorVLOOKUP")
            {
                if (e.Item is GridDataItem)
                {
                    GridDataItem gridDataItem = (GridDataItem)e.Item;

                    CP_Orders foundItem = lst_Order_checked_Items.FirstOrDefault(i => i.ID == (int)((DataRowView)gridDataItem.DataItem).Row["ID"]);

                    if (foundItem == null)
                    {
                        gridDataItem.Display = false;
                    }
                    else
                    {
                        //gridDataItem["KREDITOR"].Text = @"=LEN(""textvv"")";
                        //gridDataItem["KREDITOR"].Text = "999911";
                        gridDataItem["KREDITOR"].Text = @"=VLOOKUP(B2;'C:\inetpub\ASP_Kysuce\Cestovne_prikazy_test\Install\NEW\v.1.9.9\CP_zalohy\[Kreditor_basis.xlsx]Hárok1'!$A:$B;2; FALSE)";

                    }
                }
            }
            if (e.Item is GridFilteringItem)
            {
                e.Item.Display = false;
            }
        }
}

I'm trying to set it like: gridDataItem["KREDITOR"].Text = @"=VLOOKUP(B2;'C:\inetpub\ASP_Kysuce\Cestovne_prikazy_test\Install\NEW\v.1.9.9\CP_zalohy\[Kreditor_basis.xlsx]Hárok1'!$A:$B;2; FALSE)";

But when click on export button, then this error appeared:

We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it.\n\nNot trying to type a formula? When the first character is an equal (=) or minus (-) sign, RadSpreadsheet thinks it is a formula. For example, when you type =1+1 the cell shows 2.

In the attachment you can see, where I need to place VLOOKUP function.

Please help me to solve this issue.

Best regards

Vasssek

 

 

 

 

 

[Attila Antal]

Hi Vasssek,

One option to create formula in Xlsx is by using the OnInfrastructureExporting event of RadGrid where you can modify the export structure. (Attached you can find a basic sample demonstrating this scenario)

Example code:

Markup

<telerik:RadGrid ID="RadGrid1" runat="server"
    OnInfrastructureExporting="RadGrid1_InfrastructureExporting">
</telerik:RadGrid>

C# OnInfrastructureExporting event handler

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    for (int i = 0; i < e.ExportStructure.Tables[0].Rows.Count; i++)
    {
        if (i > 0)
            e.ExportStructure.Tables[0].Cells[3, i + 1].Value = string.Format("=(A{0}+B{0})", i + 1);
    }
}

Another option would be by using the APIs for RadSpreadProcessing - Formulas from Telerik Document Processing Library.

I hope this will prove helpful.

Kind regards,
Attila Antal
Progress Telerik

[Vasssek]

Hello,

thank you for your suggestion. But I have been struggling with another issue. How to put VLOOKUP with filename into cell formula ?

I have tried many variations, but non of them is working.  If I put there this function, there an error appeared and no export file is shown:

rows[rowIndex].Cells[2, rowIndex].Value = @"=VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-1));'[Kreditor_basis.xlsx]Sheet1'!$A$1:$B$999999;2;false)";

We found a problem with this formula. Try clicking Insert Function on the Formulas tab to fix it.\n\nNot trying to type a formula? When the first character is an equal (=) or minus (-) sign, RadSpreadsheet thinks it is a formula. For example, when you type =1+1 the cell shows 2.

If I put there this rows[rowIndex].Cells[2, rowIndex].Value = @"=VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-1));[Kreditor_basis.xlsx]Sheet1!$A$1:$B$999999;2;false)";

in result excel cell there is only sheeet name and not file name:

=VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-1));Sheet1!$A$1:$B$999999;2;FALSE)

The question is, why is filename removed during export process ?

Thank you

Best regards

Vasssek

[Attila Antal]

Hi Vasssek,

Replacing the semicolons `;` in the string with coma `,` will work.

See the changes marked with yellow. (also applied the changes to my previous sample and attached it to my response)

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    for (int i = 0; i < e.ExportStructure.Tables[0].Rows.Count; i++)
    {
        if (i > 0)
            e.ExportStructure.Tables[0].Cells[3, i + 1].Value = string.Format("{0}", "=VLOOKUP(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),[Numbers.xlsx]Sheet1!$A$1:$B$999999,2,false)");
    }
}

Also, note that referencing files in a formula with a string like "\\path\to\file" is not supported. Please check out the issue reported in our feedback portal SpreadProcessing: Add support for external references to another workbook

Kind regards,
Attila Antal
Progress Telerik