Move to next cell

[nolme]

Hi,

Imagine that we've merged column B & C.
If we have 3 texts to add on the line 15, it should be A15, B15 & D15.

Is there a way to move to next column ?

Thanks,
Vincent

[sweety1]

Hello,

Thanks for your inquiry.
Sorry that I don't quite understand your requirement.
Could you please tell us your requirement in detail? The better to provide some screenshots.

Best Regards,
Sweety

E-iceblue support team

[nolme]

Hi,

as you can see on XLSX file, column B & C are merged for items line 15 -> 22.

My code to add an article is :

Code: Select all

public static void AddItem (Worksheet sheet, string[] valueArray)
        {
            int firstRow = GetFirstItemRow(sheet);    // Return 16
            int newRow = firstRow + 1;
            sheet.InsertRow(newRow, 1, InsertOptionsType.FormatAsAfter);

            for (int col = 0; col < valueArray.Count(); col++)
            {
                char colName = (char)('A' + col);
                string cellName = colName.ToString() + firstRow.ToString();     // Keep previous line number
               
                //sheet.move
                CellRange cell = sheet.Range[cellName];
               
                cell.Text = valueArray[col];
            }
     }

In this code, it use string array to fill column A, B & C but it should be use column A, B & D (because B & C are merged).

So, I would like to use a function to move to the next cell on the right on the current line. (Like pressing the Right key on Excel).

Is this more clear ?

[sweety1]

Hello,

Thanks for your reply.
Here is the code for your reference.

Code: Select all

int firstRow = 16;  // Return 16
            int newRow = firstRow + 1;
            sheet.InsertRow(newRow, 1, InsertOptionsType.FormatAsAfter);
            int t = 0;

            for (int col = 0; col < valueArray.Count(); col++)
            {
               
                char colName = (char)('A' + t);
                string cellName = colName.ToString() + firstRow.ToString();     // Keep previous line number

                //sheet.move
                CellRange cell = sheet.Range[cellName];
                if (cell.HasMerged==true)
                {
                    t++;
                }
                cell.Text = valueArray[col];
                t++;
            }

If there are any questions, welcome to get it back to us.

Best Regards,
Sweety

E-iceblue support team

[nolme]

I'm looking for something more generic.

If we merge B, C & D this code won't work. We must add a loop within the for().
But, what about merging after this E & F ?

=> B, C, D, E & F will all have HasMerged() set to true. (I think D & F should have HasMerged() to false to break search but it doesn't seems to work as it.

Vincent

[sweety1]

Hello,

Thanks for your reply.
Please refer to the code below.

Code: Select all

  int firstRow = 16;  // Return 16
            int newRow = firstRow + 1;
            sheet.InsertRow(newRow, 1, InsertOptionsType.FormatAsAfter);
            int t = 0;
            for (int col = 0; col < valueArray.Count(); col++)
            {
                char colName = (char)('A' + t);
                string cellName = colName.ToString() + firstRow.ToString();     // Keep previous line number
                CellRange cell = sheet.Range[cellName];               
                if (cell.HasMerged == true)
                {
                    cell.Text = valueArray[col];
                    while (cell.HasMerged == true)
                    {
                        t++;
                        colName = (char)('A' + t);
                        cellName = colName.ToString() + firstRow.ToString();
                       cell = sheet.Range[cellName];
                    }
                 
                }
                else
                {
                    cell.Text = valueArray[col];
                    t++;
                }
            }
            wb.SaveToFile("result.xlsx", ExcelVersion.Version2010);

If there are any questions, welcome to get it back to us.

Best Regards,
Sweety

E-iceblue support team

[nolme]

GREATTTTTTT You will make my today's work happy ^^

Just a little more question :
- in my case, D column is a number using € currency and the E column is a number. How could we keep this formatting ?

Maky thanks for all

Vincent

[sweety1]

Hello,

Thanks for your response. While you add the data into the cell, you need to set the format of the cell, otherwise it will display as default format.

Sincerely,
Sweety
E-iceblue support team

[nolme]

I'm reading the help file :
http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Cells/How-to-Set-Number-Format-in-Excel-using-Spire.XLS-in-C-VB.NET.html

But my question is : why the format is not replicated from previous line using the code :

Code: Select all

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

Edit : Here's the working code I've used to duplicate style (just call it at the end of AddItem():

Code: Select all

public static void DuplicateStyle (Worksheet sheet, int sourceLine, int destinationLine)
        {
            int t = 0;
            for (int col = 0; col < 25; col++)
            {
                char colName = (char)('A' + t);
                string sourceCellName = colName.ToString() + sourceLine.ToString();
                string destinationCellName = colName.ToString() + destinationLine.ToString();
                CellRange sourceCell = sheet.Range[sourceCellName];
                CellRange destinationCell = sheet.Range[destinationCellName];

                if (sourceCell.HasMerged == true)
                {
                    destinationCell.NumberFormat = sourceCell.NumberFormat;
                    destinationCell.HorizontalAlignment = sourceCell.HorizontalAlignment;
                    destinationCell.VerticalAlignment = sourceCell.VerticalAlignment;

                    while (sourceCell.HasMerged == true)
                    {
                        t++;
                        colName = (char)('A' + t);
                        sourceCellName = colName.ToString() + sourceLine.ToString();
                        sourceCell = sheet.Range[sourceCellName];
                    }
                }
                else
                {
                    destinationCell.Style = sourceCell.Style;
                    destinationCell.NumberFormat = sourceCell.NumberFormat;
                    destinationCell.HorizontalAlignment = sourceCell.HorizontalAlignment;
                    destinationCell.VerticalAlignment = sourceCell.VerticalAlignment;

                    t++;
                }
            }
        }

Vincent

[sweety1]

Hello,

Thanks for your response.
It should be the below code.

Code: Select all

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

The issue you mentioned is already resolved. It will submit test a few days later. The fix will be included into the next version of Spire.Xls. We will inform you when it is released.

Best Regards,
Sweety

E-iceblue support team