JAVA-poi-Excel数据读取

JAVA-需求描述：从表格中读取信息导入到数据库

代码：

package com.test;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.tj.api.hospital.TjHospitalApi;
import com.tj.api.hospital.TjHospitalDepartmentApi;
import com.tj.api.hospital.TjHospitalDepartmentRelationApi;
import com.tj.api.hospital.TjHospitalProfessorApi;
import com.tj.mapper.apdcn.model.hospital.TjHospitalProfessorModel;

@RunWith(SpringJUnit4ClassRunner.class) // 使用junit4进行测试
@ContextConfiguration(“classpath*:spring/applicationContext.xml”)
public class test {

@Resource
private TjHospitalProfessorApi tjHospitalProfessorApi;

@Resource
private TjHospitalApi tjHospitalApi;

@Resource
private TjHospitalDepartmentApi tjHospitalDepartmentApi;

@Resource
private TjHospitalDepartmentRelationApi tjHospitalDepartmentRelationApi;

@org.junit.Test
public void readProfessiorExcel() {
    //excel文件路径
    String filepath = "C:\\Users\\Lenovo\\Desktop\\TOB产品SKU汇总12-5.xls";
    try {
        // 1.得到Excel工作簿对象
        @SuppressWarnings("resource")
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filepath));
        // 2.得到Excel工作表对象
        HSSFSheet sheet = wb.getSheetAt(0);
        // 总行数
        int trLength = sheet.getLastRowNum();
        // 3.得到Excel工作表的行
        //HSSFRow row = sheet.getRow(0);
        // 总列数
        //int tdLength = row.getLastCellNum();
        // 4.得到Excel工作表指定行的单元格
        // HSSFCell cell = row.getCell(0);
        // 5.得到单元格样式
        // CellStyle cellStyle = cell.getCellStyle();
        List<TjHospitalProfessorModel> professorModelList = new ArrayList<>();
        for (int i = 0; i < trLength; i++) {
            // 得到Excel工作表的行
            HSSFRow row1 = sheet.getRow(i + 1);
            TjHospitalProfessorModel professorModel = new TjHospitalProfessorModel();
            professorModel.setHospitalName(row1.getCell(0).getStringCellValue());
            professorModel.setDeptName(row1.getCell(1).getStringCellValue());
            professorModel.setName(row1.getCell(2).getStringCellValue());
            //处理读取Excel空单元格时，报空指针错误 
            if (row1.getCell(3) == null ) {
                professorModel.setField("");
            } else {
                professorModel.setField(row1.getCell(3).getStringCellValue());
            }
            String serverName = row1.getCell(4).getStringCellValue();
            String serverCont = row1.getCell(5).getStringCellValue();
            //poi 读取excel 把 数字转换成 字符
            if (row1.getCell(7) != null) {
                row1.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
            }
            String serverPrice = row1.getCell(7).getStringCellValue();
            String serverDetail = "";
            if (row1.getCell(8) != null) {
                serverDetail = row1.getCell(8).getStringCellValue();
            }
            String detail = "服务名称:" + serverName + "<br>"
                            + "服务内容:" + serverCont + "<br>"
                            + "服务价格(元):" + serverPrice + "<br>"
                            + "服务说明:" + serverDetail + "<br>";
            professorModel.setDetail(detail);
            professorModelList.add(professorModel);
        }
        for (TjHospitalProfessorModel tjModel : professorModelList) {
            //业务
            createProfessor(tjModel);
        }
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

public void createProfessor(TjHospitalProfessorModel professorModel) {

}

}

ps.关于遍历表格，推荐使用for (int i = 0; i < trLength; i++){} 的形式。在读取excel空行或者空单元格方便处理 。