系统自带导出（导出效率很高）

和安韩Pro

已于 2023-03-30 10:02:19 修改

阅读量144

点赞数

分类专栏： nc积累文章标签： java

于 2023-03-29 16:53:08 首次发布

本文链接：https://blog.csdn.net/weixin_45714641/article/details/129840512

版权

nc积累专栏收录该内容

72 篇文章 6 订阅

订阅专栏

该代码段展示了一个Java类`ExportZfbxdAction`，用于从系统中导出支付报销单数据到Excel文件。它使用了ApachePOI库来创建和操作Excel工作簿，并包含了数据转换和格式化的过程。此外，还涉及到了对话框`ExportDialog`来选择导出文件的位置。

摘要由CSDN通过智能技术生成

系统自带导出（导出效率很高）
1.ExportZfbxdAction.java

package nc.ui.jych.zfbx.ace.action;

import java.awt.event.ActionEvent;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import nc.ui.jych.zfbx.ace.dialog.ExportDialog;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pubapp.uif2app.model.BillManageModel;
import nc.ui.pubapp.uif2app.view.ShowUpableBillForm;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.ShowStatusBarMsgUtil;
import nc.vo.jych.zfbx.AggZfbxVO;
import nc.vo.jych.zfbx.ZfbxVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVOUtil;
import nc.vo.pub.lang.UFBoolean;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDouble;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import nc.vo.wgbfb.tool.NullValueUtils;

import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 支付报销单导出
 * @author hh
 * @date 2023-03-29
 */
public class ExportZfbxdAction extends NCAction{

	private static final long serialVersionUID = 1268844809623831465L;
	private BillManageModel model;
	protected ShowUpableBillForm editor;

	public ExportZfbxdAction(){
		setBtnName("导出");
		setCode("exportAction");
	}
	
	@Override
	public void doAction(ActionEvent arg0) throws Exception {
		// TODO 自动生成的方法存根
		Object[] aggvos = this.getModel().getSelectedOperaDatas();
		if(aggvos == null || aggvos.length < 1){
			throw new BusinessException(" 未选择导出数据！ ");
		}
		ExportDialog fileDlg = new ExportDialog();
		if (fileDlg.show()) {
			// 得到导出的EXCEL文件路径
			String filepath = fileDlg.getFilePath();
			if (filepath == null) {
				ShowStatusBarMsgUtil.showErrorMsg("提示", "请选择导出的文件！", getModel()
						.getContext());// 最下面红色显示
				return;
			}
			try {
				export(filepath, aggvos);
				MessageDialog.showHintDlg(null, "", "导出完成！");
			} catch (Exception e) {
				ExceptionUtils.wrappBusinessException("程序出错:" + e.getMessage());
			}
		}
	}
	
	private void export(String filepath, Object[] aggvos) throws BusinessException {
		// TODO 自动生成的方法存根
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100);
		// 创建工作表
		SXSSFSheet wbSheet = (SXSSFSheet) wb.createSheet(getExcelSheetName());
		// 设置默认行宽
		wbSheet.setDefaultColumnWidth(10);
		// 标题样式（加粗，垂直居中）
		XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
		XSSFFont fontStyle = (XSSFFont) wb.createFont();
		fontStyle.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		fontStyle.setFontHeightInPoints((short) 16); // 设置标题字体大小
		cellStyle.setFont(fontStyle);
		// 设置表头样式，表头居中
		XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
		// 设置单元格样式
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 设置字体
		XSSFFont font = (XSSFFont) wb.createFont();
		font.setFontHeightInPoints((short) 9);
		style.setFont(font);
		// 在第1行创建rows
		SXSSFRow row = (SXSSFRow) wbSheet.createRow((int) 0);
		// 设置列头元素
		SXSSFCell cellHead2 = null;
		for (int i = 0; i < getExportFieldsName().length; i++) {
			cellHead2 = (SXSSFCell) row.createCell(i);
			cellHead2.setCellValue(getExportFieldsName()[i]);
			cellHead2.setCellStyle(style);
		}
		List<ZfbxVO> list = new ArrayList<ZfbxVO>();
		for(Object obj : aggvos){
			AggZfbxVO aggvo = (AggZfbxVO)obj;
			ZfbxVO headvo = aggvo.getParentVO();
			list.add(headvo);
		}
		ZfbxVO[] hvos = list.toArray(new ZfbxVO[0]);
	
		ZfbxVO[] vos = execFormulaWithVOs(hvos);
		if(vos!=null && vos.length>0){
			for (int i = 0; i < vos.length; i++) {
				SXSSFRow row3 = (SXSSFRow) wbSheet.createRow((int) i+1);
				ZfbxVO vo = vos[i];
				// 设置列头元素
				SXSSFCell cellHeads = null;
				// 写入数据内容
				List<Object> valuelist=new ArrayList<Object>();
				//枚举翻译
				String jsjg = vo.getJsjg();
				if(jsjg!=null){
					if("1".equals(jsjg)){
						jsjg = "公司";
					}else if("2".equals(jsjg)){
						jsjg = "分公司";
					}
				}
				String cddwtype = vo.getCddwtype();
				if(cddwtype!=null){
					if("1".equals(cddwtype)){
						cddwtype = "项目";
					}else if("2".equals(cddwtype)){
						cddwtype = "部门";
					}
				}
				
				valuelist.add(0, NullValueUtils.getNullStringValue(vo.getDbilldate().toStdString().substring(0, 10)));
				valuelist.add(1, NullValueUtils.getNullStringValue(vo.getPk_bxr()));
				valuelist.add(2, NullValueUtils.getNullStringValue(vo.getPk_bxdept()));
				valuelist.add(3, NullValueUtils.getNullStringValue(vo.getPk_currtype()));
				valuelist.add(4, NullValueUtils.getNullUFdoubleValue(vo.getBbhl()));
				valuelist.add(5, NullValueUtils.getNullUFdoubleValue(vo.getYbje()));
				valuelist.add(6, NullValueUtils.getNullUFdoubleValue(vo.getBbje()));
				valuelist.add(7, NullValueUtils.getNullUFdoubleValue(vo.getHtje()));
				valuelist.add(8, NullValueUtils.getNullUFdoubleValue(vo.getBillnum()));
				valuelist.add(9, transferYN(vo.getIshjk()));
				valuelist.add(10, NullValueUtils.getNullStringValue(vo.getDyjkbillno()));
				valuelist.add(11, NullValueUtils.getNullUFdoubleValue(vo.getJkje()));
				valuelist.add(12, NullValueUtils.getNullUFdoubleValue(vo.getSywhkje()));
				valuelist.add(13, NullValueUtils.getNullUFdoubleValue(vo.getYhkje()));
				valuelist.add(14, NullValueUtils.getNullUFdoubleValue(vo.getBcpjje()));
				valuelist.add(15, NullValueUtils.getNullUFdoubleValue(vo.getBjhkje()));
				valuelist.add(16, NullValueUtils.getNullUFdoubleValue(vo.getThgsje()));
				valuelist.add(17, NullValueUtils.getNullUFdoubleValue(vo.getBczfje()));
				valuelist.add(18, NullValueUtils.getNullStringValue(vo.getSkrtype()));
				valuelist.add(19, NullValueUtils.getNullStringValue(vo.getPk_supplier()));
				valuelist.add(20, NullValueUtils.getNullStringValue(vo.getPk_psndoc()));
				valuelist.add(21, NullValueUtils.getNullStringValue(vo.getSupplieryhzh()));
				valuelist.add(22, NullValueUtils.getNullStringValue(vo.getSupllierkhh()));
				valuelist.add(23, NullValueUtils.getNullStringValue(vo.getPsndocyhzh()));
				valuelist.add(24, NullValueUtils.getNullStringValue(vo.getPsndockhh()));
				valuelist.add(25, NullValueUtils.getNullStringValue(vo.getFktype()));
				valuelist.add(26, NullValueUtils.getNullStringValue(jsjg));
				valuelist.add(27, NullValueUtils.getNullStringValue(vo.getMemo()));
				valuelist.add(28, NullValueUtils.getNullStringValue(cddwtype));			
				valuelist.add(29, transferYN(vo.getIsyxxm()));
				valuelist.add(30, NullValueUtils.getNullStringValue(vo.getPk_project()));
				valuelist.add(31, NullValueUtils.getNullStringValue(vo.getPk_project_dept()));
				valuelist.add(32, NullValueUtils.getNullStringValue(vo.getPk_project_psndoc()));
				valuelist.add(33, NullValueUtils.getNullStringValue(vo.getProjecttype()));
				valuelist.add(34, NullValueUtils.getNullStringValue(vo.getHsdw()));
				valuelist.add(35, NullValueUtils.getNullStringValue(vo.getPk_account()));
				valuelist.add(36, NullValueUtils.getNullStringValue(vo.getDef10()));
				valuelist.add(37, NullValueUtils.getNullStringValue(vo.getCreationtime()));
				valuelist.add(38, NullValueUtils.getNullStringValue(vo.getCreator()));
				valuelist.add(39, NullValueUtils.getNullStringValue(vo.getApprover()));
				valuelist.add(40, NullValueUtils.getNullStringValue(vo.getApprovedate()));
				valuelist.add(41, NullValueUtils.getNullStringValue(vo.getBxtype()));
				valuelist.add(42, NullValueUtils.getNullStringValue(vo.getVbillno()));
				valuelist.add(43, NullValueUtils.getNullStringValue(vo.getDef3()));
				valuelist.add(44, NullValueUtils.getNullStringValue(vo.getDef4()));
				
				Object[] fieldsValue=valuelist.toArray(new Object[valuelist.size()]);
				// 开始写入数据，j为列值
				for (int j = 0; j < fieldsValue.length; j++) {
					cellHeads = (SXSSFCell) row3.createCell(j);
					if(fieldsValue[j] instanceof UFDouble){
						if(new UFDouble(fieldsValue[j].toString()).compareTo(UFDouble.ZERO_DBL) == 0){
						}else{
							cellHeads.setCellValue(new UFDouble(fieldsValue[j].toString()).toDouble());
						}
					}else{
						cellHeads.setCellValue(fieldsValue[j].toString());
					}
				}
			}
		}
		try {
			SimpleDateFormat timeformat = new SimpleDateFormat("yyyyMMdd HHmmss");
			String format = timeformat.format(new Date());
			FileOutputStream fout = new FileOutputStream(filepath
					+ "\\支付报销单"
					+ format + ".xlsx");
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			// TODO 自动生成的 catch 块
			throw new BusinessException("生成excel出错：" + e.getMessage());
		}
	}
	
	/**
	 * @desc:档案翻译
	 * @author:hh
	 * @date :2023年3月29日
	 */
	private ZfbxVO[] execFormulaWithVOs(ZfbxVO[] hvos) {
		ZfbxVO[] clonevos = hvos.clone();
		SuperVOUtil.execFormulaWithVOs(clonevos, new String[] {
				//报销人
				"pk_bxr->getColValue(bd_psndoc,name,pk_psndoc,pk_bxr)",
				//报销部门  org_dept
				"pk_bxdept->getColValue(org_dept,name,pk_dept,pk_bxdept)",
				//币种 bd_curritype
				"pk_currtype->getColValue(bd_currtype,name,pk_currtype,pk_currtype)",
				//收款供应商  bd_customer
				"pk_supplier->getColValue(bd_customer,name,pk_customer,pk_supplier)",
				//收款个人 bd_psndoc
				"pk_psndoc->getColValue(bd_psndoc,name,pk_psndoc,pk_psndoc)",
				//科研项目   pk_project 自定义档案 科技项目和营销项目对应的bd_defdoc中的name都是智控科技项目，pk都是 1001A1100000000007ZR
				"pk_project->getColValue(bd_defdoc,name,pk_defdoc,pk_project)",
				//项目所属部门 org_dept
				"pk_project_dept->getColValue(org_dept,name,pk_dept,pk_project_dept)",
				//项目经理 pk_project_psndoc
				"pk_project_psndoc->getColValue(bd_psndoc,name,pk_psndoc,pk_project_psndoc)",
				//核算单位
				"hsdw->getColValue(org_dept,name,pk_dept,hsdw)",
				//会计科目编码 def10
				"def10->getColValue(bd_account,code,pk_account,getColValue(bd_accasoa,pk_account,pk_accasoa,pk_account))",
				//会计科目  vo中的pk_account是会计科目表的主键，会计科目表bd_accasoa联查会计科目基本信息表bd_account,取得进本信息表的name、code
				"pk_account->getColValue(bd_accasoa,dispname,pk_accasoa,pk_account)",
				//制单人
				"creator->getColValue(sm_user,user_name,cuserid,creator)",
				//审批人
				"approver->getColValue(sm_user,user_name,cuserid,approver)"
		});
		return clonevos;
	}
	
	private String[] getExportFieldsName() {
		return new String[] {"单据日期", "报销人", "报销部门", "币种",
				"本币汇率","原币金额","本币金额","核定金额","核定单据张数","是否还借款",
				"对应借款单据号","借款金额","剩余未还款金额","已还款金额","本次票据金额","本次还款金额",
				"退换公司金额","本次支付金额","收款人类型","收款供应商","收款个人","供应商银行账户",
				"供应商开户行","个人银行账户","个人开户行","付款方式","结算机构","备注",
				"承担单位类型","是否营销项目","科研项目","项目所属部门","项目经理","项目类型",
				"核算单位","会计科目名称","会计科目编码" ,"制单时间","制单人","审批人",
				"审批时间","报销类型","单据号","oa单据号","计提费用报销单据号"}; 
	}
	
	private String transferYN(UFBoolean obj){
		if(obj==null){
			return "否";
		}else{
			if(UFBoolean.TRUE.equals(obj)){
				return "是";
			}else{
				return "否";
			}
		}
		
	}
	private String getExcelSheetName() {
		String date = new UFDate().toString().substring(0, 10);
		return "支付报销单" + date;
	}
	public BillManageModel getModel() {
		return model;
	}
	public void setModel(BillManageModel model) {
		this.model = model;
		this.model.addAppEventListener(this);
	}
	public ShowUpableBillForm getEditor() {
		return editor;
	}
	public void setEditor(ShowUpableBillForm editor) {
		this.editor = editor;
	}
	
}

2.ExportDialog.java

package nc.ui.jych.zfbx.ace.dialog;

import javax.swing.JFileChooser;

public class ExportDialog {

	JFileChooser jFileChooser = new JFileChooser();

	public ExportDialog() {
		super();
	}
	
	public boolean show(){
		jFileChooser.removeChoosableFileFilter(jFileChooser.getAcceptAllFileFilter());
		
		jFileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);

		return JFileChooser.APPROVE_OPTION == jFileChooser.showOpenDialog(null);
	}

	public String getFilePath(){
		return jFileChooser.getSelectedFile().getPath();
	}
}

3.Zfbx_config.xml
列表下

<ref bean="exportImport" />

导出按钮

	<!-- 导出-->
	<bean id="exportImport" class="nc.ui.jych.zfbx.ace.action.ExportZfbxdAction">
		<property name="model"><ref bean="bmModel"/></property>
		<property name="editor"><ref bean="billForm"/></property>
	</bean>