List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle
void setCellStyle(CellStyle style);
Set the style for the cell.
From source file:com.tikal.tallerWeb.servicio.reporte.cliente.TotalServicioReporteCliente.java
License:Apache License
@Override public BordeSeccion generar(BordeSeccion borde, ContextoSeccion contexto, ReporteCliente datos) { Sheet sheet = contexto.getSheet();/*from w ww. j a v a2 s . c o m*/ XSSFWorkbook wb = contexto.getWb(); int initialRow = borde.getUpperRow(); int initialColumn = borde.getLeftColumn(); BordeSeccion r = new BordeSeccion(); r.setLeftColumn(initialColumn); r.setUpperRow(initialRow); Row row = getRow(sheet, initialRow); row.createCell(initialColumn).setCellValue("Total del servicio:"); //calculo del total Cell totalMecanica = (Cell) contexto.get("totalMecanica"); Cell totalHojalateria = (Cell) contexto.get("totalHojalateria"); Cell cell = row.createCell(initialColumn + 1); if (totalMecanica != null && totalHojalateria != null) { String formula = getSimpleReference(totalMecanica) + "+" + getSimpleReference(totalHojalateria); cell.setCellFormula(formula); } else { if (totalMecanica != null) { String formula = getSimpleReference(totalMecanica); cell.setCellFormula(formula); } if (totalHojalateria != null) { String formula = getSimpleReference(totalHojalateria); cell.setCellFormula(formula); } } if (totalMecanica == null && totalHojalateria == null) { cell.setCellValue(0d); } XSSFCellStyle cellStyle = wb.createCellStyle(); XSSFDataFormat df = wb.createDataFormat(); cellStyle.setDataFormat(df.getFormat("$#,##0.00")); cell.setCellStyle(cellStyle); r.setLowerRow(initialRow); r.setRightColumn(initialColumn + 1); return r; }
From source file:com.tm.hiber.service.util.DatabaseUtil.java
/** * * @param objFile/*ww w. j a va 2 s .c o m*/ * @param objJTable * @return number of records exported */ public int exportData(File objFile, JTable objJTable) throws FileNotFoundException, IOException { int response = 0; if (objFile == null) { return response; } DefaultTableModel tm = (DefaultTableModel) objJTable.getModel(); Object[] rows = tm.getDataVector().toArray(); JTableHeader columnNames = objJTable.getTableHeader(); TableColumnModel columnModel = columnNames.getColumnModel(); int columnCount = columnModel.getColumnCount(); Vector<String> vecColumnNames = new Vector<String>(); for (int c = 0; c < columnCount; c++) { vecColumnNames.add(columnModel.getColumn(c).getHeaderValue().toString()); } HSSFWorkbook exportReadyWorkbook = new HSSFWorkbook(); HSSFSheet dataSheet = exportReadyWorkbook.createSheet(getExportSheetName()); if (vecColumnNames.size() > 0) { int columnCounter = 0; Row objHSSFColumnName = dataSheet.createRow(0); for (String strColumnName : vecColumnNames) { /* Set Header CSS */ Cell objHSSFCell = objHSSFColumnName.createCell(columnCounter); objHSSFCell.setCellValue(strColumnName); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setFontName("Calibri"); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(true); csll.setFont(objFont); csll.setFillBackgroundColor(HSSFColor.YELLOW.index); csll.setFillForegroundColor(HSSFColor.YELLOW.index); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); objHSSFCell.setCellStyle(csll); columnCounter++; } } if (rows != null && rows.length > 0) { /* Set Data into Sheet */ for (int i = 0; i < rows.length; i++) { Vector objCellsData = (Vector) rows[i]; Row objHSSFRow = dataSheet.createRow(i + 1); if (objCellsData != null && objCellsData.size() > 0) { for (int j = 0; j < objCellsData.size(); j++) { /* Set Cell Data CSS */ Cell objHSSFCell = objHSSFRow.createCell(j); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(false); objFont.setFontName("Calibri"); csll.setFont(objFont); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setFillBackgroundColor(IndexedColors.WHITE.index); csll.setFillForegroundColor(IndexedColors.WHITE.index); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); csll.setBorderBottom(CellStyle.BORDER_THIN); csll.setBorderTop(CellStyle.BORDER_THIN); csll.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index); csll.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); objHSSFCell.setCellStyle(csll); Object cellData = objCellsData.get(j); objHSSFCell.setCellValue((String) cellData); } } } for (int i = 0; i < columnCount; i++) { if (i == 2) { dataSheet.setColumnWidth(i, 30 * 256); } else { dataSheet.autoSizeColumn(i); } } /* Write File */ FileOutputStream objFileOutputStream = new FileOutputStream(objFile); exportReadyWorkbook.write(objFileOutputStream); objFileOutputStream.flush(); objFileOutputStream.close(); response = rows.length; } return response; }
From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java
License:Open Source License
public void inlinePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver, DataSetRecord record, int row, int records) throws Exception { if (record == null) return;/*from w w w .j a va 2s.c o m*/ int currentRow = params.getCurrentRow(); Row excelRow = params.getSheet().createRow(currentRow); params.setCurrentRow(++currentRow); int colCount = dataSet.getFieldCount(); for (int col = 0; col < colCount; col++) { FieldDef fieldDef = dataSet.getFields().get(col); if (!fieldDef.isVisible()) continue; Object fieldValue = record.get(col); int fType = fieldDef.getSqlDataType(); String value = null; Cell dataCell; if (fieldValue != null) { value = dataSet.encode(fieldDef, fieldValue, driver, params.getParams(), false); } if (SqlUtils.isNumber(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellValue(value); } else if (SqlUtils.isDateOnly(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateCellStyle()); if (fieldValue instanceof java.util.Date) dataCell.setCellValue((java.util.Date) fieldValue); else dataCell.setCellValue(value); } else if (SqlUtils.isTime(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getTimeCellStyle()); if (fieldValue instanceof java.util.Date) dataCell.setCellValue((java.util.Date) fieldValue); else dataCell.setCellValue(value); } else if (SqlUtils.isTimestamp(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateTimeCellStyle()); if (fieldValue instanceof java.util.Date) dataCell.setCellValue((java.util.Date) fieldValue); else dataCell.setCellValue(value); } else if (SqlUtils.isBoolean(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_BOOLEAN); if (fieldValue instanceof Boolean) dataCell.setCellValue((Boolean) fieldValue); else dataCell.setCellValue(value); } else { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_STRING); dataCell.setCellValue(value); } } if (row >= 0 && records >= 0 && !params.isSilent() && params.getLogStep() > 0 && (row % params.getLogStep()) == 0) Logger.log(Logger.INFO, EtlLogger.class, dataSet.getName() + ": " + EtlResource.PERSITING_RECORD.getValue() + row + " out of " + records); }
From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;/*from ww w . j a va2 s . c o m*/ OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new HSSFWorkbook(); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java
License:Open Source License
public void inlinePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver, DataSetRecord record, int row, int records) throws Exception { if (record == null) return;/* www . j a v a2 s .c om*/ int currentRow = params.getCurrentRow(); Row excelRow = params.getSheet().createRow(currentRow); params.setCurrentRow(++currentRow); int colCount = dataSet.getFieldCount(); for (int col = 0; col < colCount; col++) { FieldDef fieldDef = dataSet.getFields().get(col); if (!fieldDef.isVisible()) continue; Object fieldValue = record.get(col); int fType = fieldDef.getSqlDataType(); String value = null; Cell dataCell; if (fieldValue != null) { value = dataSet.encode(fieldDef, fieldValue, driver, params.getParams(), false); } if (SqlUtils.isNumber(fType) && !Utils.isNothing(value)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellValue(Double.parseDouble(value)); } else if (SqlUtils.isDateOnly(fType)) { if (fieldValue instanceof java.util.Date) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateCellStyle()); dataCell.setCellValue((java.util.Date) fieldValue); } else { if (com.toolsverse.util.DateUtil.isValidDate(value)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateCellStyle()); dataCell.setCellValue(com.toolsverse.util.DateUtil.parse(value)); } else { dataCell = excelRow.createCell(col); dataCell.setCellValue(value); } } } else if (SqlUtils.isTime(fType)) { if (fieldValue instanceof java.util.Date) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getTimeCellStyle()); dataCell.setCellValue((java.util.Date) fieldValue); } else { if (com.toolsverse.util.DateUtil.isValidDate(value)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getTimeCellStyle()); dataCell.setCellValue(com.toolsverse.util.DateUtil.parse(value)); } else { dataCell = excelRow.createCell(col); dataCell.setCellValue(value); } } } else if (SqlUtils.isTimestamp(fType)) { if (fieldValue instanceof java.util.Date) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateTimeCellStyle()); dataCell.setCellValue((java.util.Date) fieldValue); } else { if (com.toolsverse.util.DateUtil.isValidDate(value)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_NUMERIC); dataCell.setCellStyle(params.getDateTimeCellStyle()); dataCell.setCellValue(com.toolsverse.util.DateUtil.parse(value)); } else { dataCell = excelRow.createCell(col); dataCell.setCellValue(value); } } } else if (SqlUtils.isBoolean(fType)) { dataCell = excelRow.createCell(col, Cell.CELL_TYPE_BOOLEAN); if (fieldValue instanceof Boolean) dataCell.setCellValue((Boolean) fieldValue); else dataCell.setCellValue(Utils.str2Boolean(value, false)); } else { dataCell = excelRow.createCell(col); dataCell.setCellValue(value); } } if (row >= 0 && records >= 0 && !params.isSilent() && params.getLogStep() > 0 && (row % params.getLogStep()) == 0) Logger.log(Logger.INFO, EtlLogger.class, dataSet.getName() + ": " + EtlResource.PERSITING_RECORD.getValue() + row + " out of " + records); }
From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;/*w w w . j a v a 2 s.co m*/ OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new SXSSFWorkbook(100); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.ts.excelservlet.UDR_Driver_Excel.java
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) *//*from w ww . java 2s. co m*/ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub DBTransaction dbtranobj = new DBTransaction(); String vehicle_number = request.getParameter("vehicle_number"); HttpSession session = request.getSession(true); String[] select = (String[]) session.getAttribute("id"); String driver_name = request.getParameter("driver_name"); //System.out.println("IMEI : " +imeinumber); //String vehicle_number=""; int index = 2; System.out.println("************** doGet ************"); OutputStream out = null; try { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Driver_UDR.xls"); Connection con = dbtranobj.connect(); // Class.forName("org.postgresql.Driver").newInstance(); //conn = DriverManager.getConnection("jdbc:postgresql://182.72.167.34:5432/master_database","postgres", "postgres"); ResultSet rs = null; Statement st = null; st = con.createStatement(); rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Driver Sheet"); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1)); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); /* sheet.addMergedRegion(new Region(0,(short)0,0,(short)3)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)4)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));*/ HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setHeight((short) 500); /*rowhead.createCell((short) 0).setCellValue("Fuel Information For " +vehicle_number); * */ HSSFCell cell2B = rowhead.createCell(0); cell2B.setCellValue(new HSSFRichTextString("Report For Driver : " + driver_name)); // Style Font in Cell 2B HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle = wb.createCellStyle(); HSSFFont hSSFFont = wb.createFont(); hSSFFont.setFontName(HSSFFont.FONT_ARIAL); hSSFFont.setFontHeightInPoints((short) 14); hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont.setColor(HSSFColor.BLUE.index); cellStyle.setFont(hSSFFont); cell2B.setCellStyle(cellStyle); HSSFRow rowhead1 = sheet.createRow((short) 1); rowhead1.setHeight((short) 600); Cell cell = rowhead1.createCell((short) 0); HSSFCellStyle cellStyle1 = wb.createCellStyle(); cellStyle1 = wb.createCellStyle(); HSSFFont hSSFFont1 = wb.createFont(); hSSFFont1.setFontName(HSSFFont.FONT_ARIAL); hSSFFont1.setFontHeightInPoints((short) 12); hSSFFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont1.setColor(HSSFColor.BLACK.index); cellStyle1.setFont(hSSFFont1); cell.setCellStyle(cellStyle1); // cell.setCellValue("SNO"); sheet.setColumnWidth(0, 7000); // rowhead.createCell((short) 0).setCellValue("Date"); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { if (select[i].equalsIgnoreCase("doe")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE EXPIRY"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("doj")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("JOINED DATE"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("license_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE NUMBER "); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("vehicle_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("VEHICLE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("shift_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("SHIFT NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("route_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ROUTE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("address")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ADDRESS"); sheet.setColumnWidth(i, 7000); } else { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue(select[i]); sheet.setColumnWidth(i, 7000); } } } rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); while (rs.next()) { HSSFRow row = sheet.createRow((short) index); row.setHeight((short) 500); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { row.createCell((short) i).setCellValue(rs.getString(select[i])); } } index++; } out = response.getOutputStream(); wb.write(out); } catch (Exception e) { throw new ServletException("Exception in Excel Sample Servlet", e); } finally { if (out != null) out.close(); } }
From source file:com.tutorial.excelreadwrite.excelFunctions.java
public void convertColor(int r, int g, int b, int numColors) { //Get the userDefinedColor and set the style userDefinedColor = new XSSFColor(new java.awt.Color(r, g, b)); XSSFCellStyle userDefinedCS = workbook.createCellStyle(); userDefinedCS.setFillForegroundColor(userDefinedColor); userDefinedCS.setFillPattern(CellStyle.SOLID_FOREGROUND); //Create an arrayList and add foreground colors that will be converted and then remove them List<XSSFColor> listOfColors = new ArrayList(); for (int i = 0; i < numColors; ++i) { try { //First row of excel document will be reserved for obtaining the colors of the foreground used listOfColors.add(sheet.getRow(0).getCell(i).getCellStyle().getFillForegroundXSSFColor()); sheet.getRow(0).getCell(i).setCellStyle(null); } catch (NullPointerException ex) { throw new NullPointerException("Either incorrect # colors entered OR colors NOT SET."); }/*w ww . j a v a 2s .c o m*/ } //Set-up rowIterator and get Row Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //Set-up cellIterator and get Cell Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Null-Check for Cell if (cell != null) { //Get the Cell Style, Null-Check for Cell Style XSSFCellStyle currCellStyle = (XSSFCellStyle) cell.getCellStyle(); if (currCellStyle != null) { //Get the fillForeground color XSSFColor fgColor = currCellStyle.getFillForegroundXSSFColor(); //cycle through ArrayList and compare if any of the colors listed matches for (XSSFColor col : listOfColors) { if (col.equals(fgColor)) { cell.setCellStyle(userDefinedCS); } } } } } } }
From source file:com.tutorial.excelreadwrite.excelFunctions.java
public void markHorizontal(int spacesApart){ //Set-up rowIterator and get Row Iterator<Row> rowIterator = sheet.iterator(); while(rowIterator.hasNext()){ Row row = rowIterator.next(); //w w w . j a va 2 s . c o m //Set-up cellIterator and get Cell Iterator<Cell> cellIterator = row.cellIterator(); while(cellIterator.hasNext()){ Cell cell = cellIterator.next(); //Obtains the Cell Style XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle(); //Checks to see if the Cell Style is null; if null, go to next cell if(cellStyle != null){ //Checks to see what color is the cell's color XSSFColor cellColor = cellStyle.getFillForegroundXSSFColor(); //Checks to see if color is null; if not compare to accept only editted or userDefined cells if(cellColor != null){ //Checks if current cell is userDefined or editted //If it is not, then go to the next cell if(cellColor.equals(mark.getFillForegroundXSSFColor()) || cellColor.equals(userDefinedColor)){ //Set boolean isCellMarked to false before proceeding isCellMarked = false; //Define Cell to be (spacesApart+1) away //So if x = current cell then the cell that is 5 spacesApart = // [x][][][][][][x] Cell cellMark = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex() + spacesApart + 1); //Checks to see if cell is null; if present, get its Cell Style if(cellMark != null){ XSSFCellStyle cellMarkStyle = (XSSFCellStyle)cellMark.getCellStyle(); //Checks to see if the style is null; if present, get its color if(cellMarkStyle != null){ XSSFColor cellMarkColor = cellMarkStyle.getFillForegroundXSSFColor(); //Checks to see if the color is null; if present, compare colors if(cellMarkColor != null){ if(cellMarkColor.equals(userDefinedColor)){ isCellMarked = true; } } } } /* ** CHECK#1: 'isCellMarked' ** If isCellMarked is marked true, start iterating through the ** cells in between and check if null or not userDefinedStyle */ if(isCellMarked == true){ for(int i = 1; i <= spacesApart; ++i){ Cell isNull = sheet.getRow(cell.getRowIndex()).getCell(cell.getColumnIndex()+i); //Checks to see if the cell is null; if color is present, set isCellMarked to false if(isNull != null){ XSSFCellStyle cellCheckIfNullCellStyle = (XSSFCellStyle)isNull.getCellStyle(); if(cellCheckIfNullCellStyle != null){ XSSFColor cellCheckIfNullColor = cellCheckIfNullCellStyle.getFillForegroundXSSFColor(); if(cellCheckIfNullColor != null){ if(cellCheckIfNullColor.equals(userDefinedColor)){ isCellMarked = false; break; } } } } } } /* ** CHECK#2: 'isCellMarked2' ** If isCellMarked remains as true, set the two cell's style */ if(isCellMarked == true){ cell.setCellStyle(mark); cellMark.setCellStyle(mark); } } } } } } }