Java 导出 execl

Java导出execl

基于上上一篇的博客mybatis+struts2的架构，现在加多一个功能，把报名人数的信息导出为execl表，这包含两个部分，1.把数据导出到execl表格中;

2.把execl表格下载到本地。

下面先讲讲怎么把数据导出到execl表格

1.apache帮我们写了一个超强的poijar包，具体自己去apache官网下载，jar包包括如下，注意第一个一定要（虽然不是poi开头的）：

2.随便选一个目录，见一个空的execl表格，这里取名为JavaBooks.xls：

3.先来试试效果，建一个主函数类：

package com.yctime.test;

import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
/**
 * A very simple program that writes some data to an Excel file
 * using the Apache POI library.
 * @author www.codejava.net
 *
 */
public class SimpleExcelWriterExample {
 
    public static void main(String[] args) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Java Books");
         
        Object[][] bookData = {
                {"第一行 First Java", " Serria", 79},
                {"效率 Java", "Joshua Bloch", 36},
                {"清空 Code", "Robert martin", 42},
                {"思考 in Java", "Bruce Eckel", 35},
        };
 
        int rowCount = 0;
         
        for (Object[] aBook : bookData) {
            Row row = sheet.createRow(++rowCount);
             
            int columnCount = 0;
             
            for (Object field : aBook) {
                Cell cell = row.createCell(++columnCount);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
             
        }
         
         
        try (FileOutputStream outputStream = new FileOutputStream("/home/wayne/mess_things/JavaBooks.xls")) {
            workbook.write(outputStream);
        }
    }
 
}

4.上面是测试能不能转execl表格，下面再看看用面向对象的写法：

import org.apache.struts2.ServletActionContext;

import com.yctime.pojo.Student;
public class Excel{
	

	public void writeStudent(Student aStudent, Row row) {
	    
		Cell cell = row.createCell(1);
	    cell.setCellValue(aStudent.getId());
	    cell = row.createCell(2);
	    cell.setCellValue(aStudent.getClassname());
	    cell = row.createCell(3);
	    cell.setCellValue(aStudent.getUsername());
	    cell = row.createCell(4);
	    cell.setCellValue(aStudent.getSex());
	    cell = row.createCell(5);
	    cell.setCellValue(aStudent.getTel());
	    cell = row.createCell(6);
	    cell.setCellValue(aStudent.getQq());
	    cell = row.createCell(7);
	    cell.setCellValue(aStudent.getMyflags());
	    cell = row.createCell(8);
	    cell.setCellValue(aStudent.getIntroduce());
	    cell = row.createCell(9);
	    cell.setCellValue(aStudent.getPower());
	    cell = row.createCell(10);
	    cell.setCellValue(aStudent.getIsfile());
	    cell = row.createCell(11);
	    cell.setCellValue(aStudent.getFilename());
	 
	}
	
	public void writeExcel(List<Student> listStudent, String excelFilePath) throws IOException {
	    Workbook workbook = new HSSFWorkbook();
	    Sheet sheet = workbook.createSheet();
	 
	    int rowCount = 0;
	 
	    for (Student aStudent : listStudent) {
	        Row row = sheet.createRow(++rowCount);
	        writeStudent(aStudent, row);
	    }
	 
	    try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
	        workbook.write(outputStream);
	    }
	}
	
}

student实体类：

public class Student implements Serializable {
	
	private static final long serialVersionUID=1L;
	private int id;
	private String username;
	private String sex;
	private String classname;
	private String tel;
	private String power;
	private String myflags;
	private String introduce;
	private String isfile;
	private String filename;
	private String qq;
           setter getter……

到这先停停，来看看下载功能的实现（jar包与上上一篇博客的jar包相同）：

1.写struts.xml配置文件：

<action name="FileDownload" class="com.yctime.web.Action.FileDownload">  
           <result name="success" type="stream">
               <!-- 设置返回类型（格式） -->  
               <param name="contentType">text/plain</param>  
               <!-- 是否内联 contentDisposition默认内联 -->
               <param name="contentDisposition">attachment;fileName="${fileName}"</param>  
               <!-- 与对应的Action的getinputName一致 -->
               <param name="inputName">downloadFile</param>  
               <param name="bufferSize">1024</param>  
           </result>
           <result name="loginfail">adminlogin.jsp</result>  
       </action>  

2.写对应的下载类，并将导出execl表格写在下载前：

package com.yctime.web.Action;  
  
import java.io.IOException;
import java.io.InputStream;  
  
  

import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.struts2.ServletActionContext;  
  
  






import com.opensymphony.xwork2.ActionSupport;  
import com.yctime.pojo.Student;
import com.yctime.service.studentService;
import com.yctime.utils.Excel;
  
//文件下载  
public class FileDownload extends ActionSupport{  
      
    private int number ;  
    HttpServletRequest request=ServletActionContext.getRequest();
    private String fileName;  
  
    public int getNumber() { 
        return number;  
    }  
  
    public void setNumber(int number) {  
        this.number = number;  
    }  
      
    public String getFileName() {  
        return fileName; 
    }  
  
    public void setFileName(String fileName) {  
        this.fileName = fileName;  
    }  
  
  //返回一个输入流，作为一个客户端来说是一个输入流，但对于服务器端是一个 输出流  
    public InputStream getDownloadFile() throws Exception  
    {  
        if(1 == number)  
        {  
           this.fileName = "JavaBooks.xls" ;  
           //获取资源路径  
           return ServletActionContext.getServletContext().getResourceAsStream("upload/JavaBooks.xls") ;  
        }  
        else  
           return null ;  
    }  
      
    @Override  
    public String execute() throws Exception {
    	Object codekey=request.getSession().getAttribute("user");
		if(codekey!=null)
		{
    	studentService stuService=new studentService();
    	//导出execl表格
			String excelFilePath = ServletActionContext.getRequest().getRealPath("/upload/JavaBooks.xls");
			List<Student> studentlist=stuService.getSAllstudent();
			//System.out.println("excelFilePath-->"+excelFilePath);
			Excel excel=new Excel();
			try {
				excel.writeExcel(studentlist, excelFilePath);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        return SUCCESS;  
    }  
		else{
			return "loginfail";
		}
    }
}  

3.看看jsp页面的实现：

<a href="FileDownload.action?number=1"><button class="btn btn-primary form-control">导出Execl</button></a>

4.祝大家成功。

下面是参考的资料：1.http://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi

                                            2.http://blog.youkuaiyun.com/hzc543806053/article/details/7538723

友情提醒：开发java多用google