Java Code Examples for org.apache.poi.hssf.usermodel.HSSFWorkbook#getSheet()

private List<Session> readAllSessions(InputStream is) {
    final List<Session> result = new ArrayList<Session>();

    try {
        final POIFSFileSystem fileSystem = new POIFSFileSystem(is);
        final HSSFWorkbook workBook = new HSSFWorkbook(fileSystem);
        final HSSFSheet sheet = workBook.getSheet("Alle Tage");

        int rows = sheet.getPhysicalNumberOfRows();
        // as the row is a header we start with the second one
        for (int r = 1; r < rows; r++) {
            final HSSFRow row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            final Session session = getSessionFromRow(row, r);
            if (session != null) {
                result.add(session);
            }
        }
    } catch (Exception e) {
        throw new RuntimeException("Error while reading sessions from file", e);
    }
    return result;
}
 

/**
 * Adds a sheet in the Workbook
 * @param worksheet the worksheet
 * @param sheetName name of sheet
 */
public static void addSheet(Worksheet worksheet, String sheetName) {
	HSSFWorkbook workbook = null;
	HSSFSheet sheet= null;
	String classSymbol = null;
	
	workbook = worksheet.getWorkbook();
	if (workbook.getSheet(sheetName) != null) {
		return;
	}
	sheet = workbook.createSheet(sheetName);
	
	Map<String, HSSFSheet> sheets = worksheet.getSheets();
	Map<String, String> sheetSymbol = worksheet.getSheetSymbol();
	
	classSymbol = sheetSymbol.get(sheetName);
	 if ( classSymbol != null) {
		 sheets.put(classSymbol, sheet);
	 } else {
		 sheets.put(sheetName, sheet);
	 }
}
 

public ExcelFileParser(String fileName, String sheetName, boolean isXlsx) throws Exception {
	if (isXlsx) {
		workBookXlsx = new XSSFWorkbook(new FileInputStream(fileName));
		workBookSheetXlsx = workBookXlsx.getSheet(sheetName);
	} else {
		workBookXls = new HSSFWorkbook(new FileInputStream(fileName));
		workBookSheetXls = workBookXls.getSheet(sheetName);
	}
}
 

public static HSSFSheet getTemplateSheetForRangeName(HSSFWorkbook workbook, String rangeName) {
    int rangeNameIdx = workbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1) return null;

    HSSFName aNamedRange = workbook.getNameAt(rangeNameIdx);
    String sheetName = aNamedRange.getSheetName();
    return workbook.getSheet(sheetName);
}
 

private HSSFWorkbook loadTemplateWorkbook(InputStream template,
		ERDiagram diagram) throws IOException {

	HSSFWorkbook workbook = POIUtils.readExcelBook(template);

	if (workbook == null) {
		throw new IOException(ResourceString
				.getResourceString("error.read.file"));
	}

	HSSFSheet wordsSheet = workbook.getSheet(WORDS_SHEET_NAME);

	if (wordsSheet == null) {
		throw new IOException(ResourceString
				.getResourceString("error.not.found.words.sheet"));
	}

	HSSFSheet loopsSheet = workbook.getSheet(LOOPS_SHEET_NAME);

	if (loopsSheet == null) {
		throw new IOException(ResourceString
				.getResourceString("error.not.found.loops.sheet"));
	}

	this.initLoopDefinitionMap(loopsSheet);

	for (AbstractSheetGenerator sheetGenerator : SHHET_GENERATOR_LIST) {
		sheetGenerator.init(wordsSheet);
	}

	this.sheetIndexSheetGenerator = new SheetIndexSheetGenerator();
	this.sheetIndexSheetGenerator.init(wordsSheet);

	return workbook;
}
 

