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

The following examples show how to use org.apache.poi.ss.usermodel.DataFormatter. 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: myexcel   Author: liaochong   File: DefaultExcelReader.java    License: Apache License 2.0 8 votes vote down vote up
private T instanceObj(Map<Integer, Field> fieldMap, DataFormatter formatter, Row row) {
    T obj = ReflectUtil.newInstance(dataType);
    fieldMap.forEach((index, field) -> {
        if (field.getType() == InputStream.class) {
            convertPicture(row, obj, index, field);
            return;
        }
        Cell cell = row.getCell(index, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        if (cell == null) {
            return;
        }
        String content = formatter.formatCellValue(cell);
        if (content == null) {
            return;
        }
        content = trim.apply(content);
        context.reset(obj, field, content, row.getRowNum(), index);
        ReadConverterContext.convert(obj, context, convertContext, exceptionFunction);
    });
    return obj;
}
 
Example #2
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 #3
Source Project: mobi   Author: inovexcorp   File: DelimitedRestTest.java    License: GNU Affero General Public License v3.0 7 votes vote down vote up
private List<String> getExcelResourceLines(String fileName) {
    List<String> expectedLines = new ArrayList<>();
    try {
        Workbook wb = WorkbookFactory.create(getClass().getResourceAsStream("/" + fileName));
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        int index = 0;
        for (Row row : sheet) {
            String rowStr = "";
            for (Cell cell : row) {
                rowStr += df.formatCellValue(cell, evaluator);
            }
            expectedLines.add(index, rowStr);
            index++;
        }
    } catch (IOException | InvalidFormatException e) {
        e.printStackTrace();
    }
    return expectedLines;
}
 
Example #4
Source Project: vividus   Author: vividus-framework   File: ExcelSheetParserTests.java    License: Apache License 2.0 6 votes vote down vote up
@Test
void testGetCellValuePreservingFormatting()
{
    List<List<String>> expectedStringData = new LinkedList<>();
    List<String> row1 = new LinkedList<>();
    row1.add("TRUE");
    row1.add("1");
    expectedStringData.add(row1);
    List<String> row2 = new LinkedList<>();
    row2.add("FALSE");
    row2.add("2");
    expectedStringData.add(row2);
    List<String> row3 = new LinkedList<>();
    row3.add("3");
    expectedStringData.add(row3);

    DataFormatter dataFormatter = new DataFormatter();
    sheetParser = new ExcelSheetParser(extractor.getSheet(AS_STRING_SHEET).get(), true, dataFormatter);
    List<List<String>> data = sheetParser.getData();
    assertEquals(expectedStringData, data);
}
 
Example #5
Source Project: qconfig   Author: qunarcorp   File: TemplateExcelParseServiceImpl.java    License: MIT License 6 votes vote down vote up
private String readCellAsString(final Cell cell) {
    if (cell == null) {
        return "";
    }

    switch (cell.getCellType()) {
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_BLANK:
            return "";
        case CELL_TYPE_BOOLEAN:
            return Boolean.toString(cell.getBooleanCellValue());
        case CELL_TYPE_NUMERIC:
            final DataFormatter formatter = new DataFormatter();
            return formatter.formatCellValue(cell);
        default:
            throw new RuntimeException("unknown cell type " + cell.getCellType());
    }

}
 
Example #6
Source Project: easyexcel   Author: alibaba   File: StyleTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void poi() throws Exception {
    InputStream is = new FileInputStream("D:\\test\\styleTest.xls");
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
    HSSFRow hssfRow = hssfSheet.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().getDataFormatString());
    System.out.println(hssfRow.getCell(1).getCellStyle().getDataFormatString());
    System.out.println(hssfRow.getCell(2).getCellStyle().getDataFormatString());

}
 
Example #7
Source Project: poiji   Author: ozlerhakan   File: XSSFUnmarshaller.java    License: MIT License 6 votes vote down vote up
private <T> void processSheet(StylesTable styles,
                              XMLReader reader,
                              ReadOnlySharedStringsTable readOnlySharedStringsTable,
                              Class<T> type,
                              InputStream sheetInputStream,
                              Consumer<? super T> consumer) {

    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        PoijiHandler<T> poijiHandler = new PoijiHandler<>(type, options, consumer);
        ContentHandler contentHandler
                = new XSSFSheetXMLPoijiHandler(styles,
                null,
                readOnlySharedStringsTable,
                poijiHandler,
                formatter,
                false,
                options);
        reader.setContentHandler(contentHandler);
        reader.parse(sheetSource);
    } catch (SAXException | IOException e) {
        IOUtils.closeQuietly(sheetInputStream);
        throw new PoijiException("Problem occurred while reading data", e);
    }
}
 
Example #8
Source Project: myexcel   Author: liaochong   File: SaxExcelReader.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Parses and shows the content of one sheet
 * using the specified styles and shared-strings tables.
 *
 * @param strings          The table of strings that may be referenced by cells in the sheet
 * @param sheetInputStream The stream to read the sheet-data from.
 * @throws java.io.IOException An IO exception from the parser,
 *                             possibly from a byte stream or character stream
 *                             supplied by the application.
 * @throws SAXException        if parsing the XML data fails.
 */
private void processSheet(
        SharedStrings strings,
        XSSFSheetXMLHandler.SheetContentsHandler sheetHandler,
        InputStream sheetInputStream) throws IOException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(
                null, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}
 
Example #9
Source Project: mobi   Author: inovexcorp   File: DelimitedRest.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * Converts the specified number of rows of a Excel file into JSON and returns
 * them as a String.
 *
 * @param input the Excel file to convert into JSON
 * @param numRows the number of rows from the Excel file to convert
 * @return a string with the JSON of the Excel rows
 * @throws IOException excel file could not be read
 * @throws InvalidFormatException file is not in a valid excel format
 */
private String convertExcelRows(File input, int numRows) throws IOException, InvalidFormatException {
    try (Workbook wb = WorkbookFactory.create(input)) {
        // Only support single sheet files for now
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        DataFormatter df = new DataFormatter();
        JSONArray rowList = new JSONArray();
        String[] columns;
        for (Row row : sheet) {
            if (row.getRowNum() <= numRows) {
                //getLastCellNumber instead of getPhysicalNumberOfCells so that blank values don't shift cells
                columns = new String[row.getLastCellNum()];
                for (int i = 0; i < row.getLastCellNum(); i++ ) {
                    columns[i] = df.formatCellValue(row.getCell(i), evaluator);
                }
                rowList.add(columns);
            }
        }
        return rowList.toString();
    }
}
 
Example #10
Source Project: axelor-open-suite   Author: axelor   File: ExcelReaderService.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
@Override
public boolean initialize(MetaFile input, String separator) {

  if (input == null) {
    return false;
  }

  File inFile = MetaFiles.getPath(input).toFile();
  if (!inFile.exists()) {
    return false;
  }

  try {
    FileInputStream inSteam = new FileInputStream(inFile);
    book = new XSSFWorkbook(inSteam);
    if (book.getNumberOfSheets() == 0) {
      return false;
    }
    formatter = new DataFormatter();
  } catch (IOException e) {
    e.printStackTrace();
    return false;
  }

  return true;
}
 
Example #11
Source Project: CloverETL-Engine   Author: CloverETL   File: XLSXDataParser.java    License: GNU Lesser General Public License v2.1 6 votes vote down vote up
@Override
public String[] getNames() throws ComponentNotReadyException{
	List<String> names = new ArrayList<String>();
	Row row = (metadataRow > -1) ? sheet.getRow(metadataRow) : sheet.getRow(firstRow);
	
	if (row == null) {
		throw new ComponentNotReadyException("Metadata row (" + (metadataRow > -1 ? metadataRow : firstRow) + 
				") doesn't exist in sheet " + StringUtils.quote(sheet.getSheetName()) + "!"); 
	}

	DataFormatter formatter = new DataFormatter();
	for (int i = 0; i < row.getLastCellNum(); i++) {
		Cell cell = row.getCell(i);

		if (cell != null) {
			String cellValue = formatter.formatCellValue(cell);
			names.add(XLSFormatter.getCellCode(i) + " - " + cellValue.substring(0, Math.min(cellValue.length(), MAX_NAME_LENGTH)));
		}
	}

	return names.toArray(new String[names.size()]);
}
 
Example #12
Source Project: data-prep   Author: Talend   File: XlsUtils.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Return the numeric value.
 *
 * @param cell the cell to extract the value from.
 * @return the numeric value from the cell.
 */
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
    // Date is typed as numeric
    if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
        return sdf.format(cell.getDateCellValue());
    }
    // Numeric type (use data formatter to get number format right)
    DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);

    if (cellValue == null) {
        return formatter.formatCellValue(cell);
    }

    return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
 
Example #13
Source Project: birt   Author: eclipse   File: CurrencyFormatsTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReportXls() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xls");
	assertNotNull(inputStream);
	try {
		
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Currency Formats Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals(5, this.firstNullRow(sheet));
		
		DataFormatter formatter = new DataFormatter();
		
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(1).getCell(1)));
		assertEquals( "$3141.59",              formatter.formatCellValue(sheet.getRow(2).getCell(1)));
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(3).getCell(1)));
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(4).getCell(1)));
	} finally {
		inputStream.close();
	}
}
 
Example #14
Source Project: birt   Author: eclipse   File: CurrencyFormatsTest.java    License: Eclipse Public License 1.0 6 votes vote down vote up
@Test
public void testRunReportXlsx() throws BirtException, IOException {

	InputStream inputStream = runAndRenderReport("CurrencyFormats.rptdesign", "xlsx");
	assertNotNull(inputStream);
	try {
		
		XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
		assertNotNull(workbook);
		
		assertEquals( 1, workbook.getNumberOfSheets() );
		assertEquals( "Currency Formats Test Report", workbook.getSheetAt(0).getSheetName());
		
		Sheet sheet = workbook.getSheetAt(0);
		assertEquals( 5, this.firstNullRow(sheet));
		
		DataFormatter formatter = new DataFormatter();
		
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(1).getCell(1)));
		assertEquals( "$3141.59",              formatter.formatCellValue(sheet.getRow(2).getCell(1)));
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(3).getCell(1)));
		assertEquals( "�3141.59",              formatter.formatCellValue(sheet.getRow(4).getCell(1)));
	} finally {
		inputStream.close();
	}
}
 
Example #15
Source Project: sailfish-core   Author: exactpro   File: ExcelMatrixReader.java    License: Apache License 2.0 5 votes vote down vote up
public ExcelMatrixReader(InputStream inputStream, boolean useXlsx, int sheetNumber) throws IOException {

        this.sheetIndex = sheetNumber;
        this.workbook = useXlsx ? new XSSFWorkbook(inputStream)
                : new HSSFWorkbook(new BufferedInputStream(inputStream));
        inputStream.close();
        formulaEvaluator =  workbook.getCreationHelper().createFormulaEvaluator();
        sheet = workbook.getSheetAt(sheetIndex);
        rowIterator = sheet.iterator();
        this.dataFormatter = new DataFormatter(Locale.US, false, true);
        readRecord();
    }
 
Example #16
Source Project: easyexcel   Author: alibaba   File: DataFormatTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void test3() throws IOException {
    String file = "D:\\test\\dataformat1.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    Cell cell = xssfSheet.getRow(0).getCell(0);
    DataFormatter d = new DataFormatter();
    System.out.println(d.formatCellValue(cell));
}
 
Example #17
Source Project: easyexcel   Author: alibaba   File: DataFormatTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void test355() throws IOException, InvalidFormatException {
    File file = TestFileUtil.readFile("dataformat" + File.separator + "dataformat.xlsx");
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 
Example #18
Source Project: easyexcel   Author: alibaba   File: DataFormatTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void test3556() throws IOException, InvalidFormatException {
    String file = "D://test/dataformat1.xlsx";
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
    Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
    DataFormatter d = new DataFormatter(Locale.CHINA);

    for (int i = 0; i < xssfSheet.getLastRowNum(); i++) {
        Row row = xssfSheet.getRow(i);
        System.out.println(d.formatCellValue(row.getCell(0)));
    }

}
 
Example #19
Source Project: easyexcel   Author: alibaba   File: StyleTest.java    License: Apache License 2.0 5 votes vote down vote up
private void print(Cell cell) {
    System.out.println(
        DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString()));
    System.out.println(cell.getCellStyle().getDataFormat());
    System.out.println(cell.getCellStyle().getDataFormatString());
    DataFormatter f = new DataFormatter();
    System.out.println(f.formatCellValue(cell));
    if (cell.getCellStyle().getDataFormatString() != null) {

    }
    ExcelStyleDateFormatter ff = new ExcelStyleDateFormatter(cell.getCellStyle().getDataFormatString());

}
 
Example #20
Source Project: easyexcel   Author: alibaba   File: StyleTest.java    License: Apache License 2.0 5 votes vote down vote up
private void isDate(Cell cell) {
    System.out.println(
        DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString()));
    System.out.println(HSSFDateUtil.isCellDateFormatted(cell));
    DataFormatter f = new DataFormatter();
    System.out.println(f.formatCellValue(cell));

}
 
Example #21
Source Project: poiji   Author: ozlerhakan   File: HSSFUnmarshaller.java    License: MIT License 5 votes vote down vote up
HSSFUnmarshaller(PoijiOptions options) {
    this.options = options;
    this.limit = options.getLimit();
    dataFormatter = new DataFormatter();
    columnIndexPerTitle = new HashMap<>();
    titlePerColumnIndex = new HashMap<>();
    caseSensitiveTitlePerColumnIndex = new HashMap<>();
    casting = options.getCasting();
}
 
Example #22
Source Project: poiji   Author: ozlerhakan   File: XSSFSheetXMLPoijiHandler.java    License: MIT License 5 votes vote down vote up
XSSFSheetXMLPoijiHandler(Styles styles,
                         Comments comments,
                         SharedStrings strings,
                         SheetContentsHandler sheetContentsHandler,
                         DataFormatter dataFormatter,
                         boolean formulasNotResults,
                         PoijiOptions poijiOptions) {
    super(styles, comments, strings, sheetContentsHandler, dataFormatter, formulasNotResults);
    this.stylesTable = styles;
    this.poijiOptions = poijiOptions;
    this.cellFormat = this.poijiOptions.getPoijiCellFormat();
}
 
Example #23
Source Project: DBus   Author: BriData   File: XLSX2CSV.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Accepts objects needed while parsing.
 *
 * @param styles  Table of styles
 * @param strings Table of shared strings
 * @param cols    Minimum number of columns to show
 * @param target  Sink for output
 */
public MyXSSFSheetHandler(StylesTable styles,
                          ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
    this.stylesTable = styles;
    this.sharedStringsTable = strings;
    this.minColumnCount = cols;
    this.output = target;
    this.value = new StringBuffer();
    this.nextDataType = xssfDataType.NUMBER;
    this.formatter = new DataFormatter();
}
 
Example #24
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 #25
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 single cell in a row
 * Convenience method for {@link #getCellWidth}
 *
 * @param row the row that contains the cell of interest
 * @param column the column number of the cell whose width is to be calculated
 * @param defaultCharWidth the width of a single character
 * @param formatter formatter used to prepare the text to be measured
 * @param useMergedCells    whether to use merged cells
 * @return  the width in pixels or -1 if cell is empty
 */
private static double getColumnWidthForRow(
        Row row, int column, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) {
    if( row == null ) {
        return -1;
    }

    Cell cell = row.getCell(column);

    if (cell == null) {
        return -1;
    }

    return getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
}
 
Example #26
Source Project: myexcel   Author: liaochong   File: DefaultExcelReader.java    License: Apache License 2.0 5 votes vote down vote up
private List<T> getDataFromFile(Sheet sheet, Map<Integer, Field> fieldMap) {
    long startTime = System.currentTimeMillis();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
    if (lastRowNum < 0) {
        log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
        return Collections.emptyList();
    }
    DataFormatter formatter = new DataFormatter();
    List<T> result = new LinkedList<>();
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            log.info("Row of {} is null,it will be ignored.", i);
            continue;
        }
        boolean noMatchResult = rowFilter.negate().test(row);
        if (noMatchResult) {
            log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
            continue;
        }
        int lastColNum = row.getLastCellNum();
        if (lastColNum < 0) {
            continue;
        }
        T obj = instanceObj(fieldMap, formatter, row);
        if (beanFilter.test(obj)) {
            result.add(obj);
        }
    }
    log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
    return result;
}
 
Example #27
Source Project: myexcel   Author: liaochong   File: DefaultExcelReader.java    License: Apache License 2.0 5 votes vote down vote up
private void readThenConsume(Sheet sheet, Map<Integer, Field> fieldMap, Consumer<T> consumer, Function<T, Boolean> function) {
    long startTime = System.currentTimeMillis();
    final int firstRowNum = sheet.getFirstRowNum();
    final int lastRowNum = sheet.getLastRowNum();
    log.info("FirstRowNum:{},LastRowNum:{}", firstRowNum, lastRowNum);
    if (lastRowNum < 0) {
        log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
        return;
    }
    DataFormatter formatter = new DataFormatter();
    for (int i = firstRowNum; i <= lastRowNum; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            log.info("Row of {} is null,it will be ignored.", i);
            continue;
        }
        boolean noMatchResult = rowFilter.negate().test(row);
        if (noMatchResult) {
            log.info("Row of {} does not meet the filtering criteria, it will be ignored.", i);
            continue;
        }
        int lastColNum = row.getLastCellNum();
        if (lastColNum < 0) {
            continue;
        }
        T obj = instanceObj(fieldMap, formatter, row);
        if (beanFilter.test(obj)) {
            if (consumer != null) {
                consumer.accept(obj);
            } else if (function != null) {
                Boolean noStop = function.apply(obj);
                if (!noStop) {
                    break;
                }
            }
        }
    }
    log.info("Reading excel takes {} milliseconds", System.currentTimeMillis() - startTime);
}
 
Example #28
Source Project: myexcel   Author: liaochong   File: XSSFSheetXMLHandler.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Accepts objects needed while parsing.
 *
 * @param styles               Table of styles
 * @param strings              Table of shared strings
 * @param formulasNotResults   formulasNotResults
 * @param sheetContentsHandler sheetContentsHandler
 * @param dataFormatter        dataFormatter
 * @param comments             comments
 */
public XSSFSheetXMLHandler(
        Styles styles,
        Comments comments,
        SharedStrings strings,
        XSSFSheetXMLHandler.SheetContentsHandler sheetContentsHandler,
        DataFormatter dataFormatter,
        boolean formulasNotResults) {
    this.stylesTable = styles;
    this.comments = comments;
    this.sharedStringsTable = strings;
    this.output = sheetContentsHandler;
    this.formulasNotResults = formulasNotResults;
    this.nextDataType = xssfDataType.NUMBER;
    this.formatter = dataFormatter;
    init(comments);
}
 
Example #29
Source Project: azeroth   Author: warlock-china   File: XLSX2CSV.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Parses and shows the content of one sheet using the specified styles and
 * shared-strings tables.
 *
 * @param styles
 * @param strings
 * @param sheetInputStream
 */
public void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, SheetContentsHandler sheetHandler,
                         InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
    } catch (ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
    }
}
 
Example #30
Source Project: Knowage-Server   Author: KnowageLabs   File: FileDatasetXlsDataReader.java    License: GNU Affero General Public License v3.0 5 votes vote down vote up
public FileDatasetXlsDataReader(JSONObject jsonConf) {
	super();

	formatter = new DataFormatter();

	// Get File Dataset Configuration Options
	if (jsonConf != null) {
		try {
			if (jsonConf.get(EXCEL_FILE_SKIP_ROWS) != null) {
				setSkipRows(jsonConf.get(EXCEL_FILE_SKIP_ROWS).toString());
			} else {
				setSkipRows("");
			}

			if (jsonConf.get(EXCEL_FILE_LIMIT_ROWS) != null) {
				setLimitRows(jsonConf.get(EXCEL_FILE_LIMIT_ROWS).toString());
			} else {
				setLimitRows("");
			}

			if (jsonConf.get(EXCEL_FILE_SHEET_NUMBER) != null) {
				setXslSheetNumber(jsonConf.get(EXCEL_FILE_SHEET_NUMBER).toString());
			} else {
				setXslSheetNumber("");
			}
			if (jsonConf.get(EXCEL_FILE_TYPE) != null) {
				setFileType(jsonConf.get(EXCEL_FILE_TYPE).toString());
			} else {
				setFileType("");
			}
		} catch (JSONException e) {
			throw new RuntimeException("Error Deserializing File Dataset Options", e);
		}
	} else {
		logger.error("Error jsonConf is not present for FileDatasetXlsDataReader");
		throw new RuntimeException("Error jsonConf is not present for FileDatasetXlsDataReader");
	}
}