Export to replace existing Named Sheet

[Steven]

Probably more of a suggestion than a question.

I would like an FileExportMode that is between the 2 existing modes. It creates or overrides the named Sheet in the given XLSX

User could then:

• Export to a named sheet.
• Add another sheet that references the exorted data
• Run the Export again to refresh the data

[Nadya | Tech Support Engineer]

Hello, Steven,

According to provided brief information I suppose you are referring to RadGridView and GridViewSpreadExport since you marked "GridView" into ticket info.

GridViewSpreadExport offers FileExportMode.CreateOrOverrideFile and FileExportMode.NewSheetInExistingFile. When you choose CreateOrOverrideFile the export will create new new file, or if such sheet already exist it will replace it any time when you run the export again.

Am I missing something? In case this is not what you are looking for, could you please provide more information what is the desired behavior that you want to have.

If you have any other questions do not hesitate to contact me.

Regards,
Nadya
Progress Telerik

[Steven]

If I have an existing XLXS with 2 Sheets "MyData" and "MySummary"

When I run GridViewSpreadExport with a Sheet Name of "MyData" I can currently either get:

1. "MyData"
2. "MyData" (with original values), "MyData_1" (with new values), "MySummary"

What I would like is:

1. "MyData" (with new values), "MySummary"

Using your current naming convention, the Option might be named FileExportMode.CreateOrOverrideSheet

[Nadya | Tech Support Engineer]

Hello, Steven,

I would like to note that RunExport method offers four overloads. One of them allows to start an export operation by the specified sheet. If such does not exist, it gets created. When you choose FileExportMode to be CreateOrOverrideFile and run the export, it will override the sheet in the specified file.

I created a video showing the results on my end. As you can see in the video, the second time when I export the data in the same sheet, it overrides the data as expected. Actually, you receive "MyData" sheet with new values. 

Please let me know if there is anything I can help with.

Regards,
Nadya
Progress Telerik

[Steven]

Thanks for the clear response. 2 issues.

Minor - I am using PivotGrid rather than GridView (my fault) but thats easy enough to use the corresponding objects

Major - Your example is missing the point that the ADDITIONAL sheet in the file is being deleted. Have attached a snip of the XLXS before running the Export. After Running the Export the "MySummary" sheet is missing (as expected by the name of the ExportMode).

If you use FileExportMode.NewSheetInExistingFile, it leaves "MySummary" and "MyData" alone but adds "MyData_1"

I want it to leave MySummary alone, and update MyData

 

[Steven]

I would add that I think its a valid use case where the End User wishes to use the output of a PivotGrid (or other Grid) as the datasource in a Excel file for further analysis/reporting.

[Nadya | Tech Support Engineer]

Hello, Steven,

Thank you for sharing more information about what is the exact behavior that you would like to achieve. Yes, I confirm that currently CreateOrOverrideFile overrides the file and if the file has more sheets initially, after export they are missing. I agree with you that having addition option that overrides just the sheet in a specified file name would be quite a useful feature. 

Your suggestion is meaningful to use and that is why I have logged a feature request in our Feedback Portal on your behalf. You can follow the feedback item to receive notification changes and vote for its implementation. By voting, you are increasing its priority. 

Your Telerik Points are updated for bringing this to our attention.

Currently, you can achieve similar behavior by creating a custom SpreadExportRenderer and override ImportWorkbook method in order to remove a specific sheet. Then export the data again in the same sheet by using FileExportMode.NewSheetInExistingFile. Please refer to the following code snippet:

private void ExportButton_Click(object sender, EventArgs e)
{
    PivotGridSpreadExport spreadExporter = new PivotGridSpreadExport(this.radPivotGrid1);
    ISpreadExportRenderer exportRenderer = new MyCustomSpreadExportRenderer();
    spreadExporter.FileExportMode = FileExportMode.NewSheetInExistingFile;
    spreadExporter.RunExport("C:\\Temp\\New folder\\exportedFile.xlsx", exportRenderer, "MySummary");
}


public class MyCustomSpreadExportRenderer : SpreadExportRenderer
{
    public override void ImportWorkbook(Stream stream)
    {
        base.ImportWorkbook(stream);

        var fi = typeof(SpreadExportRenderer).GetField("workbook", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);
        Telerik.Windows.Documents.Spreadsheet.Model.Workbook workbook = fi.GetValue(this) as Workbook;

        // This removes the existing worksheet by name.             
        // In the AddWorksheet method later a new Worksheet with this name will be created.
        workbook.Worksheets.Remove("MySummary");
    }
}

I hope this is useful. If you have any other questions do not hesitate to contact us.

Regards,
Nadya
Progress Telerik