List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet(String sheetname)
From source file:com.hp.action.ReportTakeOrderAction.java
public String exportTakeOrderList() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize// w w w. java 2 s . c om if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA takeOrdersList = (List<TakeOrder>) session.getAttribute("takeOrdersList"); takeOrderDetailList = (List<List<TakeOrderDetail>>) session.getAttribute("takeOrderDetailList"); if (takeOrdersList == 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("Take Order"); //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("Bo co ha n t 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 TakeOrder for (int i = 0; i < takeOrdersList.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(takeOrdersList.get(i), takeOrderDetailList.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 == (takeOrdersList.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 = "BaoCaoHoaDonDatHang" + 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.SetLunchAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); user = (User) session.getAttribute("USER"); //Authorize/* w ww .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 setLunchList = (List<SetLunch>) session.getAttribute("setLunchList"); if (setLunchList == 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("Bo cm"); //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 cm vn phng"); //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 < setLunchList.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(setLunchList.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 = "BaoComVanPhong" + 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.StaffHistoryAction.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 av a 2s. co m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA listStaffHistory = (List<StaffHistory>) session.getAttribute("listStaffHistory"); if (listStaffHistory == 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("Chm sc 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) 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 chm sc 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) 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 < listStaffHistory.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(listStaffHistory.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 = "BaoCaoChamSocKhachHang" + 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.StaffsAction.java
public String exportExcel() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize// www .j av a 2 s . c om if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA staffsList = (List<Staff>) session.getAttribute("staffsList"); if (staffsList == null) return INPUT; String fileInput = ServletActionContext.getServletContext().getRealPath("/db_exports/"); // //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Nhn vin"); //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) 10 //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 nhn vin"); //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) 10 //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 < staffsList.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(staffsList.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 = "DanhSachNhanVien.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.TakeOrderAction.java
public String exportOrders() { HttpServletRequest request = (HttpServletRequest) ActionContext.getContext() .get(ServletActionContext.HTTP_REQUEST); HttpSession session = request.getSession(); //Authorize/*from w ww.jav a2s.com*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } staffList = staffDAO.getListUser(null); String fileInput = ServletActionContext.getServletContext().getRealPath("/database/"); System.out.println("Result: " + staff + " from date: " + fromDate + " , to date: " + toDate); takeOrdersList = takeOrderDAO.getTakeOrdersList(staff, fromDate, toDate); if (takeOrdersList.isEmpty()) return INPUT; Map<String, List<TakeOrderDetail>> data = new HashMap<String, List<TakeOrderDetail>>(); for (int i = 0; i < takeOrdersList.size(); i++) { data.put(i + "", takeOrderDetailDAO.getDetailTakeOrdersList(takeOrdersList.get(i).getId())); } int line = 0; Set<String> keyset = data.keySet(); for (String key : keyset) { line += data.get(key).size(); } line += keyset.size(); //Write HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet("Order"); //TakeOrder title for (int i = 0; i < 1; i++) { Row row = sheet.createRow(i); int cellnum = 0; for (Object obj : takeOrdersList.get(i).toTitleArray()) { 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 String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrder for (int i = 0; i < takeOrdersList.size(); i++) { Row row = sheet.createRow(i + 1); int cellnum = 0; for (Object obj : takeOrdersList.get(i).toArray()) { 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 String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); } } //Write TakeOrderDetail title for (String key : keyset) { for (int i = 0; i < 1; i++) { Row row = sheet.createRow(i + takeOrdersList.size() + 3); int cellnum = 0; for (Object obj : data.get(key).get(i).toTitleArray()) { 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 String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } } //Write TakeOrderDetail for (String key : keyset) { for (int i = 0; i < data.get(key).size(); i++) { Row row = sheet.createRow(i + takeOrdersList.size() + 4); int cellnum = 0; for (Object obj : data.get(key).get(i).toArray()) { 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 String) cell.setCellValue((String) obj); else if (obj instanceof Float) cell.setCellValue((Float) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } } } //Write to xls try { FileOutputStream output = new FileOutputStream(new File(fileInput + "\\test.xls")); workBook.write(output); output.close(); System.out.println("Excel written successfully.."); orderFile = new FileInputStream(new File(fileInput + "\\test.xls")); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:com.hp.action.TimeKeeperAction.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 a v a2 s. c o m*/ if (!userDAO.authorize((String) session.getAttribute("user_name"), (String) session.getAttribute("user_password"))) { return LOGIN; } //GET DATA timeKeeperList = (List<TimeKeeper>) session.getAttribute("timeKeeperList"); if (timeKeeperList == 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("Chm cng"); //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 chm cng"); //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 < timeKeeperList.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(timeKeeperList.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 = "BaoCaoChamCong" + 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.hris.payroll.thirteenthmonth.ExportDataGridToExcel.java
public void workSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); int rownum = 1; for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { System.out.println("itemId: " + itemId); }/*from www . j a v a 2 s .c o m*/ for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { Row row = sheet.createRow(rownum); int cellcount = 0; if ((rownum - 1) == 0) { Row rowHeader = sheet.createRow(rownum - 1); for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) { Cell cell = rowHeader.createCell(cellcount); if (propertyId.toString().contains("salary")) { if (!propertyId.toString().equals("salary grand total")) { cell.setCellValue(propertyId.toString().replace("salary", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("tax")) { if (!propertyId.toString().equals("tax grand total")) { cell.setCellValue(propertyId.toString().replace("tax", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("sss")) { if (!propertyId.toString().equals("sss grand total")) { cell.setCellValue(propertyId.toString().replace("sss", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("phic")) { if (!propertyId.toString().equals("phic grand total")) { cell.setCellValue(propertyId.toString().replace("phic", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("hdmf")) { if (!propertyId.toString().equals("hdmf grand total")) { cell.setCellValue(propertyId.toString().replace("hdmf", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else { cell.setCellValue(propertyId.toString().toUpperCase()); } sheet.autoSizeColumn(cellcount); cellcount++; } } Item item = getDataGrid().getContainerDataSource().getItem(itemId); int cellnum = 0; for (Object propertyId : item.getItemPropertyIds()) { Cell cell = row.createCell(cellnum); if (propertyId.equals("employee")) { cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase()); } else { cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " " : item.getItemProperty(propertyId).getValue().toString()); } sheet.autoSizeColumn(cellnum); cellnum++; } rownum++; } FileOutputStream fos; try { Date date = new Date(); path = "C:/payroll-files/format-" + date.getTime() + ".xls"; file = new File(path); fos = new FileOutputStream(path); workbook.write(fos); fos.flush(); fos.close(); } catch (FileNotFoundException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } StreamResource.StreamSource source = () -> { try { File f = new File(path); FileInputStream fis = new FileInputStream(f); return fis; } catch (Exception e) { e.getMessage(); return null; } }; StreamResource resource = new StreamResource(source, "ThirteenthMonth.xls"); resource.setMIMEType("application/vnd.ms-office"); }
From source file:com.huawei.gsm.util.response.SiteExcelBuilder.java
@Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook hssfw, HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception { List<SiteExcelWrapper> data = (List<SiteExcelWrapper>) map.get("exportData"); HSSFSheet sheet = hssfw.createSheet("SITES"); int rowSize = data.size() + 1; int columnSize = 9; String[] columns = new String[] { "Site ID", "Site Name", "Address", "latitude", "longitude", "Site Group", "Cell Index", "Cell Name", "Frequency" }; Row headerRow = sheet.createRow(0);//from ww w . j a v a 2s. c o m for (int i = 0; i < columns.length; i++) { String column = columns[i]; headerRow.createCell(i).setCellValue(column); } for (int i = 1; i <= data.size(); i++) { Row row = sheet.createRow(i); row.createCell(0).setCellValue(data.get((i - 1)).getSiteId()); row.createCell(1).setCellValue(data.get((i - 1)).getSiteName()); row.createCell(2).setCellValue(data.get((i - 1)).getAddress()); row.createCell(3).setCellValue(data.get((i - 1)).getLatitute()); row.createCell(4).setCellValue(data.get((i - 1)).getLongitude()); row.createCell(5).setCellValue(data.get((i - 1)).getSiteGroup()); row.createCell(6).setCellValue(data.get((i - 1)).getCellIndex()); row.createCell(7).setCellValue(data.get((i - 1)).getCellName()); row.createCell(8).setCellValue(data.get((i - 1)).getFrequency()); } }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertExpiredScanReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "EXP_SCAN").getSingleResult(); StringBuffer dataQuery = new StringBuffer( "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, "); dataQuery.append(/*from ww w. j a v a2 s .co m*/ "SL.Name, SL.Bios_Serial, VA.Creation_Time, VA.Alert_Age, MT.Type, HL.OS_Type, VA.Remote_User, VA.Comments, ") .append("VA.Record_Time , AC.name as ac_name, CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time, ") .append("CC.remote_user as cc_remote_user, CC.id as cc_id ").append("FROM EAADMIN.V_Alerts VA ") .append("JOIN EAADMIN.Software_Lpar SL ON SL.Id = VA.FK_Id ") .append("JOIN EAADMIN.cause_code CC ON (VA.id = CC.alert_id AND CC.alert_type_id = :alertTypeId) ") .append("JOIN EAADMIN.alert_cause AC ON CC.alert_cause_id = AC.id ") .append("LEFT OUTER JOIN EAADMIN.hw_sw_composite HSC on HSC.software_lpar_id = SL.id ") .append("LEFT OUTER JOIN EAADMIN.hardware_lpar HL on HL.id = HSC.hardware_lpar_id ") .append("LEFT OUTER JOIN EAADMIN.hardware H on H.id = HL.hardware_id ") .append("LEFT OUTER JOIN EAADMIN.machine_type MT on MT.id = H.machine_type_id ") .append("WHERE VA.Customer_Id = :customerId AND VA.Type = :type AND VA.Open = 1 ") .append("ORDER BY SL.Name ASC"); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()) .createSQLQuery(dataQuery.toString()).setLong("customerId", pAccount.getId()) .setString("type", "EXPIRED_SCAN").setInteger("alertTypeId", alertType.getId().intValue()) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert Unexpired SW Lpar " + pAccount.getAccount() + " Report"); printHeader(ALERT_EXPIRED_SCAN_REPORT_NAME, pAccount.getAccount(), ALERT_EXPIRED_SCAN_REPORT_COLUMN_HEADERS, sheet); int i = 3; while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert Unexpired SW Lpar " + pAccount.getAccount() + " Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getAlertHardwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb, OutputStream pOutputStream) throws HibernateException, Exception { AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode") .setParameter("code", "HW_LPAR").getSingleResult(); ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, HL.Name AS HL_Name, H.Serial, MT.Name AS MT_Name, VA.Creation_Time, VA.Alert_Age, MT.Type, HL.OS_Type, VA.Remote_User, VA.Comments, VA.Record_Time, AC.name as ac_name, CC.target_date, CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware_Lpar HL, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE_LPAR' AND VA.Open = 1 AND HL.Id = VA.FK_Id AND H.Id = HL.Hardware_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY HL.Name ASC") .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue()) .scroll(ScrollMode.FORWARD_ONLY); HSSFSheet sheet = phwb.createSheet("Alert HwLPAR Report"); printHeader(ALERT_HARDWARE_LPAR_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_LPAR_REPORT_COLUMN_HEADERS, sheet); int i = 3;/*from ww w.j a v a 2 s .co m*/ while (lsrReport.next()) { int k = 1; if (i > 65535) { k++; sheet = phwb.createSheet("Alert HWLpar Report Sheet" + k); i = 1; } HSSFRow row = sheet.createRow((int) i); outputData(lsrReport.get(), row); i++; } @SuppressWarnings("unchecked") Iterator<Object[]> vCauseCodeSummary = getEntityManager() .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId()) .getResultList().iterator(); HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes"); HSSFRow rowhead0 = sheet_2.createRow((int) 0); outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0); int j = 1; while (vCauseCodeSummary.hasNext()) { HSSFRow row = sheet_2.createRow((int) j); outputData(vCauseCodeSummary.next(), row); j++; } phwb.write(pOutputStream); }