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"); }