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" target="_blank" rel="external nofollow" ><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.csdn.net/hzc543806053/article/details/7538723
友情提醒：開發java多用google