U8 cloud按钮导入功能的实现

• 读取模板

  需要将模板放置到resources目录下，并且在private进行读取模板IO

@Override
	public  byte[] getExportTemplate() throws Exception {
	//需要将模板IO转换为byte[]字节数组才可以传递到client端
		 byte[] buffer = null;
		    try {
		      FileInputStream fis = new FileInputStream("/门店奖金分配.xlsx");
		      ByteArrayOutputStream baos = new ByteArrayOutputStream();
		      byte[] buf = new byte[1024];
		      int n;
		      while ((n = fis.read(buf)) != -1) {
		        baos.write(buf, 0, n);
		      }
		      fis.close();
		      baos.flush();
		      buffer = baos.toByteArray();
		      baos.close();
		    } catch(IOException e) {
		      e.printStackTrace();
		    }

		    return buffer;
	}

• 字节数组转换为Excel

static IGetExportTemplate iet = null;
    public static Workbook getExcelFile() throws Exception {
    	if(iet==null) {
    		iet = (IGetExportTemplate) NCLocator.getInstance().lookup(IGetExportTemplate.class);
    		
    	}

    	if (buffer==null) {
    		buffer = iet.getExportTemplate();
		}
		 ByteArrayInputStream bais = new ByteArrayInputStream(buffer);
		 Workbook workbook = WorkbookFactory.create(bais);
		 bais.close();
		 return workbook;
    	
    }

• Java操作Excel
• 导入操作

package u8c.ui.pe.action;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Map;

import javax.swing.filechooser.FileNameExtensionFilter;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import nc.bs.framework.common.NCLocator;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.BeanProcessor;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.jdbc.framework.processor.MapProcessor;
import nc.ui.hr.frame.FrameUI;
import nc.ui.hr.frame.action.AbstractAction;
import nc.ui.ml.NCLangRes;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.beans.UIFileChooser;
import nc.ui.pub.bill.BillCardPanel;
import nc.ui.pub.bill.BillItem;
import nc.ui.pub.bill.BillModel;
import nc.vo.bd.CorpVO;
import nc.vo.pub.AggregatedValueObject;
import nc.vo.pub.CircularlyAccessibleValueObject;
import nc.vo.pub.ValidationException;
import nc.vo.pub.lang.UFDate;
import u8c.itf.hr.lb.IGetExportTemplate;
import u8c.itf.hr.lb.INewPayTotalInfo;
import u8c.ui.excel.ExcelFileProcess;
import u8c.ui.pe.datamodel.MdDataModel;
import u8c.ui.pe.panel.MdUI;
import u8c.vo.hr.pe_001.PerformanceVO;
import u8c.vo.pe.StoreMainVO;
import u8c.vo.pe.StoreSubVO;

public class MdExportAction extends AbstractAction {

	MdUI ui = null;
	private UIFileChooser chooser;

	public MdExportAction(FrameUI frameUI) {
		super(frameUI);
		this.ui = (MdUI) frameUI;
	}

	public void execute() throws Exception {
		/**   系统标准功能 - 可以进行模板的导出
		ExcelFileProcess excelFileProcess = new ExcelFileProcess(ui);
		excelFileProcess.downLoadDefaultFile(ic/采购入库单.xlsx);

		excelFileProcess.downLoadDefaultFile("md/门店.xlsx");
		ui.showHintMessage("导出...");
		loadDataExcel();
		*/
		/**
		BillCardPanel billCardPanel = ui.getBillCardPanel();
		BillItem[] bodyItems = billCardPanel.getBodyItems();
		String value = bodyItems[0].getKey();
		int i=1;
		*/
		
	}

