Java HSSFWorkbook.getSheetAt方法代码示例

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
/**
 * Read the Excel 2003-2007
 * 
 * @param path
 *            the path of the Excel
 * @return
 * @throws IOException
 */
public static String readXls(String path) throws IOException {
	InputStream is = new FileInputStream(path);
	HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	StringBuffer sb = new StringBuffer("");
	// Read the Sheet
	for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			continue;
		}
		// Read the Row
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				HSSFCell no = hssfRow.getCell(0);
				HSSFCell name = hssfRow.getCell(1);
				sb.append(no + ":" + name);
				sb.append(";");
			}
		}
	}
	return sb.toString().substring(0, sb.toString().length() - 1);
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
public Map<String, BoundaryPoint> parse(InputStream is) throws IOException {
    Map<String, BoundaryPoint> boundaryPoints = new HashMap<>();
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();
    rowIterator.next();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell boundaryPointNameCell = row.getCell(13);
        Cell borderFromCell = row.getCell(14);
        Cell borderToCell = row.getCell(15);
        String boundaryPointName = boundaryPointNameCell.getStringCellValue();
        if (boundaryPointName.equals("-")) {
            continue;
        }
        Country borderFrom = toCountry(borderFromCell.getStringCellValue());
        Country borderTo = toCountry(borderToCell.getStringCellValue());
        boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo));
    }
    return boundaryPoints;
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
public static void reader(String filePath) {
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell((short) 0);
		int type = cell.getCellType();
		String msg = getCellStringValue(cell);
		System.out.println(type + ":" + msg);
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
public static void readExcel(String filePth) throws Exception {
	InputStream is = new FileInputStream(filePth);
	//创建工作薄
	//XSSFWorkbook hwb = new XSSFWorkbook(is);
	HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
	//得到sheet
	for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
		HSSFSheet sheet = hwb.getSheetAt(i);
		int rows = sheet.getPhysicalNumberOfRows();
		//遍历每一行
		for (int j = 0; j < rows; j++) {
			HSSFRow hr = sheet.getRow(j);
			Iterator<?> it = hr.iterator();
			while(it.hasNext()){
				String context = it.next().toString();
				System.out.println(context);
			}
		}
	}
	hwb.close();
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
@Test
public void test01() throws Exception {
    File file = new File("C:\\Users\\he\\Downloads\\2016-05-10.xls");

    try (FileInputStream fis = new FileInputStream(file)) {
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        System.out.println(workbook.sheetIterator().hasNext());
        HSSFSheet spreadsheet = workbook.getSheetAt(0);
        print(spreadsheet);
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
private Collection loadFromSpreadsheet(final InputStream excelFile)throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook(excelFile);

    data = new ArrayList();
    Sheet sheet = workbook.getSheetAt(0);

    int numberOfColumns = countNonEmptyColumns(sheet);
    List rows = new ArrayList();
    List rowData = new ArrayList();

    for (Row row : sheet) {

        if (isEmpty(row)) {
            break;
        } else if(row.getRowNum() == 0 ) {
            //do nothing
        }else {
            rowData.clear();
            for (int column = 0; column < numberOfColumns; column++) {
                Cell cell = row.getCell(column);
                rowData.add(objectFrom(workbook, cell));
            }
            rows.add(rowData.toArray());
        }
    }
    return rows;
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
public int parseGroupSpreadsheet(FormFile fileItem, Long groupingID, Map<String, Set<String>> groups)
    throws IOException {
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);

int startRow = sheet.getFirstRowNum();
int endRow = sheet.getLastRowNum();
int skipped = 0;

for (int i = startRow + 1; i < (endRow + 1); i++) {
    HSSFRow row = sheet.getRow(i);
    String login = parseStringCell(row.getCell(0));
    if (login != null) {
	login = login.trim();
	if (login.length() > 0) {
	    String groupName = row.getLastCellNum() > 3 ? parseStringCell(row.getCell(3)) : null;
	    groupName = groupName != null ? groupName.trim() : null;
	    if (groupName == null || groupName.length() == 0) {
		skipped++;
		GroupingUploadAJAXAction.log.warn("Unable to add learner " + login
			+ " for group in related to grouping " + groupingID + " as group name is missing.");
	    } else {
		Set<String> users = groups.get(groupName);
		if (users == null) {
		    users = new HashSet<String>();
		    groups.put(groupName, users);
		}
		users.add(login);
	    }
	}
    }
}
return skipped;
   }
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
@Override
public ArrayList<MsgEntity> getMsgListFromExcel(File targetFile) throws IOException {
	ArrayList<MsgEntity> list = new ArrayList<MsgEntity>();
	POIFSFileSystem fs = new POIFSFileSystem(targetFile);
	HSSFWorkbook workBook = new HSSFWorkbook(fs);
	try {
		System.out.println("开始解析");
		HSSFSheet sheet = workBook.getSheetAt(0);
		int rows = sheet.getLastRowNum() + 1;
		for (int r = 1; r < rows; r++) {
			HSSFRow row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			MsgEntity msg = new MsgEntity();
			String number = getStringVal(row.getCell(0));
			if(StringUtils.isEmpty(number)){
				continue;
			}
			msg.setTelNumber(number);
			
			String templateId = getStringVal(row.getCell(1));
			if (!StringUtils.isEmpty(templateId) && StringUtils.isNumeric(templateId)
					&& templateId.indexOf(".") == -1) {
				msg.setTemplateId(Long.parseLong(templateId));
			}
			msg.setContent(getStringVal(row.getCell(2)));
			if (HSSFDateUtil.isCellDateFormatted(row.getCell(3))) {
				msg.setSendTime(row.getCell(3).getDateCellValue());
			}
			list.add(msg);
		}
		
	} finally {
		workBook.close();
	}
	return list;

}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
@SuppressWarnings("resource")
private void assertXlsOutputCorrect(byte[] output) throws Exception {
	HSSFWorkbook workbook = new HSSFWorkbook(new ByteArrayInputStream(output));
	HSSFSheet sheet = workbook.getSheetAt(0);
	assertNotNull("Sheet should not be null", sheet);
	HSSFRow row = sheet.getRow(3);
	HSSFCell cell = row.getCell((short) 1);
	assertNotNull("Cell should not be null", cell);
	assertEquals("Cell content should be Dear Lord!", "Dear Lord!", cell.getRichStringCellValue().getString());
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
@Override
public ByteArrayOutputStream convert(InputStream stream) {
    ByteArrayOutputStream outStream;
    try {
        outStream = new ByteArrayOutputStream();
        // Read workbook into HSSFWorkbook
        HSSFWorkbook my_xls_workbook = new HSSFWorkbook(stream);
        // Read worksheet into HSSFSheet
        HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
        // To iterate over the rows
        Iterator<Row> rowIterator = my_worksheet.iterator();
        // We will create output PDF document objects at this point
        Document pdf = new Document();
        PdfWriter.getInstance(pdf, outStream);
        pdf.open();
        // we have two columns in the Excel sheet, so we create a PDF table
        // with two columns
        // Note: There are ways to make this dynamic in nature, if you want
        // to.
        PdfPTable my_table = new PdfPTable(2);
        // We will use the object below to dynamically add new data to the
        // table
        PdfPCell table_cell;
        // Loop through rows.
        printPdf(rowIterator, my_table);
        // Finally add the table to PDF document
        pdf.add(my_table);
        pdf.close();
        // we created our pdf file..
        stream.close(); // close xls
        return outStream;
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
/**
 * {@inheritDoc}
 */
@Override
public void generate(final ProgressMonitor monitor, final HSSFWorkbook workbook, final int sheetNo, final boolean useLogicalNameAsSheetName, final Map<String, Integer> sheetNameMap, final Map<String, ObjectModel> sheetObjectMap, final ERDiagram diagram, final Map<String, LoopDefinition> loopDefinitionMap) throws InterruptedException {

    final HSSFSheet sheet = workbook.getSheetAt(sheetNo);

    setSheetListData(workbook, sheet, sheetObjectMap, diagram);
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
/**
 * {@inheritDoc}
 */
@Override
public void generate(ProgressMonitor monitor, HSSFWorkbook workbook,
		int sheetNo, boolean useLogicalNameAsSheetName,
		Map<String, Integer> sheetNameMap,
		Map<String, ObjectModel> sheetObjectMap, ERDiagram diagram,
		Map<String, LoopDefinition> loopDefinitionMap)
		throws InterruptedException {

	HSSFSheet sheet = workbook.getSheetAt(sheetNo);

	this.setSheetListData(workbook, sheet, sheetObjectMap, diagram);
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
/**
 * 获取上传文件（Excel）
 * 
 * @param inputStream
 *            输入流，导入的excel数据流
 * @return 返回列表，key为标题列名，value为单元格值
 */
public static List<Map<String, String>> getExcelListMap(InputStream inputStream) {
	try {
		POIFSFileSystem poiFs = new POIFSFileSystem(inputStream);
		HSSFWorkbook wb = new HSSFWorkbook(poiFs);
		HSSFSheet sheet = wb.getSheetAt(0);
		int lastRowNumber = sheet.getLastRowNum();

		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Map<String, String> map = new HashMap<String, String>();
		int firstColsNum = 0;
		for (int i = 0; i <= lastRowNumber; i++) {
			if (sheet != null) {
				HSSFRow row = sheet.getRow(i);
				Map<String, String> tempMap = new HashMap<String, String>();
				if (row != null) {
					if (firstColsNum == 0) {
						firstColsNum = row.getLastCellNum();
					}
					int nullCount = 0;
					for (int k = 0; k < firstColsNum; k++) {
						if (i == 0) {
							map.put(String.valueOf(k), checkNull(getCellValue(row, k)).toUpperCase());
						} else {
							String cellValue = getCellValue(row, k);
							if (cellValue == null || "".equals(cellValue) || "null".equals(cellValue)) {
								nullCount++;
							}
							tempMap.put(map.get(String.valueOf(k)), checkNull(getCellValue(row, k)));
						}
					}
					if (tempMap.size() > 0 && nullCount != firstColsNum) {
						list.add(tempMap);
					}
				}
			}
		}
		return list;
	} catch (Exception e) {
		e.printStackTrace();
	}

	return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
private void xlsToHtml() throws Throwable {
	FileOutputStream output = new FileOutputStream(new File(htmlPath));
	StringBuffer htmlHeaderSB = new StringBuffer();
	htmlHeaderSB.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' "
			+ "xmlns='http://www.w3.org/TR/REC-html40'>");
	htmlHeaderSB.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>"
			+ "</head><body>");
	output.write(htmlHeaderSB.toString().getBytes());
	HSSFSheet sheet;
	HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath)); // 获整个Excel
	for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
		if (workbook.getSheetAt(sheetIndex) != null) {
			sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet
			if (sheet != null) {
				int firstRowNum = sheet.getFirstRowNum(); // 第一行
				int lastRowNum = sheet.getLastRowNum(); // 最后一行
				// 构造Table
				output.write(("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;"
						+ "border-collapse:collapse;\">").getBytes());
				for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
					if (sheet.getRow(rowNum) != null) {// 如果行不为空，
						HSSFRow row = sheet.getRow(rowNum);
						short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格
						short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格
						int height = (int) (row.getHeight() / 15.625); // 行的高度
						output.write(("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;"
								+ "margin:2px 0 2px 0;\">").getBytes());
						for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格
							HSSFCell cell = row.getCell(cellNum);
							if (cell != null) {
								if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
									StringBuffer tdStyle = new StringBuffer("<td style=\"border:1px solid #000; border-width:0 1px 1px 0;"
											+ "margin:2px 0 2px 0; ");
									HSSFCellStyle cellStyle = cell.getCellStyle();
									HSSFPalette palette = workbook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式
									HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor());
									HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor());
									String bgColor = convertToStardColor(hColor);// 背景颜色
									short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细
									short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小
									String fontColor = convertToStardColor(hColor2); // 字体颜色
									if (bgColor != null && !"".equals(bgColor.trim())) {
										tdStyle.append(" background-color:");
										tdStyle.append(bgColor);
										tdStyle.append("; ");
									}
									if (fontColor != null && !"".equals(fontColor.trim())) {
										tdStyle.append(" color:");
										tdStyle.append(fontColor);
										tdStyle.append("; ");
									}
									tdStyle.append(" font-weight:");
									tdStyle.append(boldWeight);
									tdStyle.append("; ");
									tdStyle.append(" font-size: ");
									tdStyle.append(fontHeight);
									tdStyle.append("%;");
									output.write((tdStyle + "\"").getBytes());

									int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); //
									int cellRegionCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列（solspan）
									int cellRegionRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行（rowspan）
									String align = convertAlignToHtml(cellStyle.getAlignment()); //
									String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment());

									output.write((" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" ").getBytes());
									output.write((" colspan=\"" + cellRegionCol + "\" rowspan=\"" + cellRegionRow + "\"").getBytes());
									output.write((">" + getCellValue(cell) + "</td>").getBytes());
								}
							}
						}
						output.write("</tr>".getBytes());
					}
				}
				output.write(("</table>").getBytes());
			}
		}
	}
	output.write(("</body></html>").getBytes());
	output.close();
}
 

import org.apache.poi.hssf.usermodel.HSSFWorkbook; //导入方法依赖的package包/类
private HSSFSheet getSheet(FormFile fileItem) throws IOException {
POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
return wb.getSheetAt(0);
   }