InsertArray needConvert documentation needed

[trouse1234]

Hello, I am attempting to make a change so that spire does not see '1.1.1' as a date but rather allows that to stay as text. It seems that is what needConvert was added for with the latest release. After trying to make that change I am getting an object is set to an instance of an object error. The documentation online has not been updated with this method so I do not know if there are any more requirements to do this. Thanks.

[kylie.tian]

Hi,

Thank you for your inquiry.
I did an initial test using the latest version(Spire.XLS Pack(Hotfix) Version:12.5.3), but didn't reproduce your problem. I suggest you give the latest one a try. Also, I will attach my test code for your reference. If the problem still exist, please provide the following information for further investigation.
1) Your test code.
2) Test environment, such as win10, 64bit.
3) Application type, such as Console App, .NET Framework 4.8.

Code: Select all

 
            Workbook wb = new Workbook();
            Worksheet sheet = wb.ActiveSheet;
            string[,] array = new string[1, 1];
            array[0, 0] = "1.1.1";
            sheet.InsertArray(array, 1, 1, false);
            wb.SaveToFile("output.xlsx");

Sincerely,
Kylie
E-iceblue support team

[trouse1234]

That works but is it possible for formatting set up on the column to still apply? For example if I want a columns formatted like this "#.0######". Using this code the resulting excel file is not formatted correctly despite the column having the correct format set.

[kylie.tian]

Hi,

When needConvert is set to false, InsertArray will not automatically convert the data type. For example, "January" will be inserted by text type, but will not be converted to date format 2022/1/1. To follow the cell formatting that you set up in Excel when inserting, please directly remove the needConvert parameter.

Sincerely,
Kylie
E-iceblue support team

[trouse1234]

Okay maybe there is no way to do this using spire but what I would like to have happen is have it format the way I tell it to. If I set a column as text I would like it to not format it so that 1.1.1 will be seen like that and not as a date. but on other columns that have prices I want it to format so that the dollar amounts only have two cent places like '8.56' instead of '8.5556783'. Is that possible in any way?

[kylie.tian]

Hi,

Thank you for your reply.
I suggest that you can put values into different arrays and set needConvert as required. Please refer to the following code. If you have any questions, please do not hesitate to contact me.

Code: Select all

            //Load the Excel
            Workbook wb = new Workbook();
            wb.LoadFromFile(fileName);
            //Get sheet
            Worksheet sheet = wb.Worksheets[0];
            string[] text = { "1.1.1","1.1.2","1.1.3"};
            string[] dollar = { "0.222", "8.5556783", "6.6"};
            //Create arrayText
            string[,] arrayText = new string[text.Length, 1];
            //Create arrayDollar
            string[,] arrayDollar = new string[dollar.Length, 1];
            for (int i = 0; i < text.Length; i++)
            {
                arrayText[i,0] = text[i].ToString().Trim();
            }
            //Keep the format
            sheet.InsertArray(arrayText, 1, 2,false);
            for (int j = 0; j < dollar.Length; j++)
            {
                arrayDollar[j, 0] = dollar[j].ToString().Trim();
            }
            //follow the cell format
            sheet.InsertArray(arrayDollar, 1, 3);
            wb.SaveToFile("output.xlsx");


Sincerely,
Kylie
E-iceblue support team

[trouse1234]

That works. Thank you.

[Nina.Tang]

Hello,

Thanks for your feedback.
If you need further assistance, please feel free to contact us.
Wish you a nice day

Sincerely,
Nina
E-iceblue support team