List of usage examples for org.apache.poi.ss.usermodel IndexedColors RED
IndexedColors RED
To view the source code for org.apache.poi.ss.usermodel IndexedColors RED.
Click Source Link
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SpreadSheetFormatOptions.java
License:Open Source License
public static void initialize() { lookup_colors = new HashMap<String, Short>(); lookup_alignment = new HashMap<String, Short>(); lookup_border = new HashMap<String, Short>(); lookup_fillpatten = new HashMap<String, Short>(); lookup_underline = new HashMap<String, Byte>(); lookup_color = new HashMap<String, Color>(); lookup_underline.put("double", Font.U_DOUBLE); lookup_underline.put("double_accounting", Font.U_DOUBLE_ACCOUNTING); lookup_underline.put("single", Font.U_SINGLE); lookup_underline.put("single_accounting", Font.U_SINGLE_ACCOUNTING); lookup_underline.put("none", Font.U_NONE); lookup_colors.put("black", IndexedColors.BLACK.getIndex()); lookup_colors.put("brown", IndexedColors.BROWN.getIndex()); lookup_colors.put("olive_green", IndexedColors.OLIVE_GREEN.getIndex()); lookup_colors.put("dark_green", IndexedColors.DARK_GREEN.getIndex()); lookup_colors.put("dark_teal", IndexedColors.DARK_TEAL.getIndex()); lookup_colors.put("dark_blue", IndexedColors.DARK_BLUE.getIndex()); lookup_colors.put("indigo", IndexedColors.INDIGO.getIndex()); lookup_colors.put("grey_80_percent", IndexedColors.GREY_80_PERCENT.getIndex()); lookup_colors.put("grey_50_percent", IndexedColors.GREY_50_PERCENT.getIndex()); lookup_colors.put("grey_40_percent", IndexedColors.GREY_40_PERCENT.getIndex()); lookup_colors.put("grey_25_percent", IndexedColors.GREY_25_PERCENT.getIndex()); lookup_colors.put("orange", IndexedColors.ORANGE.getIndex()); lookup_colors.put("dark_yellow", IndexedColors.DARK_YELLOW.getIndex()); lookup_colors.put("green", IndexedColors.GREEN.getIndex()); lookup_colors.put("teal", IndexedColors.TEAL.getIndex()); lookup_colors.put("blue", IndexedColors.BLUE.getIndex()); lookup_colors.put("blue_grey", IndexedColors.BLUE_GREY.getIndex()); lookup_colors.put("red", IndexedColors.RED.getIndex()); lookup_colors.put("light_orange", IndexedColors.LIGHT_ORANGE.getIndex()); lookup_colors.put("lime", IndexedColors.LIME.getIndex()); lookup_colors.put("sea_green", IndexedColors.SEA_GREEN.getIndex()); lookup_colors.put("aqua", IndexedColors.AQUA.getIndex()); lookup_colors.put("light_blue", IndexedColors.LIGHT_BLUE.getIndex()); lookup_colors.put("violet", IndexedColors.VIOLET.getIndex()); lookup_colors.put("pink", IndexedColors.PINK.getIndex()); lookup_colors.put("gold", IndexedColors.GOLD.getIndex()); lookup_colors.put("yellow", IndexedColors.YELLOW.getIndex()); lookup_colors.put("bright_green", IndexedColors.BRIGHT_GREEN.getIndex()); lookup_colors.put("turquoise", IndexedColors.TURQUOISE.getIndex()); lookup_colors.put("dark_red", IndexedColors.DARK_RED.getIndex()); lookup_colors.put("sky_blue", IndexedColors.SKY_BLUE.getIndex()); lookup_colors.put("plum", IndexedColors.PLUM.getIndex()); lookup_colors.put("rose", IndexedColors.ROSE.getIndex()); lookup_colors.put("light_yellow", IndexedColors.LIGHT_YELLOW.getIndex()); lookup_colors.put("light_green", IndexedColors.LIGHT_GREEN.getIndex()); lookup_colors.put("light_turquoise", IndexedColors.LIGHT_TURQUOISE.getIndex()); lookup_colors.put("pale_blue", IndexedColors.PALE_BLUE.getIndex()); lookup_colors.put("lavender", IndexedColors.LAVENDER.getIndex()); lookup_colors.put("white", IndexedColors.WHITE.getIndex()); lookup_colors.put("cornflower_blue", IndexedColors.CORNFLOWER_BLUE.getIndex()); lookup_colors.put("lemon_chiffon", IndexedColors.LEMON_CHIFFON.getIndex()); lookup_colors.put("maroon", IndexedColors.MAROON.getIndex()); lookup_colors.put("orchid", IndexedColors.ORCHID.getIndex()); lookup_colors.put("coral", IndexedColors.CORAL.getIndex()); lookup_colors.put("royal_blue", IndexedColors.ROYAL_BLUE.getIndex()); lookup_colors.put("light_cornflower_blue", IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); lookup_alignment.put("left", CellStyle.ALIGN_LEFT); lookup_alignment.put("right", CellStyle.ALIGN_RIGHT); lookup_alignment.put("center", CellStyle.ALIGN_CENTER); lookup_alignment.put("justify", CellStyle.ALIGN_JUSTIFY); lookup_alignment.put("general", CellStyle.ALIGN_GENERAL); lookup_alignment.put("fill", CellStyle.ALIGN_FILL); lookup_alignment.put("center_selection", CellStyle.ALIGN_CENTER_SELECTION); lookup_alignment.put("vertical_top", CellStyle.VERTICAL_TOP); lookup_alignment.put("vertical_bottom", CellStyle.VERTICAL_BOTTOM); lookup_alignment.put("vertical_center", CellStyle.VERTICAL_CENTER); lookup_alignment.put("vertical_justify", CellStyle.VERTICAL_JUSTIFY); lookup_border.put("none", CellStyle.BORDER_NONE); lookup_border.put("thin", CellStyle.BORDER_THIN); lookup_border.put("medium", CellStyle.BORDER_MEDIUM); lookup_border.put("dashed", CellStyle.BORDER_DASHED); lookup_border.put("hair", CellStyle.BORDER_HAIR); lookup_border.put("thick", CellStyle.BORDER_THICK); lookup_border.put("double", CellStyle.BORDER_DOUBLE); lookup_border.put("dotted", CellStyle.BORDER_DOTTED); lookup_border.put("medium_dashed", CellStyle.BORDER_MEDIUM_DASHED); lookup_border.put("dash_dot", CellStyle.BORDER_DASH_DOT); lookup_border.put("medium_dash_dot", CellStyle.BORDER_MEDIUM_DASH_DOT); lookup_border.put("dash_dot_dot", CellStyle.BORDER_DASH_DOT_DOT); lookup_border.put("medium_dash_dot_dot", CellStyle.BORDER_MEDIUM_DASH_DOT_DOT); lookup_border.put("slanted_dash_dot", CellStyle.BORDER_SLANTED_DASH_DOT); lookup_fillpatten.put("big_spots", CellStyle.BIG_SPOTS); lookup_fillpatten.put("squares", CellStyle.SQUARES); lookup_fillpatten.put("nofill", CellStyle.NO_FILL); lookup_fillpatten.put("solid_foreground", CellStyle.SOLID_FOREGROUND); lookup_fillpatten.put("fine_dots", CellStyle.FINE_DOTS); lookup_fillpatten.put("alt_bars", CellStyle.ALT_BARS); lookup_fillpatten.put("sparse_dots", CellStyle.SPARSE_DOTS); lookup_fillpatten.put("thick_horz_bands", CellStyle.THICK_HORZ_BANDS); lookup_fillpatten.put("thick_vert_bands", CellStyle.THICK_VERT_BANDS); lookup_fillpatten.put("thick_backward_diag", CellStyle.THICK_BACKWARD_DIAG); lookup_fillpatten.put("thick_forward_diag", CellStyle.THICK_FORWARD_DIAG); lookup_fillpatten.put("thin_horz_bands", CellStyle.THIN_HORZ_BANDS); lookup_fillpatten.put("thin_vert_bands", CellStyle.THIN_VERT_BANDS); lookup_fillpatten.put("thin_backward_diag", CellStyle.THIN_BACKWARD_DIAG); lookup_fillpatten.put("thin_forward_diag", CellStyle.THIN_FORWARD_DIAG); lookup_fillpatten.put("diamonds", CellStyle.DIAMONDS); lookup_fillpatten.put("less_dots", CellStyle.LESS_DOTS); lookup_fillpatten.put("least_dots", CellStyle.LEAST_DOTS); lookup_color.put("black", Color.BLACK); lookup_color.put("blue", Color.BLUE); lookup_color.put("cyan", Color.CYAN); lookup_color.put("dark_gray", Color.DARK_GRAY); lookup_color.put("darkGray", Color.DARK_GRAY); lookup_color.put("gray", Color.GRAY); lookup_color.put("green", Color.GREEN); lookup_color.put("light_gray", Color.LIGHT_GRAY); lookup_color.put("lightGray", Color.LIGHT_GRAY); lookup_color.put("magenta", Color.MAGENTA); lookup_color.put("orange", Color.ORANGE); lookup_color.put("pink", Color.PINK); lookup_color.put("red", Color.RED); lookup_color.put("white", Color.WHITE); lookup_color.put("yellow", Color.YELLOW); }
From source file:org.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;//from ww w . jav a2s.c o m //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.joeffice.spreadsheet.actions.SetBordersAction.java
License:Apache License
public SetBordersAction() { thicknessList = new short[] { CellStyle.BORDER_NONE, CellStyle.BORDER_THIN, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_THICK };/* w ww .j a v a 2s. c o m*/ colors = new short[] { IndexedColors.BLACK.getIndex(), IndexedColors.RED.getIndex(), IndexedColors.BLUE.getIndex(), IndexedColors.GREEN.getIndex(), IndexedColors.LAVENDER.getIndex() }; String[] thicknessTexts = NbBundle.getMessage(getClass(), "MSG_Thickness").split(","); thicknessChoice = new JComboBox(thicknessTexts); String[] colorTexts = NbBundle.getMessage(getClass(), "MSG_Color").split(","); colorChoice = new JComboBox(colorTexts); }
From source file:packtest.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();/*from www .jav a2 s . com*/ Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); // comment2.setAddress(new CellAddress("C3")); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); 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. j av a2 s. c o 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.WorkingWithFonts.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("Fonts"); Font font0 = wb.createFont(); font0.setColor(IndexedColors.BROWN.getIndex()); CellStyle style0 = wb.createCellStyle(); style0.setFont(font0);/*from w w w.ja v a 2 s . co m*/ Font font1 = wb.createFont(); font1.setFontHeightInPoints((short) 14); font1.setFontName("Courier New"); font1.setColor(IndexedColors.RED.getIndex()); CellStyle style1 = wb.createCellStyle(); style1.setFont(font1); Font font2 = wb.createFont(); font2.setFontHeightInPoints((short) 16); font2.setFontName("Arial"); font2.setColor(IndexedColors.GREEN.getIndex()); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 18); font3.setFontName("Times New Roman"); font3.setColor(IndexedColors.LAVENDER.getIndex()); CellStyle style3 = wb.createCellStyle(); style3.setFont(font3); Font font4 = wb.createFont(); font4.setFontHeightInPoints((short) 18); font4.setFontName("Wingdings"); font4.setColor(IndexedColors.GOLD.getIndex()); CellStyle style4 = wb.createCellStyle(); style4.setFont(font4); Font font5 = wb.createFont(); font5.setFontName("Symbol"); CellStyle style5 = wb.createCellStyle(); style5.setFont(font5); Cell cell0 = sheet.createRow(0).createCell(1); cell0.setCellValue("Default"); cell0.setCellStyle(style0); Cell cell1 = sheet.createRow(1).createCell(1); cell1.setCellValue("Courier"); cell1.setCellStyle(style1); Cell cell2 = sheet.createRow(2).createCell(1); cell2.setCellValue("Arial"); cell2.setCellStyle(style2); Cell cell3 = sheet.createRow(3).createCell(1); cell3.setCellValue("Times New Roman"); cell3.setCellStyle(style3); Cell cell4 = sheet.createRow(4).createCell(1); cell4.setCellValue("Wingdings"); cell4.setCellStyle(style4); Cell cell5 = sheet.createRow(5).createCell(1); cell5.setCellValue("Symbol"); cell5.setCellStyle(style5); // Write the output to a file FileOutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-fonts.xlsx")); wb.write(fileOut); fileOut.close(); }
From source file:poi.xssf.usermodel.examples.CellComments.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); CreationHelper factory = wb.getCreationHelper(); Sheet sheet = wb.createSheet();//from w w w .j a va 2 s. com Cell cell1 = sheet.createRow(3).createCell(5); cell1.setCellValue("F4"); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); Comment comment1 = drawing.createCellComment(anchor); RichTextString str1 = factory.createRichTextString("Hello, World!"); comment1.setString(str1); comment1.setAuthor("Apache POI"); cell1.setCellComment(comment1); Cell cell2 = sheet.createRow(2).createCell(2); cell2.setCellValue("C3"); Comment comment2 = drawing.createCellComment(anchor); RichTextString str2 = factory.createRichTextString("XSSF can set cell comments"); //apply custom font to the text in the comment Font font = wb.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 14); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.RED.getIndex()); str2.applyFont(font); comment2.setString(str2); comment2.setAuthor("Apache POI"); comment2.setColumn(2); comment2.setRow(2); String fname = "comments.xlsx"; FileOutputStream out = new FileOutputStream(fname); wb.write(out); out.close(); }
From source file:poi.xssf.usermodel.examples.WorkingWithFonts.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("Fonts"); Font font0 = wb.createFont(); font0.setColor(IndexedColors.BROWN.getIndex()); CellStyle style0 = wb.createCellStyle(); style0.setFont(font0);//ww w .j av a 2s . c om Font font1 = wb.createFont(); font1.setFontHeightInPoints((short) 14); font1.setFontName("Courier New"); font1.setColor(IndexedColors.RED.getIndex()); CellStyle style1 = wb.createCellStyle(); style1.setFont(font1); Font font2 = wb.createFont(); font2.setFontHeightInPoints((short) 16); font2.setFontName("Arial"); font2.setColor(IndexedColors.GREEN.getIndex()); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); Font font3 = wb.createFont(); font3.setFontHeightInPoints((short) 18); font3.setFontName("Times New Roman"); font3.setColor(IndexedColors.LAVENDER.getIndex()); CellStyle style3 = wb.createCellStyle(); style3.setFont(font3); Font font4 = wb.createFont(); font4.setFontHeightInPoints((short) 18); font4.setFontName("Wingdings"); font4.setColor(IndexedColors.GOLD.getIndex()); CellStyle style4 = wb.createCellStyle(); style4.setFont(font4); Font font5 = wb.createFont(); font5.setFontName("Symbol"); CellStyle style5 = wb.createCellStyle(); style5.setFont(font5); Cell cell0 = sheet.createRow(0).createCell(1); cell0.setCellValue("Default"); cell0.setCellStyle(style0); Cell cell1 = sheet.createRow(1).createCell(1); cell1.setCellValue("Courier"); cell1.setCellStyle(style1); Cell cell2 = sheet.createRow(2).createCell(1); cell2.setCellValue("Arial"); cell2.setCellStyle(style2); Cell cell3 = sheet.createRow(3).createCell(1); cell3.setCellValue("Times New Roman"); cell3.setCellStyle(style3); Cell cell4 = sheet.createRow(4).createCell(1); cell4.setCellValue("Wingdings"); cell4.setCellStyle(style4); Cell cell5 = sheet.createRow(5).createCell(1); cell5.setCellValue("Symbol"); cell5.setCellStyle(style5); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("xssf-fonts.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2007+)/*ww w . j ava2s . com*/ * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42007(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { // ?,?, File copyExcelFile = null; XSSFWorkbook copyWorkBook = null; if (single) {// ?? addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new XSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? XSSFWorkbook workBook = new XSSFWorkbook(new FileInputStream(excelFile)); XSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? int month = -1; // ? int year = -1;// ? if (single) {// ?? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); if (single) {// ?? // ?,,,? String[] title = { "", "", "?" }; if (null != copyWorkBook) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(4) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 XSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , if (null != copyWorkBook) { XSSFCellStyle weekend = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); weekend.setFillForegroundColor( new XSSFColor(new java.awt.Color(21, 225, 216))); //weekend.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); if (start.after(ls) && end.before(le)) { // ??,?? if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook .getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle() .clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else {//??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? // ?,??,?? if (null != copyWorkBook) { XSSFCellStyle excepitonStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle().clone(); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (null != copyWorkBook) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); XSSFCellStyle cellStyle = (XSSFCellStyle) copyWorkBook.getSheetAt(0).getRow(i + 1) .getCell(0).getCellStyle().clone(); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (null != copyWorkBook) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xlsx")); String[] saveType = { "xlsx" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (null != copyExcelFile) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; }
From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2003)//ww w. j a va 2 s .c o m * @param excelFile ??Excel * @param single ?? */ private Map<String, double[]> parseExcel42003(File excelFile, boolean single) { Map<String, double[]> result = new HashMap<String, double[]>(); try { File copyExcelFile = null; HSSFWorkbook copyWorkBook = null; if (single) {// ?? // ?,?, addMessage(""); copyExcelFile = new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf("\\")) + "/.xlsx"); FileUtils.copyFile(excelFile, copyExcelFile); // copyWorkBook = new HSSFWorkbook(new FileInputStream(copyExcelFile)); } // ? HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(excelFile)); HSSFSheet sheet = workBook.getSheetAt(0); int rows = sheet.getLastRowNum(); if (rows >= 6) { // 6,??? // ?3,? String dateStr = sheet.getRow(2).getCell(2).getStringCellValue(); int month = -1; // ? int year = -1;// ? if (single) {// ??. if (StringUtils.isNotBlank(dateStr)) { addMessage("??:" + dateStr); String[] dates = dateStr.split("~"); month = Integer.parseInt(dates[0].split("\\/")[1]);// ?? year = Integer.parseInt(dates[0].split("\\/")[0]);// ?? } else { addMessage( "??,??,?"); } // ?,?? // ,?? int maxValue = (rows - 6) / 2; progressBar.setMaximum(maxValue); } int days = sheet.getRow(3).getLastCellNum(); // ? SimpleDateFormat punchFormat = new SimpleDateFormat("HH:mm"); // ?,,,? String[] title = { "", "", "?" }; if (single) {// ?? if (copyWorkBook != null) { for (int i = 0; i < title.length; i++) { copyWorkBook.getSheetAt(0).getRow(4).createCell(days + i).setCellValue(title[i]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle .cloneStyleFrom(copyWorkBook.getSheetAt(0).getRow(4).getCell(0).getCellStyle()); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(4).getCell(days + i).setCellStyle(cellStyle); copyWorkBook.getSheetAt(0).autoSizeColumn((short) (days + i)); } } } for (int i = 4; i < rows; i = i + 2) { // //,?,?+2 String userName = sheet.getRow(i).getCell(10).getStringCellValue();// ?? String userNum = sheet.getRow(i).getCell(2).getStringCellValue();// ? if (single) {// ?? addMessage("?:" + userName + "<?:" + userNum + ">"); // ?? addBar(1); } // ??,i+1 HSSFRow recordRow = sheet.getRow(i + 1); // double punchDays = 0; // (?),? double punchHours = 0, avgHours = 0; // ??? for (int j = 0; j < days; j++) {// ??? if (single) {// ?? // ?, // ?,??,?? if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { if (copyWorkBook != null) { // , HSSFCellStyle weekend = copyWorkBook.createCellStyle(); weekend.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); weekend.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); HSSFPalette paltette = copyWorkBook.getCustomPalette(); byte[] color = { (byte) (0xff & 21), (byte) (0xff & 225), (byte) (0xff & 216) }; paltette.setColorAtIndex((short) 9, color[0], color[1], color[2]); weekend.setFillForegroundColor((short) 9); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).setCellStyle(weekend); } } } } // ??? String record = recordRow.getCell(j).getStringCellValue();// ? if (StringUtils.isNotBlank(record)) {// ??,?? String[] records = record.split("\n"); // ???,,? if (records.length >= 2) { try { // ?start,?end,?ls,??le Date end = punchFormat.parse(records[records.length - 1]), start = punchFormat.parse(records[0]); Date ls = punchFormat.parse("11:40"), le = punchFormat.parse("13:00"); // ?:??? if (start.after(ls) && end.before(le)) { // ? if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom(copyWorkBook.getSheetAt(0) .getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle.setFillForegroundColor( IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor( IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } else { //??? punchDays = punchDays + 1; // ? long ms = end.getTime() - start.getTime();//???? // ??,???,? long mins = 75 * 60 * 1000;//?75 // ??,??? if (start.before(ls) && end.before(le)) { // ???? mins = end.getTime() - ls.getTime(); } // ??,??? if (start.after(ls) && end.after(le)) { // ???,?:??-? if (start.before(le)) { mins = le.getTime() - start.getTime(); } else if (start.after(ls)) { // ???,?0 mins = 0; } } ms = ms - mins;// ?? punchHours = punchHours + (double) ms / (3600 * 1000); // (?) } } catch (ParseException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); } } else {// ?, if (single) {// ?? if (null != copyWorkBook) { // ?,??,?? HSSFCellStyle excepitonStyle = copyWorkBook.createCellStyle(); excepitonStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j).getCellStyle()); excepitonStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); if (month != -1 && year != -1) { // ??? if (isWeekEnd(year, month, j + 1)) { // , excepitonStyle .setFillForegroundColor(IndexedColors.PINK.getIndex()); } else { excepitonStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); } } copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(j) .setCellStyle(excepitonStyle); } } } } } // ? if (punchDays > 0) { // ???? punchHours = new BigDecimal(punchHours).setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue(); avgHours = new BigDecimal(punchHours / punchDays).setScale(1, BigDecimal.ROUND_HALF_UP) .doubleValue(); } double[] values = { punchDays, punchHours, avgHours }; result.put(userNum + ":" + userName, values); if (single) {// ?? addMessage(":" + userName + "<?:" + userNum + ">??,:" + "D:" + punchDays + ",H:" + punchHours + ",AH:" + avgHours); if (copyWorkBook != null) { for (int v = 0; v < values.length; v++) { copyWorkBook.getSheetAt(0).getRow(i + 1).createCell(days + v) .setCellValue(values[v]); HSSFCellStyle cellStyle = copyWorkBook.createCellStyle(); cellStyle.cloneStyleFrom( copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(0).getCellStyle()); cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); copyWorkBook.getSheetAt(0).getRow(i + 1).getCell(days + v).setCellStyle(cellStyle); } } } } if (single) {// ?? // ?? // ,? addMessage("?,??"); if (copyWorkBook != null) { FileOutputStream out = new FileOutputStream(copyExcelFile); copyWorkBook.write(out); out.close(); } // ???,?? JFileChooser fileSaveChooser = new JFileChooser(); fileSaveChooser.setDialogTitle("?"); fileSaveChooser.setSelectedFile(new File( excelFile.getAbsolutePath().substring(0, excelFile.getAbsolutePath().lastIndexOf(".")) + "-.xls")); String[] saveType = { "xls" }; fileSaveChooser.setAcceptAllFileFilterUsed(false); fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xls", saveType)); int saveResult = fileSaveChooser.showSaveDialog(this); if (saveResult == JFileChooser.APPROVE_OPTION) { File saveFile = fileSaveChooser.getSelectedFile(); // ??? String saveFilePath = saveFile.getAbsolutePath(); addMessage("?,??->" + saveFilePath); FileUtils.copyFile(copyExcelFile, saveFile); Object[] options = { "", "", ",?" }; int response = JOptionPane.showOptionDialog(this, "??,???", "?", JOptionPane.YES_OPTION, JOptionPane.QUESTION_MESSAGE, null, options, options[0]); if (0 == response) {// // ?? addMessage(",??"); Desktop.getDesktop().open(saveFile); } else if (1 == response) {// addMessage(",??"); String[] cmd = new String[5]; cmd[0] = "cmd"; cmd[1] = "/c"; cmd[2] = "start"; cmd[3] = " "; cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\")); Runtime.getRuntime().exec(cmd); } else { alert("??,?()"); } } else { // ??,? clearMessage(); fileName.setText(""); // ??? addMessage("??"); } // ??? if (copyExcelFile != null) { copyExcelFile.delete(); } } } else { // excel???,??????? alert("????!"); } } catch (FileNotFoundException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(",??"); } catch (IOException | OfficeXmlFileException ex) { Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex); alert(":" + ex.getMessage()); } return result; }