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