NC 按照查询模板导出

前台代码

import java.awt.Color;
import java.awt.event.ActionEvent;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.prefs.Preferences;

import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;

import nc.bs.framework.common.NCLocator;
import nc.itf.hrhi.IHiofferMaintain;
//import nc.itf.hrhi.ITmppsndocMaintain;
import nc.ui.ml.NCLangRes;
import nc.ui.pub.beans.UIFileChooser;
import nc.ui.pub.bill.BillItem;
import nc.ui.pubapp.uif2app.query2.model.ModelDataManager;
import nc.ui.pubapp.uif2app.view.BillForm;
import nc.ui.querytemplate.querytree.IQueryScheme;
import nc.ui.trade.excelimport.parser.IFileParserConstants;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.ShowStatusBarMsgUtil;
import nc.ui.uif2.model.AbstractUIAppModel;
import nc.vo.hrhi.hioffer.AggHiOffer;
import nc.vo.hrhi.hioffer.HiOffer;
//import nc.vo.hrhi.tmppsndoc.AggTmppsndoc;
//import nc.vo.hrhi.tmppsndoc.TmppsndocVO;
import nc.vo.pub.lang.UFBoolean;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 单据双击节点初始化数据公共方法处理类
 * 
 * @author dck
 * 
 */
public class DataToExcleAction extends NCAction {
	public static final String XLSX_SUFFIX = IFileParserConstants.XLSX_SUFFIX;
	public static final String KEY_DEFAULT_DIR = "default";
	private AbstractUIAppModel model;
	private UIFileChooser chooser;
	// 为了自动记住上次用户选择的文件目录
	private Preferences preferences;
	private BillForm billform;
	private ModelDataManager dataManager;
	
	public DataToExcleAction() {
		super();
		super.setBtnName("导出数据");
		super.setCode("outputExcel");
	}
	@Override
	public void doAction(ActionEvent e) throws Exception {
		IQueryScheme queryScheme = getDataManager().getQueryScheme();
		if (queryScheme == null) {
			ExceptionUtils.wrappBusinessException("请先执行查询");
			return;
		}
		AggHiOffer[] aggs = NCLocator.getInstance().lookup(IHiofferMaintain.class).query(getDataManager().getQueryScheme());
		int filechoose = getFileChooser().showOpenDialog(this.getModel().getContext().getEntranceUI());
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		int i = (int) (Math.random()*100000);
		if (filechoose == JFileChooser.APPROVE_OPTION) {
			String filepath = getFileChooser().getSelectedFile().getPath();
			XSSFWorkbook book = writeExcel(aggs);
			FileOutputStream fos = new FileOutputStream(filepath+"//tmppsndoc-"+sdf.format(new Date())+String.valueOf(i)+".xlsx");
			//FileOutputStream fos = new FileOutputStream("${nchome}/resources/offertemplate/offertemplate.xlsx");
			
			book.write(fos);
			ShowStatusBarMsgUtil.showStatusBarMsg("执行成功！", getModel().getContext());
		}
		
	}

	private XSSFWorkbook writeExcel(AggHiOffer[] aggvos) throws Exception {
		

		Map<String, Map<String, String>> kenmap = getmap();
		//银行类别
		Map<String, String> pk_banktypeMap =  kenmap.get("pk_banktypeMap");
	
				
		
		HashMap<String,Map<String,String>> typemap = new HashMap<String,Map<String,String>> ();
		
		typemap.put("银行类别", pk_banktypeMap);
	
		
		//usermap user_name,cuserid
		List<Integer> valList = new ArrayList<Integer>();
		HashMap<Integer,String> typemap2 = new HashMap<Integer,String>();
		
		BillItem[] items = billform.getBillCardPanel().getBillData().getHeadShowItems();
		String[] itemName = new String[items.length];
		String[] itemkey = new String[items.length];
		for (int i = 0; i < items.length; i++) {
			itemkey[i]=items[i].getKey();
			itemName[i]=items[i].getName();
			if(typemap.containsKey(items[i].getName())){
				typemap2.put(i,items[i].getName());
				valList.add(i);
			}
		}

		
		XSSFWorkbook book = new XSSFWorkbook();
		XSSFSheet sheet = book.createSheet();
		XSSFRow headRow = sheet.createRow(0);
		for (int j = 0; j < itemkey.length; j++) {
			
			XSSFCell cell = headRow.createCell(j);
			cell.setCellValue(itemName[j]);
		}
		
		for (int k =0; k < aggvos.length; k++) {
			XSSFRow itemRow = sheet.createRow(k+1);
			for (int l = 0; l< itemkey.length; l++) {
				XSSFCell cell = itemRow.createCell(l);
				PropertyDescriptor pd = new PropertyDescriptor(itemkey[l], HiOffer.class);
				Object value = pd.getReadMethod().invoke(aggvos[k].getParentVO());
		
				if (value instanceof String) {
					cell.setCellValue(value.toString());
				}
				if (value instanceof Integer) {
					cell.setCellValue((Integer)value);
				}
				if (value instanceof UFBoolean) {
					UFBoolean b = (UFBoolean)value;
					cell.setCellValue(b.booleanValue());
				}
				if (value instanceof UFDate) {
					UFDate d = (UFDate)value;
					cell.setCellValue(d.toStdString());
				}
				if (value instanceof UFDateTime) {
					UFDateTime d = (UFDateTime)value;
					cell.setCellValue(d.toStdString());
				}
				if (value instanceof UFDouble) {
					UFDouble d = (UFDouble)value;
					cell.setCellValue(d.toString());
				}
				if (value instanceof Double) {
					Double d = (Double)value;
					cell.setCellValue(d.toString());
				}
				if(typemap2.containsKey(l)){
					Map<String, String> map =  typemap.get(typemap2.get(l));
					cell.setCellValue(map.get(value));
				}
				
		}
		}
		return book;
	}
		
	protected UIFileChooser getFileChooser() {
		if (chooser == null) {
			chooser = new ExcelUIFileChooser();
			chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
			chooser.setAcceptAllFileFilterUsed(false);

			chooser.addChoosableFileFilter(new FileFilter() {

				public boolean accept(File f) {
					return f.isDirectory() || isXLSFile(f, XLSX_SUFFIX);
				}

				@Override
				public String getDescription() {
					return null;
							/*NCLangRes.getInstance().getStrByID("excelimport",
							"ExcelImporter-000013") "Excel97_2007工作薄(*.xlsx)" ;*/
				}
			});
		}
		// 用户选择文件目录偏好
		String preferencesDir = getPreferences().get(KEY_DEFAULT_DIR,
				System.getProperty("user.dir"));
		chooser.setCurrentDirectory(new File(preferencesDir));
		return chooser;
	}
	

	public AbstractUIAppModel getModel() {
		return model;
	}

	public void setModel(AbstractUIAppModel model) {
		this.model = model;
	}

	public UIFileChooser getChooser() {
		return chooser;
	}

	public void setChooser(UIFileChooser chooser) {
		this.chooser = chooser;
	}

	public Preferences getPreferences() {
		if (preferences == null) {
			preferences = Preferences.userNodeForPackage(this.getClass());
		}
		return preferences;
	}

	public void setPreferences(Preferences preferences) {
		this.preferences = preferences;
	}

	/**
	 * 
	 * 由于jdk1.7的变化，导致导出格式首选项是CSV，覆写这个类解决这个问题
	 * 
	 * @author hulianga
	 */
	private class ExcelUIFileChooser extends UIFileChooser {

		private static final long serialVersionUID = 5573628911848324562L;

		@Override
		public void addChoosableFileFilter(FileFilter filter) {

			super.addChoosableFileFilter(filter);
			setFileFilter(filter);
		}
	}

	private static boolean isXLSFile(File file, String suffix) {
		String name = file.getName().toLowerCase();
		return name.endsWith(suffix);
	}

	public BillForm getBillform() {
		return billform;
	}

	public void setBillform(BillForm billform) {
		this.billform = billform;
	}

	public ModelDataManager getDataManager() {
		return dataManager;
	}

	public void setDataManager(ModelDataManager dataManager) {
		this.dataManager = dataManager;
	}
	public Map<String, Map<String, String>> getmap() throws Exception{
		IHiofferMaintain service = NCLocator.getInstance().lookup(IHiofferMaintain.class);
		return service.getMapList();
	}
	

	@Override
	public boolean isActionEnable() 
	{
		if (UpdatteOfferAction.flag == 0) {
			return false;
		}
		return true;
	} 
	
}

后台获取参照样例

public  Map<String, Map<String, String>> getMapList() throws Exception{
	//获取银行类别参照
			String sql1 = "select pk_banktype,name,code from  bd_banktype";
			Map<String, String>  pk_banktypeMap = new HashMap<String, String>();
			@SuppressWarnings("unchecked")
			List<Map<String, String>> pk_banktypeList = (List<Map<String, String>>) getDao().executeQuery(sql1, new MapListProcessor());
			for(int i=0;i<pk_banktypeList.size();i++){
				pk_banktypeMap.put(pk_banktypeList.get(i).get("pk_banktype"),pk_banktypeList.get(i).get("name"));
			}
	Map<String, Map<String, String>>  retmap = new HashMap<String,Map<String,String>>(); 
retmap.put("Pk_ContractMap",Pk_ContractMap);
return retmap ;
}