Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Sep 14, 2011 4:28 am

I am pulling Excel byte arrays from a SSRS webservice. I load these byte arrays in to a Workbook object using a MemoryStream and the Workbook.LoadFromStream method. I then pull the Worksheets out of the Workbook object and place them in to a "master" workbook object using Worksheets.AddCopy so I can consolidate multiple reports from SSRS in to a single workbook. The problem I’m having is that when I use AddCopy to add the Worksheet to the "MasterWorkbook" it adds 3 additional Worksheets before the report Worksheet(for a total of 4) and it completely messes up the formatting and colors of the report that I pulled from SSRS. If I simply load the MemoryStream in to a Workbook object and return it back to the browser using Workbook.SaveToHttpResponse it works perfectly so I don’t think it’s the SSRS byte array or the conversion to a MemoryStream. I have played around with the WorksheetCopyType flags but none of them seem to help. Any ideas?

Thanks
Joe

BTW Here is my code:

Code: Select all
//results is the byte array from SSRS
MemoryStream WorksheetAsStream = new MemoryStream(results);
Spire.Xls.Workbook WB = new Spire.Xls.Workbook();
WB.LoadFromStream(WorksheetAsStream);
WorksheetAsStream.Close();
MasterWorkbook.Worksheets.AddCopy(WB.Worksheets);
MasterWorkbook.SaveToHttpResponse("Report.xls", Response);

jpemberton42
 
Posts: 1
Joined: Thu Sep 01, 2011 10:26 pm

Mon Sep 19, 2011 10:03 am

Dear Joe,

Sorry for this inconvenience caused by us.
When a new workbook object is created, during the initialization, 3 blank worksheets are added also. So you can remove it before saving.
eg.
Code: Select all
    class Program
    {
        public static Stream GetStream(String name)
        {
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = name;

            sheet.Range["A1"].Text = "Hello, I'm the sheet " + name;
            Random random = new Random();
            sheet.Range["A1"].Style.Font.Color = Color.FromArgb(random.Next(256), random.Next(256), random.Next(256));

            sheet.Range["B1"].Text = "World";

            int length = FontFamily.Families.Length;
            sheet.Range["B1"].Style.Font.FontName = FontFamily.Families[random.Next(length)].Name;

            MemoryStream buffer = new MemoryStream();
            workbook.SaveToStream(buffer);
            buffer.Position = 0;

            return buffer;
        }

        static void Main(string[] args)
        {
            Workbook masterWorkbook = new Workbook();
            //number of the blank sheet
            int count = masterWorkbook.Worksheets.Count;
            for(int i = 0; i < 5; i++)
            {
                String name = String.Format("Report{0}", i);
                //load
                Workbook workbook = new Workbook();

                using (Stream results = GetStream(name))
                {
                  workbook.LoadFromStream(results);
                }
                Worksheet sheet = workbook.Worksheets[0];
                masterWorkbook.Worksheets.AddCopy(sheet);
            }

            //remove the blank sheets
            for(int i = 0; i < count; i++)
            {
                masterWorkbook.Worksheets.Remove(0);
            }

            masterWorkbook.SaveToFile("Test.xls");
            System.Diagnostics.Process.Start(masterWorkbook.FileName);
       }
    }


Please try our demo and if the styles still mess up, please send us your excel files saved from SSRS webservice.
Tina
Technical Support/Developer,
e-iceblue Support Team
User avatar

Tina.Lin
 
Posts: 152
Joined: Tue Sep 13, 2011 5:37 am

Return to Spire.XLS

cron