Forum Discussion

Cekay's avatar
Cekay
Contributor
4 years ago

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

 

Scripting
  • TNeuschwanger's avatar
    TNeuschwanger
    4 years ago

    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

  • nmrao's avatar
    nmrao
    Champion Level 3
    4 years ago
    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/
    • Cekay's avatar
      Cekay
      Contributor
      4 years ago

      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's avatar
        nmrao
        Champion Level 3
        4 years ago
        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.
  • TNeuschwanger's avatar
    TNeuschwanger
    Champion Level 1
    4 years ago

    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

    • Cekay's avatar
      Cekay
      Contributor
      4 years ago

      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)
   }
}