We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

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

Thread ID:

Created:

Updated:

Platform:

Replies:

150944 Jan 25,2020 09:23 AM UTC Feb 3,2020 11:44 AM UTC WPF 5
loading
Tags: XlsIO
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

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. 


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. 


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. 


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

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. 


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 


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

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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon