业务描述:在界面点击导入,弹出选择框,然后选择excel文件,将excel文件中的数据导入到系统中。本次将如何读取并导入的代码分享下:
这段代码是选取excel数据。
try{
//打开弹窗
int returnVal = chooser.showOpenDialog(this);
//判断是否点击确定
if(returnVal == 1){
return;
}
//获取文件
String directory = chooser.getSelectedFile().getPath();
String file = chooser.getSelectedFile().getName();
//判断是否选择了文件
if ((StringUtil.isEmpty(directory))
|| (StringUtil.isEmpty(file))) {
MessageDialog.showErrorDlg(this, "提示",
"未选择要导入的单据");
return;
}
//判断文件格式(要求07excel)
if(!file.endsWith(".xlsx")){
MessageDialog.showErrorDlg(this, "提示", "选择的文件格式错误");
return;
}
//获取文件名
String filename = directory;
//读取文件
List
> rowlist = ReadExcel(filename);
if(rowlist == null || rowlist.size()
MessageDialog.showErrorDlg(this, "提示", "数据为空");
return;
}
//导入数据
List object = importSaleOrder(rowlist);
if(object!=null && object.size()>0){
MessageDialog.showHintDlg(this, "提示", "导入成功");
}
}catch(Exception e){
MessageDialog.showErrorDlg(this, "提示", "导入失败");
return;
}
下面这个方法主要是先统一将excel里的数据先读取出来,存到List集合里,避免用一次读取一次 。
/**
* 读取excel
* @author zhuaizhuaihenguai
* @date 2018年5月8日 11:23:47
* @param filename
* @return excel
*/
private List
> ReadExcel(String filename) {
File file = new File(filename);
Workbook wb = null;
//再次判断
if (filename.endsWith(".xlsx")) {
try {
wb = new XSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
// 读取第一个sheet页表格内容
Sheet sheet = wb.getSheetAt(0);
Object value = null;
Row row = null;
Cell cell = null;
//先读取行
List
> rowlist = new ArrayList
>();
//excel前两行为标题和字段名称,所以过滤掉
for (int i = sheet.getFirstRowNum()+2; i
row = sheet.getRow(i);
if (row == null) {
continue;
}
//再读列
List cellList = new ArrayList(56);
//这里的57为数据列数,不能使用row.getLastCellNum()
//如果最后的数据为null,后续使用会空指针
for (int j = row.getFirstCellNum(); j
cell = row.getCell(j);
if (cell == null) {
cellList.add(j, null);
continue;
}
// 格式化数字
DecimalFormat nf = new DecimalFormat("0.00000000");
// 格式化日期字符串
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:// 字符串
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:// 数字——Number type
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else if ("General".equals(
cell.getCellStyle().getDataFormatString())) {
value = nf.format(cell.getNumericCellValue());
} else {
value = sdf.format(HSSFDateUtil.getJavaDate(
cell.getNumericCellValue()));
}
break;
//boolean
case XSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK://空白
value = "";
break;
default:// default type
value = cell.toString();
}
if (value == null || "".equals(value)) {
cellList.add(j, null);
continue;
}
cellList.add(j, value);
}
rowlist.add(cellList);
}
return rowlist;
}
}
这里实现数据分组并导入,按单号(流水号)进行分组,将相同单号的数据合并并保存。
/**
* 数据导入
* @authorzhuaizhuaihenguai
* @date 2018年4月2日 11:36:21
* @param rowlist excel数据
*/
private List importSaleOrder(List
> rowlist)
throws Exception{
List object = new ArrayList();
//客户提供的excel是名称或编码,我们这里要转换成对应的ID进行保存
//将需要查询的数据放到数组里进行统一处理
String[] cBizType = new String[rowlist.size()];
String[] cCalbody = new String[rowlist.size()];
String[] cCustomer = new String[rowlist.size()];
String[] cDep = new String[rowlist.size()];
String[] cReceiptcorp = new String[rowlist.size()];
String[] cReceiptcustomer = new String[rowlist.size()];
String[] cSalecorp = new String[rowlist.size()];
String[] cAdvisecalbody = new String[rowlist.size()];
String[] cInventory = new String[rowlist.size()];
String[] cCurrency = new String[rowlist.size()];
String[] cCurrencyb = new String[rowlist.size()];
List map_list;
Stringcode;//流水号
Map
>> map =
new HashMap
>>();
//将需要查询的数据循环放到数组里
for(int i= 0;i
Arrays.binarySearch(cBizType,(String) rowlist.get(i).get(0));
cBizType[i] = (String) rowlist.get(i).get(0);//类型
cCalbody[i] = (String) rowlist.get(i).get(1);
cCustomer[i] = (String) rowlist.get(i).get(2);//客户
cDep[i] = (String) rowlist.get(i).get(3);//事业部
cReceiptcorp[i] = (String) rowlist.get(i).get(4);//单位
cReceiptcustomer[i] = (String) rowlist.get(i).get(5);
cSalecorp[i] = (String) rowlist.get(i).get(6);
cCurrency[i] = (String) rowlist.get(i).get(10);//币种
cAdvisecalbody[i] = (String) rowlist.get(i).get(16);
cInventory[i] = (String) rowlist.get(i).get(17);//物品
cCurrencyb[i] = (String) rowlist.get(i).get(23);//币种
code= (String) rowlist.get(i).get(15);//流水号
map_list = map.get(code);
// 根据流水号进行分组
if(map_list != null && map_list.size()>0){
map_list.add(rowlist.get(i));
}else{
List
> li = new ArrayList
>();
li.add(rowlist.get(i));
map.put(code, li);
}
}
IQuerySOData iQuerySOData;
//需要将查询出的数据放在map集合里
Map cBizTypeMap = new HashMap();
Map cCalbodyMap = new HashMap();
Map cCustomerMap = new HashMap();
Map cDepMap = new HashMap();
Map cReceiptcorpMap = new HashMap();
Map cReceiptcustomerMap = new HashMap();
Map cSalecorpMap = new HashMap();
Map cAdvisecalbodyMap = new HashMap();
Map cInventoryMap = new HashMap();
Map cCurrencyMap = new HashMap();
Map cCurrencyMapb = new HashMap();
Map cRateMap = new HashMap();
try {
//调用接口
iQuerySOData = (IQuerySOData)Locator.getInstance().
lookup(IQuerySOData.class);
cBizTypeMap = iQuerySOData.querySaleData(
getSql(cBizType,1),1);
cCalbodyMap = iQuerySOData.querySaleData(g
etSql(cCalbody,2),1);
cCustomerMap = iQuerySOData.querySaleData(
getSql(cCustomer,3),2);
cDepMap = iQuerySOData.querySaleData0(
getSql(cDep,4),1);
cReceiptcorpMap = iQuerySOData.querySaleData(
getSql(cReceiptcorp,3),2);
cReceiptcustomerMap = iQuerySOData.querySaleData(
getSql(cReceiptcustomer,3),2);
cSalecorpMap = iQuerySOData.querySaleData(
getSql(cSalecorp,5),1);
cAdvisecalbodyMap = iQuerySOData.querySaleData(
getSql(cAdvisecalbody,2),1);
cInventoryMap = iQuerySOData.querySaleData(
getSql(cInventory,6),3);
cCurrencyMap = iQuerySOData.querySaleData(
getSql(cCurrency,7),1);
if(cCurrencyb.length>0){
cCurrencyMapb = iQuerySOData.querySaleData(
getSql(cCurrencyb,7),1);
cRateMap = iQuerySOData.querySaleData(
getSql(cCurrencyb,8),1);
}
} catch (Exception e1) {
e1.printStackTrace();
}
//遍历分组合并
Order order;
OrderHhead;
OrderBbody;
for (String key : map.keySet()) {
order= newOrder();
head= newOrderH();
head.setCbiztype(cBizTypeMap.get(
(String)map.get(key).get(0).get(0))[0]);
head.setCcalbodyid(cCalbodyMap.get(
(String) map.get(key).get(0).get(1))[0]);
......//各种set,代码就不贴上来了
order.setOrder(head);
List
> mapList = map.get(key);
List orderblist = new ArrayList();
for(List list:mapList){
body= newOrderB();
body.setNoriginalcurprice(
new UFDouble((String) list.get(20)));
body.setStatus(2);
......//同样各种set,代码就不贴上来了
orderblist.add(body);
}
order.setChildren(orderblist.toArray(newOrderB[]{}));
//调用保存接口,这里进行各种数据校验
//将非法数据过滤掉,比如流水号重复等问题
IplatFormEntryiIplat= (IplatFormEntry) Locator.
getInstance().lookup(IplatFormEntry.class.getName());
Object retObj =iIplat.processAction(order);
object.add(retObj);
}
return object;
}
客户提供的excel是名称或编码,我们这里要转换成对应的ID进行保存,这里的sql查询也是写的接口,考虑到比较简单就不放出来了。
领取专属 10元无门槛券
私享最新 技术干货