List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf
public static CellRangeAddress valueOf(String ref)
From source file:packtest.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> 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 XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);/*from w w w . j a v a 2 s . co m*/ XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell 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 XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width 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)); XSSFCell 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++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell 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 FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); wb.close(); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Multiple conditional formatting rules can apply to * one cell, some combining, some beating others. * Done in order of the rules added to the * SheetConditionalFormatting object//from w w w . jav a 2s . co m */ static void overlapping(Sheet sheet) { for (int i = 0; i < 40; i++) { int rn = i + 1; Row r = sheet.createRow(i); r.createCell(0).setCellValue("This is row " + rn + " (" + i + ")"); String str = ""; if (rn % 2 == 0) str = str + "even "; if (rn % 3 == 0) str = str + "x3 "; if (rn % 5 == 0) str = str + "x5 "; if (rn % 10 == 0) str = str + "x10 "; if (str.length() == 0) str = "nothing special..."; r.createCell(1).setCellValue("It is " + str); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.getRow(1).createCell(3).setCellValue("Even rows are blue"); sheet.getRow(2).createCell(3).setCellValue("Multiples of 3 have a grey background"); sheet.getRow(4).createCell(3).setCellValue("Multiples of 5 are bold"); sheet.getRow(9).createCell(3).setCellValue("Multiples of 10 are red (beats even)"); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Row divides by 10, red (will beat #1) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),10)=0"); FontFormatting font1 = rule1.createFontFormatting(); font1.setFontColorIndex(IndexedColors.RED.index); // Condition 2: Row is even, blue ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)=0"); FontFormatting font2 = rule2.createFontFormatting(); font2.setFontColorIndex(IndexedColors.BLUE.index); // Condition 3: Row divides by 5, bold ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("MOD(ROW(),5)=0"); FontFormatting font3 = rule3.createFontFormatting(); font3.setFontStyle(false, true); // Condition 4: Row divides by 3, grey background ConditionalFormattingRule rule4 = sheetCF.createConditionalFormattingRule("MOD(ROW(),3)=0"); PatternFormatting fill4 = rule4.createPatternFormatting(); fill4.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index); fill4.setFillPattern(PatternFormatting.SOLID_FOREGROUND); // Apply CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:F41") }; sheetCF.addConditionalFormatting(regions, rule1); sheetCF.addConditionalFormatting(regions, rule2); sheetCF.addConditionalFormatting(regions, rule3); sheetCF.addConditionalFormatting(regions, rule4); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Icon Sets / Multi-States allow you to have icons shown which vary * based on the values, eg Red traffic light / Yellow traffic light / * Green traffic light//from w w w .j a v a 2 s. c o m */ static void iconSets(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Icon Sets"); Row r = sheet.createRow(1); r.createCell(0).setCellValue("Reds"); r.createCell(1).setCellValue(0); r.createCell(2).setCellValue(0); r.createCell(3).setCellValue(0); r = sheet.createRow(2); r.createCell(0).setCellValue("Yellows"); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(5); r.createCell(3).setCellValue(5); r = sheet.createRow(3); r.createCell(0).setCellValue("Greens"); r.createCell(1).setCellValue(10); r.createCell(2).setCellValue(10); r.createCell(3).setCellValue(10); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B1:B4") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS); IconMultiStateFormatting im1 = rule1.getMultiStateFormatting(); im1.getThresholds()[0].setRangeType(RangeType.MIN); im1.getThresholds()[1].setRangeType(RangeType.PERCENT); im1.getThresholds()[1].setValue(33d); im1.getThresholds()[2].setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule1); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C1:C4") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_FLAGS); IconMultiStateFormatting im2 = rule1.getMultiStateFormatting(); im2.getThresholds()[0].setRangeType(RangeType.PERCENT); im2.getThresholds()[0].setValue(0d); im2.getThresholds()[1].setRangeType(RangeType.PERCENT); im2.getThresholds()[1].setValue(33d); im2.getThresholds()[2].setRangeType(RangeType.PERCENT); im2.getThresholds()[2].setValue(67d); sheetCF.addConditionalFormatting(regions, rule2); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D1:D4") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_SYMBOLS_CIRCLE); IconMultiStateFormatting im3 = rule1.getMultiStateFormatting(); im3.setIconOnly(true); im3.getThresholds()[0].setRangeType(RangeType.MIN); im3.getThresholds()[1].setRangeType(RangeType.NUMBER); im3.getThresholds()[1].setValue(3d); im3.getThresholds()[2].setRangeType(RangeType.NUMBER); im3.getThresholds()[2].setValue(7d); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * Color Scales / Colour Scales / Colour Gradients allow you shade the * background colour of the cell based on the values, eg from Red to * Yellow to Green./*from w w w. j av a2 s.com*/ */ static void colourScales(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Colour Scales"); Row r = sheet.createRow(1); r.createCell(0).setCellValue("Red-Yellow-Green"); for (int i = 1; i <= 7; i++) { r.createCell(i).setCellValue((i - 1) * 5); } r = sheet.createRow(2); r.createCell(0).setCellValue("Red-White-Blue"); for (int i = 1; i <= 9; i++) { r.createCell(i).setCellValue((i - 1) * 5); } r = sheet.createRow(3); r.createCell(0).setCellValue("Blue-Green"); for (int i = 1; i <= 16; i++) { r.createCell(i).setCellValue((i - 1)); } sheet.setColumnWidth(0, 5000); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs1 = rule1.getColorScaleFormatting(); cs1.getThresholds()[0].setRangeType(RangeType.MIN); cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE); cs1.getThresholds()[1].setValue(50d); cs1.getThresholds()[2].setRangeType(RangeType.MAX); ((ExtendedColor) cs1.getColors()[0]).setARGBHex("FFF8696B"); ((ExtendedColor) cs1.getColors()[1]).setARGBHex("FFFFEB84"); ((ExtendedColor) cs1.getColors()[2]).setARGBHex("FF63BE7B"); sheetCF.addConditionalFormatting(regions, rule1); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs2 = rule2.getColorScaleFormatting(); cs2.getThresholds()[0].setRangeType(RangeType.MIN); cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE); cs2.getThresholds()[1].setValue(50d); cs2.getThresholds()[2].setRangeType(RangeType.MAX); ((ExtendedColor) cs2.getColors()[0]).setARGBHex("FFF8696B"); ((ExtendedColor) cs2.getColors()[1]).setARGBHex("FFFCFCFF"); ((ExtendedColor) cs2.getColors()[2]).setARGBHex("FF5A8AC6"); sheetCF.addConditionalFormatting(regions, rule2); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingColorScaleRule(); ColorScaleFormatting cs3 = rule3.getColorScaleFormatting(); cs3.setNumControlPoints(2); cs3.getThresholds()[0].setRangeType(RangeType.MIN); cs3.getThresholds()[1].setRangeType(RangeType.MAX); ((ExtendedColor) cs3.getColors()[0]).setARGBHex("FF5A8AC6"); ((ExtendedColor) cs3.getColors()[1]).setARGBHex("FF63BE7B"); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:packtest.ConditionalFormats.java
License:Apache License
/** * DataBars / Data-Bars allow you to have bars shown vary * based on the values, from full to empty *//*from w ww.j a v a2s . c o m*/ static void dataBars(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue("Data Bars"); Row r = sheet.createRow(1); r.createCell(1).setCellValue("Green Positive"); r.createCell(2).setCellValue("Blue Mix"); r.createCell(3).setCellValue("Red Negative"); r = sheet.createRow(2); r.createCell(1).setCellValue(0); r.createCell(2).setCellValue(0); r.createCell(3).setCellValue(0); r = sheet.createRow(3); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(-5); r.createCell(3).setCellValue(-5); r = sheet.createRow(4); r.createCell(1).setCellValue(10); r.createCell(2).setCellValue(10); r.createCell(3).setCellValue(-10); r = sheet.createRow(5); r.createCell(1).setCellValue(5); r.createCell(2).setCellValue(5); r.createCell(3).setCellValue(-5); r = sheet.createRow(6); r.createCell(1).setCellValue(20); r.createCell(2).setCellValue(-10); r.createCell(3).setCellValue(-20); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 5000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF63BE7B"); CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") }; ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db1 = rule1.getDataBarFormatting(); db1.getMinThreshold().setRangeType(RangeType.MIN); db1.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule1); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FF5A8AC6"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") }; ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db2 = rule2.getDataBarFormatting(); db2.getMinThreshold().setRangeType(RangeType.MIN); db2.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule2); color = sheet.getWorkbook().getCreationHelper().createExtendedColor(); color.setARGBHex("FFF8696B"); regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") }; ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color); DataBarFormatting db3 = rule3.getDataBarFormatting(); db3.getMinThreshold().setRangeType(RangeType.MIN); db3.getMaxThreshold().setRangeType(RangeType.MAX); sheetCF.addConditionalFormatting(regions, rule3); }
From source file:packtest.WorkingWithPageSetup.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); /**//from www. ja va 2 s. com * It's possible to set up repeating rows and columns in your printouts by using the setRepeatingRowsAndColumns() function in the Workbook object. * * This function Contains 5 parameters: * The first parameter is the index to the sheet (0 = first sheet). * The second and third parameters specify the range for the columns to repreat. * To stop the columns from repeating pass in -1 as the start and end column. * The fourth and fifth parameters specify the range for the rows to repeat. * To stop the columns from repeating pass in -1 as the start and end rows. */ Sheet sheet1 = wb.createSheet("new sheet"); Sheet sheet2 = wb.createSheet("second sheet"); // Set the columns to repeat from column 0 to 2 on the first sheet Row row1 = sheet1.createRow(0); row1.createCell(0).setCellValue(1); row1.createCell(1).setCellValue(2); row1.createCell(2).setCellValue(3); Row row2 = sheet1.createRow(1); row2.createCell(1).setCellValue(4); row2.createCell(2).setCellValue(5); Row row3 = sheet2.createRow(1); row3.createCell(0).setCellValue(2.1); row3.createCell(4).setCellValue(2.2); row3.createCell(5).setCellValue(2.3); Row row4 = sheet2.createRow(2); row4.createCell(4).setCellValue(2.4); row4.createCell(5).setCellValue(2.5); // Set the columns to repeat from column 0 to 2 on the first sheet sheet1.setRepeatingColumns(CellRangeAddress.valueOf("A:C")); // Set the the repeating rows and columns on the second sheet. CellRangeAddress cra = CellRangeAddress.valueOf("E2:F3"); sheet2.setRepeatingColumns(cra); sheet2.setRepeatingRows(cra); //set the print area for the first sheet wb.setPrintArea(0, 1, 2, 0, 3); FileOutputStream fileOut = new FileOutputStream("xssf-printsetup.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:poi.xssf.usermodel.examples.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); if (args.length > 0) calendar.set(Calendar.YEAR, Integer.parseInt(args[0])); int year = calendar.get(Calendar.YEAR); XSSFWorkbook wb = new XSSFWorkbook(); Map<String, XSSFCellStyle> 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 XSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false);//w ww. j av a2 s .c o m XSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setOrientation(PrintOrientation.LANDSCAPE); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //the header row: centered text in 48pt font XSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); XSSFCell 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 XSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //for compatibility with HSSF we have to set column width 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)); XSSFCell 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++) { XSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { XSSFCell dayCell_1 = row.createCell(i * 2); XSSFCell 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 FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx"); wb.write(out); out.close(); }
From source file:poitest.SSPerformance.java
License:Apache License
public static void main(String[] args) { System.out.println("Apache POI Version: " + Version.getVersion()); args = new String[] { "XSSF", "50000", "50", "0" }; if (args.length != 4) usage("need four command arguments"); String type = args[0];/* w ww .j a v a2 s . c o m*/ long timeStarted = System.currentTimeMillis(); Workbook workBook = createWorkbook(type); boolean isHType = workBook instanceof HSSFWorkbook; int rows = parseInt(args[1], "Failed to parse rows value as integer"); int cols = parseInt(args[2], "Failed to parse cols value as integer"); boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0; Map<String, CellStyle> styles = createStyles(workBook); Sheet sheet = workBook.createSheet("Main Sheet"); Cell headerCell = sheet.createRow(0).createCell(0); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); int sheetNo = 0; int rowIndexInSheet = 1; double value = 0; Calendar calendar = Calendar.getInstance(); for (int rowIndex = 0; rowIndex < rows; rowIndex++) { if (isHType && sheetNo != rowIndex / 0x10000) { sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo)); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); rowIndexInSheet = 1; } Row row = sheet.createRow(rowIndexInSheet); for (int colIndex = 0; colIndex < cols; colIndex++) { Cell cell = row.createCell(colIndex); String address = new CellReference(cell).formatAsString(); switch (colIndex) { case 0: // column A: default number format cell.setCellValue(value++); break; case 1: // column B: #,##0 cell.setCellValue(value++); cell.setCellStyle(styles.get("#,##0.00")); break; case 2: // column C: $#,##0.00 cell.setCellValue(value++); cell.setCellStyle(styles.get("$#,##0.00")); break; case 3: // column D: red bold text on yellow background cell.setCellValue(address); cell.setCellStyle(styles.get("red-bold")); break; case 4: // column E: boolean // TODO booleans are shown as 1/0 instead of TRUE/FALSE cell.setCellValue(rowIndex % 2 == 0); break; case 5: // column F: date / time cell.setCellValue(calendar); cell.setCellStyle(styles.get("m/d/yyyy")); calendar.roll(Calendar.DAY_OF_YEAR, -1); break; case 6: // column F: formula // TODO formulas are not yet supported in SXSSF //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")"); //break; default: cell.setCellValue(value++); break; } } rowIndexInSheet++; } if (saveFile) { String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]); try { FileOutputStream out = new FileOutputStream(fileName); workBook.write(out); out.close(); } catch (IOException ioe) { System.err .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage()); } } long timeFinished = System.currentTimeMillis(); System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds"); }
From source file:project1.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from ww w.j ava 2 s .c o m*/ // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new XSSFWorkbook(); 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 = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:sample.poi.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss"); HSSFWorkbook wb = 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);/*from www . j a v a 2 s . c om*/ 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] + " " + "2013"); 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"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }