Examples with XSSFCell org.apache.poi.xssf.usermodel.XSSFCell used on opensource projects

Example 41 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project selenium_java by sergueik.

the class ReportData method createExcelFile.

// @SuppressWarnings("deprecation")
public static XSSFWorkbook createExcelFile(HashMap<String, Map<String, ArrayList<String>>> data) {
    XSSFWorkbook book = new XSSFWorkbook();
    XSSFCellStyle failCelStyle = book.createCellStyle();
    XSSFCellStyle passCelStyle = book.createCellStyle();
    // http://stackoverflow.com/questions/19145628/auto-size-height-for-rows-in-apache-poi
    for (String sheetNameKey : data.keySet()) {
        XSSFSheet sheet = book.createSheet(sheetNameKey);
        XSSFRow row = ExcelConfiguration.CreateHeader(book, sheet, Configuration.header);
        HashMap<String, ArrayList<String>> testMethods = (HashMap<String, ArrayList<String>>) data.get(sheetNameKey);
        int l = 1;
        for (String testMethod : testMethods.keySet()) {
            passCelStyle.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
            failCelStyle.setFillForegroundColor(HSSFColor.RED.index);
            // XSSFColor myColor = new XSSFColor(XSSFColor.);
            // failCelStyle.setFillForegroundColor(myColor);
            // converting to poi 3.17
            // passCelStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            passCelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            // failCelStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            failCelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            row = sheet.createRow(l++);
            XSSFCell cellName = row.createCell(Configuration.testNameIndex);
            cellName.setCellValue(testMethod);
            sheet.autoSizeColumn(Configuration.testNameIndex);
            ArrayList<String> testData = testMethods.get(testMethod);
            XSSFCell cellStatus = row.createCell(Configuration.testStatusIndex);
            if ("fail".equalsIgnoreCase(testData.get(Configuration.testStatusIndex))) {
                cellStatus.setCellStyle(failCelStyle);
                cellStatus.setCellValue(testData.get(Configuration.testStatusIndex));
                XSSFCell expCell = row.createCell(Configuration.exceptionMsgIndex);
                expCell.setCellValue(testData.get(Configuration.exceptionMsgIndex));
                sheet.autoSizeColumn(Configuration.exceptionMsgIndex);
                XSSFCell exceptionTraceCell = row.createCell(Configuration.exceptionStackTrace);
                exceptionTraceCell.setCellValue(testData.get(Configuration.exceptionStackTrace).trim());
                sheet.autoSizeColumn(Configuration.exceptionStackTrace);
                XSSFCell locatorCell = row.createCell(Configuration.locatorIndex);
                String text = testData.get(Configuration.exceptionStackTrace).trim();
                String jsonString = null;
                Gson gson = new GsonBuilder().create();
                int sIndex = text.indexOf('{');
                int eIndex = text.indexOf('}');
                jsonString = (sIndex == -1 || eIndex == -1) ? "" : text.substring(sIndex, (eIndex + 1));
                ElementLocator locator = gson.fromJson(jsonString, ElementLocator.class);
                locatorCell.setCellValue((locator == null) ? "" : locator.toString());
            } else {
                cellStatus.setCellStyle(passCelStyle);
                cellStatus.setCellValue(testData.get(Configuration.testStatusIndex));
                sheet.autoSizeColumn(Configuration.testStatusIndex);
                XSSFCell expCell = row.createCell(Configuration.exceptionMsgIndex);
                expCell.setCellValue(testData.get(Configuration.exceptionMsgIndex));
                sheet.autoSizeColumn(Configuration.exceptionMsgIndex);
                XSSFCell exceptionTraceCell = row.createCell(Configuration.exceptionStackTrace);
                exceptionTraceCell.setCellValue(testData.get(Configuration.exceptionStackTrace).trim());
                sheet.autoSizeColumn(Configuration.exceptionStackTrace);
            }
        }
    }
    return book;
}

Example 42 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project financial by greatkendy123.

the class ExcelReaderUtil method readZJRecord_NewVersion.

/**
 * 导入战绩Excel
 *
 * 两个功能：
 * 	A:导入到场次信息
 * 	B:导入到联盟Tab
 *
 * @param file 文件夹
 * @return
 */
public static Wrap readZJRecord_NewVersion(File file, String userClubId, String LMType) {
    // 新增了联盟类型
    List<UserInfos> userInfoList = new LinkedList<>();
    UserInfos info = null;
    FileInputStream is = null;
    // 是否已经判断过为空 波哥要求添加
    boolean isHasJudged = false;
    try {
        // 桌号
        String name = file.getName();
        String tableId = name.substring(name.lastIndexOf("-") + 1, name.lastIndexOf("."));
        tableId = "第" + tableId + "局";
        if (StringUtil.isBlank(userClubId) || DataConstans.Index_Table_Id_Map.containsValue(tableId)) {
            return new Wrap(false, "该战绩表(" + tableId + "场次)已经导过");
        }
        log.info("开始----------导入战绩Excel");
        userInfoList = new LinkedList<>();
        is = new FileInputStream(file);
        // 获取excel sheet
        Workbook workbook = (XSSFWorkbook) getWeebWork(file.getAbsolutePath());
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        // 若无数据，要提示
        int rowNum = sheet.getLastRowNum();
        if (rowNum == 0 && !isHasJudged) {
            isHasJudged = true;
            ShowUtil.show("提示：总手数为0！");
        }
        // 开始遍历Excel行数据
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            XSSFRow row = (XSSFRow) rowIterator.next();
            // add 总手数为空的提示
            // Cell ZSScell = row.getCell(6);
            XSSFCell ZSScell = row.getCell(7);
            if (ZSScell == null) {
                continue;
            }
            // 总手数
            String totalHandCount = ZSScell.toString();
            Integer _tempCount = 0;
            if (!"总手数".equals(totalHandCount)) {
                try {
                    _tempCount = Integer.valueOf(totalHandCount);
                    if (_tempCount == 0) {
                        if (!isHasJudged) {
                            isHasJudged = true;
                            ShowUtil.show("提示：总手数为0！");
                        }
                    }
                } catch (Exception e) {
                    _tempCount = 1;
                }
            }
            info = new UserInfos();
            // 排除第一行以及俱乐部ID不匹配的情况
            // String clubID = row.getCell(9).toString();
            String clubID = row.getCell(10).toString();
            if (row.getRowNum() != 0 && userClubId.equals(row.getCell(10).toString())) {
                // 对于符合条件的进行存储
                // int[] clumns = new int[]{6,7,8,9, 10,15,17,18,19};
                int[] clumns = new int[] { 7, 8, 9, 10, 11, 16, 18, 19, 20 };
                for (int cn : clumns) {
                    XSSFCell cell = row.getCell(cn);
                    if (cell == null) {
                        log.error("出现空值，导入战绩文件夹失败" + "\t");
                        return new Wrap();
                    }
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    value = StringUtil.isBlank(value) ? "" : value.trim();
                    switch(cn) {
                        case // add 总手数（只判断不导入）
                        7:
                            if ((StringUtil.isBlank(value) || "0".equals(value)) && !isHasJudged) {
                                isHasJudged = true;
                                ShowUtil.show("提示：总手数为0！");
                            }
                            break;
                        case 8:
                            info.setPlayerId(value);
                            break;
                        case 9:
                            info.setPlayerName(value);
                            break;
                        case 10:
                            info.setClubId(value);
                            break;
                        case 11:
                            info.setClubName(value);
                            break;
                        case 16:
                            info.setInsurance(value);
                            break;
                        case 19:
                            info.setZj(value);
                            break;
                        case 20:
                            {
                                String dateStr = value.split(" ")[0];
                                info.setDay(value);
                                // add 导入的第一局作为当天的时间
                                if (StringUtil.isBlank(DataConstans.Date_Str)) {
                                    DataConstans.Date_Str = dateStr;
                                } else {
                                    try {
                                        if (sdf.parse(dateStr).before(sdf.parse(DataConstans.Date_Str))) {
                                            DataConstans.Date_Str = dateStr;
                                        }
                                    } catch (Exception e) {
                                    }
                                }
                                break;
                            }
                        default:
                            break;
                    }
                }
                info.setTableId(tableId);
                userInfoList.add(info);
            }
        }
        // 遍历excel结束
        // 存储数据  {场次=infoList...}
        DataConstans.zjMap.put(tableId, userInfoList);
        is.close();
        // add 添加所有记录到联盟对帐表
        LMController.currentRecordList = LM_ExcelReaderUtil.readRecord_NewVersion(file);
        // LMController.refreshClubList();//放到锁定时去添加
        // LMController.checkOverEdu();
        log.info("结束----------导入战绩Excel" + " ===size:" + userInfoList.size());
    } catch (Exception e) {
        e.printStackTrace();
        return new Wrap();
    }
    return new Wrap(true, userInfoList);
}

Example 43 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project irida by phac-nml.

the class ProjectSamplesMetadataTemplateController method downloadTemplate.

/**
 * Download a {@link MetadataTemplate} as an Excel file.
 *
 * @param templateId
 * 		{@link Long} identifier for a {@link MetadataTemplate}
 * @param response
 * 		{@link HttpServletResponse}
 *
 * @throws IOException
 * 		thrown if output stream cannot be used.
 */
