package com.ym.web;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import com.ym.dao.SystemLogMapper;
import com.ym.data.JsonReturn;
import com.ym.po.SystemLog;
import com.ym.util.LayuiResponseData;
import com.ym.util.Tools;
@Controller
@RequestMapping("/Journal")
public class JournalController {
/***
* 导入Excel数据
* @param userExcel
* @param request
* @param session
* @return
* @throws IOException
* @throws InvalidFormatException
* @throws ParseException
*/
@ResponseBody
@RequestMapping(value="/ImportExcel", produces = "application/json; charset=utf-8")
public String ImportExcel(MultipartFile userExcel,HttpServletRequest request,HttpSession session) throws IOException, InvalidFormatException, ParseException{
JsonReturn jsonReturn = new JsonReturn();
jsonReturn.setState(false);
if(userExcel == null){
jsonReturn.setMsg("未选择上传文件,上传失败!");
JSONObject jsonObject=JSONObject.fromObject(jsonReturn);
return jsonObject.toString();
}
String userExcelFileName = userExcel.getOriginalFilename();
if(!userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
jsonReturn.setMsg("文件格式不正确!请使用.xls或.xlsx后缀的文档,导入失败!");
JSONObject jsonObject=JSONObject.fromObject(jsonReturn);
return jsonObject.toString();
}
//获取输入流
InputStream inputStream = userExcel.getInputStream();
//创建读取工作簿
Workbook workbook = WorkbookFactory.create(inputStream);
//获取工作表
Sheet sheet = workbook.getSheetAt(0);
//获取总行
int rows=sheet.getPhysicalNumberOfRows();
int SuccessNumber=0;
if(rows>2){
//获取单元格
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
SystemLog user =new SystemLog();
SystemLog systemLog=null;
try {
String id = row.getCell(0).getStringCellValue();
user.setSystemLogId(Integer.parseInt(id));
systemLog = systemLogMapper.selectByPrimaryKey(Integer.parseInt(id));
} catch (IllegalStateException e){
int id=(int)row.getCell(0).getNumericCellValue();
user.setSystemLogId(id);
systemLog = systemLogMapper.selectByPrimaryKey(id);
}
if(systemLog ==null){
String name = row.getCell(1).getStringCellValue();
user.setUserName(name);
String Substance = row.getCell(2).getStringCellValue();
user.setSubstance(Substance);
String time = row.getCell(3).getStringCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
user.setOperateHour(sdf.parse(time));
//想数据库中添加新对象
systemLogMapper.insert(user);//方法
SuccessNumber++;
}
}
}
jsonReturn.setMsg("成功导入"+SuccessNumber+"条数据");
jsonReturn.setState(true);
JSONObject jsonObject=JSONObject.fromObject(jsonReturn);
inputStream.close();
return jsonObject.toString();
}
}