POI按照模版导出Excel数据

POI按照模版导出Excel数据

一、所需jar包

二、ExportExcelUtil.java

package com.upsoft.exportexcel;



import java.io.FileInputStream;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;

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;



public class ExportExcelUtil {



public static void main(String[] args) throws Exception {



ExportExcelUtil export = new ExportExcelUtil();

String srcFilePath = "d:/人员信息模板.xlsx";

String fileName = "test_" + System.currentTimeMillis() + ".xlsx";

String desFilePath = "d:/" + fileName;



export.exportExcel(srcFilePath,desFilePath);

}



//根据指定的excel模板导出数据

public void exportExcel(String srcFilePath,String desFilePath) throws Exception {



//创建Excel文件的输入流对象

FileInputStream fis = new FileInputStream(srcFilePath);

//根据模板创建excel工作簿

XSSFWorkbook workBook = new XSSFWorkbook(fis);

//创建Excel文件输出流对象

FileOutputStream fos = new FileOutputStream(desFilePath);

//获取创建的工作簿第一页

XSSFSheet sheet = workBook.getSheetAt(0);

//给指定的sheet命名

workBook.setSheetName(0,"2016-11-30"); 



//修改标题

XSSFRow row = sheet.getRow(0);

XSSFCell cell = row.getCell(0);

//获取指定单元格值

String s = cell.getStringCellValue();

cell.setCellValue("修改后的标题为:"+s);



//获取当前sheet最后一行数据对应的行索引

int currentLastRowIndex = sheet.getLastRowNum();

int newRowIndex = currentLastRowIndex + 1;

XSSFRow newRow = sheet.createRow(newRowIndex);

//开始创建并设置该行每一单元格的信息，该行单元格的索引从 0 开始

int cellIndex = 0;



//创建一个单元格，设置其内的数据格式为字符串，并填充内容，其余单元格类同

XSSFCell newNameCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);

newNameCell.setCellValue("乔玉宝");

XSSFCell newGenderCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_STRING);

newGenderCell.setCellValue("男");

XSSFCell newAgeCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC);

newAgeCell.setCellValue(25);

XSSFCell newAddressCell = newRow.createCell(cellIndex++, Cell.CELL_TYPE_NUMERIC);

newAddressCell.setCellValue("重庆市渝北区");



workBook.write(fos);



//关闭流

fis.close();

fos.flush();

fos.close();

System.out.println("导出成功");

}

}

三、结果

1、模版Excel

 

 

2、导出数据后的Excel