jxl导出excel表主要代码

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.lang.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.fb.core.base.BaseAction;
import com.fb.core.constants.DwzConstants;
import com.fb.core.query.SimpleConditions;
import com.fb.core.util.SpringContextUtil;
import com.fb.core.widget.DwzAjaxResponse;
import com.fb.ds.competition.helper.CompetitionTeamHelper;
import com.fb.ds.competition.model.CompetitionEntry;
import com.fb.ds.competition.model.CompetitionTeam;
import com.fb.ds.competition.model.SumSchool;
import com.fb.ds.competition.model.TeamMenber;
import com.fb.ds.competition.model.TotalSchool;
import com.fb.ds.competition.service.CompetitionTeamService;
import com.fb.sys.user.model.Admin;
import com.fb.sys.user.model.User;
import com.fb.sys.user.service.AdminService;

/**
 * 
 * <p>
 * Title: CompetitionTeamAction
 * </p>
 * <p>
 * Description: Action
 * </p>
 * 
 * @author zhangmin
 * @date 2015-06-03 03:04:39
 */
@Controller
public class CompetitionTeamAction extends BaseAction {


/**
* CompetitionTeam服务
*/
@Autowired
private CompetitionTeamService competitionTeamService;


// @Autowired
// private CompetitionEntryService competitionEntryService;


@Autowired
private AdminService adminService;

@RequestMapping("/ds/competition/competitionTeamAction/exportExcel.do")
@RequiresPermissions("TEAM-READ")
@ResponseBody
public void exportExcel(HttpServletRequest request,
HttpServletResponse response) throws IOException {


FileOutputStream fos;
String[] title = { "学校", "报名成功人数", "报名成功团队", "团队名称", "团队成员", "团队负责人",
"指导老师" };

WritableWorkbook book = null;
InputStream in = null;
ServletOutputStream outputStream = null;
String tempFileName = String.valueOf(System.currentTimeMillis());
File file = null;
fos = new FileOutputStream(request.getSession().getServletContext()
.getRealPath("/export/" + tempFileName + ".xls"));
try {
// 打开文件
book = Workbook.createWorkbook(fos);
// 生成名为"更新报名人数统计"的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet("更新人数统计", 0);
Label label;
SimpleConditions queryConditions = new SimpleConditions();
// 构造表头
// 指定单元格位置


for (int i = 0; i < title.length; i++) {
// Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
// 在Label对象的子对象中指明单元格的位置和内容
label = new Label(i, 0, title[i], getHeader());
// 将定义好的单元格添加到工作表中
sheet.addCell(label);
}
// 下面是填充数据
/*
* 保存数字到单元格，需要使用jxl.write.Number 必须使用其完整路径，否则会出现错误
*/
// // 保存数字的单元格必须使用Number的完整包路径
// jxl.write.Number number = new jxl.write.Number(1, 0, 30);
// sheet.addCell(number);


List<TotalSchool> lstData = competitionTeamService
.totalSchool(queryConditions);


for (int j = 0; j < lstData.size(); j++) {
// sheet.mergeCells(0, 0, 4,
// 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
sheet.setRowView(j, 380, false);
sheet.setColumnView(j, 20);
sheet.addCell(new Label(0, j + 1, lstData.get(j)
.getCollegeName(), getBody()));
sheet.addCell(new Label(1, j + 1, lstData.get(j)
.getTotalPeople(), getBody()));
sheet.addCell(new Label(2, j + 1,
lstData.get(j).getTotalTeam(), getBody()));
sheet.addCell(new Label(3, j + 1, lstData.get(j).getTeamName(),
getBody()));
sheet.addCell(new Label(4, j + 1, lstData.get(j)
.getTeamMember(), getBody()));
sheet.addCell(new Label(5, j + 1, lstData.get(j)
.getTeamLeader(), getBody()));
sheet.addCell(new Label(6, j + 1, lstData.get(j).getTeacher(),
getBody()));
}
SumSchool sumDate = competitionTeamService
.sumSchools(queryConditions);
sheet.addCell(new Label(0, lstData.size() + 1, "截止目前：报名成功高校"
+ sumDate.getSumSchool() + "所，报名成功人数"
+ sumDate.getSumPeople() + "人，报名成功团队"
+ sumDate.getSumTeam() + "个.", getBottom()));
sheet.mergeCells(0, lstData.size() + 1, 6, lstData.size() + 1);

int sumPerson = competitionTeamService.addPeople(queryConditions);
int sumSchool = competitionTeamService.addCollege(queryConditions);
int sumTeam = competitionTeamService.addTeam(queryConditions);
sheet.addCell(new Label(0, lstData.size() + 2, "较昨日变化：新增高校 +"
+ sumSchool + " 所，新增团队 +" + sumTeam + " 个，新增人数 +" + sumPerson
+ " 人.", getBottom()));
sheet.mergeCells(0, lstData.size() + 2, 6, lstData.size() + 3);
// 合并单元格
int startRow = 1;
String collegeName = "";
for (int j = 0; j < lstData.size(); j++) {
if (StringUtils.isBlank(collegeName)) {
collegeName = lstData.get(j).getCollegeName();
} else {
if (!collegeName.equals(lstData.get(j).getCollegeName())) {
if (startRow != j) {
sheet.mergeCells(0, startRow, 0, j);
sheet.mergeCells(1, startRow, 1, j);
sheet.mergeCells(2, startRow, 2, j);
}
collegeName = lstData.get(j).getCollegeName();
startRow = j + 1;
}
}
}
if (startRow != lstData.size()) {
sheet.mergeCells(0, startRow, 0, lstData.size());
sheet.mergeCells(1, startRow, 1, lstData.size());
sheet.mergeCells(2, startRow, 2, lstData.size());
}
// 写入数据并关闭文件
book.write();
book.close();
Date currDate = new Date(System.currentTimeMillis());
SimpleDateFormat format = new SimpleDateFormat(
"yyyy-MM-dd_HH_mm_ss");
String fileName = format.format(currDate);
response.setContentType("applicationnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename="
+ fileName + ".xls");
response.setContentType("textml;charset=utf-8");
outputStream = response.getOutputStream();
file = new File(request.getSession().getServletContext()
.getRealPath("/export/" + tempFileName + ".xls"));
in = new FileInputStream(file);
if (in != null) {
byte[] bytes = new byte[4];
int buffer = 0;
while ((buffer = in.read(bytes)) != -1) {
outputStream.write(bytes, 0, buffer);
}
outputStream.flush();
}
} catch (Exception e) {
System.out.println(e);
} finally {
if (book != null) {
try {
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (in != null) {
in.close();
}
if (outputStream != null) {
outputStream.close();
}
// if(file!=null){
// file.delete();
// }
}
}


public static WritableCellFormat getHeader() {
WritableFont font = new WritableFont(WritableFont.TIMES, 12,
WritableFont.BOLD);// 定义字体


try {
font.setColour(Colour.BLACK);// 黑色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);// 生成一个单元格样式控制对象
try {
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}


public static WritableCellFormat getBody() {
WritableFont font = new WritableFont(WritableFont.TIMES, 12);// 定义字体


try {
font.setColour(Colour.BLACK);// 黑色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);// 生成一个单元格样式控制对象
try {
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}
public static WritableCellFormat getWord() {
WritableFont font = new WritableFont(WritableFont.TIMES, 12);// 定义字体

try {
font.setColour(Colour.RED);// 黑色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);// 生成一个单元格样式控制对象
try {
format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}




public static WritableCellFormat getBottom() {
WritableFont font = new WritableFont(WritableFont.TIMES, 12,
WritableFont.BOLD);// 定义字体


try {
font.setColour(Colour.BLACK);// 黑色字体
} catch (WriteException e1) {
e1.printStackTrace();
}
WritableCellFormat format = new WritableCellFormat(font);// 生成一个单元格样式控制对象
try {
// format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
format.setBackground(Colour.YELLOW);// 黄色背景
} catch (WriteException e) {
e.printStackTrace();
}
return format;
}


}