Excel数据的导入与导出
加入jar包
<!-- Excel导出的jar包 -->
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
一、导出Excel到本地
Ajax发送请求到Controller
//导出Excel
function outExcel(){
$.ajax({
url:'downAmountMedicine.act',
type:'Post',
data:{startTime:$("#startTime").val(),endTime:$("#endTime").val(),medName:$("#medName").val()},
dataType:'JSON',
success:function(msg){
if(msg.id==1){
window.location.href = "downLoadFile.act?savePath=" + msg.datas.savePath+"&nowDate="+new Date();
}
},
error:function(msg){
layer.alert("请联系管理员!");
}
})
//导出Excel到本地磁盘
@PostMapping("downAmountMedicine.act")
public JsonMsg downAmountMedicine(HttpServletResponse response,String startTime,String endTime,String medName) throws IOException {
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//换行配置
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
//建立新的sheet对象(excel的表单)
HSSFSheet sheet=wb.createSheet("药房库存表");
//在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1=sheet.createRow(0);
//创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFCell cell=row1.createCell(0);
//设置单元格内容
cell.setCellValue("药房库存表");
//强制换行
cell.setCellStyle(cellStyle);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,4));
//在sheet里创建第二行
HSSFRow row2=sheet.createRow(1);
//创建单元格并设置单元格内容
row2.createCell(0).setCellValue("药品名称");
row2.createCell(1).setCellValue("有效期");
row2.createCell(2).setCellValue("批次");
row2.createCell(3).setCellValue("数量");
row2.createCell(4).setCellValue("价格");
//在sheet里创建第三行
//获取需要写入的数据
List<MedicineParamacyInfo> medicineInfo= medicineInventoryCheckService.selectMedicineParamacy(null,null, medName, startTime, endTime);
//for循环一行行写入
for(int i=0;i<medicineInfo.size();i++) {
HSSFRow row=sheet.createRow(2+i);
row.createCell(0).setCellValue(medicineInfo.get(i).getMedName());
row.createCell(1).setCellValue(medicineInfo.get(i).getPhaEffdate());
row.createCell(2).setCellValue(medicineInfo.get(i).getPhaBatch());
row.createCell(3).setCellValue(medicineInfo.get(i).getPhaAmount()+" "+medicineInfo.get(i).getMedRoomUnit());
row.createCell(4).setCellValue(medicineInfo.get(i).getPrice()+"元");
}
//输出Excel文件
String savePath = "D://药房库存表.xls";//输出路径
OutputStream output=new FileOutputStream(savePath);
response.reset();
response.setHeader("Content-disposition", "attachment; filename=details.xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
//Json返回值
JsonMsg msg = new JsonMsg();
msg.setId(1);
msg.getDatas().put("savePath", savePath);
return msg;
}
//调取本地服务器路径,浏览器下载文件
@GetMapping("storeIoStatisticDownLoadFile.act")
public ResponseEntity<byte[]> downLoadFile(String savePath) throws IOException{
HttpHeaders headers = new HttpHeaders();
File file = new File(savePath);
String[] name = savePath.split("//");//路径切割
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", new String(name[name.length-1].getBytes("UTF-8"), "iso-8859-1"));
return new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(file), headers, HttpStatus.CREATED);
}
二、导入Excel到数据库中
/**
* Excel导入
*
* @param filePath
* @return
* @throws IOException
*/
public Long importExcel(String filePath) throws Exception {
// 查询高校信息
List<CollegesInfo> collegeList = collegesInfoMapper.selectAllColleges();
// 参训参数信息
List<ParamInfo> paramInfo = paramInfoMapper.selectAllParam();
System.out.println("readAndCheckInsuranceData");
Long result = 0L;
InputStream stream = null;
// 读EXCEL文件内容
// 读取文件流并转化为workbook对象
stream = new FileInputStream(filePath);
Workbook workbook = null;
if (filePath.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(stream);
} else if (filePath.endsWith(".xls") || filePath.endsWith(".et")) {
workbook = new HSSFWorkbook(stream);
}
stream.close();
// 获取第一个sheet表,也可使用sheet表名获取
Sheet sheet = workbook.getSheetAt(0);
// 获取行
Iterator<Row> rows = sheet.rowIterator();
Row row;
Cell cell;
// 获取表头
ArrayList<String> userTitleList = new ArrayList<String>();
ArrayList<String> ResumeTitleList = new ArrayList<String>();
ArrayList<String> educationalBackgroundTitleList = new ArrayList<String>();
ArrayList<String> socialExperienceTitleList = new ArrayList<String>();
// 存取对应数据
UserInfo userInfo = new UserInfo();
ResumeInfo resumeInfo = new ResumeInfo();
EducationalBackground educationalBackground = new EducationalBackground();
SocialExperience socialExperience = new SocialExperience();
System.out.println("开始读取");
// 边读取边核对
while (rows.hasNext()) {
// 获取每一行
row = rows.next();
// 获取用户表头
Long userNumber = 0L;
if (PoiUtil.getCellValue(row.getCell(0)).equals("用户个人信息")) {
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();
String cellValue = PoiUtil.getCellValue(cell);
userTitleList.add(cellValue);
}
continue;
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("USER_INFO")) {
for (int i = 0; i < userTitleList.size(); i++) {
cell = row.getCell(i);
String cellValue = PoiUtil.getCellValue(cell);
PoiUtil.setUserInfoCheckData(cellValue.trim(), userTitleList.get(i), userInfo);
}
userInfo.setUserState(1L);
// 用户数据插入完毕
result = userInfoMapper.insertExcelUser(userInfo);
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("简历信息")) {
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();
String cellValue = PoiUtil.getCellValue(cell);
ResumeTitleList.add(cellValue);
}
continue;
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("RESUME_INFO")) {
for (int i = 0; i < ResumeTitleList.size(); i++) {
cell = row.getCell(i);
String cellValue = PoiUtil.getCellValue(cell);
PoiUtil.setResumeCheckData(cellValue.trim(), ResumeTitleList.get(i), resumeInfo, collegeList,
paramInfo);
}
resumeInfo.setUserId(userInfo.getUserId());
resumeInfo.setResumeState(1L);
// 简历数据插入完毕
result = resumeInfoMapper.insertExcelResumeInfo(resumeInfo);
System.out.println(resumeInfo);
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("教育背景")) {
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();
String cellValue = PoiUtil.getCellValue(cell);
educationalBackgroundTitleList.add(cellValue);
}
continue;
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("EDUCATIONAL_BACKGROUND")) {
for (int i = 0; i < educationalBackgroundTitleList.size(); i++) {
cell = row.getCell(i);
String cellValue = PoiUtil.getCellValue(cell);
PoiUtil.setEducationalBackgroundCheckData(cellValue.trim(), educationalBackgroundTitleList.get(i),
educationalBackground);
}
// 教育经历数据插入完毕
educationalBackground.setResumeId(resumeInfo.getResumeId());
result = educationalBackgroundMapper.insertExcelEducationalBackground(educationalBackground);
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("社会经历")) {
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();
String cellValue = PoiUtil.getCellValue(cell);
socialExperienceTitleList.add(cellValue);
}
continue;
}
if (PoiUtil.getCellValue(row.getCell(0)).equals("SOCIAL_EXPERIENCE")) {
for (int i = 0; i < socialExperienceTitleList.size(); i++) {
cell = row.getCell(i);
String cellValue = PoiUtil.getCellValue(cell);
PoiUtil.setSocialExperienceCheckData(cellValue.trim(), socialExperienceTitleList.get(i),
socialExperience);
}
// 教育经历数据插入完毕
socialExperience.setResumeId(resumeInfo.getResumeId());
result = socialExperienceMapper.insertExcelsocialExperience(socialExperience);
}
}
stream.close();
if (result <= 0) {
throw new RuntimeException("发生异常");
}
return result;
}
导入判断
public class PoiUtil {
public static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case NUMERIC: // 数字
{
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 判断是否为日期类型
Date date = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
cellValue = formater.format(date);
} else {
// 有些数字过大,直接输出使用的是科学计数法: 2.67458622E8 要进行处理
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
// cellValue = cell.getNumericCellValue() + "";
}
break;
}
case STRING: // 字符串
{
cellValue = cell.getStringCellValue();
break;
}
case BOOLEAN: // Boolean
{
cellValue = cell.getBooleanCellValue() + "";
break;
}
case FORMULA: // 公式
{
try {
// 如果公式结果为字符串
cellValue = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否为日期类型
Date date = cell.getDateCellValue();
// DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm");
DateFormat formater = new SimpleDateFormat("yyyy/MM/dd");
cellValue = formater.format(date);
} else {
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
.createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
// 有些数字过大,直接输出使用的是科学计数法: 如2.67458622E8 要进行处理
DecimalFormat df = new DecimalFormat("####.####");
cellValue = df.format(cell.getNumericCellValue());
// cellValue = cell.getNumericCellValue() + "";
}
}
break;
}
case BLANK: // 空值
cellValue = "";
break;
case ERROR: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
/**
* 用户
*
* @param value
* @param title
* @param target
*/
public static void setUserInfoCheckData(String value, String title, UserInfo target) {
switch (title) {
case "用户账号": {
target.setUserAcc(value);
break;
}
case "用户密码": {
String md5DigestAsHex = DigestUtils.md5DigestAsHex(value.getBytes());
target.setUserPwd(md5DigestAsHex);
break;
}
case "用户昵称": {
target.setUserNickname(value);
break;
}
case "用户真实姓名": {
target.setUserTruename(value);
break;
}
case "性别": {
Long sex = 0L;
if (value.equals("男")) {
sex = 1L;
} else {
sex = 2L;
}
target.setUserSex(sex);
break;
}
case "年龄": {
try {
target.setUserAge(Long.parseLong(value));
} catch (Exception e) {
System.out.println("年龄数据错误");
target.setUserAge(null);
}
break;
}
case "手机号": {
try {
target.setUserPhone(value);
} catch (Exception e) {
System.out.println("手机数据错误");
target.setUserAge(null);
}
break;
}
case "通讯地址": {
try {
target.setUserAddress(value);
} catch (Exception e) {
System.out.println("通讯地址数据错误");
target.setUserAge(null);
}
break;
}
case "就业情况": {
try {
Long userEmploymentStatus = 0L;
if (value.equals("是")) {
userEmploymentStatus = 1L;
} else {
userEmploymentStatus = 2L;
}
target.setUserEmploymentStatus(userEmploymentStatus);
} catch (Exception e) {
System.out.println("就业情况数据错误");
target.setUserAge(null);
}
break;
}
case "所在公司": {
try {
target.setUserEmploymentCompany(value);
} catch (Exception e) {
System.out.println("所在公司数据错误");
target.setUserAge(null);
}
break;
}
default: {
System.out.println(title);
break;
}
}
}
/**
* 简历
*
* @param value
* @param title
* @param target
*/
public static void setResumeCheckData(String value, String title, ResumeInfo target, List<CollegesInfo> collegeList,
List<ParamInfo> paramInfo) {
switch (title) {
case "姓名": {
target.setResumeName(value);
break;
}
case "性别": {
try {
Long sex = 0L;
for (int i = 0; i < paramInfo.size(); i++) {
if (paramInfo.get(i).getParamCode().equals("SEX") && paramInfo.get(i).getKeyName().equals(value)) {
sex = Long.parseLong(paramInfo.get(i).getKeyValue());
break;
}
}
target.setResumeSex(sex);
} catch (Exception e) {
System.out.println("性别数据错误");
target.setResumeSex(null);
}
break;
}
case "出生年份": {
target.setResumeBirth(value);
break;
}
case "政治面貌": {
try {
Long politicsStatus = 0L;
for (int i = 0; i < paramInfo.size(); i++) {
if (paramInfo.get(i).getParamCode().equals("POLITICS_STATUS")
&& paramInfo.get(i).getKeyName().equals(value)) {
politicsStatus = Long.parseLong(paramInfo.get(i).getKeyValue());
break;
}
}
target.setPoliticsStatus(politicsStatus);
} catch (Exception e) {
System.out.println("政治面貌数据错误");
target.setPoliticsStatus(null);
}
break;
}
case "专业": {
try {
target.setProfessionName(value);
} catch (Exception e) {
System.out.println("专业数据错误");
target.setProfessionName(null);
}
break;
}
case "住址": {
try {
target.setPoliticsAdress(value);
} catch (Exception e) {
System.out.println("住址数据错误");
target.setPoliticsAdress(null);
}
break;
}
case "最高学历": {
try {
Long maxEducation = 0L;
System.out.println(value);
for (int i = 0; i < paramInfo.size(); i++) {
if (paramInfo.get(i).getParamCode().equals("DEGREE")
&& paramInfo.get(i).getKeyName().equals(value)) {
maxEducation = Long.parseLong(paramInfo.get(i).getKeyValue());
break;
}
}
target.setMaxEducation(maxEducation);
} catch (Exception e) {
System.out.println("最高学历数据错误");
target.setMaxEducation(null);
}
break;
}
case "工作经验/年": {
try {
target.setWorkExperience(Long.parseLong(value));
} catch (Exception e) {
System.out.println("工作经验/年数据错误");
target.setWorkExperience(null);
}
break;
}
case "手机号": {
try {
target.setPoliticsPhone(value);
} catch (Exception e) {
System.out.println("手机号数据错误");
target.setPoliticsPhone(null);
}
break;
}
case "简历隐私状态": {
try {
Long privacyState = null;
if (value.equals("公开")) {
privacyState = 1L;
} else if (value.equals("仅投递方")) {
privacyState = 2L;
}
target.setPrivacyState(privacyState);
} catch (Exception e) {
System.out.println("简历隐私状态数据错误");
target.setPrivacyState(null);
}
break;
}
case "技能证书": {
try {
target.setSkillCertificate(value);
} catch (Exception e) {
System.out.println("技能证书数据错误");
target.setSkillCertificate(null);
}
break;
}
case "自我评价": {
try {
target.setSeleAssessment(value);
} catch (Exception e) {
System.out.println("自我评价数据错误");
target.setSeleAssessment(null);
}
break;
}
case "高校名称": {
try {
Long collegesId = null;
for (int i = 0; i < collegeList.size(); i++) {
if (collegeList.get(i).getCollegesName().equals(value)) {
collegesId = collegeList.get(i).getCollegesId();
break;
}
}
target.setCollegesId(collegesId);
} catch (Exception e) {
System.out.println("高校名称数据错误");
target.setCollegesId(null);
}
break;
}
default: {
System.out.println(title);
break;
}
}
}
/**
* 教育背景
*
* @param value
* @param title
* @param target
*/
public static void setEducationalBackgroundCheckData(String value, String title, EducationalBackground target) {
switch (title) {
case "教育背景时间": {
target.setEducationalBackgroundTime(value);
break;
}
case "学校名称": {
target.setSchoolName(value);
break;
}
case "专业名称": {
target.setProfessionName(value);
break;
}
default: {
System.out.println(title);
break;
}
}
}
/**
* 教育背景
*
* @param value
* @param title
* @param target
*/
public static void setSocialExperienceCheckData(String value, String title, SocialExperience target) {
switch (title) {
case "经历时间": {
target.setSocialExperienceTime(value);
break;
}
case "公司名称": {
target.setCompanyName(value);
break;
}
case "工作内容": {
target.setJobContent(value);
break;
}
default: {
System.out.println(title);
break;
}
}
}
}