Getting invalid data in data sheet

Aspose.Cells Product Family
1

Hi Team,

we are using aspose cells 7.0.4.jar.

we found one thing in our one of column having the data like 00000 this will be converted to 0 and suppress the zeros in data sheet.

please find the attached source and csv file.

Thanks,

Kumar.

2

Hi,


This is not an issue rather MS Excel’s behavior. When you input a value e.g 00000 into a cell, MS Excel will convert it to 0. For confirmation, you may check it in MS Excel. I have tested opening both your csv and xlsx files into MS Excel, both shows same results, MS Excel will display 0 values in columns e.g I, J and K etc, both views are same.

I have attached screen shots for your reference too.

Thank you.
3

hi Team,

Thanks for giving replay.

please open the csv in notepad and check it.

we also applied the format for column as text . but this will suppress the zeros.

but we need to display the 0000 instead of 0 in column.


please find the required output for csv file and source code.

4

Hi,


Please use TxtLoadOption.setConvertNumericData(false) for your needs. I have tested using the following updated code with your template CSV file and it works fine for your needs.

Sample code:
TxtLoadOptions loadOptions = new TxtLoadOptions(FileFormatType.CSV);
loadOptions.setConvertNumericData(false);
Workbook workbook = new Workbook(“1008404_3392_1.csv”,loadOptions);
workbook.save(“3392.xlsx”,FileFormatType.XLSX);


Also, I am using latest fixed version: Aspose.Cells for .NET (Latest Version)

Thank you.

5

Hi Team ,

thanks for giving solution.
this working for csv file.

we have a case where data coming from database table.
using light cells data provider we are generating the excel.
i am using this one "writeWorkbook.getSettings().setConvertNumericData(false);"
but this is not resolve the issue.

thanks,
kumar.

6

Hi Kumar,


We need more details as we are not sure about your requirement. We are not certain that on how are you using LightCells with CSV file. What is your logic, we need your sample code on how you are using LightCells APIs while saving to CSV or reading CSV. Please provide the required details and sample code here. We will check it soon.

Thank you.
7

Hi Team,

we are generating the large excel from clob using light cell data provider.

to test the issue we creating csv file from clob.

please find the code for 2007 excel generation.



thanks,

kumar.

8

Hi,


Thanks for the Java files / code segments,

I have logged a ticket with an id: CELLSJAVA-40104. We need to investigate it. Our concerned development team will do it soon. Once we have any update about it, we will let you know here.

Thank you.
9

Hi Team,

any update on this?

Thanks,
Kumar.

10

Hi,


I am afraid there is no update on your issue. Our concerned developer (from China) is on his
Spring Festival holidays, he will re-join us in the next week. Hopefully, we could update you on your issue in the next week.

Keep in touch.

Thank you.
11
Hi,

In your code you used Cell.putValue(value, true) where the true flag will make the component try converting given string value to numeric. So, if you give a value like “0000”, it will be parsed as numeric 0. For your situation, you should use Cell.putValue(value, false) or Cell.putValue(value) instead.
12

Hi Team,

Thanks for giving response.


 
public void putValue(java.lang.String stringValue, boolean isConverted)
Puts a string value into the cell and converts the value to other data type if appropriate.
Parameters:
stringValue - Input value
isConverted - True: converted to other data type if appropriate.

according to doc putvalue method convert the string to specified data type.

in my case i will apply the format for columns at beginning. later it will be converted to specified format.
by using putvalue method. if i did not use this one, format for numbers and date will lost.
in my case the format for column will be text for data lost columns. so the source and destination format will be same. even though truncate will happened.

2.conversion will take time is there any other eefecient way to set or convert the data to specified data type.

currently we are applying the format column wise before data dumping.After lightcells's converting the data to specified data type.



Thanks,
eistechnologies.



13

Hi,

For Cell.putValue(value, true), our component will try many number formattings to check whether one can be applied to given value and parse it to proper value type if one available number formatting be found. So the process is time consumed.

For your situation, if the data type for one column(or most of the data of one column) is known for you, it would be much better for performance for you to convert the data to proper value type by yourself and then set to a cell.

Sample code for LightCellsDataProvider:

Java


String[] columnFormatString = …;

java.text.Format columnFormat = …;

…

public void startCell(Cell cell)

{

try{

if(cellValue.hasMoreTokens())

{

String strVal = cellValue.nextToken().trim();

if(strVal.equals(""))

{

return;

}

if(columnFormat[cell.getColumn()] != null)

{

try

{

Object v = columnFormat[cell.getColumn()].parseObject(strVal);

cell.putValue(v);

Style style = cell.getStyle();

style.setCustom(columnFormatString[cell.getColumn()]);

cell.setStyle(style);

return;

}

catch(Excepton e)

{

}

}

if(strVal.charAt(0) == ‘0’)

{

cell.putValue(strVal);

}

else

{

cell.putValue(strVal,true);

}

}

}

catch(Exception e)

{

logger.warning(e.getMessage());

}

}

14

Hi Team,

I am totally confused on this.

Object v = columnFormat[cell.getColumn()].parseObject(strVal);

cell.putValue(v);

Style style = cell.getStyle();

style.setCustom(columnFormatString[cell.getColumn()]);

cell.setStyle(style);

give some clarification on this
case: Generating large excel having formats like date , text and number using LightCells
which approach is taking less memory and less generating time

1. Applying format to whole Column is efficient or Cell wise.

2.Applying style to column is efficient or manually convert string to required data type and set to cell is efficient.


Thanks
kumar.

15

Hi,

Please see the following answers to your questions.

1. Applying format to whole Column is efficient or Cell wise

If most of cells in one column can use the same style, applying format to the whole column is much more efficient than applying to every cell one by one.

2. Applying style to column is efficient or manually convert string to required data type and set to cell is efficient

If you use cell.putValue(value, true) and the given value needs to be parsed as other type, we also need to reset the cell’s style according with the parsed formatting even if you have applied style for the whole column. So for setting styles there is no many difference for two ways. However, if most of your data of one column can use the same pattern to parse, it will be much more efficient for you to convert string to required data type by yourself than use cell.setValue(value, true).

So, for your situation, if you can apply the specified number formatting to one whole column, the more efficient code should be like:

Java



//set columns’ style before processing cells data

StyleFlag f = new StyleFlag();

f.setNumberFormat(true);

for(…)

{

Column column = …;

Style style = column.getStyle();

style.setCustom(columnFormatString[column.getIndex()]);

column.applyStyle(style, f);

}

…

In implementation of LightCellsDataProvider:

public void startCell(Cell cell)

{

if(…)

{

…

try

{

Object v = columnFormat[cell.getColumn()].parseObject(strVal);

cell.putValue(v);

}

…

}

}