1、Excel相关操作代码
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Component; /**
* @Description:
* @author * @date 创建时间：2016年12月8日下午2:38:47
* @version 1.0
*/
@Component
public class ExcelManage {
private HSSFWorkbook workbook = null; /**
* 判断文件是否存在
* @param filePath 文件路径
* @return
*/
public boolean fileExist(String filePath){
boolean flag = false;
File file = new File(filePath);
flag = file.exists();
return flag;
} /**
* 判断文件的sheet是否存在
* @param filePath 文件路径
* @param sheetName 表格索引名
* @return
*/
public boolean sheetExist(String filePath,String sheetName){
boolean flag = false;
File file = new File(filePath);
if(file.exists()){ //文件存在
//创建workbook
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
HSSFSheet sheet = workbook.getSheet(sheetName);
if(sheet!=null)
flag = true;
} catch (Exception e) {
e.printStackTrace();
}
}else{ //文件不存在
flag = false;
}
return flag;
}
/**
* 创建新Sheet并写入第一行数据
* @param filePath excel的路径
* @param sheetName 要创建的表格索引
* @param titleRow excel的第一行即表格头
* @throws IOException
* @throws FileNotFoundException
*/
public void createSheet(String filePath,String sheetName,String titleRow[]) throws FileNotFoundException, IOException{
FileOutputStream out = null;
File excel = new File(filePath); // 读取文件
FileInputStream in = new FileInputStream(excel); // 转换为流
workbook = new HSSFWorkbook(in); // 加载excel的 工作目录 workbook.createSheet(sheetName); // 添加一个新的sheet
//添加表头
Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行
try {
for(int i = 0;i < titleRow.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(filePath);
workbook.write(out);
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
99 /**
* 创建新excel.
* @param filePath excel的路径
* @param sheetName 要创建的表格索引
* @param titleRow excel的第一行即表格头
*/
public void createExcel(String filePath,String sheetName,String titleRow[]){
//创建workbook
workbook = new HSSFWorkbook();
//添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
workbook.createSheet(sheetName);
//新建文件
FileOutputStream out = null;
try {
//添加表头
Row row = workbook.getSheet(sheetName).createRow(0); //创建第一行
for(int i = 0;i < titleRow.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
}
out = new FileOutputStream(filePath);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 删除文件.
* @param filePath 文件路径
*/
public boolean deleteExcel(String filePath){
boolean flag = false;
File file = new File(filePath);
// 判断目录或文件是否存在
if (!file.exists()) {
return flag;
} else {
// 判断是否为文件
if (file.isFile()) { // 为文件时调用删除文件方法
file.delete();
flag = true;
}
}
return flag;
}
/**
* 往excel中写入.
* @param filePath 文件路径
* @param sheetName 表格索引
* @param object
*/
public void writeToExcel(String filePath,String sheetName, Object object,String titleRow[]){
//创建workbook
File file = new File(filePath);
try {
workbook = new HSSFWorkbook(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
FileOutputStream out = null;
HSSFSheet sheet = workbook.getSheet(sheetName);
// 获取表格的总行数
int rowCount = sheet.getLastRowNum() + 1; // 需要加一
try {
Row row = sheet.createRow(rowCount); //最新要添加的一行
//通过反射获得object的字段,对应表头插入
// 获取该对象的class对象
Class<? extends Object> class_ = object.getClass(); for(int i = 0;i < titleRow.length;i++){
String title = titleRow[i];
String UTitle = Character.toUpperCase(title.charAt(0))+ title.substring(1, title.length()); // 使其首字母大写;
String methodName = "get"+UTitle;
Method method = class_.getDeclaredMethod(methodName); // 设置要执行的方法
String data = method.invoke(object).toString(); // 执行该get方法,即要插入的数据
Cell cell = row.createCell(i);
cell.setCellValue(data);
}
out = new FileOutputStream(filePath);
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
2、main方法调用方式
public static void main(String[] args) {
String filePath = "result/数据汇总.xls";
String sheetName = "测试";
//Excel文件易车sheet页的第一行
String title[] = {"日期", "城市","新发布车源数"};
//Excel文件易车每一列对应的数据
String titleDate[] = {"date", "city","newPublish"};
ExcelManage em = new ExcelManage();
//判断该名称的文件是否存在
boolean fileFlag = em.fileExist(filePath);
if(!fileFlag){
em.createExcel(filePath,sheetName,title);
}
//判断该名称的Sheet是否存在
boolean sheetFlag = em.sheetExist(filePath,sheetName);
//如果该名称的Sheet不存在，则新建一个新的Sheet
if(!sheetFlag){
try {
em.createSheet(filePath,sheetName,title);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
YiCheData user = new YiCheData();
user.setDate("206-12-21");
user.setCity("北京");
user.setNewPublish("5");
//写入到excel
em.writeToExcel(filePath,sheetName,user,titleDate);
}
3、用于测试的bean类
public class YiCheData {
private String date;
private String city;
private String newPublish;
public String getDate() {
return date;
}
public void setDate(String date) {
this.date = date;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getNewPublish() {
eturn newPublish;
}
public void setNewPublish(String newPublish) {
this.newPublish = newPublish;
}
}
以上代码请参考：http://www.open-open.com/lib/view/open1433238476150.html