后台代码:
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;
public class ExcelUtil {
/**
* 返回大标题格式
*/
public static WritableCellFormat createWcfTitle() {
Workbook rwb=null;
try {
//读取Excel文件
FileInputStream is = new FileInputStream("C:\\8.8.xls");
rwb=Workbook.getWorkbook(is);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Sheet rs=rwb.getSheet(0);
//获取第一行 ,第一列的值
Cell c00=rs.getCell(0,0);
String strc00= c00.getContents();
//获取第一行,第二列的值
Cell c10=rs.getCell(1,0);
String strc10=c10.getContents();
//获取第二行,第一列的值
Cell c01=rs.getCell(0,1);
String strc01=c01.getContents();
//获取第二行,第一列的值
Cell c11=rs.getCell(1,1);
String strc11=c11.getContents();
//获取时间 strc41=08:30
Cell c41=rs.getCell(4,1);
String strc41=c41.getContents();
//获取时间 strc51=18:20
Cell c51=rs.getCell(5,1);
String strc51=c51.getContents();
try{
//计算时间差
SimpleDateFormat df = new SimpleDateFormat("HH:mm");
System.out.println(strc41);
System.out.println(strc51);
java.util.Date now = df.parse(strc51);
java.util.Date date=df.parse(strc41);
long l=now.getTime()-date.getTime();
System.out.println(l);
long day=l/(24*60*60*1000);
long hour=(l/(60*60*1000)-day*24);
long min=((l/(60*1000))-day*24*60-hour*60);
long s=(l/1000-day*24*60*60-hour*60*60-min*60);
System.out.println(day);
System.out.println(hour);
System.out.println(min);
System.out.println(""+day+"天"+hour+"小时"+min+"分"+s+"秒");
}
catch(Exception ex){
ex.printStackTrace();
}
WritableFont wfc_big = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf_title = new WritableCellFormat(wfc_big);
try {
wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_title.setAlignment(Alignment.CENTRE);
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_title.setWrap(true);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf_title;
}
/**
* 返回正文格式
*/
public static WritableCellFormat createWcfText() {
WritableFont wfc_small = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf_text = new WritableCellFormat(wfc_small);
try {
wcf_text.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_text.setAlignment(Alignment.LEFT);
wcf_text.setVerticalAlignment(VerticalAlignment.CENTRE);
} catch (WriteException e) {
e.printStackTrace();
}
return wcf_text;
}
}
前台代码:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="java.io.*"%>
<%@page import="jxl.write.*"%>
<%@page import="jxl.Workbook"%>
<%@page import="com.ying.ExcelUtil"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'exportExcel.jsp' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</head>
<body>
<%
response.reset();
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String("Excel文件.xls".getBytes("utf-8"), "iso8859-1"));
OutputStream os = response.getOutputStream();
WritableWorkbook wwb = null;
WritableCellFormat wcf_title = ExcelUtil.createWcfTitle();
WritableCellFormat wcf_text = ExcelUtil.createWcfText();
wwb = Workbook.createWorkbook(os);//将 WritableWorkbook 写入到输出流
WritableSheet ws = wwb.createSheet("sheet1", 0);//创建第一个sheet
ws.mergeCells(0, 0, 3, 0);//合并单元格
Label lbl = null;
lbl = new Label(0, 0, "大标题" , wcf_title);
ws.addCell(lbl);
lbl = new Label(0, 1, "列标题1", wcf_text);
ws.addCell(lbl);
lbl = new Label(1, 1, "列标题2", wcf_text);
ws.addCell(lbl);
lbl = new Label(2, 1, "列标题3", wcf_text);
ws.addCell(lbl);
lbl = new Label(3, 1, "列标题4", wcf_text);
ws.addCell(lbl);
for (int i = 0; i < 3; i++) //设置宽度
ws.setColumnView(i, 15);
os.flush();
wwb.write();
wwb.close();
os.close();
//解决异常java.lang.IllegalStateException: getOutputStream() has already been called for this response
out.clear();
out = pageContext.pushBody();
%>
</body>
</html>