none
Auto Wrap the Cell in Excel using Open XML SDK

    Question

  • hello
    I've created Excel Sheet using Open XML SDK 2.0 and now i want to know how to auto wrap the cell using OPEN XML SDK??
    Please let me know asap.

    Thanks in advance.
    Mohit
    Mohit
    • Moved by Liliane Teng Friday, August 12, 2011 8:17 AM (From:Visual Basic IDE)
    Wednesday, August 10, 2011 11:15 AM

Answers

  • Hi Mkariya,

    You can define cell format in the style.xml for the cell you want to wrap. Please see the following xml information:

    <x:cellXfs count="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
      <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
        <x:alignment wrapText="1" />
      </x:xf>
    </x:cellXfs>

    You can set the value of wrapText to 1 to enable the wrap for the cell. And here is the code snippet for you to reference: 

    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    
    namespace GeneratedCode
    {
     public class GeneratedClass
     {
     // Creates an CellFormats instance and adds its children.
     public CellFormats GenerateCellFormats()
     {
      CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)2U };
      CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
    
      CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
      Alignment alignment1 = new Alignment(){ WrapText = true };
    
      cellFormat2.Append(alignment1);
    
      cellFormats1.Append(cellFormat1);
      cellFormats1.Append(cellFormat2);
      return cellFormats1;
     }
     }
    }
    

    Hope this can help you and feel free to follow up after you have tried.  

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    • Marked as answer by Mohit Kariya Tuesday, August 16, 2011 12:06 PM
    Tuesday, August 16, 2011 6:40 AM

All replies

  • Hi mkariya,

    Thanks for your post.

    I move this thread to Open XML Format SDK forum which is more appropriate for this aspect issues. And more this aspect experts live here. Thanks for your understanding.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 12, 2011 8:46 AM
  • Hi Mkariya,

    You can define cell format in the style.xml for the cell you want to wrap. Please see the following xml information:

    <x:cellXfs count="2" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
      <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
        <x:alignment wrapText="1" />
      </x:xf>
    </x:cellXfs>

    You can set the value of wrapText to 1 to enable the wrap for the cell. And here is the code snippet for you to reference: 

    using DocumentFormat.OpenXml.Spreadsheet;
    using DocumentFormat.OpenXml;
    
    namespace GeneratedCode
    {
     public class GeneratedClass
     {
     // Creates an CellFormats instance and adds its children.
     public CellFormats GenerateCellFormats()
     {
      CellFormats cellFormats1 = new CellFormats(){ Count = (UInt32Value)2U };
      CellFormat cellFormat1 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
    
      CellFormat cellFormat2 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyAlignment = true };
      Alignment alignment1 = new Alignment(){ WrapText = true };
    
      cellFormat2.Append(alignment1);
    
      cellFormats1.Append(cellFormat1);
      cellFormats1.Append(cellFormat2);
      return cellFormats1;
     }
     }
    }
    

    Hope this can help you and feel free to follow up after you have tried.  

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.



    • Marked as answer by Mohit Kariya Tuesday, August 16, 2011 12:06 PM
    Tuesday, August 16, 2011 6:40 AM
  • Hi Bruce,

    I am having similar problem and posted a couple of questions. Can you please take a look at below links...

    http://social.msdn.microsoft.com/Forums/en/oxmlsdk/thread/c08c924b-0e01-460f-b3d6-903d660a54b8

    http://social.msdn.microsoft.com/Forums/en/oxmlsdk/thread/3c2a60fc-de6d-4ffa-98ec-760eba2d72fd

    -Jahedur Rahman

    Tuesday, August 16, 2011 8:53 AM
  • Hi Jahedur Rahman,

    I will spend some time looking into your problem and try to give you the suggestion. There might be some delay about response. Appreciate your patience.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 17, 2011 1:43 AM
  • hello Jahedur

    plz refere following two links. It might be useful. because I've already done with back color, fore color n bold text.

     

    http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspx

    http://www.lateral8.com/articles/2010/6/11/openxml-sdk-20-formatting-excel-values.aspx


    Mohit
    Wednesday, August 17, 2011 6:11 AM