/**
* 将数据写入Excel中
*/
public String exportExcel(String filepath,String sheetName,long clientID)throws Exception{
String returnPath = "";
long thistime = new Date().getTime();
OutputStream os;
Connection con = null;
ResultSet rs = null;
PreparedStatement ps = null;
try{
// 生成文件
os = new FileOutputStream(filepath + sheetName + "-" + thistime + ".xls");
WritableWorkbook book = Workbook.createWorkbook(os);
// sheet名称
WritableSheet sheet = book.createSheet(sheetName, 0);
//设置格式
WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat cellFormat=new WritableCellFormat(wf);
//设置为文本格式
NumberFormat nf = new NumberFormat("#00");
WritableCellFormat cf = new WritableCellFormat(nf);
//设置为金额
NumberFormat nf2 = new NumberFormat("#,##0.00");
WritableCellFormat wcf = new WritableCellFormat(nf2);
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setBackground(jxl.format.Colour.SKY_BLUE);//设置单元格的颜色
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 30);
sheet.setColumnView(2, 30);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 20);
WritableCellFormat cellFormat1=new WritableCellFormat();
cellFormat1.setAlignment(Alignment.CENTRE);
//设置列
Label four_1 = new Label(0, 0, "合同号");
four_1.setCellFormat(cellFormat);
//four_1.setCellFormat(cf);
sheet.addCell(four_1);
Label four_2 = new Label(1, 0, "收款方账户");
four_2.setCellFormat(cellFormat);
//four_2.setCellFormat(cf);
sheet.addCell(four_2);
Label four_3 = new Label(2, 0, "付款方账户");
four_3.setCellFormat(cellFormat);
//four_3.setCellFormat(cf);
sheet.addCell(four_3);
Label four_4 = new Label(3, 0, "金额");
four_4.setCellFormat(cellFormat);
//four_4.setCellFormat(wcf);
sheet.addCell(four_4);
Label four_5 = new Label(4, 0, "摘要");
four_5.setCellFormat(cellFormat);
//four_5.setCellFormat(cf);
sheet.addCell(four_5);
int x=0;
String sql="select * from SETT_CONSIGNRECEIVESET c where c.npayeeclientid="+clientID;
con = Database.getConnection();
ps=con.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
x++;
Label label1=new Label(0,x,rs.getString("SCONTRACTCODE"),cf);
sheet.addCell(label1);
Label label2=new Label(1,x,NameRef.getAccountNum(rs.getLong("NPAYEEACCTID")),cf);
sheet.addCell(label2);
Label label3=new Label(2,x,NameRef.getAccountNum(rs.getLong("NPAYERACCTID")),cf);
sheet.addCell(label3);
Label label4=new Label(3,x,"",wcf);
sheet.addCell(label4);
Label label5=new Label(4,x,"",cf);
sheet.addCell(label5);
}
book.write();
book.close();
returnPath=filepath + sheetName + "-" + thistime + ".xls";
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
try
{
if (rs != null)
{
rs.close();
rs = null;
}
if (ps != null)
{
ps.close();
ps = null;
}
if (con != null)
{
con.close();
con = null;
}
}
catch (Exception e)
{
;
}
}
return returnPath;
}
/**
* 从Excel中导入数据
* @param mySmartUpload
* @param uploadPath
* @param sessionMng
* @return map
* @throws Exception
*/
public Map importExcel(SmartUpload mySmartUpload, String uploadPath,SessionOB sessionMng) throws Exception {
Vector vector = new Vector();
Map map = new HashMap();
Vector returnVector = new Vector();
String strAdd = "";// 每次从上传文件中读到的一个单元。
short index = 0;
boolean bIsValid = false;
File myfile = mySmartUpload.getFiles().getFile(0);
myfile.saveAs(uploadPath + "special/consignreceive/"+myfile.getFileName());
Connection conn = null;
FileInputStream fis = new FileInputStream(Env.UPLOAD_PATH+"special/consignreceive/"+myfile.getFileName());
ConsignReceiveInfo consignReceiveInfo = null;
if(myfile.getFileName().indexOf(".xls") < 0) {
throw new Exception("选择文档与模板文件不一致，请检查");
}
try {
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
if (workbook != null) {
sheet = workbook.getSheetAt(0);
}
if (sheet == null) {
throw new Exception("不能导入空的Excel文件");
}
if (sheet != null) {
row = sheet.getRow(1);
}
if(row == null){
throw new Exception("不能导入空的Excel文件");
}
for (int j=1; row!=null; j++, row=sheet.getRow(j)) {
bIsValid = true;
consignReceiveInfo = new ConsignReceiveInfo();
//1.合同号 2.收款方账号 3.付款方账号 4.金额 5.摘要
long subTypeId = -1;
for(index = 1; index <= 5; index++){
strAdd = "";
cell = row.getCell((short) (index - 1));
if (cell != null) {
// 在Excel中的类型是文本、常规
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
strAdd = cell.getStringCellValue();
}
// 在Excel中的类型是数值
else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
strAdd = String.valueOf(cell.getNumericCellValue());
}
System.out.println(strAdd);
}
if (strAdd == null) {
strAdd = "";
}
if(index==1){
consignReceiveInfo.setSContractCode(strAdd);
}
if (index == 2) {
consignReceiveInfo.setNPayeeAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayeeClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if(index==3){
consignReceiveInfo.setNPayerAcctID((Long)NameRef.getByNoFromSett(strAdd).get("id"));
consignReceiveInfo.setNPayerClientID((Long)NameRef.getByNoFromSett(strAdd).get("nclientid"));
}
if (index == 4) {
if (strAdd.matches("^\\s*([1-9]\\d{0,12}|0)(\\.(\\d){1,2})?\\s*$")) {
consignReceiveInfo.setMAmount(Double.valueOf(strAdd.replaceAll(",", "")).doubleValue());
}
else {
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("金额格式不正确");
returnVector.addElement(upLoanReturnInfo);
}
}
if(index==5){
UpLoanReturnInfo upLoanReturnInfo = new UpLoanReturnInfo();
if(strAdd != null && !strAdd.equals("") && strAdd.length() > 0){
if(NameRef.getNAbstractIDByName(strAdd)>0){
consignReceiveInfo.setNAbstractID(NameRef.getNAbstractIDByName(strAdd));
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要不存在,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}else{
upLoanReturnInfo.setIsOk(true);
upLoanReturnInfo.setPositionCol(index);
upLoanReturnInfo.setPositionRow(j+1);
upLoanReturnInfo.setReason("摘要填写不正确,请检查!");
returnVector.addElement(upLoanReturnInfo);
}
}
}
consignReceiveInfo.setNOfficeID(sessionMng.m_lOfficeID);
consignReceiveInfo.setNCurrencyID(sessionMng.m_lCurrencyID);
consignReceiveInfo.setDTInput(Env.getSystemDate());
consignReceiveInfo.setNInputUserID(sessionMng.m_lUserID);
//consignReceiveInfo.setNPayeeClientID(sessionMng.m_lClientID);
consignReceiveInfo.setNStatus(OBConstant.SettInstrStatus.SAVE);
consignReceiveInfo.setNTransType(31);
vector.add(consignReceiveInfo);
}
//判断格式是否正确 如果正确则插入数据库
if(returnVector.size()==0){
int length=vector.size();
ConsignReceiveInfo[] infos=new ConsignReceiveInfo[length];
if (length > 0) {
for (int i = 0; i < length; i++) {
ConsignReceiveInfo info = (ConsignReceiveInfo) vector.get(i);
infos[i] = info;
}
//插入操作
}
////删除已经插入的操作
conn = Database.getConnection();
conn.setAutoCommit(false);
ConsignReceiveDao consignReceiveDao=new ConsignReceiveDao(conn);
for(int i=0;i<infos.length;i++){
ConsignReceiveInfo dataEntity = infos[i];
consignReceiveDao.add(dataEntity);
}
conn.commit();
conn.close();
//consignReceiveDao.importFromExcel(infos);
} else {
map.put("returnVector", returnVector);
}
}catch(Exception e){
e.printStackTrace();
conn.rollback();
}
return map;
}
}