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 com.nkapps.billing.services; import java.io.File; import java.io.FileInputStream; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import javax.servlet.ServletContext; 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.CellReference; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.nkapps.billing.models.BankStatement; import com.nkapps.billing.models.PrintClaimPojo; import com.nkapps.billing.models.PrintClaimRegisterPojo; import com.nkapps.billing.models.PrintRegisterPojo; /** * * @author nuraddin */ @Service("bankStatementPrintService") public class BankStatementPrintServiceImpl implements BankStatementPrintService { @Autowired private ServletContext servletContext; public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, String value) { HSSFCell cell; cell = row.createCell(cellCurrent); if (value == null) { cell.setCellValue(""); } else { cell.setCellValue(value); } cell.setCellStyle(style[cellCurrent++]); return true; } public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, Short value) { HSSFCell cell; cell = row.createCell(cellCurrent); if (value == null) { cell.setCellValue(""); } else { cell.setCellValue(value); } cell.setCellStyle(style[cellCurrent++]); return true; } public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, Integer value) { HSSFCell cell; cell = row.createCell(cellCurrent); if (value == null) { cell.setCellValue(""); } else { cell.setCellValue(value); } cell.setCellStyle(style[cellCurrent++]); return true; } public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, Long value) { HSSFCell cell; cell = row.createCell(cellCurrent); if (value == null) { cell.setCellValue(""); } else { cell.setCellValue(value); } cell.setCellStyle(style[cellCurrent++]); return true; } public boolean createCell(HSSFRow row, int cellCurrent, HSSFCellStyle[] style, BigDecimal value) { HSSFCell cell; cell = row.createCell(cellCurrent); if (value == null) { cell.setCellValue(""); } else { cell.setCellValue(value.doubleValue()); } cell.setCellStyle(style[cellCurrent++]); return true; } public boolean createTotalCells(List totalCellIndexes, HSSFSheet sheet, HSSFCellStyle[] totalStyle, Short totalStyleHeight, int rowStart, int cellStart, int rowCurrent) { HSSFRow row; HSSFCell cell; int rowTotal; if (rowCurrent == rowStart) rowCurrent++; rowTotal = rowCurrent - 1; row = sheet.createRow(rowCurrent); row.setHeight(totalStyleHeight); for (Iterator it = totalCellIndexes.iterator(); it.hasNext();) { Integer cellIndex = (Integer) it.next(); cell = row.createCell(cellIndex); String columnLetter = CellReference.convertNumToColString(cellIndex); cell.setCellFormula("SUM(" + columnLetter + (rowStart + 1) + ":" + columnLetter + (rowTotal + 1) + ")"); cell.setCellStyle(totalStyle[cellIndex]); } return true; } @Override public HSSFWorkbook printClaim(List<PrintClaimPojo> listPojo) throws Exception { HSSFWorkbook workbook = null; POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream(servletContext.getRealPath("templates") + File.separator + "print_claim.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (PrintClaimPojo pcp : listPojo) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, numQuantity); createCell(row, cellCurrent++, style, pcp.getTin()); createCell(row, cellCurrent++, style, pcp.getMfo()); createCell(row, cellCurrent++, style, pcp.getChet()); createCell(row, cellCurrent++, style, pcp.getPaymentNum()); createCell(row, cellCurrent++, style, dateFormat.format(pcp.getPaymentDate())); createCell(row, cellCurrent++, style, pcp.getPaymentSum()); createCell(row, cellCurrent++, style, pcp.getPaymentDetails()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 6); // for payment sum columns createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; } @Override public HSSFWorkbook printRegister(List<PrintRegisterPojo> listPojo) throws Exception { HSSFWorkbook workbook = null; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( servletContext.getRealPath("templates") + File.separator + "print_register.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (PrintRegisterPojo prp : listPojo) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, prp.getInvoiceNum()); createCell(row, cellCurrent++, style, prp.getPaymentNum()); createCell(row, cellCurrent++, style, dateFormat.format(prp.getPaymentDate())); createCell(row, cellCurrent++, style, prp.getTin()); createCell(row, cellCurrent++, style, prp.getName()); createCell(row, cellCurrent++, style, prp.getPaymentSum()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 5); // for payment sum columns createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; } @Override public HSSFWorkbook printClaimRegister(List<PrintClaimRegisterPojo> listPojo) throws Exception { HSSFWorkbook workbook = null; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( servletContext.getRealPath("templates") + File.separator + "print_claim_register.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); // title //HSSFRow row = sheet.getRow(1); //HSSFCell cell = row.getCell(1); //String title; //cell.setCellValue(title); // HSSFRow row; final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum() - 1; final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, rowTotal = sheet.getLastRowNum(), cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } // total style HSSFCellStyle[] totalStyle = new HSSFCellStyle[CELL_END]; short totalStyleHeight = sheet.getRow(rowTotal).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { totalStyle[i] = sheet.getRow(rowTotal).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); int numQuantity = 0; for (PrintClaimRegisterPojo pcrp : listPojo) { numQuantity++; row = sheet.createRow(rowCurrent++); row.setHeight(styleHeight); cellCurrent = CELL_START; createCell(row, cellCurrent++, style, pcrp.getInvoiceNum()); createCell(row, cellCurrent++, style, pcrp.getPaymentNum()); createCell(row, cellCurrent++, style, dateFormat.format(pcrp.getPaymentDate())); createCell(row, cellCurrent++, style, pcrp.getTin()); createCell(row, cellCurrent++, style, pcrp.getName()); createCell(row, cellCurrent++, style, pcrp.getPaymentSum()); } List<Integer> totalCellIndexes = new ArrayList<>(); totalCellIndexes.add(CELL_START + 5); // for payment sum columns createTotalCells(totalCellIndexes, sheet, totalStyle, totalStyleHeight, ROW_START, CELL_START, rowCurrent); return workbook; } @Override public HSSFWorkbook printPaymentManual(BankStatement bs) throws Exception { HSSFWorkbook workbook = null; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( servletContext.getRealPath("templates") + File.separator + "payment_manual.xls")); workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheetAt(0); HSSFRow row = sheet.getRow(0); row.getCell(1).setCellValue(bs.getId()); final int CELL_START = 1; final int ROW_START = sheet.getLastRowNum(); final int CELL_END = sheet.getRow(ROW_START).getLastCellNum(); int rowCurrent = ROW_START, cellCurrent; // HSSFCellStyle[] style = new HSSFCellStyle[CELL_END]; short styleHeight = sheet.getRow(rowCurrent).getHeight(); for (int i = CELL_START; i < CELL_END; i++) { style[i] = sheet.getRow(rowCurrent).getCell(i).getCellStyle(); } SimpleDateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy"); row = sheet.createRow(3); createCell(row, 1, style, ""); createCell(row, 2, style, bs.getMfo()); createCell(row, 3, style, bs.getChet()); createCell(row, 4, style, bs.getPaymentNum()); createCell(row, 5, style, dateFormat.format(bs.getPaymentDate())); createCell(row, 6, style, ""); createCell(row, 7, style, bs.getTin()); return workbook; } }