Java Code Examples for org.apache.poi.ss.usermodel.Sheet

The following examples show how to use org.apache.poi.ss.usermodel.Sheet. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source Project: easyexcel   Author: alibaba   File: StyleTest.java    License: Apache License 2.0 7 votes vote down vote up
@Test
public void poi07() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xlsx");
    Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的
    Sheet sheet = workbook.getSheetAt(0);
    Row hssfRow = sheet.getRow(0);
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    DataFormatter formatter = new DataFormatter();
    System.out.println(hssfRow.getCell(0).getNumericCellValue());
    System.out.println(hssfRow.getCell(1).getNumericCellValue());
    System.out.println(hssfRow.getCell(2).getNumericCellValue());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormat());
    System.out.println(hssfRow.getCell(0).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(3).getCellStyle().getDataFormatString());
    isDate(hssfRow.getCell(0));
    isDate(hssfRow.getCell(1));
    isDate(hssfRow.getCell(2));
    isDate(hssfRow.getCell(3));

}
 
Example #2
Source Project: cs-actions   Author: CloudSlang   File: ExcelServiceImpl.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Constructs a list of indexes where the data will be added in the worksheet
 *
 * @param index           A list of indexes
 * @param worksheet       The worksheet where the data will be added
 * @param rowData         Data that will be added to the worksheet
 * @param rowDelimiter    rowData row delimiter
 * @param columnDelimiter rowData column delimiter
 * @param isRow           true - if the index list (param) contains row indexes
 * @return List of indexes where data will be added in the worksheet
 */
public static List<Integer> processIndex(final String index, final Sheet worksheet, final String rowData, final String rowDelimiter,
                                         final String columnDelimiter, final boolean isRow, final boolean hasHeader) {
    final String[] rows = rowData.split(rowDelimiter);
    String[] indexArray = null;
    if (!StringUtils.isBlank(index)) {
        indexArray = index.split(",");
    }
    int sheetLastRowIndex = worksheet.getLastRowNum();
    if (sheetLastRowIndex > 0) {
        sheetLastRowIndex++;
    }
    final int dataRows = rows.length;
    final int dataColumns = rows[0].split(columnDelimiter).length;
    int headerOffset = 0;
    if (hasHeader) {
        headerOffset = 1;
    }
    if (isRow) {
        return processIndexWithOffset(indexArray, headerOffset, sheetLastRowIndex, sheetLastRowIndex + dataRows);
    } else {
        return processIndexWithOffset(indexArray, 0, 0, dataColumns);
    }
}
 
Example #3
Source Project: olat   Author: huihoo   File: DefaultXlsTableExporter.java    License: Apache License 2.0 6 votes vote down vote up
private void createHeader(final Table table, final Translator translator, final int cdcnt, final Sheet exportSheet) {

        Row headerRow = exportSheet.createRow(0);
        for (int c = 0; c < cdcnt; c++) {
            ColumnDescriptor cd = table.getColumnDescriptor(c);
            if (cd instanceof StaticColumnDescriptor) {
                // ignore static column descriptors - of no value in excel download!
                continue;
            }
            String headerKey = cd.getHeaderKey();
            String headerVal = cd.translateHeaderKey() ? translator.translate(headerKey) : headerKey;
            Cell cell = headerRow.createCell(c);
            cell.setCellValue(headerVal);
            cell.setCellStyle(headerCellStyle);
        }
    }
 
Example #4
Source Project: jeasypoi   Author: zhangdaiscott   File: ExcelToHtmlServer.java    License: Apache License 2.0 6 votes vote down vote up
private void ensureColumnBounds(Sheet sheet) {
	if (gotBounds)
		return;

	Iterator<Row> iter = sheet.rowIterator();
	firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0);
	endColumn = 0;
	while (iter.hasNext()) {
		Row row = iter.next();
		short firstCell = row.getFirstCellNum();
		if (firstCell >= 0) {
			firstColumn = Math.min(firstColumn, firstCell);
			endColumn = Math.max(endColumn, row.getLastCellNum());
		}
	}
	gotBounds = true;
}
 
Example #5
Source Project: excel-io   Author: Vatavuk   File: XsSheetTest.java    License: MIT License 6 votes vote down vote up
/**
 * Add styled sheet to a workbook.
 * @throws IOException IOException
 */
