BOS开发平台中 editUi界面导入Excel并校验表中数据
2.判断文件格式“.xls” 和 “.xlxs”(Excle的两种版本) 是,就返回路径
//选择窗口,返回路径
private static String chooser(Component comp){
String filePath = null;
JFileChooser chooser = new JFileChooser();
chooser.showOpenDialog(comp);
if(chooser.getSelectedFile()==null){
SysUtil.abort();
}
filePath = chooser.getSelectedFile().getPath();
String endName = filePath.substring(filePath.lastIndexOf("."));
if(!".xls".equals(endName) && !".xlsx".equals(endName)){
MsgBox.showWarning(comp,"文件格式错误!");
SysUtil.abort();
}
return filePath;
}
3.我的做法是把每一行作为List,然后多少行就有多少个元素,最后存入一个大List中
/**
* 读取数据
*/
private List<List> ReadExcel(File file) {
// 创建输入流,读取Excel
List<List> Alist = new ArrayList<List>();
try {
InputStream is = new FileInputStream(file.getAbsolutePath());
String endName = file.getAbsolutePath();
XSSFSheet sheet1 = null;
HSSFSheet sheet2 = null;
if (".xlsx".equals(endName.substring(endName.lastIndexOf(".")))) {
XSSFWorkbook wk1 = new XSSFWorkbook(is);
sheet1 = wk1.getSheetAt(0);
for (int i = 1; i < sheet1.getLastRowNum()+1; i++) {
List<Object> list = new ArrayList<Object>();
XSSFRow row = sheet1.getRow(i);
if (row != null) {//从第7列开始,物料信息有请购单生成过来
for (int k = 7; k < sheet1.getRow(0)
.getPhysicalNumberOfCells(); k++) {
XSSFCell cell = row.getCell(k);
if (cell == null || cell.toString().length() == 0) {
//空的单元格用0占位填坑
list.add("0");
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
if (cell.getRichStringCellValue().length() > 0) {
list.add(cell.getRichStringCellValue()
.getString());
}
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
list.add(cell.getDateCellValue());
}else{
list.add(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_FORMULA:
list.add(cell.getNumericCellValue());
break;
}
}
}
Alist.add(list);
System.out.println(Alist);
}
} else {
HSSFWorkbook wk = new HSSFWorkbook(is);
“xls”的获取省略。。。。
金蝶封装。。。调用就行了;
{
IRow row = null;
if(editData.getId()!=null){
// String ids = editData.getId().toString();
// ObjectUuidPK pk = new ObjectUuidPK(BOSUuid.read(ids));
// PurPriceBillInfo info = PurPriceBillFactory.getRemoteInstance().getPurPriceBillInfo(pk);
String path = chooser(this);
File file = new File(path);
List list = ReadExcel(file);
List<SupplierInfo> fiveList = new ArrayList<SupplierInfo>();//提前校验最终最终供应商信息是否正确!
for(int oth = 2;oth<list.size()-1;oth++){
List fourList = (List) list.get(oth);
if(fourList.get(9)==null || fourList.get(9)=="0"){
MsgBox.showConfirm3a(this, "导入失败!模版中未填写最终供应商信息!!!", "模版中:"+(oth+2)+"行-Q列中单元格为空!");
return;
}else{
SupplierInfo infoa = getSupplierInfo(fourList.get(9).toString());
if(infoa!=null){
fiveList.add(infoa);
}else{
MsgBox.showConfirm3a(this, "导入失败!系统中未查找到最终供应商信息!!!","供应商:("+fourList.get(9).toString()+")在系统中未查找到!"+"模版中:"+(oth+2)+"行-Q列中信息不正确!");
return;
}
}
}
if(fiveList.size()==list.size()-4);{
int madeNext = MsgBox.showConfirm2(this,"即将删除原有比价信息!导入模版中...");
if(madeNext==2)
return;
kdtExcelEntry.removeRows();
prmtsupOne.setValue(null);
prmtsupTwo.setValue(null);
prmtsupThree.setValue(null);
}
//获取三个供应商姓名
List supName = (List) list.get(0);
int [] arr = {0,3,6};
for(int i = 0;i<3;i++){
int supNum = i+1;
String msg = "模版供应商"+supNum+":信息未填写!";
String nsg = "系统中未查到供应商"+supNum+"信息";
if(supName.get(arr[i]).toString()=="0"){
MsgBox.showInfo(msg);
}else{
SupplierInfo infos = getSupplierInfo(supName.get(arr[i]).toString());
if(infos!=null){
if(i==0){
prmtsupOne.setValue(infos);
}else if(i==1){
prmtsupTwo.setValue(infos);
}else{
prmtsupThree.setValue(infos);
}
}else{ //信息与系统中不匹配则停止导入
MsgBox.showInfo(nsg+"名称:("+supName.get(arr[i]).toString()+")");
prmtsupOne.setValue(null);
prmtsupTwo.setValue(null);
prmtsupThree.setValue(null);
return;
}
}
}
//从第三行开始,到倒数第二行结束
for(int oth = 2;oth<list.size()-1;oth++){
List threeList = (List) list.get(oth);
SupplierInfo infoz = getSupplierInfo(threeList.get(9).toString());
if(infoz!=null){
row = kdtExcelEntry.addRow();
row.getCell("lastSup").setValue(infoz);
row.getCell("lastPrice").setValue(threeList.get(10));
row.getCell("lastTotal").setValue(threeList.get(11));
row.getCell("lastTime").setValue(threeList.get(12));
row.getCell("lastTxt").setValue(threeList.get(13));
row.getCell("supTypeNum").setValue(threeList.get(14));
}else{ //信息与系统中不匹配则停止导入
prmtsupOne.setValue(null);
prmtsupTwo.setValue(null);
prmtsupThree.setValue(null);
kdtExcelEntry.removeRows();
MsgBox.showInfo("最终定价信息*应商在系统中未查询到!");
return;
}
//如果供应商一有信息就导入
if(prmtsupOne.getValue()!=null){
row.getCell("supOnePrice").setValue(threeList.get(0));
row.getCell("supOneTotal").setValue(threeList.get(1));
row.getCell("supOneTime").setValue(threeList.get(2));
}
//如果供应商二有信息就导入
//
//
List lstTotal = (List) list.get(list.size()-1);
IRow totalRow = kdtExcelEntry.addRow();
if(prmtsupOne.getValue()!=null){
totalRow.getCell("supOneTotal").setValue(lstTotal.get(1));
}
if(prmtsupTwo.getValue()!=null){
totalRow.getCell("supTwoTotal").setValue(lstTotal.get(4));
}
if(prmtsupThree.getValue()!=null){
totalRow.getCell("supThreeTotal").setValue(lstTotal.get(7));
}
totalRow.getCell("lastTotal").setValue(lstTotal.get(11));
MsgBox.showInfo("导入比价成功!");
}else{
MsgBox.showInfo("请填写请购单并生成比价单");