cancel
Showing results for 
Search instead for 
Did you mean: 

Use different format cells while create Excel file

SOLVED
Cekay
Contributor

Use different format cells while create Excel file

Hello

I have a simple script to create a test data and put that into an Excel file and that is working so far but the import of these files doesn't work because the format is not correct.

1. Some of my numbers are saved as text in excel and our importer can't work with that. How can I set the format of some cells?

I tried to use BigDecimal format and Integer but both versions are not working.

 

 

groovy.lang.GroovyRuntimeException: Could not find matching constructor for: jxl.write.Label(Integer, Integer, Integer) error at line: 29

 

 

2.Id_import should be empty but it is also considered as text

 

 

 

import jxl.*
import jxl.write.*
import java.io.File
import java.text.SimpleDateFormat

def fileTimeStamp = new SimpleDateFormat("YYYYMMddHHmm").format(new Date())
File inputFile = File.createTempFile("Demofile" + fileTimeStamp + "_", ".xls")

WritableWorkbook workbook = Workbook.createWorkbook( inputFile)
WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)

def header = ["PartnerName","Fee","id_import","Branch","Date_of_Birth"]

for(int i=0;i<header.size();i++){
Label label = new Label(i, 0, header[i]); // i = column=0=A, row=1
sheet.addCell(label)
log.debug header[i]
}

def birthday = "19.03.1972"
def fee = "0,3"
def name = "Hans Peter"
//def branch = 5732 -> is not working
def branch = "5732"

def merchant = [name,fee,"",branch,birthday]

for(int i=0;i<merchant.size();i++){
Label label = new Label(i, 1, merchant[i]); // i = column=0=A, row=1
sheet.addCell(label)
log.debug merchant[i]
}

workbook.write()
workbook.close()

log.info "File was saved : " + inputFile.getAbsolutePath()
assert merchant.size == header.size

 

 

 

Simplified version. I need more data but most of it are normal Strings. So no problem there.

 

Thanks

Sabine

 

1 ACCEPTED SOLUTION

Accepted Solutions
TNeuschwanger
Contributor

Re: Use different format cells while create Excel file

Hello @Cekay

 

The data type of the Label object is String.  You are attempting to force an integer to a string and the date type police have said that is a violation.  🙂

 

I have taken the liberty to rewrite the offending code into something a little more groovyish...

 

//for(int i=0;i<merchant.size();i++){
//Label label = new Label(i, 1, merchant[i]); // i = column=0=A, row=1
//sheet.addCell(label)
//log.info merchant[i]
//}

merchant.eachWithIndex { val, indx ->
   log.info val.getClass();
   if (val instanceof Integer) {
      Number number = new Number(indx, 1, val);
      sheet.addCell(number);
   };
   if (val instanceof String) {
      Label label = new Label(indx, 1, val);
      sheet.addCell(label);
   ;
};

 

 

What you use in the .addCell() method is data type dependent.  If you need other data types other than a string or number, you will have to google for other examples (I just looked for solution only to your issue).

 

Regards,

Todd

View solution in original post

5 REPLIES 5
nmrao
Community Hero

Re: Use different format cells while create Excel file

I would use csv instead of excel and there very good library which is easy to read and write.
Please see if you can adopt this. Sample code is available there too.
http://xlson.com/groovycsv/


Regards,
Rao.
Cekay
Contributor

Re: Use different format cells while create Excel file

Yeah CSVs and Json files are easy. I do that all the time. But unfortunately this special test needs a xls.

Or is there a way to create the file as csv and convert afterwards to xls? (xlsx is also not allowed) :X

nmrao
Community Hero

Re: Use different format cells while create Excel file

Are you using any excel functionality?
Otherwise csv is simple text file to handle as pointed and doing.

One can open csv file in Excel and save as required extension such as .xls /.xlsx if required. But again one should not land into issue while reading. And avoid manual process of save as excel if possible.


Regards,
Rao.
TNeuschwanger
Contributor

Re: Use different format cells while create Excel file

Hello @Cekay

 

The data type of the Label object is String.  You are attempting to force an integer to a string and the date type police have said that is a violation.  🙂

 

I have taken the liberty to rewrite the offending code into something a little more groovyish...

 

//for(int i=0;i<merchant.size();i++){
//Label label = new Label(i, 1, merchant[i]); // i = column=0=A, row=1
//sheet.addCell(label)
//log.info merchant[i]
//}

merchant.eachWithIndex { val, indx ->
   log.info val.getClass();
   if (val instanceof Integer) {
      Number number = new Number(indx, 1, val);
      sheet.addCell(number);
   };
   if (val instanceof String) {
      Label label = new Label(indx, 1, val);
      sheet.addCell(label);
   ;
};

 

 

What you use in the .addCell() method is data type dependent.  If you need other data types other than a string or number, you will have to google for other examples (I just looked for solution only to your issue).

 

Regards,

Todd

View solution in original post

Cekay
Contributor

Re: Use different format cells while create Excel file

@nmrao  It has to be xls because the importer we have to test doesn't accept anything else. To write everything in a csv and manually save it as xls was my first approach. But I'd like to avoid that.

 

@TNeuschwanger 

I tried Number number = new Number before but I also got errormessages for that. Your codes works 😃

 

 Thank you so much (to both of you) for your time and help

 

 

For everyone who needs something similar for other objects:

merchant.eachWithIndex { val, indx ->
   log.info val.getClass()
   if (val instanceof Integer) {
      Number number = new Number(indx, 1, val)
      sheet.addCell(number)
   }
   if (val instanceof String) {
      Label label = new Label(indx, 1, val)
      sheet.addCell(label)
   }
   if (val instanceof NullObject) {
      Blank blank = new Blank(indx, 1, val)
      sheet.addCell(blank)
   }
   if (val instanceof BigDecimal) {
      Number number = new Number(indx, 1, val)
      sheet.addCell(number)
   }
   if (val instanceof Date) {
      DateTime dateTime  = new DateTime(indx, 1, val)
      sheet.addCell(dateTime)
   }
}

 

Tags (3)
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors