List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell
@Override public XSSFCell createCell(int columnIndex)
From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?.// w w w . j a va2 s . c o m * * @param colDesc 1717?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???1717 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?1717 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?./*from ww w .ja va 2 s . com*/ * * @param colDesc * "?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { // ??? List<String> colFieldList = getColumnFieldList(titlesList); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); // ? CellStyle dateCellStyle = getDateTimeCellStyle(wb); // ??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < titlesList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titlesList.get(i)); } ClassInfo classInfo = null; // ? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); if (classInfo == null) classInfo = ClassUtil.getClassInfo(obj.getClass()); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName); Object value = reflexField.getValue(obj); // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } // ?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date || value instanceof Timestamp) { cell.setCellStyle(dateCellStyle); cell.setCellValue(sdf.format((Date) value)); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } // ?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < titlesList.size(); i++) { // ?? sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./*from w ww. j av a2 s . c o m*/ * * @param colDesc ?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //??? //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //?? XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.FuntionLibrary.java
public void writetoExcel(String Name, String EmailId, String Password, String MobileNo, XSSFSheet sheet, String MailPaswd) throws InvalidFormatException, IOException { try {/* w ww .j a va 2s . com*/ //String excelFileName = "";//name of excel file int r = sheet.getLastRowNum(); XSSFRow r1 = sheet.createRow(0); XSSFCell Srnocell = r1.createCell(0); Srnocell.setCellValue("Name"); XSSFCell actioncell = r1.createCell(1); actioncell.setCellValue("EmailId"); XSSFCell loccell = r1.createCell(2); loccell.setCellValue("Password"); XSSFCell valuecell = r1.createCell(3); valuecell.setCellValue("Mobile Number"); XSSFCell EmailPassword = r1.createCell(4); EmailPassword.setCellValue("Email Password"); XSSFRow row = sheet.createRow(r + 1); //iterating c number of columns XSSFCell cell = row.createCell(0); cell.setCellValue(Name); System.out.println(Name); XSSFCell cell1 = row.createCell(1); cell1.setCellValue(EmailId); System.out.println(EmailId); XSSFCell cell2 = row.createCell(2); cell2.setCellValue(Password); System.out.println(Password); XSSFCell cell3 = row.createCell(3); cell3.setCellValue(MobileNo); System.out.println(MobileNo); XSSFCell cell4 = row.createCell(4); cell4.setCellValue(MailPaswd); System.out.println(MailPaswd); } catch (Exception e) { JOptionPane.showMessageDialog(null, "Oops Something Went wrong Error - > " + e.getMessage()); } }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to.//from w ww .j a v a 2 s . c o m * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to write the date graph data as a Excel workbook; required since PowerPoint charts actually * embed an Excel file with corresponding data. If invalid, it'll open in OpenOffice fine, but PowerPoint will * complain that the presentation is corrupted. * @param data the date graph data.//from w w w . j a v a 2s .c o m * @return a new Excel workbook with specified data on a new sheet. */ private static XSSFWorkbook writeChart(final DategraphData data) { final XSSFWorkbook wb = new XSSFWorkbook(); final XSSFSheet sheet = wb.createSheet("Sheet1"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat((short) 14); final List<DategraphData.Row> rows = data.getRows(); final long[] timestamps = data.getTimestamps(); final XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Timestamp"); for (int ii = 0; ii < rows.size(); ++ii) { header.createCell(ii + 1).setCellValue(rows.get(ii).getLabel()); } for (int rowIdx = 0; rowIdx < timestamps.length; ++rowIdx) { final XSSFRow row = sheet.createRow(rowIdx + 1); final XSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(new Date(timestamps[rowIdx] * 1000)); for (int ii = 0; ii < rows.size(); ++ii) { row.createCell(ii + 1).setCellValue(rows.get(ii).getValues()[rowIdx]); } } return wb; }
From source file:com.knsi.WriteDB.java
public static void writeTo(JTextField labels[], String pof) { try {/*w w w . j a v a 2 s . com*/ File db = new File(labels[4].getText() + ".xlsx"); System.out.println(db.getAbsolutePath()); Date d = new Date(); System.out.println(d.toString()); FileInputStream dbStream = new FileInputStream(db); XSSFWorkbook details = new XSSFWorkbook(dbStream); XSSFSheet spreadsheet1 = details.getSheet("details"); XSSFSheet spreadsheet2 = details.getSheet("Fees"); XSSFSheet spreadsheet3 = details.getSheet("Performance"); XSSFSheet spreadsheet4 = details.getSheet("Attendence"); System.out.println(spreadsheet1.getLastRowNum() + "------------" + spreadsheet2.getLastRowNum()); //Create row object XSSFRow row1, row2, row3, row4; //This data needs to be written (Object[]) String obj1[] = new String[14]; int j = 1; for (int i = 0; i <= 12; i++) { if (i == 4) { continue; } if (labels[i].getText().equalsIgnoreCase("")) { obj1[j] = "N/A"; } else { obj1[j] = labels[i].getText(); } j++; } //obj1[0]=Integer.toString(spreadsheet1.getLastRowNum()+1); obj1[0] = getRegId(obj1[0], Integer.toString(spreadsheet1.getLastRowNum() + 1), labels[4].getText(), pof); obj1[13] = d.toString(); System.out.println("The details for the details sheet is:"); for (int i = 0; i < 14; i++) { System.out.println(obj1[i]); } String obj2[] = new String[4]; obj2[0] = obj1[0]; obj2[1] = labels[13].getText(); obj2[2] = labels[14].getText(); obj2[3] = labels[13].getText(); System.out.println("The details for the fees sheet is:"); for (int i = 0; i < 4; i++) { System.out.println(obj2[i]); } String obj3[] = new String[2]; obj3[0] = obj1[0]; obj3[1] = obj1[1]; int rowid1 = spreadsheet1.getLastRowNum() + 1; int rowid2 = spreadsheet2.getLastRowNum() + 1; int rowid3 = spreadsheet3.getLastRowNum() + 1; int rowid4 = spreadsheet4.getLastRowNum() + 1; row1 = spreadsheet1.createRow(rowid1); row2 = spreadsheet2.createRow(rowid2); row3 = spreadsheet3.createRow(rowid3); row4 = spreadsheet4.createRow(rowid4); int cellid = 0; for (String obj : obj1) { Cell cell = row1.createCell(cellid++); cell.setCellValue(obj); } cellid = 0; for (String obj : obj2) { Cell cell = row2.createCell(cellid++); cell.setCellValue(obj); } cellid = 0; for (String obj : obj3) { Cell cell = row3.createCell(cellid); cell.setCellValue(obj); Cell cell2 = row4.createCell(cellid); cell2.setCellValue(obj); cellid++; } //Write the workbook in file system FileOutputStream out = new FileOutputStream(db); details.write(out); out.close(); System.out.println(db + " written successfully"); s = db.getAbsolutePath(); } catch (Exception ee) { s = ee.getMessage(); System.out.println(s); } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileTransformed() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/*from www . j a va 2s . com*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } XSSFWorkbook transformedWB = new XSSFWorkbook(); transformedWB.createSheet(); XSSFSheet transformedS = transformedWB.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { transformedS.createRow(i); } /* elkezdnk vgigmenni az alap sheeten ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre */ ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum()).setCellValue(sum); } } for (int index : matchingIndexes) { for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); row.getCell(index).setCellValue(3.14159); } /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { if(i == index){ for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); transformedS.getRow(j).createCell(transformedColumnCount).setCellValue( row.getCell(i).getRawValue() ); } transformedColumnCount++; } }*/ } int columnsInTransformed = 0; for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { String cellValue = row.getCell(j).getRawValue(); if (!cellValue.equals("3.14159")) { transformedS.getRow(i).createCell(columnsInTransformed); transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue); columnsInTransformed++; } } columnsInTransformed = 0; } row = transformedS.getRow(0); row.createCell(row.getLastCellNum()).setCellValue(atcMask); File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); transformedWB.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileFiltered() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/*from w ww .ja va 2 s . c om*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } row = sheet.getRow(0); row.createCell(row.getLastCellNum()).setCellValue("ATC mask:"); row.createCell(row.getLastCellNum()).setCellValue(atcMask); ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); System.out.println("SUMS"); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum() + 1).setCellValue(sum); } } File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); workbookToModify.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.pe.nisira.movil.view.action.MultitablaAction.java
public StreamedContent downFormatExcel() throws Exception { InputStream stream = null;/*from ww w.j a va2 s . c om*/ StreamedContent arch = null; try { String folder = "C:\\SOLUTION\\WEB\\FORMATOS_IMPORTACION"; File ruta = new File(folder); if (!ruta.isDirectory()) { ruta.mkdirs(); } String rutaArchivo = folder + "\\FI_MULTITABLA.xlsx"; File fileXls = new File(rutaArchivo); if (fileXls.exists()) { fileXls.delete(); } fileXls.createNewFile(); XSSFWorkbook libro = new XSSFWorkbook(); FileOutputStream file = new FileOutputStream(fileXls); XSSFSheet hoja = libro.createSheet("IMPORTAR_MULTITABLA"); CreationHelper factory = libro.getCreationHelper(); hoja = libro.getSheetAt(0); XSSFCellStyle style = libro.createCellStyle(); Font font = libro.createFont(); Font font1 = libro.createFont(); Drawing drawing = hoja.createDrawingPatriarch(); ClientAnchor anchor1 = factory.createClientAnchor(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 8); font1.setFontHeightInPoints((short) 8); font1.setFontName("Arial"); font.setFontName("Arial"); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(247, 150, 70))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.VERTICAL_CENTER); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); for (int f = 0; f < 1; f++) { XSSFRow fila = hoja.createRow(f); for (int c = 0; c < 4; c++) { XSSFCell celda = fila.createCell(c); celda.setCellStyle(style); anchor1.setCol1(celda.getColumnIndex()); anchor1.setCol2(celda.getColumnIndex() + 4); anchor1.setRow1(fila.getRowNum()); anchor1.setRow2(fila.getRowNum() + 3); Comment comment = drawing.createCellComment(anchor1); if (f == 0 && c == 0) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Obligatorio \n - Indicar si es es Padre (Usar SI o NO)."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Es Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 1) { RichTextString str = factory.createRichTextString( "ADM:\nCampo Opcional \n - Escribir la Abreviatura del campo del cual depende este."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("Abreviatura Padre"); celda.setCellComment(comment); } else if (f == 0 && c == 2) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Descripcion de la multitabla"); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("DESCRIPCION"); celda.setCellComment(comment); } else if (f == 0 && c == 3) { RichTextString str = factory .createRichTextString("ADM:\nCampo Obligatorio \n - Abreviatura de la multitabla."); str.applyFont(font1); str.applyFont(0, 29, font); comment.setString(str); comment.setAuthor("ADM"); celda.setCellValue("ABREVIATURA"); celda.setCellComment(comment); } } } hoja.autoSizeColumn((short) 0); hoja.autoSizeColumn((short) 1); hoja.autoSizeColumn((short) 2); libro.write(file); file.close(); stream = new FileInputStream(new File(rutaArchivo)); arch = new DefaultStreamedContent(stream, "application/xlsx", "FI_MULTITABLA.xlsx"); } catch (FileNotFoundException ex) { System.out.println("Error al Descargar : " + ex.getMessage()); } return arch; }