[eZACKe]

I'm going to be attempting to simply read from a Excel spreadsheet and modify it using Java.

I'm planning on using Java Excel API, but I'm not set on it yet. I've never used Excel with Java before so this is all new to me. Is there something better than this? From the tutorials I've looked at this looks like it'd be quite easy to use for what I want to do.

Question: The link says: "Reads data from Excel 95, 97, 2000 workbooks", "Generates spreadsheets in Excel 2000 format". Is this telling me that it won't work with Excel 2007? That could be a problem for me then...


Thanks for the help. Once I get an API set up I'll continue to use this thread if I have any questions on the actual implementation.

[macosxnerd101]

I believe Excel 2007 can read Excel 2000 workbooks. Unless you want to use some of the new features of Excel 2007, I don't think there will be a problem for simple File I/O. pbl is more familiar with this API, and could probably better advise you, though.

[eZACKe]

Ok well I guess I'll give this a try then.

I just have questions concerning downloading/installing this.

I have downloaded it, and extracted everything from the zip file. I now have jexcelapi folder sitting on my Desktop. What do I do with this so that I can actually use the api? I'm trying to use it in Eclipse but when I write

import jxl.*;

It says the import jxl cannot be resolved.

Is it even possible to use this with Eclipse? If not I can try just doing it with Notepad and compiling on command line.

Also the contents of the jexcelapi are:
-build
-docs
-resources
-src

---^All Folders

-.classpath
-.project
-formatworkbook.dtd
-index.html
-jxl.jar
-jxlrwtest.xls
-tutorial.html
-workbook.dtd

[KYA]

Generally you add the jar(s) to the libraries section of your current project.

[eZACKe]

Thanks I got the API set up and working. Now my code is not working at all(I could have guessed that would happen).

What I'm attempting to do: I have a spread sheet with like 5 columns and 32284 rows. I'm only concerned with the rows, and 1 column - A. I want to go down every row in column A and if there's text in that row want to modify it.

Expected Result: Every row in column A that had text in it should be modified in the format: "num - whatever text was there"

Actual Result: Error.
Exception in thread "main" jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:221)
at jxl.Workbook.getWorkbook(Workbook.java:198)
at TestCasesSheet.main(TestCasesSheet.java:12)


Code(commented to explain my thought process):

import java.io.File; 
import java.io.IOException;
import jxl.*; 
import jxl.read.biff.BiffException;
import jxl.write.*; 

public class TestCasesSheet
{
	public static void main(String[] args) throws BiffException, IOException, WriteException
	{
		//file i'm reading from
		Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\Zack\\Desktop\\Envisage Information Systems\\Test cases from QC BTW - MVAS_0716100.xlsm"));
	
		//make a copy of it
		WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);
	
		//sheet i'm working on, should be first sheet which is 0?
		WritableSheet sheet1 = copy.getSheet(0);//the first sheet
	
		
		
		int max = 32284;//number of rows in the file
		int count = 1;//running count
		
	
		for(int i = 0; i < max; i++)
		{
			WritableCell cell = sheet1.getWritableCell(0, i);//cell = column a, row i
			Label l = (Label) cell;//label of the cell.. not sure what this is exactly...
			
			if(!cell.toString().equals(""))//if the cell is not empty...
			{
				//make it say count - whatever was already there
				l.setString("" + count + "-" + " " + l.getContents());
			}
			
			count++;//increase count			
		}
		
		copy.write();//write to copy
		copy.close();//close copy
		
	}
}

I don't know if I'm really even on the right track. I just am using THIS tutorial. I've never touched this API or any other API that deals with Excel.


Help is greatly appreciated!

This post has been edited by eZACKe: 20 July 2010 - 03:43 PM

[eZACKe]

Okay so it looks like it just didn't like that it was a .xlsm that I was trying to read in. I now have saved it as a .xls and that error is gone.

I'm now getting this error:

Exception in thread "main" java.lang.ClassCastException: jxl.write.Number cannot be cast to jxl.write.Label
at TestCasesSheet.main(TestCasesSheet.java:29)

On this line:

Label l = (Label) cell;//label of the cell.. not sure what this is exactly...

Not sure how to fix this.

[pbl]

eZACKe, on 20 July 2010 - 02:04 PM, said:

Question: The link says: "Reads data from Excel 95, 97, 2000 workbooks", "Generates spreadsheets in Excel 2000 format". Is this telling me that it won't work with Excel 2007? That could be a problem for me then...

It should read the 2007 spreadsheet but not the features (formatting and othe goddies) added since 2000.
As the Java program usually needs the raw data you should be in business.

eZACKe, on 20 July 2010 - 05:16 PM, said:

Exception in thread "main" java.lang.ClassCastException: jxl.write.Number cannot be cast to jxl.write.Label
at TestCasesSheet.main(TestCasesSheet.java:29)

On this line:

Label l = (Label) cell;//label of the cell.. not sure what this is exactly...

Not sure how to fix this.

The cell contains a Number you cannot just make a Label out of it
It is like he you wrote in pure Java

// following code example is wrong

int n = 12345;
Label l = n;

// you have to do

