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

Example 16 with XSSFCell

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

the class ExportQAResult method beginExport.

public void beginExport(List<QAResultBean> dataList, List<String> fileLCList, IProgressMonitor monitor) {
    if (monitor == null) {
        monitor = new NullProgressMonitor();
    }
    // 分成十份，其中解析文件 1 份，其余 9 份
    monitor.beginTask("", 10);
    monitor.setTaskName(Messages.getString("qa.export.ExportQAResult.monitor.title"));
    // 将　fileLCList 转换成相对路径
    List<File> fileList = new ArrayList<File>();
    for (String fileLC : fileLCList) {
        fileList.add(new File(fileLC));
    }
    for (IFile iFile : ResourceUtils.filesToIFiles(fileList)) {
        filePathList.add(iFile.getFullPath().toOSString());
    }
    if (monitor.isCanceled()) {
        throw new OperationCanceledException();
    }
    monitor.worked(1);
    // UNDO 这里按文件排序给注释了。。。。
    //		// 先按文件排序
    //		sort(dataList);
    // 工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 创建sheet页
    XSSFSheet sheet = workbook.createSheet();
    // 设置sheet名称
    workbook.setSheetName(0, Messages.getString("qa.export.ExportQAResult.sheet.title"));
    sheet.setColumnWidth(0, 255 * 6);
    sheet.setColumnWidth(1, 255 * 20);
    sheet.setColumnWidth(2, 255 * 30);
    sheet.setColumnWidth(3, 255 * 60);
    sheet.setColumnWidth(4, 255 * 60);
    XSSFFont titleFont = workbook.createFont();
    titleFont.setColor(IndexedColors.GREY_80_PERCENT.getIndex());
    titleFont.setBold(true);
    titleFont.setFontHeight(20);
    XSSFFont headerFont = workbook.createFont();
    headerFont.setBold(true);
    headerFont.setFontHeight(14);
    XSSFFont errorFont = workbook.createFont();
    errorFont.setColor(IndexedColors.RED.getIndex());
    XSSFCellStyle titleStyle = workbook.createCellStyle();
    titleStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    titleStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    titleStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    titleStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    titleStyle.setAlignment(HorizontalAlignment.CENTER);
    titleStyle.setFont(titleFont);
    XSSFCellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(headerFont);
    XSSFCellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    cellStyle.setWrapText(true);
    XSSFCellStyle errorCellStyle = workbook.createCellStyle();
    errorCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    errorCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    errorCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    errorCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    errorCellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    errorCellStyle.setWrapText(true);
    errorCellStyle.setFont(errorFont);
    // 生成标题行
    XSSFRow row = sheet.createRow(0);
    XSSFCell cell = row.createCell(0);
    cell.setCellStyle(titleStyle);
    cell.setCellValue(Messages.getString("qa.export.ExportQAResult.titleCell"));
    String[] headers = new String[] { // 级别
    Messages.getString("qa.export.ExportQAResult.header.errorLeavel"), // 类型
    Messages.getString("qa.export.ExportQAResult.header.qaType"), // 位置
    Messages.getString("qa.export.ExportQAResult.header.location"), // 源文
    Messages.getString("qa.export.ExportQAResult.header.srcText"), // 　译文
    Messages.getString("qa.export.ExportQAResult.header.tgtText") };
    // 产生表格标题行
    row = sheet.createRow(1);
    for (short i = 0; i < headers.length; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(headers[i]);
    }
    // 开始生成数据
    int index = 1;
    String rowId = null;
    // 先处理品质检查结果数据为空的情况
    if (dataList.size() <= 0) {
        if (isMultiFile) {
            String multiFileStr = getMultiResouce();
            index++;
            row = sheet.createRow(index);
            for (int i = 0; i < headers.length; i++) {
                cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                if (i == headers.length - 1) {
                    cell.setCellValue(multiFileStr);
                }
            }
        } else {
            for (String filePath : this.filePathList) {
                index++;
                row = sheet.createRow(index);
                for (int i = 0; i < headers.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellStyle(cellStyle);
                    if (i == headers.length - 1) {
                        cell.setCellValue(filePath);
                    }
                }
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    } else {
        int interval = 1;
        if (dataList.size() > 9) {
            interval = dataList.size() / 9;
        }
        int startMergeRow = -1;
        int endMergeRow = -1;
        for (int i = 0; i < dataList.size(); i++) {
            QAResultBean bean = dataList.get(i);
            index++;
            System.out.println(index);
            if (index % interval == 0) {
                if (monitor.isCanceled()) {
                    throw new OperationCanceledException();
                }
                monitor.worked(1);
            }
            row = sheet.createRow(index);
            // 处理合并　级别　与　类型　两列的行
            mergeIF: if (bean.getMergeId() != null) {
                if (mergedIdSet.contains(bean.getMergeId())) {
                    break mergeIF;
                }
                startMergeRow = index;
                mergeFor: for (int j = i + 1; j < dataList.size(); j++) {
                    if (dataList.get(j).getMergeId() != null && dataList.get(j).getMergeId().equals(bean.getMergeId())) {
                        mergedIdSet.add(bean.getMergeId());
                        endMergeRow = index + (j - i);
                    } else {
                        break mergeFor;
                    }
                }
                if (startMergeRow >= 0 && endMergeRow >= 0) {
                    sheet.addMergedRegion(new CellRangeAddress(startMergeRow, endMergeRow, 0, 0));
                    sheet.addMergedRegion(new CellRangeAddress(startMergeRow, endMergeRow, 1, 1));
                    startMergeRow = -1;
                    endMergeRow = -1;
                }
            }
            // 循环当前行的每一列
            for (int h = 0; h < headers.length; h++) {
                cell = row.createCell(h);
                cell.setCellStyle(cellStyle);
                String text = null;
                switch(h) {
                    case 0:
                        if (bean.getLevel() == 0) {
                            text = Messages.getString("qa.export.ExportQAResult.errorLeavel.error");
                            cell.setCellStyle(errorCellStyle);
                        } else if (bean.getLevel() == 1) {
                            text = Messages.getString("qa.export.ExportQAResult.errorLeavel.warning");
                        }
                        cell.setCellValue(text);
                        break;
                    case 1:
                        text = bean.getQaTypeText();
                        cell.setCellValue(text);
                        break;
                    case 2:
                        text = bean.getFileName() + " [" + bean.getLineNumber() + "]";
                        cell.setCellValue(text);
                        break;
                    case 3:
                        text = bean.getSrcContent();
                        cell.setCellValue(getDisplayText(text));
                        break;
                    case 4:
                        text = bean.getTgtContent();
                        cell.setCellValue(getDisplayText(text));
                        break;
                    default:
                        break;
                }
            }
        }
        //	            // 这是合并　文件路径
        //	            if (isMultiFile) {
        //	            	sheet.addMergedRegion(new CellRangeAddress(resourceIndex, index, 6, 6));
        //	    		}else {
        //	    			sheet.addMergedRegion(new CellRangeAddress(resourceIndex, index, 6, 6));
        //	    		}
        //	            sheet.addMergedRegion(new CellRangeAddress(rowidIndex, index, 1, 1));
        //	    		sheet.addMergedRegion(new CellRangeAddress(rowidIndex, index, 4, 4));
        //	    		sheet.addMergedRegion(new CellRangeAddress(rowidIndex, index, 5, 5));
        // 标题行合并(处理未合并完的部份)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    }
    try {
        FileOutputStream fileoutputstream = new FileOutputStream(exportFilePath);
        workbook.write(fileoutputstream);
        fileoutputstream.close();
        Display.getDefault().syncExec(new Runnable() {

            public void run() {
                MessageDialog.openInformation(Display.getDefault().getActiveShell(), Messages.getString("qa.all.dialog.info"), Messages.getString("qa.export.ExportQAResult.MSG.exportSuccess"));
            }
        });
    } catch (Exception e) {
        Display.getDefault().syncExec(new Runnable() {

            public void run() {
                MessageDialog.openInformation(Display.getDefault().getActiveShell(), Messages.getString("qa.all.dialog.info"), Messages.getString("qa.export.ExportQAResult.MSG.exportFail"));
            }
        });
        LOGGER.error(Messages.getString("qa.export.ExportQAResult.LOG.exportError"), e);
    }
}

Example 17 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 18 with XSSFCell

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

the class ExcelComparator method isCellBorderMatches.

/**
     * Checks if cell border bottom matches.
     */
private void isCellBorderMatches(Locator loc1, Locator loc2, char borderSide) {
    if (!(loc1.cell instanceof XSSFCell))
        return;
    XSSFCellStyle style1 = ((XSSFCell) loc1.cell).getCellStyle();
    XSSFCellStyle style2 = ((XSSFCell) loc2.cell).getCellStyle();
    boolean b1, b2;
    String borderName;
    switch(borderSide) {
        case 't':
        default:
            b1 = style1.getBorderTopEnum() == BorderStyle.THIN;
            b2 = style2.getBorderTopEnum() == BorderStyle.THIN;
            borderName = "TOP";
            break;
        case 'b':
            b1 = style1.getBorderBottomEnum() == BorderStyle.THIN;
            b2 = style2.getBorderBottomEnum() == BorderStyle.THIN;
            borderName = "BOTTOM";
            break;
        case 'l':
            b1 = style1.getBorderLeftEnum() == BorderStyle.THIN;
            b2 = style2.getBorderLeftEnum() == BorderStyle.THIN;
            borderName = "LEFT";
            break;
        case 'r':
            b1 = style1.getBorderRightEnum() == BorderStyle.THIN;
            b2 = style2.getBorderRightEnum() == BorderStyle.THIN;
            borderName = "RIGHT";
            break;
    }
    if (b1 != b2) {
        addMessage(loc1, loc2, "Cell Border Attributes does not Match ::", (b1 ? "" : "NOT ") + borderName + " BORDER", (b2 ? "" : "NOT ") + borderName + " BORDER");
    }
}

Example 19 with XSSFCell

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

the class TestSXSSFWorkbookWithCustomZipEntrySource method customZipEntrySource.

// write an unencrypted workbook to disk, but any temporary files are encrypted
@Test
public void customZipEntrySource() throws IOException {
    SXSSFWorkbookWithCustomZipEntrySource workbook = new SXSSFWorkbookWithCustomZipEntrySource();
    SXSSFSheet sheet1 = workbook.createSheet(sheetName);
    SXSSFRow row1 = sheet1.createRow(1);
    SXSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue(cellValue);
    ByteArrayOutputStream os = new ByteArrayOutputStream(8192);
    workbook.write(os);
    workbook.close();
    workbook.dispose();
    XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(os.toByteArray()));
    XSSFSheet xs1 = xwb.getSheetAt(0);
    assertEquals(sheetName, xs1.getSheetName());
    XSSFRow xr1 = xs1.getRow(1);
    XSSFCell xc1 = xr1.getCell(1);
    assertEquals(cellValue, xc1.getStringCellValue());
    xwb.close();
}

Example 20 with XSSFCell

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

the class XSSFImportFromXML method importFromXML.

/**
     * Imports an XML into the XLSX using the Custom XML mapping defined
     *
     * @param xmlInputString the XML to import
     * @throws SAXException if error occurs during XML parsing
     * @throws XPathExpressionException if error occurs during XML navigation
     * @throws ParserConfigurationException if there are problems with XML parser configuration
     * @throws IOException  if there are problems reading the input string
     */
public void importFromXML(String xmlInputString) throws SAXException, XPathExpressionException, IOException {
    DocumentBuilder builder = DocumentHelper.newDocumentBuilder();
    Document doc = builder.parse(new InputSource(new StringReader(xmlInputString.trim())));
    List<XSSFSingleXmlCell> singleXmlCells = _map.getRelatedSingleXMLCell();
    List<XSSFTable> tables = _map.getRelatedTables();
    XPathFactory xpathFactory = XPathFactory.newInstance();
    XPath xpath = xpathFactory.newXPath();
    // Setting namespace context to XPath
    // Assuming that the namespace prefix in the mapping xpath is the
    // same as the one used in the document
    xpath.setNamespaceContext(new DefaultNamespaceContext(doc));
    for (XSSFSingleXmlCell singleXmlCell : singleXmlCells) {
        STXmlDataType.Enum xmlDataType = singleXmlCell.getXmlDataType();
        String xpathString = singleXmlCell.getXpath();
        Node result = (Node) xpath.evaluate(xpathString, doc, XPathConstants.NODE);
        // result can be null if value is optional (xsd:minOccurs=0), see bugzilla 55864
        if (result != null) {
            String textContent = result.getTextContent();
            logger.log(POILogger.DEBUG, "Extracting with xpath " + xpathString + " : value is '" + textContent + "'");
            XSSFCell cell = singleXmlCell.getReferencedCell();
            logger.log(POILogger.DEBUG, "Setting '" + textContent + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet " + cell.getSheet().getSheetName());
            setCellValue(textContent, cell, xmlDataType);
        }
    }
    for (XSSFTable table : tables) {
        String commonXPath = table.getCommonXpath();
        NodeList result = (NodeList) xpath.evaluate(commonXPath, doc, XPathConstants.NODESET);
        // the first row contains the table header
        int rowOffset = table.getStartCellReference().getRow() + 1;
        int columnOffset = table.getStartCellReference().getCol() - 1;
        for (int i = 0; i < result.getLength(); i++) {
            // TODO: implement support for denormalized XMLs (see
            // OpenOffice part 4: chapter 3.5.1.7)
            Node singleNode = result.item(i).cloneNode(true);
            for (XSSFXmlColumnPr xmlColumnPr : table.getXmlColumnPrs()) {
                int localColumnId = (int) xmlColumnPr.getId();
                int rowId = rowOffset + i;
                int columnId = columnOffset + localColumnId;
                String localXPath = xmlColumnPr.getLocalXPath();
                localXPath = localXPath.substring(localXPath.substring(1).indexOf('/') + 2);
                // TODO: convert the data to the cell format
                String value = (String) xpath.evaluate(localXPath, singleNode, XPathConstants.STRING);
                logger.log(POILogger.DEBUG, "Extracting with xpath " + localXPath + " : value is '" + value + "'");
                XSSFRow row = table.getXSSFSheet().getRow(rowId);
                if (row == null) {
                    row = table.getXSSFSheet().createRow(rowId);
                }
                XSSFCell cell = row.getCell(columnId);
                if (cell == null) {
                    cell = row.createCell(columnId);
                }
                logger.log(POILogger.DEBUG, "Setting '" + value + "' to cell " + cell.getColumnIndex() + "-" + cell.getRowIndex() + " in sheet " + table.getXSSFSheet().getSheetName());
                setCellValue(value, cell, xmlColumnPr.getXmlDataType());
            }
        }
    }
}