	protected void loadDataExcel() throws ValidationException, Exception {
		BillCardPanel billCardPanel = ui.getBillCardPanel();
		if (!billCardPanel.isShowing()) {
			throw new ValidationException("非卡片状态不能导出");
		}
//		File file  = new File("E:\\U8CTR\\U8CERP\\resources\\md\\门店奖金分配.xlsx");
		Workbook workbook = ((MdDataModel)this.ui.getDataModel()).getExcelFile();
		if (workbook == null ) {
			throw new ValidationException("没有门店模板!");
		}

		
		
		AggregatedValueObject billValueVO = billCardPanel.getBillValueVO("u8c.vo.pe.AggStoreMainVO",
				"u8c.vo.pe.StoreMainVO", "u8c.vo.pe.StoreSubVO");
		StoreSubVO[] storeSubVOs = (StoreSubVO[]) billValueVO.getChildrenVO();
		StoreMainVO mainVO = (StoreMainVO) billValueVO.getParentVO();

		ArrayList<Object> resultList = new ArrayList<Object>();
		Field[] fields = mainVO.getClass().getDeclaredFields();
		for (int i = 0; i < 22; i++) {
			Field field = fields[i];
			String fieldName = field.getName();
			// 获取属性值
			field.setAccessible(true);
			Object fieldValue = field.get(mainVO);
			resultList.add(fieldValue);
		}
		
		Sheet sheet = workbook.getSheetAt(0);
		Row row = sheet.getRow(0);
		Cell cell = row.getCell(0);
		
		IUAPQueryBS iUAPQueryBS = (IUAPQueryBS)NCLocator.getInstance().lookup(IUAPQueryBS.class.getName());
		String sql="select deptname from bd_deptdoc where pk_deptdoc='"+mainVO.getStorename()+"'";
		Object storeName = iUAPQueryBS.executeQuery(sql, new ColumnProcessor());
		UFDate logDate = nc.ui.hr.global.Global.getLogDate();
		String subDate = logDate.toString();
		String date = subDate.substring(0, 7);
		StringBuffer titel = new StringBuffer();
//        titel.append(mainVO.get)
		
		
		titel.append(storeName).append("奖金").append(date).append("分配表");
		cell.setCellValue(titel.toString());
		int k=0;
		Row headRow = null;
		Cell headCell = null;
		for (int i = 1; i < 8; i++) {
			headRow = sheet.getRow(i);
			for (int j = 1; j < 6; j=j+2) {
				headCell = headRow.createCell(j);
				Object temp = resultList.get(k);
				if(temp!=null) {
					headCell.setCellValue(temp.toString());
				}
				k++;
			}
		}
		headRow=sheet.getRow(8);
		headCell=headRow.getCell(1);
		headCell.setCellValue(storeName.toString());
		headRow=sheet.getRow(8);
		headCell=headRow.getCell(3);
		headCell.setCellValue(mainVO.getPk_stroe_main());
		/**
		 * 增加单元格样式 太丑不要 CellStyle style = workbook.createCellStyle();
		 * style.setBorderTop(BorderStyle.THIN);
		 * style.setBorderBottom(BorderStyle.THIN);
		 * style.setBorderLeft(BorderStyle.THIN);
		 * style.setBorderRight(BorderStyle.THIN);
		 */
		
		
        
        StoreSubVO storeSubVO = null;
        /**   标题参照不显示主键所以不用使用
         * 
        StringBuilder sBuilder = new StringBuilder("SELECT pk_psndoc,psnname FROM bd_psndoc where pk_psndoc in(");
		StringBuilder sql="SELECT pk_psndoc,psnname FROM bd_psndoc where pk_psndoc in('0001ZZ10000000000OQE','0001ZZ10000000000OQG')";
		Map<String,String> performanceVO = (Map<String, String>)iUAPQueryBS.executeQuery(sql, new MapProcessor());
        ArrayList<String> rYArrayList= new ArrayList<String>();
		for (int i = 0; i < storeSubVOs.length-1; i++) {
			storeSubVO=storeSubVOs[i];
			sBuilder.append("'").append(storeSubVO.getPk_personnel()).append("',");
			
		}
		storeSubVO=storeSubVOs[storeSubVOs.length-1];
		sBuilder.append("'").append(storeSubVO.getPk_personnel()).append("')");
		*/
		Cell createCell=null;
		
		int y=0;
		for (int i = 11; i < 11 + storeSubVOs.length; i++) {
			Row createRow = sheet.createRow(i);
			storeSubVO=storeSubVOs[y];
			createCell= createRow.createCell(0);
			createCell.setCellValue(storeSubVO.getPsncode());
			createCell= createRow.createCell(1);
			createCell.setCellValue(storeSubVO.getRename());
			
			createRow.setHeightInPoints(20);
			y++;

		}

		// 选择保存位置
		UIFileChooser fileChooser = new UIFileChooser();
		fileChooser.setDialogTitle("导出门店奖金分配");
		fileChooser.setFileSelectionMode(UIFileChooser.DIRECTORIES_ONLY);

		// 限制文件类型为.xlsx
//		FileNameExtensionFilter filter = new FileNameExtensionFilter("Microsoft Excel文件(*.xlsx)", "xlsx");
//		fileChooser.setFileFilter(filter);

		int userSelection = fileChooser.showSaveDialog(null);
		if (userSelection == UIFileChooser.APPROVE_OPTION) {
			File fileToSave = fileChooser.getSelectedFile();
			if (fileToSave == null) {
				MessageDialog.showHintDlg(this.ui,
						NCLangRes.getInstance().getStrByID("excelimport", "UPPexcelimport-000030"),
						NCLangRes.getInstance().getStrByID("excelimport", "UPPexcelimport-000031"));
			}
			File outputFile = new File(fileToSave, "门店奖金分配.xlsx");
//			String absolutePath = fileToSave.getAbsolutePath();
//			absolutePath=absolutePath+"\\门店奖金分配.xlsx";
			FileOutputStream fos = new FileOutputStream(outputFile);
			workbook.write(fos);
			
			workbook.close();
			fos.close();
			MessageDialog.showHintDlg(this.ui, "提示", "导出门店奖金分配Excel成功");
			ui.showHintMessage("完成导出操作");
		}else {
			ui.showHintMessage("取消导出操作");
		}
		
	}

}

• 导出操作

package u8c.ui.pe.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;

import javax.swing.filechooser.FileNameExtensionFilter;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.sun.org.apache.bcel.internal.generic.NEW;

import nc.bs.framework.common.NCLocator;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.SQLParameter;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.ui.hr.frame.FrameUI;
import nc.ui.hr.frame.action.AbstractAction;
import nc.ui.hr.frame.button.AbstractBtnReg;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.beans.UIFileChooser;
import nc.ui.pub.bill.BillCardPanel;
import nc.ui.pub.bill.BillData;
import nc.vo.bd.CorpVO;
import nc.vo.pub.AggregatedValueObject;
import nc.vo.pub.ValidationException;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.pub.lang.UFDouble;
import nc.vo.trade.pub.IBillStatus;
import u8c.itf.hr.lb.IGetExportTemplate;
import u8c.itf.hr.lb.IOperateData;
import u8c.ui.excel.ExcelFileProcess;
import u8c.ui.pe.panel.MdUI;
import u8c.vo.pe.StoreMainVO;
import u8c.vo.pe.StoreSubVO;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class MdImportAction extends AbstractAction {

	MdUI ui = null;

	public MdImportAction(FrameUI frameUI) {
		super(frameUI);
		this.ui = (MdUI) frameUI;
	}

	public void execute() throws Exception {
//		ExcelFileProcess excelFileProcess = new ExcelFileProcess(ui);
//		excelFileProcess.downLoadDefaultFile("md/门店奖金分配.xlsx");
//		File file = new File("md/门店奖金分配.xlsx");
//		FileInputStream fileInputStream = new FileInputStream(file);
		UFDateTime ufDateTime = new UFDateTime(System.currentTimeMillis());
//		excelFileProcess.exportExcelData(billmark, serverName, ids);
		ui.showHintMessage("导入...");
		loadMdExcel();
		ui.showHintMessage("完成导入操作");
		int i = 1;

	}

	protected void loadMdExcel() throws Exception {
		BillCardPanel billCardPanel = ui.getBillCardPanel();
		AggregatedValueObject billValueVO = billCardPanel.getBillValueVO("u8c.vo.pe.AggStoreMainVO",
				"u8c.vo.pe.StoreMainVO", "u8c.vo.pe.StoreSubVO");
		StoreSubVO[] storeSubVOs = (StoreSubVO[]) billValueVO.getChildrenVO();
		StoreMainVO mainVO = (StoreMainVO) billValueVO.getParentVO();
		if(mainVO.getStates()!=null&&mainVO.getStates()!=IBillStatus.FREE) {
			throw new ValidationException("非编写状态不能导入");
		}
		StoreSubVO storeSubVO = null;
		// 创建文件选择器
		UIFileChooser fileChooser = new UIFileChooser();

		// 创建文件过滤器，仅允许选择扩展名为 ".xlsx" 和 ".xls" 的 Excel 文件
		FileNameExtensionFilter filter = new FileNameExtensionFilter("Excel Files", "xlsx");
		fileChooser.setFileFilter(filter);

		// 显示文件选择器对话框，获取用户选择的文件
		int result = fileChooser.showOpenDialog(null);
		if (result == UIFileChooser.APPROVE_OPTION) {
			// 用户点击了确定按钮，获取用户选择的文件
			File selectedFile = fileChooser.getSelectedFile();
			FileInputStream fis = new FileInputStream(selectedFile);
			Workbook workbook = new XSSFWorkbook(fis);
			// 获取第一个工作表
			Sheet sheet = workbook.getSheetAt(0);
			Row row = null;
			Cell cell = null;
			row = sheet.getRow(8);
			cell = row.getCell(3);
			String mainPk = cell.getStringCellValue();
			if (mainPk == null || !(mainVO.getPk_stroe_main().equals(mainPk))) {
				throw new ValidationException("Excel和当前单据不匹配");
			}
			int bodyRow = 11;
			cell = null;
			BigDecimal monverBd = new BigDecimal("0");
			BigDecimal incentBd = new BigDecimal("0");
			BigDecimal tablampBd = new BigDecimal("0");
			BigDecimal distributBd = new BigDecimal(mainVO.getDistributablebonu().toString());
			BigDecimal divide = distributBd.divide(new BigDecimal(mainVO.getAccountednum().toString()), 2,
					RoundingMode.HALF_UP);
			BigDecimal tempBd = null;
			Cell monverCell = null;
			Cell incenCell = null;
			Cell tablampCell = null;
			Cell psncodeCell = null;
			Cell manfactorCell = null;
			Cell noteCell = null;
			for (int i = 0; i < storeSubVOs.length; i++) {

				storeSubVO = storeSubVOs[i];
				row = sheet.getRow(bodyRow);
				if (row == null) {
					throw new ValidationException("导入数据有误，请按照导出行数进行填写");
				}
				psncodeCell = row.getCell(0);
				manfactorCell = row.getCell(2);
				monverCell = row.getCell(3);
				incenCell = row.getCell(4);
				tablampCell = row.getCell(5);
				noteCell = row.getCell(6);
				if (monverCell == null || incenCell == null || tablampCell == null
						|| !(storeSubVO.getPsncode().equals(psncodeCell.getStringCellValue()))) {
					throw new ValidationException("导入数据有误，请重新检查");
				}
				String monverValue = String.valueOf(monverCell.getNumericCellValue());
				tempBd = new BigDecimal(monverValue);
				monverBd = monverBd.add(tempBd);
				String incenValue = String.valueOf(incenCell.getNumericCellValue());
				tempBd = new BigDecimal(incenValue);
				incentBd = incentBd.add(tempBd);
				String tablampValue = String.valueOf(tablampCell.getNumericCellValue());
				tempBd = new BigDecimal(tablampValue);
				tablampBd = tablampBd.add(tempBd);

				UFDouble tempDouble = null;
				// 需要进行检查金额:可分配奖金/核算人数*管理人员系数
				
				if (manfactorCell != null) {
					double numericCellValue = manfactorCell.getNumericCellValue();
					if(numericCellValue!=0) {
						String manfactValue = String.valueOf(numericCellValue);
						divide = divide.multiply(new BigDecimal(manfactValue));
						int equals = divide.setScale(2, BigDecimal.ROUND_HALF_UP)
								.compareTo(new BigDecimal(monverValue));
						if (equals > 0) {
							throw new ValidationException(psncodeCell.getStringCellValue() + "的系数或者月浮动薪有误");
						}
						tempDouble = new UFDouble(manfactValue);
						storeSubVO.setManfactor(tempDouble);
					}else {
						tempDouble = new UFDouble(numericCellValue);
						storeSubVO.setManfactor(tempDouble);
					}
				}
				tempDouble = new UFDouble(monverValue);
				storeSubVO.setMonvar(tempDouble);
				tempDouble = new UFDouble(incenValue);
				storeSubVO.setIncentives(tempDouble);
				tempDouble = new UFDouble(tablampValue);
				storeSubVO.setTablamp(tempDouble);
				if (noteCell != null) {
					storeSubVO.setNote(noteCell.getStringCellValue());
				}

				storeSubVOs[i] = storeSubVO;
				bodyRow++;

			}
			BigDecimal bigDecimal=null;

			if (monverBd.compareTo(new BigDecimal(mainVO.getDistributablebonu().toString()))!=0) {
				throw new ValidationException("可分配奖金有误");
			}
			if (incentBd.compareTo(new BigDecimal(mainVO.getInspectfee().toString()))!=0) {
				throw new ValidationException("检查费奖励有误");
			}
			 bigDecimal = new BigDecimal(mainVO.getLamp().toString());
			if (tablampBd.compareTo(bigDecimal)!=0) {
				throw new ValidationException("同仁台灯奖励有误");
			}
			BillData billData = billCardPanel.getBillData();
			billData.setBodyValueVO(storeSubVOs);
//			ui.getBtnManager().getCmdByID(AbstractBtnReg.SYSBTN_SAVE).execute();
//			ui.getBillListPanel().get
			IOperateData saveEdit = NCLocator.getInstance().lookup(IOperateData.class);
			saveEdit.importDataSave(storeSubVOs);
			MessageDialog.showHintDlg(this.ui, "提示", "导入门店奖金分配成功");

			fis.close();

		}
		

	}
	/**
	 * // 获取单元格的值 private String getCellValue(Cell cell) { if (cell == null) {
	 * return ""; }
	 * 
	 * CellType cellType = cell.getCellType(); if (cellType == CellType.STRING) {
	 * return cell.getStringCellValue(); } else if (cellType == CellType.NUMERIC) {
	 * return String.valueOf(cell.getNumericCellValue()); } else if (cellType ==
	 * CellType.BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else
	 * if (cellType == CellType.FORMULA) { return cell.getCellFormula(); } else if
	 * (cellType == CellType.BLANK) { return ""; } else { return ""; } }
	 */
}