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

The following examples show how to use org.apache.poi.ss.usermodel.Sheet. 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 File: StyleTest.java    From easyexcel with 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 File: ExcelUtils.java    From seezoon-framework-all with Apache License 2.0 6 votes vote down vote up
/**
 * 导出
 * 
 * @param templatePath
 *            模板地址,自定义比较好看 classPath下的路径
 * @param colums
 *            导出的数据列 反射用
 * @param clazz
 * @param data
 * @param out
 * @param startRow
 *            从几行开始写数据
 * @return
 * @throws IOException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static <T> void doExport(String templatePath, String[] columns, Class<T> clazz, List<T> data,
		OutputStream out, int startRow) throws IOException, IllegalArgumentException, IllegalAccessException {
	ClassPathResource classPathResource = new ClassPathResource(templatePath);
	InputStream inputStream = classPathResource.getInputStream();
	Workbook workbook = new XSSFWorkbook(inputStream);
	Sheet sheet = workbook.getSheetAt(0);
	int size = data.size() + startRow;
	int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();
	for (int i = startRow; i < size; i++) {
		Row row = sheet.createRow(i);
		for (int j = 0; j < cellNum; j++) {
			Cell cell = row.createCell(j);
			Field field = ReflectionUtils.findField(clazz, columns[j]);
			if (null == field) {
				throw new ServiceException(columns[j] + " 配置错误");
			}
			ReflectionUtils.makeAccessible(field);
			Object obj = data.get(i - startRow);
			if (field.getType().getSimpleName().equalsIgnoreCase("double")) {
				cell.setCellValue((Double) field.get(obj));
			} else if (field.getType().getSimpleName().equalsIgnoreCase("date")) {
				CellStyle style = workbook.createCellStyle();
				style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
				cell.setCellStyle(style);
				cell.setCellValue((Date) field.get(obj));
			} else {
				cell.setCellValue(field.get(obj) == null ? "" : "" + field.get(obj));
			}
		}
	}
	workbook.write(out);
	workbook.close();
}
 
Example #3
Source File: ExcelExportServer.java    From jeasypoi with 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 #4
Source File: ExcelHandler.java    From development with 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 #5
Source File: ExcelWriterTransform_FormulaRecalculationTest.java    From hop with 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 #6
Source File: POIUtils.java    From xlsmapper with 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 #7
Source File: ExcelHandlerTest.java    From development with 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 File: DocumentSheet.java    From tools with 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 #9
Source File: Importer.java    From axelor-open-suite with 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 #10
Source File: XsSheetTest.java    From excel-io with 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 #11
Source File: ExcelToHtmlServer.java    From jeasypoi with 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 #12
Source File: DefaultXlsTableExporter.java    From olat with 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 #13
Source File: ExcelServiceImpl.java    From cs-actions with 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 #14
Source File: ExcelUtil.java    From danyuan-application with 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 #15
Source File: ExcelExportOfTemplateUtil.java    From easypoi with Apache License 2.0 5 votes vote down vote up
/**
 * 往Sheet 填充正常数据,根据表头信息 使用导入的部分逻辑,坐对象映射
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet,
                            Workbook workbook) throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // 获取表头数据
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 得到所有字段
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // 获取实体对象的导出数据
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // 根据表头进行筛选排序
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //下移数据,模拟插入
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
        sheet.getLastRowNum(), getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // 合并同类项
    mergeCells(sheet, excelParams, titleHeight);
}
 
Example #16
Source File: RecordsProcessorUtil.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * アノテーション{@link XlsColumn}の属性columnNameで指定した値が、ヘッダーセルに存在するかチェックする。
 * @param sheet
 * @param recordClass
 * @param headers
 * @param reader
 * @param config
 * @throws CellNotFoundException セルが見つからない場合
 */
public static void checkColumns(final Sheet sheet, final Class<?> recordClass,
        final List<RecordHeader> headers, final AnnotationReader reader, final Configuration config)
                throws CellNotFoundException {
    
    List<FieldAccessor> properties = FieldAccessorUtils.getPropertiesWithAnnotation(recordClass, reader, XlsColumn.class);
    
    for(FieldAccessor property : properties) {
        final XlsColumn column = property.getAnnotationNullable(XlsColumn.class);
        
        if(column.optional()){
            continue;
        }
        
        String columnName = column.columnName();
        boolean find = false;
        for(RecordHeader info: headers){
            if(Utils.matches(info.getLabel(), columnName, config)){
                find = true;
                break;
            }
        }
        if(!find){
            throw new CellNotFoundException(sheet.getSheetName(), columnName);
        }
    }
    
}
 
Example #17
Source File: RegionUtil.java    From lams with GNU General Public License v2.0 5 votes vote down vote up
/**
 * Sets the left border color for a region of cells by manipulating the cell style of the individual
 * cells on the left
 * 
 * @param color The color of the border
 * @param region The region that should have the border
 * @param sheet The sheet that the region is on.
 * @since POI 3.15 beta 2
 */
public static void setLeftBorderColor(int color, CellRangeAddress region, Sheet sheet) {
    int rowStart = region.getFirstRow();
    int rowEnd = region.getLastRow();
    int column = region.getFirstColumn();

    CellPropertySetter cps = new CellPropertySetter(CellUtil.LEFT_BORDER_COLOR, color);
    for (int i = rowStart; i <= rowEnd; i++) {
        cps.setProperty(CellUtil.getRow(i, sheet), column);
    }
}
 
Example #18
Source File: SsioIntegrationTest.java    From sep4j with Apache License 2.0 5 votes vote down vote up
@Test
public void saveMapsTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
	LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
	headerMap.put("primInt", "Primitive Int");
	headerMap.put("fake", "Not Real");

	Map<String, Object> record = new LinkedHashMap<>();
	record.put("primInt", 123);
	record.put("fake", "someValueAnyway");

	Collection<Map<String, Object>> records = Arrays.asList(record);
	File outputFile = createFile("saveMapsTest_IgnoringErrors_File");

	// save it
	Ssio.saveMaps(headerMap, records, outputFile);


	// then parse it
	byte[] spreadsheet = FileUtils.readFileToByteArray(outputFile);
	Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(spreadsheet));

	/*** do assertions ***/
	Sheet sheet = workbook.getSheetAt(0);
	Row headerRow = sheet.getRow(0);
	Row dataRow = sheet.getRow(1);

	Cell cell00 = headerRow.getCell(0);
	Cell cell01 = headerRow.getCell(1);
	Cell cell10 = dataRow.getCell(0);
	Cell cell11 = dataRow.getCell(1);


	// texts
	Assert.assertEquals("Primitive Int", cell00.getStringCellValue());
	Assert.assertEquals("Not Real", cell01.getStringCellValue());
	Assert.assertEquals("123", cell10.getStringCellValue());
	Assert.assertEquals("someValueAnyway", cell11.getStringCellValue());

}
 
Example #19
Source File: Ssio.java    From sep4j with Apache License 2.0 5 votes vote down vote up
private static <T> List<T> doParse(Map<String, String> reverseHeaderMap, InputStream inputStream,
								   Class<T> recordClassIfJavaBean, SepRecordType recordType, List<CellError> cellErrors)
		throws InvalidFormatException, InvalidHeaderRowException {

	validateReverseHeaderMap(reverseHeaderMap);

	Workbook workbook = toWorkbook(inputStream);
	if (workbook.getNumberOfSheets() <= 0) {
		return new ArrayList<T>();
	}

	Sheet sheet = workbook.getSheetAt(0);

	// key = columnIndex, value= {propName, headerText}
	Map<Short, ColumnMeta> columnMetaMap = parseHeader(reverseHeaderMap,
			sheet.getRow(0));
	if (columnMetaMap.isEmpty()) {
		throw new InvalidHeaderRowException();
	}

	// now do the data rows
	List<T> records = new ArrayList<T>();
	for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			continue;
		}

		T record = (T) parseDataRow(columnMetaMap, row, rowIndex, recordType, recordClassIfJavaBean,
				cellErrors);
		records.add(record);
	}
	return records;
}
 
Example #20
Source File: CellValueAndNotFormulaHelper.java    From tutorials with MIT License 5 votes vote down vote up
public Object getCellValueByEvaluatingFormula(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);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

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

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            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 #21
Source File: ExcelToHtmlServer.java    From easypoi with 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 File: VerticalRecordsProcessor.java    From xlsmapper with Apache License 2.0 5 votes vote down vote up
/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 縦方向に結合されている場合、結合情報のコピーする。(XlsArrayColumns用)
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getRowSize(mergedRegion);

    if(mergedSize >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                toCell.getColumnIndex(), mergedRegion.getFirstRow(), toCell.getColumnIndex(), mergedRegion.getLastRow());

        // 結合先のセルの書式も設定する
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, fromCell.getColumnIndex(), toCell.getRowIndex()+i);

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex(), toCell.getRowIndex()+i);
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }
    }

}
 
Example #23
Source File: ExcelRepositoryCollection.java    From molgenis with GNU Lesser General Public License v3.0 5 votes vote down vote up
public ExcelRepository getSheet(int i) {
  Sheet poiSheet = workbook.getSheetAt(i);
  if (poiSheet == null) {
    return null;
  }

  return new ExcelRepository(poiSheet, entityTypeFactory, attributeFactory, cellProcessors);
}
 
Example #24
Source File: ReadExcelFormula.java    From journaldev with MIT License 5 votes vote down vote up
public static void readExcelFormula(String fileName) throws IOException{
	
	FileInputStream fis = new FileInputStream(fileName);
	
	//assuming xlsx file
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.iterator();
	while (rowIterator.hasNext()) 
       {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
           
           while (cellIterator.hasNext()) 
           {
           	Cell cell = cellIterator.next();
           	switch(cell.getCellType()){
           	case Cell.CELL_TYPE_NUMERIC:
           		System.out.println(cell.getNumericCellValue());
           		break;
           	case Cell.CELL_TYPE_FORMULA:
           		System.out.println("Cell Formula="+cell.getCellFormula());
           		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
           		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
           			System.out.println("Formula Value="+cell.getNumericCellValue());
           		}
           	}
           }
       }
}
 
Example #25
Source File: CellGenerateUtil.java    From robe with GNU Lesser General Public License v3.0 5 votes vote down vote up
public static Cell create() {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet = wb.createSheet("New-Sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    return cell;
}
 
Example #26
Source File: DefaultExcelReader.java    From myexcel with 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 #27
Source File: RegionUtil.java    From lams with 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 #28
Source File: Borders1ReportTest.java    From birt with 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 #29
Source File: Xcelite.java    From xcelite with 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 #30
Source File: ExcelExportServer.java    From jeasypoi with 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;

}