Java Workbook.getSheetAt方法代码示例

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static List<String[]> readExcel(InputStream is, int sheetIndex) throws Exception {
	Workbook workbook = WorkbookFactory.create(is);
	Sheet sheet = workbook.getSheetAt(sheetIndex);
	List<String[]> data = new ArrayList<>();
	for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
		Row row = sheet.getRow(i);
		if (row == null) continue;
		int last = row.getLastCellNum();
		String[] rowData = new String[last];
		for (int j = 0; j < last; j++) {
			Cell cell = row.getCell(j);
			rowData[j] = cell == null ? null : getCellString(cell);
		}
		data.add(rowData);
	}
	return data;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public <T> List<T> unmarshal(Class<T> type) {
    Workbook workbook = poijiWorkbook.workbook();
    Sheet sheet = workbook.getSheetAt(options.sheetIndex());

    int skip = options.skip();
    int maxPhysicalNumberOfRows = sheet.getPhysicalNumberOfRows() + 1 - skip;
    List<T> list = new ArrayList<>(maxPhysicalNumberOfRows);

    for (Row currentRow : sheet) {

        if (skip(currentRow, skip))
            continue;

        if (isRowEmpty(currentRow))
            continue;

        if (maxPhysicalNumberOfRows > list.size()) {
            T t = deserialize0(currentRow, type);
            list.add(t);
        }
    }

    return list;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static <T>  ImportResult<T> importExcel(ExcelFileType fileType,InputStream inputStream,Class<T> clazz) throws Exception{
	if(importInfoMap.get(clazz) == null){//初始化信息
		initTargetClass(clazz);			
	}
	ImportInfo importInfo = importInfoMap.get(clazz);
	Integer headRow = importInfo.getHeadRow();
	Workbook workbook = createWorkbook(fileType, inputStream);
	
	int sheetNum = workbook.getNumberOfSheets();
	if(sheetNum < 1 ){
		return null;
	}
	Sheet sheet = workbook.getSheetAt(0);
	int rowCount = sheet.getPhysicalNumberOfRows();
	if(rowCount < (headRow+1)){//
		return null;
	}
	List<String> headNameList = createHeadNameList(sheet, headRow);
	return readData(clazz, importInfo, workbook,headNameList);
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
@Override
protected void fillTrie(Logger logger, Trie<List<String>> trie, Corpus corpus) throws IOException, ModuleException {
	Iterator<InputStream> inputStreams = xlsFile.getInputStreams();
	while (inputStreams.hasNext()) {
		try (InputStream is = inputStreams.next()) {
			Workbook wb = WorkbookFactory.create(is);
			for (int sheetNumber : sheets) {
				Sheet sheet = wb.getSheetAt(sheetNumber);
				fillSheetEntries(trie, sheet);
			}
		}
		catch (EncryptedDocumentException|InvalidFormatException e) {
			rethrow(e);
		}
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static int getEmptyRow(Workbook wb, int sheetIndex, String cellRef) {
	final Sheet sheet = wb.getSheetAt(sheetIndex);
	final CellReference cellReference = new CellReference(cellRef); // һ����A1
	boolean flag = false;
	for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
		final Row r = sheet.getRow(i);
		if (r == null) {
			// ����ǿ��У���û���κ����ݡ���ʽ����ֱ�Ӱ������µ����������ƶ�
			sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
			continue;
		}
		flag = false;
		for (final Cell c : r) {
			if (c.getCellType() != Cell.CELL_TYPE_BLANK) {
				flag = true;
				break;
			}
		}
		if (flag) {
			i++;
			continue;
		} else {// ����ǿհ��У�������û�����ݣ�������һ����ʽ��
			if (i == sheet.getLastRowNum())// ����������һ�У�ֱ�ӽ���һ��remove��
				sheet.removeRow(r);
			else// �����û�����һ�У�������������һ��
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
		}
	}
	return sheet.getLastRowNum() + 1;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
/**
 * 读取Excel里面客户的信息
 * @param wb
 * @return
 */
private Map<String, Object> readInfoValue(Workbook wb) {
    //得到第一个shell
    Sheet sheet = wb.getSheetAt(0);
    Map<String, Object> modelMap = new HashMap<>();
    //得到Excel的行数
    this.totalRows = sheet.getPhysicalNumberOfRows();
    //得到Excel的列数(前提是有行数)
    if (totalRows >= 1 && sheet.getRow(0) != null) {
        this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    }
    List<InfoEntity> infoList = new ArrayList<InfoEntity>();
    InfoEntity info;
    List<ErrorJson> errorList = new ArrayList<ErrorJson>();
    ErrorJson error;
    //循环Excel行数,从第二行开始。标题不入库
    for (int r = 1; r < totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row == null) continue;
        info = new InfoEntity();
        //循环Excel的列
        for (int c = 0; c < this.totalCells; c++) {
            Cell cell = row.getCell(c);
            if (null != cell) {
                if (c == 0) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    info.setTitle(cell.getStringCellValue());//title

                } else if (c == 1) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    info.setContext(cell.getStringCellValue());//context
                } else if (c == 2) {
                    Date ti =new Date();
                    Timestamp time = new Timestamp(ti.getTime());
                    info.setTime(time);//time
                } else if (c == 3) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    info.setSender(cell.getStringCellValue());//sender
                }
                else if (c == 4) {
                    info.setStudentId((int) cell.getNumericCellValue());//学号

                }

            }
        }
        //添加学生
        System.out.println(info);
        if (info.getStudentId()!=0&&info.getTitle()!=""&&info.getContext()!=""&&info.getTime()!=null&&info.getSender()!="") {
            infoList.add(info);
        } else if (info.getStudentId()==0) {
            error = new ErrorJson();
            error.setId(info.getId());
            error.setErrors("学号不能为空");
            errorList.add(error);
        } else if ( info.getTitle()=="") {
            error = new ErrorJson();
            error.setId(info.getId());
            error.setErrors("title不能为空");
            errorList.add(error);
        } else if (info.getId() != 0 && info.getContext() == "") {
            error = new ErrorJson();
            error.setId(info.getId());
            error.setErrors("内容不能为空");
            errorList.add(error);
        } else if (info.getId() != 0 && info.getSender() == "") {
            error = new ErrorJson();
            error.setId(info.getId());
            error.setErrors("发送者不能为空");
            errorList.add(error);
        }
    }
    modelMap.put("result", infoList);
    modelMap.put("error", errorList);
    return modelMap;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
/**
 * 读取Excel里面客户的信息
 * @param wb
 * @return
 */
private Map<String, Object> readStudentValue(Workbook wb) {
    //得到第一个shell
    Sheet sheet = wb.getSheetAt(0);
    Map<String, Object> modelMap = new HashMap<>();
    //得到Excel的行数
    this.totalRows = sheet.getPhysicalNumberOfRows();
    //得到Excel的列数(前提是有行数)
    if (totalRows >= 1 && sheet.getRow(0) != null) {
        this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    }
    List<StudentEntity> studentList = new ArrayList<StudentEntity>();
    StudentEntity student;
    List<ErrorJson> errorList = new ArrayList<ErrorJson>();
    ErrorJson error;
    //循环Excel行数,从第二行开始。标题不入库
    for (int r = 1; r < totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row == null) continue;
        student = new StudentEntity();
        //循环Excel的列
        for (int c = 0; c < this.totalCells; c++) {
            Cell cell = row.getCell(c);
            if (null != cell) {
                if (c == 0) {
                    student.setStudentId((int) cell.getNumericCellValue());//学号
                } else if (c == 1) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    student.setStudentName(cell.getStringCellValue());//学生姓名
                } else if (c == 2) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    student.setClassName(cell.getStringCellValue());//班级
                } else if (c == 3) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    student.setMajor(cell.getStringCellValue());//专业
                }
                student.setPassword("00000000");

            }
        }
        //添加学生
        System.out.println(student);
        if (student.getStudentId() != 0 && student.getStudentName() != "" && student.getClassName() != null && student.getMajor() != "") {
            studentList.add(student);
        } else if (student.getStudentId() != 0 && student.getStudentName() == "") {
            error = new ErrorJson();
            error.setId(student.getStudentId());
            error.setErrors("名字不能为空");
            errorList.add(error);
        } else if (student.getStudentId() != 0 && student.getClassName() == null) {
            error = new ErrorJson();
            error.setId(student.getStudentId());
            error.setErrors("班级不能为空");
            errorList.add(error);
        } else if (student.getStudentId() != 0 && student.getMajor() == "") {
            error = new ErrorJson();
            error.setId(student.getStudentId());
            error.setErrors("专业不能为空");
            errorList.add(error);
        }
    }
    modelMap.put("result", studentList);
    modelMap.put("error", errorList);
    return modelMap;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
/**
 * 读取Excel里面客户的信息
 * @param wb
 * @return
 */
private Map<String, Object> readStudentValue(Workbook wb) {
    //得到第一个shell
    Sheet sheet = wb.getSheetAt(0);
    Map<String, Object> modelMap = new HashMap<>();
    //得到Excel的行数
    this.totalRows = sheet.getPhysicalNumberOfRows();
    //得到Excel的列数(前提是有行数)
    if (totalRows >= 1 && sheet.getRow(0) != null) {
        this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
    }
    List<ClassroomEntity> classList = new ArrayList<ClassroomEntity>();
    ClassroomEntity classroom;
    List<ErrorCJson> errorList = new ArrayList<ErrorCJson>();
    ErrorCJson error;
    //循环Excel行数,从第二行开始。标题不入库
    for (int r = 1; r < totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row == null) continue;
        classroom = new ClassroomEntity();
        //循环Excel的列
        for (int c = 0; c < this.totalCells; c++) {
            Cell cell = row.getCell(c);
            if (null != cell) {
                if (c == 0) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    classroom.setClassroom(cell.getStringCellValue());//教室名
                } else if (c == 1) {
                    classroom.setPeopleNum((int) cell.getNumericCellValue());//教室容量
                }

            }
        }
        System.out.println(classroom);
        if (classroom.getClassroom() != null && classroom.getPeopleNum() >=0 && classroom.getPeopleNum() <1000 ) {
            classList.add(classroom);
        }else if(classroom.getPeopleNum() <0 && classroom.getPeopleNum() >1000){
            error = new ErrorCJson();
            error.setCourseId(classroom.getClassroom());
            error.setErrors("教室容量过小或过大");
        }
    }
    modelMap.put("result", classList);
    modelMap.put("error", errorList);
    return modelMap;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
private BaudData generateBaudEntries(File xls) throws Exception {
    BaudData baudData = new BaudData();
    Set<BaudEntryDay> baudEntries = new TreeSet<>();
    baudData.setBaudEntries(baudEntries);

    FileInputStream fis = new FileInputStream(xls);

    Workbook wb = new HSSFWorkbook(fis);
    Sheet sheet = wb.getSheetAt(0);

    String name = sheet.getRow(1).getCell(0).getStringCellValue();
    baudData.setFundId(name);

    for(int i = 4; i <= sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);

        String date = row.getCell(0).getStringCellValue();

        LocalDate localDate = LocalDate.parse(date, DateTimeFormatter.ofPattern("dd.MM.yyyy"));
        double totalNav = row.getCell(5).getNumericCellValue();
        double sharePrice = row.getCell(6).getNumericCellValue();

        BaudEntryDay baudEntryDay = new BaudEntryDay();
        baudEntryDay.setDate(localDate);
        baudEntryDay.setTotalNav(totalNav);
        baudEntryDay.setSharePrice(sharePrice);

        baudEntries.add(baudEntryDay);
    }

    wb.close();

    return baudData;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static double failureRate() {
	// Time.getTime();
	// final String path = System.getProperty("user.dir") + "/" + processName + "/"
	// + Time.year + "/" + Time.month
	// + "/" + Time.day + ".xlsx";
	// final File xlsx = new File(path);
	int rowIndex = 0;
	int failureCount = 0;
	final Workbook wb = GA_AA_Data.tmpWB;

	if (wb == null)
		return 0;
	else {
		rowIndex = ExcelOperation.getEmptyRow(wb, 0, "A1");

		for (int i = 1; i < rowIndex; i++) {
			final Sheet sheet = wb.getSheetAt(0);
			final Row row = sheet.getRow(i);
			final Cell cell = row.getCell(10);
			if (cell.getStringCellValue().equals("NG")) {
				failureCount++;
			}
		}
		// System.out.println(failureCount);
		// System.out.println(rowIndex);
		final BigDecimal bd_rate = new BigDecimal((double) failureCount * 100 / (rowIndex - 1));
		final double rate = bd_rate.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
		return rate;
	}
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static Workbook writeOneRow(Workbook wb, int sheetIndex, int rowIndex, List<String> content) {
	final Sheet sheet = wb.getSheetAt(sheetIndex);
	final Row row = sheet.createRow(rowIndex);
	for (int i = 0; i < content.size(); i++) {
		row.createCell(i).setCellValue(content.get(i));
	}
	return wb;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
private Sheet selectSheet(Workbook workbook) {
	if(sheetIndex != null) {
		return workbook.getSheetAt(sheetIndex);
	}
	return workbook.getSheet(sheetName);
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
private List<List<String>> read(Workbook wb, int sheetIndex) {
    List<List<String>> dataLst = Lists.newArrayList();
    Sheet sheet = wb.getSheetAt(sheetIndex);
    this.totalRows = sheet.getPhysicalNumberOfRows();
    if ((this.totalRows >= 1) && (sheet.getRow(0) != null)) {
        this.totalCells = sheet.getRow(0).getLastCellNum(); // 获取最后一个不为空的列是第几个
    }
    for (int r = 0; r < this.totalRows; r++) {
        Row row = sheet.getRow(r);
        if (row != null) {
            List<String> rowLst = Lists.newArrayList();
            for (int c = 0; c < getTotalCells(); c++) {
                Cell cell = row.getCell(c);

                String cellValue = "";
                if (cell != null) {
                    switch (cell.getCellType()) {
                        case 0:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();

                                cellValue = DateUtils.dateToStr(date,
                                        "yyyy-MM-dd HH:mm:ss");

                            } else {
                                Integer num = (int) cell.getNumericCellValue();
                                cellValue = String.valueOf(num);
                            }
                            break;
                        case 1:
                            cellValue = cell.getStringCellValue().trim();
                            break;
                        case 4:
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case 2:
                            cellValue = cell.getCellFormula();
                            break;
                        case 3:
                            cellValue = "";
                            break;
                        case 5:
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                    }
                }
                rowLst.add(cellValue);
            }
            dataLst.add(rowLst);
        }
    }
    return dataLst;
}
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
@Test
    public void testExportFeedback() throws IOException {
        int plantComments = 160;
        int plantMetadata = 500;
        int bedMetadata = 500;
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();

        FeedbackWriter feedback = new FeedbackWriter(buffer);
        String [] input = new String[COL_CMT_FIELDS];
        for(int i = 0; i < plantComments; i++) {
            input[COL_CMT_PLANTID] = "16001";
            input[COL_CMT_COMMONNAME] = "Alternathera";
            input[COL_CMT_CULTIVAR] = "Experimental";
            input[COL_CMT_GRDNLOC] = "1S";
            input[COL_CMT_COMMENT] = "What a lovely flower! " + i;
            input[COL_CMT_DATE] = "4 10 2017"; //This isn't the format
            feedback.writeToSheet(input, SHEET_COMMENTS);
        }

        input = new String[FeedbackWriter.COL_PLANT_FIELDS];
        for(int i = 0; i < plantMetadata; i++) {
            input[COL_PLANT_PLANTID] = "16001";
            input[COL_PLANT_COMMONNAME] = "Alternathera";
            input[COL_PLANT_CULTIVAR] = "Experimental";
            input[COL_PLANT_GRDNLOC] = "1S";
            input[COL_PLANT_LIKES] = Integer.toString(i);
            input[COL_PLANT_DISLIKES] = "0";
            input[COL_PLANT_COMMENTS] = "4";
            input[COL_PLANT_PAGEVIEWS] = "4";
            feedback.writeToSheet(input, SHEET_METADATA);
        }

        input = new String[FeedbackWriter.COL_BED_FIELDS];
        for(int i = 0; i < bedMetadata; i++) {
            input[COL_BED_GRDNLOC] = "1S";
            input[COL_BED_PAGEVIEWS] = "0";
            input[COL_BED_QRSCANS] = "0";
            feedback.writeToSheet(input, SHEET_BEDMETADATA);
        }

        feedback.complete();
        //Use that output stream and produce an input stream from the bytes to
        //read the spreadsheet into an XSSFWorkbook
        ByteArrayInputStream reRead = new ByteArrayInputStream(buffer.toByteArray());
        Workbook workbook = new XSSFWorkbook(reRead);
        assertEquals("Excel Spreadsheet does not contain two Sheets", workbook.getNumberOfSheets(), 3);

        Sheet sheet;

        sheet = workbook.getSheetAt(0); //Comment Sheet
        assertEquals("Comment Sheet does not contain "+ plantComments +"+2 rows",sheet.getPhysicalNumberOfRows(), plantComments+2);

        sheet = workbook.getSheetAt(1); //Metadata Sheet
        assertEquals("Metadata Sheet does not contain "+ plantMetadata + "+2 rows",sheet.getPhysicalNumberOfRows(), plantMetadata+2);

//        sheet = workbook.getSheetAt(2); //Metadata Sheet
//        assertEquals("Bed Metadata Sheet does not contain "+ bedMetadata + "+2 rows",sheet.getPhysicalNumberOfRows(), plantMetadata+2);

    }
 

import org.apache.poi.ss.usermodel.Workbook; //导入方法依赖的package包/类
public static void wipeoutContent(Workbook wb, int sheetIndex, String cellRef) {
	final int lastRowIndex = getEmptyRow(wb, sheetIndex, cellRef);
	final Sheet sheet = wb.getSheetAt(sheetIndex);
	sheet.shiftRows(1, lastRowIndex - 1, -1);
	
}