List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCustomPalette
public HSSFPalette getCustomPalette()
From source file:org.sigmah.server.endpoint.export.sigmah.spreadsheet.ExcelUtils.java
License:Open Source License
public CellStyle getGroupStyle(HSSFWorkbook wb) { CellStyle style = createBorderedStyle(wb); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFPalette palette = wb.getCustomPalette(); palette.setColorAtIndex(HSSFColor.BROWN.index, ExportConstants.LIGHTORANGE_RGB[0], ExportConstants.LIGHTORANGE_RGB[1], ExportConstants.LIGHTORANGE_RGB[2]); style.setFillForegroundColor(HSSFColor.BROWN.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(getItalicFont(wb, (short) 10)); style.setWrapText(true);/* w ww . ja v a 2 s . c om*/ return style; }
From source file:org.sysmodb.xml.HSSFXMLStyleHelper.java
License:BSD License
public HSSFXMLStyleHelper(HSSFWorkbook wb) { workbook = wb; palette = wb.getCustomPalette(); }
From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java
License:Open Source License
/** * ?excel(2003)/*from w w w .j av a2s.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; }
From source file:uk.co.spudsoft.birt.emitters.excel.StyleManagerHUtils.java
License:Open Source License
/** * Get an HSSFPalette index for a workbook that closely approximates the passed in colour. * @param workbook//from w w w . j a va2 s .co m * The workbook for which the colour is being sought. * @param colour * The colour, in the form "rgb(<i>r</i>, <i>g</i>, <i>b</i>)". * @return * The index into the HSSFPallete for the workbook for a colour that approximates the passed in colour. */ private short getHColour(HSSFWorkbook workbook, String colour) { int[] rgbInt = ColorUtil.getRGBs(colour); if (rgbInt == null) { return 0; } byte[] rgbByte = new byte[] { (byte) rgbInt[0], (byte) rgbInt[1], (byte) rgbInt[2] }; HSSFPalette palette = workbook.getCustomPalette(); HSSFColor result = palette.findColor(rgbByte[0], rgbByte[1], rgbByte[2]); if (result == null) { if (paletteIndex > minPaletteIndex) { --paletteIndex; palette.setColorAtIndex(paletteIndex, rgbByte[0], rgbByte[1], rgbByte[2]); return paletteIndex; } else { result = palette.findSimilarColor(rgbByte[0], rgbByte[1], rgbByte[2]); } } return result.getIndex(); }
From source file:uk.co.spudsoft.birt.emitters.excel.tests.NestedTables2ReportTest.java
License:Open Source License
@Test public void testRunReportXls() throws BirtException, IOException { InputStream inputStream = runAndRenderReport("NestedTables2.rptdesign", "xls"); assertNotNull(inputStream);//from w w w . j a va 2 s. c om try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); assertEquals("Nested Tables Test Report", workbook.getSheetAt(0).getSheetName()); Sheet sheet = workbook.getSheetAt(0); assertEquals(12, firstNullRow(sheet)); assertEquals(1, sheet.getRow(0).getCell(0).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(0).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(1).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(2).getCell(1).getNumericCellValue(), 0.0); assertEquals(1, sheet.getRow(3).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(3).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(3).getCell(2).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(0).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(4).getCell(1).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(5).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(6).getCell(1).getNumericCellValue(), 0.0); assertEquals(2, sheet.getRow(7).getCell(0).getNumericCellValue(), 0.0); assertEquals(4, sheet.getRow(7).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(7).getCell(2).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(0).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(8).getCell(1).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(9).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(0).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(10).getCell(1).getNumericCellValue(), 0.0); assertEquals(3, sheet.getRow(11).getCell(0).getNumericCellValue(), 0.0); assertEquals(6, sheet.getRow(11).getCell(1).getNumericCellValue(), 0.0); assertEquals(9, sheet.getRow(11).getCell(2).getNumericCellValue(), 0.0); short bgColour = ((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFillBackgroundColor(); assertEquals("0:0:0", workbook.getCustomPalette().getColor(bgColour).getHexString()); short baseColour = workbook .getFontAt(((HSSFCell) sheet.getRow(0).getCell(0)).getCellStyle().getFontIndex()).getColor(); assertEquals("FFFF:FFFF:FFFF", workbook.getCustomPalette().getColor(baseColour).getHexString()); } finally { inputStream.close(); } }