Budget Preparation monthly estimation of budget java source code cfed

import java.io.IOException;

import javax.swing.JOptionPane;

/**

 * 

 */

/**

 * @author vipin

 *

 */

public class MainClass {

/**

* @param args

*/

public static void main(String[] args) {

    ReadExcel test = new ReadExcel();

   test.setInputFile("BUDGET.xls");

  // test.setInputFile("BUDGET.xls");

    try {

test.read();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

JOptionPane.showMessageDialog(null, "Err"+e.getMessage());

}

}

}

import java.io.File;

import java.io.IOException;

import java.util.HashMap;

import java.util.Map;

import java.util.Vector;

import javax.swing.JOptionPane;

import jxl.Cell;

import jxl.CellType;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.WriteException;

/**

 * 

 * @author cfed

 *

 */

public class ReadExcel {

  private String inputFile = null;

  private BudgetCreator budgetCreator = null;

  

  //private Vector<Vector> array =null;

  

  private Map<Integer,Double> lastFinYrRetail = null;

  private Map<Integer,Double> lastFinYrSbdy = null;

  private Map<Integer,Double> newFinYrRetail = null;

  private Map<Integer,Double> newFinYrSbdy = null;

  private Map<Integer,Double> budgetData = null;

  

  private int lstYrsRFirmCode = 0;

  private double lstYrsRValue = 0;

 

  private int lstYrsSFirmCode = 0;

  private double lstYrsSValue = 0;

  

  private int newYrsRFirmCode = 0;

  private double newYrsRValue = 0;

  private int newYrsSFirmCode = 0;

  private double newYrsSValue = 0;

  

  private int budgetFirmCode = 0;

  private double budgetValue = 0;

  public void setInputFile(String inputFile) {

    this.inputFile = inputFile;

  }

  public void read() throws IOException  {

  budgetCreator = new BudgetCreator();

// array = new Vector<Vector>();

  lastFinYrRetail = new HashMap<Integer, Double>();

  lastFinYrSbdy= new HashMap<Integer, Double>();

  newFinYrRetail = new HashMap<Integer, Double>();

  newFinYrSbdy= new HashMap<Integer, Double>();

  budgetData= new HashMap<Integer, Double>();

    File inputWorkbook = new File(inputFile);

    Workbook w;

    try {

      w = Workbook.getWorkbook(inputWorkbook);

      Sheet sheet = w.getSheet("Sheet1");

      

     int rowCount = sheet.getRows();

     int colCount = 10;

      

      for(int row=0; row<rowCount; row++){

      

      for(int col=0;col<colCount;col++){

      

      Cell cell = sheet.getCell(col, row);

      CellType type = cell.getType();

      String value = cell.getContents();

      switch (col) {

case 0:

lstYrsRFirmCode = budgetCreator.getNameFirmCode(value);

break;

case 1:

if(lstYrsRFirmCode>0&&(type==CellType.NUMBER)){

lstYrsRValue = Double.parseDouble(value);

    lastFinYrRetail.put(lstYrsRFirmCode, lstYrsRValue/100000);  // first retails input

}

break;

case 2:

lstYrsSFirmCode = budgetCreator.getNameFirmCode(value);

break;

case 3:

if(lstYrsSFirmCode>0&&(type==CellType.NUMBER)){

lstYrsSValue = Double.parseDouble(value);

    lastFinYrSbdy.put(lstYrsSFirmCode, lstYrsSValue/100000); // first sbdy input

}

break;

case 4:

newYrsRFirmCode = budgetCreator.getNameFirmCode(value);

break;

case 5:

if(newYrsRFirmCode>0&&(type==CellType.NUMBER)){

newYrsRValue = Double.parseDouble(value);

    newFinYrRetail.put(newYrsRFirmCode, newYrsRValue/100000); // new retail input

}

break;

case 6:

newYrsSFirmCode = budgetCreator.getNameFirmCode(value);

break;

case 7:

if(newYrsSFirmCode>0&&(type==CellType.NUMBER)){

newYrsSValue = Double.parseDouble(value);

    newFinYrSbdy.put(newYrsSFirmCode, newYrsSValue/100000); // new sbdy input

}

break;

case 8:

budgetFirmCode = budgetCreator.getNameFirmCode(value);

break;

case 9:

if((budgetFirmCode>0)&&(type==CellType.NUMBER)){  //add recently

budgetValue = Double.parseDouble(value);

    budgetData.put(budgetFirmCode, budgetValue); // budget value

}

break;

default:

break;

}

      

    }

   

      

      }

      

      Vector<Vector> array =budgetCreator.calculateBudget(lastFinYrRetail,lastFinYrSbdy,newFinYrRetail,newFinYrSbdy,budgetData);

      WriteExcel test = new WriteExcel();

         test.setOutputFile("BUDGET_REPORT.xls",array);

    //test.setOutputFile("BUDGET_REPORT.xls",array);

          test.write();

          JOptionPane.showMessageDialog(null, "Excel file : BUDGET_REPORT created !!! ");

          

    } catch (BiffException e) {

      e.printStackTrace();

      JOptionPane.showMessageDialog(null, "Err"+e.getMessage());

    } catch (WriteException e) {

// TODO Auto-generated catch block

e.printStackTrace();

JOptionPane.showMessageDialog(null, "Err"+e.getMessage());

}catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

JOptionPane.showMessageDialog(null, "Err"+e.getMessage());

}

  }

} 

import java.util.HashMap;

import java.util.Map;

public class UnitsMap {

private Map<Integer,String> unitMap = null;

public UnitsMap(){

unitMap = new HashMap<Integer, String>();

//unitMap.put(287, "GROCERY SECTION MUTHALAKULM( 0287)");

}

public Map<Integer, String> getUnitMap() {

unitMap.put(287,"GROCERY SECTION MUTHALAKULM( 0287)");

unitMap.put(2002,"LITTLE TRIVENI  STORE  KOODARANJI( 2002)");

unitMap.put(2003,"LITTLE TRIVENI STORE KODANCHERY( 2003)");

unitMap.put(2750,"LITTLE TRIVENI STORE KOODATHAI( 2750)");

unitMap.put(2001,"LITTLE TRIVENI STORE KOOLIMADU( 2001)");

unitMap.put(2649,"LITTLE TRIVENI STORE PANAMARAM( 2649)");

unitMap.put(2650,"LITTLE TRIVENI STORE VAALAD( 2650)");

unitMap.put(304,"LITTLE TRIVENI SUPER STORE  K P K( 0304)");

unitMap.put(327,"LITTLE TRIVENI SUPER STORE CHERUVAD( 0327)");

unitMap.put(2538,"LITTLE TRIVENI SUPER STORE KARASSERY( 2538)");

unitMap.put(410,"LITTLE TRIVENI SUPER STORE KODUVALL( 0410)");

unitMap.put(2514,"LITTLE TRIVENI SUPER STORE MANASSERY( 2514)");

unitMap.put(619,"MINI TRIVENI STORE KULAGARA( 0619)");

unitMap.put(2648,"MINI TRIVENI STORE PANNIMUKKU( 2648)");

unitMap.put(2540,"MOBILE TRIVENI BALUSSERY( 2540)");

unitMap.put(2541,"MOBILE TRIVENI BEYPORE( 2541)");

unitMap.put(2507,"MOBILE TRIVENI CALICUT NORTH( 2507)");

unitMap.put(1893,"MOBILE TRIVENI CALICUT SOUTH( 1893)");

unitMap.put(2505,"MOBILE TRIVENI KALPETTA( 2505)");

unitMap.put(2544,"MOBILE TRIVENI KODUVALLI( 2544)");

unitMap.put(2635,"MOBILE TRIVENI KOYILANDY( 2635)");

unitMap.put(2543,"MOBILE TRIVENI KUNNAMANGALAM( 2543)");

unitMap.put(2633,"MOBILE TRIVENI KUTTIADY( 2633)");

unitMap.put(1895,"MOBILE TRIVENI MANANTHAVADY( 1895)");

unitMap.put(2542,"MOBILE TRIVENI NADAPURAM( 2542)");

unitMap.put(2632,"MOBILE TRIVENI PERAMBRA( 2632)");

unitMap.put(1892,"MOBILE TRIVENI STORE ELATHOOR( 1892)");

unitMap.put(1894,"MOBILE TRIVENI SULTHANBATHERI( 1894)");

unitMap.put(2508,"MOBILE TRIVENI THIRUVAMPADI( 2508)");

unitMap.put(2634,"MOBILE TRIVENI VATAKARA( 2634)");

unitMap.put(257,"TRIVENI DEPARTMENT COSMETICS MUTHAL( 0257)");

unitMap.put(259,"TRIVENI GIFT LAND MUTHALAKULAM( 0259)");

unitMap.put(340,"TRIVENI GODOWN  VATAKARA( 0340)");

unitMap.put(33,"TRIVENI GODOWN GROCERY( 0033)");

unitMap.put(261,"TRIVENI SUPER MARKET  VATAKARA( 0261)");

unitMap.put(430,"TRIVENI SUPER MARKET BALUSSERY( 0430)");

unitMap.put(2977,"TRIVENI SUPER MARKET EASTHILL( 2977)");

unitMap.put(325,"TRIVENI SUPER MARKET FEROKE( 0325)");

unitMap.put(2804,"TRIVENI SUPER MARKET KADIYANGAD( 2804)");

unitMap.put(422,"TRIVENI SUPER MARKET KAKKATTIL( 0422)");

unitMap.put(431,"TRIVENI SUPER MARKET KALPATTA( 0431)");

unitMap.put(260,"TRIVENI SUPER MARKET KOYILAN( 0260)");

unitMap.put(450,"TRIVENI SUPER MARKET KUNNAMANGALAM( 0450)");

unitMap.put(266,"TRIVENI SUPER MARKET MANANTHAVADY( 0266)");

unitMap.put(414,"TRIVENI SUPER MARKET MEENANGADY( 0414)");

unitMap.put(258,"TRIVENI SUPER MARKET NADAKKAVU( 0258)");

unitMap.put(432,"TRIVENI SUPER MARKET NADAPURAM( 0432)");

unitMap.put(379,"TRIVENI SUPER MARKET PAROPADI( 0379)");

unitMap.put(328,"TRIVENI SUPER MARKET PERAMBRA( 0328)");

unitMap.put(263,"TRIVENI SUPER MARKET THAMARASSERY( 0263)");

unitMap.put(3001,"TRIVENI GODOWN KARASSERY(3001)");

return unitMap;

}

public void setUnitMap(Map<Integer, String> unitMap) {

this.unitMap = unitMap;

}

}

import java.io.File;

import java.io.IOException;

import java.util.Locale;

import java.util.Vector;

import jxl.CellView;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.UnderlineStyle;

import jxl.write.Formula;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class WriteExcel {

  private WritableCellFormat timesBoldUnderline;

  private WritableCellFormat times;

  private String inputFile;

  private Vector<Vector> array = null;

  public void setOutputFile(String inputFile) {

  this.inputFile = inputFile;

  }

  public void setOutputFile(String inputFile, Vector<Vector> array) {

  this.inputFile = inputFile;

  this.array = array;

}

  

  public void write() throws IOException, WriteException {

    File file = new File(inputFile);

    WorkbookSettings wbSettings = new WorkbookSettings();

    wbSettings.setLocale(new Locale("en", "EN"));

    WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);

    workbook.createSheet("Report", 0);

    WritableSheet excelSheet = workbook.getSheet("Report");

    createLabel(excelSheet);

    createContent(excelSheet);

    workbook.write();

    workbook.close();

  }

  private void createLabel(WritableSheet sheet)

      throws WriteException {

    // Lets create a times font

    WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);

    // Define the cell format

    times = new WritableCellFormat(times10pt);

    // Lets automatically wrap the cells

    times.setWrap(true);

    // Create create a bold font with unterlines

    WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,

        UnderlineStyle.SINGLE);

    timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);

    // Lets automatically wrap the cells

    timesBoldUnderline.setWrap(true);

    CellView cv = new CellView();

    cv.setFormat(times);

    cv.setFormat(timesBoldUnderline);

    cv.setAutosize(false);

    // Write a few headers

    addCaption(sheet, 0, 0, "SL No");

    addCaption(sheet, 1, 0, "Branch Name");

    addCaption(sheet, 2, 0, "Retail");

    addCaption(sheet, 3, 0, "Sbdy");

    addCaption(sheet, 4, 0, "Retail");

    addCaption(sheet, 5, 0, "Sbdy");

    addCaption(sheet, 6, 0, "Variation");

    addCaption(sheet, 7, 0, "Budget");

    addCaption(sheet, 8, 0, "Comparison");

    

  }

  private void createContent(WritableSheet sheet) throws WriteException,

      RowsExceededException {

  

  int size = array.size();

  sheet.setColumnView(1, 50);

      for(int row = 0; row < size; row ++){

  sheet.setRowView(row, 400);

  Vector val = array.get(row);

  for(int col =0; col<9;col++){

  addValues(sheet,col, row, val.get(col));

  }

}

  

  

  }

  private void addCaption(WritableSheet sheet, int column, int row, String s)

      throws RowsExceededException, WriteException {

    Label label;

    label = new Label(column, row, s, timesBoldUnderline);

    sheet.addCell(label);

  }

  private void addNumber(WritableSheet sheet, int column, int row,

      Integer integer ) throws WriteException, RowsExceededException {

 

    Number number;

    number = new Number(column, row, integer, times);

    sheet.addCell(number);

    

  }

  

  private void addValues(WritableSheet sheet, int column, int row, Object obj)

      throws WriteException, RowsExceededException {

    Label label;

    label = new Label(column, row, obj.toString(), times);

    sheet.addCell(label);

  }

  private void addLabel(WritableSheet sheet, int column, int row, String s)

      throws WriteException, RowsExceededException {

    Label label;

    label = new Label(column, row, s, times);

    sheet.addCell(label);

  }

} 

/**

 * Budget Creator

 */

import java.text.DecimalFormat;

import java.util.Map;

import java.util.Vector;

/**

 * Consumerfed kozhikode region

 */

/**

 * @author shimjith

 *

 */

public class BudgetCreator {

private UnitsMap unitMap = null;

private Vector<Vector> array = null; //output array

DecimalFormat decimalFormat = null;

public BudgetCreator(){

}

public int getNameFirmCode(String value) {

int firmCode = 0;

String str = value;

if(str.contains("(")&& str.contains(")")){

int startIndex = str.indexOf("(");

int endIndex = str.indexOf(")");

//System.out.println(true);

//System.out.println(startIndex + "--" +endIndex);

String sub = str.substring(startIndex+1, endIndex).trim();

sub = sub.trim();

//System.out.println(sub);

firmCode = Integer.parseInt(sub);

//System.out.println(" firmcode :"+firmCode);

}

return firmCode;

}

public void viewData(Map<Integer, Double> budgetData) {

// TODO Auto-generated method stub

System.out.println(" ------- Report -----------");

System.out.println(budgetData.size());

System.out.println(budgetData);

}

public Vector<Vector> calculateBudget(Map<Integer, Double> lastFinYrRetail,

Map<Integer, Double> lastFinYrSbdy,

Map<Integer, Double> newFinYrRetail,

Map<Integer, Double> newFinYrSbdy, Map<Integer, Double> budgetData) {

// TODO Auto-generated method stub

unitMap = new UnitsMap();

array = new Vector<Vector>();

Vector heading = new Vector();

heading.add("SLNO");

heading.add("BRANCH NAME");

heading.add("RETAIL");

heading.add("SBDY");

heading.add("RETAIL");

heading.add("SBDY");

heading.add("VARIATION");

heading.add("BUDGET");

heading.add("COMPARISON");

array.add(heading);

decimalFormat = new DecimalFormat("0.00");

Map map = unitMap.getUnitMap();

Vector data = null;

int serialNo = 1;

Double oldRetailTotal = 0.00;

Double oldSbdyTotal = 0.00;

Double newRetailTotal = 0.00;

Double newSbdyTotal = 0.00;

Double variationTotal = 0.00;

Double budgetTotal = 0.00;

Double comparisonTotal = 0.00;

for (Object key :map.keySet()){

data = new Vector();

int firmCode = Integer.parseInt(key.toString());

Double newRetail = newFinYrRetail.containsKey(firmCode) ? newFinYrRetail.get(firmCode) : 0.00;

Double newSbdy = newFinYrSbdy.containsKey(firmCode) ? newFinYrSbdy.get(firmCode) : 0.00;

Double oldRetail = lastFinYrRetail.containsKey(firmCode) ? lastFinYrRetail.get(firmCode) : 0.00;

Double oldSbdy = lastFinYrSbdy.containsKey(firmCode) ? lastFinYrSbdy.get(firmCode) : 0.00;

Double budgetValue = budgetData.containsKey(firmCode) ? budgetData.get(firmCode) : 0.00;

//System.out.println(firmCode +"--->"+budgetValue);

Double variation = (newRetail + newSbdy) - (oldRetail + oldSbdy);

Double comparison = budgetValue!=0 ? (newRetail + newSbdy)*100/budgetValue : 0.00;

//inserting all values to array

data.add(serialNo); //1

data.add(map.get(firmCode)); //2

data.add(Double.parseDouble(decimalFormat.format(oldRetail))); //3

data.add(Double.parseDouble(decimalFormat.format(oldSbdy))); //4

data.add(Double.parseDouble(decimalFormat.format(newRetail))); //5

data.add(Double.parseDouble(decimalFormat.format(newSbdy))); //6

data.add(Double.parseDouble(decimalFormat.format(variation))); //7

data.add(budgetValue); //8

data.add(decimalFormat.format(comparison)); //9

//Grand Total calculation

oldRetailTotal =  oldRetailTotal + Double.parseDouble(decimalFormat.format(oldRetail));

oldSbdyTotal =  oldSbdyTotal + Double.parseDouble(decimalFormat.format(oldSbdy));

newRetailTotal = newRetailTotal + Double.parseDouble(decimalFormat.format(newRetail));

newSbdyTotal =  newSbdyTotal + Double.parseDouble(decimalFormat.format(newSbdy));

variationTotal = variationTotal + Double.parseDouble(decimalFormat.format(variation));

budgetTotal = budgetTotal + budgetValue;

//System.out.println(" String : "+comparison);

comparisonTotal = comparisonTotal + Double.parseDouble(decimalFormat.format(comparison));

//System.out.println(map.get(key));

//System.out.println(" firmcode :"+ firmCode +" variation : "+Variation + "comparison : "+comparison);

serialNo = serialNo +1;

array.add(data);

}

Vector footer = new Vector();

footer.add(" ");

footer.add(" GRAND TOTAL ");

footer.add(oldRetailTotal);

footer.add(oldSbdyTotal);

footer.add(newRetailTotal);

footer.add(newSbdyTotal);

footer.add(variationTotal);

footer.add(budgetTotal);

footer.add(comparisonTotal);

array.add(footer);

return array;

}

}

dependency jar : jxl.jar

git clone : https://github.com/consumerfed/BudgetPreparation.git