Auto width of column and word wrap of Headers not working in 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

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!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sujeet Raman6y ago
how can i impliment this without much change in my existing code?
0
alice yang6y ago
You can try Free Spire.XLS API to export datatable to excel, auto fit columns and wrap text, it's very simple.
//Create a Workbook
Workbook workbook = new Workbook();

//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//export datatable to the worksheet
sheet.InsertDataTable(datatable, true, 1, 1);

//Auto fit columns
sheet.AllocatedRange.AutoFitColumns();
//Wrap text
sheet.AllocatedRange.Style.WrapText = true;

//Save the file
workbook.SaveToFile("ImportToExcel.xlsx",ExcelVersion.Version2013);
0