Exporting Excel worksheets to Word maintaining merged cells

[wangarat]

Hello!
As part of my documentation procedures automation project I use Spire.Office to convert .xlsx document containing multiple worksheets to .doc document.

Overall, auto copy paste works well, I've modified "How to Export Excel Data to Word Table Maintaining Formatting in C#" Tutorial code.

However merged cells are ignored and copied as individual cells. Here is the code:

Code: Select all

class ToWord
    {
        public void CopyToWord(string fpath, int qnumber)
        {
            Workbook spirewb = new Workbook();
            spirewb.LoadFromFile(fpath);

            Document doc = new Document();
            for (int i = 1; i <= qnumber; i++)
            {
                Spire.Xls.Worksheet sheet = spirewb.Worksheets[i];

                Table table = doc.AddSection().AddTable(true);
                table.ResetCells(sheet.LastRow, sheet.LastColumn);

                for (int r = 1; r <= sheet.LastRow; r++)
                {
                    for (int c = 1; c <= sheet.LastColumn; c++)
                    {
                        CellRange xCell = sheet.Range[r, c];
                        TableCell wCell = table.Rows[r - 1].Cells[c - 1];
                        //fill data to word table
                        TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
                        //copy font and cell style from excel to word
                        CopyStyle(textRange, xCell, wCell);
                    }
                }
            }
               
            doc.SaveToFile("result.doc", Spire.Doc.FileFormat.Doc);
            System.Diagnostics.Process.Start("result.doc");
        }

        private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
        {
            //copy font stlye
            wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
            wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
            wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
            wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
            wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
            //copy backcolor
            wCell.CellFormat.BackColor = xCell.Style.Color;
            //copy text alignment
            switch (xCell.HorizontalAlignment)
            {
                case HorizontalAlignType.Left:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
                    break;
                case HorizontalAlignType.Center:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
                    break;
                case HorizontalAlignType.Right:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
                    break;
            }
        }

Please refer to this code or the one from documentation.

[rachel.lei]

Hello,

Thanks for your inquiry.
Please refer to the code below to maintain merged cells when exporting Excel worksheets to Word. If there is any question, please provide your Excel file for further investigation.

Code: Select all

        public static void CopyToWord(string fpath, int qnumber)
        {
            Workbook spirewb = new Workbook();
            spirewb.LoadFromFile(fpath);

            Document doc = new Document();
            for (int i = 1; i <= qnumber; i++)
            {
                Spire.Xls.Worksheet sheet = spirewb.Worksheets[i];
                Table table = doc.AddSection().AddTable(true);
                table.ResetCells(sheet.LastRow, sheet.LastColumn);

                for (int r = 1; r <= sheet.LastRow; r++)
                {
                    for (int c = 1; c <= sheet.LastColumn; c++)
                    {
                        CellRange xCell = sheet.Range[r, c];

                        //!!!
                        //Get the merge area
                        CellRange mergeArea = xCell.MergeArea;
                        if (mergeArea != null && mergeArea.Row == r && mergeArea.Column == c)
                        {
                            int rowIndex = mergeArea.Row;
                            int columnIndex = mergeArea.Column;
                            int rowCount = mergeArea.RowCount;
                            int columnCount = mergeArea.ColumnCount;

                            //Merge cells of the Word table
                            for (int m = 0; m < rowCount; m++)
                            {
                                table.ApplyHorizontalMerge(rowIndex - 1 + m, columnIndex - 1, columnIndex + columnCount - 2);
                            }
                            table.ApplyVerticalMerge(columnIndex - 1, rowIndex - 1, rowIndex + rowCount - 2);             
                        }
                        //!!!

                        TableCell wCell = table.Rows[r - 1].Cells[c - 1];
                        //fill data to word table
                        if (!string.IsNullOrEmpty(xCell.NumberText))
                        {
                            //wCell.CellFormat.
                            TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
                            CopyStyle(textRange, xCell, wCell);
                        }
                        else
                        {
                            wCell.CellFormat.BackColor = xCell.Style.Color;
                        }                                     
                    }
                }             
            }
               
            doc.SaveToFile("result.doc", Spire.Doc.FileFormat.Doc);
            System.Diagnostics.Process.Start("result.doc");
        }

        private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
        {
            //copy font stlye
            wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
            wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
            wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
            wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
            wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
            //copy backcolor
            wCell.CellFormat.BackColor = xCell.Style.Color;
            //copy text alignment
            switch (xCell.HorizontalAlignment)
            {
                case HorizontalAlignType.Left:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
                    break;
                case HorizontalAlignType.Center:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
                    break;
                case HorizontalAlignType.Right:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
                    break;
            }
            switch (xCell.VerticalAlignment)
            {
                case VerticalAlignType.Bottom:
                    wCell.CellFormat.VerticalAlignment = VerticalAlignment.Bottom;
                    break;
                case VerticalAlignType.Center:
                    wCell.CellFormat.VerticalAlignment = VerticalAlignment.Middle;
                    break;
                case VerticalAlignType.Top:
                    wCell.CellFormat.VerticalAlignment = VerticalAlignment.Top;
                    break;
            }
        }

Sincerely,
Rachel
E-iceblue support team

[wangarat]

Thanks a lot. The code you've provided did export worksheets merged cells with correct formatting.

[rachel.lei]

Hello,

Thanks for your feedback.
If you encounter any issues related to our product in the future, please feel free to contact us.
Have a nice day!

Sincerely,
Rachel
E-iceblue support team