前兩天公司的同事用java jxl寫了一段導出excel的代碼，值得學習，現提供代碼如下：（導出一個list,要作到iterator）
首先導出jxl.jar，然后方法如下：
/**
* @param mapping
* @param form
* @param request
* @param response
* @return ActionForward 得到輸入的起始客戶代碼和結束客戶代碼,查詢結算清單，返回結果
*/
public void doExportAsXLS(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
String userCodeBegin = request.getParameter("uCodeBegin");
String userCodeEnd = request.getParameter("uCodeEnd");
log.info("userCodeBegin " + userCodeBegin.toString());
log.info("userCodeEnd " + userCodeEnd.toString());
if (userCodeBegin == null || userCodeBegin.equals("")) {
userCodeBegin = userCodeEnd;
}
if (userCodeEnd == null || userCodeEnd.equals("")) {
userCodeEnd = userCodeBegin;
}
List balanceList = settleService.getSettleBill(userCodeBegin,
userCodeEnd);
OutputStream os = null;
WritableWorkbook wwb = null;
Date date = new Date();
Format format = new SimpleDateFormat("yyyyMMdd");
String strDate = format.format(date);
try {
os = new FileOutputStream(strDate + "settle.xls");
wwb = Workbook.createWorkbook(os);
Iterator it = balanceList.iterator();
doConfigExcel(it, wwb);
} catch (Exception e) {
} finally {
try {
wwb.write();
wwb.close();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
response.setHeader("Content-disposition", "attachment;filename="
+ strDate + "settle.xls\"");
response.setContentType("application/msexcel");
try {
FileInputStream fileInputStream = new FileInputStream(strDate
+ "settle.xls");
OutputStream out = response.getOutputStream();
int i = 0;
while ((i = fileInputStream.read()) != -1) {
out.write(i);
}
fileInputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private void doConfigExcel(Iterator it, WritableWorkbook wwb) {
try {
while (it.hasNext()) {
Label lchild;
SettleVo sv = (SettleVo) it.next();
String userCode = sv.getUserCode();
int i = 2;
int j = 0;
WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true);
WritableCellFormat wcfF = new WritableCellFormat(wf);
WritableSheet ws = wwb.createSheet(userCode, 0);
Label labelMain = new Label(4, 0, "中國玉米淀粉網 -- 客戶結算清單",wcfF);
Label labelMain1 = new Label(4, 1, "客戶結算清單",wcfF);
ws.addCell(labelMain);
ws.addCell(labelMain1);
lchild = new Label(0, 2, "客戶代碼");
ws.addCell(lchild);
lchild = new Label(0, 3, userCode);
ws.addCell(lchild);
String code = sv.getCode();
lchild = new Label(2, 2, "攤位代碼");
ws.addCell(lchild);
lchild = new Label(2, 3, code);
ws.addCell(lchild);
String fdate = sv.getFormatDate();
lchild = new Label(4, 2, "日期");
ws.addCell(lchild);
lchild = new Label(4, 3, fdate);
ws.addCell(lchild);
lchild = new Label(4, 4, "當日成交清單",wcfF);
ws.addCell(lchild);
Iterator its = sv.getOrders().iterator();
lchild = new Label(0, 5, "序 號");
ws.addCell(lchild);
lchild = new Label(2, 5, "交貨時間");
ws.addCell(lchild);
lchild = new Label(4, 5, "市 場");
ws.addCell(lchild);
lchild = new Label(6, 5, "合約名稱");
ws.addCell(lchild);
lchild = new Label(8, 5, "成交時間");
ws.addCell(lchild);
lchild = new Label(10, 5, "上網時間");
ws.addCell(lchild);
lchild = new Label(12, 5, "價 格");
ws.addCell(lchild);
lchild = new Label(14, 5, "數 量");
ws.addCell(lchild);
lchild = new Label(16, 5, "類 型");
ws.addCell(lchild);
int ii = 6;
BigDecimal totalDealprice = new BigDecimal(0);
Long totalAmount = new Long(0);
while (its.hasNext()) {
ii ++;
TSpotOrder order = (TSpotOrder) its.next();
String ordercode = order.getOrderCode();
lchild = new Label(0, ii, ordercode);
ws.addCell(lchild);
String tradeTime = order.getLastTradeTime().toString();
lchild = new Label(2, ii, tradeTime);
ws.addCell(lchild);
String exchange = order.getExchange();
lchild = new Label(4, ii, exchange);
ws.addCell(lchild);
String stockname = order.getFuturesStockBase()
.getStockName();
lchild = new Label(6, ii, stockname);
ws.addCell(lchild);
String dealtime = order.getForMateDealTime();
lchild = new Label(8, ii, dealtime);
ws.addCell(lchild);
lchild = new Label(10, ii, "-");
ws.addCell(lchild);
String dealprice = order.getDealPrice().toString();
lchild = new Label(12, ii, dealprice);
ws.addCell(lchild);
totalDealprice = totalDealprice.add(order.getDealPrice());
String amount = String.valueOf(order.getAmount());
lchild = new Label(14, ii, amount);
ws.addCell(lchild);
totalAmount = totalAmount + order.getAmount();
String buy = order.getBuyFormat();
lchild = new Label(16, ii, buy);
ws.addCell(lchild);
}
lchild = new Label(0, ii + 1, "合計");
ws.addCell(lchild);
lchild = new Label(12, ii + 1, totalDealprice.toString());
ws.addCell(lchild);
lchild = new Label(14, ii + 1, totalAmount.toString());
ws.addCell(lchild);
int jj = ii + 3;
int jjj = jj + 2;
lchild = new Label(4, ii + 2, "訂貨表",wcfF);
ws.addCell(lchild);
lchild = new Label(0, jj, "交貨月份");
ws.addCell(lchild);
lchild = new Label(2, jj, "市 場");
ws.addCell(lchild);
lchild = new Label(4, jj, "合約名稱");
ws.addCell(lchild);
lchild = new Label(6, jj, "買 / 賣");
ws.addCell(lchild);
lchild = new Label(8, jj, "平均價格 ");
ws.addCell(lchild);
lchild = new Label(10, jj, "數 量");
ws.addCell(lchild);
lchild = new Label(12, jj, "保證金");
ws.addCell(lchild);
lchild = new Label(14, jj, "浮 虧");
ws.addCell(lchild);
BigDecimal totalAvgprice = new BigDecimal(0);
Long totalAvgamount = new Long(0);
BigDecimal totalDeposit = new BigDecimal(0);
BigDecimal totalProloss = new BigDecimal(0);
Iterator _its = sv.getHolds().iterator();
while (its.hasNext()) {
jjj ++ ;
HoldVo4settle hold = (HoldVo4settle) _its.next();
String month = hold.getDeliverMonth();
lchild = new Label(0, jjj, month);
ws.addCell(lchild);
String market = hold.getMarket();
lchild = new Label(2, jjj, market);
ws.addCell(lchild);
String ordername = hold.getOrderName();
lchild = new Label(4, jjj, ordername);
ws.addCell(lchild);
String buyformat = hold.getBuyFormat();
lchild = new Label(6, jjj, buyformat);
ws.addCell(lchild);
String avgprice = hold.getPrice().toString();
lchild = new Label(8, jjj, avgprice);
ws.addCell(lchild);
totalAvgprice = totalAvgprice.add(hold.getPrice());
String avgamount = hold.getAmount().toString();
lchild = new Label(10, jjj, avgamount);
ws.addCell(lchild);
totalAvgamount = totalAvgamount + hold.getAmount();
String deposit = hold.getDeposit().toString();
lchild = new Label(12, jjj, deposit);
ws.addCell(lchild);
totalDeposit = totalDeposit.add(hold.getDeposit());
String proloss = hold.getPro_loss().toString();
lchild = new Label(14, jjj, proloss);
ws.addCell(lchild);
totalProloss = totalProloss.add(hold.getPro_loss());
}
lchild = new Label(0, jjj , "合計");
ws.addCell(lchild);
lchild = new Label(8, jjj, totalAvgprice.toString());
ws.addCell(lchild);
lchild = new Label(10, jjj, totalAvgamount.toString());
ws.addCell(lchild);
lchild = new Label(12, jjj, totalDeposit.toString());
ws.addCell(lchild);
lchild = new Label(14, jjj, totalProloss.toString());
ws.addCell(lchild);
lchild = new Label(4, jjj + 2, "資金結算表",wcfF);
ws.addCell(lchild);
BigDecimal capital = sv.getAddCapital();
lchild = new Label(0, jjj + 3, "+ 當日存入");
ws.addCell(lchild);
lchild = new Label(6, jjj + 3, "- 當日支出 ");
ws.addCell(lchild);
lchild = new Label(0, jjj + 4, "+ 當日交易盈虧");
ws.addCell(lchild);
lchild = new Label(6, jjj + 4, "- 交易手續費 ");
ws.addCell(lchild);
if(capital != null){
lchild = new Label(2, jjj + 3, capital.toString());
ws.addCell(lchild);
}
BigDecimal redcapital = sv.getReduceCapital();
if(redcapital != null){
lchild = new Label(8, jjj + 3, redcapital.toString());
ws.addCell(lchild);
}
BigDecimal pro_loss = sv.getPro_loss();
if(pro_loss != null){
lchild = new Label(2, jjj + 4, pro_loss.toString());
ws.addCell(lchild);
}
BigDecimal fee = sv.getFee();
if(fee != null){
lchild = new Label(8, jjj + 4, fee.toString());
ws.addCell(lchild);
}
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}