none
Excel Interop setting borders bloats file size RRS feed

  • Question

  • I am setting the borders to a pivot table using C# Excel interop code as follows - 

    Excel.Range startCell = (Excel.Range)newSheet.Cells[11, 1];
    Excel.Range endCell = (Excel.Range)newSheet.Cells[416, 18];

    Excel.Range pivotrange = newSheet.Range[startCell, endCell];

    pivotrange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
    pivotrange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
    pivotrange.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
    pivotrange.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
    pivotrange.Borders.Color = Color.Black;

    This works fine. However the filesize of the resulting excel file is a almost twice the filesize compared to if I did it through the tool UI. I compared the VB code generated via Excel for the UI operation and effectively the code seems to be doing the same thing. So why is my code generated file so much larger? I can comment out the border code and the resulting excel file is a lot smaller.

    Any idea?

    Thanks in advance,
    Jake.

    Saturday, January 20, 2018 10:08 PM

All replies

  • Hello jacobUT,

    I tried to use a workbook and your code to set borders for range "Cells[11,1]" to "Cells[416,18]"(not a pivot table, I did not have a pivot table with such big range). The result file size does not get too big.

    I also tried to use a workbook with a pivot table with smaller range than your code. Result file size does not change too much too.

    I would suggest you share your whole code and a simply file so we could try to reproduce your issue.

    You could share the file via Cloud Storage, such as One Drive, and then put the link address here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 22, 2018 3:25 AM