Is there a way to write a previously read and updated DataSet back to Excel? [duplicate]

Question

The idea is simple and no matter how much I have searched I couldn't find any similar example.

Using the OleDbDataAdapter class, I am able to achieve the first step: Read a specific range in a specific tab from an Excel document.

I have to change the Dataset contents as I please and then move to the last step.

The last step is where I struggle: how to write the Dataset back to Excel into the same range and same tab? Basically perform an update of what was read initially with my changes.

My code so far for the first step - the changes in the dataset are not relevant for this example.

    static void Main(string[] args)
    {
        string dq = "\"";
        string dataSheetName = "Sheet1";
        string fileName = @"C:\Myfile.xlsm";
        string excelRange = "A1:E7";
        string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName}; Extended Properties={dq}Excel 12.0;HDR=NO;IMEX=1{dq}";
        var adapter = new OleDbDataAdapter($"SELECT * FROM [{dataSheetName}${excelRange}]", connectionString);
        DataSet dataSet = new DataSet();

        adapter.Fill(dataSet, "myData");
        DataTable dataTable = dataSet.Tables["myData"];

        foreach (DataRow row in dataTable.Rows)
        {

            // change rows contents as I please before writing back to Excel
        }

        // ------------------------------------------------------------------------------------
        // GOAL: To write back to Excel the dataset changed above into the same tab and range
        // ------------------------------------------------------------------------------------

    }

I am using Office 365.

Can anyone help?

Much appreciated

Are you sure its possible to write? Why not use Ole Automation or DDE? Select is a read only SQL command. — Warren P
– Warren P, Commented Oct 31 at 2:49
I agree, you can use OLE Automation with Excel.Range but it has been years (decades?) since I did this, so someone else will need to provide code — SSS
– SSS, Commented Oct 31 at 4:08

Arthur · Accepted Answer · 2025-11-03 14:40:52Z

What I would do is the following, schematically:

Create a DataSet to store the date from your original Excel file (that's what you do with the adapter.Fill())
Using a StreamWriter sw = new StreamWriter("path/to/yourCSVfile.csv");, I would write everything I need from the DataSet, after transformation into a string row, with a separator (below I'll use ;), like:
1. For example, if your XLSX file has 2 columns "dept" and "revenue" > you add "dept;revenue" as the 1st line of your StreamWriter (this line will be the header row), with sw.WriteLine("dept;revenue");
2. Say your 1st data row is "MKG" and "$35.000" you can transform this anyway you want into a String modifiedRow = "{whatever you need}"; and simply use, again: sw.WriteLine(modifiedRow);
Once all your original content is parsed and modified, you have a CSV file somewhere on your hard drive that you can easily transform into an Excel file using a "Data Flow" component with a flat file source and an Excel destination.

If you need your original Excel to be overwritten with this new one, simply finish with a File System Task to do that.

I hope this helps, even if it will require you to have a look into:

StreamWriter
WriteLine
"Data Flow" task
"File System" task

Collectives™ on Stack Overflow

Is there a way to write a previously read and updated DataSet back to Excel? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related