List of usage examples for org.apache.poi.ss.usermodel Sheet setDefaultColumnWidth
void setDefaultColumnWidth(int width);
From source file:de.quamoco.qm.editor.export.ResultCalibrationExporter.java
License:Apache License
/** Create the sheet to display the {@link MeasureEvaluation}s. */ private void createMeasureEvaluationSheet(Workbook workbook, IProgressMonitor monitor) throws IOException { checkNumberOfColumns(workbook, measureEvaluations.size() + 1); Sheet sheet = workbook.createSheet("Measures"); sheet.setDefaultColumnWidth(30); int header = createMeasureEvaluationSheetHeader(sheet); createMeasureEvaluationSheetBody(sheet, header, monitor); autoSizeColumns(sheet);//from ww w. j a v a 2 s . co m }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//* w w w. j av a 2 s. co m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { LOGGER.debug("testWriteExcelFileAttribute start...."); short rowheight = 40 * 10; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xls"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(CellStyle.ALIGN_RIGHT); cs.setFillPattern(CellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); assertEquals(CellStyle.ALIGN_RIGHT, cs1.getAlignment()); LOGGER.debug("getFillPattern : {}", cs1.getFillPattern()); assertEquals(CellStyle.DIAMONDS, cs1.getFillPattern()); LOGGER.debug("getFillForegroundColor : {}", cs1.getFillForegroundColor()); LOGGER.debug("getFillBackgroundColor : {}", cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testWriteExcelFileAttribute end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//* w w w . j av a 2 s. co m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }
From source file:eleanalysis.SampleLibrary.java
/** * writes a report in the form of an excel spreadsheet that is exported. * Stage variable is for using a filechooser to pick where to save file. * @param myStage Stage is for saving file using FileChooser * @throws FileNotFoundException /* www . j a v a 2s . co m*/ */ public void writeReport(Stage myStage) throws FileNotFoundException { FileChooser pickFile = new FileChooser(); pickFile.setInitialDirectory(new File("C:\\Users\\Yan\\Documents\\NetBeansProjects\\EleAnalysis\\")); pickFile.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("XLS", "*.xls"), new FileChooser.ExtensionFilter("XLSX", "*.xlsx")); File writeFile = pickFile.showSaveDialog(myStage); FileOutputStream fileOut; fileOut = new FileOutputStream(writeFile); Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("WDXRF"); sheet1.setDefaultColumnWidth(15); // Create a cell space for (int i = 0; i < ElementUtils.skf.length + 5; i++) { Row tempR = sheet1.createRow(i); for (int j = 0; j <= array.size(); j++) { Cell tempC = tempR.createCell(j); } } CellStyle csCenter = wb.createCellStyle(); csCenter.setAlignment(CellStyle.ALIGN_CENTER); csCenter.setBorderTop(CellStyle.BORDER_THIN); csCenter.setBorderLeft(CellStyle.BORDER_THIN); csCenter.setBorderRight(CellStyle.BORDER_THIN); csCenter.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csRight = wb.createCellStyle(); csRight.setAlignment(CellStyle.ALIGN_RIGHT); csRight.setBorderTop(CellStyle.BORDER_THIN); csRight.setBorderLeft(CellStyle.BORDER_THIN); csRight.setBorderRight(CellStyle.BORDER_THIN); csRight.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csLeft = wb.createCellStyle(); csLeft.setAlignment(CellStyle.ALIGN_LEFT); csLeft.setBorderTop(CellStyle.BORDER_THIN); csLeft.setBorderLeft(CellStyle.BORDER_THIN); csLeft.setBorderRight(CellStyle.BORDER_THIN); csLeft.setBorderBottom(CellStyle.BORDER_THIN); //Top Row sheet1.getRow(0).setHeightInPoints(25); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, array.size())); sheet1.getRow(0).getCell(0).setCellValue("WDXRF Analysis"); sheet1.getRow(0).getCell(0).setCellStyle(csCenter); sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, array.size())); //Second Row sheet1.getRow(1).getCell(0).setCellValue("Conc as Wt%"); sheet1.getRow(1).getCell(0).setCellStyle(csCenter); // Third Row sheet1.getRow(2).setHeightInPoints(35); sheet1.getRow(2).getCell(0).setCellValue("Common Oxides/Oxication States"); sheet1.getRow(2).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(2).getCell(j).setCellStyle(csLeft); sheet1.getRow(2).getCell(j).setCellValue(array.get(j - 1).getName()); } //Fourth Row sheet1.getRow(3).getCell(0).setCellValue("% Detectable"); sheet1.getRow(3).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(3).getCell(j).setCellValue("0.00"); sheet1.getRow(3).getCell(j).setCellStyle(csLeft); } //Fifth Row sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, array.size())); sheet1.getRow(4).getCell(0).setCellValue("Results Normalized with Respect to Detectable Concentration"); sheet1.getRow(4).getCell(0).setCellStyle(csCenter); //Rows 6 and beyond. Prints element list and defaults values to 0 for (int i = 5; i < ElementUtils.skf.length + 5; i++) { sheet1.getRow(i).getCell(0).setCellValue(ElementUtils.skf[i - 5]); sheet1.getRow(i).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(i).getCell(j).setCellValue("0.0"); sheet1.getRow(i).getCell(j).setCellStyle(csRight); } } // Copies values in SampleLibrary array into report for (int i = 0; i < array.size(); i++) { List<Element> eleArray = array.get(i).getArrayCopy(); for (int j = 0; j < ElementUtils.skf.length; j++) { for (int k = 0; k < eleArray.size(); k++) { if (ElementUtils.skf[j].contains(eleArray.get(k).getBaseElement())) sheet1.getRow(j + 5).getCell(i + 1).setCellValue(eleArray.get(k).getConcWeight()); } } } try { wb.write(fileOut); wb.close(); fileOut.close(); } catch (IOException ex) { Logger.getLogger(SampleLibrary.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.eclipse.emfforms.internal.spreadsheet.core.renderer.EMFFormsSpreadsheetControlRenderer.java
License:Open Source License
private static void setupSheetFormat(final Sheet sheet) { sheet.setDefaultColumnWidth(30); // do not scroll the first column (id) and the three top rows (label+info) sheet.createFreezePane(1, 3);// www.java2 s . c o m }
From source file:org.joeffice.spreadsheet.csv.SmartCsvReader.java
License:Apache License
public Workbook read(File csvFile) throws IOException { detect(csvFile);// w ww.ja v a 2s . co m Workbook csvWorkbook = new CSVWorkbook(this); Sheet csvSheet = csvWorkbook.createSheet(csvFile.getName()); Reader csvReader = new BufferedReader(new InputStreamReader(new FileInputStream(csvFile), getCharset())); ResultSet rs = csvMetadata.read(csvReader, getHeaders()); try { ResultSetMetaData meta = rs.getMetaData(); int rowIndex = 0; // First row contains the headers while (rs.next()) { Row dataRow = csvSheet.createRow(rowIndex); for (int i = 0; i < meta.getColumnCount(); i++) { Cell dataCell = dataRow.createCell(i); String cellValue = rs.getString(i + 1); try { double cellNumericValue = Double.parseDouble(cellValue); dataCell.setCellValue(cellNumericValue); } catch (NumberFormatException ex) { dataCell.setCellValue(cellValue); } } rowIndex++; } rs.close(); } catch (SQLException ex) { Exceptions.printStackTrace(ex); } csvSheet.setDefaultColumnWidth(-1); return csvWorkbook; }
From source file:org.opentestsystem.delivery.testreg.rest.view.ExcelView.java
License:Open Source License
@SuppressWarnings("unchecked") @Override/*from w ww. ja v a2s .c o m*/ protected void buildExcelDocument(final Map<String, Object> model, final HSSFWorkbook workbook, final HttpServletRequest request, final HttpServletResponse response) { // get data model which is passed by the Spring container final List<TestRegistrationBase> entityList = (List<TestRegistrationBase>) model.get(DATA_LIST); final String formatType = ((String) model.get(FORMAT_TYPE)).toUpperCase(); String exportType = null; if (formatType.equalsIgnoreCase(FormatType.STUDENT.name())) { exportType = ((String) model.get(EXPORT_TYPE)).toUpperCase(); HSSFName name = workbook.createName(); // display filename to return to the client name.setNameName(exportType); } final Sheet sheet = workbook.createSheet(formatType); sheet.setDefaultColumnWidth(30); String headerColumns[] = new String[100]; if (formatType.equalsIgnoreCase(FormatType.STUDENT.name())) { // Based on export type for FormatType( STUDENT ) assigning headers for export file if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.studentsPlusAccommodations.name())) { List<String> headerStudentList = templateDownloadMap.get(FormatType.STUDENT); String[] headerCodesTemp = headersMap.get(FormatType.DESIGNATEDSUPPORTSANDACCOMMODATIONS.name()); List<String> headerCodes = Arrays.asList(headerCodesTemp); List<String> headerAccommodationList = headerCodes.subList(2, headerCodes.size()); String lastElement = headerStudentList.get(headerStudentList.size() - 1); List<String> combinedHeaders = ListUtils .union(headerStudentList.subList(0, headerStudentList.size() - 1), headerAccommodationList); combinedHeaders.add(lastElement); headerColumns = combinedHeaders.toArray(new String[combinedHeaders.size()]); } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.studentsOnly.name())) { List<String> headerStudentList = templateDownloadMap.get(FormatType.STUDENT); headerColumns = headerStudentList.toArray(new String[headerStudentList.size()]); } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.accommodationsOnly.name())) { headerColumns = headersMap.get(FormatType.DESIGNATEDSUPPORTSANDACCOMMODATIONS.name()); } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.explicitEligibility.name())) { List<String> headerExplicitEligibilityList = templateDownloadMap .get(FormatType.EXPLICITELIGIBILITY); headerColumns = headerExplicitEligibilityList .toArray(new String[headerExplicitEligibilityList.size()]); } } else { headerColumns = this.headersMap.get(formatType); } final Row header = sheet.createRow(0); for (int i = 0; i < headerColumns.length; i++) { final Cell cell = header.createCell(i); cell.setCellValue(headerColumns[i]); } // DataRow Begin if (!CollectionUtils.isEmpty(entityList)) { int rowCount = 1; for (final TestRegistrationBase entity : entityList) { // if format is user then we need to customize export to include role associations as separate rows switch (FormatType.valueOf(formatType)) { case USER: rowCount = exportUser(sheet, rowCount, entity); break; case STUDENTGROUP: rowCount = exportStudentGroup(sheet, rowCount, entity); break; case STUDENT: rowCount = exportStudent(sheet, rowCount, (Student) entity, exportType); break; default: final Row rowData = sheet.createRow(rowCount); final String[] columnData = this.testRegistrationViewTransformer.transform(entity); addData(rowData, 0, columnData.length, columnData); } rowCount++; } } }
From source file:test.poi.MyExcelDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);/* ww w. j a v a 2 s . c om*/ sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("XX????20130506-20140503"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$O$1")); //header row Row headerRow = sheet.createRow(1); sheet.setDefaultColumnWidth(20); headerRow.setHeightInPoints(20); Cell headerCell; for (int i = 1; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); CellRangeAddress cra = new CellRangeAddress(1, 2, i, i); sheet.addMergedRegion(cra); setBorder(cra, sheet, wb); headerCell.setCellStyle(styles.get("header")); } // Write the output to a file String file = "E:\\test.xls"; // if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }