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

[Andreas Reitberger]

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]

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. 

[Andreas Reitberger]

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

[Abirami Varadharajan]

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 

[Andreas Reitberger]

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]

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.