List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet
Sheet createSheet();
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);/*from w w w .j a v a 2s . c o m*/ startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }
From source file:com.swordlord.gozer.components.csv.GCsvList.java
License:Open Source License
@Override public void renderToWorkbook(Workbook wb) { Sheet sheet = null;//from w ww . j a va2 s . c o m String caption = _obList.getCaption(); if (caption == null) { sheet = wb.createSheet(); } else { if (wb.getSheet(caption) == null) { sheet = wb.createSheet(caption); } else { sheet = wb.createSheet(MessageFormat.format("{0} {1}", caption, wb.getNumberOfSheets())); } } createTableColumn(sheet); createRows(sheet); }
From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException { File template = new File("Template.xls"); template.createNewFile();/*from w w w .j av a 2 s. c o m*/ org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet(); wb.setSheetName(0, "Requirements"); int column = 0; CellStyle cs = wb.createCellStyle(); cs.setDataFormat(getBuiltinFormat("text")); Font f = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setBold(true); f.setColor((short) Font.COLOR_NORMAL); cs.setFont(f); Row newRow = sheet.createRow(0); for (String label : COLUMNS) { Cell newCell = newRow.createCell(column); newCell.setCellStyle(cs); newCell.setCellValue(label); column++; } try (FileOutputStream out = new FileOutputStream(template)) { wb.write(out); out.close(); } catch (FileNotFoundException e) { LOG.log(Level.SEVERE, null, e); } catch (IOException e) { LOG.log(Level.SEVERE, null, e); } return template; }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
public static File exportTemplate() throws FileNotFoundException, IOException, InvalidFormatException { File template = new File("Template.xls"); template.createNewFile();/* w w w . ja v a 2 s .com*/ org.apache.poi.ss.usermodel.Workbook wb = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet sheet = wb.createSheet(); wb.setSheetName(0, "Steps"); int column = 0; CellStyle cs = wb.createCellStyle(); cs.setDataFormat(getBuiltinFormat("text")); Font f = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setBold(true); f.setColor((short) Font.COLOR_NORMAL); cs.setFont(f); Row newRow = sheet.createRow(0); for (String label : COLUMNS) { Cell newCell = newRow.createCell(column); newCell.setCellStyle(cs); newCell.setCellValue(label); column++; } try (FileOutputStream out = new FileOutputStream(template)) { wb.write(out); out.close(); } catch (FileNotFoundException e) { LOG.log(Level.SEVERE, null, e); } catch (IOException e) { LOG.log(Level.SEVERE, null, e); } return template; }
From source file:com.wantdo.stat.excel.poi_src.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow((short) 2); row.setHeightInPoints(30);//from w w w. ja v a 2s . co m for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("ss-example-align.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:com.wantdo.stat.excel.poi_src.formula.SettingExternalFunction.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook() // register the add-in wb.addToolPack(new BloombergAddIn()); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0);/*from w w w .j a va 2s . c o m*/ row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100"); row.createCell(1).setCellFormula( "BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") "); row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") "); FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx"); wb.write(out); out.close(); }
From source file:com.ykun.commons.utils.excel.ExcelUtils.java
License:Apache License
/** * xlsheaders// w w w .jav a 2s . c o m * * @param list the list * @param headers the headers * @param out the out */ public static <T> void export(List<T> list, List<String> headers, OutputStream out) { // ? if (list == null || list.size() == 0) { return; } try { Workbook workbook = new XSSFWorkbook(); // XSSFWorkbook Sheet sheet = workbook.createSheet(); // ?Sheet // ? int rowNo = 0; CellStyle headerStyle = createHeaderStyle(workbook); if (headers != null && headers.size() > 0) { Row row = sheet.createRow(rowNo++); for (int i = 0; i < headers.size(); i++) { Cell cell = row.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(headers.get(i)); } } // ? CellStyle normalStyle = createNormalStyle(workbook); for (T t : list) { Row row = sheet.createRow(rowNo++); Field[] fields = t.getClass().getDeclaredFields(); int column = 0; for (int i = 0; i < fields.length; i++) { Object value; Field field = fields[i]; ExcelField excelField = field.getAnnotation(ExcelField.class); if (excelField != null && !excelField.ignore()) { String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable? Method method = t.getClass().getMethod(methodName, new Class[] {}); value = method.invoke(t, new Object[] {}); } else if (excelField != null && excelField.ignore()) { continue; } else { String methodName = PREFIX_GETTER + StringUtils.capitalize(field.getName()); // get???getisEnable? Method method = t.getClass().getMethod(methodName, new Class[] {}); value = method.invoke(t, new Object[] {}); } row.setRowStyle(normalStyle); addCell(row, column++, value, excelField); } } workbook.write(out); } catch (Exception e) { logger.error("Export error:", e); throw new RuntimeException(e); } }
From source file:Controladores.HacerReporte.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w w w.ja v a2s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Reporte.xlsx"); // Se crea el libro Workbook libro = new XSSFWorkbook(); // // Manager_BD bd= new Manager_BD(); // bd.iniciarConexion(); // // libro=bd.tareasParaReporte(request.getParameter("fechaI"), request.getParameter("fechaF"), request.getParameter("cliente"), request.getParameter("proyecto")); // // bd.cerrarConexion(); // Se crea una hoja dentro del libro Sheet hoja = libro.createSheet(); // Se crea una fila dentro de la hoja Row fila = hoja.createRow(1); Cell celda = fila.createCell(1); celda.setCellValue("Hola"); libro.write(response.getOutputStream()); }
From source file:Controller.Sonstiges.CreateExcelContorller.java
public void createTableExcelAction() { try {/* w w w. j a va2 s. co m*/ Workbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet(); this.sheet = sheet; //Create XSSFTable table = this.sheet.createTable(); table.setDisplayName("StudentsTablle"); CTTable cttable = table.getCTTable(); //Style configurations CTTableStyleInfo style = cttable.addNewTableStyleInfo(); style.setName("inprotucTable"); style.setShowColumnStripes(false); style.setShowRowStripes(true); //Set which area the table should be placed in cttable.setId(1); cttable.setName("Test"); cttable.setTotalsRowCount(1); CTTableColumns columns = cttable.addNewTableColumns(); this.columns = columns; this.columns.setCount(3); // save the data in Excel Tabele before that u create an excel File this.writeInTableExcelAction(); //Create a File Excel FileOutputStream fileOut = new FileOutputStream("/home/kourda/Downloads/table.xls"); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException f) { System.out.print(f.toString()); } catch (IOException io) { System.out.print(io.toString()); } }
From source file:cs.handmail.processtable.ExportExcel.java
public void export() { try {//ww w . j ava 2s . co m new WorkbookFactory(); Workbook wb = new XSSFWorkbook(); //Excell workbook Sheet sheet = wb.createSheet(); //WorkSheet Row row = sheet.createRow(2); //Row created at line 3 TableModel model = _tableExport.getModel(); String temp; Row headerRow = sheet.createRow(0); //Create row at line 0 for (int headings = 0; headings < model.getColumnCount(); headings++) { headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name } for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row for (int cols = 0; cols < _tableExport.getColumnCount(); cols++) { //For each table column if (model.getValueAt(rows, cols) != null) temp = model.getValueAt(rows, cols).toString(); else temp = ""; row.createCell(cols).setCellValue(temp); } //Set the row to the next one in the sequence row = sheet.createRow((rows + 3)); } wb.write(new FileOutputStream(_pathFolder + "/" + _nameFile));//Save the file JOptionPane.showMessageDialog(null, "Save file Success"); } catch (IOException ex) { Logger.getLogger(ExportExcel.class.getName()).log(Level.SEVERE, null, ex); } }