Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package eu.squadd.timesheets.eolas; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.HashMap; import java.util.Locale; import java.util.Map; import java.util.TimeZone; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.commons.lang.StringUtils; 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.ss.usermodel.FormulaEvaluator; /** * * @author 58128 */ public class TimeTemplate { private static final String template = "timesheet_template.xls"; private static final String outFilePath = "Jaroslaw Smorczewski_#MONTH#_Timesheet.xls"; private static final Map<String, String> publicHolidays2017 = new HashMap(); static { } private static final Map<String, String> publicHolidays2016 = new HashMap(); static { publicHolidays2016.put("01/01/2016", "New Year Eve"); publicHolidays2016.put("17/03/2016", "St Patrics Day"); publicHolidays2016.put("25/03/2016", "Good Friday"); publicHolidays2016.put("28/03/2016", "Easter Monday"); publicHolidays2016.put("02/05/2016", "May Bank Holiday"); publicHolidays2016.put("06/06/2016", "June Bank Holiday"); publicHolidays2016.put("01/08/2016", "August Bank Holiday"); publicHolidays2016.put("31/10/2016", "October Bank Holiday"); publicHolidays2016.put("26/12/2016", "St Stephens Day"); publicHolidays2016.put("27/12/2016", "In lieu of Christmas Day"); } private String monthName; private final Integer defaultHours = 8; private int month; private int year; public String prepareTimesheet(String[] args) { String response = null; try { String[] ym = args[0].split("/"); month = Integer.parseInt(ym[0]); year = Integer.parseInt(ym[1]); Calendar cal = Calendar.getInstance(TimeZone.getDefault()); cal.set(Calendar.YEAR, year); cal.set(Calendar.MONTH, month - 1); int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH); monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH); String periodName = monthName + "-" + year; cal.set(Calendar.DATE, 1); String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime()); System.out.println("Month: " + periodName); System.out.println("Days in month: " + days); System.out.println("Month starts in: " + dayOfWeek); Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017; Map<String, String> holidays = this.extractHolidays(args); HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template)); HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0); HSSFRow currentRow; SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy"); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); sheet.getRow(4).getCell(1).setCellValue(periodName); int row = 7; int startRow = 0; int i = 1; while (i <= days) { currentRow = sheet.getRow(row); if (currentRow.getRowNum() > 47) break; String day = currentRow.getCell(0).getStringCellValue(); if (day.startsWith("Total")) { evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); row++; continue; } if (startRow == 0) { if (dayOfWeek.equals(day.substring(0, 3))) { startRow = currentRow.getRowNum(); System.out.println("Starting row found: " + startRow + 1); } else { row++; continue; } } cal.set(Calendar.DATE, i); String date = sdf.format(cal.getTime()); if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null && holidays.get(date) == null) { currentRow.getCell(1).setCellValue(date); currentRow.getCell(2).setCellValue(defaultHours); // regular hours //currentRow.getCell(3).setCellValue(defaultHours); // overtime hours currentRow.getCell(4).setCellValue(defaultHours); // total hours } i++; row++; } currentRow = sheet.getRow(46); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); currentRow = sheet.getRow(47); evaluator.evaluateFormulaCell(currentRow.getCell(2)); evaluator.evaluateFormulaCell(currentRow.getCell(4)); response = outFilePath.replace("#MONTH#", periodName); wb.write(new FileOutputStream(response)); } catch (IOException ex) { Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Timesheet created."); return response; } private Map extractHolidays(String[] args) { Map<String, String> holidays = new HashMap(); int j = 1; while (j < args.length) { String[] period = args[j].split("-"); int start = Integer.parseInt(period[0]); int end = period.length > 1 ? Integer.parseInt(period[1]) : 0; if (end > 0) { for (int i = start; i <= end; i++) { String dayOfMonth = StringUtils.leftPad(String.valueOf(i), 2, '0').concat("/") .concat(StringUtils.leftPad(String.valueOf(month), 2, '0')).concat("/") .concat(StringUtils.leftPad(String.valueOf(year), 2, '0')); holidays.put(dayOfMonth, "Leave"); } } else { String dayOfMonth = StringUtils.leftPad(String.valueOf(start), 2, '0').concat("/") .concat(StringUtils.leftPad(String.valueOf(month), 2, '0')).concat("/") .concat(StringUtils.leftPad(String.valueOf(year), 2, '0')); holidays.put(dayOfMonth, "Leave"); } j++; } return holidays; } }