Saving datatable from 1 workbook into another results in overwriting the first workbook

[Paul Meems]

I have a workbook with several worksheets.

Some of the worksheets need to be exported as new workbooks.

Before the export, some records will be excluded and not all columns need to be exported.

I open the workbook and use worksheet.ExportDataTable() to get a DataTable.

Next I create a DataView so I can filter records and I export this DataView to a new DataTable so I can set the columns I need.

This DataTable I import into a new workbook using worksheet.ImportDataTable().

I save this new workbook to a new filename.

The result:

The new file doesn't exist but the data is saved in the input workbook and all other worksheets are removed.

The data is correct, though.

My code:

const string fileName = @"input.xlsx";

//Creates a new instance for ExcelEngine

var excelEngine = new ExcelEngine();

//Loads or open an existing workbook through Open method of IWorkbooks

var workbook = excelEngine.Excel.Workbooks.Open(fileName, ExcelParseOptions.ParseWorksheetsOnDemand, true);

foreach (var worksheet in workbook.Worksheets)

{

    using (var filteredDataTable = FilterDatatable(worksheet))

    {

        if (filteredDataTable == null) continue;

        // Create new workbook:

        var newWb = excelEngine.Excel.Workbooks.Create(1);

        // Import DataTable: 

        newWb.Worksheets[0].ImportDataTable(filteredDataTable, true, 1, 1);

        var newFilename = $@"Conversion.xlsx";

        if (File.Exists(newFilename)) File.Delete(newFilename);

        newWb.SaveAs(fileName, ExcelSaveType.SaveAsXLS);

        Assert.IsTrue(File.Exists(newFilename), "Filename does not exists");

    }

}

private DataTable FilterDatatable(IWorksheet worksheet)

{

    using (var dataTable = worksheet.ExportDataTable(worksheet.UsedRange,

        ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.DetectColumnTypes |

        ExcelExportDataTableOptions.ExportHiddenColumns | ExcelExportDataTableOptions.ExportHiddenRows))

    {

        if (worksheet.Name != "1. Foo") return null;

        using (var dv = new DataView(dataTable))

        {

            dv.RowFilter = "Bar = '370554243'";

            return dv.ToTable(true, "col1", "col2", "col3");

        }

    }

}

I open the input file as read-only but it still gets overwritten.

When I don't call newWb.SaveAs() the new file doesn't get created (obviously) but the input file isn't changed either.

So somehow is SaveAs() also triggering some save action in the input file.

What am I doing wrong? Please advice.

I'm using v17.4.0.46

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Paul, 

Greetings from Syncfusion. 

You are trying to save the new workbook (newWb) in the name of input workbook in the same location(fileName). We suggest you to save newWb in the name of newFilename to overcome the issue. Please find the code snippet below. 

Code Snippet: 

const string fileName = @"input.xlsx"; 

 

//Creates a new instance for ExcelEngine 

var excelEngine = new ExcelEngine(); 

//Loads or open an existing workbook through Open method of IWorkbooks 

var workbook = excelEngine.Excel.Workbooks.Open(fileName, ExcelParseOptions.ParseWorksheetsOnDemand, true); 

foreach (var worksheet in workbook.Worksheets) 

{ 

    using (var filteredDataTable = FilterDatatable(worksheet)) 

    { 

        if (filteredDataTable == null) continue; 

 

        // Create new workbook: 

        var newWb = excelEngine.Excel.Workbooks.Create(1); 

        // Import DataTable:  

        newWb.Worksheets[0].ImportDataTable(filteredDataTable, true, 1, 1); 

        var newFilename = $@"Conversion.xlsx"; 

        if (File.Exists(newFilename)) File.Delete(newFilename); 

        newWb.SaveAs(newFilename, ExcelSaveType.SaveAsXLS); 

        Assert.IsTrue(File.Exists(newFilename), "Filename does not exists"); 

    } 

} 

 

private DataTable FilterDatatable(IWorksheet worksheet) 

{ 

    using (var dataTable = worksheet.ExportDataTable(worksheet.UsedRange, 

        ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.DetectColumnTypes | 

        ExcelExportDataTableOptions.ExportHiddenColumns | ExcelExportDataTableOptions.ExportHiddenRows)) 

    { 

        if (worksheet.Name != "1. Foo") return null; 

 

        using (var dv = new DataView(dataTable)) 

        { 

            dv.RowFilter = "Bar = '370554243'"; 

 

            return dv.ToTable(true, "col1", "col2", "col3"); 

        } 

    } 

} 

Kindly try this and let us know if this helps. 

Regards, 

Keerthi. 

[Paul Meems]

Thank you for your reply.

I feel really stupid. I've overlooked it several times.

It is working now.

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Paul, 

We are glad that the provided suggestion helped you. Kindly let us know if you need any further assistance. 

Regards, 

Keerthi.