List of usage examples for org.apache.poi.ss.usermodel IndexedColors PINK
IndexedColors PINK
To view the source code for org.apache.poi.ss.usermodel IndexedColors PINK.
Click Source Link
From source file:com.ostrichemulators.semtool.poi.main.XlsWriter.java
License:Open Source License
@Override public void write(ImportData data, OutputStream output) throws IOException { createWorkbook(data);/*from w ww . ja v a2s . c o m*/ CellStyle errorstyle = currentwb.createCellStyle(); errorstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); errorstyle.setFillForegroundColor(IndexedColors.PINK.getIndex()); for (LoadingSheetData nodes : data.getNodes()) { List<String> props = new ArrayList<>(nodes.getProperties()); createTab(nodes.getName(), makeHeaderRow(nodes, props)); // +2 -> 1 for the blank first col and 1 for the subject type String[] row = new String[2 + props.size()]; CellStyle[] fmts = new CellStyle[2 + props.size()]; Map<String, IRI> proplkp = nodes.getPropertiesAndDataTypes(); IRI[] datatypes = new IRI[2 + props.size()]; int dtcol = 2; for (String propname : props) { if (null != proplkp.get(propname)) { datatypes[dtcol] = proplkp.get(propname); } dtcol++; } DataIterator di = nodes.iterator(); while (di.hasNext()) { LoadingNodeAndPropertyValues nap = di.next(); row[1] = nap.getSubject(); fmts[1] = (nap.isSubjectError() ? errorstyle : null); int col = 2; for (String prop : props) { Value val = nap.get(prop); row[col++] = (null == val ? null : val.stringValue()); } addRow(row, fmts, datatypes); } } for (LoadingSheetData rels : data.getRels()) { List<String> props = new ArrayList<>(rels.getProperties()); createTab(rels.getName(), makeHeaderRow(rels, props)); // +3 -> 1 for the blank first col and 1 for the subject type, 1 for object type String[] row = new String[3 + props.size()]; CellStyle[] fmts = new CellStyle[3 + props.size()]; if (rels.isEmpty()) { // no rows to add, but still add the relationship name field row[0] = rels.getRelname(); } Map<String, IRI> proplkp = rels.getPropertiesAndDataTypes(); IRI[] datatypes = new IRI[3 + props.size()]; int dtcol = 3; for (String propname : props) { if (null != proplkp.get(propname)) { datatypes[dtcol] = proplkp.get(propname); } dtcol++; } DataIterator di = rels.iterator(); while (di.hasNext()) { LoadingNodeAndPropertyValues nap = di.next(); if (rels.hasErrors()) { log.warn("tab color not set (refactor, please)"); //currentsheet.setTabColor( IndexedColors.ROSE.getIndex() ); } // do we need the relation name in the first column? row[0] = (nextRowIsFirstRowOfTab() ? rels.getRelname() : null); row[1] = nap.getSubject(); fmts[1] = (nap.isSubjectError() ? errorstyle : null); row[2] = nap.getObject(); fmts[2] = (nap.isObjectError() ? errorstyle : null); int col = 3; for (String prop : props) { Value val = nap.get(prop); row[col++] = (null == val ? null : val.stringValue()); } addRow(row, fmts, datatypes); } } write(output); }
From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java
License:Apache License
protected short colorToIndex(Workbook wb, Color color) { if (color == null) { return 0; }//from w ww . j a v a 2 s. com if (Color.black.equals(color)) { return IndexedColors.BLACK.getIndex(); } if (Color.white.equals(color)) { return IndexedColors.WHITE.getIndex(); } if (Color.blue.equals(color) || Color.blue.darker().equals(color) || Color.blue.brighter().equals(color)) { return IndexedColors.BLUE.getIndex(); } if (Color.red.equals(color) || Color.red.darker().equals(color) || Color.red.brighter().equals(color)) { return IndexedColors.RED.getIndex(); } if (Color.LIGHT_GRAY.equals(color)) { return IndexedColors.GREY_25_PERCENT.getIndex(); } if (Color.GRAY.equals(color)) { return IndexedColors.GREY_50_PERCENT.getIndex(); } if (Color.DARK_GRAY.equals(color)) { return IndexedColors.GREY_80_PERCENT.getIndex(); } if (Color.green.equals(color) || Color.green.brighter().equals(color) || Color.green.darker().equals(color)) { return IndexedColors.GREEN.getIndex(); } if (Color.magenta.equals(color) || Color.magenta.darker().equals(color) || Color.magenta.brighter().equals(color)) { return IndexedColors.MAROON.getIndex(); } if (Color.orange.equals(color) || Color.orange.darker().equals(color) || Color.orange.brighter().equals(color)) { return IndexedColors.ORANGE.getIndex(); } if (Color.pink.equals(color) || Color.pink.darker().equals(color) || Color.pink.brighter().equals(color)) { return IndexedColors.PINK.getIndex(); } if (Color.yellow.equals(color) || Color.yellow.darker().equals(color) || Color.yellow.brighter().equals(color)) { return IndexedColors.YELLOW.getIndex(); } byte r = (byte) color.getRed(); byte g = (byte) color.getGreen(); byte b = (byte) color.getBlue(); HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); HSSFColor hssColor = palette.findColor(r, g, b); try { if (hssColor == null) { hssColor = palette.addColor(r, g, b); } return hssColor.getIndex(); } catch (RuntimeException e) { hssColor = palette.findSimilarColor(r, g, b); return hssColor != null ? hssColor.getIndex() : 0; } }
From source file:jdbreport.model.io.xls.poi.Excel2007Writer.java
License:Apache License
protected short colorToIndex(Workbook wb, Color color) { if (color == null) { return 0; }/*w w w . j a va 2 s . c o m*/ if (Color.black.equals(color)) { return IndexedColors.BLACK.getIndex(); } if (Color.white.equals(color)) { return IndexedColors.WHITE.getIndex(); } if (Color.blue.equals(color) || Color.blue.darker().equals(color) || Color.blue.brighter().equals(color)) { return IndexedColors.BLUE.getIndex(); } if (Color.red.equals(color) || Color.red.darker().equals(color) || Color.red.brighter().equals(color)) { return IndexedColors.RED.getIndex(); } if (Color.LIGHT_GRAY.equals(color)) { return IndexedColors.GREY_25_PERCENT.getIndex(); } if (Color.GRAY.equals(color)) { return IndexedColors.GREY_50_PERCENT.getIndex(); } if (Color.DARK_GRAY.equals(color)) { return IndexedColors.GREY_80_PERCENT.getIndex(); } if (Color.green.equals(color) || Color.green.brighter().equals(color) || Color.green.darker().equals(color)) { return IndexedColors.GREEN.getIndex(); } if (Color.magenta.equals(color) || Color.magenta.darker().equals(color) || Color.magenta.brighter().equals(color)) { return IndexedColors.MAROON.getIndex(); } if (Color.orange.equals(color) || Color.orange.darker().equals(color) || Color.orange.brighter().equals(color)) { return IndexedColors.ORANGE.getIndex(); } if (Color.pink.equals(color) || Color.pink.darker().equals(color) || Color.pink.brighter().equals(color)) { return IndexedColors.PINK.getIndex(); } if (Color.yellow.equals(color) || Color.yellow.darker().equals(color) || Color.yellow.brighter().equals(color)) { return IndexedColors.YELLOW.getIndex(); } XSSFColor xssfColor = new XSSFColor(color); return xssfColor.getIndexed(); }
From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryStats.java
License:Apache License
private static CellStyle getCS(XSSFWorkbook wb, int i) { CellStyle cs = wb.createCellStyle(); cs.setFillPattern(CellStyle.SOLID_FOREGROUND); if (i == 1) { ///*w ww .ja v a 2s . c o m*/ cs.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); } else if (i == 2) { // cs.setFillForegroundColor(IndexedColors.GREEN.getIndex()); } else { cs.setFillForegroundColor(IndexedColors.PINK.getIndex()); } return cs; }
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:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2007+)/*from w ww. j av a 2 s . co m*/ * @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 v a2 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; }