POI接口编写工具类

坐标:

        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

工具类:

Poi(表格读取)

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.LinkedList;


public class Poi {
    
   private Sheet sheet;    //表格类实例  
   @SuppressWarnings("rawtypes")
   LinkedList[] result;    //保存每个单元格的数据 ，使用的是一种链表数组的结构  
   
   @SuppressWarnings("unused")
   private static HSSFWorkbook wb;
   @SuppressWarnings("unused")
   private static HSSFRow row;
   @SuppressWarnings("unused")
   private static String[] excleTitle;  //设置表格表头
   
   //读取excel文件，创建表格实例  
   public void loadExcel(String filePath) {  
       FileInputStream inStream = null;  
       try {  
           inStream = new FileInputStream(new File(filePath));  
           Workbook workBook = WorkbookFactory.create(inStream);  
         
           sheet = (Sheet) workBook.getSheetAt(0);           
       } catch (Exception e) {  
           e.printStackTrace();  
       }finally{  
           try {  
               if(inStream!=null){  
                   inStream.close();  
               }                  
           } catch (IOException e) {                  
               e.printStackTrace();  
           }  
       }  
   }  
   
   //获取单元格的值  
   @SuppressWarnings("deprecation")
private String getCellValue(Cell cell) {  
       String cellValue = "";  
       DataFormatter formatter = new DataFormatter();  
       if (cell != null) {  
           //判断单元格数据的类型，不同类型调用不同的方法  
           switch (cell.getCellType()) {  
               //数值类型  
               case Cell.CELL_TYPE_NUMERIC:  
                   //进一步判断 ，单元格格式是日期格式   
                   if (DateUtil.isCellDateFormatted(cell)) {  
                       cellValue = formatter.formatCellValue(cell);  
//                       new SimpleDateFormat("yyyy/MM/dd").format(cell);  
                   } else {  
                       //数值  
                       double value = cell.getNumericCellValue();  
                       int intValue = (int) value;  
                       cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);  
                   }  
                   break;  
               case Cell.CELL_TYPE_STRING:  
                   cellValue = cell.getStringCellValue();  
                   break;  
               case Cell.CELL_TYPE_BOOLEAN:  
                   cellValue = String.valueOf(cell.getBooleanCellValue());  
                   break;  
                   //判断单元格是公式格式，需要做一种特殊处理来得到相应的值  
               case Cell.CELL_TYPE_FORMULA:{  
                   try{  
                       cellValue = String.valueOf(cell.getNumericCellValue());  
                   }catch(IllegalStateException e){  
                       cellValue = String.valueOf(cell.getRichStringCellValue());  
                   }  
                     
               }  
                   break;  
               case Cell.CELL_TYPE_BLANK:  
                   cellValue = "";  
                   break;  
               case Cell.CELL_TYPE_ERROR:  
                   cellValue = "";  
                   break;  
               default:  
                   cellValue = cell.toString().trim();  
                   break;  
           }  
       }  
       return cellValue.trim();  
   }  
 
   //初始化表格中的每一行，并得到每一个单元格的值  
   @SuppressWarnings({ "rawtypes", "unchecked" })
public LinkedList[]  init(){  
       int rowNum = ((org.apache.poi.ss.usermodel.Sheet) sheet).getLastRowNum() + 1;//getLastRowNum()最后一行行标，比行数小1  
       result = new LinkedList[rowNum]; 
       for(int i=0;i<rowNum;i++){  
           Row row = ((org.apache.poi.ss.usermodel.Sheet) sheet).getRow(i);//获取行
           //每有新的一行，创建一个新的LinkedList对象  
           result[i] = new LinkedList();
           for(int j=0;j<row.getLastCellNum();j++){ //getLastCellNum()获取列数，比最后一列列标大1
               Cell cell = row.getCell(j);//getCell(j)获取列
               //获取单元格的值
               String str = getCellValue(cell); 
               //将得到的值放入链表中
               result[i].add(str);  
           } 
       }
       return result;
   }  
   
   
}

 

PoiReaderWrite(表格创建及写操作)

import com.tmao.common.base.BaseResponse;
import com.tmao.common.base.BaseResponseEnum.Success;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Component;

import java.io.*;
import java.lang.reflect.Method;

@Component
public class PoiReaderWrite {
	
    private HSSFWorkbook workbook = null;
    
    /** 
     * 判断文件是否存在
     * @param filePath  文件路径 
     * @return 
     */  
    public boolean fileExist(String filePath){  
         boolean flag = false;  
         File file = new File(filePath);  
         flag = file.exists();  
         return flag;  
    }  
    
    /** 
     * 判断文件的sheet是否存在
     * @param filePath   文件路径 
     * @param sheetName  表格索引名 
     * @return 
     */  
    public boolean sheetExist(String filePath,String sheetName){  
         boolean flag = false;  
         File file = new File(filePath);  
         if(file.exists()){    //文件存在  
            //创建workbook  
             try {  
                workbook = new HSSFWorkbook(new FileInputStream(file));
                //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)  
                HSSFSheet sheet = workbook.getSheet(sheetName);
                if(sheet!=null)  
                    flag = true;  
            } catch (Exception e) {  
                e.printStackTrace();  
            }                 
         }else{    //文件不存在  
             flag = false;  
         }            
         return flag;  
    }
    /** 
     * 创建新Sheet并写入第一行数据
     * @param filePath  excel的路径 
     * @param sheetName 要创建的表格索引 
     * @param titleRow excel的第一行即表格头 
     * @throws IOException 
     * @throws FileNotFoundException 
     */  
    public void createSheet(String filePath,String sheetName,String titleRow[]) throws FileNotFoundException, IOException{ 
        FileOutputStream out = null;         
        File excel = new File(filePath);  // 读取文件
        FileInputStream in = new FileInputStream(excel); // 转换为流
        workbook = new HSSFWorkbook(in); // 加载excel的 工作目录
        workbook.createSheet(sheetName); // 添加一个新的sheet  
        //添加表头  
        Row row = workbook.getSheet(sheetName).createRow(0);    //创建第一行            
        try {              
            for(int i = 0;i < titleRow.length;i++){  
                Cell cell = row.createCell(i);  
                cell.setCellValue(titleRow[i]);  
            } 
            out = new FileOutputStream(filePath);  
            workbook.write(out);
       }catch (Exception e) {  
           e.printStackTrace();  
       }finally {    
           try {    
               out.close();    
           } catch (IOException e) {    
               e.printStackTrace();  
           }    
       }             
    }
    /** 
     * 创建新excel. 
     * @param filePath  excel的路径 
     * @param sheetName 要创建的表格索引 
     * @param titleRow excel的第一行即表格头 
     */  
    public void createExcel(String filePath,String sheetName,String titleRow[]){  
        //创建workbook  
        workbook = new HSSFWorkbook();
        //添加Worksheet（不添加sheet时生成的xls文件打开时会报错)  
        workbook.createSheet(sheetName);    
        //新建文件  
        FileOutputStream out = null;  
        try {  
            //添加表头  
            Row row = workbook.getSheet(sheetName).createRow(0);    //创建第一行    
            for(int i = 0;i < titleRow.length;i++){  
                Cell cell = row.createCell(i);  
                cell.setCellValue(titleRow[i]);  
            }               
            out = new FileOutputStream(filePath);  
            workbook.write(out);  
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally {    
            try {    
                out.close();    
            } catch (IOException e) {    
                e.printStackTrace();  
            }    
        }    
    }  
    /** 
     * 删除文件. 
     * @param filePath  文件路径 
     */  
    public boolean deleteExcel(String filePath){  
        boolean flag = false;  
        File file = new File(filePath);  
        // 判断目录或文件是否存在    
        if (!file.exists()) {  
            return flag;    
        } else {    
            // 判断是否为文件    
            if (file.isFile()) {  // 为文件时调用删除文件方法    
                file.delete();  
                flag = true;  
            }   
        }  
        return flag;  
    }  
    /** 
     * 往excel中写入. 
     * @param filePath    文件路径 
     * @param sheetName  表格索引 
     * @param object 
     */  
    public BaseResponse writeToExcel(String filePath, String sheetName, Object object, String titleRow[]){
    	BaseResponse baseResponse = new BaseResponse();
    	String flag = Success.TRUE.name();
        //创建workbook  
        File file = new File(filePath);  
        try {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } catch (FileNotFoundException e) {
        	flag = Success.FALSE.name();
            baseResponse.setObject(e);
            e.printStackTrace();  
        } catch (IOException e) {  
        	flag = Success.FALSE.name();
            baseResponse.setObject(e);
        	e.printStackTrace();
        }  
        FileOutputStream out = null;  
        HSSFSheet sheet = workbook.getSheet(sheetName);
        // 获取表格的总行数  
        int rowCount = sheet.getLastRowNum() + 1; // 需要加一  
        try {  
            Row row = sheet.createRow(rowCount);     //最新要添加的一行  
            //通过反射获得object的字段,对应表头插入  
            // 获取该对象的class对象  
            Class<? extends Object> class_ = object.getClass();              
            for(int i = 0;i < titleRow.length;i++){    
                String title = titleRow[i];
                String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;  
                String methodName  = "get" + UTitle;  
                Method method = class_.getDeclaredMethod(methodName); // 设置要执行的方法
                String data = "";
                try {
                    data = method.invoke(object).toString(); // 执行该get方法,即要插入的数据
                }catch (Exception e){   //忽略空值
                }
                Cell cell = row.createCell(i);
                cell.setCellValue(data);	
            }           
            out = new FileOutputStream(filePath);  
            workbook.write(out);  
            
        } catch (Exception e) {  
            e.printStackTrace();
            flag = Success.FALSE.name();
            baseResponse.setObject(e);
        } 
        finally {    
            try {    
                out.close();    
            } catch (IOException e) {
            	flag = Success.FALSE.name();
                baseResponse.setObject(e);
                e.printStackTrace();
            }    
        }
        baseResponse.setSuccess(flag);
		baseResponse.setMessage(filePath);
        return baseResponse;
    }
   
    
    
}

 

 

 

public class BaseResponseEnum {

	public static enum Success {
		TRUE("TRUE"), FALSE("FALSE");

		private final String text;

		private Success(final String text) {
			this.text = text;
		}

		@Override
		public String toString() {
			return text;
		}
	}
}

public class BaseResponse {
    private String success; 
    private String message;
    private Object object;
    /**
     * @return the success
     */
    public String getSuccess() {
        return success;
    }
    /**
     * @param success the success to set
     */
    public void setSuccess(String success) {
        this.success = success;
    }
    /**
     * @return the message
     */
    public String getMessage() {
        return message;
    }
    /**
     * @param message the message to set
     */
    public void setMessage(String message) {
        this.message = message;
    }
    /**
     * @return the object
     */
    public Object getObject() {
        return object;
    }
    /**
     * @param object the object to set
     */
    public void setObject(Object object) {
        this.object = object;
    }
    
    @Override
    public String toString() {
        return "BaseResponse [success=" + success + ", message=" + message + ", object=" + object + "]";
    }
    
}

public class BaseEntity implements Serializable
{
    private static final long serialVersionUID = 1L;

    /** 搜索值 */
    private String searchValue;

    /** 创建者 */
    private String createBy;

    /** 创建时间 */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    /** 更新者 */
    private String updateBy;

    /** 更新时间 */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private Date updateTime;

    /** 备注 */
    private String remark;

    /** 请求参数 */
    private Map<String, Object> params;

    public String getSearchValue()
    {
        return searchValue;
    }

    public void setSearchValue(String searchValue)
    {
        this.searchValue = searchValue;
    }

    public String getCreateBy()
    {
        return createBy;
    }

    public void setCreateBy(String createBy)
    {
        this.createBy = createBy;
    }

    public Date getCreateTime()
    {
        return createTime;
    }

    public void setCreateTime(Date createTime)
    {
        this.createTime = createTime;
    }

    public String getUpdateBy()
    {
        return updateBy;
    }

    public void setUpdateBy(String updateBy)
    {
        this.updateBy = updateBy;
    }

    public Date getUpdateTime()
    {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime)
    {
        this.updateTime = updateTime;
    }

    public String getRemark()
    {
        return remark;
    }

    public void setRemark(String remark)
    {
        this.remark = remark;
    }

    public Map<String, Object> getParams()
    {
        if (params == null)
        {
            params = new HashMap<>();
        }
        return params;
    }

    public void setParams(Map<String, Object> params)
    {
        this.params = params;
    }
}