package com.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
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 UploadExcel {
	
	public static void main(String[] args) throws IOException {
		 
		UploadExcel.readExcel();
		
		//UploadExcel.writeExcel();
	}
	
	//对日期格式//数字类型的格式化
	public static String getFormatKey(short formatNumber, Date date)
	{
		String format = "" ;
		
		SimpleDateFormat sdf = null;
		
		if(formatNumber == HSSFDataFormat.getBuiltinFormat("h:mm"))
		{
			sdf = new SimpleDateFormat("HH:mm");
		}
		else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy"))
		{
			sdf = new SimpleDateFormat("yyyy-MM-dd");
		}
		else if(formatNumber == HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))
		{
			sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		}
		else
		{
			sdf = new SimpleDateFormat("MM-dd");
		}
		format = sdf.format(date);
		
		return format;
	}
	//读excel
	public static void readExcel() throws IOException{
		
		  String excelFilePath = "F://a.xlsx";
	        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
	        
	        //读写xls和xlsx格式时，HSSFWorkbook针对xls，XSSFWorkbook针对xlsx
	        
	        Workbook workbook = null; 
	        
	        int index = excelFilePath.lastIndexOf(".");
	        String suffix = excelFilePath.substring(index + 1).toLowerCase();
	        
	        if ("xls".equals(suffix))
	        {  
	        	workbook = new HSSFWorkbook(inputStream); 
	            
	        }
	        else if("xlsx".equals(suffix))
	        {  
	        	workbook = new XSSFWorkbook(inputStream);  
	        }  
	        else
	        {
	        	System.err.println("错误");
	        }
	        Sheet firstSheet = workbook.getSheetAt(0);
	        
	        Iterator<Row> iterator = firstSheet.iterator();
	        
	        int  i = 0; 
	        while (iterator.hasNext()) {
	        	
	        	System.out.print("序列号:" + (i++) + "	");
	        	
	            Row nextRow = iterator.next();
	            Iterator<Cell> cellIterator = nextRow.cellIterator();
	             
	            while (cellIterator.hasNext()) {
	                Cell cell = cellIterator.next();
	                
	                switch (cell.getCellType()) {
	                
	                    case Cell.CELL_TYPE_BOOLEAN:
		                    {
		                    	System.out.print(cell.getBooleanCellValue());
		                        break;
		                    }
	                    case Cell.CELL_TYPE_NUMERIC:
		                    {
		                    	 short format = cell.getCellStyle().getDataFormat(); //获取类型值
		                    	
		                    	 String printString = "";
		                    	 if( format == 14 || format == 20 || format == 22
		                    			 || format == 31 || format == 32 || format == 57 || format == 58)
		                    	 {
		                    		 printString  =  UploadExcel.getFormatKey(format, cell.getDateCellValue());
		                    	 }
		                    	 else
		                    	 {
		                    		DecimalFormat df = new DecimalFormat("#.#");
		                    		printString = df.format(cell.getNumericCellValue()); 
		                    	 }
		                    	 
		                    	 System.out.print(printString);
		                    	 
		                    	 break;
		                    }
	                    case Cell.CELL_TYPE_STRING:
		                    {
		                        System.out.print(cell.getStringCellValue());
		                        break;
		                    }
	                    default : 
		                    {
		                    	System.out.print(cell.getStringCellValue());
		                    }
	                    	
	                }
	                System.out.print("|");
	            }
	            System.out.println("###");
	        }
	         
	        workbook.close();
	        inputStream.close();
	}
	//写excel
	public static void writeExcel(){
		try {
			FileOutputStream fileOut = new FileOutputStream("F://poi-test.xls");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

			// index from 0,0... cell A1 is cell(0,0)
			HSSFRow row1 = worksheet.createRow((short) 0);

			HSSFCell cellA1 = row1.createCell((short) 0);
			cellA1.setCellValue("Hello");
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellA1.setCellStyle(cellStyle);

			HSSFCell cellB1 = row1.createCell((short) 1);
			cellB1.setCellValue("Goodbye");
			cellStyle = workbook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellB1.setCellStyle(cellStyle);

			HSSFCell cellC1 = row1.createCell((short) 2);
			cellC1.setCellValue(true);

			HSSFCell cellD1 = row1.createCell((short) 3);
			cellD1.setCellValue(new Date());
			cellStyle = workbook.createCellStyle();
			cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
			cellD1.setCellStyle(cellStyle);

			workbook.write(fileOut);
			fileOut.flush();
			fileOut.close();
			System.out.println("读取结束");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}