@RequestMapping(value = "/{templateId}/excel")
public void downloadTemplate(@PathVariable Long templateId, HttpServletResponse response) throws IOException {
    MetadataTemplate template = metadataTemplateService.read(templateId);
    List<MetadataTemplateField> fields = template.getFields();
    List<String> headers = fields.stream().map(MetadataTemplateField::getLabel).collect(Collectors.toList());
    String label = template.getLabel().replace(" ", "_");
    // Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    // Create a blank sheet
    XSSFSheet worksheet = workbook.createSheet(label);
    // Write the headers
    XSSFRow headerRow = worksheet.createRow(0);
    for (int i = 0; i < headers.size(); i++) {
        XSSFCell cell = headerRow.createCell(i);
        cell.setCellValue(headers.get(i));
    }
    response.setHeader("Content-Disposition", "attachment; filename=\"" + label + ".xlsx\"");
    ServletOutputStream stream = response.getOutputStream();
    workbook.write(stream);
    stream.flush();
}

Example 44 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project goci by EBISPOT.

the class SheetProcessorImpl method createHeaderMap.

@Override
public Map<Integer, UploadFileHeader> createHeaderMap(XSSFRow row) {
    Map<Integer, UploadFileHeader> headerMap = new HashMap<>();
    if (row.getPhysicalNumberOfCells() != 0) {
        short minColIx = row.getFirstCellNum();
        short maxColIx = row.getLastCellNum();
        for (short colIx = minColIx; colIx < maxColIx; colIx++) {
            XSSFCell cell = row.getCell(colIx);
            UploadFileHeader headerType = translateUploadHeaders.translateToEnumValue(cell.getStringCellValue().trim());
            headerMap.put((int) colIx, headerType);
        }
    } else {
        getLog().error("Header column contains no cells");
    }
    return headerMap;
}

Example 45 with XSSFCell

use of org.apache.poi.xssf.usermodel.XSSFCell in project structr by structr.

the class ToExcelFunction method writeExcel.

public Workbook writeExcel(final List list, final String propertyView, final List<String> properties, final boolean includeHeader, final boolean localizeHeader, final String headerLocalizationDomain, final Locale locale) throws IOException {
    final Workbook workbook = new XSSFWorkbook();
    final XSSFSheet sheet = (XSSFSheet) workbook.createSheet();
    int rowCount = 0;
    int cellCount = 0;
    XSSFRow currentRow = null;
    XSSFCell cell = null;
    if (includeHeader) {
        currentRow = (XSSFRow) sheet.createRow(rowCount++);
        cellCount = 0;
        if (propertyView != null) {
            final Object obj = list.get(0);
            if (obj instanceof GraphObject) {
                for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) {
                    cell = (XSSFCell) currentRow.createCell(cellCount++);
                    String value = key.dbName();
                    if (localizeHeader) {
                        try {
                            value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain);
                        } catch (FrameworkException fex) {
                            logger.warn("to_excel(): Exception", fex);
                        }
                    }
                    cell.setCellValue(value);
                }
            } else {
                cell = (XSSFCell) currentRow.createCell(cellCount++);
                cell.setCellValue("Error: Object is not of type GraphObject, can not determine properties of view for header row");
            }
        } else if (properties != null) {
            for (final String colName : properties) {
                cell = (XSSFCell) currentRow.createCell(cellCount++);
                String value = colName;
                if (localizeHeader) {
                    try {
                        value = LocalizeFunction.getLocalization(locale, value, headerLocalizationDomain);
                    } catch (FrameworkException fex) {
                        logger.warn("to_excel(): Exception", fex);
                    }
                }
                cell.setCellValue(value);
            }
        }
    }
    for (final Object obj : list) {
        currentRow = (XSSFRow) sheet.createRow(rowCount++);
        cellCount = 0;
        if (propertyView != null) {
            if (obj instanceof GraphObject) {
                for (PropertyKey key : ((GraphObject) obj).getPropertyKeys(propertyView)) {
                    final Object value = ((GraphObject) obj).getProperty(key);
                    cell = (XSSFCell) currentRow.createCell(cellCount++);
                    cell.setCellValue(escapeForExcel(value));
                }
            } else {
                cell = (XSSFCell) currentRow.createCell(cellCount++);
                cell.setCellValue("Error: Object is not of type GraphObject, can not determine properties of object");
            }
        } else if (properties != null) {
            if (obj instanceof GraphObject) {
                final GraphObject castedObj = (GraphObject) obj;
                for (final String colName : properties) {
                    final PropertyKey key = StructrApp.key(obj.getClass(), colName);
                    final Object value = castedObj.getProperty(key);
                    cell = (XSSFCell) currentRow.createCell(cellCount++);
                    cell.setCellValue(escapeForExcel(value));
                }
            } else if (obj instanceof Map) {
                final Map castedObj = (Map) obj;
                for (final String colName : properties) {
                    final Object value = castedObj.get(colName);
                    cell = (XSSFCell) currentRow.createCell(cellCount++);
                    cell.setCellValue(escapeForExcel(value));
                }
            }
        }
    }
    return workbook;
}