List of usage examples for org.apache.poi.ss.usermodel Sheet createFreezePane
void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow);
From source file:cn.afterturn.easypoi.excel.export.ExcelExportService.java
License:Apache License
protected int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook, List<ExcelExportEntity> excelParams) { int rows = 0, fieldLength = getFieldLength(excelParams); if (entity.getTitle() != null) { rows += createTitle2Row(entity, sheet, workbook, fieldLength); }//from www . ja v a2s .com createHeaderRow(entity, sheet, workbook, rows, excelParams, 0); rows += getRowNums(excelParams, true); if (entity.isFixedTitle()) { sheet.createFreezePane(0, rows, 0, rows); } return rows; }
From source file:cn.afterturn.easypoi.excel.export.ExcelExportService.java
License:Apache License
protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList, Collection<?> dataSet, Sheet sheet) { try {//from w ww .j a v a 2 s. c om dataHandler = entity.getDataHandler(); if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) { needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields()); } dictHandler = entity.getDictHandler(); i18nHandler = entity.getI18nHandler(); // ? setExcelExportStyler( (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } excelParams.addAll(entityList); sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); setColumnHidden(excelParams, sheet); short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams); setCurrentIndex(1); Iterator<?> its = dataSet.iterator(); List<Object> tempList = new ArrayList<Object>(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0]; tempList.add(t); if (index >= MAX_NUM) { break; } } if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } mergeCells(sheet, excelParams, titleHeight); its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i < le; i++) { its.next(); its.remove(); } if (LOGGER.isDebugEnabled()) { LOGGER.debug("List data more than max ,data size is {}", dataSet.size()); } // ?list Sheet if (dataSet.size() > 0) { createSheetForMap(workbook, entity, entityList, dataSet); } else { // ?? addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet); } } catch (Exception e) { LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } }
From source file:cn.bzvs.excel.export.ExcelExportServer.java
License:Apache License
protected int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook, List<ExcelExportEntity> excelParams) { int rows = 0, feildWidth = getFieldLength(excelParams); if (entity.getTitle() != null) { rows += createHeaderRow(entity, sheet, workbook, feildWidth); }/* ww w . j ava 2s . c o m*/ rows += createTitleRow(entity, sheet, workbook, rows, excelParams); sheet.createFreezePane(0, rows, 0, rows); return rows; }
From source file:cn.bzvs.excel.export.ExcelExportServer.java
License:Apache License
protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList, Collection<?> dataSet, Sheet sheet) { try {/* w w w . j av a 2s.com*/ dataHanlder = entity.getDataHanlder(); if (dataHanlder != null && dataHanlder.getNeedHandlerFields() != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } // ? setExcelExportStyler( (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = sheet.createDrawingPatriarch(); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } excelParams.addAll(entityList); sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); short rowHeight = getRowHeight(excelParams); setCurrentIndex(1); Iterator<?> its = dataSet.iterator(); List<Object> tempList = new ArrayList<Object>(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); tempList.add(t); if (index >= MAX_NUM) break; } if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } mergeCells(sheet, excelParams, titleHeight); its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i < le; i++) { its.next(); its.remove(); } // ?list Sheet if (dataSet.size() > 0) { createSheetForMap(workbook, entity, entityList, dataSet); } else { // ?? addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet); } } catch (Exception e) { e.getMessage(); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } }
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
@SuppressWarnings("squid:S3776") protected Workbook createSpreadsheet(ManagedProcess report) { Workbook wb = new XSSFWorkbook(); String name = report.getName(); for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) { name = StringUtils.remove(name, ch); }//from w w w.j a va 2 s.c om Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); CellStyle dateStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss")); for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) { Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells()); headerCell.setCellValue(columnName); headerCell.setCellStyle(headerStyle); } Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList(); //make rows, don't forget the header row for (ArchivedProcessFailure error : rows) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); Cell c; c = row.createCell(0); c.setCellValue(error.time); c.setCellStyle(dateStyle); c = row.createCell(1); c.setCellValue(error.nodePath); c = row.createCell(2); c.setCellValue(error.error); c = row.createCell(3); c.setCellValue(error.stackTrace); } autosize(sheet, 4); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3)); return wb; }
From source file:com.ipcglobal.fredimport.xls.DistinctCategoriesSpreadsheet.java
License:Apache License
/** * Creates the sheet.//www. j av a 2 s . c om * * @param distinctCategoryItems the distinct category items * @throws Exception the exception */ public void createSheet(Collection<DistinctCategoryItem> distinctCategoryItems) throws Exception { List<XlsDefItem> xlsDefItems = initHhdrWidthItems(); String sheetName = "DistinctCategoryItems"; Sheet sheet = wb.createSheet(sheetName); processColumnWidths(sheet, xlsDefItems); sheet.createFreezePane(0, 1, 0, 1); // freeze top row sheet.setAutoFilter(CellRangeAddress.valueOf("A1:AB1")); // hack - i know the number of columns sheet.getPrintSetup().setLandscape(true); sheet.setAutobreaks(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 1); int rowCnt = 0; // Header int colCnt = 0; Row rowHdr = sheet.createRow(rowCnt); for (XlsDefItem xlsDefItem : xlsDefItems) { Cell cellHdr = rowHdr.createCell(colCnt, Cell.CELL_TYPE_STRING); CellStyle style = findCellStyle("Arial", HSSFColor.WHITE.index, (short) 11, XSSFFont.BOLDWEIGHT_BOLD, cellStyleFromHdrAlign(HdrAlign.Left), XSSFCellStyle.VERTICAL_TOP, HSSFColor.LIGHT_BLUE.index, CellBorder.All_Thin, formatGeneral); style.setWrapText(true); cellHdr.setCellStyle(style); cellHdr.setCellValue(xlsDefItem.getName()); colCnt++; } rowCnt++; // Data for (DistinctCategoryItem distinctCategoryItem : distinctCategoryItems) { Row rowData = sheet.createRow(rowCnt); int colNum = 0; for (XlsDefItem xlsDefItem : xlsDefItems) populateCell(rowData, colNum++, xlsDefItem.getDataType(), getByNameAsString(distinctCategoryItem, xlsDefItem.getName())); rowCnt++; } }
From source file:com.qihang.winter.poi.excel.export.ExcelExportServer.java
License:Apache License
private int createHeaderAndTitle(ExportParams entity, Sheet sheet, Workbook workbook, List<ExcelExportEntity> excelParams) { int rows = 0, feildWidth = getFieldWidth(excelParams); if (entity.getTitle() != null) { rows += createHeaderRow(entity, sheet, workbook, feildWidth); }/* w ww . j a va2 s . c o m*/ rows += createTitleRow(entity, sheet, workbook, rows, excelParams); sheet.createFreezePane(0, rows, 0, rows); return rows; }
From source file:com.qihang.winter.poi.excel.export.ExcelExportServer.java
License:Apache License
public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Excel export start ,class is {}", pojoClass); LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07"); }//from w w w .j a v a 2 s . c o m if (workbook == null || entity == null || pojoClass == null || dataSet == null) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } super.type = entity.getType(); if (type.equals(ExcelType.XSSF)) { MAX_NUM = 1000000; } Sheet sheet = null; try { sheet = workbook.createSheet(entity.getSheetName()); } catch (Exception e) { // ????,???,???Sheet sheet = workbook.createSheet(); } try { dataHanlder = entity.getDataHanlder(); if (dataHanlder != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } // ? setExcelExportStyler((com.qihang.winter.poi.excel.export.styler.IExcelExportStyler) entity.getStyle() .getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = sheet.createDrawingPatriarch(); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } // Field fileds[] = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); String targetId = etarget == null ? null : etarget.value(); getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null); sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); short rowHeight = getRowHeight(excelParams); setCurrentIndex(1); Iterator<?> its = dataSet.iterator(); List<Object> tempList = new ArrayList<Object>(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); tempList.add(t); if (index >= MAX_NUM) break; } mergeCells(sheet, excelParams, titleHeight); if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i < le; i++) { its.next(); its.remove(); } // ?? addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet); // ?list Sheet if (dataSet.size() > 0) { createSheet(workbook, entity, pojoClass, dataSet); } } catch (Exception e) { LOGGER.error(e.getMessage(), e); LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } }
From source file:com.qihang.winter.poi.excel.export.ExcelExportServer.java
License:Apache License
public void createSheetForMap(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList, Collection<? extends Map<?, ?>> dataSet) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07"); }/*from w w w . j a v a 2s .c o m*/ if (workbook == null || entity == null || entityList == null || dataSet == null) { throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR); } super.type = entity.getType(); if (type.equals(ExcelType.XSSF)) { MAX_NUM = 1000000; } Sheet sheet = null; try { sheet = workbook.createSheet(entity.getSheetName()); } catch (Exception e) { // ????,???,???Sheet sheet = workbook.createSheet(); } try { dataHanlder = entity.getDataHanlder(); if (dataHanlder != null) { needHanlderList = Arrays.asList(dataHanlder.getNeedHandlerFields()); } // ? setExcelExportStyler((com.qihang.winter.poi.excel.export.styler.IExcelExportStyler) entity.getStyle() .getConstructor(Workbook.class).newInstance(workbook)); Drawing patriarch = sheet.createDrawingPatriarch(); List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>(); if (entity.isAddIndex()) { excelParams.add(indexExcelEntity(entity)); } excelParams.addAll(entityList); sortAllParams(excelParams); int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0; int titleHeight = index; setCellWith(excelParams, sheet); short rowHeight = getRowHeight(excelParams); setCurrentIndex(1); Iterator<?> its = dataSet.iterator(); List<Object> tempList = new ArrayList<Object>(); while (its.hasNext()) { Object t = its.next(); index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight); tempList.add(t); if (index >= MAX_NUM) break; } if (entity.getFreezeCol() != 0) { sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0); } mergeCells(sheet, excelParams, titleHeight); its = dataSet.iterator(); for (int i = 0, le = tempList.size(); i < le; i++) { its.next(); its.remove(); } // ?list Sheet if (dataSet.size() > 0) { createSheetForMap(workbook, entity, entityList, dataSet); } } catch (Exception e) { LOGGER.error(e.getMessage(), e); throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause()); } }
From source file:contestTabulation.Setup.java
License:Open Source License
@Override public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException { HttpTransport httpTransport = new NetHttpTransport(); JacksonFactory jsonFactory = new JacksonFactory(); DatastoreService datastore = DatastoreServiceFactory.getDatastoreService(); Entity contestInfo = Retrieve.contestInfo(); GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory) .setTransport(httpTransport) .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"), (String) contestInfo.getProperty("OAuth2ClientSecret")) .build().setFromTokenResponse(new JacksonFactory().fromString( ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class)); String docName = null, docLevel = null; for (Level level : Level.values()) { docName = req.getParameter("doc" + level.getName()); if (docName != null) { docLevel = level.toString(); break; }//from w ww . j ava2 s . c o m } if (docLevel == null) { resp.sendError(HttpServletResponse.SC_BAD_REQUEST, "Spreadsheet creation request must have paramater document name parameter set"); return; } Query query = new Query("registration") .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel)) .addSort("schoolName", SortDirection.ASCENDING); List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults()); Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>(); for (Entity registration : registrations) { String regSchoolName = ((String) registration.getProperty("schoolName")).trim(); String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue()); JSONArray regStudentData = null; try { regStudentData = new JSONArray(regStudentDataJSON); } catch (JSONException e) { e.printStackTrace(); resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString()); return; } for (int i = 0; i < regStudentData.length(); i++) { if (!studentData.containsKey(regSchoolName)) { studentData.put(regSchoolName, new ArrayList<JSONObject>()); } try { studentData.get(regSchoolName).add(regStudentData.getJSONObject(i)); } catch (JSONException e) { resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR); e.printStackTrace(); return; } } } for (List<JSONObject> students : studentData.values()) { Collections.sort(students, new Comparator<JSONObject>() { @Override public int compare(JSONObject a, JSONObject b) { try { return a.getString("name").compareTo(b.getString("name")); } catch (JSONException e) { e.printStackTrace(); return 0; } } }); } Workbook workbook = new XSSFWorkbook(); XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle(); Font boldFont = workbook.createFont(); boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD); boldStyle.setFont(boldFont); Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>(); for (Subject subject : Subject.values()) { final double ALPHA = .144; String colorStr = (String) contestInfo.getProperty("color" + subject.getName()); byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(), Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(), Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() }; // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)), (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)), (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) }; XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle(); style.setFillBackgroundColor(new XSSFColor(backgroundColor)); style.setFillPattern(CellStyle.ALIGN_FILL); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(new XSSFColor(borderColor)); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(new XSSFColor(borderColor)); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(new XSSFColor(borderColor)); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(new XSSFColor(borderColor)); subjectCellStyles.put(subject, style); } Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {}); Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() { @Override public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) { return Integer.compare(arg0.getValue().size(), arg1.getValue().size()); } })); for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) { Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey())); Row row = sheet.createRow((short) 0); String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" }; for (int i = 0; i < columnNames.length; i++) { String columnName = columnNames[i]; Cell cell = row.createCell(i); cell.setCellValue(columnName); cell.setCellStyle(boldStyle); CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER); } int longestNameLength = 7; int rowNum = 1; for (JSONObject student : studentDataEntry.getValue()) { try { row = sheet.createRow((short) rowNum); row.createCell(0).setCellValue(student.getString("name")); row.createCell(1).setCellValue(student.getInt("grade")); for (Subject subject : Subject.values()) { String value = student.getBoolean(subject.toString()) ? "" : "X"; Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString())); cell.setCellValue(value); cell.setCellStyle(subjectCellStyles.get(subject)); } if (student.getString("name").length() > longestNameLength) { longestNameLength = student.getString("name").length(); } rowNum++; } catch (JSONException e) { e.printStackTrace(); resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString()); return; } } sheet.createFreezePane(0, 1, 0, 1); // sheet.autoSizeColumn((short) 0); Not supported by App Engine sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1)); } Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential) .setApplicationName("contestTabulation").build(); File body = new File(); body.setTitle(docName); body.setMimeType("application/vnd.google-apps.spreadsheet"); ByteArrayOutputStream outStream = new ByteArrayOutputStream(); workbook.write(outStream); ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray()); InputStreamContent content = new InputStreamContent( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream); drive.files().insert(body, content).execute(); workbook.close(); }