List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
public void generateExcelFile() { Row row;/*from w ww. j ava 2s . c om*/ Cell cell; int yOffset = 1; File workingDirectory = new File( (workingDirectoryPath.isEmpty() ? "" : (workingDirectoryPath + File.separatorChar)) + evaluationUID); if (!workingDirectory.exists()) { workingDirectory.mkdir(); } File file = new File(workingDirectory, "auswertung.xls"); try { FileOutputStream out = new FileOutputStream(file); Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Evaluation"); // configure cell styles configureCellStyles(wb); /* * ********************************** * begin formatting document * ********************************** * */ //construct first row of infopanel yOffset = constructInfoPanelRow("Lehrveranstaltung", subject, numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.topLeftCorner, InfoPanelBorderStyles.top, InfoPanelBorderStyles.topRightCorner); //construct second row of infopanel yOffset = constructInfoPanelRow("Semester", semesterType == SemesterType.WINTER ? "Winter" : "Sommer", numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct third row of infopanel StringBuilder tutors = new StringBuilder(); for (int i = 0; i < this.tutors.size(); i++) { if (i + 1 < this.tutors.size()) { tutors.append(this.tutors.get(i)).append(", "); } else { tutors.append(this.tutors.get(i)); } } yOffset = constructInfoPanelRow("Lehrende(r)", tutors.toString(), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fourth row of infopanel yOffset = constructInfoPanelRow("Datum der Befragung", dateOfEvaluation.toString("dd.MM.yy HH:mm"), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fifth row of infopanel yOffset = constructInfoPanelRow("Anzahl der Teilnehmer", Integer.toString(numberStudentsAll), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct sixth row of infopanel () last yOffset = constructInfoPanelRow("Anzahl der ausgefllten Fragebgen", Integer.toString(numberStudentsVoted), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.bottomLeftCorner, InfoPanelBorderStyles.bottom, InfoPanelBorderStyles.bottomRightCorner); //begin construction of evaluationPanel yOffset++; row = sheet.createRow(yOffset); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellValue("Frage"); cell.setCellStyle(headerStyle); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellValue("MW"); cell.setCellStyle(headerStyle); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellValue("Ifd NR."); cell.setCellStyle(headerStyle); cell = row.createCell(4); cell.setCellStyle(commonStyle); // add count of valid evaluations (how many students voted) (horizontal) for (int i = 0; i < numberStudentsVoted; i++) { cell = row.createCell(i + 5); cell.setCellValue(i + 1); sheet.setColumnWidth(cell.getColumnIndex(), 4 * 256); cell.setCellStyle(headerStyle); } // get letter of last student column CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); String endCellName = cellReference.getCellRefParts()[2]; Row headRow = row; for (int i = 1; i < mcQuestionTexts.size() + 1; i++) { //add number of questions row = sheet.createRow(i + yOffset); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(i); cell.setCellStyle(commonStyle); //add average formula cell = row.createCell(2, Cell.CELL_TYPE_FORMULA); //formlua works with blanks, empty strings and negative values String formula = "SUMPRODUCT(ABS(N(+F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))/COUNT(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")"; //String averageFormula = "AVERAGE(IF(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + "<>\"\", ABS(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))"; cell.setCellFormula(formula); cell.setCellStyle(commonStyle); //fill blank cells cell = row.createCell(3); cell.setCellStyle(commonStyle); //add question texts sheet.setColumnWidth(4, findLongestString(mcQuestionTexts) * 256 * (wb.getFontAt(questionStyle.getFontIndex()).getFontHeightInPoints()) / 10); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellValue(mcQuestionTexts.get(i - 1)); cell.setCellStyle(questionStyle); } //add student votes for (int i = 0; i < studentVotes.size(); i++) { Vote vote = studentVotes.get(i); for (int k = 0; k < mcQuestionTexts.size(); k++) { row = sheet.getRow(headRow.getRowNum() + 1 + k); cell = row.createCell(5 + i); for (MCAnswer answer : vote.getMcAnswers()) { //if question of inner loop equals question of outer loop we found // the correct question for this cell if (answer.getQuestion().getText().equals(mcQuestionTexts.get(k))) { Choice choice = answer.getChoice(); if (choice != null && choice.getGrade() != 0) { cell = colorizeCell(cell, wb, choice.getGrade()); cell.setCellValue(answer.getChoice().getGrade()); } else { cell = colorizeCell(cell, wb, -1); cell.setCellValue(""); } } } } } // include textual answers createTextualAnswers(studentVotes, textualQuestionTexts, sheet, wb); wb.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }
From source file:demo.poi.AddDimensionedImage.java
License:Apache License
/** * Determines whether the sheets columns should be re-sized to accomodate * the image, adjusts the columns width if necessary and creates then * returns a ClientAnchorDetail object that facilitates construction of an * ClientAnchor that will fix the image on the sheet and establish it's * size./*from w ww . j ava 2 s . c o m*/ * * @param sheet * A reference to the sheet that will 'contain' the image. * @param colNumber * A primtive int that contains the index number of a column on * the sheet. * @param reqImageWidthMM * A primitive double that contains the required width of the * image in millimetres * @param resizeBehaviour * A primitive int whose value will indicate how the width of the * column should be adjusted if the required width of the image * is greater than the width of the column. * @return An instance of the ClientAnchorDetail class that will contain the * index number of the column containing the cell whose top left * hand corner also defines the top left hand corner of the image, * the index number column containing the cell whose top left hand * corner also defines the bottom right hand corner of the image and * an inset that determines how far the right hand edge of the image * can protrude into the next column - expressed as a specific * number of coordinate positions. */ private ClientAnchorDetail fitImageToColumns(Sheet sheet, int colNumber, double reqImageWidthMM, int resizeBehaviour) { double colWidthMM = 0.0D; double colCoordinatesPerMM = 0.0D; int pictureWidthCoordinates = 0; ClientAnchorDetail colClientAnchorDetail = null; // Get the colum's width in millimetres colWidthMM = ConvertImageUnits.widthUnits2Millimetres((short) sheet.getColumnWidth(colNumber)); // Check that the column's width will accomodate the image at the // required dimension. If the width of the column is LESS than the // required width of the image, decide how the application should // respond - resize the column or overlay the image across one or more // columns. if (colWidthMM < reqImageWidthMM) { // Should the column's width simply be expanded? if ((resizeBehaviour == AddDimensionedImage.EXPAND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW_AND_COLUMN)) { // Set the width of the column by converting the required image // width from millimetres into Excel's column width units. sheet.setColumnWidth(colNumber, ConvertImageUnits.millimetres2WidthUnits(reqImageWidthMM)); // To make the image occupy the full width of the column, // convert // the required width of the image into co-ordinates. This value // will become the inset for the ClientAnchorDetail class that // is then instantiated. if (sheet instanceof HSSFSheet) { colWidthMM = reqImageWidthMM; colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); } else { pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM; } colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } // If the user has chosen to overlay both rows and columns or just // to expand ONLY the size of the rows, then calculate how to lay // the image out across one or more columns. else if ((resizeBehaviour == AddDimensionedImage.OVERLAY_ROW_AND_COLUMN) || (resizeBehaviour == AddDimensionedImage.EXPAND_ROW)) { colClientAnchorDetail = this.calculateColumnLocation(sheet, colNumber, reqImageWidthMM); } } // If the column is wider than the image. else { if (sheet instanceof HSSFSheet) { // Mow many co-ordinate positions are there per millimetre? colCoordinatesPerMM = ConvertImageUnits.TOTAL_COLUMN_COORDINATE_POSITIONS / colWidthMM; // Given the width of the image, what should be it's // co-ordinate? pictureWidthCoordinates = (int) (reqImageWidthMM * colCoordinatesPerMM); } else { pictureWidthCoordinates = (int) reqImageWidthMM * AddDimensionedImage.EMU_PER_MM; } colClientAnchorDetail = new ClientAnchorDetail(colNumber, colNumber, pictureWidthCoordinates); } return (colClientAnchorDetail); }
From source file:demo.poi.BusinessPlan.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("Business Plan"); // turn off gridlines sheet.setDisplayGridlines(false);//from w ww. j a v a 2 s . c o m 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(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } // columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } // freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { String str = data[i][j]; cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } // group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); // set column widths, the width is measured in units of 1/256th of a // character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "target/businessplan.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:demo.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from ww w . j a v a 2s . co m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "target/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:demo.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* w w w . jav a2 s . c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); // header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { // the 10th cell contains sum over week days, e.g. // SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } // row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); // set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet.setColumnWidth(0, 30 * 256); // 30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); // 6 characters wide } sheet.setColumnWidth(10, 10 * 256); // 10 characters wide // Write the output to a file String file = "target/timesheet.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:Demos.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {/*ww w . j av a 2s.c om*/ calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); 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 = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:egovframework.rte.fdl.excel.EgovExcelServiceTest.java
License:Apache License
/** * [Flow #-2] ? : ? ? ? ? ?// w ww.ja v a2 s . c o m */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xls"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new HSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? Workbook wb = excelService.loadWorkbook(sb.toString()); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cell = row.createCell(cellnum); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(new HSSFRichTextString(content)); cell.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-2] ? : ? ? ? ? ?//from w ww. j a v a2s . c om */ @Test public void testModifyCellContents() throws Exception { try { String content = "Use \n with word wrap on to create a new line"; short rownum = 2; int cellnum = 2; LOGGER.debug("testModifyCellContents start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx"); if (!EgovFileUtil.isExistsFile(sb.toString())) { Workbook wbT = new XSSFWorkbook(); wbT.createSheet(); // ? ? excelService.createWorkbook(wbT, sb.toString()); } // ? XSSFWorkbook wb = null; wb = excelService.loadWorkbook(sb.toString(), wb); LOGGER.debug("testModifyCellContents after loadWorkbook...."); Sheet sheet = wb.getSheetAt(0); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); //Word Wrap MUST be turned on cs.setWrapText(true); Row row = sheet.createRow(rownum); row.setHeight((short) 0x349); Cell cellx = row.createCell(cellnum); cellx.setCellType(XSSFCell.CELL_TYPE_STRING); cellx.setCellValue(new XSSFRichTextString(content)); cellx.setCellStyle(cs); sheet.setColumnWidth(20, (int) ((50 * 8) / ((double) 1 / 20))); //excelService.writeWorkbook(wb); FileOutputStream outx = new FileOutputStream(sb.toString()); wb.write(outx); outx.close(); // ? Workbook wb1 = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheet1 = wb1.getSheetAt(0); Row row1 = sheet1.getRow(rownum); Cell cell1 = row1.getCell(cellnum); // ? ? ? LOGGER.debug("cell ###{}###", cell1.getRichStringCellValue()); LOGGER.debug("cont ###{}###", content); assertNotSame("TEST", cell1.getRichStringCellValue().toString()); assertEquals(content, cell1.getRichStringCellValue().toString()); } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellContents end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelXSSFServiceTest.java
License:Apache License
/** * [Flow #-6] ? : ? ?(?, ? )? /*from w w w. j av a2 s .com*/ */ @Test public void testModifyCellAttribute() throws Exception { try { LOGGER.debug("testModifyCellAttribute start...."); StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testModifyCellAttribute.xlsx"); if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); LOGGER.debug("Delete file....{}", sb.toString()); } Workbook wbTmp = new XSSFWorkbook(); wbTmp.createSheet(); // ? ? excelService.createWorkbook(wbTmp, sb.toString()); // ? XSSFWorkbook wb = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); LOGGER.debug("testModifyCellAttribute after loadWorkbook...."); Sheet sheet = wb.createSheet("cell test sheet2"); sheet.setColumnWidth((short) 3, (short) 200); // column Width CellStyle cs = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontHeight(16); font.setBoldweight((short) 3); font.setFontName("fixedsys"); cs.setFont(font); cs.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // cell cs.setWrapText(true); for (int i = 0; i < 100; i++) { Row row = sheet.createRow(i); row.setHeight((short) 300); // row? height for (int j = 0; j < 5; j++) { Cell cell = row.createCell(j); cell.setCellValue(new XSSFRichTextString("row " + i + ", cell " + j)); cell.setCellStyle(cs); } } // ? FileOutputStream out = new FileOutputStream(sb.toString()); wb.write(out); out.close(); ////////////////////////////////////////////////////////////////////////// // ? XSSFWorkbook wbT = excelService.loadWorkbook(sb.toString(), new XSSFWorkbook()); Sheet sheetT = wbT.getSheet("cell test sheet2"); LOGGER.debug("getNumCellStyles : {}", wbT.getNumCellStyles()); XSSFCellStyle cs1 = (XSSFCellStyle) wbT.getCellStyleAt((short) (wbT.getNumCellStyles() - 1)); XSSFFont fontT = cs1.getFont(); LOGGER.debug("font getFontHeight : {}", fontT.getFontHeight()); LOGGER.debug("font getBoldweight : {}", fontT.getBoldweight()); LOGGER.debug("font getFontName : {}", fontT.getFontName()); LOGGER.debug("getAlignment : {}", cs1.getAlignment()); LOGGER.debug("getWrapText : {}", cs1.getWrapText()); for (int i = 0; i < 100; i++) { Row row1 = sheetT.getRow(i); for (int j = 0; j < 5; j++) { Cell cell1 = row1.getCell(j); LOGGER.debug("row {}, cell {} : {}", i, j, cell1.getRichStringCellValue()); assertEquals(320, fontT.getFontHeight()); assertEquals(400, fontT.getBoldweight()); LOGGER.debug("fontT.getBoldweight()? ? 400? ?"); assertEquals(XSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); assertTrue(cs1.getWrapText()); } } } catch (Exception e) { LOGGER.error(e.toString()); throw new Exception(e); } finally { LOGGER.debug("testModifyCellAttribute end...."); } }
From source file:eu.esdihumboldt.hale.io.xls.writer.XLSAlignmentMappingWriter.java
License:Open Source License
@Override protected IOReport execute(ProgressIndicator progress, IOReporter reporter) throws IOProviderConfigurationException, IOException { super.execute(progress, reporter); Workbook workbook;/* w ww . jav a 2s. co m*/ // write xls file if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xls")) { workbook = new HSSFWorkbook(); } // write xlsx file else if (getContentType().getId().equals("eu.esdihumboldt.hale.io.xls.xlsx")) { workbook = new XSSFWorkbook(); } else { reporter.error(new IOMessageImpl("Content type is invalid!", null)); reporter.setSuccess(false); return reporter; } Sheet sheet = workbook.createSheet(); workbook.setSheetName(0, "Mapping table"); Row row = null; Cell cell = null; // create cell style of the header CellStyle headerStyle = XLSCellStyles.getHeaderStyle(workbook); // create cell style CellStyle cellStyle = XLSCellStyles.getNormalStyle(workbook, false); // create highlight style for type cells CellStyle highlightStyle = XLSCellStyles.getHighlightedStyle(workbook, false); // create disabled style CellStyle disabledStyle = XLSCellStyles.getNormalStyle(workbook, true); // create disabled highlight style CellStyle disabledTypeStyle = XLSCellStyles.getHighlightedStyle(workbook, true); List<Map<CellType, CellInformation>> mapping = getMappingList(); // determine if cells are organized by type cell boolean byTypeCell = isByTypeCell(); int rownum = 0; // write header row = sheet.createRow(rownum++); for (int i = 0; i < getMappingHeader().size(); i++) { cell = row.createCell(i); cell.setCellValue(getMappingHeader().get(i)); cell.setCellStyle(headerStyle); } // write all mappings for (Map<CellType, CellInformation> entry : mapping) { boolean disabled = false; if (getParameter(TRANSFORMATION_AND_DISABLED_FOR).as(Boolean.class)) { List<String> transformationDisabled = entry.get(CellType.TRANSFORMATION_AND_DISABLED).getText(); disabled = !transformationDisabled.isEmpty() && !transformationDisabled.contains(TransformationMode.active.displayName()); } // create a row row = sheet.createRow(rownum); CellStyle rowStyle = cellStyle; String targetProp = getCellValue(entry, CellType.TARGET_PROPERTIES); boolean isTypeCell = targetProp == null || targetProp.isEmpty(); if (isTypeCell && byTypeCell) { // organized by type cells and this is a type cell if (disabled) { // disabled type cell rowStyle = disabledTypeStyle; } else { // normal type cell rowStyle = highlightStyle; } } else if (disabled) { // disabled property cell rowStyle = disabledStyle; } List<CellType> celltypes = getCellTypes(); for (int i = 0; i < celltypes.size(); i++) { cell = row.createCell(i); cell.setCellValue(getCellValue(entry, celltypes.get(i))); cell.setCellStyle(rowStyle); } rownum++; } // could be integrated in configuration page // int maxColWidth = calculateWidth(getParameter(MAX_COLUMN_WIDTH).as(Integer.class)); int maxColWidth = calculateWidth(maxWidth); // autosize all columns for (int i = 0; i < getMappingHeader().size(); i++) { sheet.autoSizeColumn(i); if (sheet.getColumnWidth(i) > maxColWidth) sheet.setColumnWidth(i, maxColWidth); } // write file FileOutputStream out = new FileOutputStream(getTarget().getLocation().getPath()); workbook.write(out); out.close(); reporter.setSuccess(true); return reporter; }