locked
Excel, saveas worksheet RRS feed

  • Question

  • User-419423739 posted

    Hi,
    I need to divide one excel file in more sheets.
    If the excel file got 30 sheets I need to save 30 files .xls with the name of the sheets.

    I have tried with this:

    Excel.Application ExcelObj = new Excel.Application();
    Excel.Sheets sheets;
    Excel.Worksheet worksheet;
    
    Excel.Workbook workBook = ExcelObj.Workbooks.Open(filename, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    sheets = workBook.Worksheets;
    
    for (int i = 0 + incr; i < sheets.Count; i++)
    {
    worksheet = (Excel.Worksheet)sheets.get_Item(i + 1);
    worksheet.SaveAs(Server.MapPath("Upload/") + worksheet.Name + ".xls", Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,Type.Missing);
    }


    and at last I've 30 files... but when I open every single one of this files it got more than one sheet.
    Why ??! 

    Wednesday, December 1, 2010 5:57 PM

Answers

  • User551462331 posted

    here in this code, you are opening evry worksheet and saving the complete workbook with another name (sheet name)...

    its like manually opening a workbook and then going to every sheet, copying the name of sheet and going to file->save as menu and saving. this will save complete workbook because, worksheet is part of workbook..

    the only way i can see to do this is....

    1) inside for loop, create new workbook...

    2) copy current sheet content ((Excel.Worksheet)sheets.get_Item(i + 1) ) to new workbook sheet

    3) save new workbook with new name (current sheet name)..

    please let us know if you need help with code...

    hope this helps....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 2, 2010 12:08 AM