Save to multiple sheets

Question

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.

Lili R · Answer

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

Francis · Answer

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!

Lili R · Answer

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 { 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.

Rajeesh Menoth · Answer

Hi, Check this reference : https://www.daniweb.com/programming/software-development/threads/437567/write-data-to-excel-in-multiple-sheets-using-c

Save to multiple sheets

Answers (4)