List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java
License:Apache License
public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException { FileOutputStream fileOut = new FileOutputStream(outputXls); HSSFWorkbook workbook = new HSSFWorkbook(); try {//from w w w. j a v a 2 s . c om HSSFSheet worksheet = workbook.createSheet("localizations"); HSSFCellStyle systemStyle = workbook.createCellStyle(); systemStyle.setFillForegroundColor(HSSFColor.RED.index); systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Integer currentRow = 0; HSSFRow headLine = worksheet.createRow(++currentRow); headLine.createCell(0).setCellValue("File"); headLine.createCell(1).setCellValue("Property"); headLine.createCell(2).setCellValue("Source Value"); headLine.createCell(3).setCellValue("Excel Value"); HSSFRow row; for (LocalizationLog.Type type : LocalizationLog.Type.values()) { List<LocalizationLog> logs = getLogsByType(differences, type); if (logs.size() > 0) { row = worksheet.createRow(++currentRow); HSSFCell cell = row.createCell(0); cell.setCellValue(LogHelper.getMessageByType(type)); cell.setCellStyle(LogHelper.getStyleByType(workbook, type)); for (int i = 1; i < 4; i++) { row.createCell(i); } CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3); worksheet.addMergedRegion(region); for (LocalizationLog log : logs) { createNewLogRow(worksheet, ++currentRow, log); } row = worksheet.createRow(++currentRow); for (int i = 0; i < 4; i++) { row.createCell(i); } region = new CellRangeAddress(currentRow, currentRow, 0, 3); worksheet.addMergedRegion(region); } } worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3)); worksheet.createFreezePane(0, headLine.getRowNum() + 1); for (int i = 0; i < worksheet.getLastRowNum(); i++) { worksheet.autoSizeColumn(i); } } finally { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Create new merge regions in result sheet identically to range's merge regions from template. * Not support copy of frames and rules/* ww w . ja v a 2s . c o m*/ * * @param resultSheet - result sheet * @param rangeName - range name * @param firstTargetRangeRow - first column of target range * @param firstTargetRangeColumn - first column of target range */ protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow, int firstTargetRangeColumn) { int rangeNameIdx = templateWorkbook.getNameIndex(rangeName); if (rangeNameIdx == -1) return; HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx); AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula()); int column = aref.getFirstCell().getCol(); int row = aref.getFirstCell().getRow(); List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName); if (regionsList != null) for (SheetRange sheetRange : regionsList) { if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) { CellRangeAddress cra = sheetRange.getCellRangeAddress(); if (cra != null) { int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1; int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1; int regionVOffset = cra.getFirstRow() - row; int regionHOffset = cra.getFirstColumn() - column; CellRangeAddress newRegion = cra.copy(); newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn); newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn); newRegion.setFirstRow(regionVOffset + firstTargetRangeRow); newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow); boolean skipRegion = false; for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) { CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex); if (!intersects(newRegion, mergedRegion)) { continue; } skipRegion = true; } if (!skipRegion) { resultSheet.addMergedRegion(newRegion); } } } } }
From source file:com.hp.action.CalculateDistanceAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize// www . j a v a 2 s .co m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA locationDistanceList = (List<LocationDistance>) session.getAttribute("locationDistanceList"); if (locationDistanceList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Qung ?ng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 4 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co Qung ?ng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 4 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < locationDistanceList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(locationDistanceList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoTinhQuangDuong" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.CalculateDistanceAction.java
public String exportExcelDetail() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize/*w w w . jav a 2s. com*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA locationDistanceList = (List<LocationDistance>) session.getAttribute("locationDistanceDetailList"); if (locationDistanceList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDateDetail"); String end = (String) session.getAttribute("endDateDetail"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Qung ?ng chi tit"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co chi tit Qung ?ng "); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArrayDetail()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < locationDistanceList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArrayDetail(locationDistanceList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoTinhChiTietQuangDuong" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.CalendarAction.java
public String exportCalendarList() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize// w w w . j a v a 2 s . c o m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA calendarList = (List<Calendar>) session.getAttribute("calendarList"); if (calendarList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("K hoch cng tc"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 8 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("K hoch cng tc"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 8 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < calendarList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(calendarList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoLichCongTac" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.CustomerAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize// ww w . ja v a 2s . co m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA customersList = (List<Customer>) session.getAttribute("customersList"); if (customersList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Khch hng"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Danh sch khch hng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); cell1.setCellValue(""); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < customersList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(customersList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "DanhSachKhachHang.xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.ForLeaveAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize/*from w w w .j a v a 2 s. c om*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA forLeaveList = (List<ForLeave>) session.getAttribute("forLeaveList"); if (forLeaveList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Ngh php"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co ngh php"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < forLeaveList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(forLeaveList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoNghiPhep" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.InterruptedTimeAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize//w w w .j av a 2s .c o m if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA interruptedTimeList = (List<InterruptedTime>) session.getAttribute("interruptedTimeList"); if (interruptedTimeList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Mt tn hiu"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co mt tn hiu"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < interruptedTimeList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(interruptedTimeList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "BaoCaoMatTinHieu" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.ProductsAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize/*from w w w. j a va 2 s .c om*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA productsList = (List<Product>) session.getAttribute("productsList"); if (productsList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Sn phm"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //TakeOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Danh sch sn phm"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 12 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); cell1.setCellValue(""); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < productsList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(productsList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } } outputFile = "DanhSachSanPham.xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.ReportSaleOrderAction.java
public String exportSaleOrderList() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize/*from ww w . j a v a2s .c o m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA saleOrdersList = (List<SaleOrder>) session.getAttribute("saleOrdersList"); saleOrderDetailList = (List<List<SaleOrderDetail>>) session.getAttribute("saleOrderDetailList"); String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); String start = (String) session.getAttribute("startDate"); String end = (String) session.getAttribute("endDate"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Sale Order"); //sheet.autoSizeColumn(200); sheet.setColumnWidth(0, 1000); sheet.setDefaultColumnWidth(20); //SaleOrder title for (int i = 1; i < 2; i++) { // Row rowstart = sheet.createRow(0); //Row Title Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell0 = row0.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i, //first row (0-based) i, //last row (0-based) 0, //first column (0-based) 8 //last column (0-based) )); //CellUtil.setAlignment(cell0, workBook, CellStyle.ALIGN_CENTER); CellStyle cellStyle = workBook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); //font Font headerFont = workBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeight((short) 250); cellStyle.setFont(headerFont); cell0.setCellStyle(cellStyle); cell0.setCellValue("Bo co ha n bn hng"); //Row date Row row1 = sheet.createRow(i + 1); //row1.setHeight((short)500); Cell cell1 = row1.createCell(0); //Merge for title sheet.addMergedRegion(new CellRangeAddress(i + 1, //first row (0-based) i + 1, //last row (0-based) 0, //first column (0-based) 8 //last column (0-based) )); CellStyle cellAlign = workBook.createCellStyle(); cellAlign.setAlignment(CellStyle.ALIGN_CENTER); cell1.setCellStyle(cellAlign); if (start == null) start = ""; if (end == null) end = ""; cell1.setCellValue("T ngy: " + start + " - ?n ngy: " + end); //Row Header Row row = sheet.createRow(4); int cellnum = 0; for (Object obj : titleArray()) { Cell cell = row.createCell(cellnum++); CellStyle style = workBook.createCellStyle(); style.setFillForegroundColor(HSSFColor.YELLOW.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write SaleOrder for (int i = 0; i < saleOrdersList.size(); i++) { Row row = sheet.createRow(i + 5); int cellnum = 0; //Cell 0 - stt Cell cell0 = row.createCell(cellnum++); cell0.setCellValue(i + 1); //Set content for (Object obj : objectArray(saleOrdersList.get(i), saleOrderDetailList.get(i))) { Cell cell = row.createCell(cellnum++); if (obj instanceof Timestamp) cell.setCellValue((Timestamp) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Float) { // CellStyle cellStyle = workBook.createCellStyle(); // DataFormat format = workBook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("#.#")); // cell.setCellStyle(cellStyle); cell.setCellValue((Float) obj); } else if (obj instanceof Double) cell.setCellValue((Double) obj); } //SUM ROW if (i == (saleOrdersList.size() - 1)) { Row rowEnd = sheet.createRow(i + 7); for (int j = (titleArray().length - 5); j < (titleArray().length - 1); j++) { Cell cell = rowEnd.createCell(j); cell.setCellValue((Double) sumArray()[j - (titleArray().length - 5)]); } } } outputFile = "BaoCaoHoaDonBanHang" + start + " - " + end + ".xls"; try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\" + outputFile)); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\" + outputFile)); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }