Excel Export Integer in String column

[erickr]

We have a column "code" which is logically a string but can be numerals mixed with letters - see datasource below.

When we export to Excel we have one code that is "35D" and when put into a Excel general column it becomes 35 (decimal). Other than this it seems fine although pure numerals align right and those interpreted as strings align left in Excel.

Any help is appreciated - details follow.

Data returned is correct - snipped for privacy

 {
                code:"35D", 
                id:184, 
                intermediateCer:"N", 
                organization:"clip", 
                description:"clip", 
                longDescription:"clip" 
            },

INFO ISCInit - Isomorphic SmartClient/SmartGWT Framework (v8.3p_2013-01-12/Enterprise Deployment 2013-01-12) - Initialization Complete


Call to Export code

exportToExcelButton.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				ExcelExportDsRequest exportRequest = new ExcelExportDsRequest(exportFileName);
				//grid.exportClientData(exportRequest);
				grid.exportData(exportRequest);  // lose grid formatting
			}
		});

Request code

public class ExcelExportDsRequest extends DSRequest {
	/**
	 * Create a request suitable for client export
	 * 
	 * @param exportFileName - base file name
	 */
	public ExcelExportDsRequest(String exportFileName) {
		this.setExportAs(ExportFormat.OOXML);  // 2007 Excel format
		this.setTimeout(0); // do not timeout
		//this.setExportDisplay(ExportDisplay.DOWNLOAD);
		//this.setExportResults(true); // NEW not sure what this is for
		DateTimeFormat fmt = DateTimeFormat.getFormat("yyyy-MM-dd");				
		this.setExportFilename(exportFileName + "-" + fmt.format(new Date()));
		this.setExportDatesAsFormattedString(true);
	}

}

Portion of the datasource

<DataSource ID="Cer" serverType="generic" dropExtraFields="true" >
	<fields>
		<field name="id" type="integer" title="Id" hidden="true" primaryKey="true" />        
		
        <field name="code" type="text" title="CER Code" length="20" required="true">
        	<validators>
                <validator type="regexp" expression="^[A-Z0-9]{3}$" 
                	errorMessage="Please enter a valid 3 character CER code with only capital letters and numbers"/>
            </validators>
        </field>
        <field name="description" type="text" title="Title" required="true"/>
        <field name="longDescription" type="text" title="CER Description" required="true"/>
        <field name="organization" type="text" title="Organization" required="true"/>
        <field name="intermediateCer" type="text" title="Intermediate" required="true"/>

Grid code - tried added field type but it didn't help.

public class CerGrid extends BasicGrid {

	public CerGrid() {
		super("Cer");
		this.setUseAllDataSourceFields(false);

		ListGridField id = new ListGridField("id");
		id.setHidden(true);
		ListGridField code = new ListGridField("code");
		code.setType(ListGridFieldType.TEXT);
		ListGridField organization = new ListGridField("organization");
		ListGridField description = new ListGridField("description");
		ListGridField longDescription = new ListGridField("longDescription");
		ListGridField intermediateCer = new ListGridField("intermediateCer");
		this.setFields(id, code, organization, description, longDescription, intermediateCer);
	}
}

Request from the server console.

[face] 2013-01-15 13:03:20,130 DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        ratePackageId:1
    },
    operationConfig:{
        dataSource:"Cer",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    exportResults:true,
    exportAs:"ooxml",
    exportDelimiter:",",
    exportTitleSeparatorChar:"",
    exportFilename:"Cost Estimating Relationship-2013-01-15",
    exportDisplay:"download",
    lineBreakStyle:"default",
    exportFields:[
        "code",
        "organization",
        "description",
        "longDescription",
        "intermediateCer"
    ],
    exportHeader:"Raytheon Proprietary",
    exportFieldTitles:{
        code:"CER Code",
        organization:"Organization",
        description:"Title",
        longDescription:"CER Description",
        intermediateCer:"Intermediate"
    },
    appID:"builtinApplication",
    operation:"Cer_fetch",
    oldValues:{
        ratePackageId:1
    }
}

Request from the DEV console

{
    dataSource:"Cer", 
    operationType:"fetch", 
    data:{
        ratePackageId:1
    }, 
    textMatchStyle:"substring", 
    showPrompt:false, 
    oldValues:{
        ratePackageId:1
    }, 
    requestId:"Cer$62715", 
    fallbackToEval:false, 
    exportAs:"ooxml", 
    timeout:0, 
    exportFilename:"Cost Estimating Relationship-2013-01-15", 
    exportDatesAsFormattedString:true, 
    downloadResult:true, 
    downloadToNewWindow:false, 
    bypassCache:true
}

[Isomorphic]

Excel's heuristics do very often misfire like this.

dsResponse.getExportObject() is a relatively advanced API that could be used to get to the default POI object we generate and force that column to be explicitly specified as a string column to POI.

We may also add an attribute like dataSourceField.exportType as a convenience for forcing Excel to use a specific type. If you'd prefer this and need it by a specific date, the Feature Sponsorship program would be the way to do that.

[erickr]

Hi Isomorphic,

Well, I'm not sure how you guys map the data/datasource to the POI API but we have a custom excel export that includes the same String based entry 35D and it remains 35D in the resulting Excel File.

We use the following API:
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellValue%28java.lang.String%29

I would think that the string is getting converted to Integer first. 35D becomes 35 then the following method is getting called.

http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29

Since the code column in the datasource is "text" it would make sense to me that they be converted to Strings and the first API get called not the second.

What seems to be occurring based on what I see in the Excel is that if they can be converted to Integer they are, otherwise they become String. See in the attached file and how the numbers and strings align different in Excel (general column). Row 17 is the row that 35D becomes 35.

Could you please take another look?

Thanks,
Eric

[Isomorphic]

It's not us doing the conversion to integer, it's Excel. Same thing happens if you copy and paste a value into Excel. Similar heuristics apply to dates, currency values, etc.

So the values are already fine, but you would need to reach into the generated POI sheet and change the column type to be explicitly "text", as covered before.

[erickr]

Well, I don't think you guys are using the same API as I am. I have checked and double checked and we just create a Cell and then set the string value as described above.

See the rendering in Excel attached - the column is general, we don't do anything special. The only possible difference is we are generating XLS format with our custom code.

[Isomorphic]

OK there was a mix-up here - Excel does have heuristics for number and date values that applies to pasting data or CSV and can misfire similarly to what you saw here, however, in this case what was going on is that we were testing for whether a column was numeric, and Java's number parsing will accept 35D as a number, on the basis that the D designates "double".

We've now fixed this for future 3.1p and 4.0d builds.

[erickr]

Thank you so much that is great news.