Java tutorial
/* * File: $RCSfile$ * * Copyright (c) 2005 Wincor Nixdorf International GmbH, * Heinz-Nixdorf-Ring 1, 33106 Paderborn, Germany * All Rights Reserved. * * This software is the confidential and proprietary information * of Wincor Nixdorf ("Confidential Information"). You shall not * disclose such Confidential Information and shall use it only in * accordance with the terms of the license agreement you entered * into with Wincor Nixdorf. */ package cn.fql.template.poi; /** * The class <code>cn.fql.template.poi.MergeInvoiceSheet</code> * * @author User, WN ASP SSD * @version $Revision$ */ import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.Region; /** * The class <code>com.rab.test.research.invoice.MergeInvoiceSheet</code> * * @author feng.xie, WN ASP SSD * @version $Revision$ */ public class MergeInvoiceSheet { private static HSSFWorkbook templateWbk; private static HSSFCellStyle percentageStyle; public static void main(String[] args) { writeInvoice(args[0], args[1]); writeMonth(args[2], args[3]); } private static void writeMonth(String inputFile, String outputFile) { FileOutputStream oos = null; FileInputStream fis = null; try { oos = new FileOutputStream(outputFile); fis = new FileInputStream(inputFile); templateWbk = new HSSFWorkbook(fis); percentageStyle = templateWbk.createCellStyle(); percentageStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); percentageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); percentageStyle.setDataFormat((short) 9); writeIndividualTime(); writeSubActivityTime(); templateWbk.write(oos); } catch (Exception e) { e.printStackTrace(); } finally { try { fis.close(); oos.close(); } catch (IOException e) { e.printStackTrace(); } } } private static void writeInvoice(String inputFile, String outputFile) { FileOutputStream oos = null; FileInputStream fis = null; try { oos = new FileOutputStream(outputFile); fis = new FileInputStream(inputFile); templateWbk = new HSSFWorkbook(fis); percentageStyle = templateWbk.createCellStyle(); percentageStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); percentageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); percentageStyle.setDataFormat((short) 9); writeEffort(1, "Effort Summary by Type"); writeEffortSummary(); writeEffortTotal(); templateWbk.write(oos); } catch (Exception e) { e.printStackTrace(); } finally { try { fis.close(); oos.close(); } catch (IOException e) { e.printStackTrace(); } } } private static List getMonthInfo(int index, HSSFRow row, HSSFSheet templateSheet) { List months = new ArrayList(); row = templateSheet.getRow(index - 1); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } return months; } private static void writeSubActivityTime() { Map userHours = getHoursInfo(); HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage"); String lastSubActivityName = null; List months = new ArrayList(); int count = 0; for (int i = 5; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } HSSFCell cell = row.getCell((short) 4); HSSFCell userNameCell = row.getCell((short) 2); if (cell != null) { String userName = userNameCell.getStringCellValue(); if ("Month".equals(userName.trim()) || "User Name".equals(userName.trim())) { continue; } if ("Normal Working day of a Month".equals(userName.trim())) { months = getMonthInfo(i, row, templateSheet); continue; } if (lastSubActivityName == null) { lastSubActivityName = cell.getStringCellValue(); count = 1; } else { String newSubActivityName = cell.getStringCellValue(); if (newSubActivityName != null) { if (newSubActivityName.equals(lastSubActivityName)) { count++; for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5)) .getCellStyle().getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); HSSFCell precOfSubActivity = row.getCell((short) (j + 3)); // String prec = precOfType.getStringCellValue().trim(); double prec = precOfType.getNumericCellValue(); String key = userName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j)); region.setColumnTo((short) (j)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) j).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 3)); region.setColumnTo((short) (j + 3)); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) (j + 3)).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); if (prec > 0 && timeUsage.getPercentage() > 0) { templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellStyle(percentageStyle); double oldValue = templateSheet.getRow(i - count + 1) .getCell((short) (j + 3)).getNumericCellValue(); templateSheet.getRow(i - count + 1).getCell((short) (j + 3)) .setCellValue(oldValue + prec); } j += 6; k++; continue; } j++; } } else { lastSubActivityName = newSubActivityName; writePrecOfSubActivity(templateSheet, months, userName, userHours, row, i); count = 1; } } else { lastSubActivityName = newSubActivityName; count = 1; } } } } } private static void writeIndividualTime() { Map userHours = getHoursInfo(); HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage"); String lastUserName = null; List months = new ArrayList(); for (int i = 5; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } HSSFCell cell = row.getCell((short) 2); if (cell != null) { if (lastUserName == null) { lastUserName = cell.getStringCellValue(); } else { String newUserName = cell.getStringCellValue(); if ("Month".equals(newUserName.trim()) || "User Name".equals(newUserName.trim())) { continue; } if ("Normal Working day of a Month".equals(newUserName.trim())) { months = new ArrayList(); row = templateSheet.getRow(i - 1); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } continue; } if (newUserName != null) { if (newUserName.equals(lastUserName)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 2); region.setColumnTo((short) 2); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 2).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell activityCell = row.getCell((short) 3); HSSFCell oldActivityCell = templateSheet.getRow(i - 1).getCell((short) 3); if (activityCell.getStringCellValue().equals(oldActivityCell.getStringCellValue())) { region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 3); region.setColumnTo((short) 3); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(i - 1).getCell((short) (j + 5)) .getCellStyle().getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); String prec = precOfType.getStringCellValue().trim(); String key = newUserName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); if (!prec.equals("")) { if (timeUsage.getHours() > 0) { precOfType.setCellStyle(percentageStyle); precOfType.setCellValue( (new BigDecimal(prec)).doubleValue() / timeUsage.getHours()); } } region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 4)); region.setColumnTo((short) (j + 4)); templateSheet.addMergedRegion(region); if (timeUsage.getHours() > 0) { templateSheet.getRow(i - 1).getCell((short) (j + 4)) .setCellValue(timeUsage.getHours()); } region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) (j + 5)); region.setColumnTo((short) (j + 5)); templateSheet.addMergedRegion(region); if (timeUsage.getPercentage() > 0) { templateSheet.getRow(i - 1).getCell((short) (j + 5)) .setCellStyle(percentageStyle); templateSheet.getRow(i - 1).getCell((short) (j + 5)) .setCellValue(timeUsage.getPercentage()); } j += 6; k++; continue; } j++; } } else { lastUserName = newUserName; writePrec(templateSheet, months, newUserName, userHours, row, i); } } else { lastUserName = newUserName; } } } } } private static void writePrecOfSubActivity(HSSFSheet templateSheet, List months, String newUserName, Map userHours, HSSFRow row, int rowIndex) { for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle() .getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); HSSFCell precOfSubActivity = row.getCell((short) (j + 3)); try { double prec = precOfType.getNumericCellValue(); String key = newUserName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); if (prec > 0) { if (timeUsage.getHours() > 0) { precOfSubActivity.setCellStyle(percentageStyle); precOfSubActivity.setCellValue(prec); } } } catch (Exception e) { } j += 6; k++; } } } private static void writePrec(HSSFSheet templateSheet, List months, String newUserName, Map userHours, HSSFRow row, int rowIndex) { for (int j = 4, k = 0; j < 1000;) { HSSFCell detailCell = row.getCell((short) (j)); if (detailCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { percentageStyle.setFont(templateSheet.getRow(rowIndex - 1).getCell((short) (j + 5)).getCellStyle() .getFont(templateWbk)); HSSFCell precOfType = row.getCell((short) (j + 2)); String prec = precOfType.getStringCellValue().trim(); String key = newUserName + "." + months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(key); if (!prec.equals("")) { if (timeUsage.getHours() > 0) { precOfType.setCellStyle(percentageStyle); precOfType.setCellValue((new BigDecimal(prec)).doubleValue() / timeUsage.getHours()); } } j += 6; k++; } } } private static Map getHoursInfo() { HSSFSheet templateSheet = templateWbk.getSheet("Individual Time Usage (1)"); boolean fillMap = false; Map userHours = new HashMap(); List months = new ArrayList(); for (int i = 4; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } else { fillMap = false; } } else { HSSFCell userNameCell = row.getCell((short) 2); if (userNameCell == null) { continue; } String userNameCellValue = userNameCell.getStringCellValue(); if ("Month".equals(userNameCellValue.trim())) { months = new ArrayList(); for (int j = 3; j < 1000; j++) { HSSFCell monthCell = row.getCell((short) j); if (monthCell == null) { if (row.getCell((short) (j + 1)) == null) { break; } } else { String monthValue = monthCell.getStringCellValue(); if (monthValue != null && !monthValue.equals("")) { months.add(monthCell.getStringCellValue()); } } } continue; } if ("Normal Working day of a Month".equals(userNameCellValue.trim())) { continue; } if ("User Name".equals(userNameCellValue)) { fillMap = true; } else if (fillMap) { for (int j = 3, k = 0; j < 1000; j++) { HSSFCell detailCell = row.getCell((short) j); if (detailCell == null) { break; } else { if (j % 2 == 1) { TimeUsage timeUsage = new TimeUsage(); String hoursStr = detailCell.getStringCellValue().trim(); String month = (String) months.get(k); userHours.put(userNameCellValue + "." + month, timeUsage); if (hoursStr != null && !hoursStr.equals("")) { double hours = (new BigDecimal(hoursStr)).doubleValue(); timeUsage.setHours(hours); } } else { String month = (String) months.get(k); TimeUsage timeUsage = (TimeUsage) userHours.get(userNameCellValue + "." + month); timeUsage.setPercentage(detailCell.getNumericCellValue()); k++; } } } } } } return userHours; } private static void writeEffortTotal() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Total"); String lastProjectName = null; Date startDateValue = null; Date endDateValue = null; double totalDaysValue = 0; for (int i = 4; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell projectNameCell = row.getCell((short) 1); HSSFCell startDateCell = row.getCell((short) 2); HSSFCell endDateCell = row.getCell((short) 3); HSSFCell totalDaysCell = row.getCell((short) 4); if (totalDaysCell == null) { break; } String newProjectName = projectNameCell.getStringCellValue(); Date _startDateValue = startDateCell.getDateCellValue(); Date _endDateValue = endDateCell.getDateCellValue(); double _totalDaysValue = totalDaysCell.getNumericCellValue(); if (lastProjectName == null) { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } else { if (newProjectName.equals(lastProjectName)) { totalDaysValue += _totalDaysValue; templateSheet.getRow(i - 1).getCell((short) 4).setCellValue(totalDaysValue); if (startDateValue.compareTo(_startDateValue) > 0) { startDateValue = _startDateValue; templateSheet.getRow(i - 1).getCell((short) 2).setCellValue(startDateValue); } if (endDateValue.compareTo(_endDateValue) < 0) { endDateValue = _endDateValue; templateSheet.getRow(i - 1).getCell((short) 3).setCellValue(endDateValue); } templateSheet.removeRow(row); templateSheet.shiftRows(i + 1, 109, -1); i--; } else { lastProjectName = newProjectName; startDateValue = _startDateValue; endDateValue = _endDateValue; totalDaysValue = _totalDaysValue; } } } } } public static void writeEffortSummary() { HSSFSheet templateSheet = templateWbk.getSheet("Effort Summary"); String lastCellValue = null; double days = 0; int count = 1; for (int i = 6; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) 2); HSSFCell dayCell = row.getCell((short) 6); if (cell != null) { if (lastCellValue == null) { lastCellValue = cell.getStringCellValue(); count = 1; String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } else { String newCellValue = cell.getStringCellValue(); if ("Project Name".equals(newCellValue)) { continue; } if (newCellValue != null) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 2); region.setColumnTo((short) 2); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) 3).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 3); region.setColumnTo((short) 3); templateSheet.addMergedRegion(region); count++; templateSheet.getRow(i - count + 1).getCell((short) 3).setCellValue((double) count); region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) 4); region.setColumnTo((short) 4); templateSheet.addMergedRegion(region); String dayStr = dayCell.getStringCellValue().trim(); days += new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i - count + 1).getCell((short) 4).setCellValue(days); } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } else { lastCellValue = newCellValue; count = 1; templateSheet.getRow(i).getCell((short) 3).setCellValue((double) count); String dayStr = dayCell.getStringCellValue().trim(); days = new BigDecimal(dayStr).doubleValue(); templateSheet.getRow(i).getCell((short) 4).setCellValue(days); } } } } } } public static void writeEffort(int index, String sheetName) { HSSFSheet templateSheet = templateWbk.getSheet(sheetName); String lastCellValue = null; for (int i = 0; i < 10000; i++) { HSSFRow row = templateSheet.getRow(i); if (row == null) { if (templateSheet.getRow(i + 1) == null) { break; } } else { HSSFCell cell = row.getCell((short) index); if (cell != null) { if (lastCellValue == null && cell.getStringCellValue() != null && !cell.getStringCellValue().trim().equals("")) { lastCellValue = cell.getStringCellValue(); } else { String newCellValue = cell.getStringCellValue(); if (newCellValue != null && !newCellValue.trim().equals("")) { if (newCellValue.equals(lastCellValue)) { Region region = new Region(); region.setRowTo(i); region.setRowFrom(i - 1); region.setColumnFrom((short) index); region.setColumnTo((short) index); templateSheet.addMergedRegion(region); templateSheet.getRow(i - 1).getCell((short) index).getCellStyle() .setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } else { lastCellValue = newCellValue; } } else { lastCellValue = newCellValue; } } } } } } } /** * History: /** * History: * * $Log$ */