ExcelUtil工具类

ExcelUtil导出导入

package com.pflm.shakeExternal.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.format.CellFormatType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil {
    
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    
     /**
     * 创建excel文档，
     * @param list 数据
     * @param keys list中map的key数组集合
     * @param columnNames excel的列名
     * */
    public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for(int i=0;i<keys.length;i++){
            sheet.setColumnWidth((short) i, (short) (35.7 * 150));
        }

        // 创建第一行
        Row row = sheet.createRow((short) 0);

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setBorderLeft(CellStyle.BORDER_THIN);
        cs.setBorderRight(CellStyle.BORDER_THIN);
        cs.setBorderTop(CellStyle.BORDER_THIN);
        cs.setBorderBottom(CellStyle.BORDER_THIN);
        cs.setAlignment(CellStyle.ALIGN_CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setBorderLeft(CellStyle.BORDER_THIN);
        cs2.setBorderRight(CellStyle.BORDER_THIN);
        cs2.setBorderTop(CellStyle.BORDER_THIN);
        cs2.setBorderBottom(CellStyle.BORDER_THIN);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        //设置列名
        for(int i=0;i<columnNames.length;i++){
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        //设置每行每列的值
        for (short i = 1; i < list.size(); i++) {
            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            // 创建一行，在页sheet上
            Row row1 = sheet.createRow((short) i);
            // 在row行上创建一个方格
            for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
    
    /**
     * 得去excel （支持加密文件，但是对xls文件，xlsx文件不支持）
     * @param f
     * @param password
     * @return
     */
    @SuppressWarnings("resource")
    public static List<String> readExcel(File f,String password){
        try{
            String fileName = f.getName();
            String type = fileName.substring(fileName.indexOf(".")+1);
            if(XLS.equals(type)){
                HSSFWorkbook hwb = null;
                Sheet sheet = null;
                if(password!=null && !"".equals(password)){
                    Biff8EncryptionKey.setCurrentUserPassword(password);
                    NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true);
                    hwb = new HSSFWorkbook(fs);
                    Biff8EncryptionKey.setCurrentUserPassword(null);
                }else{
                    hwb = new HSSFWorkbook(new FileInputStream(f));
                }
                hwb = new HSSFWorkbook(new FileInputStream(f));
                sheet = hwb.getSheetAt(0);
                return readCell(sheet);
            }else{
                NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true);
                EncryptionInfo info = new EncryptionInfo(fs);
                Decryptor decryptor = Decryptor.getInstance(info);
                decryptor.verifyPassword(password);
                InputStream is = decryptor.getDataStream(fs);
                XSSFWorkbook xwb = new XSSFWorkbook(is);
                is.close();
                XSSFSheet sheet = xwb.getSheetAt(0);
                return readCell(sheet);
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        
        return null;
    }
    
    private static List<String> readCell(Sheet sheet){
        List<String> res = new ArrayList<>();
        int start_row = sheet.getFirstRowNum();
        int end_row = sheet.getLastRowNum();
        for(int i=start_row; i<=end_row;i++){
            Row row = sheet.getRow(i);
            int start_cell = row.getFirstCellNum();
            int end_cell = row.getLastCellNum();
            StringBuilder sb = new StringBuilder();
            for(int j = start_cell; j<end_cell; j++){
                Cell cell = row.getCell(j);
                if(cell == null){
                    continue;
                }
                sb.append(",");
//              CellType type1 = cell.getCellTypeEnum();
//              switch(type1){  //获取数据
//              case _NONE:
//                  break;
//              case BLANK: //白单元格  
//                  sb.append("");
//                  break;
//              case BOOLEAN: //布尔类型
//                  break;
//              case ERROR: //故障  
//                  break; 
//              case FORMULA: //公式
//                  break;
//              case NUMERIC: //数值、日期类型  
//                  break;
//              case STRING: //字符串
//                  break;
//              default:// 未知类型  
//                  break;
//              }
                sb.append(cell.toString());
            }
            String line = sb.toString();
            line = line.substring(1);
            res.add(line);
        }
        return res;
    }
    
    private static Sheet getSheet(InputStream in,String fileName){
        Sheet sheet = null;
        try{
            String type = fileName.substring(fileName.indexOf(".")+1);
            if(XLS.equals(type)){
                HSSFWorkbook hwb = hwb = new HSSFWorkbook(in);
                sheet = hwb.getSheetAt(0);
            }else{
                XSSFWorkbook xwb = xwb = new XSSFWorkbook(in);
                sheet = xwb.getSheetAt(0);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            if(in!=null){
                try {
                    in.close();
                    in = null;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return sheet;
    }
    
    /**
     * 读取excel文件，只要某一行的某一列出现空值，直接结束循环，并返回null
     * @param in
     * @param fileName
     * @param titles
     * @param date
     * @param length
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> readExcel(InputStream in,String fileName,String[] titles,String[] colums, Date date,int length){
        return readCell(getSheet(in,fileName),titles,colums,date,length);
    }
    
    /**
     * 读取数据，只要某一行的某一列出现空值，直接结束循环，并返回null
     * @param sheet
     * @param titles
     * @param colums
     * @param date
     * @return
     */
    private static List<Map<String,Object>> readCell(Sheet sheet,String[] titles,String[] colums,Date date,int length){
        List<Map<String,Object>> res = new ArrayList<>();
        
        int start_row = sheet.getFirstRowNum();
        int end_row = sheet.getLastRowNum();
        boolean flag = Boolean.TRUE;
        SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        
        for(int i=start_row; i<=end_row&&flag;i++){//读取行
            Row row = sheet.getRow(i);
            int start_cell = row.getFirstCellNum();
            //隐藏下面这这几段是因为 有些行中，是没有数据的，比如一共7列。第一行全有数据，第二行前三个字段有数据，那么此时end_cell=3，就会读取不到完整的数据了@！
            /*int end_cell = row.getLastCellNum();
            if((titles!=null && titles.length<end_cell) || (colums!=null && colums.length<end_cell) || end_cell!=length){
                return null;
            }*/
            Map<String,Object> map = new HashMap<>();;
            for(int j = start_cell; j<titles.length; j++){//读取列
                Cell cell = row.getCell(j);
                /*if(cell==null){
                    continue;
                }*/
                if(i==0){//检查导入表的标题是否符合colums所给的数据
                    if(!cell.toString().equals(colums[j])){
                        res.clear();
                        res=null;
                        flag = Boolean.FALSE;
                        break;
                    }
                }else{
                    Object data = null;
                    if(cell!=null){ //levan增加。处理那些数值为空值，list中key没了的问题
                        switch (cell.getCellType()) {  
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数值型     
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {     
                                    //  如果是date类型则 ，获取该cell的date值     
                                    data = format.format(addDay(cell.getDateCellValue(), 0));  
                                } else { 
                                    //xieyz版本  
                                    /*double cellValue = cell.getNumericCellValue();   
                                    data = String.valueOf(new DecimalFormat("#").format(cellValue)); */
                                    
                                     //Levan版本
                                     cell.setCellType(Cell.CELL_TYPE_STRING);  
                                     String temp = cell.getStringCellValue();  
                                     // 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串  
                                     if (temp.indexOf(".") > -1) {  
                                         data = String.valueOf(new Double(temp)).trim();  
                                     } else {  
                                         data = temp.trim();  
                                     }  
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                data = cell.getStringCellValue().replaceAll("\"|'", "");
                                break;
                        }
                    }
                    map.put(titles[j], data);
                }
            }
            if(date!=null)
                map.put("createTime", date);
            if(i!=0)
                res.add(map);
        }
        return res;
    }
    
    private static Date addDay(Date date,int i){
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, i);
        return calendar.getTime();
    }
    
    public static void main(String[] args) throws IOException{
        //woshihd
        //电渠二季.xls
//      File f = new File("F:/电渠二季度政企专项活动宽带号码清单.xlsx");

//      File f = new File("F:/星级用户首访第二批.xls");
        File f = new File("F:/视频类目标用户 （3月流量使用率低于25%）-20170411.xlsx");
    }
}