Java Code Examples for org.apache.poi.ss.usermodel.Workbook#getSheetAt()

public void generate(File templateFile, OutputStream out, final ETemplateContext context){
//		File destFile = FileUtils.copyFile(templatePath, generatedPath);
//		System.out.println("dest: " + destFile);
		
		Workbook wb = readExcelTemplate(templateFile);
		int sheetNumbs = wb.getNumberOfSheets();
		
		ExcelTemplateValueProvider provider = new ExcelTemplateValueProvider(context);
		for (int index = 0; index < sheetNumbs; index++) {
			Sheet sheet = wb.getSheetAt(index);
			ETSheetContext directiveContext = new ETSheetContext(this, provider, context);
			directiveContext.setSheet(sheet);
			parseSheet(directiveContext);
		}
		try {
			wb.write(out);
		} catch (Exception e) {
			throw new ExcelException("write workbook error: " + e.getMessage());
		}
	}
 

/**
 * 在指定的工作簿、行、列书写值。
 * 作者：朱志杰
 * Sep 19, 2012 3:25:01 PM
 * @param sheetOrder 工作簿序号，基于0.
 * @param colum 列 基于1
 * @param row 行 基于1
 * @param content 将要被书写的内容。
 * @throws Exception 书写后保存异常。
 */
public void write(int sheetOrder,int colum, int row, String content) throws Exception {
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	Row rows = sheet.createRow(row-1);
	Cell cell = rows.createCell(colum-1);
	cell.setCellValue(content);
	FileOutputStream fileOut = new FileOutputStream(path);
	workbook.write(fileOut);
	fileOut.close();

}
 

public void printStyles(Workbook wb) {
    if (DEFAULTS_CLASS_CSS == null) {
        DEFAULTS_CLASS_CSS = getDefaultsClassCss();
    }
    out.format(DEFAULTS_CLASS_CSS);
    Set<CellStyle> seen = new HashSet<CellStyle>();
    sheet = wb.getSheetAt(sheetNum);
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        Row row = rows.next();
        for (Cell cell : row) {
            CellStyle style = cell.getCellStyle();
            if (!seen.contains(style)) {
                printStyle(style);
                seen.add(style);
            }
        }
    }
}
 

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;
}
 

/**
 * 得到一个工作区最后一条记录的序号，相当于这个工作簿共多少行数据。
 * @param sheetOrder 工作区序号
 * @return int 序号。
 * @throws IOException 根据excel路径加载excel异常。
 * @throws InvalidFormatException 
 */
public int getSheetLastRowNum(int sheetOrder) throws IOException, InvalidFormatException{
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	return sheet.getLastRowNum();
}
 

@Test
public void saveMapsTest_UsingGeneratedHeader_File() throws InvalidFormatException, IOException, ParseException {
	Map<String, Object> record = new LinkedHashMap<>();
	record.put("primIntProp", 1);
	record.put("intObjProp", 100);
	record.put("strProp", "some string");
	record.put("dateProp", "2000-01-01 00:00:00");


	Collection<Map<String, Object>> records = Arrays.asList(record);
	File outputFile = createFile("saveMapsTest_UsingGeneratedHeader_File");
	// save it
	Ssio.saveMaps(Arrays.asList("primIntProp", "intObjProp", "strProp", "dateProp"), 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);

	List<String> headerCells = getAllCells(headerRow).stream().map(c -> c.getStringCellValue()).collect(Collectors.toList());
	List<Object> dataCells = getAllCells(dataRow).stream().map(c -> getStringOrDateValue(c)).collect(Collectors.toList());
	Map<String, Object> keyValueMap = new LinkedHashMap<>();
	for (int i = 0; i < headerCells.size(); i++) {
		keyValueMap.put(headerCells.get(i), dataCells.get(i));
	}

	Assert.assertEquals(4, keyValueMap.size());
	Assert.assertEquals("1", keyValueMap.get("Prim Int Prop"));
	Assert.assertEquals("100", keyValueMap.get("Int Obj Prop"));
	Assert.assertEquals("some string", keyValueMap.get("Str Prop"));
	Assert.assertEquals("2000-01-01 00:00:00", keyValueMap.get("Date Prop"));


}
 

@Test
public void poi0702() throws Exception {
    Workbook workbook = WorkbookFactory.create(new FileInputStream("D:\\test\\t2.xlsx"));
    workbook = WorkbookFactory.create(new File("D:\\test\\t2.xlsx"));
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);
    System.out.println(row.getCell(0).getNumericCellValue());
}
 

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());
           		}
           	}
           }
       }
}
 

public static void readExcel(String filePath) {

        System.out.println("filePath:" + filePath);

        try {
            Workbook wb = WorkbookFactory.create(new FileInputStream(new File(filePath)));
            Sheet sheet = wb.getSheetAt(0);
            int i = 0;
            for (Row row : sheet) {
                System.out.print("row:" + i + "--->:");
                i++;
               String value = null;
                for (Cell cell : row) {
                     switch (cell.getCellTypeEnum()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                value = String.valueOf(cell.getNumericCellValue());
                                break;
                            default:
                                continue;
                        }

                    System.out.print(value + ",");
                }
                System.out.println();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 

@Test
public void givenFormualCell_whenGetCellStringValue_thenReturnOriginalFormulaString() throws IOException {
    Workbook workbook = new XSSFWorkbook(fileLocation);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(0);

    ExcelCellFormatter formatter = new ExcelCellFormatter();
    assertEquals("SUM(1+2)", formatter.getCellStringValue(row.getCell(FORMULA_CELL_INDEX)));
    workbook.close();
}
 

public Object getCellValueByFetchingLastCachedValue(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

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

    Sheet sheet = workbook.getSheetAt(0);

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

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

    workbook.close();
    return cellValue;
}
 

@Test
public void saveTest_IgnoringErrors_File() throws InvalidFormatException, IOException {
	LinkedHashMap<String, String> headerMap = new LinkedHashMap<String, String>();
	headerMap.put("primInt", "Primitive Int");
	headerMap.put("fake", "Not Real");

	ITRecord record = new ITRecord();
	record.setPrimInt(123);

	Collection<ITRecord> records = Arrays.asList(record);
	File outputFile = createFile("saveTest_IgnoringErrors_File");

	// save it
	Ssio.save(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("", cell11.getStringCellValue());
	
}
 

@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());

}
 

protected static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
    for(int i=0; i<wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for(Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateFormulaCellEnum(c);
                }
            }
        }
    }
}
 

