the following code is used to get the contents of an excel document and pint it to console - will eventually filter and write a new excel file, but that will come later.
/**
*
*/
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFCellStyle;
//import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
/**
* @author bjchampl
*
*/
class BpsExportGUI extends JFrame implements ActionListener{
JTextField fileIN; //Text Field Bull
//JLabel fInHelp;//Labels time
JFileChooser BpsFile = new JFileChooser(); //File Chooser
JCheckBox Check;
JButton expButton; //reference button object
JButton opnButton;
JButton reset;
JButton all;
//Panels
List<JCheckBox> checkList = new ArrayList<JCheckBox>();
JPanel inOut = new JPanel();
JPanel ChckBx= new JPanel();
BpsExportGUI(String title, String[] chkNms){ // Constructor
//Name Window
super(title);
//construct TextField //Set what to do
fileIN = new JTextField(15);
fileIN.addActionListener(this);
fileIN.setEditable(false);
//Label constructor
// fInHelp = new JLabel("OPen- Choose File to export");
// construct all JButton & Actions
expButton = new JButton("Export");
expButton.setActionCommand("export");
opnButton = new JButton("Open");
opnButton.setActionCommand("Open");
reset = new JButton("Reset");
reset.setActionCommand("reset");
all = new JButton("Check All");
all.setActionCommand("all");
// set the layout manager
setLayout( new FlowLayout());
inOut.add(fileIN);
inOut.add(opnButton);
inOut.add(expButton);
ChckBx.setLayout(new GridLayout());
// ChckBx.add(Check);
// ChckBx.setLayout(new GridLayout());
// ChckBx.add(Check);
/**
* Order for adding in the parts of the GUI will matter
**/
//add Label to text field
//add(fInHelp);
//Add Text Field
add(fileIN);
//add buttons and check boxes
add(opnButton);
add(expButton);
add(all);
//CheckBox Creator
for(String name : chkNms) {
Check = new JCheckBox(name);
Check.setMnemonic(KeyEvent.VK_C);
Check.setSelected(false);
Check.setActionCommand(name);
add(Check);
checkList.add(Check);
Check.addActionListener(this);
}
add(reset);
//register listener to buttons
expButton.addActionListener(this);
opnButton.addActionListener(this);
//Set what happens on hitting x(close) button- top right
setDefaultCloseOperation( JFrame.EXIT_ON_CLOSE );
}
// listener method for the ActionListener interface
public void actionPerformed( ActionEvent evt)
{
ArrayList<String> filter = new ArrayList<String>();
for(int x=0; x< checkList.size();x++){
}
if(evt.getActionCommand().equals("reset")){
}
// if(evt.getActionCommand().equals("all")){
// }
if(evt.getActionCommand().equals("export")){
String file2Read;
file2Read = fileIN.getText();
//for(int i = 0; i < checkList.size(); i++){
// JCheckBox cb = checkList.get(i);
// if(cb.isSelected()){
// filter.add(cb.getText());
// }
// }
new BPSepcExporter().BPSReader(file2Read);
}
//If to check if Open button was hit
if(evt.getActionCommand().equals("Open")){
//If to make sure something useful was done
int fIn = BpsFile.showOpenDialog(null);
BpsFile.setFileSelectionMode(1);
if(fIn == JFileChooser.APPROVE_OPTION){
File file = BpsFile.getSelectedFile();
fileIN.setText(file.getPath());
}
}
}
}
public class BPSepcExporter {
//Main is the call for all other nested functions
public static void main(String[] args) {
int width = 750;
int heigth = 375;
//Array of the check names that will be passed into the gui for creation
String chkNms[] = {"COMMITMENTTYPE", "PONUMBER", "HEADERSTATUS", "VENDORNUMBER", "LINENUMBER",
"LINESTATUS", "REVISION", "JOB", "CURRENCYCODE", "POAwardDate", "RELEASENUMBER",
"RELEASELINENUMBER", "PODESCRIPTION", "PERCENTQTY", "SUBRESOURCE", "EACQTY",
"EACAMT COSTCODE", "QTYORDERED", "RATEDATE", "UOM", "UNITPRICE", "DESCRIPTION",
"NATURALCLASS", "LINESTATUSDATE", "LINETYPE"};
String title = "BPS to EPC Works Exporter";
BpsExportGUI frame = new BpsExportGUI(title, chkNms);
frame.setSize( width, heigth );
frame.setLocationRelativeTo(null);
frame.setVisible( true );
//String[] checker = {"PONUMBER"};
}
public void BPSReader(String fileName){
//Input control variable- take from GUI options
List<List<HSSFCell>> cellDataList = new ArrayList<List<HSSFCell>>();
try {
//Create a new instance for FileInputStream class
FileInputStream fileInputStream = new FileInputStream(fileName);
// Create a new instance for POIFSFileSystem class
POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
//Create a new instance for HSSFWorkBook Class
HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
HSSFSheet hssfSheet = workBook.getSheetAt(0);
//Iterate the rows and cells of the spreadsheet to get all the datas.
Iterator<Row> rowIterator = hssfSheet.rowIterator();
while (rowIterator.hasNext()){
HSSFRow hssfRow = (HSSFRow) rowIterator.next();
Iterator<Cell> iterator = hssfRow.cellIterator();
List<HSSFCell> cellTempList = new ArrayList<HSSFCell>();
while (iterator.hasNext()){
HSSFCell hssfCell = (HSSFCell) iterator.next();
cellTempList.add(hssfCell);
}
cellDataList.add(cellTempList);
}
}
catch (Exception e){
e.printStackTrace();
}
printToConsole(cellDataList);
}
private void printToConsole(List<List<HSSFCell>> cellDataList){
int row = 0;
int col = 0;
//String[] checker = {"PONUMBER"};
for (int x = 0; x < cellDataList.size(); x++){
List cellTempList = (List) cellDataList.get(x);
row = cellDataList.size();
col = cellTempList.size();
String[][] listCollector = new String[row][col];
for (int y = 0; y < cellTempList.size(); y++){
HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);
listCollector[x][y] = hssfCell.toString();
//if(listCollector[y][0].equals(filter)){
System.out.print(listCollector[x][y] + "\t");
//}
}
System.out.println();
}
for(;;){
}
}
//Takes reader variable and will write an excel sheet
public static void exportFile(String fileName){
String exportFileName= fileName;
try {
FileOutputStream fileOut = new FileOutputStream(exportFileName);
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet workSheet = workbook.createSheet("Sheet1");
// index from 0,0... cell A1 is cell(0,0)
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}The problem is that once you load a file in and export it... the program freezes with the export button... frozen in a recessed position. No Run time errors present. Oh yes, the gui is very ugly at present, but I figure get the code working first, then work on how the gui looks. ALso the check all and reset buttons do not work.