import java.io.File
import java.io.FileNotFoundException
import java.io.FileReader
import java.io.IOException
import java.util.ArrayList
import jxl.Sheet
import jxl.Workbook
import jxl.read.biff.BiffException
public static ArrayList<String>readTelExcel(String filepath) {
try {
//查询文件是否存在
File file = new File(filepath)
if (file.exists()) {
ArrayList<String>result = new ArrayList<String>()
//声明一个excel文件对象
Workbook wb = Workbook.getWorkbook(file)
//读取每一个工作薄,你也可能用wb.getSheets()得到全部工作薄
Sheet ws = wb.getSheet(0)
if (null != ws) {
//取出所有行
int rows = ws.getRows()
for (int i = 0i <rowsi++) {
//读取第一列中的内容
String cell = ws.getCell(0, i).getContents()
if (null != cell) {
result.add(cell.trim())
}
}
}
return result
}
return null
} catch (BiffException e) {
e.printStackTrace()
return null
} catch (IndexOutOfBoundsException e) {
e.printStackTrace()
return null
} catch (IOException e) {
e.printStackTrace()
return null
}
}
/*** 通过EXCEL模板导入团队信息
*/
@ResponseBody
@RequestMapping("importTemp")
public Map<String,String> importTemp(HttpServletRequest request,HttpSession session,
@RequestParam(value="excel", required=false) MultipartFile file, HttpServletResponse response ) {
Yhb yhb=(Yhb)session.getAttribute(WebConstants.CURRENT_USER)
String zjr = yhb.getYhid()
List<DrTdb> tdxxList = new ArrayList<DrTdb>()
Map<String,String> map = new HashMap<String,String>()
String sfcg = "1"
String bcghs = ""
String bcgyy = ""
HSSFWorkbook workbook = null
try {
workbook = new HSSFWorkbook(file.getInputStream())
HSSFSheet sheet = workbook.getSheetAt(0)
if (sheet != null)
{
int d= sheet.getPhysicalNumberOfRows()
other: for (int i = 7 i < sheet.getPhysicalNumberOfRows() i++)
{
sfcg = "2"
HSSFRow row = sheet.getRow(i)
DrTdb td = new DrTdb()
int rs = 1
//td.setTdbm("T"+DateUtil.CurrentTime("MM-dd"))
td.setZjr(zjr)
for (int j = 0 j < 8 j++)
{
HSSFCell cell = row.getCell(j)
if(j == 0){
if(cell != null){
String cellStr = cell.toString()
if(isDigit(cellStr) != ""){
String NDID = (String) tdglService.queryForObject("tdExcel.queryNd",isDigit(cellStr))
td.setNd(NDID)
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "请填写正确的年度"
break other
}
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "年度不可为空"
break other
}
}else if(j == 1){
if(cell != null){
String cellStr = cell.toString()
String YXID = (String) tdglService.queryForObject("tdExcel.queryIdByYx",cellStr)
String YXDM = (String) tdglService.queryForObject("tdExcel.queryDmByYx",cellStr)
if(YXDM==null || YXDM==""){
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "院系不存在"
break other
}
td.setSsyx(YXID)
//设置团队编码
String currentYear = DateUtil.CurrentTime("yyyy")//当前年份
String tdbm=""
String str=null
str="T"+currentYear+YXDM
String maxID = (String) tdglService.queryForObject("tdgl.queryMaxID", str)//当前团队最大ID
String str_q= maxID.substring(1)
int MAXID=Integer.parseInt(str_q) //将返回的字符串去掉T
tdbm="T"+(MAXID+1)
td.setTdbm(tdbm)
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "院系不存在"
break other
}
}else if(j == 3){
if(cell != null){
String cellStr = cell.toString()
String[] zdjs = cellStr.split(",")
for(int in = 0 in < zdjs.length in ++){
zdjs[in] = zdjs[in].substring(1,zdjs[in].length()-1)
//zdjs[in]=isDigit(zdjs[in])
zdjs[in] = (String) tdglService.queryForObject("tdExcel.queryIdByZgh",zdjs[in])
if("".equals(zdjs[in]) || zdjs[in] == null){
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "教师编号有误"
break other
}
}
td.setZdjs(zdjs)
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "指导教师不可为空"
break other
}
}else if(j == 5){
if(cell != null){
String cellStr = cell.toString()
cellStr = cellStr.substring(1,cellStr.length()-1)
// BigDecimal bg=new BigDecimal(cellStr)
//cellStr=bg.toPlainString()
cellStr = (String) tdglService.queryForObject("tdExcel.queryIdByXh",cellStr)
if("".equals(cellStr) || cellStr == null){
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "团队组长学号有误"
break other
}
else{
String Tdid = (String) tdglService.queryForObject("tdExcel.queryryid",cellStr)
String XM = (String) tdglService.queryForObject("tdExcel.queryXmBy",cellStr)
if( Tdid!= null){
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "团队组长"+XM+"已存在"
break other
}
}
td.setTdzz(cellStr)
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "团队组长不可为空"
break other
}
}else if(j == 7){
if(cell != null){
String cellStr = cell.toString()
String[] tdzy = cellStr.split(",")
for(int k = 0 k < tdzy.length k++){
tdzy[k] = tdzy[k].substring(1,tdzy[k].length()-1)
tdzy[k] = (String) tdglService.queryForObject("tdExcel.queryIdByXh",tdzy[k])
if("".equals(tdzy[k]) || tdzy[k] == null){
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "团队组员学号有误"
break other
}
rs++
}
td.setTdzy(tdzy)
}else{
sfcg = "0"
bcghs = String.valueOf(i+1)
bcgyy = "团队组员不可为空"
break other
}
}
}
td.setRs(String.valueOf(rs))
tdxxList.add(td)
}
}
}catch(IOException e){
e.printStackTrace()
}
if("2".equals(sfcg)){
for(DrTdb tdxx : tdxxList){
tdglService.insert("tdExcel.addTdxx", tdxx)
tdglService.insert("tdExcel.addTdzz", tdxx)
tdglService.insert("tdExcel.addTdzy", tdxx)
tdglService.insert("tdExcel.addZdjs", tdxx)
}
}
map.put("sfcg",sfcg )
map.put("bcghs",bcghs )
map.put("bcgyy",bcgyy )
return map
}
看不懂留言
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)