List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell
@Override public XSSFCell createCell(int columnIndex)
From source file:Servelt.ExcelWriter.java
public ExcelWriter(HttpServletRequest request) { logger = Logger.getLogger(ExcelWriter.class.getName()); workbook = new XSSFWorkbook(); Map<String, XSSFSheet> sheetMap = new TreeMap<String, XSSFSheet>(); XSSFRow row; sheetMap.put(CPU.ComponentName, createSheet(workbook, new CpuSet())); sheetMap.put(Antennas.ComponentName, createSheet(workbook, new AntennasSet())); sheetMap.put(Button.ComponentName, createSheet(workbook, new ButtonSet())); sheetMap.put(Camera.ComponentName, createSheet(workbook, new CameraSet())); sheetMap.put(CardReader.ComponentName, createSheet(workbook, new CardReaderSet())); sheetMap.put(ClickPad.ComponentName, createSheet(workbook, new ClickPadSet())); sheetMap.put(Ethernet.ComponentName, createSheet(workbook, new EthernetSet())); sheetMap.put(KBCEBC.ComponentName, createSheet(workbook, new KbcEbcSet())); sheetMap.put(LcdPanel.ComponentName, createSheet(workbook, new LcdPanelSet())); sheetMap.put(Memory.ComponentName, createSheet(workbook, new MemorySet())); sheetMap.put(Sensor.ComponentName, createSheet(workbook, new SensorSet())); sheetMap.put(Storage.ComponentName, createSheet(workbook, new StorageSet())); sheetMap.put(TouchPanel.ComponentName, createSheet(workbook, new TouchPanelSet())); sheetMap.put(WLAN.ComponentName, createSheet(workbook, new WlanSet())); sheetMap.put(WWAN.ComponentName, createSheet(workbook, new WwanSet())); sheetMap.put(Keyboard.ComponentName, createSheet(workbook, new KeyboardSet())); sheetMap.put(Battery.ComponentName, createSheet(workbook, new BatterySet())); sheetMap.put(Charger.ComponentName, createSheet(workbook, new ChargerSet())); sheetMap.put(Measurement.ComponentName, createSheet(workbook, new MeasurementSet())); sheetMap.put(Graphic.ComponentName, createSheet(workbook, new GraphicSet())); sheetMap.put(AudioCodec.ComponentName, createSheet(workbook, new AudioCodecSet())); sheetMap.put(PanelInterfaceBridge.ComponentName, createSheet(workbook, new PanelInterfaceBridgeSet())); sheetMap.put(ExternalStorageCard.ComponentName, createSheet(workbook, new ExternalStorageCardSet())); sheetMap.put(ODD.ComponentName, createSheet(workbook, new ODDSet())); sheetMap.put(Speaker.ComponentName, createSheet(workbook, new SpeakerSet())); sheetMap.put(Mic.ComponentName, createSheet(workbook, new MicSet())); sheetMap.put(IoPort.ComponentName, createSheet(workbook, new IoPortSet())); sheetMap.put(OS.ComponentName, createSheet(workbook, new OSSet())); if (request != null) { ComponentSet set = requestToCom(request); filename = request.getParameter("category") + ".xlsx"; int i = 1; for (Iterator<Component> iter = set.getComList().iterator(); iter.hasNext();) { row = sheetMap.get(set.getComponentName()).createRow(i++); int j = 0; for (Iterator<String> str = iter.next().getComponent().iterator(); str.hasNext();) { String data = str.next(); row.createCell(j); row.getCell(j).setCellValue(data); j++;/*from ww w . ja va2s . c o m*/ } } } autoStyle(workbook); try (FileOutputStream out = new FileOutputStream(filepath + filename)) { workbook.write(out); } catch (Exception ex) { logger.log(Level.WARNING, ex.toString()); ex.printStackTrace(); } }
From source file:sistemas.Utils.java
public static void writeXLSXFile(JTable table, String path) throws IOException { TableModel model = table.getModel(); //Table model String excelFileName = path;//name of excel file String sheetName = "Sheet1";//name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow rowH = sheet.createRow(0); for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column rowH.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name }/*from w w w. j a va 2 s .co m*/ //iterating r number of rows for (int r = 1; r <= model.getRowCount(); r++) { XSSFRow row = sheet.createRow(r); //iterating c number of columns for (int c = 0; c < model.getColumnCount(); c++) { XSSFCell cell = row.createCell(c); cell.setCellValue("" + model.getValueAt(r - 1, c)); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); //write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); }
From source file:si_piket_smkn3.frm_tampil_piket.java
private void exportexcel(String alamat) { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();//ww w .j av a 2 s . co m //load data dari tabel ke treemap TreeMap<Integer, Object[]> data = new TreeMap<>(); data.put(0, new Object[] { tableModel.getColumnName(0), tableModel.getColumnName(1), tableModel.getColumnName(2), tableModel.getColumnName(3), tableModel.getColumnName(4), tableModel.getColumnName(5), tableModel.getColumnName(6), tableModel.getColumnName(7), tableModel.getColumnName(8) }); for (int i = 1; i < tableModel.getRowCount() + 1; i++) { data.put(i, new Object[] { getCellValue(i - 1, 0), getCellValue(i - 1, 1), getCellValue(i - 1, 2), getCellValue(i - 1, 3), getCellValue(i - 1, 4), getCellValue(i - 1, 5), getCellValue(i - 1, 6), getCellValue(i - 1, 7), getCellValue(i - 1, 8), }); } //menulis ke kertas Set<Integer> ids = data.keySet(); XSSFRow row; int rowID = 0; for (Integer key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write excel to file system try { FileOutputStream fos = new FileOutputStream(new File(alamat)); wb.write(fos); fos.close(); } catch (Exception ex) { System.err.println(ex.getMessage()); JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.INFORMATION_MESSAGE); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;// ww w .ja v a2 s .c om String ruthPath = null; try { if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) { // Se crea el libro XSSFWorkbook libro = new XSSFWorkbook(); // Se crea una hoja dentro del libro XSSFSheet sheetD = libro.createSheet(); //Obtener lista de alumnos del curso List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso()); //Leer datos y colocarlos en la hoja int f = 0; //Guardar datos en celda for (SieniAlumno alumno : alumnosEval) { // Se crea una fila dentro de la hoja XSSFRow fila = sheetD.createRow(f); f++; // Se crea las celdas dentro de la fila XSSFCell celdaCarnet = fila.createCell((short) 0); XSSFCell celdaAlumno = fila.createCell((short) 1); XSSFCell celdaNota = fila.createCell((short) 2); //Colocar valor en celda celdaCarnet.setCellValue(alumno.getAlCarnet()); celdaAlumno.setCellValue(alumno.getNombreCompleto()); celdaNota.setCellValue((double) 0.00); } //Encabezados desde plantilla InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx"); StreamedContent plantillaXLS = new DefaultStreamedContent(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx"); XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream()); XSSFSheet sheetP = plantilla.getSheetAt(0); //Filas que ocupa el encabezado de plantilla int encabezado = 3; //Quitar encabezado y desplazar Datos sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado); //Copiar contenido de plantilla a la hoja del reporte int inicio = 0; for (int row = 0; row < encabezado; row++) { copyRow(sheetP, sheetD, row, inicio); inicio++; } //Combinar las columnas al igual que la plantilla for (int m = 0; m < sheetP.getNumMergedRegions(); m++) { CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy(); sheetD.addMergedRegion(cellRangeAddress); } //Evaluacion XSSFCell celdaEval = sheetD.getRow(0).getCell(1); celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre()); // Se salva el libro. FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx"); libro.write(elFichero); elFichero.close(); //Leer libro para descarga FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx")); filePlantilla = new DefaultStreamedContent(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx"); } else { new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR); } } catch (Exception exc) { new ValidationPojo().printMsj( "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath, FacesMessage.SEVERITY_ERROR); } return filePlantilla; }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//from w w w.ja v a 2 s .c o m continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }
From source file:tan.jam.jsf.Shifting.java
public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//w w w. ja v a 2s. co m newRow = worksheet.createRow(destinationRowNum); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null; continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: //newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(""); break; } } for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } int inc = destinationRowNum + 1; worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc); }
From source file:tan.jam.jsf.Shifting.java
private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum, int Mov) { XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) { int d = i - 1; XSSFCell oldCell = sourceRow.getCell(d); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null;//from w ww. java 2 s .co m continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }
From source file:test.WriteXLSX.java
public static void main(String[] args) throws IOException { XSSFWorkbook workbook;//from w ww . j a v a 2 s . c o m try (FileInputStream fis = new FileInputStream(new File("D:/desk/test.xlsx"))) { workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row1 = sheet.createRow(0); XSSFCell r1c1 = row1.createCell(0); r1c1.setCellValue("Emd Id"); XSSFCell r1c2 = row1.createCell(1); r1c2.setCellValue("NAME"); XSSFCell r1c3 = row1.createCell(2); r1c3.setCellValue("AGE"); XSSFRow row2 = sheet.createRow(1); XSSFCell r2c1 = row2.createCell(0); r2c1.setCellValue("1"); XSSFCell r2c2 = row2.createCell(1); r2c2.setCellValue("Ram"); XSSFCell r2c3 = row2.createCell(2); r2c3.setCellValue("20"); XSSFRow row3 = sheet.createRow(2); XSSFCell r3c1 = row3.createCell(0); r3c1.setCellValue("2"); XSSFCell r3c2 = row3.createCell(1); r3c2.setCellValue("Shyam"); XSSFCell r3c3 = row3.createCell(2); r3c3.setCellValue("25"); } try (FileOutputStream fos = new FileOutputStream(new File("D:\\desk\\test2.xlsx"))) { workbook.write(fos); } System.out.println("Done"); }
From source file:tr.org.liderahenk.liderconsole.core.utils.SWTResourceManager.java
License:Open Source License
private static XSSFWorkbook createWorkbookFromTable(TableViewer tableViewer, String sheetName) { // Create workbook & sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName == null ? "Sheet1" : sheetName); // Shade the background of the header row XSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setBorderTop(CellStyle.BORDER_THIN); headerStyle.setBorderBottom(CellStyle.BORDER_THIN); headerStyle.setBorderLeft(CellStyle.BORDER_THIN); headerStyle.setBorderRight(CellStyle.BORDER_THIN); headerStyle.setAlignment(HorizontalAlignment.CENTER); // Add header row Table table = tableViewer.getTable(); TableColumn[] columns = table.getColumns(); int rowIndex = 0; int cellIndex = 0; XSSFRow header = sheet.createRow((short) rowIndex++); for (TableColumn column : columns) { XSSFCell cell = header.createCell(cellIndex++); cell.setCellValue(column.getText()); cell.setCellStyle(headerStyle);//from www . ja v a2 s.com } // Add data rows TableItem[] items = tableViewer.getTable().getItems(); for (TableItem item : items) { // create a new row XSSFRow row = sheet.createRow((short) rowIndex++); cellIndex = 0; for (int i = 0; i < columns.length; i++) { // Create a new cell XSSFCell cell = row.createCell(cellIndex++); String text = item.getText(i); // Set the horizontal alignment (default to RIGHT) XSSFCellStyle cellStyle = wb.createCellStyle(); if (LiderCoreUtils.isInteger(text)) { cellStyle.setAlignment(HorizontalAlignment.RIGHT); } else if (LiderCoreUtils.isValidDate(text, ConfigProvider.getInstance().get(LiderConstants.CONFIG.DATE_FORMAT))) { cellStyle.setAlignment(HorizontalAlignment.CENTER); } else { cellStyle.setAlignment(HorizontalAlignment.LEFT); } cell.setCellStyle(cellStyle); // Set the cell's value cell.setCellValue(text); } } // Auto-fit the columns for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn((short) i); } return wb; }
From source file:uk.ac.bbsrc.tgac.miso.core.util.FormUtils.java
License:Open Source License
public static void createPlateExportForm(File outpath, JSONArray jsonArray) throws Exception { InputStream in = null;/*from w w w . j a v a 2 s. co m*/ in = FormUtils.class.getResourceAsStream("/forms/ods/plate_input.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("Input"); FileOutputStream fileOut = new FileOutputStream(outpath); int i = 4; for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) { String sampleinwell = jsonObject.getString("value"); //"sampleid:wellid:samplealias:projectname" String sampleId = sampleinwell.split(":")[0]; String wellId = sampleinwell.split(":")[1]; String sampleAlias = sampleinwell.split(":")[2]; String projectName = sampleinwell.split(":")[3]; XSSFRow row = sheet.createRow(i); XSSFCell cellA = row.createCell(0); cellA.setCellValue(wellId); XSSFCell cellB = row.createCell(1); cellB.setCellValue(projectName); XSSFCell cellC = row.createCell(2); cellC.setCellValue(sampleAlias); i++; } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }