Java Code Examples for org.apache.poi.ss.usermodel.Sheet#rowIterator()

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#rowIterator() . 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: ExcelToHtmlServer.java    From autopoi 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 2
Source File: StreamingWorkbookTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
@Test
public void testFormulaCells() throws Exception {
  try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
    assertEquals(1, workbook.getNumberOfSheets());
    Sheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.rowIterator();
    Cell A1 = getCellFromNextRow(rowIterator, 0);
    Cell A2 = getCellFromNextRow(rowIterator, 0);
    Cell A3 = getCellFromNextRow(rowIterator, 0);

    expectType(A3, FORMULA);
    expectCachedType(A3, NUMERIC);
    expectFormula(A3, "SUM(A1:A2)");

    expectStringContent(A1, "1");
    expectStringContent(A2, "2");
    expectStringContent(A3, "3");
  }
}
 
Example 3
Source File: ExcelToHtmlServer.java    From easypoi 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 4
Source File: StreamingWorkbookTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
@Test
  public void testQuotedStringFormattedFormulaCell() throws Exception {
    try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
      Sheet sheet = workbook.getSheetAt(0);
      Iterator<Row> rowIterator = sheet.rowIterator();

      nextRow(rowIterator);
      Cell B2 = getCellFromNextRow(rowIterator, 1);
      nextRow(rowIterator);
      Cell B4 = getCellFromNextRow(rowIterator, 1);

      expectType(B4, FORMULA);
//      expectCachedType(B4, STRING); // this can't return FUNCTION as cached type as per javadoc ! fix in future work
//      expectFormula(B4, "B2"); // returning wrong forumla type? this needs to be fixed in future work
      expectSameStringContent(B2, B4);
      expectStringContent(B4, "\"a\"");
    }
  }
 
Example 5
Source File: ExcelUtil.java    From javautils with Apache License 2.0 6 votes vote down vote up
/**
 * 获取指定页sheet的数据
 * @param sheet
 * @return
 */
private static List<List<String>> getSheet(Sheet sheet){
    List<List<String>> list = new ArrayList<>();
    // 获得表单的迭代器
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        // 获得行数据
        Row row = rows.next();
        // 获得行的迭代器
        Iterator<Cell> cells = row.cellIterator();
        List<String> rowList = new ArrayList<>();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if(cell != null) {
                String value = getCellValue(cell);
                rowList.add(value);
            }
        }

        list.add(rowList);

    }

    return list;
}
 
Example 6
Source File: StreamingWorkbookTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
@Test
public void testNumericFormattedFormulaCell() throws Exception {
  try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
    Sheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.rowIterator();

    Cell C1 = getCellFromNextRow(rowIterator, 2);
    Cell C2 = getCellFromNextRow(rowIterator, 2);

    expectType(C2, FORMULA);
    expectCachedType(C2, NUMERIC);
    expectFormula(C2, "C1");
    expectSameStringContent(C2, C1);
    expectStringContent(C2, "May 11 2018");
  }
}
 
Example 7
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 8
Source File: StreamingWorkbookTest.java    From excel-streaming-reader with Apache License 2.0 6 votes vote down vote up
@Test
  public void testStringFormattedFormulaCell() throws Exception {
    try(Workbook workbook = openWorkbook("formula_cell.xlsx")) {
      Sheet sheet = workbook.getSheetAt(0);
      Iterator<Row> rowIterator = sheet.rowIterator();

      Cell B1 = getCellFromNextRow(rowIterator, 1);
      nextRow(rowIterator);
      Cell B3 = getCellFromNextRow(rowIterator, 1);

      expectType(B3, FORMULA);
//      expectCachedType(B3, STRING); // this can't return FUNCTION as cached type as per javadoc ! fix in future work
      expectFormula(B3, "B1");
      expectSameStringContent(B1, B3);
      expectStringContent(B3, "a");
    }
  }
 
Example 9
Source File: FileUtils.java    From atlas with Apache License 2.0 5 votes vote down vote up
public static List<String[]> readExcel(InputStream inputStream, String extension) throws IOException {
    List<String[]> ret        = new ArrayList<>();
    Workbook       excelBook  = extension.equalsIgnoreCase(XLS.name()) ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
    Sheet          excelSheet = excelBook.getSheetAt(0);
    Iterator       itr        = excelSheet.rowIterator();
    Row            headerRow  = (Row) itr.next();

    if (isRowEmpty(headerRow)) {
        return ret;
    }

    while (itr.hasNext()) {
        Row row = (Row) itr.next();

        if (!isRowEmpty(row)) {
            String[] data = new String[row.getLastCellNum()];

            for (int i = 0; i < row.getLastCellNum(); i++) {
                data[i] = (row.getCell(i) != null) ? row.getCell(i).getStringCellValue().trim() : null;
            }

            ret.add(data);
        }
    }

    return ret;
}
 
Example 10
Source File: ExcelConnector.java    From TAcharting with GNU Lesser General Public License v2.1 5 votes vote down vote up
@Override
  public boolean connect(File resource) {
      
try 
(	FileInputStream inputStream = new FileInputStream(resource);
	Workbook wb = new XSSFWorkbook(inputStream);
){
       Sheet sheet = wb.getSheetAt(0);
       sheet.rowIterator();
       // first row with name and time ofFormat
       Row infoRow = rowIterator.next();
       name = infoRow.getCell(0).getStringCellValue();
       String timeFormat = infoRow.getCell(1).getStringCellValue();
       id =FormatUtils.extractInteger(timeFormat);
       isDateTwoColumn = id== TimeFormatType.yyyy_MM_ddHmsz.id;
       dateTimeFormatter = FormatUtils.getDateTimeFormatter(id);
       String currencyString = infoRow.getCell(2).getStringCellValue().replaceAll("\\s","").toUpperCase();
       if(currencyString==null){
           currencyString = Parameter.DEFAULT_CURRENCY;
       }
        currency = Currency.getInstance(currencyString);
	} catch (IOException e) {
		log.error("Error connecting with .xls file: " + e.getMessage());
		e.printStackTrace();
		return false;
	}
	return true;
  }
 
Example 11
Source File: ExcelToHtmlServer.java    From autopoi 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 12
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 13
Source File: ExcelReaderService.java    From abixen-platform with GNU Lesser General Public License v2.1 5 votes vote down vote up
private DataFileDto parseWorkbook(final Sheet sheet, final FileParserMessage<DataFileColumn> msg, final Boolean readFirstColumnAsColumnName) {
    final DataFileDto dataFileDto = new DataFileDto();
    dataFileDto.setRowTypes(buildRowTypes(sheet.getRow(readFirstColumnAsColumnName ? 1 : 0)));
    final Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        dataFileDto.addRow(readRowAsRowInMemory(rowIterator.next(), dataFileDto.getRowTypes()));
    }
    return dataFileDto;
}
 
Example 14
Source File: ExcelExportOfTemplateUtil.java    From jeewx with 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 15
Source File: SpreadsheetDeleteColumn.java    From openbd-core with GNU General Public License v3.0 5 votes vote down vote up
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
	cfSpreadSheetData	spreadsheet = null;
	String cols;
	
	/*
	 * Collect up the parameters
	 */
spreadsheet	= (cfSpreadSheetData)parameters.get(1);
cols				= parameters.get(0).getString();

Sheet	sheet = spreadsheet.getActiveSheet();

Set<Integer>	numbers	= tagUtils.getNumberSet( cols );
Iterator<Row> rowIT	= sheet.rowIterator();
while ( rowIT.hasNext() ){
	Row row	= rowIT.next();
	
	Iterator<Integer>	columnIndx 	= numbers.iterator();
	while ( columnIndx.hasNext() ){
		Cell cell = row.getCell( columnIndx.next() - 1 );
		if ( cell != null ){
			row.removeCell( cell );
		}
	}
}

	return cfBooleanData.TRUE;
}
 
Example 16
Source File: ExcelDataReader.java    From micro-integrator with 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 17
Source File: LondonLonelinessImporter.java    From TomboloDigitalConnector with MIT License 4 votes vote down vote up
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    String fileLocation = LondonLonelinessImporter.DatasourceId.lonelinessPrevalence.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xlsx");

    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    XSSFWorkbook workbook = new XSSFWorkbook(isr);

    if (geographyScope == null || geographyScope.isEmpty()) {
        geographyScope = new ArrayList<>();
        geographyScope.add("localAuthority");
        log.warn("No geography scope provided. Defaulting to Local Authority");
    }

    for (String geographyLabel : geographyScope) {
        Sheet datatypeSheet = null;
        SubjectType subjectType = null;
        List<Integer> validAttributes = new ArrayList<>();

        switch (geographyLabel) {
            case "localAuthority":
                datatypeSheet = workbook.getSheetAt(1);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
                validAttributes.addAll(Arrays.asList(0,1,2,3));
                break;
            case "msoa":
                datatypeSheet = workbook.getSheetAt(2);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));
                validAttributes.addAll(Arrays.asList(0,1,2,3,4));
                break;
            case "lsoa":
                datatypeSheet = workbook.getSheetAt(3);
                subjectType = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(2));
                validAttributes.addAll(Arrays.asList(0,2,3,4));
                break;
        }
        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        LocalDateTime timestamp = TimedValueUtils.parseTimestampString("Jun-15");
        log.info("The analysis was made in {} and we persist it as {}", "June 2015", timestamp);
        // Skipping unrelevant rows
        rowIterator.next();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geograghy =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(subjectType, geograghy);
            if (subject != null) {
                try {
                    ListIterator<Integer> it = validAttributes.listIterator();
                    while (it.hasNext()) {
                        Double record = row.getCell(it.nextIndex()+2).getNumericCellValue();
                        Attribute attribute = datasource.getTimedValueAttributes().get(it.next());
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    }
                } catch (IllegalStateException e) {
                    log.warn("Value for subject " + subject.getLabel() + " not found. " +
                            "Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
                    continue;
                }
            }
        }
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 
Example 18
Source File: ONSWellbeingImporter.java    From TomboloDigitalConnector with MIT License 4 votes vote down vote up
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {

    String fileLocation = DatasourceId.ONSWellbeing.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    List<TimedValue> timedValues = new ArrayList<TimedValue>();

    HSSFWorkbook workbook = new HSSFWorkbook(isr);
    int attributeIndex = 0;

    // Looping through the excell sheets
    for (int sheet = 1; sheet <= 8; sheet = sheet+2){
        Sheet datatypeSheet = workbook.getSheetAt(sheet);
        Row rowTime = datatypeSheet.getRow(5);

        // Creating the row iterator object
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        // Skipping unrelevant rows
        int ignore = 0;
        while (ignore++ < 7) {
            rowIterator.next();
        }

        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geograghy =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geograghy);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geograghy);

            // Checking if subject is null
            if (subject != null) {

                // Looping through the time values
                for (int timeValuesIndex=2; timeValuesIndex <= 7; timeValuesIndex++ ) {
                    // This is the row number that contains our time values (years) in the dataset
                    String year = rowTime.getCell(timeValuesIndex).toString();
                    year = year.substring(0, year.length() - 3);
                    LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
                    log.info("Time is presented in the dataset as {} and we persist it as {}", year, timestamp);

                    try {
                        Double record = row.getCell(timeValuesIndex).getNumericCellValue();
                        // Here is where we are assigning the values of our .xls file to the attribute fields we
                        // created.
                        Attribute attribute = datasource.getTimedValueAttributes().get(attributeIndex);
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    } catch (IllegalStateException e) {
                        log.warn("Value for subject " + subject.getLabel() + " not found. " +
                                "Defaulting to 0.0. Consider using a BackoffField or ConstantField.");
                        continue;
                    }
                }
            }
        }
        attributeIndex++;
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 
Example 19
Source File: ONSBusinessDemographyImporter.java    From TomboloDigitalConnector with MIT License 4 votes vote down vote up
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    String fileLocation = DatasourceId.ONSNewBusinessSurvival.datasourceSpec.getUrl();
    InputStream isr = downloadUtils.fetchInputStream(new URL(fileLocation), getProvider().getLabel(), ".xls");

    // This dataset contains both subject types
    SubjectType localauthority = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(0));
    SubjectType englandboundaries = SubjectTypeUtils.getSubjectTypeByProviderAndLabel(AbstractONSImporter.PROVIDER.getLabel(), getOaDatasourceIds().get(1));

    List<TimedValue> timedValues = new ArrayList<TimedValue>();
    HSSFWorkbook workbook = new HSSFWorkbook(isr);

    // Looping through the excell sheets
    for (int sheet = 13; sheet <= 17; sheet++){
        Sheet datatypeSheet = workbook.getSheetAt(sheet);
        Iterator<Row> rowIterator = datatypeSheet.rowIterator();
        int ignore = 0;
        while (ignore++ < 6) {
            rowIterator.next();
        }
        // Hardcoded year of survey
        String year = null;
        switch (sheet){
            case 13:
                year = "2011";
                break;
            case 14:
                year = "2012";
                break;
            case 15:
                year = "2013";
                break;
            case 16:
                year = "2014";
                break;
            case 17:
                year = "2015";
                break;
        }
        Row rowAttribute = datatypeSheet.getRow(6);
        // Looping through rows
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            String geography =  String.valueOf(row.getCell(0)).trim();
            Subject subject = SubjectUtils.getSubjectByTypeAndLabel(localauthority, geography);
            subject = subject != null ? subject : SubjectUtils.getSubjectByTypeAndLabel(englandboundaries, geography);
            // Checking if subject is null
            if (subject != null) {
                // loop through attribute columns
                for (int i=3; i<=12;i++){
                    String attributeName = String.valueOf(rowAttribute.getCell(i)).trim();
                    LocalDateTime timestamp = TimedValueUtils.parseTimestampString(year);
                    try {
                        Double record;
                        if (attributeName.contains("per cent")) {
                            record = row.getCell(i).getNumericCellValue() / 100;
                            log.info("Value for " + subject.getLabel()+". Appears as: "+ row.getCell(i).getNumericCellValue()+
                            " Saving as: "+record);
                        } else {
                            record = row.getCell(i).getNumericCellValue();
                        }
                        row.getCell(i).getNumericCellValue();
                        Attribute attribute = AttributeId.getAttributeIdByEqual(attributeName).attribute;
                        timedValues.add(new TimedValue(
                                subject,
                                attribute,
                                timestamp,
                                record));
                    } catch (IllegalStateException | NullPointerException e) {
                        log.warn("Invalid value for subject " + subject.getLabel()+". Skipping");
                        continue;
                    }
                }
            }
        }
    }
    saveAndClearTimedValueBuffer(timedValues);
    workbook.close();
}
 
Example 20
Source File: ExcelToSQLite.java    From SQLiteToExcel with Apache License 2.0 4 votes vote down vote up
/**
 * create table by sheet
 *
 * @param sheet
 */
private void createTable(Sheet sheet) {
    StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
    createTableSql.append(sheet.getSheetName());
    createTableSql.append("(");
    Iterator<Row> rit = sheet.rowIterator();
    Row rowHeader = rit.next();
    List<String> columns = new ArrayList<>();
    for (int i = 0; i < rowHeader.getPhysicalNumberOfCells(); i++) {
        createTableSql.append(rowHeader.getCell(i).getStringCellValue());
        if (i == rowHeader.getPhysicalNumberOfCells() - 1) {
            createTableSql.append(" TEXT");
        } else {
            createTableSql.append(" TEXT,");
        }
        columns.add(rowHeader.getCell(i).getStringCellValue());
    }
    createTableSql.append(")");
    database.execSQL(createTableSql.toString());
    while (rit.hasNext()) {
        Row row = rit.next();
        ContentValues values = new ContentValues();
        for (int n = 0; n < row.getPhysicalNumberOfCells(); n++) {
            if (row.getCell(n) == null) {
                continue;
            }
            if (row.getCell(n).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(n))) {
                    if (sdf == null) {
                        values.put(columns.get(n), DateFormat.getDateTimeInstance().format(row.getCell(n).getDateCellValue()));
                    } else {
                        values.put(columns.get(n), sdf.format(row.getCell(n).getDateCellValue()));
                    }
                } else {
                    String value = getRealStringValueOfDouble(row.getCell(n).getNumericCellValue());
                    values.put(columns.get(n), value);
                }
            } else if (row.getCell(n).getCellType() == Cell.CELL_TYPE_STRING) {
                values.put(columns.get(n), row.getCell(n).getStringCellValue());
            }
        }
        if (values.size() == 0)
            continue;
        long result = database.insert(sheet.getSheetName(), null, values);
        if (result < 0) {
            throw new RuntimeException("Insert value failed!");
        }
    }
}