Auto width of column and word wrap of Headers not working in OpenXML

Question

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

below is 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

);

any help or suggestions is much appreciated!