/**
 * 
 */
package com.jeecg.demo.controller;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.LinkedHashMap;

/**
 * @author xzx
 *  
 * 2019年2月26日
 *
 */
public class FinStatisTest {

	 
	private static final String EXCEL_XLS = "xls";  
    private static final String EXCEL_XLSX = "xlsx";  
  
    /** 
     * 判断Excel的版本,获取Workbook 
     * @param in 
     * @param file
     * @return 
     * @throws IOException 
     */  
    public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
        Workbook wb = null;  
        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
            wb = new HSSFWorkbook(in);  
        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
            wb = new XSSFWorkbook(in);  
        }  
        return wb;  
    }  
  
    /** 
     * 判断文件是否是excel 
     * @throws Exception  
     */  
    public static void checkExcelVaild(File file) throws Exception{  
        if(!file.exists()){  
            throw new Exception("文件不存在");  
        }  
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
            throw new Exception("文件不是Excel");  
        }  
    } 
    
    private static Object getValue(Cell cell) {
    	if(cell == null) {
    		return "";
    	}
    	Object obj = null;
    	switch (cell.getCellType()) {
			case BOOLEAN:
	            obj = cell.getBooleanCellValue(); 
	            break;
			case ERROR:
	            obj = cell.getErrorCellValue(); 
	            break;
			case NUMERIC:
	            obj = cell.getNumericCellValue(); 
	            break;
			case STRING:
	            obj = cell.getStringCellValue(); 
	            break;
	        default:
	            break;
    	}
    	return obj;
    }


	public static void main(String[] args) {
		  LinkedHashMap<Integer, String> map = new LinkedHashMap<>();
		  map.put(4, "0101_基本工资");
		  map.put(5, "0102_岗位津贴");
		  map.put(6, "0401_高温费");
		  map.put(7, "0104_证书津贴");
		  map.put(8, "0103_项目补贴");
		  map.put(9, "0105_社保津贴");
		  map.put(10, "0107_其他补贴");
		  map.put(11, "0107_独生子女费");
		  map.put(12, "0203_加班费");
		  map.put(13, "餐费_餐费");
		  map.put(14, "0202_国定加班费");
		  map.put(15, "0201_值班费");
		  map.put(16, "0303_奖金");
		  map.put(17, "0106_交通补贴");
		  map.put(18, "0107_夜班津贴");
		  map.put(19, "0107_白班津贴");
		  map.put(20, "0203_签报");

		  SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");  
	        try {  
	            // 同时支持Excel 2003、2007  
	            File srcFile = new File("d:/f_1812_2.xls"); // 创建文件对象  
	            FileInputStream in = new FileInputStream(srcFile); // 文件流  
	            checkExcelVaild(srcFile);  
	            Workbook srcWorkbook = getWorkbok(in,srcFile);  
	            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  
	  
	            /** 
	             * 设置当前excel中sheet的下标：0开始 
	             */  
	           Sheet sheetSrc = srcWorkbook.getSheetAt(0);   // 遍历Sheet  
	           
	           HSSFWorkbook targetWb = new HSSFWorkbook(); 
	           HSSFSheet sheetTarget = targetWb.createSheet("sheet1");
	           FileOutputStream output=new FileOutputStream("d:/f_1812_targ3.xls");
	           int rowNumSrc = sheetSrc.getLastRowNum();
	           int targetRowNum = 0;
	           for(int i=1 ; i<=rowNumSrc ; i++) {
	        	   Row rowSrc = sheetSrc.getRow(i);
	        	   int lastColNumSrc = rowSrc.getLastCellNum();
	        	   for(Integer key : map.keySet()) {
	        		   Cell cellSrc = rowSrc.getCell(key.intValue());
	        		   double valueSrc = cellSrc.getNumericCellValue();
	        		   if(valueSrc > 0d) {
	        			   System.out.println(valueSrc);
	        			   targetRowNum ++;
	        			   Row newRow = sheetTarget.createRow(targetRowNum);
	        			   //科目编码
	        			   Cell cell0 = newRow.createCell(0);
	        			   cell0.setCellType(CellType.STRING);
	        			   HSSFCellStyle cellStyle = targetWb.createCellStyle(); 
	        			   //创建一个DataFormat对象 
	        			   HSSFDataFormat format = targetWb.createDataFormat(); 
	        			   //这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧 
	        			   cellStyle.setDataFormat(format.getFormat("@")); 
	        			   
	        			   //具体如何创建cell就省略了，最后设置单元格的格式这样写 
	        			   cell0.setCellStyle(cellStyle);
	        			   if(rowSrc.getCell(3) == null) {
	        				   cell0.setCellValue("");
	        			   }else {
	        				   cell0.setCellValue(getValue(rowSrc.getCell(3))+"");
	        			   }
	        			   //借方金额
	        			   Cell cell1 = newRow.createCell(1);
	        			   cell1.setCellType(CellType.NUMERIC);
	        			   cell1.setCellValue(valueSrc);
	        			   //贷方金额
	        			   Cell cell2 = newRow.createCell(2);
	        			   cell2.setCellType(CellType.STRING);
	        			   cell2.setCellValue("");
	        			   //部门编码
	        			   Cell cell3 = newRow.createCell(3);
	        			   cell3.setCellType(CellType.STRING);
	        			   if(rowSrc.getCell(1) == null) {
	        				   cell3.setCellValue("");
	        			   }else {
	        				   cell3.setCellValue(getValue(rowSrc.getCell(1))+"");
	        			   }
	        			   //项目编码
	        			   Cell cell4 = newRow.createCell(4);
	        			   cell4.setCellType(CellType.STRING);
	        			   cell4.setCellValue(map.get(key).split("_")[0]);
	        			   //大类项目编码
	        			   Cell cell5 = newRow.createCell(5);
	        			   cell5.setCellType(CellType.STRING);
	        			   cell5.setCellValue("00");
	        			   //说明
	        			   Cell cell6 = newRow.createCell(6);
	        			   cell6.setCellType(CellType.STRING);
	        			   cell6.setCellValue(getValue(rowSrc.getCell(0))
	        					   +map.get(key).split("_")[1]
	        					   +getValue(rowSrc.getCell(2)));
	        		   }
	        	   }
	           }
	           targetWb.write(output);
	           output.flush();
	           output.close();
	           in.close();
		    } catch (Exception e) {  
		        e.printStackTrace();  
		    }finally {
		    	
		    }

	}
}