List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:com.zxy.commons.poi.excel.ExcelUtils.java
License:Apache License
/** * export excel// ww w . j a v a2 s. com * * @param sheetName sheet name * @param table table * @return Workbook */ @SuppressWarnings("PMD.ShortVariable") private static Workbook exportExcel(String sheetName, Table<Integer, String, String> table) { Set<Integer> tableRows = table.rowKeySet(); Set<String> tableColumns = table.columnKeySet(); // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(sheetName); // ???n? /*for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); }*/ // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f1 = wb.createFont(); Font f2 = wb.createFont(); // ???? f1.setFontHeightInPoints((short) 10); f1.setColor(IndexedColors.BLACK.getIndex()); f1.setBold(true); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f1); cs.setBorderLeft(BorderStyle.THIN); cs.setBorderRight(BorderStyle.THIN); cs.setBorderTop(BorderStyle.THIN); cs.setBorderBottom(BorderStyle.THIN); cs.setAlignment(HorizontalAlignment.CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(BorderStyle.THIN); cs2.setBorderRight(BorderStyle.THIN); cs2.setBorderTop(BorderStyle.THIN); cs2.setBorderBottom(BorderStyle.THIN); cs2.setAlignment(HorizontalAlignment.CENTER); // ?? int i = 0; for (String tableColumn : tableColumns) { Cell cell = row.createCell(i); cell.setCellValue(tableColumn); cell.setCellStyle(cs); i++; } // ?? for (Integer tableRow : tableRows) { // Row ,Cell , Row Cell 0 // sheet checkArgument(tableRow > 0, "Row index must be greater than zero!"); Row row1 = sheet.createRow(tableRow); // row Map<String, String> item = table.row(tableRow); int j = 0; for (Map.Entry<String, String> entry : item.entrySet()) { // for(String v:item.keySet()){ // System.out.println(tableRow + "-" + v + "-" + item.get(v)); Cell cell = row1.createCell(j); cell.setCellValue(entry.getValue()); cell.setCellStyle(cs2); j++; } } return wb; }
From source file:Compras.altaCompras.java
private void b_muestraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_b_muestraActionPerformed // TODO add your handling code here: javax.swing.JFileChooser jF1= new javax.swing.JFileChooser(); jF1.setFileFilter(new ExtensionFileFilter("Excel document (*.xls)", new String[] { "xls" })); String ruta = null;/*from w w w .jav a2s .co m*/ if(jF1.showSaveDialog(null)==jF1.APPROVE_OPTION) { ruta = jF1.getSelectedFile().getAbsolutePath(); File 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("Muestras"); org.apache.poi.ss.usermodel.Font font10 = libro.createFont(); font10.setFontHeightInPoints((short)10); font10.setFontName("Arial"); font10.setItalic(false); font10.setBold(false); font10.setColor(new HSSFColor.YELLOW().getIndex()); CellStyle titulo = libro.createCellStyle(); CellStyle contenido = libro.createCellStyle(); CellStyle firma = libro.createCellStyle(); CellStyle costado = libro.createCellStyle(); CellStyle derecha = libro.createCellStyle(); CellStyle derecha_borde = libro.createCellStyle(); titulo.setFont(font10); titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titulo.setFillBackgroundColor(new HSSFColor.GREEN().getIndex()); titulo.setAlignment(CellStyle.ALIGN_CENTER); contenido.setBorderBottom(HSSFCellStyle.BORDER_THIN); contenido.setBorderTop(HSSFCellStyle.BORDER_THIN); contenido.setBorderRight(HSSFCellStyle.BORDER_THIN); contenido.setBorderLeft(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderBottom(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderTop(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderRight(HSSFCellStyle.BORDER_THIN); derecha_borde.setBorderLeft(HSSFCellStyle.BORDER_THIN); derecha_borde.setAlignment(CellStyle.ALIGN_RIGHT); derecha.setAlignment(CellStyle.ALIGN_RIGHT); firma.setBorderTop(HSSFCellStyle.BORDER_THIN); firma.setAlignment(CellStyle.ALIGN_CENTER); //costado.setBorderLeft(HSSFCellStyle.BORDER_THIN); hoja.setColumnWidth(0, 3000); hoja.setColumnWidth(2, 3000); hoja.setColumnWidth(3, 8000); hoja.setColumnWidth(4, 5000); try { InputStream is = new FileInputStream("imagenes/grande300115.jpg"); byte[] bytes = IOUtils.toByteArray(is); int pictureIdx = libro.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); is.close(); CreationHelper helper = libro.getCreationHelper(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(0); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); pict.resize(); }catch(Exception e){e.printStackTrace();} Row r7 = hoja.createRow(7); r7.createCell(0).setCellValue("ORDEN:"); r7.createCell(1).setCellValue(String.valueOf(ord.getIdOrden())); r7.createCell(2).setCellValue("Hrs. Entrega:"); r7.createCell(3).setCellValue(""); hoja.addMergedRegion(new CellRangeAddress(7,7,4,7)); r7.createCell(4).setCellValue("ORDEN PARA SURTIR MUESTRAS"); r7.getCell(4).setCellStyle(derecha); Date fecha = new Date(); DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");//YYYY-MM-DD HH:MM:SS String valor=dateFormat.format(fecha); Row r8 = hoja.createRow(8); r8.createCell(0).setCellValue("Supervisor:"); hoja.addMergedRegion(new CellRangeAddress(8,8,1,3)); r8.createCell(1).setCellValue(""); r8.createCell(4).setCellValue("F. Elaboracin:"); r8.createCell(5).setCellValue(valor); Row r9 = hoja.createRow(9); r9.createCell(0).setCellValue("Comprador:"); hoja.addMergedRegion(new CellRangeAddress(9,9,1,3)); r9.createCell(1).setCellValue(""); r9.createCell(4).setCellValue("F. Entrega:"); r9.createCell(5).setCellValue(""); Row r10 = hoja.createRow(10); r10.createCell(0).setCellValue("Cantidad"); r10.getCell(0).setCellStyle(titulo); hoja.addMergedRegion(new CellRangeAddress(10,10,1,7)); r10.createCell(1).setCellValue("Descripcin"); r10.getCell(1).setCellStyle(titulo); int ren=11; for(int r=0;r<(t_datos.getRowCount());r++) { if((boolean)t_titulos.getValueAt(r, 3)==true) { Row fila = hoja.createRow(ren); Cell celda = fila.createCell(0); celda.setCellStyle(derecha_borde); Cell celda1 = fila.createCell(1); celda1.setCellStyle(contenido); fila.createCell(2).setCellStyle(contenido); fila.createCell(3).setCellStyle(contenido); fila.createCell(4).setCellStyle(contenido); fila.createCell(5).setCellStyle(contenido); fila.createCell(6).setCellStyle(contenido); fila.createCell(7).setCellStyle(contenido); //Cell celda8 = fila.createCell(8); //celda8.setCellStyle(costado); try{ celda.setCellValue(t_datos.getValueAt(r, 14).toString()); hoja.addMergedRegion(new CellRangeAddress(ren,ren,1,7)); celda1.setCellValue(t_titulos.getValueAt(r, 2).toString()); //celda8.setCellValue(""); }catch(Exception e){ celda.setCellValue(""); } ren++; } } Row rx = hoja.createRow(ren+5); hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,0,2)); rx.createCell(0).setCellValue("Recibe Muestras"); rx.getCell(0).setCellStyle(firma); rx.createCell(1).setCellStyle(firma); rx.createCell(2).setCellStyle(firma); hoja.addMergedRegion(new CellRangeAddress(ren+5,ren+5,5,7)); rx.createCell(5).setCellValue("Entrega Muestras"); rx.getCell(5).setCellStyle(firma); rx.createCell(6).setCellStyle(firma); rx.createCell(7).setCellStyle(firma); libro.write(archivo); archivo.close(); Desktop.getDesktop().open(archivoXLS); }catch(Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(this, "No se pudo realizar el reporte si el archivo esta abierto"); } } }
From source file:Compras.avanceSurtido.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: h = new Herramientas(this.user, 0); h.session(sessionPrograma);//from ww w .ja v a 2 s . co m File archivoXLS = null; 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) { 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("Avance de pedidos"); Font font = libro.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName("Arial"); font.setItalic(false); font.setBold(true); // Fonts are set into a style so create a new one to use. CellStyle style = libro.createCellStyle(); style.setFont(font); 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(2, 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("Modelo:"); r4.createCell(1).setCellValue("" + ord.getModelo()); hoja.createRow(5).createCell(0).setCellValue( "**********************************************************************************************************************************************************************************************************************************************************************************************************************************************************"); Row r6 = hoja.createRow(6); r6.createCell(0).setCellValue("Partida"); r6.createCell(1).setCellValue("sub"); r6.createCell(2).setCellValue("Descripcion"); r6.createCell(3).setCellValue("Hoj"); r6.createCell(4).setCellValue("Mec"); r6.createCell(5).setCellValue("Sus"); r6.createCell(6).setCellValue("Ele"); r6.createCell(7).setCellValue("Can"); r6.createCell(8).setCellValue("Med"); r6.createCell(9).setCellValue("Folio"); r6.createCell(10).setCellValue("Codigo"); r6.createCell(11).setCellValue("Origen"); r6.createCell(12).setCellValue("Proveedor"); r6.createCell(13).setCellValue("Cant C."); r6.createCell(14).setCellValue("C/U Comprado"); r6.createCell(15).setCellValue("Plazo"); r6.createCell(16).setCellValue("Pedido"); r6.createCell(17).setCellValue("F. Pedido"); r6.createCell(18).setCellValue("Entradas"); r6.createCell(19).setCellValue("Devoluciones"); r6.createCell(20).setCellValue("Pendientes"); r6.createCell(21).setCellValue("No Factura"); hoja.createRow(7).createCell(0).setCellValue( "**********************************************************************************************************************************************************************************************************************************************************************************************************************************************************"); session.close(); if (t_datos.getRowCount() > 0) { for (int i = 0; i < t_datos.getRowCount(); i++) { Row fila = hoja.createRow(i + 8); for (int j = 1; j < t_datos.getColumnCount(); j++) { if (j > 3 && j < 8) { if ((boolean) t_datos.getValueAt(i, j) == true) fila.createCell(j - 1).setCellValue(""); else fila.createCell(j - 1).setCellValue(""); } else { if (t_datos.getValueAt(i, j) != null) fila.createCell(j - 1).setCellValue("" + t_datos.getValueAt(i, j)); else fila.createCell(j - 1).setCellValue(""); } } } } libro.write(archivo); archivo.close(); Desktop.getDesktop().open(archivoXLS); } 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:Compras.generaCotizacion.java
void exel() { h = new Herramientas(this.user, 0); h.session(sessionPrograma);/*from ww w .jav a 2 s . c om*/ 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:Controller.Sonstiges.ExcelController.java
private void makeTitleOfPage() throws IOException { this.sheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 13)); this.row = this.sheet.createRow(0); XSSFCell cell2 = this.row.createCell(0); String title = "Inprotuc Datenbank | Informationen zur Personen \nSuchkriterien: "; ArrayList<String> array2 = this.model.getQueryInfo(); ArrayList<String> array = this.deleteEmptyValueOArray(array2); String info = ""; if (array.size() == 2) { info = array.get(0) + " / " + array.get(1) + "."; }/*from www . j av a2 s . com*/ if (array.size() == 4) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + "."; } if (array.size() == 6) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + ", "; info = info + array.get(4) + "/" + array.get(5) + "."; } cell2.setCellValue(title + info); CellStyle cellStyle = this.wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); // font Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); }
From source file:Controller.Sonstiges.ExcelController.java
private void makeTitleOfTable() { this.row = this.sheet.createRow(this.firstRow); CellStyle cellStyle = this.wb.createCellStyle(); // font /*from w w w . j a v a 2 s . co m*/ Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 10); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); this.saveDataInExcel(0, "Urz", cellStyle); this.saveDataInExcel(1, "Vorname", cellStyle); this.saveDataInExcel(2, "Name", cellStyle); this.saveDataInExcel(3, "Fakultt", cellStyle); this.saveDataInExcel(4, "Geburtsdatum", cellStyle); this.saveDataInExcel(5, "E-Mail", cellStyle); this.saveDataInExcel(6, "Telefonnummer", cellStyle); this.saveDataInExcel(7, "Aktivitten-ID", cellStyle); this.saveDataInExcel(8, "Aktivittenname", cellStyle); this.saveDataInExcel(9, "Zeitraum", cellStyle); this.saveDataInExcel(10, "Art", cellStyle); this.saveDataInExcel(11, "Durchfhrung", cellStyle); this.saveDataInExcel(12, "Bemerkung", cellStyle); this.saveDataInExcel(13, "Status", cellStyle); }
From source file:controller.VisitasController.java
public void makeRowBold(Workbook wb, Row row) { CellStyle style = wb.createCellStyle();//Create style Font font = wb.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(new HSSFColor.WHITE().getIndex());//Make font bold style.setFont(font);//set it to bold style.setAlignment(HorizontalAlignment.CENTER); style.setFillBackgroundColor(new HSSFColor.BLACK().getIndex()); style.setFillForegroundColor(new HSSFColor.BLACK().getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); for (int i = 0; i < row.getLastCellNum(); i++) { if (!row.getCell(i).getStringCellValue().equals("")) { row.getCell(i).setCellStyle(style);//Set the sty;e }/*w ww. j a va 2s.c om*/ } }
From source file:cv_extractor.DocReader.java
protected static void generateExcel(String directory, String fileName) { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Email Address"); //Create CellStyle for header (First row) CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); font.setBold(true);// w w w .jav a 2 s.co m font.setFontHeightInPoints((short) 16); cellStyle.setFont(font); //Create first row (Header) Row header = sheet.createRow(0); //Creating a cell (Only one required, for single attribute E-mail) Cell cellTitle = header.createCell(1); cellTitle.setCellStyle(cellStyle); cellTitle.setCellValue("E-Mail"); //Counter to use while creating further rows, starting from row 1 int rowNum = 1; //Iterate over data and write to sheet Iterator<String> iterator = data.iterator(); while (iterator.hasNext()) { //Create a row for each E-mail string Row row = sheet.createRow(rowNum++); //Create a cell to put the E-mail string Cell cell = row.createCell(1); //Put value in the cell cell.setCellValue(iterator.next()); } try { //Write the workbook in file system if (fileName.equals("")) { fileName = "Email List"; } FileOutputStream out = new FileOutputStream(new File(directory + "/" + fileName + ".xlsx")); workbook.write(out); out.close(); JOptionPane.showMessageDialog(null, "File generated !"); } catch (Exception e) { e.printStackTrace(); } }
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {//www. ja va2 s .c om //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>(); WorkDao wdao1 = new WorkDao(); wi1 = wdao1.getWOItem(pono); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); // spreadsheet.protectSheet("kandarpCBA"); // spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("NOTE"); cell.setCellStyle(xstyle); int i = 2; for (WorkItemBean w : wi1) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(w.getLINE_NO()); cell = row.createCell(1); cell.setCellValue(w.getITEM_ID()); cell = row.createCell(2); cell.setCellValue(w.getITEM_DESC()); cell = row.createCell(3); cell.setCellValue(w.getUOM()); cell = row.createCell(4); cell.setCellValue(w.getQTY()); cell = row.createCell(5); cell.setCellValue(w.getRATE()); cell = row.createCell(6); cell.setCellValue(w.getCMT()); i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsBillDao.java
public void GenXLS(String pono, String relpath) { try {/*from w ww . j a v a2s. c om*/ //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; String sql; Connection con; PreparedStatement ps; ResultSet rs; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data con = DBmanager.GetConnection(); sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n" + " mtl_system_items.description, cba_wo_item.uom,\n" + " cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n" + " cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task," + " cba_wo_item.po_no\n" + " FROM cba_wo_item, mtl_system_items\n" + " WHERE ( (cba_wo_item.item_id = mtl_system_items.segment1)\n" + " AND (mtl_system_items.organization_id = 0)\n" + " AND (cba_wo_item.po_no = '" + pono + "')\n" + " )\n" + " ORDER BY cba_wo_item.line_no"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); //spreadsheet.protectSheet("kandarpCBA"); //spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); // borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex()); // borderStyle.setFillPattern(CellStyle.ALIGN_FILL); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono + " Note : If WO is with project information,each bill item should have project and task"); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("WO NOTE"); cell.setCellStyle(xstyle); cell = row.createCell(7); cell.setCellValue("PLANT"); cell.setCellStyle(xstyle); cell = row.createCell(8); cell.setCellValue("COST CENTER"); cell.setCellStyle(xstyle); cell = row.createCell(9); cell.setCellValue("PROJECT"); cell.setCellStyle(xstyle); cell = row.createCell(10); cell.setCellValue("TASK"); cell.setCellStyle(xstyle); cell = row.createCell(11); cell.setCellValue("HERE ADD NOTE"); cell.setCellStyle(xstyle); //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(rs.getString(1)); cell = row.createCell(1); cell.setCellValue(rs.getString(2)); cell = row.createCell(2); cell.setCellValue(rs.getString(3)); cell = row.createCell(3); cell.setCellValue(rs.getString(4)); cell = row.createCell(4); cell.setCellValue(rs.getString(6)); cell = row.createCell(5); cell.setCellValue(rs.getString(5)); cell = row.createCell(6); cell.setCellValue(""); cell = row.createCell(7); cell.setCellValue(rs.getString(7)); cell = row.createCell(8); cell.setCellValue(rs.getString(8)); cell = row.createCell(9); cell.setCellValue(rs.getString(9)); cell = row.createCell(10); cell.setCellValue(rs.getString(10)); cell = row.createCell(11); cell.setCellValue(""); i++; } //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row2; XSSFCell cell2; XSSFSheet ccsheet = workbook.createSheet("Cost Center"); row2 = ccsheet.createRow(0); cell2 = row2.createCell(0); cell2.setCellValue("Cost Center name and code. Please enter only code in excel"); cell2.setCellStyle(borderStyle); ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row2 = ccsheet.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("NAME"); cell2.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_cc_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i2 = 2; while (rs.next()) { row2 = ccsheet.createRow(i2); cell2 = row2.createCell(0); cell2.setCellValue(rs.getString(1)); cell2 = row2.createCell(1); cell2.setCellValue(rs.getString(2)); i2++; } //THIRD SHEET //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row3; XSSFCell cell3; XSSFSheet plantsheet = workbook.createSheet("Plant Center"); row3 = plantsheet.createRow(0); cell3 = row3.createCell(0); cell3.setCellValue("Plant Center name and code. Please enter only code in excel"); cell3.setCellStyle(borderStyle); plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row3 = plantsheet.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("NAME"); cell3.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_plant_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i3 = 2; while (rs.next()) { row3 = plantsheet.createRow(i3); cell3 = row3.createCell(0); cell3.setCellValue(rs.getString(1)); cell3 = row3.createCell(1); cell3.setCellValue(rs.getString(2)); i3++; } //SHEET 3 HEADER //row1 XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task"); XSSFRow row4 = spreadsheet4.createRow(0); XSSFCell cell4 = row4.createCell(0); cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row4 = spreadsheet4.createRow(1); cell4 = row4.createCell(0); cell4.setCellValue("PROJECT CODE"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(1); cell4.setCellValue("PROJECT NAME"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(2); cell4.setCellValue("TASK CODE"); cell4.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123"); for (WorkItemBean w : wi1) { row4 = spreadsheet4.createRow(j); cell4 = row4.createCell(0); cell4.setCellValue(w.getPROJ()); cell4 = row4.createCell(1); cell4.setCellValue(w.getPROJ_NAME()); cell4 = row4.createCell(2); cell4.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (SQLException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }