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!