Click here to Skip to main content
15,032,024 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


C#
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>
Posted
Updated 16-Sep-20 4:22am

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900