Open Table defined in excel template and add a new row to it

Andreas Reitberger

Asked On January 25, 2020 09:24 AM UTC

Hi,

I'm kind of lost here. I try to open an excel template with a pre-definied table in it. Find it attached.

Then I try to open it and select the table as shown in your docs and in my code below.

IApplication application = excelEngine.Excel;

application.DefaultVersion = ExcelVersion.Excel2013;

//Create a workbook

IWorkbook workbook = application.Workbooks.Open(template, ExcelOpenType.Automatic);

IWorksheet worksheet = workbook.Worksheets[0];

//Accessing first table in the sheet

IListObject table = worksheet.ListObjects[0];

However I stuck to simply add a new row to it?

I loop to my data and increase the row count by 1 for each item.

The data looks fine in the export, however the table stays at 1 data row.

//Go to list

for (int i = 0; i < calcs.Count; i++)

{

int Row = Convert.ToInt32(Settings.StartRow);

string Col = Settings.StartColumn;

// Pos

worksheet.Range[string.Format("{0}{1}", Col ,Row + i)].Number = i +1 ;

// Description

StringBuilder sb = new StringBuilder();

sb.AppendLine(calcs[i].Name);

/*

sb.AppendLine(string.Format("Volume: {0} | Time: {1}",

Convert.ToDouble(calcs[i].Volume),

Convert.ToDouble(calcs[i].CalculatedPrintTime))

);

*/

Col = GetNextColumn(Col, 1);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].Text = sb.ToString();

// Quantity

Col = GetNextColumn(Col, 1);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].Number = Convert.ToDouble(calcs[i].Quantity);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].NumberFormat = "0";

// Single

Col = GetNextColumn(Col, 1);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].Number = (Convert.ToDouble(calcs[i].Total) / (Convert.ToDouble(calcs[i].Quantity)));

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].NumberFormat = "€.00";

// Total

Col = GetNextColumn(Col, 1);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].Number = Convert.ToDouble(calcs[i].Total);

worksheet.Range[string.Format("{0}{1}", Col, Row + i)].NumberFormat = "€.00";

if (i < calcs.Count - 1)

{

// add new row here?

}

Also I want to currency formated using the current currency symbol from the OS.

Thanks for helping out!

Best,

Andreas

Attachment: export_template_428740d1.zip

Reply | Reply with quote

Konduru Keerthi Konduru Ravichandra Raju [Syncfusion]

Replied On January 27, 2020 08:34 AM UTC

Hi Andreas,

Greetings from Syncfusion.

New rows can be inserted into a table using the InsertRow method option available under IWorksheet interface.

Code Snippet:

worksheet.InsertRow(23, 10, ExcelInsertOptions.FormatAsBefore);

Please look into the following link to know more about this.

Help Link: https://help.syncfusion.com/file-formats/xlsio/worksheet-rows-and-columns-manipulation#insert-rows-and-columns

You can retrieve the currency symbol from OS and set the number format, using the following code snippet.

Code Snippet:

string currencySymbol = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;

worksheet.Range["A1"].NumberFormat = currencySymbol + "#,##0.00";

Kindly try this and let us know if the provided suggestion helps you.

Regards,

Keerthi.

Reply | Reply with quote

Andreas Reitberger

Replied On January 28, 2020 06:22 AM UTC

Hi Andreas,

Greetings from Syncfusion.

New rows can be inserted into a table using the InsertRow method option available under IWorksheet interface.

Code Snippet:

worksheet.InsertRow(23, 10, ExcelInsertOptions.FormatAsBefore);

Please look into the following link to know more about this.

Help Link: https://help.syncfusion.com/file-formats/xlsio/worksheet-rows-and-columns-manipulation#insert-rows-and-columns

You can retrieve the currency symbol from OS and set the number format, using the following code snippet.

Code Snippet:

string currencySymbol = System.Globalization.CultureInfo.CurrentCulture.NumberFormat.CurrencySymbol;

worksheet.Range["A1"].NumberFormat = currencySymbol + "#,##0.00";

Kindly try this and let us know if the provided suggestion helps you.

Regards,

Keerthi.

Hi Keerthi,

thank you for your help on that. Sadly the insert row function seems not to work in the table.

It just copies the format of the row, however not extend the table itself. Please see the image below.

I want to add a new row to the defined table object:

Thanks for the hint with the currency, this is working for me.

Greetings from Germany,

Andreas

Reply | Reply with quote

Abirami Varadharajan [Syncfusion]

Replied On January 29, 2020 01:41 PM UTC

Hi Andreas,

We are unable to reproduce the issue. Table range is updated properly when rows are inserted in the shared Excel file. We have prepared sample and shared for your reference.

Sample Link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample1858890146.zip

Please modify the sample above to reproduce the issue and share us the issue reproducing sample which will be helpful for us to validate further from our side.

Regards,

Abirami

Reply | Reply with quote

Andreas Reitberger

Replied On February 1, 2020 08:46 AM UTC

Hi,

thanks for the showcase. I found my issue. Added "+1" to the row, so I actually inserted the new row outside of the table.

Thanks for helping, all is working fine now.

Best,

Andreas

Reply | Reply with quote

Konduru Keerthi Konduru Ravichandra Raju [Syncfusion]

Replied On February 3, 2020 11:44 AM UTC

Hi Andreas,

Thanks for the update.

We are glad that the issue is resolved at your end.

Kindly let us know if you need any further assistance.

Regards,

Keerthi.

Reply | Reply with quote

Thread ID:	Created:	Updated:	Platform:	Replies:
150944	Jan 25,2020 09:23 AM UTC	Feb 3,2020 11:44 AM UTC	WPF	5

Thread ID:

Created:

Updated:

Platform:

Replies:

Please sign in to access our forum

CONTACT US