Java后台代码实现POI文件的导入导出

前言

工作中常常会用到POI的导入导出功能,今天为大家详细介绍一下平时用到的最多的导入Excel表格数据和导出数据为Excel表格的相关代码操作!本案例是SpringBoot项目,废话不多说上代码!

1.Controller层代码

//相关导包
import java.io.File;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
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 com.baidu.entity.Student;
import com.baidu.mapper.StudentMapper;
import com.baidu.utils.ExcelUtils;
import com.baidu.utils.FileUtils;

@Controller
public class StudentController {
   

	@Autowired
	private StudentMapper studentMapper;

	//导出数据到Excel表格内
	@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletResponse response,Student stu){
   
		//查询要导出数据集合
		List<Student> list = studentMapper.getExcelList();
		String [] columnNames= {
   "编号","姓名","性别","生日"};
		String [] columns= {
   "sid","sname","sex","birthday"};
		ExcelUtils.exportExcel(response, list, columnNames, columns, "工作簿", "汇总");
	}
	
	//将Excel表格信息导入到数据库
	@RequestMapping("/uploadFile")
	@ResponseBody
	public boolean uploadFile(HttpServletRequest request,Student stu,MultipartFile myfile,HttpServletResponse response) {
   
		try {
   
			//调用工具类FileUtils上传,前台传来的文件形参为myfile
			String newFileName = FileUtils.upload(myfile, request);
			String realPath = request.getSession().getServletContext().getRealPath("file");
			//将Excel文件的内容放入到String类型的双层数组中
			String[][] strings = ExcelUtils.readexcell(realPath+File.separator+newFileName, 1);
			//循环数组,依次拿取数组内的数据放入到实体类的对象里,以便于添加数据到数据库的表单内
			for (int i = 0; i < strings.length; i++) {
   
				String [] stuData=strings[i];
				Student user=new Student();
				//从索引为1的开始拿数据,因为Excel表格内第一行是主键id,添加数据不需要id,不过如果在这写了也无所谓,没影响,添加的sql注意就好了.
				user.setSname(stuData[1]);
				user.setSex(stuData[2]);
				user.setBirthday(stuData[3]);
				studentMapper.addStu(user);
			}
			return true;
		} catch (Exception e) {
   
			// TODO: handle exception
			return false;
		}
	}	
}

2.用到的几个工具类

总共用到了三个工具类,工具类里面的方法可能不是都有用,不过我记不清楚用的哪几个了,就全部复制过来了,有兴趣可以仔细研究下,不过工具类,粘贴拿过去用就好了,多几个方法也问题不大.

第一个:ExcelUtils

//工具类所在包名
package com.XXX.utils;
//导包
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook