/*
 * ExcelTest.java
 *
 * Created on December 15, 2002, 12:41 PM
 */

package greg.test;

import java.math.BigDecimal;
import java.util.Date;
import java.io.*;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.*;

/**
 *
 * @author  ggarson
 */
public class ExcelTest {
    
    /** Creates a new instance of ExcelTest */
    public ExcelTest() {
    }
    
    private static HSSFCellStyle headerStyle = null;
    private static HSSFCellStyle bodyStyle = null;
    
    public static void addDataToSheet(HSSFWorkbook book, int startAt){
        HSSFRow row = null;
        HSSFSheet sheet = null;
        HSSFCell cell = null;
        sheet = book.getSheetAt(0);
        if(sheet == null){
            return;
        }
        String cellName = null, cellType = null;
        row = sheet.getRow(11);
        if(row != null){
            cell = row.getCell((short)0);
        }
        headerStyle = cell.getCellStyle();
        int rowsAdded = 0;
        int logicalRow = 0;
        int cellsExpected = 0;
        String costCenter = "717";
        BigDecimal lawsonBudget = new BigDecimal(0.00);
        int[] projectIds = {1, 2, 3, 4, 5, 6};
        String[] accountCode1 = {"Account Code1", "1234"};
        String[] accountCode2 = {"Account Code2", "2345"};
        String[] accountCode3 = {"Account Code3", "3456"};
        String[] accountCode4 = {"Account Code4", "4567"};
        String[] accountCode5 = {"Account Code5", "5678"};
        String[] accountCode6 = {"Account Code6", "6789"};
        String[][] accountCodes = {accountCode1, accountCode2, accountCode3, accountCode4, accountCode5, accountCode6};
        for(int r = startAt;(row = sheet.getRow(r)) != null;r++){
            if(logicalRow == 0){
                cellsExpected = 2;
            }
            else{
                cellsExpected = 1;
            }
            for(int c = 0;c<=(cellsExpected - 1);c++){
                if(logicalRow == 0 && c == 0){
                    cellName = "costcenter";
                    cellType = "string";
                }
                if(logicalRow == 0 && c == 1){
                    cellName = "lawsonbudget";
                    cellType = "currency";
                }
                if(logicalRow == 7){
                    cellName = "costcenter";
                    cellType = "string";
                }
                if(logicalRow == 17){
                    cellName = "projectid";
                    cellType = "currency";
                }
                if(logicalRow == 21){
                    cellName = "accountcode";
                    cellType = "string";
                }
                if(cellName.equals("costcenter")){
                    System.out.println("Adding costcenter");
                    r += setCostCenter(sheet, costCenter, cellType, r, c);
                }
                if(cellName.equals("lawsonbudget")){
                    System.out.println("Adding lawson amount");
                    r += setLawsonBudget(sheet, lawsonBudget, cellType, r, c);
                }
                if(cellName.equals("projectid")){
                    System.out.println("Adding project id");
                    r += setProjectIds(sheet, projectIds, cellType, r, c);
                }
                if(cellName.equals("accountcode")){
                    System.out.println("Adding account code");
                    r += setAccountCodes(book, sheet, accountCodes, cellType, r, c);
                }
                cellName = "";
		cellType = null;	
            }
	    logicalRow++;
        }
    }
    
    private static int setCostCenter(HSSFSheet sheet, String costCenter, String cellType, int rowPos, int cellPos){
        HSSFRow row = null;
        HSSFCell cell = null;
        String val = null;
	row = sheet.getRow(rowPos);
        cell = row.createCell((short)cellPos);
        val = cell.getStringCellValue();
        val = (val != null ? val += (" " + costCenter) : costCenter);
	cell.setCellValue(val);
        return 0;
    }
    
    private static int setLawsonBudget(HSSFSheet sheet, BigDecimal data, String cellType,  int rowPos, int cellPos){
        HSSFRow row = null;
        HSSFCell cell = null;
        String val = null;
        row = sheet.getRow(rowPos);
        cell = row.createCell((short)cellPos);
        val = cell.getStringCellValue();
        val += " $" + data;
        cell.setCellValue(val);
        return 0;
    }
    
    private static int setProjectIds(HSSFSheet sheet, int[] projectIds, String cellType,  int rowPos, int cellPos){
        HSSFCell cell = null;
        HSSFRow row = sheet.getRow(rowPos);
        int p = 0;
        for(p = 0;(projectIds != null) && (p<=projectIds.length-1);p++){
            row = sheet.createRow((short)(rowPos+p));
            cell = row.createCell((short)cellPos);
            cell.setCellValue(projectIds[p]);
        }
        return p;
    }
    
    private static int setAccountCodes(HSSFWorkbook book, HSSFSheet sheet, String[][] accountCodes, String cellType,  int rowPos, int cellPos){
        HSSFCell cell = null;
        HSSFCellStyle cellStyle = null;
        HSSFRow row = sheet.getRow(rowPos);
        String[] accountCode = null;
        int a = 0;
        row = sheet.createRow((short)(rowPos));
        cell = row.createCell((short)cellPos);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("Account Code (and description)");
        a++;
        for(;(accountCodes != null) && (a<=accountCodes.length-1);a++){
            accountCode = accountCodes[a];
            row = sheet.createRow((short)(rowPos+a));
            cell = row.createCell((short)cellPos);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(accountCode[0] + " - " + accountCode[1]);
        }
        return a;
    }
    
    private static void setCellValue(HSSFCell cell, Object value, String cellType){
        HSSFCellStyle style = null;
        if((cellType.equals("numeric") || cellType.equals("currency")) && value != null){
            style = cell.getCellStyle();
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal)value).doubleValue());
            cell.setCellStyle(style);
        }
        else if(cellType.equals("string") && value != null){
            style = cell.getCellStyle();
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String)value);
            cell.setCellStyle(style);
        }
    }
    
    public static void main(String[] argvs){
        if(argvs.length < 2){
            System.out.println("ExcelTest <input file> <outputfile>");
            System.exit(9);
        }
        try{
            FileInputStream fis = new FileInputStream(argvs[0]);
            FileOutputStream fileOut = new FileOutputStream(argvs[1]);
            POIFSFileSystem poifs = new POIFSFileSystem(fis);
            HSSFWorkbook wb = new HSSFWorkbook(poifs);
            ExcelTest.addDataToSheet(wb, 5);
            wb.write(fileOut);
            fileOut.close();
        }
        catch(Exception e){
            e.printStackTrace();
            System.exit(0);
        }
        System.exit(0);

    }
}