基本思路：
1.从附件服务器上取得模板的流文件
2.拿到流文件之后再使用workbook.write(outs);方法改变流文件中的数据。
else if (pageContext.getParameter("Export") != null) {
OraclePreparedStatement oraclepreparedstatement;
OracleResultSet oracleresultset;
oraclepreparedstatement = null;
oracleresultset = null;
String attachmentId = null;
int count = 0;
try {
oraclepreparedstatement =
(OraclePreparedStatement)EditImportDataAM.getOADBTransaction().createPreparedStatement("SELECT COUNT(1)\n" +
" FROM mtl_categories_b_kfv\n" +
" WHERE category_id IN (SELECT category_id FROM cux_pon_categorys WHERE auction_header_id = '"+auctionHeaderId+"')",
1);
oracleresultset = (OracleResultSet)oraclepreparedstatement.executeQuery();
if (oracleresultset.next()) {
count = oracleresultset.getInt(1);
}
} catch (SQLException e) {
}
if(count==0){
throw new OAException("CUX","CUX_CATEGORY_DEMAND_NOT_NULL",null,OAException.ERROR,null);
}
try {
oraclepreparedstatement =
(OraclePreparedStatement)EditImportDataAM.getOADBTransaction().createPreparedStatement(" select cat.id from cux_attachment_t cat where cat.source_type='PON' and cat.source_table = 'PON_AUCTION_ITEM_TEMPLETE' and cat.source_id='-99999' and cat.file_status = 'C'",
1);
oraclepreparedstatement.defineColumnType(1, -5);
oracleresultset =
(OracleResultSet)oraclepreparedstatement.executeQuery();
if (oracleresultset.next()) {
attachmentId =
(new StringBuilder()).append("").append(oracleresultset.getLong(1)).toString();
} else {
throw new OAException("不存在定价行导入模板");
}
} catch (Exception exception2) {
throw OAException.wrapperException(exception2);
}
LogUtil.of(attachmentId, pageContext).print(pageContext);
downloadFileFromServer(pageContext, webBean, attachmentId);
// exportModeExcel(pageContext, webBean, attachmentId);
return;
}
public void downloadFileFromServer(OAPageContext pageContext,
OAWebBean webBean,
String attachmentId) {
OAApplicationModule am = pageContext.getApplicationModule(webBean);
AttachementVOImpl AttachementVO =
(AttachementVOImpl)am.findViewObject("AttachementVO");
if (AttachementVO == null) {
AttachementVO =
(AttachementVOImpl)am.createViewObject("AttachementVO",
"cux.oracle.apps.cux.attachement.server.AttachementVO");
}
AttachementVO.setWhereClause(null);
AttachementVO.setWhereClauseParams(null);
String sql = "1=1 and ID='" + attachmentId + "'";
AttachementVO.setWhereClause(sql);
AttachementVO.setMaxFetchSize(-1);
AttachementVO.executeQuery();
AttachementVORowImpl attachementInfo =
(AttachementVORowImpl)AttachementVO.first();
HttpServletResponse response =
(HttpServletResponse)pageContext.getRenderingContext().getServletResponse();
String fileType =
attachementInfo.getFileMimetype();
response.setContentType(fileType);
String fileName = attachementInfo.getFileName();
try {
response.addHeader("Content-Disposition",
"attachment;filename=" + new String(fileName.getBytes("GBK"),
"ISO-8859-1"));
response.setHeader("Content-Transfer-Encoding", "binary");
// response.setHeader("Cache-Control","must-revalidate, post-check=0, pre-check=0");
response.setHeader("Cache-Control", "no-store");
response.setHeader("Pragma", "public");
} catch (UnsupportedEncodingException e) {
LogUtil.of("downloadFileFromServer from FTP server Error-filename!",
this);
}
InputStream in = null;
ServletOutputStream outs = null;
FtpUtil ftp = this.getFtp(pageContext);
try {
outs = response.getOutputStream();
//获取附件流文件
in = ftp.getFile(attachementInfo.getFilePath(), attachementInfo.getFileName());
// int ch;
// while ((ch = in.read()) != -1) {
// outs.write(ch);
// }
// Workbook workbook = new XSSFWorkbook(in);
// org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(1);
// org.apache.poi.ss.usermodel.Row row = null;
// org.apache.poi.ss.usermodel.Cell cell = null;
//
// String cellValue = "132700002800";
//
// for (int i = 0; i < 5; i++) {
// System.out.println(" i =" + i);
// row = sheet.createRow(i);
// cell = row.createCell(0);
// cell.setCellValue(cellValue);
// }
// workbook.write(outs);
exportExcel(pageContext,in, outs);
} catch (IOException e) {
LogUtil.of("downloadFileFromServer from FTP server Error-!" +
e.getMessage(), this);
} finally {
try {
outs.flush();
outs.close();
if (in != null) {
in.close();
}
ftp.closeClient();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 获取FTP配制信息，并初始化连接
* @param pageContext
* @return
*/
protected FtpUtil getFtp(OAPageContext pageContext) {
String host = pageContext.getProfile("CUX_SRM_FTP_HOST");
String port = pageContext.getProfile("CUX_SRM_FTP_PORT");
String user = pageContext.getProfile("CUX_SRM_FTP_USER");
String password = pageContext.getProfile("CUX_SRM_FTP_PASSWORD");
return new FtpUtil(host, port, user, password);
}
public void exportExcel(OAPageContext pageContext,InputStream ins,OutputStream outs){
try {
OAApplicationModule am = pageContext.getRootApplicationModule();
OAApplicationModule EditImportDataAM = null;
EditImportDataAM =
(OAApplicationModule)am.findApplicationModule("EditImportDataAM");
OAViewObject AuctionHeadersAllVO =
(OAViewObject)am.findViewObject("AuctionHeadersAllVO");
AuctionHeadersAllVORowImpl AuctionHeadersAllRow =
(AuctionHeadersAllVORowImpl)AuctionHeadersAllVO.first();
Number auctionHeaderId = AuctionHeadersAllRow.getAuctionHeaderId();
OAViewObject itemSegVO =
(OAViewObject)EditImportDataAM.findViewObject("CuxAucItemSegmentsVO1");
itemSegVO.setWhereClause(null);
itemSegVO.setWhereClauseParams(null);
itemSegVO.setWhereClauseParam(0, auctionHeaderId);
itemSegVO.executeQuery();
RowSetIterator itemSegIter =
itemSegVO.findRowSetIterator("itemSegIter") == null ?
itemSegVO.createRowSetIterator("itemSegIter") :
itemSegVO.findRowSetIterator("itemSegIter");
Row itemSegRow = null;
int itemSegCount = itemSegVO.getRowCount();
Workbook workbook = new XSSFWorkbook(ins);
org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(1);
org.apache.poi.ss.usermodel.Row row = null;
org.apache.poi.ss.usermodel.Cell cell = null;
if (itemSegCount > 0) {
itemSegIter.setRangeStart(0);
itemSegIter.setRangeSize(itemSegCount);
for (int i = 0; i < itemSegCount; i++) {
itemSegRow = itemSegIter.getRowAtRangeIndex(i);
String itemDescription =
(String)itemSegRow.getAttribute("Description");
String itemNumber =
(String)itemSegRow.getAttribute("ItemNumber");
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(itemDescription);
cell = row.createCell(1);
cell.setCellValue(itemNumber);
}
}
itemSegIter.closeRowSetIterator();
workbook.write(outs);
}catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outs.flush();
outs.close();
if (ins != null) {
ins.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}