java excel导出数据

最新推荐文章于 2024-08-30 18:40:43 发布

Mario♔

最新推荐文章于 2024-08-30 18:40:43 发布

阅读量566

点赞数 1

文章标签： java poi excel导出数据

本文链接：https://blog.csdn.net/semial/article/details/53127935

版权

可以通过该链接http://download.csdn.net/detail/semial/9680176进行下载，直接运行Demo测试

1、导入相应的poijar包

poi-3.12-beta1-20150228.jar
poi-ooxml-3.12-beta1-20150228.jar
poi-ooxml-schemas-3.12-beta1-20150228.jar

2、创建实体Model

package om.han.demo;

import java.util.Date;

public class Student {
	private int id;
	private String name;
	private int age;
	private Date birth;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Date getBirth() {
		return birth;
	}

	public void setBirth(Date birth) {
		this.birth = birth;
	}

	public Student() {
	}

	public Student(int id, String name, int age, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
		this.birth = birth;
	}
}

3、创建Selvet，网页下载

package om.han.demo;

import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
 * 通过点击按钮触发调用该方法进行下载
 * @author issuser
 *
 */
public class StuOut extends HttpServlet {

	/**
	 * Constructor of the object.
	 */
	public StuOut() {
		super();
	}

	/**
	 * Destruction of the servlet. <br>
	 */
	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}

	/**
	 * The doGet method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
		/*
		 * response.setContentType("text/html"); PrintWriter out =
		 * response.getWriter(); out.println(
		 * "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
		 * out.println("<HTML>");
		 * out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
		 * out.println("  <BODY>"); out.print("    This is ");
		 * out.print(this.getClass()); out.println(", using the GET method");
		 * out.println("  </BODY>"); out.println("</HTML>"); out.flush();
		 * out.close();
		 */
	}

	/**
	 * The doPost method of the servlet. <br>
	 * 
	 * This method is called when a form has its tag value method equals to
	 * post.
	 * 
	 * @param request
	 *            the request send by the client to the server
	 * @param response
	 *            the response send by the server to the client
	 * @throws ServletException
	 *             if an error occurred
	 * @throws IOException
	 *             if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		HSSFWorkbook wb = null;
		try {
			List<Student> list = new ArrayList<Student>();
			SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

			Student user1 = new Student(1, "张三", 16, df.parse("1997-03-12"));
			Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));
			Student user3 = new Student(3, "王五", 26, df.parse("1985-11-12"));
			list.add(user1);
			list.add(user2);
			list.add(user3);
			// 第一步，创建一个webbook，对应一个Excel文件
			wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("学生表一");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow((int) 0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

			HSSFCell cell = row.createCell(0);
			cell.setCellValue("学号");
			cell.setCellStyle(style);
			cell = row.createCell(1);
			cell.setCellValue("姓名");
			cell.setCellStyle(style);
			cell = row.createCell(2);
			cell.setCellValue("年龄");
			cell.setCellStyle(style);
			cell = row.createCell(3);
			cell.setCellValue("生日");
			cell.setCellStyle(style);

			// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow((int) i + 1);
				Student stu = (Student) list.get(i);
				// 第四步，创建单元格，并设置值
				row.createCell(0).setCellValue(stu.getId());
				row.createCell(1).setCellValue(stu.getName());
				row.createCell(2).setCellValue(stu.getAge());
				cell = row.createCell(3);
				cell.setCellValue(df.format(stu.getBirth()));
			}
			// 第六步，将文件存到指定位置
			// FileOutputStream fout = new FileOutputStream("E:/students.xls");
			// wb.write(fout);
			// fout.close();
			// 第六步调用此方法直接进行下载
			String fileName = "导出Excel.xls";
			fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
			response.reset();
			response.setHeader("Content-Disposition", "attachment;filename="+ fileName);// 指定下载的文件名
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Pragma", "no-cache");
			response.setHeader("Cache-Control", "no-cache");
			response.setDateHeader("Expires", 0);
			OutputStream ouputStream = response.getOutputStream();
			wb.write(ouputStream);
			ouputStream.flush();
			ouputStream.close();
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Initialization of the servlet. <br>
	 * 
	 * @throws ServletException
	 *             if an error occurs
	 */
	public void init() throws ServletException {
		// Put your code here
	}

}

4、Test测试类，本地下载

package om.han.demo;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

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;

/**
 * 指定文件下载路劲，进行下载
 * 
 * @author issuser
 * 
 */
public class Test {

	public static void main(String[] args) throws ParseException {
		HSSFWorkbook wb = null;
		try {
			List<Student> list = new ArrayList<Student>();
			SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

			Student user1 = new Student(1, "张三", 16, df.parse("1997-03-12"));
			Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));
			Student user3 = new Student(3, "王五", 26, df.parse("1985-11-12"));
			list.add(user1);
			list.add(user2);
			list.add(user3);
			// 第一步，创建一个webbook，对应一个Excel文件
			wb = new HSSFWorkbook();
			// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
			HSSFSheet sheet = wb.createSheet("学生表一");
			// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
			HSSFRow row = sheet.createRow((int) 0);
			// 第四步，创建单元格，并设置值表头 设置表头居中
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

			HSSFCell cell = row.createCell(0);
			cell.setCellValue("学号");
			cell.setCellStyle(style);
			cell = row.createCell(1);
			cell.setCellValue("姓名");
			cell.setCellStyle(style);
			cell = row.createCell(2);
			cell.setCellValue("年龄");
			cell.setCellStyle(style);
			cell = row.createCell(3);
			cell.setCellValue("生日");
			cell.setCellStyle(style);

			// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow((int) i + 1);
				Student stu = (Student) list.get(i);
				// 第四步，创建单元格，并设置值
				row.createCell(0).setCellValue(stu.getId());
				row.createCell(1).setCellValue(stu.getName());
				row.createCell(2).setCellValue(stu.getAge());
				cell = row.createCell(3);
				cell.setCellValue(df.format(stu.getBirth()));
			}
			// 第六步，将文件存到指定位置
			FileOutputStream fout = new FileOutputStream("E:/students.xls");
			wb.write(fout);
			fout.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}