/*** 读取Excel并转换* @param file 获取到的文件* @return Map<String,List<String>> 把表头和表体以键值对的形式保存*/private Map<String,List<Map<String,Object>>> readExcelFileOutList(File file){Map<String,List<Map<String,Object>>> map = new HashMap<String,List<Map<String,Object>>>();InputStream is = null;try {is = new FileInputStream(file);Workbook rwb = Workbook.getWorkbook(is); //获得总的Sheets，得到sheet的层数//Sheet[] sheets = rwb.getSheets();//int sheetLen = sheets.length; //获得第一个Sheets 的结果 jxl.Sheet rs = rwb.getSheet(0); //得到行数 int rowNumber = rs.getRows();//得到列数int columnNum = rs.getColumns();for(int i=0;i<rowNumber;i++){//表头 if(i != 0){Map<String,Object> headMap = new HashMap<String,Object>();String companyCode = String.valueOf(rs.getCell(4, 1).getContents()); //公司名称 headMap.put("companyCode", companyCode);String company = String.valueOf(rs.getCell(5, 1).getContents()); //公司名称 headMap.put("company", company);String voucherType = String.valueOf(rs.getCell(1, 1).getContents());//凭证类型headMap.put("voucherType", voucherType);String fiscalYear = String.valueOf(rs.getCell(6, 1).getContents());//会计年度 headMap.put("fiscalYear", fiscalYear);String accountingPeriod = String.valueOf(rs.getCell(7, 1).getContents());//会计期间headMap.put("accountingPeriod", accountingPeriod);String voucherId = String.valueOf(rs.getCell(0, 1).getContents());//凭证号 headMap.put("voucherId", voucherId);String attachmentNumber = String.valueOf(rs.getCell(8, 1).getContents());//附件数量headMap.put("attachmentNumber", attachmentNumber);String prepareddate = String.valueOf(rs.getCell(3, 1).getContents());//制单日期headMap.put("prepareddate", prepareddate);String date = String.valueOf(rs.getCell(3, 1).getContents());//日期headMap.put("date", date);String enter = String.valueOf(rs.getCell(9, 1).getContents());//制单人headMap.put("enter", enter);/*String cashier = ""; //headlist.add(cashier);String signature = ""; //headlist.add(signature);String checker = "";//headlist.add(checker);String postingDate = ""; //记录时间headlist.add(postingDate);String postingPerson = ""; //记录人headlist.add(postingPerson);String voucherMakingSystem = "";//收据形成制度headlist.add(voucherMakingSystem);String memo1 = "";//备忘录headlist.add(memo1);String memo2 = "";//headlist.add(memo2);String reserve1 = "";//headlist.add(reserve1);String reserve2 = "";//headlist.add(reserve2);*/List<Map<String,Object>> headlist = new ArrayList<Map<String,Object>>();headlist.add(headMap);map.put("head", headlist); //表体List<Map<String,Object>> bodylist = new ArrayList<Map<String,Object>>();Map<String,Object> bodyMap = new HashMap<String,Object>();String entryId = String.valueOf(rs.getCell(2, i).getContents());//分录号bodyMap.put("entryId", entryId);String accountCode = String.valueOf(rs.getCell(12, i).getContents());//科目编码 bodyMap.put("accountCode", accountCode);String abstractContent = String.valueOf(rs.getCell(10, i).getContents());//摘要bodyMap.put("abstractContent", abstractContent);/* String settlement = "";//结算bodylist.add(settlement);String documentId = "";//bodylist.add(documentId);String documentDate = "";//bodylist.add(documentDate);*/String currency = "人命币";//币种 bodyMap.put("currency", currency);String unitPrice = "0.0";//单价 bodyMap.put("unitPrice", unitPrice);String exchangeRate1 = "0.0";//汇率1 bodyMap.put("exchangeRate1", exchangeRate1);String exchangeRate2 = "0.0";//汇率2 bodyMap.put("exchangeRate2", exchangeRate2);String debitQuantity = String.valueOf(rs.getCell(17, i).getContents());//借方数量 bodyMap.put("debitQuantity", debitQuantity);String primaryDebitAmount = String.valueOf(rs.getCell(13, i).getContents());//借方金额 bodyMap.put("primaryDebitAmount", primaryDebitAmount);/* String secondaryDebitAmount = "";//二次借方金额bodylist.add(secondaryDebitAmount);*/String naturalDebitCurrency = String.valueOf(rs.getCell(15, i).getContents());//原币借方金额bodyMap.put("naturalDebitCurrency", naturalDebitCurrency);String creditQuantity = String.valueOf(rs.getCell(18, i).getContents());// 贷方数量 bodyMap.put("creditQuantity", creditQuantity);String primaryCreditAmount = String.valueOf(rs.getCell(14, i).getContents());// 贷方金额 bodyMap.put("primaryCreditAmount", primaryCreditAmount);/* String secondaryCreditAmount = "";//二次贷方金额 bodylist.add(secondaryCreditAmount);*/ String naturalCreditCurrency = String.valueOf(rs.getCell(16, i).getContents());// 原币贷方金额bodyMap.put("naturalCreditCurrency", naturalCreditCurrency);/*String billType = "";//票据类型 bodylist.add(billType);String billId = "";// bodylist.add(billId);String billDate = "";// bodylist.add(billDate);*/List<Map<String,String>> adjustlist = new ArrayList<Map<String,String>>();for(int h=19;h<columnNum;h+=2){Map<String,String> adjustMap = new HashMap<String,String>();String auxiliaryCode = String.valueOf(rs.getCell(h, i).getContents());// 客商辅助核算String auxiliaryType = String.valueOf(rs.getCell(h, 0).getContents());adjustMap.put("auxiliaryCode", auxiliaryCode);adjustMap.put("auxiliaryType", auxiliaryType);adjustlist.add(adjustMap);}bodyMap.put("adjust", adjustlist);/* String auxiliaryMerchants = String.valueOf(rs.getCell(19, i).getContents());// 客商辅助核算bodyMap.put("auxiliaryMerchants", auxiliaryMerchants);String auxiliaryProject = String.valueOf(rs.getCell(21, i).getContents());//项目辅助核算bodyMap.put("auxiliaryProject", auxiliaryProject);String auxiliaryContract = String.valueOf(rs.getCell(23, i).getContents());//招标合同编码辅助核算bodyMap.put("auxiliaryContract", auxiliaryContract);*/bodylist.add(bodyMap);map.put("body"+i, bodylist);is.close();}}} catch (FileNotFoundException e) {// TODO 自动生成的 catch 块e.printStackTrace();} catch (BiffException e) {// TODO 自动生成的 catch 块e.printStackTrace();} catch (IOException e) {// TODO 自动生成的 catch 块e.printStackTrace();}return map;}
import java.awt.Container;import java.awt.event.ActionEvent;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStreamWriter;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.swing.JFileChooser;import jxl.Workbook;import jxl.read.biff.BiffException;import nc.ui.pub.beans.MessageDialog;import nc.ui.pubapp.uif2app.model.BillManageModel;import nc.ui.uif2.NCAction;/*** excel转换成XML按钮事件* @author ChenSiyi**/public class TransitionAction extends NCAction{/*** 给按钮设置唯一编码以及按钮名称*/public TransitionAction() {super();this.setCode("WDDD-TRANSITION");this.setBtnName("EXCEl转换成XML");}private BillManageModel model;public BillManageModel getModel() {return model;}public void setModel(BillManageModel model) {this.model = model;}@Overridepublic void doAction(ActionEvent arg0) throws Exception {// TODO 自动生成的方法存根/*用户自由选择文件*/JFileChooser fileChooser = new JFileChooser("D:\\");/* 设置 JFileChooser，以允许用户只选择文件、只选择目录，或者可选择文件和目录。* mode参数：FILES_AND_DIRECTORIES 指示显示文件和目录。FILES_ONLY 指示仅显示文件。DIRECTORIES_ONLY 指示仅显示目录。*/fileChooser.setFileSelectionMode(JFileChooser.FILES_AND_DIRECTORIES);/*弹出一个 "Open File" 文件选择器对话框。showSaveDialog:弹出一个 "Save File" 文件选择器对话框 */int returnVal = fileChooser.showOpenDialog(fileChooser);/*fileChooser.showDialog(new JLabel(), "选择");修改弹框按钮文字内容*/if(returnVal == JFileChooser.APPROVE_OPTION){/*getSelectedFile：返回选中的文件*/File file = fileChooser.getSelectedFile();String filePath= fileChooser.getSelectedFile().getAbsolutePath().replace(file.getName(), "");//转换成XMLOutputStreamWriter pw = null;Map<String,List<Map<String,Object>>> map = readExcelFileOutList(file);File fi = new File(filePath,"华西集.xml");if (!fi.exists()) {fi.createNewFile();}//确认流的输出文件和编码格式，此过程创建了实例pw = new OutputStreamWriter(new FileOutputStream(fi),"gbk");String companyCode = null;for(String str:map.keySet()){if(str.indexOf("head")!=-1){List<Map<String,Object>> list = map.get(str);for(int i=0;i<list.size();i++){Map<String,Object> headMap = list.get(i);companyCode = headMap.get("companyCode").toString();}}}pw.write("<?xml version='1.0' encoding='gbk'?>"+"\n");pw.write("<ufinterface billtype='gl' codeexchanged='y' docid='989898989898' proc='add' receiver='"+companyCode+"' roottag='voucher' sender='003'>"+"\n"); pw.write("<voucher id='1495A1100000000273E7'>"+"\n");pw.write("<voucher_head>"+"\n");for(String str:map.keySet()){if(str.indexOf("head")!=-1){List<Map<String,Object>> list = map.get(str);for(int i=0;i<list.size();i++){Map<String,Object> headMap = list.get(i); pw.write("<company>"+headMap.get("company")+"</company>"+"\n");pw.write("<voucher_type>"+headMap.get("voucherType")+"</voucher_type>"+"\n"); pw.write("<fiscal_year>"+headMap.get("fiscalYear")+"</fiscal_year>"+"\n");pw.write("<accounting_period>"+headMap.get("accountingPeriod")+"</accounting_period>"+"\n");pw.write("<voucher_id>"+headMap.get("voucherId")+"</voucher_id>"+"\n");pw.write("<attachment_number>"+headMap.get("attachmentNumber")+"</attachment_number>"+"\n");pw.write("<prepareddate>"+headMap.get("prepareddate")+"</prepareddate>"+"\n");pw.write("<date>"+headMap.get("date")+"</date>"+"\n");pw.write("<enter>"+headMap.get("enter")+"</enter>"+"\n");pw.write("<cashier></enter>"+"\n");pw.write("<signature>N</signature>"+"\n");pw.write("<checker></checker>"+"\n");pw.write("<posting_date></posting_date>"+"\n");pw.write("<posting_person></posting_person>"+"\n");pw.write("<voucher_making_system>总账</voucher_making_system>"+"\n");pw.write("<memo1></memo1>"+"\n");pw.write("<memo2></memo2>"+"\n");pw.write("<reserve1></reserve1>"+"\n");pw.write("<reserve2>N</reserve2>"+"\n");pw.write("<revokeflag />"+"\n");}}}pw.write("</voucher_head>"+"\n");pw.write("<voucher_body>"+"\n");for(String str:map.keySet()){if(str.indexOf("body")!=-1){List<Map<String,Object>> list = map.get(str);for(int i=0;i<list.size();i++){Map<String,Object> bodyMap = list.get(i); pw.write("<entry>"+"\n");pw.write("<entry_id>"+bodyMap.get("entryId")+"</entry_id>"+"\n");pw.write("<account_code>"+bodyMap.get("accountCode")+"</account_code>"+"\n");pw.write("<settlement></settlement>"+"\n");pw.write("<document_id></document_id>"+"\n");pw.write("<document_date></document_date>"+"\n");pw.write("<currency>"+bodyMap.get("currency")+"</currency>"+"\n");pw.write("<unit_price>"+bodyMap.get("unitPrice")+"</unit_price>"+"\n");pw.write("<exchange_rate1>"+bodyMap.get("exchangeRate1")+"</exchange_rate1>"+"\n");pw.write("<exchange_rate2>"+bodyMap.get("exchangeRate2")+"</exchange_rate2>"+"\n");pw.write("<debit_quantity>"+bodyMap.get("debitQuantity")+"</debit_quantity>"+"\n");pw.write("<primary_debit_amount>"+bodyMap.get("primaryDebitAmount")+"</primary_debit_amount>"+"\n");pw.write("<secondary_debit_amount>0.00</secondary_debit_amount>"+"\n");pw.write("<natural_debit_currency>"+bodyMap.get("naturalDebitCurrency")+"</natural_debit_currency>"+"\n");pw.write("<credit_quantity>"+bodyMap.get("creditQuantity")+"</credit_quantity>"+"\n");pw.write("<primary_credit_amount>"+bodyMap.get("primaryCreditAmount")+"</primary_credit_amount>"+"\n");pw.write("<secondary_credit_amount>0.00</secondary_credit_amount>"+"\n");pw.write("<natural_credit_currency>"+bodyMap.get("naturalCreditCurrency")+"</natural_credit_currency>"+"\n");pw.write("<bill_type></bill_type>"+"\n");pw.write("<bill_id></bill_id>"+"\n");pw.write("<bill_date></bill_date>"+"\n");pw.write("<auxiliary_accounting>"+"\n");List<Map<String,String>> adjustlist = (List<Map<String, String>>) bodyMap.get("adjust");for(int h=0;h<adjustlist.size();h++){Map<String,String> adjust = adjustlist.get(h);pw.write("<item name="+adjust.get("auxiliaryType")+">"+adjust.get("auxiliaryCode")+"</item>"+"\n");}pw.write("</auxiliary_accounting>"+"\n");/*pw.write("<auxiliary_accounting>"+"\n");pw.write("<item name='客商辅助核算'>"+bodyMap.get("auxiliaryMerchants")+"</item>"+"\n");pw.write("<item name='项目辅助核算'>"+bodyMap.get("auxiliaryProject")+"</item>"+"\n");pw.write("<item name='招标合同编码辅助核算'>"+bodyMap.get("auxiliaryContract")+"</item>"+"\n");pw.write("</auxiliary_accounting>"+"\n");*/pw.write("<detail></detail>"+"\n");pw.write("</entry>"+"\n");}}}pw.write("</voucher_body>"+"\n");pw.write("</voucher>"+"\n");pw.write("</ufinterface>"+"\n");MessageDialog.showOkCancelDlg(new Container(), "提示", "转换成功，转换路径为："+filePath);pw.flush();pw.close();//关闭流}}
