Java 实现追加excle文件内容

Java 实现追加excle文件内容

一、示例一：excle（.xlsx）

//jar
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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 org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import com.xyzq.kettle.dao.KettleDao;
import com.xyzq.kettle.entity.KettleEntity;
import com.xyzq.pub.Pub;


    //实现方法
    public void makeExcle(List<KettleEntity> list){
        logger.info("生成excle:start");
        String filepath = exl_sh_path+"/"+list.get(0).getSysName();
        String filename = "P_gp_"+list.get(0).getSysName()+".xlsx" ;
        //判断文件夹是否存在，不存在则创建
        try {
            Pub.checkPath(filepath);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
            logger.info("文件夹创建异常："+e1.toString());
        }
        //判断文件是否存在，存在则追加，否则新增
        // todo:
        File file = new File(filepath+"/"+filename); 
        XSSFWorkbook wb =null;
        XSSFSheet sheet = null;
        InputStream input = null;
        FileOutputStream output=null;
        try {
            wb = new XSSFWorkbook();
            input = new FileInputStream(file);
            wb = (XSSFWorkbook) WorkbookFactory.create(input);
            if(wb != null){
                //获取文件的指定工作表
                sheet =wb.getSheet("job");
                output = new FileOutputStream(filepath+"/"+filename,false);
                //获取最大行数
                //int rownum = sheet.getPhysicalNumberOfRows();
                int index = sheet.getLastRowNum()+1;
                logger.info("index>>>:"+index);
                String cmdStr = "";
                if(list.size() > 0){
                    for(int i = 0; i < list.size(); i++){
                        cmdStr = "";
                        if("自然日".equals(list.get(i).getDataType())){
                            cmdStr = "sh "+pan_path+"/"+list.get(i).getSysName()+"/"+list.get(i).getPanName()+".sh -p${yes_date} -s${yes_date} -e${curr_date}";
                        }else if("交易日".equals(list.get(i).getDataType())){
                            cmdStr = "sh "+pan_path+"/"+list.get(i).getSysName()+"/"+list.get(i).getPanName()+".sh -p${start_date} -s${start_date} -e${end_date}";
                        }
                        //插入excle
                        XSSFRow row = null;
                        row = sheet.createRow(index+i);
                        XSSFCell cell_flow = row.createCell(0);
                        XSSFCell cell_job = row.createCell(2);
                        XSSFCell cell_cmd = row.createCell(6);
                        cell_flow.setCellValue("F_gp_"+list.get(i).getSysName());
                        cell_job.setCellValue("J_gp_"+list.get(i).getTableName());
                        cell_cmd.setCellValue(cmdStr);
                    }
                }else{
                    logger.info("待处理数据为空");
                }   
                output.flush();
                wb.write(output);
                if (input != null){
                    input.close();
                }
                output.close();
            }    
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            logger.info("makeExcle>获取exlce数据异常:"+e.toString());
        }
        logger.info("生成excle:end");
    }

二、示例二：excle2007（.xls）

//jar
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.WorkbookFactory;
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 org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import com.xyzq.kettle.dao.KettleDao;
import com.xyzq.kettle.entity.KettleEntity;
import com.xyzq.pub.Pub;

　　//实现方法
　　public void makeExcle2007(List<KettleEntity> list){
        logger.info("生成excle:start");
        String filepath = exl_sh_path+"/"+list.get(0).getSysName();
        String filename = "P_gp_"+list.get(0).getSysName()+".xlsx" ;
        //判断文件夹是否存在，不存在则创建
        try {
            Pub.checkPath(filepath);
        } catch (InterruptedException e1) {
            e1.printStackTrace();
            logger.info("文件夹创建异常："+e1.toString());
        }
        //判断文件是否存在，存在则追加，否则新增
        // todo:
         
        
        try {
            FileInputStream fileInput = new FileInputStream(filepath+"/"+filename);
            POIFSFileSystem poiFile = new POIFSFileSystem(fileInput);
            XSSFWorkbook wb = new XSSFWorkbook(fileInput);
            XSSFSheet sheet = null;
            if(wb != null){
                //获取文件的指定工作表
                sheet =wb.getSheet("job");
                //获取最大行数
                //int rownum = sheet.getPhysicalNumberOfRows();
                int index = sheet.getLastRowNum();
                FileOutputStream out=new FileOutputStream(filepath+"/"+filename);
                XSSFRow row = null;
                String cmdStr = "";
                if(list.size() > 0){
                    for(int i = 0; i < list.size(); i++){
                        cmdStr = "";
                        if("自然日".equals(list.get(i).getDataType())){
                            cmdStr = "sh "+pan_path+"/"+list.get(i).getSysName()+"/"+list.get(i).getPanName()+".sh -p${yes_date} -s${yes_date} -e${curr_date}";
                        }else if("交易日".equals(list.get(i).getDataType())){
                            cmdStr = "sh "+pan_path+"/"+list.get(i).getSysName()+"/"+list.get(i).getPanName()+".sh -p${start_date} -s${start_date} -e${end_date}";
                        }
                        //插入excle
                        row = sheet.createRow(index+i);
                        XSSFCell cell_flow = row.createCell(0);
                        XSSFCell cell_job = row.createCell(2);
                        XSSFCell cell_cmd = row.createCell(6);
                        cell_flow.setCellValue("F_gp_"+list.get(i).getSysName());
                        cell_job.setCellValue("J_gp_"+list.get(i).getTableName());
                        cell_cmd.setCellValue(cmdStr);
                    }
                }else{
                    logger.info("待处理数据为空");
                }   
                out.flush();
                wb.write(out);  
                out.close(); 
            }    
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            logger.info("makeExcle>获取exlce数据异常:"+e.toString());
        }
        logger.info("生成excle:end");
    }