把Excel转换成xml文件

 

	/**
	 * 读取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;
	}

	@Override
	public 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(), "");
			
			//转换成XML
			OutputStreamWriter 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();//关闭流
		}
		
		
		
	}