private void importToDataBase(InputStream stream) throws InvalidFormatException, IOException {
    try {
        //        Workbook workbook = new HSSFWorkbook(stream);
        Workbook workbook = WorkbookFactory.create(stream);
        Sheet sheet = workbook.getSheetAt(0);//取第一个sheet
        List<Test> testList = new ArrayList<Test>();

        int rowStart = Math.max(1, sheet.getFirstRowNum());//从第二行开始查找
        int rowEnd = Math.min(Integer.MAX_VALUE, sheet.getLastRowNum());
        for (int rownum = rowStart; rownum < rowEnd; rownum++) {
            Row row = sheet.getRow(rownum);

            int lastColumn = Math.max(row.getLastCellNum(), 1);

            Test test = new Test();
            for (int columnnum = 0; columnnum < lastColumn; columnnum++) {
                Cell c = row.getCell(columnnum, Row.RETURN_BLANK_AS_NULL);
                if (c == null) {//记录为空
                    continue;
                } else {//batch
                    test.setMsg(setCellValue(c).toString());
                    testList.add(test);
                }
            }
        }

        service.saveBatchTest(testList);
    } finally {
        try {
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
 

public String[][] getCellData(String path) throws InvalidFormatException, IOException {
    System.out.println(path);
    FileInputStream stream = new FileInputStream(path);
    Workbook workbook = WorkbookFactory.create(stream);
    System.out.println(workbook);
    Sheet s = workbook.getSheetAt(0);
    System.out.println(s);
    int rowcount = s.getLastRowNum();
    int cellcount = s.getRow(0).getLastCellNum();
    System.out.println(rowcount);
    String data[][] = new String[rowcount][cellcount];
    for (int rowCnt = 1; rowCnt <= rowcount; rowCnt++) {
        Row row = s.getRow(rowCnt);
        for (int colCnt = 0; colCnt < cellcount; colCnt++) {
            Cell cell = row.getCell(colCnt);
            try {
                if (cell.getCellType() == cell.CELL_TYPE_STRING) {
                    data[rowCnt - 1][colCnt] = cell.getStringCellValue();
                } else {
                    data[rowCnt - 1][colCnt] = String.valueOf(cell.getNumericCellValue());
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    return data;
}
 

/**
 * 读取某个工作簿上的某个单元格的值。
 * 作者：朱志杰
 * Sep 19, 2012 3:27:08 PM
 * @param sheetOrder 工作簿序号,从0开始。
 * @param colum 列数 从1开始
 * @param row 行数 从1开始
 * @return 单元格的值。
 * @throws Exception 加载excel异常。
 */
public String read(int sheetOrder,int colum, int row) throws Exception {
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	Row rows = sheet.getRow(row-1);
	Cell cell = rows.getCell(colum-1);
	String content = cell.getStringCellValue();
	return content;
}
 

/**
 * 构建柱状图.
 *
 * @param chart          the chart
 * @param seriesDataList the series data list
 * @throws IOException the io exception
 * @since nile -cmszbs-szcst 0.1.0
 */
private static void generatorBarGraph(XSLFChart chart, List<ChartSeries> seriesDataList) {
    //创建Excel 绑定到图表
    Workbook workBook = createGraphWorkBook(seriesDataList);
    Sheet sheet = workBook.getSheetAt(0);
    CTPlotArea plotArea = chart.getCTChart().getPlotArea();
    CTBarChart barChart = plotArea.getBarChartList().get(0);

    if (seriesDataList.size() > barChart.getSerList().size()) {
        int addTimes = seriesDataList.size() - barChart.getSerList().size();
        for (int i = 0; i < addTimes; i++) {
            CTBarSer ctBarSer = barChart.addNewSer();

            CTSerTx serTx = ctBarSer.addNewTx();

            CTAxDataSource axDataSource = ctBarSer.addNewCat();

            CTNumDataSource numDataSource = ctBarSer.addNewVal();

            addSeries(serTx, axDataSource, numDataSource);
        }
    }

    if (barChart.getSerList().size() > seriesDataList.size()) {
        int removeTimes = barChart.getSerList().size() - seriesDataList.size();
        for (int i = 0; i < removeTimes; i++) {
            barChart.removeSer(barChart.getSerList().size() - 1);
        }
    }

    List<CTBarSer> serList = barChart.getSerList();
    if (serList == null) {
        throw new ReportExcetion(ReportErrorEnum.BAR_CHART_SERIER_ERROR.getMsg());
    }

    for (int i = 0; i < seriesDataList.size(); i++) {
        ChartSeries seriesData = seriesDataList.get(i);

        CTBarSer barSer = serList.get(i);
        //更新系列名称
        CTSerTx tx = barSer.getTx();

        //获取类别和值的数据源操作对象
        CTAxDataSource category = barSer.getCat();
        CTNumDataSource val = barSer.getVal();

        int colNum = i + 1;
        refreshSeriesData(sheet, tx, category, val, seriesData, colNum);
    }
    // 更新嵌入的workbook
    writeToGraphExcel(workBook, chart);

}
 

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}
 

/**
 * 通用读取Excel
 *
 * @Title: readExcel
 * @Date : 2014-9-11 上午11:26:53
 * @param wb
 * @return
 */
public List<List<Row>> readExcel(Workbook wb) {
	List<List<Row>> list = new ArrayList<>();
	List<Row> rowList = new ArrayList<>();
	int sheetCount = 1;// 需要操作的sheet数量
	
	Sheet sheet = null;
	if (onlyReadOneSheet) { // 只操作一个sheet
		// 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
		sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
	} else { // 操作多个sheet
		sheetCount = wb.getNumberOfSheets();// 获取可以操作的总数量
	}
	
	// 获取sheet数目
	for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
		// 获取设定操作的sheet
		if (!onlyReadOneSheet) {
			sheet = wb.getSheetAt(t);
		}
		
		// 获取最后行号
		int lastRowNum = sheet.getLastRowNum();
		
		if (lastRowNum > 0) { // 如果>0，表示有数据
			out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
		}
		
		Row row = null;
		// 循环读取
		for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
			row = sheet.getRow(i);
			if (row != null) {
				rowList.add(row);
				out("第" + (i + 1) + "行：", false);
				// 获取每一单元格的值
				for (int j = 0; j < row.getLastCellNum(); j++) {
					String value = getCellValue(row.getCell(j));
					if (!value.equals("")) {
						out(value + " | ", false);
					}
				}
				out("");
			}
			
		}
		list.add(rowList);
	}
	return list;
}