struts2实现从MYSQL数据库表中导出EXCEL表

1.获取项目路径;

  ActionContext ac = ActionContext.getContext();
  ServletContext sc = (ServletContext) ac.get(ServletActionContext.SERVLET_CONTEXT);
  String path = sc.getRealPath("/");

 

2.引入jxl.jar;

 

3.调用服务层获取数据(使用list存放):

         List<Advise> advises = new ArrayList<Advise>();

         advises = this.adviseService.getAdvises();
4.调用导出EXCEL表方法:

         export(advises, path);

 

 

附:

model:

 

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name="t_advise")
public class Advise implements Serializable {

 private Integer id;
 private String adviser;
 private String content;
 private Date feedBackTime = new Date();
 private Searcher searcher;
 private Integer visible = 1;

 public Advise() {
 }

 public Advise(Integer id, String adviser, String content, Date feedBackTime, Searcher searcher, Integer visible) {
  this.id = id;
  this.adviser = adviser;
  this.content = content;
  this.feedBackTime = feedBackTime;
  this.searcher = searcher;
  this.visible = visible;
 }

 @Id
 @GeneratedValue
 public Integer getId() {
  return id;
 }

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

 public String getAdviser() {
  return adviser;
 }

 public void setAdviser(String adviser) {
  this.adviser = adviser;
 }

 public String getContent() {
  return content;
 }

 public void setContent(String content) {
  this.content = content;
 }

 public Date getFeedBackTime() {
  return feedBackTime;
 }

 public void setFeedBackTime(Date feedBackTime) {
  this.feedBackTime = feedBackTime;
 }

 @ManyToOne
 @JoinColumn(name="sid")
 public Searcher getSearcher() {
  return searcher;
 }

 public void setSearcher(Searcher searcher) {
  this.searcher = searcher;
 }

 public Integer getVisible() {
  return visible;
 }

 public void setVisible(Integer visible) {
  this.visible = visible;
 }

 @Override
 public String toString() {
  return "Advise [adviser=" + adviser + ", content=" + content + ", feedBackTime=" + feedBackTime + ", id=" + id
    + ", searcher=" + searcher + ", visible=" + visible + "]";
 }

}

 

 

导出EXCEL表方法:

 

private void export(List<Advise> advises, String path) {
  try {
   WritableWorkbook wwb = Workbook.createWorkbook(new File(path + "/advise.xls"));
   System.out.println("/advise.xls");
   WritableSheet sheet = wwb.createSheet("第一页", 0);
   sheet.setColumnView(2, 80);
   sheet.setColumnView(3, 20);
   sheet.setColumnView(4, 15);
   sheet.setRowView(0,500);

   WritableFont font1 = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD);
   WritableCellFormat format1 = new WritableCellFormat(font1);
   format1.setAlignment(jxl.format.Alignment.CENTRE);
   format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

   jxl.write.Number number;
   Label label0, label1, label2, label3, label4;

   int i = 0, j = 1;

   label0 = new Label(0, 0, "序号", format1);
   label1 = new Label(1, 0, "建议者", format1);
   label2 = new Label(2, 0, "建议内容", format1);
   label3 = new Label(3, 0, "提交时间", format1);
   label4 = new Label(4, 0, "针对产品", format1);

   sheet.addCell(label0);
   sheet.addCell(label1);
   sheet.addCell(label2);
   sheet.addCell(label3);
   sheet.addCell(label4);
   
   
   font1 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD);
   format1 = new WritableCellFormat(font1);
   format1.setAlignment(jxl.format.Alignment.CENTRE);
   format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

   for (Iterator<Advise> iterator = advises.iterator(); iterator.hasNext();) {
    Advise advise = iterator.next();

    number = new jxl.write.Number(i, j, advise.getId(),format1);
    label1 = new Label(++i, j, advise.getAdviser(),format1);
    label2 = new Label(++i, j, advise.getContent(),format1);
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    label3 = new Label(++i, j, sdf.format(advise.getFeedBackTime()),format1);
    label4 = new Label(++i, j, advise.getSearcher().getName(),format1);

    sheet.addCell(number);
    sheet.addCell(label1);
    sheet.addCell(label2);
    sheet.addCell(label3);
    sheet.addCell(label4);

    i = 0;
    j++;
   }

   // 写入数据并关闭文件
   wwb.write();
   wwb.close();

  } catch (Exception e) {
   e.printStackTrace();
  }
 }