custdev/src/public/nc/pub/toos/FileTool/ExcelUtil.java

package nc.pub.toos.FileTool;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import nc.vo.pubapp.pattern.exception.ExceptionUtils;

  
public class ExcelUtil {
	//默认单元格内容为数字时格式  
    private static DecimalFormat df = new DecimalFormat("0");  
    // 默认单元格格式化日期字符串   
    private static SimpleDateFormat sdf = new SimpleDateFormat(  "yyyy-MM-dd HH:mm:ss");   
    // 格式化数字  
    private static DecimalFormat nf = new DecimalFormat("0.00");    
    public static ArrayList<ArrayList<Object>> readFile(File file){  
        if(file == null){  
            return null;  
        }  
        String filename = file.getName().toLowerCase();
        
        if(filename.endsWith("xlsx")){  
            //处理ecxel2007  
        	
            try {
				return readExcel2007(new FileInputStream(file));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				return null;
			}  
        }else if(filename.endsWith("xls")){  
            //处理ecxel2003  
            try {
				return readExcel2003(new FileInputStream(file));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				return null;
			}  
        }else if(filename.endsWith("csv"))  {
        	try {
				return readCsv(new FileInputStream(file));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				return null;
			}
        }else {
        	ExceptionUtils.wrappBusinessException("导入失败，系统只接收xls,xlsx,csv类型文件");
        	return null;
        }
    }  
    /*  
     * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似  
     * lists.get(0).get(0)表示过去Excel中0行0列单元格  
     */  
    public static ArrayList<ArrayList<Object>> readExcel2003(InputStream input){  
        try{  
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();  
            ArrayList<Object> colList;  
            HSSFWorkbook wb = new HSSFWorkbook(input);  
            HSSFSheet sheet = wb.getSheetAt(0);  
            HSSFRow row;  
            HSSFCell cell;  
            Object value = null;  
            for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){  
                row = sheet.getRow(i);  
                colList = new ArrayList<Object>();  
                if(row == null){  
                    //当读取行为空时  
                    if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行  
                        rowList.add(colList);  
                    }  
                    continue;  
                }else{  
                    rowCount++;  
                }  
                for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){  
                    cell = row.getCell(j);  
                    if(cell == null || cell.getCellType() == CellType.BLANK){  
                        //当该单元格为空  
                        if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格  
                            colList.add("");  
                        }  
                        continue;  
                    }  
                    switch(cell.getCellType()){  
                     case STRING:    
                            value = cell.getStringCellValue().trim();    
                            break;    
                        case NUMERIC:    
                        	short format = cell.getCellStyle().getDataFormat();
                            if (DateUtil.isCellDateFormatted(cell)) {
                                SimpleDateFormat sdf = null;  
                                //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                                if (format == 20 || format == 32) {  
                                    sdf = new SimpleDateFormat("HH:mm");  
                                } else if (format == 14 || format == 31 || format == 57 || format == 58) {  
                                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                                    double celvalue = cell.getNumericCellValue();  
                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(celvalue);  
                                    value = sdf.format(date);  
                                }else {// 日期  
                                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                                }  
                                try {
                                    value = sdf.format(cell.getDateCellValue());// 日期
                                } catch (Exception e) {
                                    try {
                                        throw new Exception("exception on get date data !".concat(e.toString()));
                                    } catch (Exception e1) {
                                        e1.printStackTrace();
                                    }
                                }finally{
                                    sdf = null;
                                }
                            }  else {
                               // BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); 
                                value = cell.toString().trim();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                            }      
                            break;    
                        case BOOLEAN:    
                            value = Boolean.valueOf(cell.getBooleanCellValue());  
                            break;    
                        case BLANK:    
                            value = "";    
                            break;    
                        default:    
                            value = cell.toString().trim();    
                    }// end switch  
                    colList.add(value);  
                }//end for j  
                if(isAllNotEmpty(colList)) {
                	
                	rowList.add(colList);  
                }
            }//end for i  
              
            return rowList;  
        }catch(Exception e){  
            return null;  
        }  
    }  
      
    public static ArrayList<ArrayList<Object>> readExcel2007(InputStream input){  
        try{  
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();  
            ArrayList<Object> colList;  
            XSSFWorkbook wb = new XSSFWorkbook(input);  
            XSSFSheet sheet = wb.getSheetAt(0);  
            XSSFRow row;  
            XSSFCell cell;  
            Object value = null;  
            for(int i = sheet.getFirstRowNum() , rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows() ; i++ ){  
                row = sheet.getRow(i);  
                colList = new ArrayList<Object>();  
                if(row == null){  
                    //当读取行为空时  
                    if(i != sheet.getPhysicalNumberOfRows()){//判断是否是最后一行  
                        rowList.add(colList);  
                    }  
                    continue;  
                }else{  
                    rowCount++;  
                }  
                for( int j = row.getFirstCellNum() ; j <= row.getLastCellNum() ;j++){  
                    cell = row.getCell(j);  
                    if(cell == null || cell.getCellType() == CellType.BLANK){  
                        //当该单元格为空  
                        if(j != row.getLastCellNum()){//判断是否是该行中最后一个单元格  
                            colList.add("");  
                        }  
                        continue;  
                    }  
                    switch(cell.getCellType()){  
                     case STRING:    
                            value = cell.getStringCellValue().trim();    
                            break;    
                        case NUMERIC:    
                        	short format = cell.getCellStyle().getDataFormat();
                            if (DateUtil.isCellDateFormatted(cell)) {
                                SimpleDateFormat sdf = null;  
                                //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
                                if (format == 20 || format == 32) {  
                                    sdf = new SimpleDateFormat("HH:mm");  
                                } else if (format == 14 || format == 31 || format == 57 || format == 58) {  
                                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
                                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                                    double celvalue = cell.getNumericCellValue();  
                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(celvalue);  
                                    value = sdf.format(date);  
                                }else {// 日期  
                                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
                                }  
                                try {
                                    value = sdf.format(cell.getDateCellValue());// 日期
                                } catch (Exception e) {
                                    try {
                                        throw new Exception("exception on get date data !".concat(e.toString()));
                                    } catch (Exception e1) {
                                        e1.printStackTrace();
                                    }
                                }finally{
                                    sdf = null;
                                }
                            }  else {
                               // BigDecimal bd = new BigDecimal(cell.getNumericCellValue()); 
                                value = cell.toString().trim();// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                            }      
                            break;    
                        case BOOLEAN:    
                            value = Boolean.valueOf(cell.getBooleanCellValue());  
                            break;    
                        case BLANK:    
                            value = "";    
                            break;    
                        default:    
                            value = cell.toString().trim();    
                    }// end switch  
                   
                    colList.add(value);  
                }//end for j  
                if(isAllNotEmpty(colList)) {
                	
                	rowList.add(colList);  
                }
            }//end for i  
              
            return rowList;  
        }catch(Exception e){  
            System.out.println("exception");  
            return null;  
        }  
    }  
      
    private static boolean isAllNotEmpty(ArrayList<Object> colList) {
		// TODO Auto-generated method stub
    	for (Object object : colList) {
			String value = object.toString();
			if(StringUtils.isNotBlank(value))
				return true;
		}
		return false;
	}
	public static void writeExcel(ArrayList<ArrayList<Object>> result,String path){  
        if(result == null){  
            return;  
        }  
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("sheet1");  
        for(int i = 0 ;i < result.size() ; i++){  
             HSSFRow row = sheet.createRow(i);  
            if(result.get(i) != null){  
                for(int j = 0; j < result.get(i).size() ; j ++){  
                    HSSFCell cell = row.createCell(j);  
                    cell.setCellValue(result.get(i).get(j).toString());  
                }  
            }  
        }  
        ByteArrayOutputStream os = new ByteArrayOutputStream();  
        try  
        {  
            wb.write(os);  
        } catch (IOException e){  
            e.printStackTrace();  
        }  
        byte[] content = os.toByteArray();  
        File file = new File(path);//Excel文件生成后存储的位置。  
        OutputStream fos  = null;  
        try  
        {  
            fos = new FileOutputStream(file);  
            fos.write(content);  
            os.close();  
            fos.close();  
        }catch (Exception e){  
            e.printStackTrace();  
        }             
    }  
    
    
    
   
        
        /**
         * 导出
         * 
         * @param file csv文件(路径+文件名)，csv文件不存在会自动创建
         * @param dataList 数据
         * @return
         */
        public static boolean exportCsv(File file, List<String> dataList){
            boolean isSucess=false;
            
            FileOutputStream out=null;
            OutputStreamWriter osw=null;
            BufferedWriter bw=null;
            try {
                out = new FileOutputStream(file);
                osw = new OutputStreamWriter(out);
                bw =new BufferedWriter(osw);
                if(dataList!=null && !dataList.isEmpty()){
                    for(String data : dataList){
                        bw.append(data).append("\r");
                    }
                }
                isSucess=true;
            } catch (Exception e) {
                isSucess=false;
            }finally{
                if(bw!=null){
                    try {
                        bw.close();
                        bw=null;
                    } catch (IOException e) {
                        e.printStackTrace();
                    } 
                }
                if(osw!=null){
                    try {
                        osw.close();
                        osw=null;
                    } catch (IOException e) {
                        e.printStackTrace();
                    } 
                }
                if(out!=null){
                    try {
                        out.close();
                        out=null;
                    } catch (IOException e) {
                        e.printStackTrace();
                    } 
                }
            }
            
            return isSucess;
        }
        
        /**
         * 导入
         * 
         * @param input csv文件(路径+文件)
         * @return
         */
        public static ArrayList<ArrayList<Object>> readCsv(InputStream input){
        	
        	 ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();  
            BufferedReader br=null;
            try { 
                br = new BufferedReader(new InputStreamReader(input,"GBK"));
                String line = ""; 
                ArrayList<Object> colList;  
                while ((line = br.readLine()) != null) { 
                	colList = new ArrayList<Object>();
                	String[] datas = line.split(",");
                	for (String data : datas) {
                		colList.add(data);
					}
                	if(isAllNotEmpty(colList)) {
                    	
                    	rowList.add(colList);  
                    }
                }
            }catch (Exception e) {
            }finally{
                if(br!=null){
                    try {
                        br.close();
                        br=null;
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
     
            return rowList;
        }
    
          
    public static DecimalFormat getDf() {  
        return df;  
    }  
    public static void setDf(DecimalFormat df) {  
        ExcelUtil.df = df;  
    }  
    public static SimpleDateFormat getSdf() {  
        return sdf;  
    }  
    public static void setSdf(SimpleDateFormat sdf) {  
        ExcelUtil.sdf = sdf;  
    }  
    public static DecimalFormat getNf() {  
        return nf;  
    }  
    public static void setNf(DecimalFormat nf) {  
        ExcelUtil.nf = nf;  
    }  
      
}