public static void readFile(String path) throws IOException {
	// Path to the excel file
	File datafile = new File(path);
	// A Buffered File Input Stream to read the data
	InputStream fis = new BufferedInputStream(new FileInputStream(datafile));
	// We create a workbook which represents the excel file
	HSSFWorkbook book = new HSSFWorkbook(fis);
	// Next a sheet which represents the sheet within that excel file
	HSSFSheet sheet = book.getSheet("Details");
	// No of rows in the sheet
	int rowNum = sheet.getLastRowNum() + 1;
	// No of columns in the sheet
	int colNum = sheet.getRow(0).getLastCellNum();
	// A Two dimensional array of Strings which represents the data in the
	// sheet
	String[][] data = new String[rowNum][colNum];

	for (int i = 0; i < rowNum; i++) {
		// Get the row
		HSSFRow row = sheet.getRow(i);
		for (int j = 0; j < colNum; j++) {
			// Get the columns or cells for the first row and keep looping
			// for the other rows
			HSSFCell cell = row.getCell(j);
			// Make a call to the method cellToString which actually
			// converts the cell contents to String
			String value = cellToString(cell);
			data[i][j] = value;
			// Here is where you write the logic to handle the data.I am
			// just printing out the contents here.
			System.out.println("The value is " + value);
		}
	}
}
 

/**
   * workbook转数据对象
   * @param workbook excel表格对象
   * @param rowNumber 开始行
   * @return List
   */
  public static JSONArray workbookToData(String propertiesPath,String key,String sheetName,HSSFWorkbook workbook,int rowNumber){
if (logger.isDebugEnabled()) {
	logger.debug("workbookToData(String, String, String, HSSFWorkbook, int) - start"); //$NON-NLS-1$
}

  	JSONArray array = new JSONArray();
//将写入excel的数据不能为空
if (workbook!=null) {
	//读取properties配置文件
	PropertiesModel properties = PropertiesCacheUtil.loadProperties(propertiesPath);
	if (properties!=null) {
		//从properties中获取导出对应的配置json
		String columnJsonString = properties.get(key);
		try {
			//创建一个sheet
			HSSFSheet sheet = null;
			if (StringUtils.isNotBlank(sheetName)) {
				sheet = workbook.getSheet(sheetName);
			}
			//如果未获取到sheet则获取第一个sheet
			if (sheet==null) {
				sheet = workbook.getSheetAt(0);
			}
			if (sheet!=null) {
				//定义sheet的行码,并保存列信息
				//List<String> columnList = new ArrayList<String>();
				JSONObject columnJson = JSONObject.parseObject(columnJsonString);
				//读取每列对应的列号通过标题
				Map<Integer, String> map = readSheetHead(sheet, columnJson, rowNumber++);
				//读取内容
				rowNumber = readSheetBody(sheet, map, rowNumber,array);
			}else{
				logger.error("workbookToData#sheet is null");
			}
			
		} catch (Exception e) {
			logger.error("workbookToData#parse json error",e);
		}
		
	}else{
		logger.warn("workbookToData#properties is null");
	}
}

if (logger.isDebugEnabled()) {
	logger.debug("workbookToData(String, String, String, HSSFWorkbook, int) - end"); //$NON-NLS-1$
}
  	return array;
  }
 

private HSSFWorkbook loadTemplateWorkbook(final InputStream template, final ERDiagram diagram) throws IOException {

        final HSSFWorkbook workbook = POIUtils.readExcelBook(template);

        if (workbook == null) {
            throw new IOException(ResourceString.getResourceString("error.read.file"));
        }

        final HSSFSheet wordsSheet = workbook.getSheet(WORDS_SHEET_NAME);

        if (wordsSheet == null) {
            throw new IOException(ResourceString.getResourceString("error.not.found.words.sheet"));
        }

        final HSSFSheet loopsSheet = workbook.getSheet(LOOPS_SHEET_NAME);

        if (loopsSheet == null) {
            throw new IOException(ResourceString.getResourceString("error.not.found.loops.sheet"));
        }

        initLoopDefinitionMap(loopsSheet);

        for (final AbstractSheetGenerator sheetGenerator : SHHET_GENERATOR_LIST) {
            sheetGenerator.init(wordsSheet);
        }

        sheetIndexSheetGenerator = new SheetIndexSheetGenerator();
        sheetIndexSheetGenerator.init(wordsSheet);

        return workbook;
    }