none
C# Excel - Problem with inserting vertical pagebreak at Merged Cells column RRS feed

  • Question

  • Hello. I have a worksheet which has many columns, the latter ones of which are merged over 2 columns on the headers. When I try programmatically to insert another column just after where the vertical line breaks are in the worksheet, (to show/insert a column of numbers copied from the left side so the user can read the sheet easily), I get an exception if it seems the column where the line break is, is the 2nd one of the merged set, as the exception returns the fact that a merged cell (column then) cannot be split into two, understandably.

    So what I want to do is somehow find out the reference or how to get the address for the 2nd column so that - if it is a 2nd column in the set, I can jump another column to the right and then insert the new column. C# and Excel does not mind that at all - it will let me insert a column either before the leftmost one of the 2 column set or after the 2nd one, but not in the middle, naturally! Here is my existing code and I wonder if anyone else has had this problem. Essentially I need to check if I have "landed" in the middle of a 2 column merged set. I have looked at Range.MergedCells but as far as I can see that is only telling that the range is merged, which I know already. I need to find out is the column "landed" at the 2nd one in the set of two. Any ideas would be very welcome! Thanks 

    if (xlWS.VPageBreaks.Count > 1) { // Schleife für jede Vertikale pagebreak in Sheet: foreach (Excel.VPageBreak pageBreak in xlWS.VPageBreaks) { int colPageBreak = pageBreak.Location.Column; // + 1 nach der Pagebreak, - 1 vor dem Pagebreak // Jetzt neu Spalte für Reihen Nummern hinfügen: Excel.Range colRng = xlWS.Range[xlWS.Cells[1, colPageBreak], xlWS.Cells[iLastRow, colPageBreak]]; // Ganze Spalte eigentlich. colRng.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow); // Einfach Spalte 1 kopieren zur neue Spalte: Excel.Range colNr = xlWS.Range[xlWS.Cells[1, 1], xlWS.Cells[iLastRow, 1]]; // Ganze Spalte eigentlich. // Update range als neue Spalte hingefügt: colRng = xlWS.Range[xlWS.Cells[1, colPageBreak], xlWS.Cells[iLastRow, colPageBreak]]; // Ganze Spalte eigentlich. colNr.Copy(colRng); // ERROR OCCURS HERE WHEN TRYING TO COPY // Spalte Autofit lassen: colRng.Columns.AutoFit(); // Nur eine Spalte jedes mal. // Nun, wenn der Spalte ist verkleinert, es wird zu den letzte Seite zurückspringen, // und wir werden die Nummern auf der rechten Seite von dem ersten Seite haben. // Dass natürlich wollen wir nicht so wir machen ein Manuelles Page Break immer zum // linke von dem Spalte gerade hingefügt. Und dass nach unten macht so was: // This example sets a manual page break to the left of column colPageBreak on WorkSheet.

    xlWS.Columns[colPageBreak].PageBreak = Excel.XlPageBreak.xlPageBreakManual; // Zum näcshten Vertikale Page Break } }




    • Edited by Sansevieria Wednesday, June 24, 2015 4:17 PM
    • Moved by Kristin Xie Thursday, June 25, 2015 1:55 AM move to appropriate forum
    Wednesday, June 24, 2015 4:12 PM

All replies

  • Hi Sansevieria,

    According to your description, your case more related to Excel, I will move your case to Excel for developers forum for better support.

    Best regards,

    Kristin


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 25, 2015 1:54 AM
  • Hi Sansevieria,

    >>When I try programmatically to insert another column just after where the vertical line breaks are in the worksheet, (to show/insert a column of numbers copied from the left side so the user can read the sheet easily), I get an exception if it seems the column where the line break is, is the 2nd one of the merged set, as the exception returns the fact that a merged cell (column then) cannot be split into two<<

    What's the execption you got? I suggest that you insert the column manually and record macro to get the code for reference.

    >>Essentially I need to check if I have "landed" in the middle of a 2 column merged set. I have looked at Range.MergedCells but as far as I can see that is only telling that the range is merged, which I know already. I need to find out is the column "landed" at the 2nd one in the set of two. <<

    What did you mean the "landed"?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 5:26 AM
    Moderator
  • Hi Sansevieria,

    Have you fixed this issue now? If yes, would you mind you how fix this issue? It is helpful for others who have the same issue.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 3, 2015 8:48 AM
    Moderator
  • No as I recall I never fixed it. Sorry. I just left it to page break at that point. Didn't have enough (project) time otherwise and the client wasn't too bothered.
    Tuesday, December 13, 2016 3:11 PM