List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle
void setCellStyle(CellStyle style);
Set the style for the cell.
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/*w ww. j ava 2s. c om*/ 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/*from w ww . j a v a2s . 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.TimeKeeperAction.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 2 s . c om*/ 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.hp.amss.util.HyperlinkExample.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);/*from w w w.j av a 2 s. c o m*/ Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //TODO cell.setCellValue(createHelper.createRichTextString("")); cell.setCellType(Cell.CELL_TYPE_STRING); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream("C:\\hyperinks.xlsx"); wb.write(out); out.close(); }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * Excel//w ww . jav a 2 s . c o m * * @param id * ID * @return Excel */ public String getModelExcel(String id) { List<ModelAttribute> list = ServiceManager.getModelService().getModelAttributesByModelId(id); // String[] IGNORATTR = new String[] { "id", "create_time", "contract_start", "searchcode", "last_update_time", "contract_end", "task_link", "order_id", "customer_id", "status", "last_update_by" }; List<String> l = new ArrayList<String>(Arrays.asList(IGNORATTR)); Workbook wb = new HSSFWorkbook(); // Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(id); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setColor(HSSFColor.RED.index); style.setFont(font); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); int i = 0; Cell cell = null; HSSFRichTextString textString; for (ModelAttribute ma : list) { if (l.contains(ma.getAttrId())) continue; cell = row.createCell(i); textString = new HSSFRichTextString(ma.getName() + "/" + ma.getAttrId()); cell.setCellValue(textString); if (!ma.isNullable()) cell.setCellStyle(style); sheet.autoSizeColumn(i); i++; } for (int k = 0; k < list.size(); k++) { sheet.autoSizeColumn(k); } // Write the output to a file FileOutputStream fileOut; String file; try { file = System.getProperty("user.dir") + "/../temp/" + id + new Random().nextLong() + ".xls"; fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return ""; } catch (IOException e) { e.printStackTrace(); return ""; } return file; }
From source file:com.hp.idc.resm.util.ExcelUtil.java
License:Open Source License
/** * , Excel// www. j a va2s . com * * @param modelId * Id * @return Excel */ public String getResouceDataToExcel(String modelId) { Model m = ServiceManager.getModelService().getModelById(modelId); List<ResourceObject> l = ServiceManager.getResourceService().getResourcesByModelId(modelId, 1); List<ModelAttribute> mas = m.getAttributes(); Workbook wb = new HSSFWorkbook(); CellStyle style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 12); font.setFontName(""); style.setFont(font); Sheet sheet = wb.createSheet(modelId); Row row = sheet.createRow(0); int i = 0; HSSFRichTextString textString; for (ModelAttribute ma : mas) { Cell cell = row.createCell(i); textString = new HSSFRichTextString(ma.getDefine().getName()); cell.setCellStyle(style); cell.setCellValue(textString); i++; } i = 1; for (ResourceObject ro : l) { row = sheet.createRow(i); int j = 0; for (ModelAttribute ma : mas) { textString = new HSSFRichTextString(ro.getAttributeValue(ma.getAttrId())); row.createCell(j).setCellValue(textString); j++; } i++; } for (int k = 0; k < mas.size(); k++) { sheet.autoSizeColumn(k); } // Write the output to a file FileOutputStream fileOut; String file; try { file = System.getProperty("user.dir") + "/../temp/" + modelId + new Random().nextLong() + "_data.xls"; fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return ""; } catch (IOException e) { e.printStackTrace(); return ""; } return file; }
From source file:com.ideaspymes.proyecttemplate.stock.web.ProductoConsultaBean.java
@Override public Workbook getWorkBook() { Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("My Sample Excel"); List<CatalogoProductos> lista = (List<CatalogoProductos>) getDetalles(); sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * new Short("6"))); org.apache.poi.ss.usermodel.Font fontTitulo = wb.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); org.apache.poi.ss.usermodel.Font fontTituloPricipal = wb.createFont(); fontTituloPricipal.setFontHeightInPoints((short) 22); fontTituloPricipal.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD); DataFormat format = wb.createDataFormat(); CellStyle styleTituloPrincipal = wb.createCellStyle(); styleTituloPrincipal.setFont(fontTituloPricipal); styleTituloPrincipal.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTituloPrincipal.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleTitulo = wb.createCellStyle(); styleTitulo.setFont(fontTitulo);/*from www . j a v a2 s .c o m*/ styleTitulo.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleTitulo.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); styleTitulo.setFillPattern(CellStyle.SOLID_FOREGROUND); styleTitulo.setWrapText(true); CellStyle styleNumero = wb.createCellStyle(); styleNumero.setDataFormat(format.getFormat("#,##0")); styleNumero.setWrapText(true); styleNumero.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleNumero.setAlignment(CellStyle.ALIGN_CENTER); CellStyle styleFecha = wb.createCellStyle(); styleFecha.setDataFormat(format.getFormat("dd/MM/yyyy")); styleFecha.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleFecha.setAlignment(CellStyle.ALIGN_CENTER); CellStyle style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); CellStyle styleCenter = wb.createCellStyle(); styleCenter.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleCenter.setAlignment(CellStyle.ALIGN_CENTER); styleCenter.setWrapText(true); Row rowTitle = sheet.createRow(0); Cell cellTitle = rowTitle.createCell(1); cellTitle.setCellStyle(styleTituloPrincipal); sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 11 //last column (0-based) )); cellTitle.setCellValue("Listado de Activos"); int i = 2; Row row0 = sheet.createRow(i); row0.setHeight((short) 500); Cell cell1 = row0.createCell(1); cell1.setCellValue("Foto"); cell1.setCellStyle(styleTitulo); Cell cellFecha = row0.createCell(3); cellFecha.setCellValue("Fecha Ingreso"); cellFecha.setCellStyle(styleTitulo); Cell cellFechaCarga = row0.createCell(4); cellFechaCarga.setCellValue("Fecha Carga"); cellFechaCarga.setCellStyle(styleTitulo); Cell cell3 = row0.createCell(5); cell3.setCellValue("Nombre"); cell3.setCellStyle(styleTitulo); Cell cell4 = row0.createCell(6); cell4.setCellValue("Cdigo"); cell4.setCellStyle(styleTitulo); Cell cell5 = row0.createCell(7); cell5.setCellValue("Descripcin"); cell5.setCellStyle(styleTitulo); Cell cell6 = row0.createCell(8); cell6.setCellValue("Es Regalo?"); cell6.setCellStyle(styleTitulo); Cell cell7 = row0.createCell(9); cell7.setCellValue("Familia"); cell7.setCellStyle(styleTitulo); Cell cell8 = row0.createCell(10); cell8.setCellValue("Ubicaciones"); cell8.setCellStyle(styleTitulo); Cell cell9 = row0.createCell(11); cell9.setCellValue("Stock"); cell9.setCellStyle(styleTitulo); for (CatalogoProductos cp : lista) { int indexFila = i + 1; if (cp.getImagen() != null) { int pictureIdx = wb.addPicture(cp.getImagen(), Workbook.PICTURE_TYPE_PNG); CreationHelper helper = wb.getCreationHelper(); //Creates the top-level drawing patriarch. Drawing drawing = sheet.createDrawingPatriarch(); //Create an anchor that is attached to the worksheet ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner for the image anchor.setCol1(1); anchor.setRow1(indexFila); //Creates a picture Picture pict = drawing.createPicture(anchor, pictureIdx); //Reset the image to the original size pict.resize(0.4); } Row row1 = sheet.createRow(indexFila); row1.setHeightInPoints(80f); Cell cellColFecha = row1.createCell(3); if (cp.getFecha() != null) { cellColFecha.setCellValue(cp.getFecha()); cellColFecha.setCellStyle(styleFecha); } else { cellColFecha.setCellValue(""); cellColFecha.setCellStyle(styleFecha); } Cell cellColFechaCarga = row1.createCell(4); if (cp.getFechaCarga() != null) { cellColFechaCarga.setCellValue(cp.getFechaCarga()); cellColFechaCarga.setCellStyle(styleFecha); } else { cellColFechaCarga.setCellValue(""); cellColFechaCarga.setCellStyle(styleFecha); } Cell cellCol1 = row1.createCell(5); cellCol1.setCellValue(cp.getProducto()); cellCol1.setCellStyle(style); Cell cellCol2 = row1.createCell(6); cellCol2.setCellValue(cp.getCodigo()); cellCol2.setCellStyle(styleNumero); Cell cellCol3 = row1.createCell(7); cellCol3.setCellValue(cp.getDescripcion()); cellCol3.setCellStyle(style); Cell cellCol4 = row1.createCell(8); cellCol4.setCellValue(cp.isEsRegalo() ? "SI" : "NO"); cellCol4.setCellStyle(styleCenter); Cell cellCol5 = row1.createCell(9); cellCol5.setCellValue(cp.getFamilia()); cellCol5.setCellStyle(style); Cell cellCol6 = row1.createCell(10); cellCol6.setCellValue(cp.getUbicaciones()); cellCol6.setCellStyle(style); Cell cellCol7 = row1.createCell(11); cellCol7.setCellValue(cp.getStock()); cellCol7.setCellStyle(styleNumero); i++; } sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 0); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 4000); sheet.setColumnWidth(5, 10000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 10000); sheet.setColumnWidth(8, 3500); sheet.setColumnWidth(9, 6000); sheet.setColumnWidth(10, 10000); sheet.setColumnWidth(11, 2000); return wb; }
From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java
License:Open Source License
/** * write group header/*from w w w . j a va2 s . c o m*/ * @param sheet * @param group */ private void writeGroupHeader(Sheet sheet, LdapGroup group, CellStyle style) { Row headerRow = sheet.createRow(index); headerRow.setHeightInPoints(12); Cell cell = headerRow.createCell(STT); cell.setCellValue(group.getName() + " - " + group.getDescription()); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(index, index++, STT, MOBILE)); }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Creates the sheet.//from w ww . ja v a 2 s. c o m * * @param distinctCategoryItems the distinct category items * @throws Exception the exception */ public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception { List<XlsDefItem> xlsDefItems = initHhdrWidthItems(); String sheetName = "DistinctCategoryItems"; Sheet sheet = wb.createSheet(sheetName); processColumnWidths(sheet, xlsDefItems); sheet.createFreezePane(0, 1, 0, 1); // freeze top row sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns sheet.getPrintSetup().setLandscape(true); sheet.setAutobreaks(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 1); int rowCnt = 0; // Header int colCnt = 0; Row rowHdr = sheet.createRow(rowCnt); for (XlsDefItem xlsDefItem : xlsDefItems) { Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING); CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD, cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index, CellBorder.All_Thin, formatGeneral); style.setWrapText(true); cellHdr.setCellStyle(style); cellHdr.setCellValue(xlsDefItem.getName()); colCnt++; } rowCnt++; // Data for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) { Row rowData = sheet.createRow(rowCnt); int colNum = 0; for (XlsDefItem xlsDefItem : xlsDefItems) populateCell(rowData, colNum++, xlsDefItem.getDataType(), getByNameAsString(distinctCategoryItem, xlsDefItem.getName())); rowCnt++; } }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Populate cell./*w ww.j a va 2 s.c om*/ * * @param rowData the row data * @param colCnt the col cnt * @param dataType the data type * @param obj the obj * @throws Exception the exception */ private void populateCell(Row rowData, int colCnt, DataType dataType, Object obj) throws Exception { int cellType = 0; if (dataType == DataType.Numeric) cellType = XSSFCell.CELL_TYPE_NUMERIC; else if (dataType == DataType.NumericDec2) cellType = XSSFCell.CELL_TYPE_NUMERIC; else if (dataType == DataType.Text) cellType = XSSFCell.CELL_TYPE_STRING; else if (dataType == DataType.Date) cellType = XSSFCell.CELL_TYPE_STRING; else if (dataType == DataType.Accounting) cellType = XSSFCell.CELL_TYPE_NUMERIC; else if (dataType == DataType.Percent) cellType = XSSFCell.CELL_TYPE_NUMERIC; Cell cellData = rowData.createCell(colCnt, cellType); short findFormat = -1; if (dataType == DataType.Date) findFormat = formatMmDdYyyy; else if (dataType == DataType.Percent) findFormat = formatPercent; else if (dataType == DataType.Accounting) findFormat = formatAccounting; else if (dataType == DataType.Numeric) findFormat = formatNumeric; else if (dataType == DataType.NumericDec2) findFormat = formatNumericDec2; else findFormat = formatGeneral; CellStyle style = findCellStyle("Arial", HSSFColor.BLACK.index, (short) 11, XSSFFont.BOLDWEIGHT_NORMAL, cellStyleFromDataAlign(findAlignByDataType(dataType)), XSSFCellStyle.VERTICAL_TOP, BG_COLOR_NONE, CellBorder.All_Thin, findFormat); cellData.setCellStyle(style); if (dataType == DataType.Numeric || dataType == DataType.NumericDec2 || dataType == DataType.Accounting || dataType == DataType.Percent) { if (obj == null) ; // leave the cell empty else if (obj instanceof BigDecimal) { BigDecimal value = (BigDecimal) obj; if (value != null) cellData.setCellValue(value.doubleValue()); } else if (obj instanceof Integer) { Integer value = (Integer) obj; if (value != null) cellData.setCellValue(value.intValue()); } else if (obj instanceof Long) { Long value = (Long) obj; if (value != null) cellData.setCellValue(value.longValue()); } else if (obj instanceof Double) { Double value = (Double) obj; if (value != null) cellData.setCellValue(value.doubleValue()); } else if (obj instanceof Short) { Short value = (Short) obj; if (value != null) cellData.setCellValue(value.shortValue()); } else if (obj instanceof String) { String value = (String) obj; if (value != null) cellData.setCellValue(value); } else throw new Exception("Unsupported numeric type: " + obj.getClass().getSimpleName()); } else if (dataType == DataType.Date) { Date date = (Date) obj; if (date != null) cellData.setCellValue(date); } else { cellData.setCellValue((String) obj); } }