How to insert multiple rows and clone the style?

[waldomirmarkgraf]

I've been trying to insert multiple rows from a DataTable into my excel sheet via InsertDataColumn, but it always only uses the first row and stops there. What's the preferred way to do this? Shouldn't InsertDataColumn insert the whole column? If so, is it actually inserting new cells and thus creating new rows for each data row in the column or should all data go into one cell?

Code: Select all

foreach (CellRange cell in spire_ws.Cells)
{                       
  foreach (DataTable dT in dS.Tables)
  {
    if (dT.TableName == tn && dT.Columns["mycolumn"] != null)
    {
      spire_ws.InsertDataColumn(dT.Columns["mycolumn"], false, cell.Row, cell.Column);
    }
  }
}


Update: Ok it get's a little more complicated actually.
Is it possible to clone a row and copy it multiple times below that one row and then fill the new rows with new data? The filling with data is trivial, it's just that, I've got a sheet, that has a variable amount of data in a table and should retain the formatting of the "mother row". Imagine a list of telephone numbers surrounded by borders and with colors. I want to add a variable number of telephone numbers, depending on the actually back end that delivers the data. I want each telephone number added with the same formatting as the first one. In Excel I'd highlight the row c/p and add the telephone numbers. Only that i want to do that programmatically now.

I know about sheet.Copy, but that requires a CellRange and I couldn't figure out a way to retrieve the current CellRange, since I'm traversing the Excel sheet cell by cell to check for strings to substitute, with said lists in between. so if i really have to use a range, I'd need a method to retrieve the the current CellRange from cell.

This code actually DOESN'T do what i want. It just inserts new cells. The copy command either doesn't work or not the way I imagine it to, which of course is more likely:

Code: Select all

foreach (CellRange cell in spire_ws.Cells)
{                       
  foreach (DataTable dT in dS.Tables)
  {
    if (dT.TableName == tn && dT.Columns["mycolumn"] != null)
    {
      int row = cell.Row;
      spire_ws.InsertRow(cell.Row + 1, dT.Rows.Count - 1);
      spire_ws.Copy(spire_ws.Rows[cell.Row], spire_ws.Rows[cell.Row + dT.Rows.Count - 1], true);

      foreach (DataRow dR in dT.Rows)
      {
        spire_ws.Range[row++, cell.Column].Value = dR[pc].ToString();
      }
    }
  }
}


[Tina.Lin]

Hello waldomirmarkgraf,

Sorry for late reply.

Firstly:
If you want to import multiple rows from DataTable to worksheet, we recommend you use worksheet.InsertDataTable() instead of worksheet.InsertDataColumn();

If you want to use worksheet.InsertDataColumn() to insert columns. You should do like the following code:

Code: Select all

    for (int i = 0; i < table.Columns.Count; i++)
            {
                worksheet.InsertDataColumn(table.Columns[i], true, 1, i + 1);

            }


Secondly:
You want to keep the the first row's style. You can set the first style and save the style as CellStyle, and then apply the CellStyle.
I attached you the demo. Please have a try.

If you still have any other questions, please don't hesitate to contact us.

Have a nice day.