Worksheet.AddCopy messes up formatting

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

Worksheet.AddCopy messes up formatting

Postby jpemberton42 » 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?


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();
MasterWorkbook.SaveToHttpResponse("Report.xls", Response);
Posts: 1
Joined: Thu Sep 01, 2011 10:26 pm

Re: Worksheet.AddCopy messes up formatting

Postby Tina.Lin » 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.
Code: Select all
    class Program
        public static Stream GetStream(String name)
            Workbook workbook = new Workbook();
            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();
            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);
                Workbook workbook = new Workbook();

                using (Stream results = GetStream(name))
                Worksheet sheet = workbook.Worksheets[0];

            //remove the blank sheets
            for(int i = 0; i < count; i++)


Please try our demo and if the styles still mess up, please send us your excel files saved from SSRS webservice.
Technical Support/Developer,
e-iceblue Support Team
User avatar
Posts: 152
Joined: Tue Sep 13, 2011 5:37 am

Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 0 guests