Auto width of column and word wrap of headers not working in openxml excel

See more:

C#3.5

OpenXML

While I'm trying to open excel file using OpenXML. Excel is coming fine but my style property is not taking.

1. I need to give my excel data column has auto width property.I found this solution but its not working for me

https://stackoverflow.com/questions/18268620/openxml-auto-size-column-
width- in-excel



2.I am not able to word-wrap my column headers but values getting wrapped


any help or suggestions is much appreciated!

What I have tried:

my excel code having the width property to set

public static string OpenXMLCreateXL(string FolderPath, DataSet tableSet)
     {
     WorkbookPart wBookPart = null;
     var datetime = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
      string FilePath = "";
       foreach (DataTable table1 in tableSet.Tables)
      {
      if (table1.Rows.Count != 0)
      {
      using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(FilePath, 
     SpreadsheetDocumentType.Workbook))
     {
     wBookPart = spreadsheetDoc.AddWorkbookPart();
    wBookPart.Workbook = new Workbook();
    uint sheetId = 1;
    spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
    Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild();
    WorkbookStylesPart wbsp = wBookPart.AddNewPart();
    wbsp.Stylesheet = CreateStylesheet();
    wbsp.Stylesheet.Save();
     foreach (DataTable table in tableSet.Tables)
     {
     if (table.Rows.Count != 0)
     {
     table.TableName = table.Rows[0]["LeaseCondition"].ToString();
     WorksheetPart wSheetPart = wBookPart.AddNewPart();
     Sheet sheet = new Sheet() { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wSheetPart), SheetId = 
    sheetId, Name = table.TableName };
    sheets.Append(sheet);
    SheetData sheetData = new SheetData();
    wSheetPart.Worksheet = new Worksheet();
    Row headerRow = new Row();
    Columns columns = new Columns();
    int ColumnNumber = 1;
      foreach (DataColumn column in table.Columns)
     {
       Cell cell = new Cell();
         cell.DataType = CellValues.String;
        cell.CellValue = new CellValue(column.ColumnName);
        cell.StyleIndex = 2;
        headerRow.AppendChild(cell);
        Column column1 = new Column();  
         column1.Width = 30; ;
       column1.BestFit = true;
        column1.CustomWidth = true;        //how i can set autowidth here?
       column1.Min = Convert.ToUInt32(ColumnNumber);
      column1.Max = Convert.ToUInt32(ColumnNumber);
      columns.AppendChild(column1);
       ColumnNumber = ColumnNumber + 1;
        }
         wSheetPart.Worksheet.AppendChild(columns);
         sheetData.AppendChild(headerRow);
         foreach (DataRow dr in table.Rows)
        {
       Row row = new Row();
      foreach (DataColumn column in table.Columns)
         {
      Cell cell = new Cell();
       cell.DataType = CellValues.String;
      cell.CellValue = new CellValue(dr[column].ToString());
       cell.StyleIndex = 1;
      row.AppendChild(cell);
    }
    sheetData.AppendChild(row);
      }
       sheetId++;
      wSheetPart.Worksheet.AppendChild(sheetData);
        }
        }
      }
      }
        }
         return FilePath;
         }



//My word-wrap code

           Alignment alignment0 = new Alignment();
            alignment0.WrapText = true;
            alignment0.Vertical = VerticalAlignmentValues.Top;
           

            CellFormats cellFormats = new CellFormats(
                    new CellFormat(new Alignment() { WrapText = true }), // default
                    new CellFormat { FontId = 0, FillId = 0, BorderId = 
          1,ApplyBorder = true, Alignment = alignment0, ApplyAlignment = true }, // 
         body
                    new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill 
           = true ,ApplyAlignment = true,} // header
                );</pre>