22、NC导入数据至EXCEL

**

22、NC导入数据至EXCEL

**
代码：自定义按钮导出

package nc.ui.hfhr.hfhr_continefault.action;

import java.awt.Component;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import javax.swing.JFileChooser;

import nc.bs.framework.common.NCLocator;
import nc.itf.hfdm.IHfdm_dormdocMaintain;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.ui.hf.pub.utils.excel.ExcelOutUnit;
import nc.ui.hf.pub.utils.excel.FileFromLocalUtils;
import nc.ui.hf.pub.utils.excel.PathCacheManager;
import nc.ui.hf.pub.utils.excel.Utils;
import nc.ui.pub.ClientEnvironment;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.beans.UIFileChooser;
import nc.ui.pub.bill.BillCardPanel;
import nc.ui.pubapp.uif2app.model.BatchBillTableModel;
import nc.ui.pubapp.uif2app.view.ShowUpableBatchBillTable;
import nc.ui.pubapp.uif2app.view.ShowUpableBillForm;
import nc.ui.querytemplate.QueryConditionDLG;
import nc.ui.uif2.NCAction;
import nc.ui.uif2.model.IAppModelDataManager;
import nc.vo.hfhr.hfhr_continefault.Hfhr_continefault;
import nc.vo.hfhr.hfhr_tempvo.Hfhr_Defult;
import nc.vo.hfhr.hfhr_tempvo.Hfhr_PsnTrainDA;
import nc.vo.pubapp.AppContext;
import nc.vo.ta.overtime.OvertimeRegVO;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.xssf.usermodel.XSSFWorkbook;

public class OutputERegAction extends NCAction {
	
	/**
	 * @author cwx
	 * 2017年9月19日14:57:58
	 * @添加免校验按钮
	 */
	private static final long serialVersionUID = 1L;
	private QueryConditionDLG qcDlg;
	private BatchBillTableModel model;
	private ShowUpableBatchBillTable editor;
	private IAppModelDataManager modelManager = null;
	public OutputERegAction() {
		setCode("OutputEReg");
		setBtnName("导出旷工数据");
	}
	@Override
	public void doAction(ActionEvent arg0) throws Exception {
		String pkuser = AppContext.getInstance().getPkUser(); 
		BillCardPanel card = getEditor().getBillCardPanel();
		IUAPQueryBS server = NCLocator.getInstance().lookup(IUAPQueryBS.class);
		String sql = "select * from hfhr_continefault where creator = '"+pkuser+"' order by sybcode,orgcode,yijicode,deptcode,psncode asc";
		List<Hfhr_continefault> lists = (List<Hfhr_continefault>)server.executeQuery(sql, new BeanListProcessor(Hfhr_continefault.class));
		//for (Hfhr_continefault hfhr_continefault : lists) {
			onOutPutExcel(card,lists);
			getModelManager().initModel();
		//}
		/*Object[]  selectdatas = getModel().getSelectedOperaDatas();
		for (Object object : selectdatas) {
			Container AggOvertimeVO = (Container)object;
			onOutPutExcel(AggOvertimeVO);
			
		}*/
	}
	public static void onOutPutExcel(BillCardPanel card,List<Hfhr_continefault> lists ) throws Exception {
		ExcelOutPutFilePath(card, "导出旷工数据","",lists);

	}
		public static void ExcelOutPutFilePath(BillCardPanel card, String dialogName, String pathKey,List<Hfhr_continefault> lists ) throws Exception{
			String filepath = getPathCachekey(card, dialogName, pathKey);
			if(filepath == null){
				 return;
			}
			// 创建工作文档对象  
	        Workbook wb = null; 
	        filepath += "\\旷工数据"+new Date().getTime()+".xlsx";
	        if (filepath.endsWith(".xls")) {  
	            wb = new HSSFWorkbook();  
	        } else if (filepath.endsWith(".xlsx")) {  
	            wb = new XSSFWorkbook();  
	        } else {  
	        	MessageDialog.showErrorDlg(card, "提示框", "您的文档格式不正确！");
	            return;  
	        }  
			OutputStream output = new FileOutputStream(filepath);
			// 创建sheet对象  2017-06-05
	        Sheet sheet = (Sheet) wb.createSheet("旷工数据");  
	        //创建一行数据
	        Row row = (Row) sheet.createRow(0);
	        Cell cell1 = row.createCell(0);  
	        cell1.setCellValue("事业部");
	        cell1.setCellType(Cell.CELL_TYPE_STRING);
	        Cell cell2 = row.createCell(1);  
	        cell2.setCellValue("组织");
	        Cell cell3 = row.createCell(2); 
	        cell3.setCellValue("一级部门");
	        Cell cell4 = row.createCell(3); 
	        cell4.setCellValue("末级部门");
	        Cell cell5 = row.createCell(4); 
	        cell5.setCellValue("工号");
	        Cell cell6 = row.createCell(5); 
	        cell6.setCellValue("姓名");
	        Cell cell7 = row.createCell(6); 
	        cell7.setCellValue("连续旷工天数");
	        Cell cell8 = row.createCell(7); 
	        cell8.setCellValue("连续旷工日期");
	        for(int i = 0 ; i < lists.size() ; i ++){
	        	Hfhr_continefault hfhr_continefault = lists.get(i);
	        	 Row row1 = (Row) sheet.createRow(i+1);
	        	 Cell cell12 = row1.createCell(0);  
	 	        cell12.setCellValue(lists.get(i).getSybname());
	 	        cell12.setCellType(Cell.CELL_TYPE_STRING);
	 	        Cell cell22 = row1.createCell(1);  
	 	        cell22.setCellValue(lists.get(i).getOrgname());
	 	        Cell cell32 = row1.createCell(2); 
	 	        cell32.setCellValue(lists.get(i).getYijidept());
	 	        Cell cell42 = row1.createCell(3); 
	 	        cell42.setCellValue(lists.get(i).getDept());
	 	        Cell cell52 = row1.createCell(4); 
	 	        cell52.setCellValue(lists.get(i).getPsncode());
	 	        Cell cell62 = row1.createCell(5); 
	 	        cell62.setCellValue(lists.get(i).getPsnname());
	 	        Cell cell72 = row1.createCell(6); 
	 	        cell72.setCellValue(lists.get(i).getContidays());
	 	        Cell cell82 = row1.createCell(7); 
	 	        cell82.setCellValue(lists.get(i).getContidates());
	        }
	        sheet.setColumnWidth(0,5120);
	        sheet.setColumnWidth(1,7680);
	        sheet.setColumnWidth(2,5120);
	        sheet.setColumnWidth(3,5120);
	        sheet.setColumnWidth(4,5120);
	        sheet.setColumnWidth(5,5120);
	        sheet.setColumnWidth(6,5120);
	        sheet.setColumnWidth(7,2560);
	        sheet.setColumnWidth(8,12400);
	        // 写入数据  
	        wb.write(output);
	        // 关闭文件流  
	        output.close();
	        IHfdm_dormdocMaintain svc=NCLocator.getInstance().lookup(IHfdm_dormdocMaintain.class);
	        String pkuser = AppContext.getInstance().getPkUser(); 
	        String sql = "delete from hfhr_continefault where  creator = '"+pkuser+"'";
			svc.deleteSql(sql);
	        MessageDialog.showHintDlg(card, "提示框", "模版导出成功！");
	        
		}
		public static String getPathCachekey(Component aggOvertimeVO, String dialogName, String pathKey){
			//取文件路径缓存并置入
			File file = new File(PathCacheManager.getInstance().getPach(ClientEnvironment.getInstance().getUser().getPrimaryKey(), pathKey));
			JFileChooser fileChooser = new UIFileChooser();
			fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
			if(file.exists()){
				fileChooser.setCurrentDirectory(file);
				if(file.isFile()){
					fileChooser.setSelectedFile(file);
				}
			}
			fileChooser.setDialogTitle(dialogName);
			int result = fileChooser.showDialog(aggOvertimeVO, "确定");
			if(result == JFileChooser.APPROVE_OPTION){
				file = fileChooser.getSelectedFile();
				
				//更新文件路径缓存
				String path = Utils.getValidateFilePath(file.getAbsolutePath());
				path = path.substring(0, path.length() - 1);
				PathCacheManager.getInstance().updatePath(ClientEnvironment.getInstance().getUser().getPrimaryKey(), pathKey, path);
				return file.getPath();
			}else return null;
		}
		public QueryConditionDLG getQcDlg() {
			return qcDlg;
		}
		public void setQcDlg(QueryConditionDLG qcDlg) {
			this.qcDlg = qcDlg;
		}
		public BatchBillTableModel getModel() {
			return model;
		}
		public void setModel(BatchBillTableModel model) {
			this.model = model;
		}
		public ShowUpableBatchBillTable getEditor() {
			return editor;
		}
		public void setEditor(ShowUpableBatchBillTable editor) {
			this.editor = editor;
		}
		public IAppModelDataManager getModelManager() {
			return modelManager;
		}
		public void setModelManager(IAppModelDataManager modelManager) {
			this.modelManager = modelManager;
		}

	
	

}