FXML 控制器 :
@FXML
public void selectExcel(MouseEvent event) {
FileChooser fileChooser = new FileChooser();
fileChooser.setTitle("选择Excel文件");
Stage selectFile = new Stage();
fileChooser.setInitialDirectory(new File(System.getProperty("user.home")));
fileChooser.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("All Excel", "*.xlsx"),
new FileChooser.ExtensionFilter("XLS", "*.xls"), new FileChooser.ExtensionFilter("XLSX", "*.xlsx"));
File file = fileChooser.showOpenDialog(selectFile);
if (file != null) {
try {
bom.initBOM(ExcelUtil.importExcel(Util.getWorkbok(new FileInputStream(file), file)));
session.commit();
session.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Excel工具类 :
package oa.util;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import oa.bean.Materiel;
public class ExcelUtil {
public static List<Materiel> importExcel(Workbook wb){
Materiel materiel;
List<Materiel> BOM = new ArrayList<>();
if (wb instanceof HSSFWorkbook) {
return null;
} else if (wb instanceof XSSFWorkbook) {
XSSFWorkbook xs = (XSSFWorkbook) wb;
for (int s = 0; s < xs.getNumberOfSheets(); s++) {
XSSFSheet sheet = xs.getSheetAt(s);
int lastRowNum = sheet.getLastRowNum();
System.out.println("当前页:" + xs.getSheetName(s));
for (int i = 0; i < lastRowNum; i++) {
System.out.println(lastRowNum);
XSSFRow row = sheet.getRow(i);
materiel = new Materiel();
for (int j = 0; j < row.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);
switch (cell.getColumnIndex()) {
case 0:
System.out.println(Integer.parseInt(cell.getRawValue()));
materiel.setMateriel_id(Integer.parseInt(cell.getRawValue()));
break;
case 1:
materiel.setMateriel_detail(cell.toString());
break;
case 2:
System.out.println();
materiel.setMateriel_package(Float.valueOf(cell.getRawValue()));
break;
case 3:
materiel.setMateriel_supplier(cell.toString());
break;
default:
// TODO 数据格式有误
break;
}
}
materiel.setMateriel_type(sheet.getSheetName());
BOM.add(materiel);
}
}
}
return BOM;
}
}