Java 新建excle文件并填充模版内容

Java 新建excle文件并填充模版内容

一、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;

二、主要功能实现代码

   　　　　//filename= P_gp_hs08.xlsx

        //判断文件是否存在，存在则追加，否则新增
        File file = new File(filepath+"/"+filename); 
        if (!file.exists() || !file.isFile()) {
            //logger.info("excle模板不存在，新增");
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheetFlow = wb.createSheet("flow");
            XSSFRow rowFlow0 = sheetFlow.createRow(0);
            XSSFCell cellFlow0Flow = rowFlow0.createCell(0);
            cellFlow0Flow.setCellValue("flow名称");
            XSSFCell cellFlow0Remark = rowFlow0.createCell(1);
            cellFlow0Remark.setCellValue("备注");
            XSSFRow rowFlow1 = sheetFlow.createRow(1);
            XSSFCell cellFlow1Folw = rowFlow1.createCell(0);
            cellFlow1Folw.setCellValue("F_gp_"+list.get(0).getSysName());
            
            XSSFSheet sheetGroup = wb.createSheet("group");
            XSSFRow rowGroup0 = sheetGroup.createRow(0);
            XSSFCell cellGroup0Flow = rowGroup0.createCell(0);
            cellGroup0Flow.setCellValue("flow名称");
            XSSFCell cellGroup0Group = rowGroup0.createCell(1);
            cellGroup0Group.setCellValue("group名称");
            XSSFCell cellGroup0Remark = rowGroup0.createCell(2);
            cellGroup0Remark.setCellValue("备注");
            XSSFCell cellGroup0Relation = rowGroup0.createCell(2);
            cellGroup0Relation.setCellValue("依赖");
            
            XSSFSheet sheetJob = wb.createSheet("job");
            XSSFRow rowJob0 = sheetJob.createRow(0);
            XSSFCell cellJob0 = rowJob0.createCell(0);
            cellJob0.setCellValue("flow名称");
            XSSFCell cellJob1 = rowJob0.createCell(1);
            cellJob1.setCellValue("group名称");
            XSSFCell cellJob2 = rowJob0.createCell(2);
            cellJob2.setCellValue("job名称");
            XSSFCell cellJob3 = rowJob0.createCell(3);
            cellJob3.setCellValue("备注");
            XSSFCell cellJob4 = rowJob0.createCell(4);
            cellJob4.setCellValue("依赖");
            XSSFCell cellJob5 = rowJob0.createCell(5);
            cellJob5.setCellValue("类型");
            XSSFCell cellJob6 = rowJob0.createCell(6);
            cellJob6.setCellValue("命令");
            XSSFCell cellJob7 = rowJob0.createCell(7);
            cellJob7.setCellValue("参数变量");
            XSSFCell cellJob8 = rowJob0.createCell(8);
            cellJob8.setCellValue("重试次数");
            XSSFCell cellJob9 = rowJob0.createCell(9);
            cellJob9.setCellValue("重试间隔");
            XSSFCell cellJob10 = rowJob0.createCell(10);
            cellJob10.setCellValue("超时时间");
            
            FileOutputStream output;
            try {
                output = new FileOutputStream(filepath+"/"+filename);
                wb.write(output);
                output.flush();
                output.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                logger.info("excle模板创建异常："+e.toString());
            }    
        }