Java tutorial
package com.webbfontaine.valuewebb.report.fcvrprocessing; /* --------------------------------------------------------------- * Copyrights 2002-2014 Webb Fontaine * * This software is the proprietary information of Webb Fontaine. * Its use is subject to License terms. * Developer : Edgar Harutyunyan * Date: 6/26/14 * --------------------------------------------------------------- */ import com.webbfontaine.valuewebb.model.util.PrintUtils; import com.webbfontaine.valuewebb.model.util.Utils; import com.webbfontaine.valuewebb.report.FcvrProcessingTimeReportBean; import com.webbfontaine.valuewebb.report.ProcessingTime; import com.webbfontaine.valuewebb.report.utils.ReportProcessResult; import com.webbfontaine.valuewebb.report.utils.cellStyles.CellStyleUtils; import com.webbfontaine.valuewebb.report.utils.cellStyles.stylecomponents.CellType; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; class FcvrTimeReportEngine { private static final Logger LOGGER = LoggerFactory.getLogger(FcvrTimeReportEngine.class); private static final int DEFAULT_STARTING_ROW_NUMBER = 2; //no need for instance private FcvrTimeReportEngine() { } public static final ReportProcessResult processReport(String query, FcvrProcessingTimeReportBean reportBean) { try (Connection connection = Utils.getSQLConnection(); PreparedStatement preparedStatement = setPreparedStatementParams(connection.prepareStatement(query), reportBean); ResultSet resultSet = preparedStatement.executeQuery(); InputStream is = getTemplateFileStream()) { resultSet.setFetchSize(1000); HSSFWorkbook workbook = new HSSFWorkbook(is); workbook.setSheetName(0, "Time Processing"); workbook.setSheetName(1, "Containers"); int generatedRows = createRows(workbook, resultSet); if (generatedRows == DEFAULT_STARTING_ROW_NUMBER) { return ReportProcessResult.REPORT_IS_EMPTY; } exportWorkBook(workbook); return ReportProcessResult.PROCESSING_SUCCEED; } catch (SQLException | IOException e) { LOGGER.error("Error occurred during FcvrTimeReport", e); return ReportProcessResult.PROCESSING_FAILED; } } private static int createRows(HSSFWorkbook workbook, ResultSet resultSet) throws SQLException { Sheet sheet1 = workbook.getSheetAt(0); Sheet sheet2 = workbook.getSheetAt(1); int rowNumber = DEFAULT_STARTING_ROW_NUMBER; Map<CellType, CellStyle> cellStyleMap = retrieveNecessaryStyles(workbook); ProcessingTime processingTime = new ProcessingTime(); while (resultSet.next()) { Row sheet1Row = sheet1.createRow(rowNumber); Row sheet2Row = sheet2.createRow(rowNumber); processingTime.calculate(resultSet.getLong("tt_id")); CellStyleUtils.createCell(sheet1Row, 'A', resultSet.getString("fcvr_num"), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'B', resultSet.getString("imp_nam"), cellStyleMap.get(CellType.TEXT_LEFT)); CellStyleUtils.createCell(sheet1Row, 'C', resultSet.getBigDecimal("fob_assessed"), cellStyleMap.get(CellType.NUMBER_RIGHT)); CellStyleUtils.createCell(sheet1Row, 'D', resultSet.getString("inv_cur"), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'E', resultSet.getDate("tt_dat"), cellStyleMap.get(CellType.DATE_CENTER)); CellStyleUtils.createCell(sheet1Row, 'F', resultSet.getDate("fcvr_dat"), cellStyleMap.get(CellType.DATE_CENTER)); CellStyleUtils.createCell(sheet1Row, 'G', resultSet.getInt("num_of_queries"), cellStyleMap.get(CellType.DIGIT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'H', resultSet.getInt("num_of_holidays"), cellStyleMap.get(CellType.DIGIT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'I', resultSet.getInt("num_of_weekends"), cellStyleMap.get(CellType.DIGIT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'J', Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery() - processingTime.getProcessingTimeWithoutQuery()), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'K', Utils.convertProcessingTimeFromLongToString(processingTime.getProcessingTimeWithQuery()), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet1Row, 'L', resultSet.getString("status"), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet2Row, 'A', resultSet.getString("fcvr_num"), cellStyleMap.get(CellType.TEXT_CENTER)); CellStyleUtils.createCell(sheet2Row, 'B', resultSet.getString("containers"), cellStyleMap.get(CellType.TEXT_LEFT)); rowNumber++; } return rowNumber; } private static Map<CellType, CellStyle> retrieveNecessaryStyles(HSSFWorkbook workBook) { Map<CellType, CellStyle> styles = new HashMap<>(); styles.put(CellType.TEXT_CENTER, CellStyleUtils.cellStyle(workBook, CellType.TEXT_CENTER)); styles.put(CellType.TEXT_LEFT, CellStyleUtils.cellStyle(workBook, CellType.TEXT_LEFT)); styles.put(CellType.NUMBER_RIGHT, CellStyleUtils.cellStyle(workBook, CellType.NUMBER_RIGHT)); styles.put(CellType.DATE_CENTER, CellStyleUtils.cellStyle(workBook, CellType.DATE_CENTER)); styles.put(CellType.DIGIT_CENTER, CellStyleUtils.cellStyle(workBook, CellType.DIGIT_CENTER)); return styles; } private static void exportWorkBook(Workbook workbook) throws IOException { try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { workbook.write(outputStream); Utils.sendBytes(outputStream.toByteArray(), "fcvr_processing_time_report.xls", "application/vnd.ms-excel"); } } private static PreparedStatement setPreparedStatementParams(PreparedStatement preparedStatement, FcvrProcessingTimeReportBean queryParameters) throws SQLException { preparedStatement.setTimestamp(1, new java.sql.Timestamp(queryParameters.getDateFrom().getTime())); preparedStatement.setTimestamp(2, new java.sql.Timestamp(queryParameters.getDateTo().getTime())); return preparedStatement; } private static InputStream getTemplateFileStream() { return FcvrTimeReportEngine.class.getResourceAsStream(PrintUtils.getFcvrProcessingTemplateFilePath()); } }