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. Image for the cookie policy date

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

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

5 Replies

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team 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. 



AR Andreas Reitberger replied to Konduru Keerthi Konduru Ravichandra Raju 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


AV Abirami Varadharajan Syncfusion Team 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 



AR Andreas Reitberger 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


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team 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. 


Loader.
Live Chat Icon For mobile
Up arrow icon