一、本地操作
1、读取
package com.ljf.mb; import java.io.FileInputStream;
import java.io.InputStream; import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook; public class ReadExcel
{
public static void main(String[] args) throws Exception
{
{
String filePath = "d:/.xls"; //文件在电脑中的位置
InputStream is = new FileInputStream(filePath);
Workbook rwb = Workbook.getWorkbook(is); Sheet rs = rwb.getSheet(0);// 获取第一张Sheet表
int hangNum = rs.getRows();//得到总行数 for (int i = 1; i < hangNum; i++)
{
String name = getCallStr(rs, i, 5).trim(); //得到第i行，第5列数据，行列均是从0开始
String gg = getCallStr(rs, i, 6).trim();
System.out.println(name + " " + gg); //组织自己想要的输出
}
}
} /**
* 功能描述：得到工作表中某个单元格的数据
*</br>日期：2013-3-29
*</br>@author ： 劲风
*</br>@param rs 工作表对象
*</br>@param hang 行数，从0开始
*</br>@param lie 列数，从0开始
*</br>@return 单元格的内容
*/
public static String getCallStr(Sheet rs, int hang, int lie)
{
Cell cell = rs.getCell(lie, hang);
String value = cell.getContents();
if (value != null)
{
return value.trim();
}
return "";
}
}
2、生成
import java.io.File;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook; public class WriteExcel
{ public static void main(String[] args) throws Exception
{
WritableWorkbook wwb = null;
WritableSheet ws = null;
try
{
//首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(new File("d:/tables/one.xls"));
if(wwb!=null)
{
//创建一个可写入的工作表
ws = wwb.createSheet("sheet1", 0); //第一个是工作表的名称，第二个是工作表在工作薄中的位置 //下面开始添加单元格
Label lab00 = creatLabel(0, 0, "姓名"); //行，列，内容
Label lab01 = creatLabel(0, 1, "年龄"); //行，列，内容
Label lab10 = creatLabel(1, 0, "张三"); //行，列，内容
Label lab11 = creatLabel(1, 1, "20"); //行，列，内容 ws.addCell(lab00);
ws.addCell(lab01);
ws.addCell(lab10);
ws.addCell(lab11); wwb.write(); //从内存中写入文件中
wwb.close(); //关闭资源，释放内存
}
}
catch (Exception e)
{
e.printStackTrace();
} } public static Label creatLabel(int hang,int lie,String con) //行，列，内容
{
return new Label(lie, hang, con);
}
}
二、Spring MVC 生成下载Excel
1、页面请求
<a href="javascript:void(0)" class="easyui-linkbutton exportBtn" iconCls="glyphicon-export" plain="true" onclick="exportCsv()">导出</a> ...... <script type="text/javascript">
//导出（未实现）
function exportCsv() {
var option = datagrid.datagrid("options");
var param = option.queryParams;
param.sortName = option.sortName;
param.sortOrder = option.sortOrder;
window.location.href = "exportExcel?" + $.param(param);
}
</script>
2、后台逻辑
/**
* 导出Excel
* @throws Exception
*/
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response,OutBillVo vo) throws Exception { List<OutBill> ls=service.exportExcelQuery(vo); //取得导出数据 File file = new File("aaa.xls");
jxl.write.WritableWorkbook book = Workbook.createWorkbook(file); //工作薄
jxl.write.WritableSheet sheet = book.createSheet("Sheet1", 0); //工作表 //设置行高
sheet.getSettings().setDefaultRowHeight(350); //设置所有行默认行高
sheet.setRowView(0, 700);//第1行 高度
sheet.setRowView(1, 400);//第2行 高度 //设置个别列宽
sheet.setColumnView(1, 16);//宽度设置：登记时间
sheet.setColumnView(8, 16);//宽度设置：出发时间
sheet.setColumnView(9, 16);//宽度设置：归队时间
sheet.setColumnView(6, 12);//宽度设置：用车部门
sheet.setColumnView(7, 16);//宽度设置：目的地
sheet.setColumnView(18, 12);//宽度设置：车辆状况
sheet.setColumnView(19, 16);//宽度设置：备注 //定义格式
//主标题格式
WritableCellFormat formatterTitle= new WritableCellFormat(); //标题格式
formatterTitle.setAlignment(Alignment.CENTRE);//水平对齐
formatterTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
//WritableFont font = new WritableFont(WritableFont.createFont("ＭＳ Ｐゴシック"), 18, WritableFont.BOLD, false); //黑体
WritableFont font = new WritableFont(WritableFont.TAHOMA, 18, WritableFont.BOLD, false);
formatterTitle.setFont(font); //列标题格式
WritableCellFormat formatterColumnTitle= new WritableCellFormat(); //列标题格式
formatterColumnTitle.setAlignment(Alignment.CENTRE);//水平对齐
formatterColumnTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
formatterColumnTitle .setBackground(Colour.GRAY_25);//背景色
formatterColumnTitle.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线 //普通内容格式
WritableCellFormat formatterCon= new WritableCellFormat(); //内容格式
formatterCon.setAlignment(Alignment.CENTRE);//水平对齐
formatterCon.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
formatterCon.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线 //合计行格式
WritableCellFormat formatterSum= new WritableCellFormat(); //合计格式
formatterSum.setAlignment(Alignment.CENTRE);//水平对齐
formatterSum.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
formatterSum.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线 所有边框 细线
WritableFont font_sum = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD, false);
formatterSum.setFont(font_sum); //输出标题
sheet.mergeCells(0, 0, 19, 0); //合并单元格（标题）
sheet.addCell(creatLabel(0,0,"车辆外出登记表",formatterTitle)); //输出列标题
int rowNum=1; //行号
int columnNum=0; //列号
sheet.addCell(creatLabel(rowNum,columnNum++,"序号",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"登记时间",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"车辆",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"驾驶员",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"用车人",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"审批人",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"用车部门",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"目的地",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"出发时间",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"归队时间",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"总时长",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"等待时长",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"出发里程",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"归队里程",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"使用里程",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"加油金额",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"过/停费",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"总费用",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"车辆状况",formatterColumnTitle));
sheet.addCell(creatLabel(rowNum,columnNum++,"备注",formatterColumnTitle)); //合计项
int useNum = 0; // 使用里程
double useTime = 0; // 使用时长(小时，保留一位小数)
double waitTime = 0; // 等待时间(小时，保留一位小数)
double fuelNum = 0; // 加油费
double otherFee = 0; // 其他费用（过路费、停车费）
double allFee = 0; // 全部费用 if(ls != null && ls.size() > 0)
{
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm"); //日期格式化
for(int i = 0; i < ls.size(); i++)
{
OutBill bo = ls.get(i);
rowNum++;
columnNum=0; //非必填项Null值处理
Double fuelNumDouble = bo.getFuelNum(); //加油费
Double otherFeeDouble = bo.getOtherFee(); //其他费用（过路费、停车费）
String remark = bo.getRemark(); //备注
String carStatus = bo.getCarStatus(); //车辆状况 String fuelNumStr = ""; //
if(fuelNumDouble != null){fuelNumStr = fuelNumDouble.toString();}
String otherFeeStr = ""; //
if(otherFeeDouble != null){otherFeeStr = otherFeeDouble.toString();}
if(remark == null){remark="";}
if(carStatus == null){remark="";} //输出一行数据
sheet.addCell(creatLabel(rowNum,columnNum++,(i+1)+"",formatterCon)); //序号
sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getAddTime()) ,formatterCon)); //登记时间
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getCar().getName() ,formatterCon)); //车辆
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDriver() ,formatterCon)); //驾驶员
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUser() ,formatterCon)); //用车人
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getApprover(),formatterCon)); //审批人
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getOrgan().getName() ,formatterCon)); //用车部门
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDestination() ,formatterCon)); //目的地
sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getBeginTime()) ,formatterCon)); //出发时间
sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getEndTime()) ,formatterCon)); //归队时间
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseTime()+"" ,formatterCon)); //总时长
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getWaitTime()+"" ,formatterCon)); //等待时长
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getBeginNum()+"" ,formatterCon)); //出发里程
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getEndNum()+"" ,formatterCon)); //归队里程
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseNum()+"" ,formatterCon)); //使用里程
sheet.addCell(creatLabel(rowNum,columnNum++,fuelNumStr ,formatterCon)); //加油金额
sheet.addCell(creatLabel(rowNum,columnNum++,otherFeeStr ,formatterCon)); //过 / 停费
sheet.addCell(creatLabel(rowNum,columnNum++,bo.getAllFee()+"" ,formatterCon)); //总费用
sheet.addCell(creatLabel(rowNum,columnNum++,carStatus ,formatterCon)); //车辆状况
sheet.addCell(creatLabel(rowNum,columnNum++,remark,formatterCon)); //备注 //合计项累加
if(bo.getUseNum() != null)
{
useNum += bo.getUseNum();
}
if(bo.getUseTime() != null)
{
useTime += bo.getUseTime();
}
if(bo.getWaitTime() != null)
{
waitTime += bo.getWaitTime();
}
if(bo.getFuelNum() != null)
{
fuelNum += bo.getFuelNum();
}
if(bo.getOtherFee() != null)
{
otherFee += bo.getOtherFee();
}
if(bo.getAllFee() != null)
{
allFee += bo.getAllFee();
}
} //表格增加合计项
rowNum++;
columnNum = 0;
sheet.setRowView(rowNum, 400);//合计行 高度 sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //序号
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //登记时间
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //驾驶员
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车人
sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //审批人
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车部门
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //目的地
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发时间
sheet.addCell(creatLabel(rowNum,columnNum++,"合计",formatterSum)); //归队时间
sheet.addCell(creatLabel(rowNum,columnNum++,useTime+"" ,formatterSum)); //总时长
sheet.addCell(creatLabel(rowNum,columnNum++,waitTime+"" ,formatterSum)); //等待时长
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发里程
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //归队里程
sheet.addCell(creatLabel(rowNum,columnNum++,useNum+"" ,formatterSum)); //使用里程
sheet.addCell(creatLabel(rowNum,columnNum++,fuelNum+"" ,formatterSum)); //加油金额
sheet.addCell(creatLabel(rowNum,columnNum++,otherFee+"" ,formatterSum)); //过 / 停费
sheet.addCell(creatLabel(rowNum,columnNum++,allFee+"" ,formatterSum)); //总费用
sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆状况
sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //备注 } //------------------------------- book.write();
book.close(); FileInputStream f = new FileInputStream(file);
byte[] fb = new byte[f.available()];
f.read(fb);
response.setHeader("Content-disposition", "attachment; filename=" + new String("出车记录.xls".getBytes("gb2312"), "iso8859-1"));
ByteArrayInputStream bais = new ByteArrayInputStream(fb);
int b;
while ((b = bais.read()) != -1)
{
response.getOutputStream().write(b);
}
response.getOutputStream().flush();
f.close(); } private Label creatLabel(int rowNum,int columnNum,String con,WritableCellFormat formatter) //行，列，内容 ，格式化
{
return new Label(columnNum, rowNum, con,formatter);
}