Java按行读取Excel内容-蒲公英云

Java按行读取Excel内容

一. Java对Excel文件读取，通常用poi包来实现

二.代码如下

public class ExcelUtils {

public static final String OFFICE_EXCEL_XLS = “xls”;

public static final String OFFICE\_EXCEL\_XLSX = "xlsx";  
/\*\*  
 \* 读取指定Sheet也的内容  
 \* @param filepath filepath 文件全路径  
 \* @param sheetNo sheet序号,从0开始,如果读取全文sheetNo设置null  
 \*/  
public static String readExcel(String filepath, Integer sheetNo)  
        throws EncryptedDocumentException, InvalidFormatException, IOException \{  
    StringBuilder sb = new StringBuilder();  
    Workbook workbook = getWorkbook(filepath);  
    if (workbook != null) \{  
        if (sheetNo == null) \{  
            int numberOfSheets = workbook.getNumberOfSheets();  
            for (int i = 0; i < numberOfSheets; i++) \{  
                Sheet sheet = workbook.getSheetAt(i);  
                if (sheet == null) \{  
                    continue;  
                \}  
                sb.append(readExcelSheet(sheet));  
            \}  
        \} else \{  
            Sheet sheet = workbook.getSheetAt(sheetNo);  
            if (sheet != null) \{  
                sb.append(readExcelSheet(sheet));  
            \}  
        \}  
    \}  
    return sb.toString();  
\}  
/\*\*  
 \* 根据文件路径获取Workbook对象  
 \* @param filepath 文件全路径  
 \*/  
public static Workbook getWorkbook(String filepath)  
        throws EncryptedDocumentException, InvalidFormatException, IOException \{  
    InputStream is = null;  
    Workbook wb = null;  
    if (StringUtils.isBlank(filepath)) \{  
        throw new IllegalArgumentException("文件路径不能为空");  
    \} else \{  
        String suffiex = getSuffiex(filepath);  
        if (StringUtils.isBlank(suffiex)) \{  
            throw new IllegalArgumentException("文件后缀不能为空");  
        \}  
        if (OFFICE\_EXCEL\_XLS.equals(suffiex) || OFFICE\_EXCEL\_XLSX.equals(suffiex)) \{  
            try \{  
                is = new FileInputStream(filepath);  
                wb = WorkbookFactory.create(is);  
            \} finally \{  
                if (is != null) \{  
                    is.close();  
                \}  
                if (wb != null) \{  
                    wb.close();  
                \}  
            \}  
        \} else \{  
            throw new IllegalArgumentException("该文件非Excel文件");  
        \}  
    \}  
    return wb;  
\}  
/\*\*  
 \* 获取后缀  
 \* @param filepath filepath 文件全路径  
 \*/  
private static String getSuffiex(String filepath) \{  
    if (StringUtils.isBlank(filepath)) \{  
        return "";  
    \}  
    int index = filepath.lastIndexOf(".");  
    if (index == -1) \{  
        return "";  
    \}  
    return filepath.substring(index + 1, filepath.length());  
\}

//返回的内容用#按行分隔，便于在后台解析字符串
private static String readExcelSheet(Sheet sheet) {
StringBuilder sb = new StringBuilder();
if(sheet != null){
int rowNos = sheet.getLastRowNum();// 得到excel的总记录条数
for (int i = 0; i <= rowNos; i++) {// 遍历行
Row row = sheet.getRow(i);
if(row != null){
int columNos = row.getLastCellNum();// 表头总共的列数
for (int j = 0; j < columNos; j++) {
Cell cell = row.getCell(j);
if(cell != null){
cell.setCellType(CellType.STRING);
if(j == (columNos-1)){
sb.append(cell.getStringCellValue());
}else{
sb.append(cell.getStringCellValue() + “ “);
}
}
}
}
sb.append(“#“);
}
}
return sb.toString();