<button type="submit" ngf-select="uploadExcel($file)" ngf-pattern="'*'"ngf-accept="'*'" class="btn btn-info btn-sm"> 导入Excel</button>
//导入excel
$scope.uploadExcel = function($file) {
if($file){
Upload.upload({
url:'api/sys-orgs/upload',
data:{file:$file}
}).then(function(rtData){
if (rtData.status == 200) {
alert(rtData.data.message);
}
vm.refresh();
})
}
};
/**
* POST /upload : 上传Excel
*
* @param file Spring封装的文件对象
* @param fileLib 文件库名
* @return 附件对象列表Json
* @throws URISyntaxException if the Location URI syntax is incorrect
* @throws IOException
*/
@PostMapping("/sys-orgs/upload")
@Timed
public Map<String, Object> upload(@RequestParam("file") MultipartFile[] fileArray) throws URISyntaxException, IOException {
Map<String, Object> rtMap = new HashMap<String, Object>();
rtMap = sysOrgService.analysisExcel(fileArray);
return rtMap;
}
/**
* 读取 xls
*
* @author wcy
* @param file
* @return
* @throws IOException
* @throws Exception
*/
@SuppressWarnings("null")
public Map<String, Object> analysisExcel(MultipartFile[] fileArray) throws IOException {
Map<String, Object> mapMsg = new HashMap<String, Object>();
InputStream inputStream = null;
InputStream inputStream2 = null;
try {
MultipartFile file = fileArray[0];
String orginFilename = file.getOriginalFilename();
String suffix = orginFilename.substring(orginFilename.lastIndexOf('.'), orginFilename.length());
inputStream = file.getInputStream();
inputStream2 = file.getInputStream();
String orgSheet = "org";
String userSheet = "user";
List<String> Orgdatas = readExcel(inputStream, suffix, 1, 6, orgSheet).get("org");
List<String> Userdatas = readExcel(inputStream2, suffix, 1, 9, userSheet).get("user");
if (Orgdatas != null && Orgdatas.size() > 0) {
mapMsg = setOrg(Orgdatas);
}
if (Userdatas != null && Userdatas.size() != 0) {
mapMsg = setUser(Userdatas);
// System.out.println(mapMsg);
}
inputStream.close();
inputStream2.close();
} catch (Exception e) {
e.printStackTrace();
mapMsg.put("message", "添加失败!");
} finally {
if (null == inputStream) {
inputStream.close();
}
}
return mapMsg;
}
/**
* 读取EXCEL
*
* @author lhn
* @param filePath
* @throws FileNotFoundException
* @throws FileFormatException
*/
@SuppressWarnings("unlikely-arg-type")
public Map<String, List<String>> readExcel(InputStream is, String suffix, int row, int col, String name) {
// 获取workbook对象
Workbook workbook = null;
Map<String, List<String>> mapList = new HashMap<>();
try {
workbook = getWorkbook(is, suffix);
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
List<String> orgOrPersonDates = new ArrayList<String>();
if (sheet == null) {
return null;
}
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
String orgOrPersonDate = "";
String currentCellValue = "";
if (firstRowIndex != lastRowIndex && lastRowIndex != 0) {
// 读取数据行
for (int rowIndex = row; rowIndex <= lastRowIndex; rowIndex++) {
Row currentRow = sheet.getRow(rowIndex);// 当前行
if (null == currentRow || currentRow.equals("")) {
break;
}
for (int i = 0; i < col; i++) {
if (null == currentRow.getCell(i) || currentRow.getCell(i).equals("")) {
currentCellValue = "";
} else {
currentRow.getCell(i).setCellType(Cell.CELL_TYPE_STRING);// 当做文本来取值
// (取到的是文本，不会把“1”取成“1.0”)
currentCellValue = currentRow.getCell(i).getStringCellValue();
}
orgOrPersonDate += currentCellValue + "<";
}
if (!orgOrPersonDate.equals("")) {
orgOrPersonDates.add(orgOrPersonDate);
}
orgOrPersonDate = "";
mapList.put(sheet.getSheetName(), orgOrPersonDates);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return mapList;
}
/**
* 判断EXCEL版本
*
* @author wcy
* @param in
* @param suffix
* @return
* @throws IOException
*/
private Workbook getWorkbook(InputStream in, String suffix) throws IOException {
Workbook wb = null;
if (suffix.endsWith("xls")) {
wb = new HSSFWorkbook(in);// Excel 2003
} else if (suffix.endsWith("xlsx")) {
wb = new XSSFWorkbook(in);// Excel 2007
}
return wb;
}
/**
* 设置org
*
* @author zgf
* @param dataList
* @return
*/
private Map<String, Object> setOrg(List<String> dataList) {
Map<String, Object> map = new HashMap<String, Object>();
List<SysOrg> orgSuperList = new ArrayList<>();
List<SysOrg> orgSupers = new ArrayList<>();
try {
map.put("message", "导入组织0条");
for (int i = 0; i < dataList.size(); i++) {
List<SysOrg> orgList = null;
String[] str = dataList.get(i).split("<");
String superOrgDesc = null;
if (str.length > 2) {
if (str[1] != null) {
orgList = new ArrayList<SysOrg>();
orgList = sysOrgRepository.findAllByOrgDesc(str[1]);
// 判断上级单位
// List<SysOrg> superList = sysOrgRepository.findAllByOrgDesc(superOrgDesc ==
// null? str[2]:superOrgDesc);
if (orgList.size() == 0 && str[2] == null) {// 没有上级单位 super设置为null
SysOrg sysOrg = new SysOrg();
sysOrg.setOrgAbbName(str[1]);
sysOrg.setOrgAlia(Pinyin.getPinYinHeadChar(str[1]));
sysOrg.setOrgDesc(str[1]);
sysOrg.setOrgName(str[1]);
sysOrg.setOrgSuper(null);
sysOrg.setOrgType(sysOrgTypeRepository.getOne((long) 3));
sysOrg.setDim(sysDimensionRepository.getOne((long) 1));
sysOrg.setGuid(UUIDUtils.get32UUID());
System.out.println(str[4]);
sysOrg.setCode(str[4]);
sysOrg.setSn(Integer.valueOf(str[4]));
sysOrg.setStatus("0");
sysOrgRepository.save(sysOrg);
} else if (orgList.size() == 0 && str[2] != null) {// 有上级单位
if (str[2] != null) {
if (str[2].contains("/")) {
String[] superString = str[2].split("/");
for (int x = 0; x < superString.length; x++) {
superOrgDesc = str[x];
}
}
}
List<SysOrg> superList = sysOrgRepository
.findAllByOrgDesc(superOrgDesc == null ? str[2] : superOrgDesc);
if (str[2] != null) {
if (str[2].contains("/")) {
String[] superString = str[2].split("/");
for (int x = 0; x < superString.length; x++) {
orgSuperList = sysOrgRepository.findAllByOrgDesc(superString[x]);
if (x >= 1 && orgSupers.size() != 0) {
orgSuperList = sysOrgRepository.findAllByOrgNameAndOrgSuperId(
orgSupers.get(0).getOrgDesc(), orgSupers.get(0).getId());
}
if (x + 1 < superString.length && orgSuperList.size() != 0) {
orgSupers = sysOrgRepository.findAllByOrgNameAndOrgSuperId(
superString[x + 1], orgSuperList.get(0).getId());
}
}
}
}
SysOrg sysOrg = new SysOrg();
sysOrg.setGuid(UUIDUtils.get32UUID());
sysOrg.setOrgAbbName(str[1]);
sysOrg.setOrgAlia(Pinyin.getPinYinHeadChar(str[1]));
sysOrg.setOrgDesc(str[1]);
sysOrg.setOrgName(str[1]);
sysOrg.setCode(str[4]);
sysOrg.setSn(Integer.valueOf(str[4]));
sysOrg.setOrgSuper(orgSupers.size() == 0 ? superList.get(0) : orgSupers.get(0));
sysOrg.setOrgType(sysOrgTypeRepository.getOne((long) 3));
sysOrg.setDim(sysDimensionRepository.getOne((long) 1));
sysOrg.setStatus("0");
sysOrgRepository.save(sysOrg);
} else {
map.put("message", "导入组织0条");
}
map.put("message", "导入组织成功");
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
