Duplicate row (formulas + style)

[nolme]

Hi,

is there a snippet or function to duplicate a cell or a row ? (like adding a line in an invoice)

Thanks,

Vincent

[nolme]

I've used the code :

Code: Select all

sheet.InsertRow(newRow, 1, InsertOptionsType.FormatAsAfter);

but :
- it doesn't replicate merged B + C column cell
- it doesn't replicate currency (€)

Vincent

[Gary.zhang]

Hello,

Thanks for your inquiry.
I have tested with the following method along with the document you provided, both can work, please note that the index of row starts with 0.

Code: Select all

Workbook book = new Workbook();
book.LoadFromFile( "facture 2 (defaut).xlsx", ExcelVersion.Version2010);
Worksheet sheet = book.Worksheets[0];
sheet.InsertRow(22, 1, InsertOptionsType.FormatAsBefore);


Please let us know if you have any questions.
Gary
E-iceblue support team

[nolme]

Strange thing. It don't work with odd line number :

Don't work :

Code: Select all

sheet.InsertRow(19, 1, InsertOptionsType.FormatAsBefore);

Work :

Code: Select all

sheet.InsertRow(18, 1, InsertOptionsType.FormatAsBefore);

[Gary.zhang]

Hello,

Thanks for your response. I have noticed the issue you mentioned, and I have transferred it to our Dev team, we will let you know as soon as there is any update.
Thanks,
Gary
E-iceblue support team

[Gary.zhang]

Hello,

We just released a newest hotfix of Spire.Office(Spire.Office Platinum (Hot Fix) Version:2.12.1), in which the issue has get resolved, you can download and test.
Please contact us if you have any questions.
Sincerely,
Gary
E-iceblue support team

[nolme]

Not working in my case.

Code: Select all

sheet.InsertRow(24, 1, InsertOptionsType.FormatAsBefore);

Columns B & C are not merged

[nolme]

Here's a workaround to duplicate style :

Code: Select all

/// <summary>
        /// Duplicate Excel row style
        /// </summary>
        /// <param name="sheet">Excel document.</param>
        /// <param name="sourceLine">Source line to copy.</param>
        /// <param name="destinationLine">Destination line for copy.</param>
        /// <param name="maxColumns">Max columns to duplicate.</param>
        /// <param name="version">The Excel version to use.</param>
        public static void DuplicateRowStyle(Worksheet sheet, int sourceLine, int destinationLine, int maxColumns, ExcelVersion version)
        {
            // Limit max columns for speed
            int max = maxColumns < ExcelRequirements.MaxColumns(version) ? maxColumns : ExcelRequirements.MaxColumns(version);

            // Loop on each column to duplicate cells
            int col = 1;
            do
            {
                string sourceCellName = ExcelUtility.ColumnNameFromNumber(col) + sourceLine.ToString();
                string destinationCellName = ExcelUtility.ColumnNameFromNumber(col) + destinationLine.ToString();
                CellRange sourceCell = sheet.Range[sourceCellName];
                CellRange destinationCell = sheet.Range[destinationCellName];

                if (sourceCell.HasMerged == true)
                {
                    Duplicate(sourceCell, destinationCell);

                    // Skip merge cells
                    int sourceMergeStart = col;
                    int sourceMergeEnd = col;
                    do
                    {
                        sourceMergeEnd++;
                    } while ((sourceMergeEnd < max) && (sheet.Range[ExcelUtility.ColumnNameFromNumber(sourceMergeEnd) + sourceLine.ToString()].HasMerged == true));

                    // Merge new area on destination line
                    string range = ExcelUtility.ColumnNameFromNumber(sourceMergeStart) + destinationLine.ToString() + ":" + ExcelUtility.ColumnNameFromNumber(sourceMergeEnd-1) + destinationLine.ToString();
                    sheet.Range[range].Merge();

                    col += (sourceMergeEnd - sourceMergeStart);
                }
                else
                {
                    Duplicate(sourceCell, destinationCell);
                    col++;
                }
            } while (col < max);
        }

        /// <summary>
        /// Duplicates the specified cell range attribute to another range.
        /// </summary>
        /// <param name="source">The source range.</param>
        /// <param name="destination">The destination range.</param>
        private static void Duplicate(CellRange source, CellRange destination)
        {
            destination.Style = source.Style;
            destination.NumberFormat = source.NumberFormat;
            destination.HorizontalAlignment = source.HorizontalAlignment;
            destination.VerticalAlignment = source.VerticalAlignment;

            destination.Borders.LineStyle = source.Borders.LineStyle;
            destination.Borders[BordersLineType.DiagonalDown].LineStyle = source.Borders[BordersLineType.DiagonalDown].LineStyle;
            destination.Borders[BordersLineType.DiagonalUp].LineStyle = source.Borders[BordersLineType.DiagonalUp].LineStyle;
            destination.Borders[BordersLineType.EdgeBottom].LineStyle = source.Borders[BordersLineType.EdgeBottom].LineStyle;
            destination.Borders[BordersLineType.EdgeLeft].LineStyle = source.Borders[BordersLineType.EdgeLeft].LineStyle;
            destination.Borders[BordersLineType.EdgeRight].LineStyle = source.Borders[BordersLineType.EdgeRight].LineStyle;
            destination.Borders[BordersLineType.EdgeTop].LineStyle = source.Borders[BordersLineType.EdgeTop].LineStyle;
            destination.Borders.Color = source.Borders.Color;
            destination.Borders.Value = source.Borders.Value;
        }


[Gary.zhang]

Hello,

Thanks for sharing the codes. It would be very helpful.

Regards,
Gary
E-iceblue support team

[nolme]

Hi

for information InsertRow() is still not working properly. It seems to border style is copied from previous line.

Code: Select all

static void Main(string[] args)
        {
            string strTemplate = Path.GetFullPath(@".\Templates\facture (defaut).xlsx");
            string strDocument = Path.GetFullPath(@"result.xlsx");

            using (Workbook workbook = new Workbook())
            {
                workbook.LoadFromFile(strTemplate, Program.CurrentExcelVersion);

                // Gets worksheet
                Worksheet sheet = ExcelUtility.GetWorkSheet(workbook, "Invoice");

                sheet.InsertRow(22, 1, InsertOptionsType.FormatAsAfter);
               
                workbook.SaveToFile(strDocument, Program.CurrentExcelVersion);
                System.Diagnostics.Process.Start(strDocument);
            }
            return;
}


[amy.zhao]

Hi,

I tested your code and excel file with Spire.Office Platinum (Hot Fix) Version:2.12.5. The result was correct exactly as the destination in your attachment picture. Please try the version and tell us your test result.

Best Regards,
Amy
E-iceblue support team

[nolme]

I'm already using this version.
I have understood my problem. The border line between 22 & 23 is the top border from line 22. So there's no problem sorry.

I must insert to next line to avoid thsi problem.

Many thanks

[amy.zhao]

Hi,

You are welcome. Welcome to write to us again for further problems. We are here to help you.

Best Regards,
Amy
E-iceblue support team