I'm relatively new to Java (learning on my own for about 6 months) and I have never written a program as large as the one I'm working on now. I have to take tax data and filter it and then calculate fees for an entire county given an Excel spreadsheet and then output a new Excel Workbook with the data sorted on different sheets using Apache POI v4.1.2. I've been working on this program for a month and I finally got it to where it works beautifully for an excerpt of 100 out of the 135k records. When I went to run it on the full file I got a NullPointer error. I believe this is because my laptop ran out of memory and that it happened because I only write the file at the end of the program so it has to store all the data and calculations and everything else until the very end.
Sorry for the long intro but my question is: How do I write the file, close it, and reopen it periodically to give my poor potato laptop a break before taking it's next "byte" of data (sorry for the pun. I couldn't resist)? I know I should post code and an error log but my code is pretty long with a class and a bunch of methods in it and I don't think you guys want to read my entire 700+ line code. If there is something in particular I can share then let me know. Be gentle though. I've always been able to find my answers with a search and so I haven't really needed to ask questions before. Of course if there is there is a better way of preserving memory as a program runs then I'm open to that suggestion as well.
package swfeecalc;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.JOptionPane;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author B
*/
public class SWFeeCalc {
/**
* @param args the command line arguments
*/
public static void main(String[] args) throws IOException {
//Excel Workbook
XSSFWorkbook wb = null;
//Excel Sheets
XSSFSheet orgSht = null; //original raw data minus unused columns
XSSFSheet sortSht = null; //sorted raw data sheet
XSSFSheet dupSht = null; //duplicates that were thrown out
XSSFSheet exSht = null; //records thrown out because of exemptions
XSSFSheet finSht = null; //final product
XSSFSheet errSht = null; //unhandled errors (may not end up getting used)
CellCopyPolicy policy = new CellCopyPolicy();
try{
//output fileDir
String date = new SimpleDateFormat("MM-dd-yyyy").format(new Date());
String rawDataInputFolder = "C:\\StormWaterFeeReports\\Reports\\" +date +"\\";
File excelFolder = new File(rawDataInputFolder);
boolean dirCreated = excelFolder.mkdirs();
System.out.println("Directory created = " +dirCreated);
//output sheet name
String timeStamp = new SimpleDateFormat("HH.mm.ss").format(new Date());
String rawDataInput = ("FeeData " +timeStamp +".xlsx");
File excelFile = new File(rawDataInputFolder +rawDataInput);
//create output sheets
wb = new XSSFWorkbook();
finSht = wb.createSheet ("Final Product");
sortSht = wb.createSheet("Sorted Raw Data");
orgSht = wb.createSheet("Raw Data");
dupSht = wb.createSheet("Duplicates");
exSht = wb.createSheet ("Exemptions");
errSht = wb.createSheet ("Unhandled Errors");
FileOutputStream out = new FileOutputStream(excelFile);
int rowNum = 0; //row number
int cellNum = 0; //cell number
//Create output sheets title row
XSSFRow row = orgSht.createRow(rowNum);
XSSFCell cell = row.createCell(cellNum);
//prompt user to put file in folder
JOptionPane.showMessageDialog(null, "Folder created... \n"
+ "Please enter an Excel spreadsheet titled 'RawData' (rename if necessary) in to the folder at the following location: \n\n"
+rawDataInputFolder +"\n\n"
+ "This file will be used to calculate the Storm Water fee and should be the raw tax data provided by the Assessors office.\n "
+ "Please rename the file as mentioned above if needed. \n"
+ "Click OK when finished.");
//input file path
String excelInputName = rawDataInputFolder +"RawData.xlsx"; //create string to hold file directory for new workbook
FileInputStream inputStream = new FileInputStream(new File(excelInputName)); //finds inputStream from the file directory in excelInputName
//create input workbook
XSSFWorkbook iwb = new XSSFWorkbook(inputStream);
//input sheet
XSSFSheet inSht = iwb.getSheetAt(0);
int rowEnd = inSht.getLastRowNum(); //variable to hold end of row
double inCellValNum;
String inCellValStr;
CellType type = cell.getCellType();
for (int i = 0; i <= rowEnd; i++){ //loop that populates the orgSht
XSSFRow inRow = inSht.getRow(i);
row = orgSht.createRow(i);
cellNum = 0;
for (int j = 0; j < inRow.getLastCellNum(); j++){
if(j ==0 || j ==3 || j ==4 || j ==5 || j ==6 || j ==9 || j ==11){
}
else{
XSSFCell inCell = inRow.getCell(j);
type = inCell.getCellType(); //checks cell type of current input cell
if (type == CellType.NUMERIC){
cell = row.createCell(cellNum); //make a new cell to store value
inCellValNum = inCell.getNumericCellValue(); //save value from input cell
cell.setCellValue(inCellValNum); //set the newly created cells value to the stored value
}
if (type == CellType.STRING){
cell = row.createCell(cellNum);
inCellValStr = inCell.getStringCellValue();
cell.setCellValue(inCellValStr);
}
cellNum++;
}
}
}
The error
Directory created = true
Exception in thread "main" java.lang.NullPointerException
at swfeecalc.SWFeeCalc.main(SWFeeCalc.java:121)
C:\Users\BD\AppData\Local\NetBeans\Cache\8.2\executor-snippets\run.xml:53: Java returned: 1
BUILD FAILED (total time: 8 seconds)