Label l = new Label("" + n);  // as nothing to do

Oups confusion between awt.Label and jxl.Label

This post has been edited by pbl: 20 July 2010 - 06:17 PM

[eZACKe]

Like this?

Label l = new Label("" + cell);

Well that's not working either.


Maybe I'm just going about this wrong? Is there an easier way to get the contents of say for example cell A2? And then set those contents to something else? I feel like I'm missing something.

[pbl]

eZACKe, on 20 July 2010 - 06:28 PM, said:

Like this?

Label l = new Label("" + cell);

Well that's not working either.


Maybe I'm just going about this wrong? Is there an easier way to get the contents of say for example cell A2? And then set those contents to something else? I feel like I'm missing something.

    WritableCell cell = sheet1.getWritableCell(0, i);//cell = column a, row i  
    Label l = (Label) cell;//label of the cell.. not sure what this is exactly...  

not sure what a WritableCell object is and if it has a toString() method
I would have to check but the name tells me

sheet1.getWritableCell(0, i) // give a cell where I can write to at 0, i
I don't see why you would like to make a Label out a blank new cell

I'll check my doc

[eZACKe]

Well I'm not 100% sure what a Label is actually, I was just following the tutorial and they did that. If all goes well in this program, I won't be touching any blank cells at all. If the cells are empty I want to skip them. Just modify them if they have something written in them and they are in column A.

And WriteableCell does indeed have a toString() method, not too sure how that will help me though?

[pbl]

OK check my code
These are not java.awt.Label but jxl.Label

syntax id

Label jxLabel = new Label(int col, int row, String str, WritableCellFormat wcf);

[eZACKe]

New code:

import java.io.File; 
import java.io.IOException;
import jxl.*; 
import jxl.read.biff.BiffException;
import jxl.write.*; 
import java.util.*;

public class TestCasesSheet
{
	public static void main(String[] args) throws BiffException, IOException, WriteException
	{
		//file i'm reading from
		Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\Zack\\Desktop\\Envisage Information Systems\\Test cases from QC BTW - MVAS_0716100.xls"));
	
		//make a copy of it
		WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);
	
		//sheet i'm working on, should be first sheet which is 0?
		WritableSheet sheet1 = copy.getSheet(1);//the first sheet
	
		
		
		int max = 32284;//number of rows in the file
		int count = 1;//running count
		
	
		for(int i = 0; i < max; i++)
		{
			WritableCell cell = sheet1.getWritableCell(0, i);//cell = column a, row i
			Label l = new Label(0, i, "" + cell);//label of the cell.. not sure what this is exactly...
			//.to
			
			System.out.println(cell.getContents().toString());
			if(!cell.toString().equals(""))//if the cell is not empty...
			{
				//make it say count - whatever was already there
				l.setString("" + count + "-" + " " + l.getContents());
			}
			
			count++;//increase count			
		}
		
		copy.write();//write to copy
		copy.close();//close copy
		
	}
}

Ok as you can see I changed the label, but don't know what your last parameter is..?

Also, I did a print statement, and it is actually printing the cell but it starts at row 16145 when it should be starting at 1 seeing as that is the first cell in column A that is not empty. Though it is printing those cells out, it is not modifying them at all.


Not sure what's going on.

[pbl]

You have to check that the WritableCell you extract is a Label (a Cell that conmtains text)

WritableCell cell = sheet.getWritableCell(col, row); 

if (cell.getType() == CellType.LABEL) 
{ 
  Label l = (Label) cell; 
  l.setString("whatever you want here"); 
} 

[eZACKe]

Works great, thank you so much!

Here's my code for those following along:

import java.io.File; 
import java.io.IOException;
import jxl.*; 
import jxl.read.biff.BiffException;
import jxl.write.*; 
import java.util.*;

public class TestCasesSheet
{
	public static void main(String[] args) throws BiffException, IOException, WriteException
	{
		//file i'm reading from
		Workbook workbook = Workbook.getWorkbook(new File("C:\\Users\\Zack\\Desktop\\Envisage Information Systems\\Test cases from QC BTW - MVAS_0716100.xls"));
	
		//make a copy of it
		WritableWorkbook copy = Workbook.createWorkbook(new File("output.xls"), workbook);
	
		//sheet i'm working on, should be first sheet which is 0?
		WritableSheet sheet1 = copy.getSheet(1);//the first sheet
	
		
		
		int max = 32284;//number of rows in the file
		int count = 1;//running count
		
	
		for(int i = 1; i < max; i++)
		{
			WritableCell cell = sheet1.getWritableCell(0, i);//cell = column a, row i
			//Label l = new Label(0, i, "" + cell);//label of the cell.. not sure what this is exactly...
			
			
			if (cell.getType() == CellType.LABEL) 
			{ 
			  Label l = (Label) cell; 
			  l.setString("" + count + "-" + " " + l.getContents()); 
			  count++;//increase count	
			} 
			
			
			
		}
		
		
		copy.write();//write to copy
		copy.close();//close copy
		
	}
}

Just one last question: Is there a way I can make the text I add bold?

Thank you

[KYA]

Check this out

from the WritableFont section