upload from excel

[vdas]

Hi Isomorphic,

Is there a utility to upload excel file using smart client.

My requirement is to read from an excel file, validate some details, store it database and then display it in a listgrid.

Any help/suggestion on this will be highly appreciated.

[ect]

This can be done best by writing a program for it ;-)

In case you are speaking Java, you can have a look at http://poi.apache.org

[vdas]

upload excel

Ya. i know i can do it using java program.
Wanted to check smart client can handle it or not..
Because somewhere i saw it can handle export to excel functionality. If smart client can handle the upload also, i would prefer using it as rest of my application is based on that.

[Isomorphic]

The BatchUploader component supports CSV upload, and you can export a CSV from Excel. Direct Excel (.xls) upload is not yet supported.

[claudiobosticco]

The 8.0 docs for BatchUploader say "...dataset expressed in CSV or other upload formats".

I've tried the sample with xls format and it seems not supported yet. Which are the other supported formats?

Also: the sample uses a csv file with comma as column separator, and double quotes as column delimiter.
Is this customizable? Excel csv format depends on locales, so here in Italy when users save a csv file with Excel they got a format like value1;value2;value3;value4
instead of "value1","value2","value3","value4"

[claudiobosticco]

Also: the sample uses a csv file with comma as column separator, and double quotes as column delimiter.
Is this customizable? Excel csv format depends on locales, so here in Italy when users save a csv file with Excel they got a format like value1;value2;value3;value4
instead of "value1","value2","value3","value4"

is it possible to override the batchUpload method and modify the uploaded file (before it gets parsed and loaded in the grid)?

[Isomorphic]

The other export formats are the same as are supported for use with the Admin Console and are not typically used in a tool exposed to non-developer end users. .xls upload is not currently supported as it would typically require extra code to pick out exactly what should be used or not used from the spreadsheet, and a user can just export the relevant portion to CSV instead.

Our CSV parser is flexible enough to allow most variations in syntax. Have you tried removing delimiters and encountered a problem? If so can you show a CSV file that didn't work?

[claudiobosticco]

Our CSV parser is flexible enough to allow most variations in syntax. Have you tried removing delimiters and encountered a problem? If so can you show a CSV file that didn't work?

the format in which Excel (2003) with Italian locale saves csv:

value1;value2;value3

[Isomorphic]

It does that with no special settings? Because CSV means Comma-Separated Values and those aren't commas (even in Italy)

[claudiobosticco]

It does that with no special settings? Because CSV means Comma-Separated Values and those aren't commas (even in Italy)

Yes, it does that. I discovered it because my users use Excel (I use libre office)

Here's how to change that behaviour (but "...all programs use the new character as a list separator"):
http://office.microsoft.com/en-us/excel-help/import-or-export-text-txt-or-csv-files-HP010099725.aspx#BMchange_the_separator_in_all_.csv_text

I can't require users to do that.

[claudiobosticco]

is it possible to override the batchUpload method and modify the uploaded file (before it gets parsed and loaded in the grid)?

I've found this working example:
http://svn.apache.org/repos/asf/poi/...LS2CSVmra.java
which uses POI library to convert xls file to csv file.

So I renew my question: is there available an override point where I can modify the file uploaded by BatchUploader before it gets loaded in the grid?

[claudiobosticco]

Another question: how to express a newline in a csv file and successfully upload it with BatchUploader?
I've tried:
"column1","column2","column3"
"value1","val\nue2","value3"

and:
"column1","column2","column3"
"value1","val
ue2","value3"

with no success

[claudiobosticco]

For now, I'm using the format with the \n, and I'm overriding the setData method of the grid like this:

setData: function(newData) {
	this.Super("setData", arguments);
	var data = this.getData();
	if (! data.isEmpty()) {
		var fieldsNames = this.getAllFields().getProperty("name");
		for (var i = data.length - 1; i >= 0; i--) {
			for (var j = fieldsNames.length - 1; j >= 0; j--) {
				var value = data[i][fieldsNames[j]] && data[i][fieldsNames[j]].toString();
				if (value && value.contains("\\n")) {
					data[i][fieldsNames[j]] = eval('"'+value+'"');
				}
			}
		}
	}
}

[kavyabellary]

Hi Isomorphic,

i tried to create excel sheet using poi in smart gwt..classpath n build path is ok..
bt wen i run error log is showing as noclassfound error..

please help me....

[Isomorphic]

We've added new attributes, BatchUploader.defaultDelimiter and defaultQuoteString, which will appear in the next nightly build. Let us know if that doesn't give you enough control.