List of usage examples for org.apache.poi.ss.usermodel Row createCell
Cell createCell(int column);
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
@Override public void setValueAt(Object obj, int rowIndex, int columnIndex) { Row row = getSheet().getRow(rowIndex); if (row == null) { row = getSheet().createRow(rowIndex); }/*from www. ja v a 2 s. com*/ Cell cell = row.getCell(columnIndex); if (cell == null) { cell = row.createCell(columnIndex); } if (obj != null) { Class<?> clazz = obj.getClass(); if (String.class.isAssignableFrom(clazz)) { cell.setCellValue(((String) obj)); } else if (Number.class.isAssignableFrom(clazz)) { cell.setCellValue(((Number) obj).doubleValue()); } else if (Boolean.class.isAssignableFrom(clazz)) { cell.setCellValue(((Boolean) obj)); } else if (Calendar.class.isAssignableFrom(clazz)) { cell.setCellValue(((Calendar) obj)); } else if (Date.class.isAssignableFrom(clazz)) { cell.setCellValue(((Date) obj)); } else if (RichTextString.class.isAssignableFrom(clazz)) { cell.setCellValue(((RichTextString) obj)); } else { cell.setCellValue(obj.toString()); } } if (columnIndex >= getColumnCount() || rowIndex >= getRowCount()) { fireTableStructureChanged(); } else { fireTableChanged(new TableModelEvent(this, rowIndex, rowIndex, columnIndex)); } }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Insert a row at a given index./* ww w . j av a 2 s.co m*/ * * @param createAtIndex row-number of the cell at which to create a new row * @param sourceRow the row to insert */ public void insertRowAt(int createAtIndex, Row sourceRow) { Row newRow = getRow(createAtIndex); if (newRow != null) { // shift all rows >= createAtIndex up by one getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1); } else { newRow = getSheet().createRow(createAtIndex); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.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; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < getSheet().getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); getSheet().addMergedRegion(newCellRangeAddress); } } }
From source file:com.l3.info.magenda.emplois_du_temps.Semaine.java
void writeInSheet(Workbook workbook, int week_of_year) { XSSFWorkbook xssfWorkbook = workbook.getWorkbook(); Sheet sheet = xssfWorkbook.createSheet("Sem. " + week_of_year); Row row = sheet.createRow((short) 0); row.setHeight(Workbook.PixelsToTwips(64)); Cell cell = row.createCell((short) 0); // first row (0-based) - last row (0-based) - first column (0-based) -last column (0-based) sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (20 - 7) * 2 + 2)); // Cree une nouvelle police Font font = xssfWorkbook.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial"); // Fonts are set into a style so create a new one to use. XSSFCellStyle style = xssfWorkbook.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setFont(font);/*from w w w.jav a 2 s . c om*/ // Create a cell and put a value in it. cell.setCellValue("Semaine " + this.num_semaine); cell.setCellStyle(style); sheet.setDefaultRowHeight((short) 500); int x = 0, y = 2; for (Jour day : days_of_week) { day.writeInSheet(workbook, sheet, x, y); y += 2 + day.getNbrLigne(); } }
From source file:com.lapis.jsfexporter.excel.ExcelExportType.java
License:Apache License
@Override public Row exportRow(IExportRow row) { Row xlsRow = sheet.createRow(rowCount++); int cellIndex = 0; for (IExportCell cell : row.getCells()) { boolean cellIsUsed; do {/*from ww w.j a va2 s. co m*/ cellIsUsed = false; for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress region = sheet.getMergedRegion(i); if (region.isInRange(xlsRow.getRowNum(), cellIndex)) { cellIsUsed = true; cellIndex += region.getLastColumn() - region.getFirstColumn() + 1; } } } while (cellIsUsed); Cell xlsCell = xlsRow.createCell(cellIndex++); xlsCell.setCellValue(cell.getValue()); if (cell.getColumnSpanCount() > 1 || cell.getRowSpanCount() > 1) { sheet.addMergedRegion(new CellRangeAddress(xlsCell.getRowIndex(), xlsCell.getRowIndex() + cell.getRowSpanCount() - 1, xlsCell.getColumnIndex(), xlsCell.getColumnIndex() + cell.getColumnSpanCount() - 1)); cellIndex += cell.getColumnSpanCount() - 1; } } return xlsRow; }
From source file:com.liferay.events.global.mobile.portlet.PollsPortlet.java
License:Open Source License
@Override public void serveResource(ResourceRequest request, ResourceResponse response) throws PortletException, IOException { // do search and return result String cmd = ParamUtil.getString(request, "cmd"); long questionId = ParamUtil.getLong(request, "questionId"); EventPollQuestion question;/*from w w w.ja v a 2s. c o m*/ List<EventPollAnswer> answers; try { question = EventPollQuestionLocalServiceUtil.getEventPollQuestion(questionId); answers = EventPollAnswerLocalServiceUtil.getAllAnswerObjs(questionId); } catch (SystemException e) { throw new PortletException("Cannot get answers for questionId " + questionId); } catch (PortalException e) { throw new PortletException("Cannot get question or answers for questionId " + questionId); } if (Validator.equals(cmd, "exportAnswersCSV")) { File f = FileUtil.createTempFile(); CSVWriter writer = new CSVWriter(new FileWriter(f), ','); // find out all headers List<String> headers = new ArrayList<String>(); headers.add("ID"); headers.add("RAW ANSWER"); Set<String> payloadHeaders = new HashSet<String>(); for (EventPollAnswer answer : answers) { JSONObject payloadObj = null; try { payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload()); if (Validator.isNull(payloadObj)) { continue; } } catch (JSONException e) { throw new PortletException("cannot read payload: " + answer.getPayload()); } Iterator<String> keyIt = payloadObj.keys(); while (keyIt.hasNext()) { String key = keyIt.next(); payloadHeaders.add(key); } } headers.addAll(payloadHeaders); Map<String, Integer> headerCols = new HashMap<String, Integer>(); for (int i = 0; i < headers.size(); i++) { headerCols.put(headers.get(i), i); } // now print them writer.writeNext(headers.toArray(new String[] {})); for (EventPollAnswer answer : answers) { List<String> vals = new ArrayList<String>(); JSONObject payloadObj = null; try { payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload()); if (Validator.isNull(payloadObj)) { continue; } } catch (JSONException e) { throw new PortletException("cannot read payload: " + answer.getPayload()); } for (String headerCol : headers) { String val; if (headerCol.equals("ID")) { val = String.valueOf(answer.getAnswerId()); } else if (headerCol.equals("RAW ANSWER")) { val = String.valueOf(answer.getAnswer()); } else { val = payloadObj.getString(headerCol); } if (Validator.isNull(val)) { val = ""; } vals.add(val); } writer.writeNext(vals.toArray(new String[] {})); } writer.flush(); writer.close(); PortletResponseUtil.sendFile(request, response, question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".csv", new FileInputStream(f)); } else if (Validator.equals(cmd, "exportAnswersXLSX")) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Poll Answers"); Row headerRow = sheet.createRow(0); Cell headerCell = headerRow.createCell(0); headerCell.setCellValue("ID"); headerCell = headerRow.createCell(1); headerCell.setCellValue("Raw Answer"); HashMap<String, Integer> rowMap = new HashMap<String, Integer>(); int currentRow = 1; int nextHeaderCol = 2; for (EventPollAnswer answer : answers) { Row row = sheet.createRow(currentRow); currentRow++; JSONObject payloadObj = null; long answerId = answer.getAnswerId(); Cell idCell = row.createCell(0); idCell.setCellValue(String.valueOf(answerId)); try { payloadObj = JSONFactoryUtil.createJSONObject(answer.getPayload()); if (Validator.isNull(payloadObj)) { continue; } } catch (JSONException e) { throw new PortletException("cannot read payload: " + answer.getPayload()); } Cell answerCell = row.createCell(1); answerCell.setCellValue(String.valueOf(answer.getAnswer())); Iterator<String> keyIt = payloadObj.keys(); while (keyIt.hasNext()) { String key = keyIt.next(); Integer headerCol = rowMap.get(key); if (Validator.isNull(headerCol)) { rowMap.put(key, nextHeaderCol); Cell nextHeaderCell = headerRow.createCell(nextHeaderCol); nextHeaderCell.setCellValue(key.toUpperCase()); headerCol = nextHeaderCol; nextHeaderCol++; } Cell cell = row.createCell(headerCol); cell.setCellValue(payloadObj.getString(key)); } } File f = FileUtil.createTempFile(); FileOutputStream fos = new FileOutputStream(f); workbook.write(fos); fos.flush(); fos.close(); PortletResponseUtil.sendFile(request, response, question.getShortTitle().replaceAll("[^0-9A-Za-z]", "-") + ".xlsx", new FileInputStream(f)); } }
From source file:com.linus.excel.poi.AligningCells.java
License:Apache License
/** * Creates a cell and aligns it a certain way. * * @param wb the workbook//from w w w . ja v a 2 s . c o m * @param row the row to create the cell in * @param column the column number to create the cell in * @param halign the horizontal alignment for the cell. */ private static void createCell(Workbook wb, Row row, short column, short halign, short valign) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column); cell.setCellValue(ch.createRichTextString("Align It")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cell.setCellStyle(cellStyle); }
From source file:com.linus.excel.poi.MergingCells.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue(new XSSFRichTextString("This is a test of merging")); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2)); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("excel/merging_cells.xlsx"); wb.write(fileOut);/*from w w w . ja v a 2 s . c o m*/ fileOut.close(); }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (String arg : args) { if (arg.charAt(0) == '-') { xlsx = arg.equals("-xlsx"); } else {// w ww. j av a2 s .c o m calendar.set(Calendar.YEAR, Integer.parseInt(arg)); } } int year = calendar.get(Calendar.YEAR); try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) { Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); } } }
From source file:com.luna.showcase.excel.service.ExcelDataService.java
License:Apache License
/** * workbook/*ww w . j ava 2 s. c o m*/ * 1?vbs ? * 2?c#?? * ? ????office 2007 ? * @param user * @param contextRootPath * @param searchable */ @Async public void exportExcel2003WithOneSheetPerWorkBook(final User user, final String contextRootPath, final Searchable searchable) { int workbookCount = 0; List<String> workbookFileNames = new ArrayList<String>(); int perSheetRows = 60000; //?sheet 6w? int totalRows = 0; String extension = "xls"; int pageSize = 1000; Long maxId = 0L; BufferedOutputStream out = null; try { long beginTime = System.currentTimeMillis(); while (true) { workbookCount++; String fileName = generateFilename(user, contextRootPath, workbookCount, extension); workbookFileNames.add(fileName); File file = new File(fileName); HSSFWorkbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row headerRow = sheet.createRow(0); Cell idHeaderCell = headerRow.createCell(0); idHeaderCell.setCellValue("?"); Cell contentHeaderCell = headerRow.createCell(1); contentHeaderCell.setCellValue(""); totalRows = 1; Page<ExcelData> page = null; do { searchable.setPage(0, pageSize); // if (!searchable.containsSearchKey("id_in")) { searchable.addSearchFilter("id", SearchOperator.gt, maxId); } page = findAll(searchable); for (ExcelData data : page.getContent()) { Row row = sheet.createRow(totalRows); Cell idCell = row.createCell(0); idCell.setCellValue(data.getId()); Cell contentCell = row.createCell(1); contentCell.setCellValue(data.getContent()); maxId = Math.max(maxId, data.getId()); totalRows++; } //clear entity manager RepositoryHelper.clear(); } while (page.hasNextPage() && totalRows <= perSheetRows); out = new BufferedOutputStream(new FileOutputStream(file)); wb.write(out); IOUtils.closeQuietly(out); if (!page.hasNextPage()) { break; } } String fileName = workbookFileNames.get(0); if (workbookCount > 1 || needCompress(new File(fileName))) { fileName = fileName.substring(0, fileName.lastIndexOf("_")) + ".zip"; // compressAndDeleteOriginal(fileName, workbookFileNames.toArray(new String[0])); } else { String newFileName = fileName.substring(0, fileName.lastIndexOf("_")) + "." + extension; FileUtils.moveFile(new File(fileName), new File(newFileName)); fileName = newFileName; } long endTime = System.currentTimeMillis(); Map<String, Object> context = Maps.newHashMap(); context.put("seconds", (endTime - beginTime) / 1000); context.put("url", fileName.replace(contextRootPath, "")); notificationApi.notify(user.getId(), "excelExportSuccess", context); } catch (Exception e) { e.printStackTrace(); // IOUtils.closeQuietly(out); log.error("excel export error", e); Map<String, Object> context = Maps.newHashMap(); context.put("error", e.getMessage()); notificationApi.notify(user.getId(), "excelExportError", context); } }
From source file:com.luna.showcase.excel.service.ExcelDataService.java
License:Apache License
/** * excel 2003// ww w .j ava 2 s . c o m * ???? * ?sheet65536(usermodel? ?flush ????) * @param user * @param contextRootPath * @param searchable */ @Async public void exportExcel2003WithUsermodel(final User user, final String contextRootPath, final Searchable searchable) { int perSheetRows = 60000; //?sheet 6w? int totalRows = 0; Long maxId = 0L; String fileName = generateFilename(user, contextRootPath, "xls"); File file = new File(fileName); BufferedOutputStream out = null; try { long beginTime = System.currentTimeMillis(); HSSFWorkbook wb = new HSSFWorkbook(); while (true) { Sheet sheet = wb.createSheet(); Row headerRow = sheet.createRow(0); Cell idHeaderCell = headerRow.createCell(0); idHeaderCell.setCellValue("?"); Cell contentHeaderCell = headerRow.createCell(1); contentHeaderCell.setCellValue(""); totalRows = 1; Page<ExcelData> page = null; do { searchable.setPage(0, pageSize); // if (!searchable.containsSearchKey("id_in")) { searchable.addSearchFilter("id", SearchOperator.gt, maxId); } page = findAll(searchable); for (ExcelData data : page.getContent()) { Row row = sheet.createRow(totalRows); Cell idCell = row.createCell(0); idCell.setCellValue(data.getId()); Cell contentCell = row.createCell(1); contentCell.setCellValue(data.getContent()); maxId = Math.max(maxId, data.getId()); totalRows++; } //clear entity manager RepositoryHelper.clear(); } while (page.hasNextPage() && totalRows <= perSheetRows); if (!page.hasNextPage()) { break; } } out = new BufferedOutputStream(new FileOutputStream(file)); wb.write(out); IOUtils.closeQuietly(out); if (needCompress(file)) { fileName = compressAndDeleteOriginal(fileName); } long endTime = System.currentTimeMillis(); Map<String, Object> context = Maps.newHashMap(); context.put("seconds", (endTime - beginTime) / 1000); context.put("url", fileName.replace(contextRootPath, "")); notificationApi.notify(user.getId(), "excelExportSuccess", context); } catch (Exception e) { IOUtils.closeQuietly(out); log.error("excel export error", e); Map<String, Object> context = Maps.newHashMap(); context.put("error", e.getMessage()); notificationApi.notify(user.getId(), "excelExportError", context); } }