Table Format disturb while copying excel worksheet data(having tables & chart ) to Word document .How to make it fit properly

Aspose.Cells Product Family
1

Hello Team ,

We are facing format issue , while replacing Table Data from excel worksheet to the Word documents , if table is big for format getting disturb , can you please provide solution or code sample which help us , I have attach sample document which have format issue

application (12) (1).docx (138.6 KB)

Thanks,
Avinash

2

@avinashchilke1
Would you like to provide Excel sample files and runnable test code? If you could take a screenshot and highlight the incorrect location, it would be very helpful for us to locate the issue. We will check it soon.

3

Hello John ,
Thanks for quick reply .

ACFR Section 4 Reports.zip (127.1 KB)

Please check attach excel file in zip format .

Thanks,
Avinash

4

Hello John ,

Please check attach excel and code sample.

Thanks,
Avinash
ACFR Section 4 Reports.zip (127.1 KB)

Code_API3.zip (1.5 KB)

5

@avinashchilke1
Thank you for your feedback. Did you manually copy Excel data to a Word document? If it is automatically copied by the program, would you like to provide runnable test code? We will check it soon.

6

Program.zip (178.4 KB)

DocumentExcelService.zip (1.7 KB)
Please check attach file & create console app
Thanks ,
Avinash
Poc_excel_doc.zip (761.4 KB)

7

@avinashchilke1
Thank you for your feedback. Due to the large amount of table data, there may be table format disturb when inserting data into a Word file. By calling the Worksheet.AutoFitColumns() method, column width can be better matched with data width. Of course, this can only have an adjusting effect when the column width is greater than the data width. If there are too many columns in the data table, resulting in a width that is too large, I believe there is no way to maintain the original format of the data and transplant it to a Word file.

After calling the Worksheet.AutoFitColumns() method, better results were obtained. Please refer to the attachment. output_net.zip (37.1 KB)

public string ReplaceExcelSheetsAsHtmlInWord(string wordBase64, string excelBase64, Dictionary<string, object> tags)
{
    byte[] wordBytes = Convert.FromBase64String(wordBase64);
    byte[] excelBytes = Convert.FromBase64String(excelBase64);

    using var wordStream = new MemoryStream(wordBytes);
    Document wordDoc = new Document(wordStream);
    ReplaceTagsInWordDocument(wordDoc, tags);
    DocumentBuilder builder = new DocumentBuilder(wordDoc);

    using var excelStream = new MemoryStream(excelBytes);
    Workbook workbook = new Workbook(excelStream);

    var sheets = workbook.Worksheets.Cast<Worksheet>().ToList(); // snapshot again

    foreach (Worksheet sheet in sheets)
    {
        //add this line to autofit the columns
        sheet.AutoFitColumns();
        string placeholder = $"{{{{Rpt.{sheet.Name}}}}}";

        if (wordDoc.Range.Text.Contains(placeholder))
        {
            string htmlContent = ConvertSheetToHtml(workbook, sheet.Index);
            string tempMarker = $"###REPLACEME_{Guid.NewGuid()}###";

            wordDoc.Range.Replace(placeholder, tempMarker);
            int columnCount = sheet.Cells.MaxDataColumn + 1;

            bool forceLandscape = columnCount > 8;
            InsertHtmlAtMarker(wordDoc, builder, tempMarker, htmlContent, forceLandscape);
        }
    }

    using var outputStream = new MemoryStream();
    wordDoc.Save(outputStream, Aspose.Words.SaveFormat.Docx);
    wordDoc.Save("output_net.docx", Aspose.Words.SaveFormat.Docx);
    return Convert.ToBase64String(outputStream.ToArray());
}

Additionally, if there is too much table data that prevents it from being inserted into a Word file in its original format. Modifying data to better adapt to Word is also a temporary solution.

8

@John.He , Thanks for reply , Still we have issue with formatting , is there any way to convert page into Landscape mode if table data is more or Can we reduce the size of table with So that it can fit in Page.

You help is appreciated .
Thanks,
Avinash

9

@avinashchilke1,

Excel and Word are distinct file formats with unique architectures and layouts, resulting in different attributes and limitations. This means you might not achieve 100% accurate results when transferring data from Excel to Word, especially in terms of formatting. For more information on Excel’s specifications and limits, you can refer to this document: Excel Specifications and Limits|Documentation. To learn how to set the orientation of a worksheet or page to Landscape or Portrait, visit: Setting Page Options|Documentation. You might consider using Aspose.Words APIs to adjust the layout and formatting of the data. For any queries related to formatting in Aspose.Words, feel free to post in the Aspose.Words forum.