Java导出/导入Excel,简单直白。。。

导出Excel

需要的jar包

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi  导出Excel需要的jar-->
	    <dependency>
	       <groupId>org.apache.poi</groupId>
	       <artifactId>poi</artifactId>
	       <version>3.6</version>
	    </dependency>

先创建一个实体类，用来模拟列表数据。

public class ExcelUser {
    private String name;
    private String age;
    private String gender;
    private String school;
    private String klass;
    private String address;
    
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getAge() {
		return age;
	}
	public void setAge(String age) {
		this.age = age;
	}
	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}
	public String getSchool() {
		return school;
	}
	public void setSchool(String school) {
		this.school = school;
	}
	public String getKlass() {
		return klass;
	}
	public void setKlass(String klass) {
		this.klass = klass;
	}
	
    
}

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.zgr.pack.entity.test.ExcelUser;
import org.zgr.pack.util.ExcelUtil;

@Controller
@RequestMapping(value = "/report")
public class ExportController {

	// 导出报表
	@RequestMapping(value = "/export")
	@ResponseBody
	public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// 获取数据
		List<ExcelUser> list = new ArrayList<ExcelUser>();
		ExcelUser user = new ExcelUser();
		user.setAge("27");
		user.setGender("男");
		user.setKlass("1");
		user.setName("张三");
		user.setSchool("第一中学");
		user.setAddress("01中国江苏省南京市哈哈哈哈哈哈哈哈哈哈哈人民街道爱情海公寓4栋一单元1105室");
		list.add(user);
		ExcelUser user2 = new ExcelUser();
		user2.setAge("37");
		user2.setGender("女");
		user2.setKlass("5");
		user2.setName("李四");
		user2.setSchool("实验中学");
		user2.setAddress("02中国江苏省南京市哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈哈人民街道爱情海公寓4栋一单元1106室");
		list.add(user2);

		// excel标题
		String[] title = { "姓名", "性别", "年龄", "学校", "班级", "地址" };

		// excel文件名
		String fileName = "学生信息表" + System.currentTimeMillis() + ".xls";
		// sheet名
		String sheetName = "学生信息表";
		String[][] content = new String[list.size()][title.length];
		for (int i = 0; i < list.size(); i++) {
			ExcelUser userEntity = list.get(i);
			content[i][0] = userEntity.getName();
			content[i][1] = userEntity.getGender();
			content[i][2] = userEntity.getAge();
			content[i][3] = userEntity.getSchool();
			content[i][4] = userEntity.getKlass();
			content[i][5] = userEntity.getAddress();
		}

		// 创建HSSFWorkbook
		HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content);

		// 响应到客户端
		try {
			fileName = new String(fileName.getBytes(), "ISO8859-1");
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
			OutputStream os = response.getOutputStream();
			wb.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

ExcelUtil工具类

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelUtil {

	  /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //声明列对象
        HSSFCell cell = null;
       
        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        
        //sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50);
        
        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
                // 设置每个单元格宽度 （第一个参数是第几列，这里地址列处于第6列，这里写死（也可以动态的j）给他设置宽点。第二个参数是宽度值）
                sheet.setColumnWidth(5, 20 * 1500);
            }
        }
        return wb;
    }

 下载结果如下：