Save to multiple sheets

Lili R

Hi there

How can we save data to every sheet of an existing file. when we do not know that how many sheets are present in the file.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Lili R10y ago
Hi Francis

What i want to do is apply some formulas on the existing sheet data.. and extract the required values and save that in the same sheet. But the no of sheets available in an excel file will not be fixed.

Thus, i m opening the excel file every time to extract a sheet data and perform what i want, save it, close it and then again open the file to do the same with the next sheet of the file. Until, done with all the sheets in an excel file.

This is working, but is slow. So, i wanted to make it faster.. If you got any idea please suggest.

Thanks
0
Francis Susaimichael10y ago
If i'm understanding you correctly, you just want to copy the content of an excel file to another excel file. If this is your ultimate aim, why don't you copy the excel file, instead of open every sheet and write it on another excel.

https://msdn.microsoft.com/en-us/library/ms178800.aspx

If this is not the case, what you are doing on "// do something" block? Have you implemented any complex logic there?

Also, you can consider the below stack overflow discussion:

http://stackoverflow.com/questions/3808368/c-sharp-how-to-copy-a-single-excel-worksheet-from-one-workbook-to-another

Hope this helps!
0
Lili R10y ago
Hi Rajeesh..

Thanks for the suggestion that was great, but i wanted to fill the cells of every sheet in an excel file, where we do not know how many sheets are present.

Actually.. i tried and what i did is:

//1. open excel file and after that
int sheetCount = workBook.workSheets.Count;

//2. put the sheet names in an array
//3. put a loop for sheet count
//4. then, for each sheet open & read the current sheet by sheetCount and do the needfull
// 5. then, save the sheet and close the connection;
//6. then again open and do the steps 4 & 5

for (int c = 0; c < sheetCount; c++)
{

            Excel.Application App = new Excel.Application();
          Excel.Workbook mWorkBook = App.Workbooks.Open(destFile, missing, false, missing,                                  missing, missing, true, missing, missing, true, missing, missing, missing,                                  missing, missing);
            Excel.Worksheet wSheet = new Excel.Worksheet();
        wSheet = (Excel.Worksheet)mWorkBook.Worksheets.get_Item(sheetname);
            Excel.Range range = mWSheet1.UsedRange;

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +                                  destFile + @";Extended Properties=""Excel                                  8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
            conn.Open();
            OleDbCommand command = new OleDbCommand
            (
            "Select * from [" + sname[c] + "$]", conn//sname[c] + "$]", conn
            );
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            adapter.Fill(dt);

            conn.Close();

           //
           //do
           //something
           //


            dt.Clear();
            wSheet = null;
            mWorkBook.SaveAs(destFile, missing, missing, missing, missing, missing,                            Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing,                            missing, missing, missing, missing);
            mWorkBook.Close(missing, missing, missing);
            App.Quit();
}

This code will save the sheet and close the file, and then open the file again and do the same for next sheet, untill done for all sheets.

This piece of code is working fine, working as i wanted, but is slow as it opens the file again and again for every sheet present in the file. So, i wish there can be some better way. Please suggest if you got any idea.

Thanks again.
0
Rajeesh Menoth10y ago
Hi,

Check this reference :

https://www.daniweb.com/programming/software-development/threads/437567/write-data-to-excel-in-multiple-sheets-using-c
0