写入:
boolean hsaNext = true;
Long pageNo = 1L;
int row = 1;
WritableWorkbook book = null;
WritableSheet sheet;
WritableCellFormat wcf_left;
try {
book = Workbook.createWorkbook(new File("d:/Test.xls"));
sheet = book.createSheet("Sheet_1", 0);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD);
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_center.setWrap(false); // 文字是否换行
wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行
sheet.addCell(new Label(0, 0, "订单号", wcf_center));
sheet.addCell(new Label(1, 0, "买家昵称", wcf_center));
sheet.addCell(new Label(2, 0, "实付金额", wcf_center));
sheet.addCell(new Label(3, 0, "付款时间", wcf_center));
sheet.addCell(new Label(4, 0, "优惠金额", wcf_center));
sheet.addCell(new Label(5, 0, "优惠名称", wcf_center));
sheet.addCell(new Label(6, 0, "优惠描述", wcf_center));
sheet.addCell(new Label(7, 0, "优惠金额", wcf_center));
sheet.addCell(new Label(8, 0, "优惠id", wcf_center));
while (hsaNext) {
TradesSoldGetRequest req = new TradesSoldGetRequest();
req.setFields("tid");
Date dateTime = null;
try {
dateTime = SimpleDateFormat.getDateTimeInstance().parse(
"2014-04-03 00:00:00");
} catch (ParseException e) {
e.printStackTrace();
}
req.setStartCreated(dateTime);
req.setStatus("TRADE_FINISHED");
req.setUseHasNext(true);
req.setPageSize(100L);
req.setPageNo(pageNo);
TradesSoldGetResponse response = null;
try {
response = client.execute(req, sessionKey);
hsaNext = response.getHasNext();
List<Trade> trades = response.getTrades();
Iterator<Trade> iterTrades = trades.iterator();
while (iterTrades.hasNext()) {
try {
Trade trade = iterTrades.next();
Trade fullTrade = topTradeWrapperService
.getFullInfoTrade(trade.getTid(),
sessionKey);
List<PromotionDetail> proDetails = fullTrade
.getPromotionDetails();
sheet.addCell(new Label(0, row, String
.valueOf(fullTrade.getTid()), wcf_left));
sheet.addCell(new Label(1, row, fullTrade
.getBuyerNick(), wcf_left));
sheet.addCell(new Label(2, row, fullTrade
.getPayment(), wcf_left));
sheet.addCell(new Label(3, row, SimpleDateFormat
.getDateTimeInstance().format(
fullTrade.getPayTime()), wcf_left));
sheet.addCell(new Label(4, row, fullTrade
.getDiscountFee(), wcf_left));
if (null != proDetails && !proDetails.isEmpty()) {
for (PromotionDetail promotionDetail : proDetails) {
sheet.addCell(new Label(5, row,
promotionDetail.getPromotionName(),
wcf_left));
sheet.addCell(new Label(6, row,
promotionDetail.getPromotionDesc(),
wcf_left));
sheet.addCell(new Label(7, row,
promotionDetail.getDiscountFee(),
wcf_left));
sheet.addCell(new Label(8, row,
promotionDetail.getPromotionId(),
wcf_left));
row++;
}
}
System.out.println(row);
row++;
} catch (Exception e) {
e.printStackTrace();
}
}
pageNo++;
} catch (ApiException e) {
e.printStackTrace();
try {
Thread.sleep(3000);
} catch (InterruptedException e1) {
}
}
}
book.write();
} catch (RowsExceededException e1) {
e1.printStackTrace();
} catch (WriteException e1) {
e1.printStackTrace();
} catch (IOException e1) {
e1.printStackTrace();
} finally {
try {
book.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
读取:
Workbook book = null;
try {
book = Workbook.getWorkbook(new File(
"d:/gymboree官方旗舰店订单记录.xls"));
Sheet sheet = book.getSheet(0);
String buyer = "";
List<String> delBuyer = new ArrayList<String>();
for (int i = 0; i < sheet.getRows(); i++) {
try {
Cell cell = sheet.getCell(1, i);
String buyerCell = cell.getContents();
if (StringUtils.isNotBlank(buyerCell)) {
buyer = buyerCell;
}
cell = sheet.getCell(6, i);
String couponCell = cell.getContents();
if (!delBuyer.contains(buyer) && couponCell.equals("优惠券:省80.00元")) {
User user = new User();
user.setNick(buyer);
delBuyer.add(buyer);
Long eId = couponCardServiceImpl.use(28539L, user);
System.out.println(eId);
}
} catch (Exception e) {
e.printStackTrace();
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
book.close();
}