none
Autofit column using DocumentFormat.OpenXml C# RRS feed

  • Question

  • I want to autofit my column using XmlWriter. Bellow you can find my code for exporting in Excel. I am creating a document excel with one sheet or more and a finite set of columns( in this case just two).

    I need the equivalent for :

     Excel.Range cell_in1 = workSheet.Cells[row, "A"];
                                    Excel.Range cell_in2 = workSheet.Cells[row, "B"];
                                    Excel.Range range_11 = workSheet.get_Range(cell_in1, cell_in2);
                                    range_1.Columns.AutoFit();
    

    Are there any functions for this or I need to calculate the Width? Thank you

    Here is my code for openXML:

     using (var myDoc = SpreadsheetDocument.Open(filePath, true))
                {
                    WorkbookPart workbookPart = myDoc.WorkbookPart;
    
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == listSheetName[i]);
                    var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
    
                    var orgSheetId = workbookPart.GetIdOfPart(worksheetPart);
    
                    WorksheetPart replacementPart = workbookPart.AddNewPart<WorksheetPart>();
                    var replacementPartId = workbookPart.GetIdOfPart(replacementPart);
    
                    var xmlReader = OpenXmlReader.Create(worksheetPart);
                    var xmlWriter = OpenXmlWriter.Create(replacementPart);
    
                    var cell = new Cell();
    
                    while (xmlReader.Read())
                    {
                        if (xmlReader.ElementType == typeof(SheetData))
                        {
                            if (xmlReader.IsEndElement)
                            {
                                continue;
                            }
    
                            xmlWriter.WriteStartElement(new SheetData());
    
                            xmlWriter.WriteStartElement(new Row());
    
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue("COLUMN 1");
                            xmlWriter.WriteElement(cell);
    
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue("COLUMN 2");
                            xmlWriter.WriteElement(cell);                           
    
                            xmlWriter.WriteEndElement();
    
                            foreach (string elem in  listSheetName[i]))
                            {
                                xmlWriter.WriteStartElement(new Row());
    
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(elem.Id);
                                xmlWriter.WriteElement(cell);
    
                                cell.DataType = CellValues.String;
                                cell.CellValue = new CellValue(elem.Name);
                                xmlWriter.WriteElement(cell);
    
    
                                xmlWriter.WriteEndElement();
                            }
                        }
                        else
                        {
                            if (xmlReader.IsStartElement)
                            {
                                xmlWriter.WriteStartElement(xmlReader);
                            }
                            else if (xmlReader.IsEndElement)
                            {
                                xmlWriter.WriteEndElement();
                            }
                        }
                    }
    
                    xmlReader.Close();
                    xmlWriter.Close();
    
                    Sheet sheet1 = workbookPart.Workbook.Descendants<Sheet>().First(s => s.Id.Value.Equals(orgSheetId));
                    sheet1.Id.Value = replacementPartId;
    
                    workbookPart.DeletePart(worksheetPart);
                }
    


    • Edited by Quitty15 Thursday, September 26, 2019 6:51 AM
    Thursday, September 26, 2019 6:48 AM