Reading and Writing to Excel via Java, (JTable is involved)

[BrownZeus]

Hey all,

 

I'm writing a GUI based program and can't find any solid directions  on how to do what I want, though I know its possible.

 

The main GUI is going to contain a JTable, with only two columns (For first name, last name). I'd like for the Jtable to get it's data from an Excel spreadsheet, I'd also like to write to the Excel sheet via another function (A button).

 

I know there will be an Apache API I'd have to utilize, but don't really know much else from there. Any help will be appreciated.

[Nuluvius]

5 hours ago, BrownZeus said:

I'd like for the Jtable to get it's data from an Excel spreadsheet, I'd also like to write to the Excel sheet via another function (A button). 

Would a CSV file be an acceptable data medium? i.e. are you only concerned with raw data?

[Beesman]

For java try looking into POI-HSSF(/+XSSF) i suspect that's the Apache API you are talking about anyways.

Here is a quick guide with pretty much everything you would need for this:

http://poi.apache.org/spreadsheet/quick-guide.html

they also have some easy to understand examples in this svn:

https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/usermodel/examples/

 

Here is some more direct help so i am not the "here are links and learn" guy - it's a simple iteration and it's assuming things like you have your data in 1st sheet and you have your names in 1st two columns etc. but it should get you started.

Don't forget to import what you need - if you use eclipse or net beans or some other thing cool cats use these days instead of vi it should tell you what is missing if anything:

import javax.swing.table.*;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

//the JTable part - create it with DefaultTableModel:
DefaultTableModel nameTableModel = new DefaultTableModel();
model.addColumn("FirstName");
model.addColumn("LastName");
JTable table = new JTable(nameTableModel);

//the Excel part
InputStream inStr = new FileInputStream("C:/path_to_the_file/foo.xls");
Workbook wBook = WorkbookFactory.create(inStr);
Sheet sheet = wBook.getSheetAt(0); //Get's the first sheet - you can also find it via the sheet name but this should be enough for that

//and the iteration part filling the JTable with the excel table
for (int i = 0; i <= sheet.getLastRowNum(); i++)//iterate all rows from the first one to the last - make i 1 if you have headers or w/e
{
	Row row = sheet.getRow(i);
	Cell cell_firstName = row.getCell(0);//the first column cell in the row
	Cell cell_lastName = row.getCell(1);//the second column cell in the row
	
	string firstName = cell_firstName.getStringCellValue();
  	string secondName = cell_firstName.getStringCellValue();
  
  	//And here you just add them to your table or w/e
  	DefaultTableModel nameTableModel = (DefaultTableModel) table.getModel();
  	nameTableModel.addRow(new Object[]{firstName, secondName});
}

Good luck!