Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed May 12, 2021 11:28 am

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

Ankita_123
 
Posts: 5
Joined: Thu Jan 28, 2021 10:22 am

Thu May 13, 2021 2:14 am

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
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu May 13, 2021 2:26 pm

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 ?

Ankita_123
 
Posts: 5
Joined: Thu Jan 28, 2021 10:22 am

Fri May 14, 2021 7:16 am

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
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Fri May 14, 2021 8:55 am

Hello,

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

Ankita_123
 
Posts: 5
Joined: Thu Jan 28, 2021 10:22 am

Fri May 14, 2021 12:18 pm

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
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS