Java Code Examples for org.apache.poi.xssf.usermodel.XSSFWorkbook#getSheetAt()
The following examples show how to use
org.apache.poi.xssf.usermodel.XSSFWorkbook#getSheetAt() .
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: TableXLS.java From Rel with Apache License 2.0 | 6 votes |
|
private TupleIterator iteratorRawXLSX() throws IOException {
FileInputStream reader = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(reader);
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
return new SpreadsheetTupleIterator(sheet.iterator()) {
@Override
public void close() {
try {
workbook.close();
} catch (IOException e1) {
}
try {
reader.close();
} catch (IOException e) {
}
}
};
}
Example 2
| Source File: Issue50MultiRowCrosstabHeaderGrids.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testHeader() throws Exception {
debug = false;
InputStream inputStream = runAndRenderReport("Issue50MultiRowCrosstabHeaderGrids.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
Sheet sheet = workbook.getSheetAt(0);
assertEquals( "Atelier graphique", sheet.getRow(2).getCell(1).getStringCellValue() );
assertTrue( mergedRegion( sheet, 0, 0, 1, 0 ) );
assertTrue( mergedRegion( sheet, 0, 1, 1, 1 ) );
assertEquals( 34, sheet.getNumMergedRegions() );
assertEquals( 100, this.firstNullRow(workbook.getSheetAt(0)));
} finally {
inputStream.close();
}
}
Example 3
| Source File: Borders4ReportTest.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testRunReport() throws BirtException, IOException {
removeEmptyRows = false;
InputStream inputStream = runAndRenderReport("Borders4.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 2, workbook.getNumberOfSheets() );
Sheet sheet = workbook.getSheetAt(1);
assertEquals( 2, firstNullRow(sheet));
assertBorder( sheet, 0, 0, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE );
assertBorder( sheet, 0, 1, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE, CellStyle.BORDER_NONE );
} finally {
inputStream.close();
}
}
Example 4
| Source File: PoiTest.java From easyexcel with Apache License 2.0 | 6 votes |
|
@Test
public void lastRowNum2333() throws IOException, InvalidFormatException {
String file = TestFileUtil.getPath() + "fill" + File.separator + "simple.xlsx";
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new File(file));
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);
Sheet xssfSheet = xssfWorkbook.getSheetAt(0);
Cell cell = xssfSheet.getRow(0).createCell(9);
cell.setCellValue("testssdf是士大夫否t");
FileOutputStream fileout = new FileOutputStream("d://test/r2" + System.currentTimeMillis() + ".xlsx");
sxssfWorkbook.write(fileout);
sxssfWorkbook.dispose();
sxssfWorkbook.close();
xssfWorkbook.close();
}
Example 5
| Source File: Issue62RowSpanAutoHeight.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testIssue62Spread() throws BirtException, IOException {
debug = false;
removeEmptyRows = false;
spannedRowHeight = ExcelEmitter.SPANNED_ROW_HEIGHT_SPREAD;
InputStream inputStream = runAndRenderReport("Issue62RowSpanAutoHeight.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
Sheet sheet = workbook.getSheetAt(0);
assertEquals( 9, this.lastRow(sheet));
assertEquals( sheet.getRow(0).getHeight(), sheet.getRow(1).getHeight() );
assertEquals( sheet.getRow(3).getHeight(), sheet.getRow(4).getHeight() );
assertTrue( sheet.getRow(6).getHeight() < sheet.getRow(7).getHeight() );
assertTrue( sheet.getRow(6).getHeight() > sheet.getRow(8).getHeight() );
assertTrue( sheet.getRow(7).getHeight() > sheet.getRow(8).getHeight() );
} finally {
inputStream.close();
}
}
Example 6
| Source File: NestedTablesReportTest.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testRunReport() throws BirtException, IOException {
InputStream inputStream = runAndRenderReport("NestedTables.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
assertEquals( "Nested Tables Test Report", workbook.getSheetAt(0).getSheetName());
Sheet sheet = workbook.getSheetAt(0);
assertEquals(1, firstNullRow(sheet));
assertEquals( "One Two Three\n1 2 3\n2 4 6\n3 6 9", sheet.getRow(0).getCell(0).getStringCellValue());
assertEquals( "One Two Three\n1 2 3\n2 4 6\n3 6 9", sheet.getRow(0).getCell(1).getStringCellValue());
} finally {
inputStream.close();
}
}
Example 7
| Source File: BasicReportTest.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testRunReportWithJpegXlsxZerosReport() throws BirtException, IOException {
InputStream inputStream = runAndRenderReport("SimpleWithJpegHideZeros.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
assertEquals( "Simple Test Report", workbook.getSheetAt(0).getSheetName());
Sheet sheet = workbook.getSheetAt(0);
assertEquals( false, sheet.isDisplayFormulas() );
assertEquals( true, sheet.isDisplayGridlines() );
assertEquals( true, sheet.isDisplayRowColHeadings() );
assertEquals( false, sheet.isDisplayZeros() );
performSimpleWithJpegTests(sheet);
} finally {
inputStream.close();
}
}
Example 8
| Source File: Issue82NoSuchMethod.java From birt with Eclipse Public License 1.0 | 6 votes |
|
@Test
public void testIssue86BlankRowInGroupHeader() throws BirtException, IOException {
debug = false;
InputStream inputStream = runAndRenderReport("Issue82TableInFooter.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
Sheet sheet = workbook.getSheetAt(0);
assertEquals( 4, this.firstNullRow(sheet));
} finally {
inputStream.close();
}
}
Example 9
| Source File: DataFormatTest.java From easyexcel with Apache License 2.0 | 5 votes |
|
@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 10
| Source File: Issue87PrintSettings.java From birt with Eclipse Public License 1.0 | 5 votes |
|
@Test
public void testPrintPagesXlsx() throws Exception {
scale = false;
InputStream inputStream = runAndRenderReport("BigCrosstab.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
assertEquals( 13, workbook.getNumCellStyles() );
assertEquals( "Big Crosstab Report 1", workbook.getSheetAt(0).getSheetName());
assertEquals( 2, workbook.getSheetAt(0).getPrintSetup().getFitWidth() );
assertEquals( 3, workbook.getSheetAt(0).getPrintSetup().getFitHeight() );
assertEquals( true, workbook.getSheetAt(0).getAutobreaks() );
assertEquals( 60, workbook.getSheetAt(0).getRow(1).getCell(2).getCellStyle().getRotation());
assertEquals( 60, workbook.getSheetAt(0).getRow(2).getCell(2).getCellStyle().getRotation());
assertEquals( 60, workbook.getSheetAt(0).getRow(2).getCell(3).getCellStyle().getRotation());
assertEquals( 0, workbook.getSheetAt(0).getRow(3).getCell(2).getCellStyle().getRotation());
assertTrue( runTime - startTime < 4500L );
assertTrue( renderTime - runTime < 4000L );
Sheet sheet = workbook.getSheetAt(0);
assertEquals( 236, firstNullRow(sheet));
assertEquals(28, greatestNumColumns(sheet));
} finally {
inputStream.close();
}
}
Example 11
| Source File: ExcelAdjacencyListExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
int numberOfSheets = workbook.getNumberOfSheets();
for(int i=0; i<numberOfSheets && !forceStop(); i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
processSheet(sheet, topicMap);
}
}
Example 12
| Source File: PoiXSSFExcelUtil.java From JavaWeb with Apache License 2.0 | 5 votes |
|
public static List<?> readSingleExcelSheet(InputStream inputStream,int sheetIndex,Map<Integer,String> map,Class<?> objectClass) throws Exception {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheetIndex);
List<?> list = readSheet(xssfSheet,map,objectClass);
xssfWorkbook.close();
return list;
}
Example 13
| Source File: ExcelTopicTreeExtractor.java From wandora with GNU General Public License v3.0 | 5 votes |
|
@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
int numberOfSheets = workbook.getNumberOfSheets();
for(int i=0; i<numberOfSheets && !forceStop(); i++) {
XSSFSheet sheet = workbook.getSheetAt(i);
processSheet(sheet, topicMap);
}
}
Example 14
| Source File: PoiXSSFExcelUtil.java From JavaWeb with Apache License 2.0 | 5 votes |
|
public static Object[] readAllExcelSheet(InputStream inputStream) throws IOException {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
int sheets = xssfWorkbook.getNumberOfSheets();
Object[] objects = new Object[sheets];
for(int i=0;i<sheets;i++){
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(i);
objects[i] = readSheet(xssfSheet);
}
xssfWorkbook.close();
return objects;
}
Example 15
| Source File: TestExcelFormulaDemo.java From poi with Apache License 2.0 | 5 votes |
|
public static void readSheetWithFormula() {
try {
FileInputStream file = new FileInputStream(new File(
TestUtil.DOC_PATH + File.separator + EXCEL_NAME
+ Globals.SUFFIX_XLSX));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// If it is formula cell, it will be evaluated otherwise no
// change will happen
switch (evaluator.evaluateInCell(cell).getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t\t");
break;
case Cell.CELL_TYPE_FORMULA:
// Not again
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Example 16
| Source File: ReadXlsxFileWithHeader.java From WhiteRabbit with Apache License 2.0 | 5 votes |
|
public RowIterator() {
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
XSSFSheet sheet = workbook.getSheetAt(0);
iterator = sheet.iterator();
fieldName2ColumnIndex = new HashMap<String, Integer>();
for (Cell header : iterator.next())
fieldName2ColumnIndex.put(header.toString(), fieldName2ColumnIndex.size());
} catch (IOException e) {
e.printStackTrace();
}
}
Example 17
| Source File: Issue55GroupsNotWorkingCorrectly.java From birt with Eclipse Public License 1.0 | 4 votes |
|
@Test
public void testHeaderHierarchy() throws Exception {
debug = false;
groupSummaryHeader = true;
InputStream inputStream = runAndRenderReport("Issue55GroupHierarchy.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 1, workbook.getNumberOfSheets() );
XSSFSheet sheet0 = workbook.getSheetAt( 0 );
assertEquals( ! groupSummaryHeader, sheet0.getRowSumsBelow() );
/* for( int i = 0; i < 64; ++i ) {
System.out.println( "assertEquals( " + sheet0.getRow( i ).getCTRow().getOutlineLevel() + ", sheet0.getRow( " + i + " ).getCTRow().getOutlineLevel() );" );
}
*/
assertEquals( 0, sheet0.getRow( 0 ).getCTRow().getOutlineLevel() );
assertEquals( 0, sheet0.getRow( 1 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 2 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 3 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 4 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 5 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 6 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 7 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 8 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 9 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 10 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 11 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 12 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 13 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 14 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 15 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 16 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 17 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 18 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 19 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 20 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 21 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 22 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 23 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 24 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 25 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 26 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 27 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 28 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 29 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 30 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 31 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 32 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 33 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 34 ).getCTRow().getOutlineLevel() );
assertEquals( 0, sheet0.getRow( 35 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 36 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 37 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 38 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 39 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 40 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 41 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 42 ).getCTRow().getOutlineLevel() );
assertEquals( 0, sheet0.getRow( 43 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 44 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 45 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 46 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 47 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 48 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 49 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 50 ).getCTRow().getOutlineLevel() );
assertEquals( 0, sheet0.getRow( 51 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 52 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 53 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 54 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 55 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 56 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 57 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 58 ).getCTRow().getOutlineLevel() );
assertEquals( 0, sheet0.getRow( 59 ).getCTRow().getOutlineLevel() );
assertEquals( 1, sheet0.getRow( 60 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 61 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 62 ).getCTRow().getOutlineLevel() );
assertEquals( 2, sheet0.getRow( 63 ).getCTRow().getOutlineLevel() );
} finally {
inputStream.close();
}
}
Example 18
| Source File: Groupings.java From birt with Eclipse Public License 1.0 | 4 votes |
|
@Test
public void testGroupingsBlockedByTable() throws BirtException, IOException {
InputStream inputStream = runAndRenderReport("GroupingDisabledAtTable.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals( 3, workbook.getNumberOfSheets() );
XSSFSheet sheet0 = workbook.getSheetAt(0);
XSSFSheet sheet1 = workbook.getSheetAt(1);
XSSFSheet sheet2 = workbook.getSheetAt(2);
assertEquals( "HeaderAndFooter", sheet0.getSheetName());
int rowNum0 = 1;
int rowNum1 = 1;
int rowNum2 = 1;
for( int i = 1; i < 9; ++i ) {
System.out.println( "i==" + i );
assertEquals( "rowNum=" + rowNum0, 0, sheet0.getRow( rowNum0++ ).getCTRow().getOutlineLevel() );
assertEquals( "rowNum=" + rowNum1, 1, sheet1.getRow( rowNum1++ ).getCTRow().getOutlineLevel() );
assertEquals( "rowNum=" + rowNum2, 0, sheet2.getRow( rowNum2++ ).getCTRow().getOutlineLevel() );
for( int j = 0; j < i; ++j) {
assertEquals( "rowNum=" + rowNum0, 0, sheet0.getRow( rowNum0++ ).getCTRow().getOutlineLevel() );
if( j < i - 1 ) {
assertEquals( "rowNum=" + rowNum1, i == 1 ? 0 : 1, sheet1.getRow( rowNum1++ ).getCTRow().getOutlineLevel() );
assertEquals( "rowNum=" + rowNum2, 0, sheet2.getRow( rowNum2++ ).getCTRow().getOutlineLevel() );
}
}
assertEquals( "rowNum=" + rowNum0, 0, sheet0.getRow( rowNum0++ ).getCTRow().getOutlineLevel() );
assertEquals( "rowNum=" + rowNum1, 0, sheet1.getRow( rowNum1++ ).getCTRow().getOutlineLevel() );
assertEquals( "rowNum=" + rowNum2, 0, sheet2.getRow( rowNum2++ ).getCTRow().getOutlineLevel() );
}
assertTrue( rowNum0 > 50 );
assertTrue( rowNum1 > 40 );
assertTrue( rowNum2 > 40 );
} finally {
inputStream.close();
}
}
Example 19
| Source File: LondonLonelinessImporter.java From TomboloDigitalConnector with MIT License | 4 votes |
|
@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 20
| Source File: OptionsFileConverterUtil.java From sakai with Educational Community License v2.0 | 4 votes |
|
public static List<String> convertInputStreamToOptionList(InputStream in) throws IOException {
List<String> optionsList = new ArrayList<String>();
try (BufferedInputStream bufferedInputStream = new BufferedInputStream(in)) {
Iterator<Row> iterator;
switch(FileMagic.valueOf(bufferedInputStream)) {
case OOXML:
log.debug("Input file detected as OOXML.");
XSSFWorkbook workbook = new XSSFWorkbook(bufferedInputStream);
XSSFSheet datatypeSheet = workbook.getSheetAt(0);
iterator = datatypeSheet.iterator();
break;
case OLE2:
log.debug("Input file detected as OLE2.");
HSSFWorkbook lagacyWorkbook = new HSSFWorkbook(bufferedInputStream);
HSSFSheet legacyDatatypeSheet = lagacyWorkbook.getSheetAt(0);
iterator = legacyDatatypeSheet.iterator();
break;
default:
log.debug("Input file detected as UNKNOWN, try to open it as text and ignore if it's not ASCII text.");
try(Scanner scanner = new Scanner(bufferedInputStream).useDelimiter("\\r\\n")) {
while(scanner.hasNext()){
String inputString = HtmlUtils.htmlEscape(scanner.next(), "UTF-8");
if(StringUtils.isNotBlank(inputString)){
optionsList.add(inputString);
}
}
} catch(Exception ex){
throw new IOException("Error processing the file as text type.", ex);
}
return optionsList;
}
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Iterator<Cell> cellIterator = currentRow.iterator();
if(cellIterator.hasNext()) {
Cell currentCell = cellIterator.next();
switch(currentCell.getCellType()) {
case STRING:
if (StringUtils.isNotBlank(currentCell.getStringCellValue())) {
optionsList.add(HtmlUtils.htmlEscape(currentCell.getStringCellValue(), "UTF-8"));
}
break;
case NUMERIC:
optionsList.add(String.valueOf(currentCell.getNumericCellValue()));
break;
case BOOLEAN:
optionsList.add(currentCell.getBooleanCellValue() ? "1" : "0");
break;
case FORMULA:
case BLANK:
case _NONE:
case ERROR:
default:
break;
}
}
}
} catch (Exception e) {
throw new IOException("Error converting the file to options list.");
}
return optionsList;
}
