How to insert multiple rows and clone the style?

Technical support for Spire.XLS

Moderators: iceblue support, Flash, Manager

How to insert multiple rows and clone the style?

Postby waldomirmarkgraf » Wed Nov 30, 2011 10:31 am

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();
      }
    }
  }
}
waldomirmarkgraf
 
Posts: 15
Joined: Mon Dec 06, 2010 12:14 pm

Re: How to insert multiple rows and clone the style?

Postby Tina.Lin » Thu Dec 01, 2011 10:35 am

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.
Attachments
233InsertMultipleRow.zip
(4.62 KiB) Downloaded 236 times
Tina
Technical Support/Developer,
e-iceblue Support Team
User avatar
Tina.Lin
 
Posts: 152
Joined: Tue Sep 13, 2011 5:37 am


Return to Spire.XLS

Who is online

Users browsing this forum: No registered users and 1 guest