If new data inserted, table is not resizing

[Ankita_123]

Hello,

I am inserting data from code, In my excel file I have already inserted table, which only contain structure not data. the problem is, table is not resizing after adding data,
am I missing out on something? Please reply asap

[Brian.Li]

Hello,

Thanks for your inquiry.
You want each cell of the table to be automatically resized according to the length of the content, right? You can refer to the following code to meet your requirement.

Code: Select all

           ...
            for (int i = 1;i<= sheet.Rows.Length;i++)
            {
                sheet.AutoFitRow(i);
            }
            for (int j = 1; j <= sheet.Columns.Length; j++)
            {
                sheet.AutoFitColumn(j);
            }
            ...

If this cannot help you, please provide your complete code, your input file and the desired output for further investigation. Thanks in advance for your assistance.

Sincerely,
Brian
E-iceblue support team

[Ankita_123]

thank you for your reply.

autofit is not my requirement. As I dont want to autofit the column and rows. I want to change dimension of the table according to data

I tried below code , it is resizing the table. but didnt autofill the formula. Is their any way to achieve the same?

ws2.ListObjects.Single(i => i.Name == "Table3").Location = ws2.Range[1, 1, 12,2];

Also, I want to check whether is there any way to work with SPILL ranges ?

[Brian.Li]

Hello,

Thanks for your response.

Sorry I’m not very clear about the issue “ didnt autofill the formula” you mentioned. To help us has a better understanding on your issue, please provide your full test code, your input file (if any) and the output files you expect. You could send them to us via email (support@e-iceblue.com).

Besides, regarding “work with SPILL ranges”, could you please provide more detailed information to help us investigate further? Some screenshots or your desired output would help us lot. Thanks in advance for your assistance.

Sincerely,
Brian
E-iceblue support team

[Ankita_123]

Hello,

I have shared the file on support@e-iceblue.com.

[Brian.Li]

Hello,

Thanks for providing more information via email.

I’m sorry that it is impossible to autofill the formula as you expect. But you can refer to the following code to copy the formula to achieve a similar effect.

Code: Select all

            Workbook workbook = new Workbook();
            workbook.LoadFromFile("snopformulaexpand.xlsx");
            Worksheet ws0 = workbook.Worksheets[0];
            Worksheet ws1 = workbook.Worksheets[1];

            ws0.Range["A10"].Value = "SKU9"; ws1.Range["A10"].Value = "SKU9";
            ws0.Range["B10"].Value = "Customer9"; ws1.Range["B10"].Value = "Customer9";
            ws0.Range["C10"].NumberValue = 1; ws1.Range["C10"].NumberValue = 1;
            ws0.Range["D10"].NumberValue = 2; ws1.Range["D10"].NumberValue = 2;
            ws0.Range["E10"].NumberValue = 3; ws1.Range["E10"].NumberValue = 3;
            ws0.Range["F10"].NumberValue = 4; ws1.Range["F10"].NumberValue = 4;
            ws0.Range["G10"].NumberValue = 5; ws1.Range["G10"].NumberValue = 5;
            ws0.ListObjects.Single(i => i.Name == "Table1").Location = ws0.Range[1, 1, ws0.LastRow, ws0.LastColumn];
            ws1.ListObjects.Single(i => i.Name == "Table2").Location = ws1.Range[1, 1, ws1.LastRow, ws1.LastColumn];

            Worksheet sheet = workbook.Worksheets[2];

            sheet.Range["A10"].Formula = sheet.Range["A9"].Formula;
            sheet.Range["B10"].Formula = sheet.Range["B9"].Formula;
            sheet.Range["C10"].Formula = sheet.Range["C9"].Formula;
            sheet.Range["D10"].Formula = sheet.Range["D9"].Formula;
            sheet.Range["E10"].Formula = sheet.Range["E9"].Formula;
            sheet.Range["F10"].Formula = sheet.Range["F9"].Formula;
            sheet.Range["G10"].Formula = sheet.Range["G9"].Formula;

            string result = "result.xlsx";
            workbook.SaveToFile(result, ExcelVersion.Version2010);

If there are any other questions or any misunderstandings, just feel free to contact us.

Sincerely,
Brian
E-iceblue support team