List of usage examples for org.apache.poi.ss.usermodel CellStyle setFont
void setFont(Font font);
From source file:aco.Utilities.java
License:Open Source License
static void writeInputDataPoints() { //the file already exists if (new File(filePath3).canRead()) { //System.out.println("File already exists.."); try {//from w ww . j av a2s.c om FileInputStream file = new FileInputStream(new File(filePath3)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(11); //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); Row r1 = sheet1.getRow(0); if (r1 == null) { // First cell in the row, create r1 = sheet1.createRow(0); } Cell c = r1.getCell(0); if (c == null) { c = r1.createCell(0); } c.setCellValue("VRPTW instance - " + VRPTW.instance.name + "; data point coordinates corresponding to customers' requests"); c.setCellStyle(style); Row r = sheet1.getRow(2); if (r == null) { // First cell in the row, create r = sheet1.createRow(2); } Cell c1 = r.getCell(0); if (c1 == null) { c1 = r.createCell(0); } c1.setCellValue("Point #"); c1.setCellStyle(style); c1 = r.getCell(1); if (c1 == null) { c1 = r.createCell(1); } c1.setCellValue("X Coord"); c1.setCellStyle(style); c1 = r.getCell(2); if (c1 == null) { c1 = r.createCell(2); } c1.setCellValue("Y Coord"); c1.setCellStyle(style); int size = VRPTW.instance.nodes.length; int rowIndex = 3; double x, y; for (int i = 0; i < size; i++) { x = VRPTW.instance.nodes[i].x; y = VRPTW.instance.nodes[i].y; r = sheet1.getRow(rowIndex + i); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(rowIndex + i); } c1 = r.getCell(0); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(0); } c1.setCellValue(i); c1 = r.getCell(1); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(1); } c1.setCellValue(x); c1 = r.getCell(2); if (c1 == null) { // New cell //System.out.println("Empty cell, create new one"); c1 = r.createCell(2); } c1.setCellValue(y); } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath3)); workbook1.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } else { System.out.println("File not exists.."); } }
From source file:aco.Utilities.java
License:Open Source License
static void writeResultsExcel(int trialNumber, boolean saveIterCosts) { Row r, r1;/*from w w w . j a v a 2 s .c o m*/ Cell c; int index1 = 0, index2 = 0, index3 = 0, index4 = 0, index5 = 0; //int index6 = 0; //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath).canRead()) { //System.out.println("File already exists.."); try { FileInputStream file = new FileInputStream(new File(filePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); int startIndex = 0, rowIndex = 0; /*switch (VRPTW.m) { case 2: startIndex = 0; rowIndex = 4; break; case 3: startIndex = 2; rowIndex = 5; break; case 5: startIndex = 4; rowIndex = 7; break; case 7: startIndex = 6; rowIndex = 9; break; default: System.out.println("Unknown value for m"); break; }*/ //Get desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(startIndex); //for tours /*XSSFSheet sheet2 = workbook1.getSheetAt(startIndex + 1); //for number of assigned cities XSSFSheet sheet3 = workbook1.getSheetAt(startIndex + 2); //for cost of individual subtours XSSFSheet sheet4 = workbook1.getSheetAt(startIndex + 3); //for total cost of subtours XSSFSheet sheet5 = workbook1.getSheetAt(startIndex + 4); //for verbose output of total cost at each 5 iteration */ XSSFSheet sheet2 = workbook1.getSheetAt(startIndex + 1); //for verbose output of longest cost at each 5 iteration //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); //define style with bold font and blue color for font CellStyle styleBoldBlue = workbook1.createCellStyle(); font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLUE.index); styleBoldBlue.setFont(font); index1 = 133; if (!saveIterCosts) { //write only once the name of the algorithm that was run if (trialNumber == 0) { r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue( "Obtained solutions (values) after running new version (ACS MinMax global, voiajor si oras alesi simultan) with local search"); c.setCellStyle(styleBoldBlue); } //write only once the table header index1 = index1 + 3; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue("Run #"); c.setCellStyle(style); c = r.getCell(1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(1); } c.setCellValue("MinMax (cost of longest subtour)"); c.setCellStyle(style); c = r.getCell(2); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(2); } c.setCellValue("Total Cost"); c.setCellStyle(style); c = r.getCell(3); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(3); } c.setCellValue("Amplitude"); c.setCellStyle(style); //write number of run index1 = 137 + trialNumber; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue(trialNumber + 1); //write MinMax (cost of longest subtour) double longestSubtour = getLongestSubtour(); c = r.getCell(1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(1); } c.setCellValue(longestSubtour); //write total cost double totalCost = getTotalCost(); c = r.getCell(2); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(2); } c.setCellValue(totalCost); //write amplitude double amplitude = getAmplitude(); c = r.getCell(3); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(3); } c.setCellValue(amplitude); } index5 = 859; if (saveIterCosts) { //write only once the name of the algorithm that was run if (trialNumber == 0) { r = sheet2.getRow(index5); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(index5); } c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue( "Longest cost of subtour at each 5 iteration after running new version (ACS MinMax global, voiajor si oras alesi simultan) with local search"); c.setCellStyle(styleBoldBlue); int tempIndex = index5 + 3; r = sheet2.getRow(tempIndex); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(tempIndex); } ArrayList<Integer> iterNumber = getIterNumber(); c = r.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(0); } c.setCellValue("Nr Iter"); c.setCellStyle(style); int indexTemp = 0; for (int j = 0; j < iterNumber.size(); j++) { indexTemp = tempIndex + 1 + j; r1 = sheet2.getRow(indexTemp); if (r1 == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r1 = sheet2.createRow(indexTemp); } c = r1.getCell(0); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r1.createCell(0); } c.setCellValue(iterNumber.get(j)); } } index5 = index5 + 3; r = sheet2.getRow(index5); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet2.createRow(index5); } //for each trial run save at each 5 iteration the best longest cost of a subtour so far ArrayList<Double> iterLongestCost = getIterLongestCost(); int index; //for each run write the table header cell c = r.getCell(trialNumber + 1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(trialNumber + 1); } c.setCellValue("Run " + (trialNumber + 1)); c.setCellStyle(style); for (int j = 0; j < iterLongestCost.size(); j++) { index = index5 + 1 + j; r1 = sheet2.getRow(index); if (r1 == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r1 = sheet2.createRow(index); } c = r1.getCell(trialNumber + 1); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r1.createCell(trialNumber + 1); } c.setCellValue(iterLongestCost.get(j)); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook1.write(out); out.close(); int nrOfRun = trialNumber + 1; System.out.println("\nRun #" + nrOfRun + " written successfully on disk.\n"); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook System.out.println("File " + filePath + " doesn't exists.."); } }
From source file:aco.Utilities.java
License:Open Source License
static void writeExcelFinalSolution(int trial, double scalledValue) { Row r;/*www. j a v a2 s . c om*/ Cell c; int index1 = 0; //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath5).canRead()) { //System.out.println("File already exists.."); try { FileInputStream file = new FileInputStream(new File(filePath5)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(0); //define a cell style for bold font CellStyle style = workbook1.createCellStyle(); Font font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); //define style with bold font and blue color for font CellStyle styleBoldBlue = workbook1.createCellStyle(); font = workbook1.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.BLUE.index); styleBoldBlue.setFont(font); index1 = 8; //8 //26 index1 = index1 + trial; r = sheet1.getRow(index1); if (r == null) { // First cell in the row, create //System.out.println("Empty row, create new one"); r = sheet1.createRow(index1); } int nrOfRun = trial + 1; //write trial number (Run #) c = r.getCell(15); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(15); } c.setCellValue(nrOfRun); //write number of used vehicles c = r.getCell(16); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(16); } c.setCellValue(Ants.best_so_far_ant.usedVehicles); //write total traveled distance c = r.getCell(17); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(17); } c.setCellValue(scalledValue); //write the total number of feasible solutions c = r.getCell(18); if (c == null) { // New cell //System.out.println("Empty cell, create new one"); c = r.createCell(18); } c.setCellValue(InOut.noSolutions); //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath5)); workbook1.write(out); out.close(); System.out.println("\nRun #" + nrOfRun + " written successfully on disk.\n"); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook System.out.println("File " + filePath5 + " doesn't exists.."); } }
From source file:apm.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?/*from w ww . j a va2 s.c om*/ * @param wb * @return ? */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; }
From source file:ar.edu.unrc.gametictactoe.performanceandtraining.configurations.StatisticExperiment.java
License:Open Source License
/** * * @param filePath/*from www . j a va 2s . com*/ * @param backupFiles * @param resultsPerFile * @param resultsRandom * @param randomPerceptronFile <p> * @throws IOException * @throws InvalidFormatException */ public void exportToExcel(String filePath, List<File> backupFiles, Map<File, StatisticForCalc> resultsPerFile, Map<File, StatisticForCalc> resultsRandom, File randomPerceptronFile) throws IOException, InvalidFormatException { InputStream inputXLSX = this.getClass() .getResourceAsStream("/ar/edu/unrc/gametictactoe/resources/EstadisticasTicTacToe.xlsx"); Workbook wb = WorkbookFactory.create(inputXLSX); try (FileOutputStream outputXLSX = new FileOutputStream( filePath + "_" + dateFormater.format(dateForFileName) + "_STATISTICS" + ".xlsx")) { //============= imptimimos en la hoja de % Of Games Won =================== Sheet sheet = wb.getSheetAt(0); //Estilo par los titulos de las tablas int rowStartTitle = 0; int colStartTitle = 2; int rowStart = 1; int colStart = 3; Row rowPlayer1; Row rowPlayer2; Row rowDraw; // Luego creamos el objeto que se encargar de aplicar el estilo a la celda Font fontCellTitle = wb.createFont(); fontCellTitle.setFontHeightInPoints((short) 10); fontCellTitle.setFontName("Arial"); fontCellTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle CellStyleTitle = wb.createCellStyle(); CellStyleTitle.setWrapText(true); CellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); CellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_TOP); CellStyleTitle.setFont(fontCellTitle); // Establecemos el tipo de sombreado de nuestra celda CellStyleTitle.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); CellStyleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND); loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); //estilo titulo finalizado //Estilo de celdas con los valores de las estadisticas CellStyle cellStyle = wb.createCellStyle(); cellStyle.setWrapText(true); /* We are now ready to set borders for this style */ /* Draw a thin left border */ cellStyle.setBorderLeft(CellStyle.BORDER_THIN); /* Add medium right border */ cellStyle.setBorderRight(CellStyle.BORDER_THIN); /* Add dashed top border */ cellStyle.setBorderTop(CellStyle.BORDER_THIN); /* Add dotted bottom border */ cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //estilo celdas finalizado //loadTitle(rowStartTitle, colStartTitle, sheet, backupFiles.size(), CellStyleTitle); rowPlayer1 = sheet.getRow(rowStart); rowPlayer2 = sheet.getRow(rowStart + 1); rowDraw = sheet.getRow(rowStart + 2); for (int file = 0; file < backupFiles.size(); file++) { Cell cellPlayer1 = rowPlayer1.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); Cell cellDraw = rowDraw.createCell(file + colStart, Cell.CELL_TYPE_NUMERIC); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); cellDraw.setCellStyle(cellStyle); Double cellValuePlayer1 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer1(); Double cellValuePlayer2 = resultsPerFile.get(backupFiles.get(file)).getWinRatePlayer2(); Double cellValueDraw = resultsPerFile.get(backupFiles.get(file)).getDrawRate(); assert cellValuePlayer1 <= 100 && cellValuePlayer1 >= 0; assert cellValuePlayer2 <= 100 && cellValuePlayer2 >= 0; assert cellValueDraw <= 100 && cellValueDraw >= 0; //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellDraw.setCellValue(cellValueDraw); cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); } if (!resultsRandom.isEmpty()) { int file = 0;//hay que ir a buscar el randomfile Cell cellDraw = rowDraw.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer1 = rowPlayer1.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); Cell cellPlayer2 = rowPlayer2.createCell(file + colStart - 1, Cell.CELL_TYPE_NUMERIC); cellDraw.setCellStyle(cellStyle); cellPlayer1.setCellStyle(cellStyle); cellPlayer2.setCellStyle(cellStyle); // StatisticForCalc get = resultsRandom.get(randomPerceptronFile); // Double cellValuePlayer1 = get.getWinRatePlayer1(); Double cellValuePlayer1 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer1(); Double cellValuePlayer2 = resultsRandom.get(randomPerceptronFile).getWinRatePlayer2(); Double cellValueDraw = resultsRandom.get(randomPerceptronFile).getDrawRate(); //assert cellValueDraw + cellValuePlayer1 + cellValuePlayer2 == 100; cellPlayer1.setCellValue(cellValuePlayer1); cellPlayer2.setCellValue(cellValuePlayer2); cellDraw.setCellValue(cellValueDraw); } wb.write(outputXLSX); } }
From source file:at.fh.swenga.firefighters.report.ExcelFireEngineReportView.java
@Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { List<FireEngineModel> fireEngines = (List<FireEngineModel>) model.get("fireEngines"); // create a worksheet Sheet sheet = workbook.createSheet("FireEngine Report"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create a new row in the worksheet Row headerRow = sheet.createRow(0);/* w w w . ja v a 2 s . c o m*/ // create a new cell in the row Cell cell0 = headerRow.createCell(0); cell0.setCellValue("ID"); cell0.setCellStyle(style); // create a new cell in the row Cell cell1 = headerRow.createCell(1); cell1.setCellValue("Modell"); cell1.setCellStyle(style); // create a new cell in the row Cell cell2 = headerRow.createCell(2); cell2.setCellValue("Kennzeichen"); cell2.setCellStyle(style); // create a new cell in the row Cell cell3 = headerRow.createCell(3); cell3.setCellValue("Leistung"); cell3.setCellStyle(style); // create a new cell in the row Cell cell4 = headerRow.createCell(4); cell4.setCellValue("Baujahr"); cell4.setCellStyle(style); // create a new cell in the row Cell cell5 = headerRow.createCell(5); cell5.setCellValue("Aktiv"); cell5.setCellStyle(style); // create a new cell in the row Cell cell6 = headerRow.createCell(6); cell6.setCellValue("Funktion"); cell6.setCellStyle(style); // create a new cell in the row Cell cell7 = headerRow.createCell(7); cell7.setCellValue("Feuerwehr"); cell7.setCellStyle(style); // create multiple rows with fireEngines data int rowNum = 1; for (FireEngineModel fireEngine : fireEngines) { // create the row data Row row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(fireEngine.getId()); row.createCell(1).setCellValue(fireEngine.getModel()); row.createCell(2).setCellValue(fireEngine.getLicensePlate()); row.createCell(3).setCellValue(fireEngine.getPerformance()); row.createCell(4).setCellValue(fireEngine.getBuildYear()); row.createCell(5).setCellValue(fireEngine.getActive()); row.createCell(6).setCellValue(fireEngine.getAbbreviation().getAbbreviation()); row.createCell(7).setCellValue(fireEngine.getFireBrigade().getName()); } // adjust column width to fit the content sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.autoSizeColumn((short) 7); }
From source file:bad.robot.excel.style.ReplaceExistingStyle.java
License:Apache License
private void applyFontTo(CellStyle style, Workbook workbook) { if (fontSize != null) { Font font = workbook.createFont(); font.setFontHeightInPoints(fontSize.value()); font.setColor(fontColour.value().getPoiStyle()); style.setFont(font); } else {//from ww w . java 2 s .c o m // doesn't work Font existing = workbook.getFontAt(style.getFontIndex()); existing.setColor(fontColour.value().getPoiStyle()); style.setFont(existing); } }
From source file:balony.tableWriter.java
public static void writeTable(JFrame parent, Object[][] tableData, String[] colNames) { String opts[] = { "Tab-delimited text (raw)", "Excel .xls", "Excel 2007+ .xlsx" }; int i = JOptionPane.showOptionDialog(parent, "Choose output format:", "Export Table", JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null, opts, opts[0]); if (i == JOptionPane.CLOSED_OPTION) { return;//from w w w . jav a2 s . c o m } String[] chars = { ":", "[", "]", "/", "\\", "?", "*" }; String fname = parent.getTitle(); for (String c : chars) { fname = fname.replace(c, ""); } JFileChooser jfc = new JFileChooser(); if (i == 0) { jfc.setFileFilter(new FileNameExtensionFilter("Tab-delimited text files", "txt")); jfc.setSelectedFile(new File(fname.concat(".txt"))); } if (i == 1) { jfc.setFileFilter(new FileNameExtensionFilter("Excel .xls files", "xls")); jfc.setSelectedFile(new File(fname.concat(".xls"))); } if (i == 2) { jfc.setFileFilter(new FileNameExtensionFilter("Excel .xlsx files", "xlsx")); jfc.setSelectedFile(new File(fname.concat(".xlsx"))); } int rv = jfc.showSaveDialog(parent); if (rv == JFileChooser.APPROVE_OPTION) { File f = jfc.getSelectedFile(); try { if (i == 0) { BufferedWriter out = new BufferedWriter(new FileWriter(f)); for (String columnName : colNames) { out.write(columnName); out.write("\t"); } out.newLine(); for (Object[] tableData1 : tableData) { for (int k = 0; k < colNames.length; k++) { if (tableData1[k] != null) { out.write(tableData1[k].toString()); } out.write("\t"); } out.newLine(); } out.close(); } if (i > 0) { Workbook wb; if (i == 1) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } Sheet sheet = wb.createSheet(fname); Row r; CellStyle style; sheet.createFreezePane(0, 1); for (int j = 0; j < tableData.length; j++) { r = sheet.createRow(j + 1); for (int k = 0; k < colNames.length; k++) { if (tableData[j][k] != null) { Cell c = r.createCell(k); if (tableData[j][k] instanceof Integer) { c.setCellType(Cell.CELL_TYPE_NUMERIC); int v = ((Integer) tableData[j][k]); c.setCellValue(v); } else { if (tableData[j][k] instanceof Double) { c.setCellType(Cell.CELL_TYPE_NUMERIC); double v = ((Double) tableData[j][k]); c.setCellValue(v); } else { c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(tableData[j][k].toString()); } } } } } r = sheet.createRow(0); style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int k = 0; k < colNames.length; k++) { Cell c = r.createCell(k); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellStyle(style); c.setCellValue(colNames[k]); } FileOutputStream fos = new FileOutputStream(f); wb.write(fos); fos.close(); } } catch (Exception ex) { Logger.getLogger(dataTable.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(parent, "File error: ".concat(ex.getLocalizedMessage()), "File Error", JOptionPane.ERROR_MESSAGE); return; } int n = JOptionPane.showOptionDialog(parent, "File saved. Open in default application?", "Message", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, null, null); if (n == JOptionPane.YES_OPTION) { try { Desktop d = Desktop.getDesktop(); d.open(f); } catch (IOException e) { System.out.println(e.getLocalizedMessage()); } } } }
From source file:bandaru_excelreadwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*//from w w w .jav a 2 s.co m Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); style2.setAlignment(style2.ALIGN_CENTER); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Bandaru, Sreekanth"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_SINGLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); combined.setAlignment(combined.ALIGN_CENTER); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.LIME.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:F4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:F5"), CellRangeAddress.valueOf("A6:F6"), CellRangeAddress.valueOf("A7:F7"), CellRangeAddress.valueOf("A8:F8"), CellRangeAddress.valueOf("A13:F13"), CellRangeAddress.valueOf("A14:F14"), CellRangeAddress.valueOf("A15:F15"), CellRangeAddress.valueOf("A16:F16"), CellRangeAddress.valueOf("A23:F23"), CellRangeAddress.valueOf("A24:F24"), CellRangeAddress.valueOf("A25:F25"), CellRangeAddress.valueOf("A26:F26") }; /* setting new rule to apply alternate colors to cells having same Fenre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:F9"), CellRangeAddress.valueOf("A10:F10"), CellRangeAddress.valueOf("A11:F11"), CellRangeAddress.valueOf("A12:F12"), CellRangeAddress.valueOf("A17:F17"), CellRangeAddress.valueOf("A18:F18"), CellRangeAddress.valueOf("A19:F19"), CellRangeAddress.valueOf("A20:F20"), CellRangeAddress.valueOf("A21:F21"), CellRangeAddress.valueOf("A22:F22"), CellRangeAddress.valueOf("A27:F27"), CellRangeAddress.valueOf("A28:F28"), CellRangeAddress.valueOf("A29:F29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; CellStyle style4 = workbook.createCellStyle(); XSSFFont my_font2 = (XSSFFont) workbook.createFont(); my_font2.setBold(true); style4.setFont(my_font2); rowMain.setRowStyle(style4); rowMain.createCell(mainCellIndex++).setCellValue("SNO"); rowMain.createCell(mainCellIndex++).setCellValue("Genre"); rowMain.createCell(mainCellIndex++).setCellValue("Rating"); rowMain.createCell(mainCellIndex++).setCellValue("Movie Name"); rowMain.createCell(mainCellIndex++).setCellValue("Director"); rowMain.createCell(mainCellIndex++).setCellValue("Release Date"); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:biz.webgate.dominoext.poi.component.kernel.WorkbookProcessor.java
License:Apache License
public void setCellValue(Sheet shProcess, int nRow, int nCol, Object objValue, boolean isFormula, PoiCellStyle pCellStyle) {/*from www . j a v a2s. c o m*/ // Logger logCurrent = // LoggerFactory.getLogger(WorkbookProcessor.class.getCanonicalName()); try { Row rw = shProcess.getRow(nRow); if (rw == null) { // logCurrent.finest("Create Row"); rw = shProcess.createRow(nRow); } Cell c = rw.getCell(nCol); if (c == null) { // logCurrent.finest("Create Cell"); c = rw.createCell(nCol); } if (isFormula) { c.setCellFormula((String) objValue); } else { if (objValue instanceof Double) { c.setCellValue((Double) objValue); } else if (objValue instanceof Integer) { c.setCellValue((Integer) objValue); } else { if (objValue instanceof Date) { c.setCellValue((Date) objValue); } else { c.setCellValue("" + objValue); } } } // *** STYLE CONFIG Since V 1.1.7 *** if (pCellStyle != null) { checkStyleConstantValues(); if (pCellStyle.getCellStyle() != null) { c.setCellStyle(pCellStyle.getCellStyle()); } else { CellStyle style = shProcess.getWorkbook().createCellStyle(); if (pCellStyle.getAlignment() != null) style.setAlignment(m_StyleConstantValues.get(pCellStyle.getAlignment())); if (pCellStyle.getBorderBottom() != null) style.setBorderBottom(m_StyleConstantValues.get(pCellStyle.getBorderBottom())); if (pCellStyle.getBorderLeft() != null) style.setBorderLeft(m_StyleConstantValues.get(pCellStyle.getBorderLeft())); if (pCellStyle.getBorderRight() != null) style.setBorderRight(m_StyleConstantValues.get(pCellStyle.getBorderRight())); if (pCellStyle.getBorderTop() != null) style.setBorderTop(m_StyleConstantValues.get(pCellStyle.getBorderTop())); if (pCellStyle.getBottomBorderColor() != null) style.setBottomBorderColor( IndexedColors.valueOf(pCellStyle.getBottomBorderColor()).getIndex()); if (pCellStyle.getDataFormat() != null) { DataFormat format = shProcess.getWorkbook().createDataFormat(); style.setDataFormat(format.getFormat(pCellStyle.getDataFormat())); } if (pCellStyle.getFillBackgroundColor() != null) style.setFillBackgroundColor( IndexedColors.valueOf(pCellStyle.getFillBackgroundColor()).getIndex()); if (pCellStyle.getFillForegroundColor() != null) style.setFillForegroundColor( IndexedColors.valueOf(pCellStyle.getFillForegroundColor()).getIndex()); if (pCellStyle.getFillPattern() != null) style.setFillPattern(m_StyleConstantValues.get(pCellStyle.getFillPattern())); // Create a new font and alter it. Font font = shProcess.getWorkbook().createFont(); if (pCellStyle.getFontBoldweight() != null) font.setBoldweight(m_StyleConstantValues.get(pCellStyle.getFontBoldweight())); if (pCellStyle.getFontColor() != null) font.setColor(IndexedColors.valueOf(pCellStyle.getFontColor()).getIndex()); if (pCellStyle.getFontHeightInPoints() != 0) font.setFontHeightInPoints(pCellStyle.getFontHeightInPoints()); if (pCellStyle.getFontName() != null) font.setFontName(pCellStyle.getFontName()); if (pCellStyle.isFontItalic()) font.setItalic(pCellStyle.isFontItalic()); if (pCellStyle.isFontStrikeout()) font.setStrikeout(pCellStyle.isFontStrikeout()); if (pCellStyle.getFontUnderline() != null) font.setUnderline(m_StyleByteConstantValues.get(pCellStyle.getFontUnderline())); if (pCellStyle.getFontTypeOffset() != null) font.setTypeOffset(m_StyleConstantValues.get(pCellStyle.getFontTypeOffset())); // Set Font style.setFont(font); if (pCellStyle.isHidden()) style.setHidden(pCellStyle.isHidden()); if (pCellStyle.getIndention() != null) style.setIndention(m_StyleConstantValues.get(pCellStyle.getIndention())); if (pCellStyle.getLeftBorderColor() != null) style.setLeftBorderColor(IndexedColors.valueOf(pCellStyle.getLeftBorderColor()).getIndex()); if (pCellStyle.isLocked()) style.setLocked(pCellStyle.isLocked()); if (pCellStyle.getRightBorderColor() != null) style.setRightBorderColor( IndexedColors.valueOf(pCellStyle.getRightBorderColor()).getIndex()); if (pCellStyle.getRotation() != 0) style.setRotation(pCellStyle.getRotation()); if (pCellStyle.getTopBorderColor() != null) style.setTopBorderColor(IndexedColors.valueOf(pCellStyle.getTopBorderColor()).getIndex()); if (pCellStyle.getVerticalAlignment() != null) style.setVerticalAlignment(m_StyleConstantValues.get(pCellStyle.getVerticalAlignment())); if (pCellStyle.isWrapText()) style.setWrapText(pCellStyle.isWrapText()); c.setCellStyle(style); pCellStyle.setCellStyle(style); } } } catch (Exception e) { e.printStackTrace(); } }