Java 常用工具类

1）导入EXCEL文件数据

　　List<List<List<Cell>>> sheetLst = ExcelFileParser.parseExcel(file,suffix);
　　file: 文件路径
　　suffix：文件后缀

　　List<List<Cell>> rows = sheetLst.get(0);

对应工具类：

　　

package importFile;

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

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.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelFileParser {

public static final String EXCEL_97 = "XLS";
public static final String EXCEL_2007 = "XLSX";

/*
* return List{worksheet_1<list>{row1<List>{col1,col2,...},
* row2<List>{col1,col2,...}, ... row_n<List>{col1,col2,...} },
* worksheet_2<list>{row1<List>{col1,col2,...}, row2<List>{col1,col2,...},
* ... row_n<List>{col1,col2,...} }, ...
* worksheet_n<list>{row1<List>{col1,col2,...}, row2<List>{col1,col2,...},
* ... row_n<List>{col1,col2,...} } }
*/
public static List<List<List<Cell>>> parseExcel(File file, String type)
throws Exception {
List<List<List<Cell>>> sheetList = new ArrayList<List<List<Cell>>>();

if (type.equalsIgnoreCase(EXCEL_97)) {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
List<List<Cell>> rowList = new ArrayList<List<Cell>>();
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
List<Cell> colList = new ArrayList<Cell>();
HSSFRow row = sheet.getRow(i);
if (row == null)
continue;
int colNum = row.getLastCellNum();
for (int colIndex = 0; colIndex < colNum; colIndex++) {
Cell cell = row.getCell(colIndex);
colList.add(cell);
System.out.print(cell == null ? null : cell.toString());
}

rowList.add(colList);
}
sheetList.add(rowList);
}

} else if (type.equalsIgnoreCase(EXCEL_2007)) {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
List<List<Cell>> rowList = new ArrayList<List<Cell>>();
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
List<Cell> colList = new ArrayList<Cell>();
XSSFRow row = sheet.getRow(i);
if (row == null)
continue;
int colNum = row.getLastCellNum();
for (int colIndex = 0; colIndex < colNum; colIndex++) {
Cell cell = row.getCell(colIndex);
colList.add(cell);
System.out.print(cell == null ? null : cell.toString());
}
rowList.add(colList);
}
sheetList.add(rowList);
}
} else {
throw new RuntimeException("文件类型不正确");
}
return sheetList;
}

}