@Test
public void addsSheetWithStyleToWorkbook() throws IOException {
    try (final Workbook workbook = new XSSFWorkbook()) {
        final Sheet sheet = new XsSheet(new XsRow(new TextCell("text")))
            .with(
                new XsStyle(
                    new ForegroundColor(
                        IndexedColors.GOLD.getIndex()
                    )
                )
            )
            .attachTo(workbook);
        MatcherAssert.assertThat(
            sheet.getRow(0).getCell(0)
                .getCellStyle().getFillForegroundColor(),
            Matchers.equalTo(IndexedColors.GOLD.getIndex())
        );
    }
}
 
Example #6
Source Project: development   Author: servicecatalog   File: ExcelHandler.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Read the excel to get the Map of properties for supported locals
 * 
 * @param wb
 *            workbook which is the source
 * @param supportedLocales
 *            supported Locale Iterator
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readExcel(Workbook wb,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {
    Sheet sheet = null;
    try {
        sheet = wb
                .getSheet(getDefaultResourceBundle().getString(sheetName));
        if (sheet == null) {
            throw new TranslationImportException();
        }
    } catch (Exception e) {
        throw new TranslationImportException(
                TranslationImportException.Reason.SHEET_NAME_NOT_FOUND);
    }
    return readSheet(sheet, supportedLocales, sheetName, defaultKeySet);
}
 
Example #7
Source Project: development   Author: servicecatalog   File: ExcelHandlerTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void readSheet_ManageLanguage() throws Exception {
    // given

    Sheet sheet = prepareSheet(null, true, KEY1, "key2");
    FacesContext fc = prepareContext();
    HashSet<Object> defaultKeySet = new HashSet<Object>();
    defaultKeySet.add(KEY1);
    defaultKeySet.add("key2");

    // when
    Map<String, Properties> map = ExcelHandler.readSheet(sheet, fc
            .getApplication().getSupportedLocales(), SHEET_NAME,
            defaultKeySet);

    // then
    Properties props = map.get(DE);
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "key1de ");
    props = map.get("de system");
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "\nkey1de");

}
 
Example #8
Source Project: jeasypoi   Author: zhangdaiscott   File: ExcelExportServer.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 创建 表头改变
 * 
 * @param entity
 * @param sheet
 * @param workbook
 * @param feildWidth
 */
public int createHeaderRow(ExportParams entity, Sheet sheet, Workbook workbook, int feildWidth) {
	Row row = sheet.createRow(0);
	row.setHeight(entity.getTitleHeight());
	createStringCell(row, 0, entity.getTitle(), getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	for (int i = 1; i <= feildWidth; i++) {
		createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
	}
	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildWidth));
	if (entity.getSecondTitle() != null) {
		row = sheet.createRow(1);
		row.setHeight(entity.getSecondTitleHeight());
		CellStyle style = workbook.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		createStringCell(row, 0, entity.getSecondTitle(), style, null);
		for (int i = 1; i <= feildWidth; i++) {
			createStringCell(row, i, "", getExcelExportStyler().getHeaderStyle(entity.getHeaderColor()), null);
		}
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, feildWidth));
		return 2;
	}
	return 1;
}
 
Example #9
Source Project: tools   Author: spdx   File: DocumentSheet.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * @param wb
 * @param sheetName
 */
public static void create(Workbook wb, String sheetName) {
	int sheetNum = wb.getSheetIndex(sheetName);
	if (sheetNum >= 0) {
		wb.removeSheetAt(sheetNum);
	}
	Sheet sheet = wb.createSheet(sheetName);
	CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
	CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
	Row row = sheet.createRow(0);
	for (int i = 0; i < HEADER_TITLES.length; i++) {
		sheet.setColumnWidth(i, COLUMN_WIDTHS[i]*256);
		sheet.setDefaultColumnStyle(i, defaultStyle);
		Cell cell = row.createCell(i);
		cell.setCellStyle(headerStyle);
		cell.setCellValue(HEADER_TITLES[i]);
	}
}
 
Example #10
Source Project: xlsmapper   Author: mygreen   File: POIUtils.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 指定した行の下に行を1行追加する
 * @param sheet
 * @param rowIndex 追加する行数
 * @return 追加した行を返す。
 */
public static Row insertRow(final Sheet sheet, final int rowIndex) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notMin(rowIndex, 0, "rowIndex");

    // 最終行を取得する
    int lastRow = sheet.getLastRowNum();
    if(lastRow < rowIndex) {
        // データが定義されている範囲害の場合は、行を新たに作成して返す。
        return sheet.createRow(rowIndex);
    }

    sheet.shiftRows(rowIndex, lastRow+1, 1);
    return sheet.createRow(rowIndex);
}
 
Example #11
Source Project: hop   Author: project-hop   File: ExcelWriterTransform_FormulaRecalculationTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void forcesToRecalculate_Hssf() throws Exception {
  data.wb = new HSSFWorkbook();
  data.wb.createSheet( "sheet1" );
  data.wb.createSheet( "sheet2" );

  setupTransform();
  transform.recalculateAllWorkbookFormulas();

  if ( !data.wb.getForceFormulaRecalculation() ) {
    int sheets = data.wb.getNumberOfSheets();
    for ( int i = 0; i < sheets; i++ ) {
      Sheet sheet = data.wb.getSheetAt( i );
      assertTrue( "Sheet #" + i + ": " + sheet.getSheetName(), sheet.getForceFormulaRecalculation() );
    }
  }
}
 
Example #12
Source Project: danyuan-application   Author: 514840279   File: ExcelUtil.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 获取合并单元格的值
 *
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public void setMergedRegion(Sheet sheet) {
	int sheetMergeCount = sheet.getNumMergedRegions();
	
	for (int i = 0; i < sheetMergeCount; i++) {
		// 获取合并单元格位置
		CellRangeAddress ca = sheet.getMergedRegion(i);
		int firstRow = ca.getFirstRow();
		if (startReadPos - 1 > firstRow) {// 如果第一个合并单元格格式在正式数据的上面,则跳过。
			continue;
		}
		int lastRow = ca.getLastRow();
		int mergeRows = lastRow - firstRow;// 合并的行数
		int firstColumn = ca.getFirstColumn();
		int lastColumn = ca.getLastColumn();
		// 根据合并的单元格位置和大小,调整所有的数据行格式,
		for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
			// 设定合并单元格
			sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
			j = j + mergeRows;// 跳过已合并的行
		}
		
	}
}
 
Example #13
Source Project: axelor-open-suite   Author: axelor   File: Importer.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
public void importExcel(File excelFile) throws IOException {
  List<Map> sheetList = excelToCSV.generateExcelSheets(excelFile);
  FileInputStream inputStream = new FileInputStream(excelFile);
  Workbook workBook = new XSSFWorkbook(inputStream);

  try {
    for (int i = 0; i < sheetList.size(); i++) {
      Sheet sheet = workBook.getSheet(sheetList.get(i).get("name").toString());
      File sheetFile =
          new File(
              excelFile.getParent() + "/" + sheetList.get(i).get("name").toString() + ".csv");
      excelToCSV.writeTOCSV(sheetFile, sheet, 0, 0);
    }

  } catch (Exception e) {
    e.printStackTrace();
  }
}
 
Example #14
Source Project: micro-integrator   Author: wso2   File: ExcelDataReader.java    License: Apache License 2.0 5 votes vote down vote up
public void populateData() throws SQLException {
    Workbook workbook = ((TExcelConnection) getConnection()).getWorkbook();
    int noOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < noOfSheets; i++) {
        Sheet sheet = workbook.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        ColumnInfo[] headers = this.extractColumnHeaders(sheet);
        DataTable dataTable = new FixedDataTable(sheetName, headers);

        Iterator<Row> rowItr = sheet.rowIterator();
        while (rowItr.hasNext()) {
            Row row = rowItr.next();
            if (row.getRowNum() != 0) {
                DataRow dataRow = new DataRow(row.getRowNum() - 1);
                Iterator<Cell> cellItr = row.cellIterator();
                int cellIndex = 0;
                while (cellItr.hasNext()) {
                    Cell cell = cellItr.next();
                    DataCell dataCell =
                            new DataCell(cellIndex + 1, cell.getCellType(), extractCellValue(cell));
                    dataRow.addCell(dataCell.getColumnId(), dataCell);
                    cellIndex++;
                }
                dataTable.addRow(dataRow);
            }
        }
        this.getData().put(dataTable.getTableName(), dataTable);
    }
}
 
Example #15
Source Project: birt   Author: eclipse   File: AutoRowHeightsTest.java    License: Eclipse Public License 1.0 5 votes vote down vote up
@Test
public void testRunReportXls() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("AutoRowHeight.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Auto RowHeight Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 7, this.firstNullRow(sheet));
		
		assertEquals( 275, sheet.getRow(0).getHeight() );
		assertEquals( 992, sheet.getRow(1).getHeight() );
		assertEquals( 826, sheet.getRow(2).getHeight() );
		assertEquals( 1405, sheet.getRow(3).getHeight() );
		assertEquals( 2988, sheet.getRow(4).getHeight() );
		assertEquals( 275, sheet.getRow(5).getHeight() );
		assertEquals( 4103, sheet.getRow(6).getHeight() );
		

	} finally {
		inputStream.close();
	}
}
 
Example #16
Source Project: MicroCommunity   Author: java110   File: AssetImportSMOImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 获取小区
 *
 * @param workbook
 * @param floors
 */
private void getFloors(Workbook workbook, List<ImportFloor> floors) {
    Sheet sheet = null;
    sheet = ImportExcelUtils.getSheet(workbook, "楼栋单元");
    List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
    ImportFloor importFloor = null;
    for (int osIndex = 0; osIndex < oList.size(); osIndex++) {
        Object[] os = oList.get(osIndex);
        if (osIndex == 0) { // 第一行是 头部信息 直接跳过
            continue;
        }

        if (StringUtil.isNullOrNone(os[0])) {
            continue;
        }

        Assert.hasLength(os[0].toString(), "楼栋单元选项中" + (osIndex + 1) + "行楼栋号为空");
        Assert.hasLength(os[1].toString(), "楼栋单元选项中" + (osIndex + 1) + "行单元编号为空");
        Assert.hasLength(os[2].toString(), "楼栋单元选项中" + (osIndex + 1) + "行总楼层为空");
        Assert.hasLength(os[3].toString(), "楼栋单元选项中" + (osIndex + 1) + "行是否有电梯为空");
        importFloor = new ImportFloor();
        importFloor.setFloorNum(os[0].toString());
        importFloor.setUnitNum(os[1].toString());
        importFloor.setLayerCount(os[2].toString());
        importFloor.setLift("有".equals(os[3].toString()) ? "1010" : "2020");
        floors.add(importFloor);
    }
}
 
Example #17
Source Project: data-prep   Author: Talend   File: XlsRunnable.java    License: Apache License 2.0 5 votes vote down vote up
private void serializeColumns(Workbook workbook, JsonGenerator generator, Sheet sheet, List<ColumnMetadata> columns)
        throws IOException {

    for (int i = 0, size = sheet.getLastRowNum(); i <= size; i++) {
        if (limit > 0 && i > limit) {
            break;
        }
        // is header line?
        Row row = sheet.getRow(i);
        if (isHeaderLine(i, columns) || row == null) {
            continue;
        }

        generator.writeStartObject();
        for (ColumnMetadata columnMetadata : columns) {

            // do not write the values if this has been detected as an header
            if (i < columnMetadata.getHeaderSize()) {
                continue;
            }

            int colId = Integer.parseInt(columnMetadata.getId());
            String cellValue =
                    getCellValueAsString(row.getCell(colId), workbook.getCreationHelper().createFormulaEvaluator());
            LOG.trace("cellValue for {}/{}: {}", i, colId, cellValue);
            generator.writeFieldName(columnMetadata.getId());
            if (cellValue != null) {
                generator.writeString(cellValue);
            } else {
                generator.writeNull();
            }
        }
        generator.writeEndObject();
    }
}
 
Example #18
Source Project: mumu   Author: babymm   File: ExcelParser.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 解析表格
 * @param workbook
 * @return
 */
protected List<List<Object>> parseSheet(Workbook workbook) {
	List<List<Object>> data = new ArrayList<List<Object>>();
	Iterator<Sheet> sheetIterator = workbook.iterator();
	
	int sheetCount=0;
	List<Object> headerRowData=null;
	// 遍历excel多个sheet
	while (sheetIterator.hasNext()) {
		Sheet sheet = sheetIterator.next();
		Iterator<Row> rowIterator = sheet.iterator();
		int rowNumber=0;
		// 遍历行
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			List<Object> rowData = parseRow(row);

			//获取第一页 第一行数据
			if(sheetCount==0&&rowNumber==0){
				headerRowData=rowData;
			}
			//比較剩餘sheet第一行数据是否相等
			if(sheetCount>1&&rowNumber==0){
				if(compareRow(headerRowData,rowData)){
					continue;
				}
			}
			data.add(rowData);
			
			rowNumber++;
		}
		
		sheetCount++;
	}
	return data;
}
 
Example #19
Source Project: excel-streaming-reader   Author: monitorjbl   File: StreamingWorkbook.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * {@inheritDoc}
 */
@Override
public Sheet getSheet(String name) {
  int index = getSheetIndex(name);
  if(index == -1) {
    throw new MissingSheetException("Sheet '" + name + "' does not exist");
  }
  return reader.getSheets().get(index);
}
 
Example #20
Source Project: jeewx   Author: zhangdaiscott   File: ExcelExportOfTemplateUtil.java    License: Apache License 2.0 5 votes vote down vote up
private static void parseTemplate(Sheet sheet, Map<String, Object> map)
		throws Exception {
	Iterator<Row> rows = sheet.rowIterator();
	Row row;
	while (rows.hasNext()) {
		row = rows.next();
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			setValueForCellByMap(row.getCell(i), map);
		}
	}
}
 
Example #21
Source Project: jeasypoi   Author: zhangdaiscott   File: ExcelToHtmlServer.java    License: Apache License 2.0 5 votes vote down vote up
private void printSheetContent(Sheet sheet) {
	// printColumnHeads(sheet);
	MergedRegionHelper mergedRegionHelper = new MergedRegionHelper(sheet);
	CellValueHelper cellValueHelper = new CellValueHelper(wb, cssRandom);
	out.format("<tbody>%n");
	Iterator<Row> rows = sheet.rowIterator();
	int rowIndex = 1;
	while (rows.hasNext()) {
		Row row = rows.next();
		out.format("  <tr style='height:%spx;'>%n", row.getHeight() / 15);
		// out.format("    <td class='%s'>%d</td>%n", ROW_HEAD_CLASS,
		// row.getRowNum() + 1);
		for (int i = firstColumn; i < endColumn; i++) {
			if (mergedRegionHelper.isNeedCreate(rowIndex, i)) {
				String content = "&nbsp;";
				CellStyle style = null;
				if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) {
					Cell cell = row.getCell(i);
					if (cell != null) {
						style = cell.getCellStyle();
						content = cellValueHelper.getHtmlValue(cell);
					}
				}
				if (mergedRegionHelper.isMergedRegion(rowIndex, i)) {
					Integer[] rowAndColSpan = mergedRegionHelper.getRowAndColSpan(rowIndex, i);
					out.format("    <td rowspan='%s' colspan='%s' class='%s' >%s</td>%n", rowAndColSpan[0], rowAndColSpan[1], styleName(style), content);
				} else {
					out.format("    <td class='%s'>%s</td>%n", styleName(style), content);
				}
			}

		}
		out.format("  </tr>%n");
		rowIndex++;
	}
	out.format("</tbody>%n");
}
 
Example #22
Source Project: lams   Author: lamsfoundation   File: SheetUtil.java    License: GNU General Public License v2.0 5 votes vote down vote up
/**
 * Compute width of a column based on a subset of the rows and return the result
 *
 * @param sheet the sheet to calculate
 * @param column    0-based index of the column
 * @param useMergedCells    whether to use merged cells
 * @param firstRow  0-based index of the first row to consider (inclusive)
 * @param lastRow   0-based index of the last row to consider (inclusive)
 * @return  the width in pixels or -1 if cell is empty
 */
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow){
    DataFormatter formatter = new DataFormatter();
    int defaultCharWidth = getDefaultCharWidth(sheet.getWorkbook());

    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
        Row row = sheet.getRow(rowIdx);
        if( row != null ) {
            double cellWidth = getColumnWidthForRow(row, column, defaultCharWidth, formatter, useMergedCells);
            width = Math.max(width, cellWidth);
        }
    }
    return width;
}
 
Example #23
Source Project: tutorials   Author: eugenp   File: CellValueAndNotFormulaHelper.java    License: MIT License 5 votes vote down vote up
public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (cell.getCachedFormulaResultType()) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 
Example #24
Source Project: molgenis   Author: molgenis   File: XlsxWriter.java    License: GNU Lesser General Public License v3.0 5 votes vote down vote up
private void writeRow(List<Object> values, Sheet sheet, int rowNr) {
  final Row row = sheet.createRow(rowNr);
  AtomicInteger counter = new AtomicInteger(0);
  values.forEach(
      record -> {
        int index = counter.getAndIncrement();
        if (record != null) {
          Cell cell = row.createCell(index);
          setCellValue(cell, record);
        }
      });
}
 
Example #25
Source Project: jeasypoi   Author: zhangdaiscott   File: ExcelExportServer.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 创建表头
 * 
 * @param title
 * @param index
 */
private int createTitleRow(ExportParams title, Sheet sheet, Workbook workbook, int index, List<ExcelExportEntity> excelParams) {
	Row row = sheet.createRow(index);
	int rows = getRowNums(excelParams);
	row.setHeight((short) 450);
	Row listRow = null;
	if (rows == 2) {
		listRow = sheet.createRow(index + 1);
		listRow.setHeight((short) 450);
	}
	int cellIndex = 0;
	CellStyle titleStyle = getExcelExportStyler().getTitleStyle(title.getColor());
	for (int i = 0, exportFieldTitleSize = excelParams.size(); i < exportFieldTitleSize; i++) {
		ExcelExportEntity entity = excelParams.get(i);
		if (StringUtils.isNotBlank(entity.getName())) {
			createStringCell(row, cellIndex, entity.getName(), titleStyle, entity);
		}
		if (entity.getList() != null) {
			List<ExcelExportEntity> sTitel = entity.getList();
			if (StringUtils.isNotBlank(entity.getName())) {
				sheet.addMergedRegion(new CellRangeAddress(index, index, cellIndex, cellIndex + sTitel.size() - 1));
			}
			for (int j = 0, size = sTitel.size(); j < size; j++) {
				createStringCell(rows == 2 ? listRow : row, cellIndex, sTitel.get(j).getName(), titleStyle, entity);
				cellIndex++;
			}
			cellIndex--;
		} else if (rows == 2) {
			createStringCell(listRow, cellIndex, "", titleStyle, entity);
			sheet.addMergedRegion(new CellRangeAddress(index, index + 1, cellIndex, cellIndex));
		}
		cellIndex++;
	}
	return rows;

}
 
Example #26
Source Project: xcelite   Author: eBay   File: Xcelite.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Gets the sheet with the specified index.
 * 
 * @param sheetIndex the sheet name
 * @return XceliteSheet object
 */
public XceliteSheet getSheet(String sheetName) {
  Sheet sheet = workbook.getSheet(sheetName);
  if (sheet == null) {
    throw new XceliteException(String.format("Could not find sheet named \"%s\"", sheetName));
  }
  return new XceliteSheetImpl(sheet, file);
}
 
Example #27
Source Project: birt   Author: eclipse   File: Borders1ReportTest.java    License: Eclipse Public License 1.0 5 votes vote down vote up
@Test
public void testRunReport() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("Borders1.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Borders Test Report 1", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 12, firstNullRow(sheet));
		
		int i = 0;
		assertBorder( sheet, i++, 0, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN, CellStyle.BORDER_THIN );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM, CellStyle.BORDER_MEDIUM );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK, CellStyle.BORDER_THICK );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED, CellStyle.BORDER_DOTTED );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED, CellStyle.BORDER_DASHED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED, CellStyle.BORDER_MEDIUM_DASHED );
		
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		assertBorder( sheet, i++, 0, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE, CellStyle.BORDER_DOUBLE );
		
	} finally {
		inputStream.close();
	}
}
 
Example #28
Source Project: lams   Author: lamsfoundation   File: RegionUtil.java    License: GNU General Public License v2.0 5 votes vote down vote up
/**
 * Sets the bottom border style for a region of cells by manipulating the cell style of the individual
 * cells on the bottom
 * 
 * @param border The new border
 * @param region The region that should have the border
 * @param sheet The sheet that the region is on.
 * @since POI 3.16 beta 1
 */
public static void setBorderBottom(BorderStyle border, CellRangeAddress region, Sheet sheet) {
    int colStart = region.getFirstColumn();
    int colEnd = region.getLastColumn();
    int rowIndex = region.getLastRow();
    CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_BOTTOM, border);
    Row row = CellUtil.getRow(rowIndex, sheet);
    for (int i = colStart; i <= colEnd; i++) {
        cps.setProperty(row, i);
    }
}
 
Example #29
Source Project: myexcel   Author: liaochong   File: DefaultExcelReader.java    License: Apache License 2.0 5 votes vote down vote up
private Sheet getSheetOfInputStream(@NonNull InputStream fileInputStream, String password) throws IOException {
    if (StringUtil.isBlank(password)) {
        wb = WorkbookFactory.create(fileInputStream);
    } else {
        wb = WorkbookFactory.create(fileInputStream, password);
    }
    return getSheet();
}
 
Example #30
Source Project: onetwo   Author: wayshall   File: AbstractRowMapper.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public List<String> mapTitleRow(Sheet sheet) {
	try {
		Row titleRow = sheet.getRow(getTitleRowIndex());
		return ExcelUtils.getRowValues(titleRow);
	} catch (Exception e) {
		throw ExcelUtils.wrapAsUnCheckedException("mapTitleRow error" , e);
	}
}