List of usage examples for org.apache.poi.hssf.usermodel HSSFDataFormat getBuiltinFormat
public static String getBuiltinFormat(short index)
From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadsheetCell.java
/** * Get cell format<p>//from ww w . j av a 2s . c o m * * Thanks to http://stackoverflow.com/questions/15248284/using-poi-how-to-set-the-cell-type-as-number * @param wb * @return */ public HSSFCellStyle getCellStyle(HSSFWorkbook wb) { //EX1 if (sheet.containsStyleId(styleId)) { return sheet.getStyle(styleId); } HSSFCellStyle style = wb.createCellStyle(); Integer clazzX = clazz != null ? clazz : (headerCell != null ? headerCell.clazz : null); switch (clazzX != null ? clazzX : CLASS_STRING) { case CLASS_DATE: if (!isHeader()) { CreationHelper createHelper = wb.getCreationHelper(); style = wb.createCellStyle(); style.setDataFormat(createHelper.createDataFormat() .getFormat(sheet.getDateFormat() != null ? sheet.getDateFormat() : "m/d/yy")); } style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_PERCENTAGE: style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("0.00%")); style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); break; case CLASS_INTEGER: case CLASS_LONG: style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0")); break; case CLASS_DOUBLE: case CLASS_UKURS: style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); break; case CLASS_STRING: case CLASS_INTEGER_LEFT: case CLASS_BOOLEAN: case CLASS_CHARACTER: default: style.setAlignment(HSSFCellStyle.ALIGN_LEFT); } sheet.setCellStyle(wb, style, this, styleId); return style; }
From source file:com.simeosoft.util.XlsUtils.java
License:Open Source License
public static void addXlsWorksheet(HSSFWorkbook wb, String sheetName, ArrayList<ArrayList<Object>> data) { HSSFSheet s = wb.createSheet(sheetName); HSSFRow r = null;//from ww w . j a v a2s. co m HSSFCell c = null; int i = 0; HSSFDataFormat df = wb.createDataFormat(); HSSFCellStyle cs = wb.createCellStyle(); for (ArrayList<Object> record : data) { r = s.createRow(i); i++; short y = 0; /* * tipi dato previsti: null,String,Date,Integer,Time,Timestamp * - gli altri tipi restituiscono errore */ for (Object obj : record) { c = r.createCell(y); y++; if (obj == null) { c.setCellValue(""); continue; } if (obj instanceof String) { c.setCellValue((String) obj); continue; } // MODIF - java.util.Date o java.sql.Date? if (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp || obj instanceof java.sql.Time) { HSSFCellStyle csd = wb.createCellStyle(); csd.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy")); c.setCellValue((java.sql.Date) obj); c.setCellStyle(csd); continue; } if (obj instanceof Integer) { c.setCellValue(((Integer) obj).doubleValue()); continue; } if (obj instanceof BigDecimal) { c.setCellValue(((BigDecimal) obj).doubleValue()); cs.setDataFormat(df.getFormat("######0.0000")); c.setCellStyle(cs); continue; } // default - non previsto c.setCellValue("ERRORE: TIPO NON PREVISTO: " + obj.getClass()); } } }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) { try {/*from w ww . jav a 2 s. c om*/ String formatV = ""; FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem); HSSFSheet worksheet = workbook.getSheet(sheetName); if (worksheet == null) { worksheet = workbook.createSheet(sheetName); } // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow(row); for (int col = 0; col < value.length; col++) { HSSFCell cellA1 = row1.createCell(col); cellA1.setCellValue(value[col]); if (format.length >= col) { HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col])); //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellA1.setCellStyle(cellStyle); } } FileOutputStream fileOut = new FileOutputStream(this.fileName); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName + " with Excel Row =" + row + " " + e.getMessage()); e.printStackTrace(); } catch (IOException e) { WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName + " with Excel Row =" + row + " " + e.getMessage()); e.printStackTrace(); } }
From source file:Compras.generaCotizacion.java
void exel() { h = new Herramientas(this.user, 0); h.session(sessionPrograma);/* w ww . j a v a 2s.co m*/ javax.swing.JFileChooser jF1 = new javax.swing.JFileChooser(); jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null; if (jF1.showSaveDialog(null) == jF1.APPROVE_OPTION) { ruta = jF1.getSelectedFile().getAbsolutePath(); if (ruta != null) { if (ruta.endsWith(".xls") == true) archivoXLS = new File(ruta); else archivoXLS = new File(ruta + ".xls"); try { if (archivoXLS.exists()) archivoXLS.delete(); archivoXLS.createNewFile(); Workbook libro = new HSSFWorkbook(); FileOutputStream archivo = new FileOutputStream(archivoXLS); Sheet hoja = libro.createSheet("Cotizacion"); Font font = libro.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Arial"); font.setItalic(false); font.setBold(true); Font font10 = libro.createFont(); font10.setFontHeightInPoints((short) 10); font10.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font10.setColor(new HSSFColor.YELLOW().getIndex()); Font font11 = libro.createFont(); font11.setFontHeightInPoints((short) 10); font11.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font11.setColor(new HSSFColor.BLACK().getIndex()); // Fonts are set into a style so create a new one to use. CellStyle style = libro.createCellStyle(); CellStyle desBloqueo = libro.createCellStyle(); CellStyle desBloqueo1 = libro.createCellStyle(); CellStyle desBloqueoFecha = libro.createCellStyle(); style.setFont(font); desBloqueo.setFont(font10); desBloqueo.setLocked(false); desBloqueo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); desBloqueo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); desBloqueo1.setFont(font11); desBloqueo1.setLocked(false); desBloqueo1.setFillBackgroundColor(new HSSFColor.WHITE().getIndex()); desBloqueoFecha.setFont(font10); desBloqueoFecha.setLocked(false); desBloqueoFecha.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); desBloqueoFecha.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); desBloqueoFecha.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); Session session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction().begin(); Orden ord = (Orden) session.get(Orden.class, Integer.parseInt(orden)); Configuracion con = (Configuracion) session.get(Configuracion.class, 1); hoja.setColumnWidth(5, 15000); Row r0 = hoja.createRow(0); Cell celdaTitulo = r0.createCell(0); celdaTitulo.setCellValue(con.getEmpresa()); celdaTitulo.setCellStyle(style); Row r1 = hoja.createRow(1); r1.createCell(6).setCellValue("Orden de Taller:"); r1.createCell(7).setCellValue(orden); Row r2 = hoja.createRow(2); r2.createCell(0).setCellValue("Marca:"); r2.createCell(1).setCellValue(ord.getMarca().getMarcaNombre()); r2.createCell(6).setCellValue("N Serie:"); r2.createCell(7).setCellValue(ord.getNoSerie()); Row r3 = hoja.createRow(3); r3.createCell(0).setCellValue("Tipo:"); r3.createCell(1).setCellValue(ord.getTipo().getTipoNombre()); r3.createCell(6).setCellValue("N Motor:"); r3.createCell(7).setCellValue(ord.getNoMotor()); Row r4 = hoja.createRow(4); r4.createCell(0).setCellValue("NP:"); r4.createCell(1).setCellValue(t_datos1.getValueAt(t_datos1.getSelectedRow(), 1).toString()); r4.createCell(2).setCellValue("Proveedor:"); r4.createCell(3).setCellValue(t_datos1.getValueAt(t_datos1.getSelectedRow(), 2).toString()); r4.createCell(6).setCellValue("Modelo:"); r4.createCell(7).setCellValue("" + ord.getModelo()); Row r5 = hoja.createRow(5); r5.createCell(0).setCellValue( "**********************************************************************************[Nota: Solo puedes editar las celdas de color]*******************************************************************************"); Row r6 = hoja.createRow(6); r6.createCell(0).setCellValue("Partida"); r6.createCell(1).setCellValue("sub"); r6.createCell(2).setCellValue("Cantidad"); r6.createCell(3).setCellValue("U/Medida"); r6.createCell(4).setCellValue("N Parte"); r6.createCell(5).setCellValue("Descripcion"); r6.createCell(6).setCellValue("Instruccin"); r6.createCell(7).setCellValue("Precio c/u"); r6.createCell(8).setCellValue("T o t a l"); r6.createCell(9).setCellValue("Origen"); r6.createCell(10).setCellValue("Pazo"); Row r7 = hoja.createRow(7); r7.createCell(0).setCellValue( "**********************************************************************************************************************************************************************************************************************"); List misCotizaciones = null; Query query = session.createQuery("SELECT DISTINCT par FROM Partida par " + "RIGHT JOIN FETCH par.partidaCotizacions partC " + "RIGHT JOIN partC.cotizacion cot " + "where cot.idCotizacion=" + t_datos1.getValueAt(t_datos1.getSelectedRow(), 0).toString() + " order by par.idEvaluacion asc, par.subPartida asc"); //misCotizaciones=c.addOrder(Order.asc("idCotizacion")).list(); misCotizaciones = query.list(); if (misCotizaciones.size() > 0) { for (int i = 0; i < misCotizaciones.size(); i++) { Partida Part = (Partida) misCotizaciones.get(i); Row fila = hoja.createRow(i + 8); fila.createCell(0).setCellValue(Part.getIdEvaluacion()); fila.createCell(1).setCellValue(Part.getSubPartida()); fila.createCell(2).setCellValue(Part.getCant()); fila.createCell(3).setCellValue(Part.getMed()); Cell aux = fila.createCell(4); aux.setCellStyle(desBloqueo); if (Part.getEjemplar() != null) aux.setCellValue(Part.getEjemplar().getIdParte()); else aux.setCellValue(""); fila.createCell(5).setCellValue(Part.getCatalogo().getNombre()); int fil = i + 9; Cell a10 = fila.createCell(6); a10.setCellStyle(desBloqueo1); a10.setCellValue(Part.getInstruccion()); Cell a6 = fila.createCell(7); a6.setCellStyle(desBloqueo); a6.setCellValue(""); Cell celForm = fila.createCell(8); celForm.setCellType(HSSFCell.CELL_TYPE_FORMULA); celForm.setCellFormula("H" + fil + "*C" + fil); if (Part.isOri() == true) fila.createCell(9).setCellValue("Ori"); else if (Part.isNal() == true) fila.createCell(9).setCellValue("Nal"); else if (Part.isDesm() == true) fila.createCell(9).setCellValue("Des"); else fila.createCell(9).setCellValue(""); Cell a9 = fila.createCell(10); a9.setCellValue(""); a9.setCellStyle(desBloqueo); } } hoja.protectSheet("04650077"); libro.write(archivo); archivo.close(); Desktop.getDesktop().open(archivoXLS); if (session != null) if (session.isOpen()) session.close(); } catch (Exception e) { System.out.println(e); e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto"); } } } }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelImportServiceAttributesIntegrationTest.java
License:Open Source License
@Test public void testImportDateAttribute() { // create attribute type and sample building block AttributeTypeGroup atg = testDataHelper.createAttributeTypeGroup("myTAG", ""); DateAT dateType = testDataHelper.createDateAttributeType("MyDateType", "", atg); testDataHelper.assignAttributeTypeToAllAvailableBuildingBlockTypes(dateType); InformationSystem is = testDataHelper.createInformationSystem("myIS"); InformationSystemRelease isr = testDataHelper.createInformationSystemRelease(is, "1.0"); // create excel cells with import data final HSSFWorkbook workbook = new HSSFWorkbook(); final CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); final HSSFSheet sheet = workbook.createSheet(); final HSSFRow row = sheet.createRow(0); final Cell cell1 = row.createCell(0); cell1.setCellValue(createDate(2010, 12, 31)); cell1.setCellStyle(dateStyle); // need to do this so our import routines can parse it back as a date value Map<String, Cell> attributes = new HashMap<String, Cell>(); attributes.put(dateType.getName(), cell1); LandscapeData landscapeData = new LandscapeData(); landscapeData.addAttributes(isr, attributes); landscapeData.setLocale(Locale.GERMAN); excelImportService.importLandscapeData(landscapeData); InformationSystemRelease isrLoadedFromDb = isrService.loadObjectById(isr.getId()); String loadedAv = isrLoadedFromDb.getAttributeValue(dateType.getName(), Locale.GERMAN); assertEquals("31.12.2010", loadedAv); // following call failed before ITERAPLAN-170 was fixed @SuppressWarnings("unused") AttributeType at = attributeTypeService.getAttributeTypeByName(dateType.getName()); }
From source file:de.jwic.ecolib.tableviewer.export.ExcelExportControl.java
License:Apache License
private HSSFWorkbook createWorkBook() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet"); HSSFRow row = sheet.createRow(0);/* ww w . j ava2 s . co m*/ // Style for title cells HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLUE.index); HSSFCellStyle styleTitle = wb.createCellStyle(); styleTitle.setFont(font); // Style for data date cells font = wb.createFont(); HSSFCellStyle styleDate = wb.createCellStyle(); styleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); short col = 0; TableModel model = tableViewer.getModel(); Iterator<TableColumn> it = model.getColumnIterator(); // create title in the sheet while (it.hasNext()) { TableColumn column = it.next(); if (!isColumnVisible(column)) { continue; } sheet.setColumnWidth(col, (short) (column.getWidth() * 40)); HSSFCell cell = row.createCell(col++); cell.setCellValue(column.getTitle()); cell.setCellStyle(styleTitle); } // add the datas from the table viewer IContentProvider<?> contentProvider = model.getContentProvider(); Iterator<?> iter = contentProvider.getContentIterator(new Range()); try { renderRows(iter, 0, model, sheet, styleDate); } catch (Throwable t) { log.error("Error rendering rows", t); } return wb; }
From source file:de.powerstaff.web.backingbean.profile.ProfileBackingBean.java
License:Open Source License
public void commandSearchExportExcel() { try {/*from ww w . j a v a 2 s. c o m*/ FacesContext theContext = FacesContext.getCurrentInstance(); ExternalContext externalContext = theContext.getExternalContext(); HttpServletResponse response = (HttpServletResponse) externalContext.getResponse(); response.reset(); // Some JSF component library or some Filter might have set some headers in the buffer beforehand. We want to get rid of them, else it may collide. response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=\"ExportSuche.xls\""); HSSFWorkbook theWorkbook = new HSSFWorkbook(); HSSFSheet theWorkSheet = theWorkbook.createSheet("ExportSuche"); HSSFCellStyle theDateStyle = theWorkbook.createCellStyle(); theDateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("d/m/jj")); int aRow = 0; // Header HSSFRow theRow = theWorkSheet.createRow(aRow++); ExcelUtils.addCellToRow(theRow, 0, "Anrede"); ExcelUtils.addCellToRow(theRow, 1, "Name1"); ExcelUtils.addCellToRow(theRow, 2, "Name2"); ExcelUtils.addCellToRow(theRow, 3, "eMail"); ExcelUtils.addCellToRow(theRow, 4, "Code"); ExcelUtils.addCellToRow(theRow, 5, "Verfgbarkeit"); ExcelUtils.addCellToRow(theRow, 6, "Satz"); ExcelUtils.addCellToRow(theRow, 7, "Plz"); ExcelUtils.addCellToRow(theRow, 8, "Letzter Kontakt"); ExcelUtils.addCellToRow(theRow, 9, "Skills"); ExcelUtils.addCellToRow(theRow, 10, "Tags"); // Rows PagedListDataModel<ProfileSearchEntry> theData = getData().getSearchResult(); for (int i = 0; i < theData.getRowCount() && i < profileSearchService.getPageSize(); i++) { theData.setRowIndex(i); ProfileSearchEntry theDataRow = (ProfileSearchEntry) theData.getRowData(); Freelancer theFreelancer = freelancerService.findByPrimaryKey(theDataRow.getFreelancer().getId()); String theSkills = ExcelUtils.saveObject( theFreelancer.getSkills().replace("\f", "").replace("\n", "").replace("\t", "")); StringBuilder theTagList = new StringBuilder(); for (FreelancerToTag theTagAssignment : theFreelancer.getTags()) { if (theTagList.length() > 0) { theTagList.append(" "); } theTagList.append(theTagAssignment.getTag().getName()); } HSSFRow theFreelancerRow = theWorkSheet.createRow(aRow++); ExcelUtils.addCellToRow(theFreelancerRow, 0, ExcelUtils.saveObject(theFreelancer.getTitel())); ExcelUtils.addCellToRow(theFreelancerRow, 1, ExcelUtils.saveObject(theFreelancer.getName1())); ExcelUtils.addCellToRow(theFreelancerRow, 2, ExcelUtils.saveObject(theFreelancer.getName2())); ExcelUtils.addCellToRow(theFreelancerRow, 3, ExcelUtils.saveObject(theFreelancer.getFirstContactEMail())); // eMail ExcelUtils.addCellToRow(theFreelancerRow, 4, ExcelUtils.saveObject(theFreelancer.getCode())); ExcelUtils.addCellToRow(theFreelancerRow, 5, ExcelUtils.saveObject(theFreelancer.getAvailabilityAsDate()), theDateStyle); ExcelUtils.addCellToRow(theFreelancerRow, 6, ExcelUtils.saveObject(theFreelancer.getSallaryLong())); ExcelUtils.addCellToRow(theFreelancerRow, 7, ExcelUtils.saveObject(theFreelancer.getPlz())); ExcelUtils.addCellToRow(theFreelancerRow, 8, ExcelUtils.saveObject(theFreelancer.getLastContactDate()), theDateStyle); ExcelUtils.addCellToRow(theFreelancerRow, 9, ExcelUtils.saveObject(theSkills)); ExcelUtils.addCellToRow(theFreelancerRow, 10, theTagList.toString()); } theWorkbook.write(response.getOutputStream()); theContext.responseComplete(); // Important! } catch (Exception e) { JSFMessageUtils.addGlobalErrorMessage(MSG_FEHLERBEIDERPROFILSUCHE, e.getMessage()); LOGGER.error("Fehler bei Profilsuche", e); } }
From source file:demo.poi.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;/*from w ww . j a v a 2 s . c om*/ // create a new file FileOutputStream out = new FileOutputStream("target/bigworkbook.xls"); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet(); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it red f.setColor(HSSFColor.RED.index); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it the color at palette index 0xf (white) f2.setColor(HSSFColor.WHITE.index); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); //set cell stlye cs.setFont(f); //set the cell format see HSSFDataFromat for a full list cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); //set a thin border cs2.setBorderBottom(CellStyle.BORDER_THIN); //fill w fg fill color cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set foreground fill to red cs2.setFillForegroundColor(HSSFColor.RED.index); // set the font cs2.setFont(f2); // set the sheet name to HSSF Test wb.setSheetName(0, "HSSF Test"); // create a sheet with 300 rows (0-299) for (rownum = 0; rownum < 300; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 50 cells (0-49) (the += 2 becomes apparent later for (int cellnum = 0; cellnum < 50; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); } // create a string cell (see why += 2 in the c = r.createCell(cellnum + 1); // set the cell's string value to "TEST" c.setCellValue("TEST"); // make this column a bit wider s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20))); // on every other row if ((rownum % 2) == 0) { // set this to the white on red cell style // we defined above c.setCellStyle(cs2); } } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(CellStyle.BORDER_THICK); //create 50 cells for (int cellnum = 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); }
From source file:gda.hrpd.data.ExcelWorkbook.java
License:Open Source License
/** * modify if exist, or create if not exist, a cell in the specified row at specified position with the specified * value.//from w w w.ja v a2 s .co m * * @param row * @param column * @param date * @throws IOException */ public void setCellValue(HSSFRow row, int column, Date date) throws IOException { HSSFCell cell = row.getCell(column); if (cell == null) { if (!writeable) { logger.error("Cannot create a new sheet in file {}.", this.filename); throw new IOException("Cannot write to file {}." + this.filename); } // we style the cell as a date (and time). It is important to // create a new cell style from the workbook otherwise you can end // up modifying the built in style and effecting not only this cell // but other cells. HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell = row.createCell((short) column); cell.setCellValue(date); cell.setCellStyle(cellStyle); } else { cell.setCellValue(date); } }
From source file:gda.hrpd.data.HSSF.java
License:Apache License
/** * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells. * * @param filename//from w w w . j a v a 2 s . com * @param write * @exception IOException */ public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException { short rownum = 0; FileOutputStream out = new FileOutputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = (short) 0; rownum < 300; rownum++) { r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } // r.setRowNum(( short ) rownum); for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("TEST")); s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } // 50 characters divided by 1/20th of a point } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (short cellnum = (short) 0; cellnum < 50; cellnum++) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK); // c.setCellValue(0); c.setCellStyle(cs3); } s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3)); s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet wb.write(out); out.close(); }