Java 实现追加excle文件内容
2024-10-19 02:18:54
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");
}
最新文章
- matlab 将多个盒图放在一张图上
- BFC的深入理解
- LCLFramework框架之开发约束
- 初始化 Ubuntu Trusty 14.04
- split 方法的正确使用姿势
- Libsvm:脚本(subset.py、grid.py、checkdata.py) | MATLAB/OCTAVE interface | Python interface
- 仿淘宝js图片切换
- [ASP.NET]更简单的方法:FormsAuthentication登录ReturnUrl使用绝对路径
- Android-Universal-Image-Loader学习笔记(一个)
- centos6.5中gitlab的搭建
- usb调试
- scrapy批量下载图片
- localhost换成127.0.0.1和本机IP打不开本地项目了的问题
- Quartz框架学习(1)—核心层次结构
- Laravel 5.2数据库--迁移migration
- fiddler修改response header
- java学习之动手实验
- MySQL 5.7 分区表性能下降的案例分析
- c# 设置自动隐藏任务栏、获取状态
- Exp3 免杀原理与实践 20164323段钊阳