java createcell_Java 简单的excel读写操作

packageexcel;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.hssf.usermodel.HSSFRow;importorg.apache.poi.hssf.usermodel.HSSFSheet;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.poifs.filesystem.POIFSFileSystem;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.CellType;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.junit.Test;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.text.Format;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;import staticokhttp3.internal.http.HttpDate.format;public classexcelTest1 {public String path = System.getProperty("user.dir") + "/src/main/resources/excelTest.xls";public String path1 = System.getProperty("user.dir") + "/src/main/resources/excelTest.xlsx";public String path2 = "C:\\Users\\Desktop\\基线脚本维护.xlsx";public final static String DATE_OUTPUT_PATTERNS = "yyyy-MM-dd";public final static SimpleDateFormat simpleDateFormat = newSimpleDateFormat(

DATE_OUTPUT_PATTERNS);

@Testpublic void createExcelTest() throwsException {

excelTest1 ce= newexcelTest1();//ce.createExcelxls(path);

ce.createExcelxlsx(path1);

}

@Testpublic void getExcelTest() throwsException{

excelTest1 ce= newexcelTest1();

ce.getExcelxlsx(path2);

}//创建excel.xls

public void createExcelxls(String path) throwsException {//创建excel对象

HSSFWorkbook wb = newHSSFWorkbook();//用文件对象创建sheet对象

HSSFSheet sheet = wb.createSheet("这是第一个sheet页");//用sheet对象创建行对象

HSSFRow row = sheet.createRow(0);//创建单元格样式

CellStyle cellStyle =wb.createCellStyle();//用行对象创建单元格对象Cell

Cell cell = row.createCell(0);//用cell对象读写。设置excel工作表值

cell.setCellValue(1);

FileOutputStream output= newFileOutputStream(path);

wb.write(output);

output.flush();

}//创建excel.xlsx

public void createExcelxlsx(String path) throwsException {//创建excel对象

XSSFWorkbook wb = newXSSFWorkbook();//用文件对象创建sheet对象

XSSFSheet sheet = wb.createSheet("这是第一个sheet页");//用sheet对象创建行对象

XSSFRow row = sheet.createRow(0);//创建单元格样式

CellStyle cellStyle =wb.createCellStyle();//构造数据

List list = new ArrayList<>();

list.add("这是String");

list.add(1);

list.add(getDate());int length =list.size();for(int n=0;n

FileOutputStream output= newFileOutputStream(path);//用行对象创建单元格对象Cell

Cell cell =row.createCell(n);//用cell对象读写。设置excel工作表值

cell.setCellValue(list.get(n).toString());

wb.write(output);

output.flush();

output.close();

}/*//用行对象创建单元格对象Cell

Cell cell0 = row.createCell(0);

Cell cell1 = row.createCell(0);

Cell cell2 = row.createCell(0);

//用cell对象读写。设置excel工作表值

cell0.setCellValue(1);

cell1.setCellValue("这是String");

cell2.setCellValue(getDate());

FileOutputStream output = new FileOutputStream(path);

wb.write(output);

output.flush();

output.close();*/}//读取Excel.xls文件的值

public void getExcelxls(String path) throwsException{

POIFSFileSystem fs= new POIFSFileSystem(newFileInputStream(path));//得到excel工作簿对象

HSSFWorkbook wb = newHSSFWorkbook(fs);//得到sheet页个数(从1开始数，但是取值的时候要从index=0开始)

int scount =wb.getNumberOfSheets();

System.out.println("sheet页的个数为："+scount);for (int a =0;a

String sheetName=wb.getSheetName(a);

System.out.println("第"+(a+1)+"个sheet页的名字为"+sheetName+",内容如下：");//得到excel工作表对象(0代表第一个sheet页)

HSSFSheet sheet =wb.getSheetAt(a);

HSSFSheet sheet1= wb.getSheet("第一个sheet页");//预定义单元格的值

String c = "";//得到工作表的有效行数(行数从0开始数，取值是从index=0开始)

int rcount =sheet.getLastRowNum();

System.out.println("第"+(a+1)+"个sheet页有"+rcount+"行");for(int i=0;i

HSSFRow row =sheet.getRow(i);if(null!=row){//获取一行(row)的有效单元格(cell)个数(列数从1开始数，取值的时候从index=0开始取)

int ccount =row.getLastCellNum();

System.out.println("第"+(i+1)+"行有"+ccount+"个单元格");for(int j=0;j

HSSFCell cell =row.getCell(j);if(null!=cell){//得到单元格类型

int cellType =cell.getCellType();switch(cellType){caseHSSFCell.CELL_TYPE_STRING:

c=cell.getStringCellValue();if(c.trim().equals("")||c.trim().length()<=0)

c="";break;caseHSSFCell.CELL_TYPE_NUMERIC:

c=String.valueOf(cell.getNumericCellValue());default:break;

}//String c = cell.getStringCellValue();

System.out.println("第"+(i+1)+"行"+(j+1)+"列的值为："+c+" ");

}else{

System.out.println("第"+(i+1)+"行"+(j+1)+"列的值为空"+" ");

}

}

System.out.println();

}else{

System.out.println("第"+(i+1)+"行的值为空");

}

}

}

}//读取Excel.xlsx文件的值

public void getExcelxlsx(String path) throwsException{//得到excel工作簿对象

XSSFWorkbook wb = new XSSFWorkbook(newFileInputStream(path));//得到sheet页个数(从1开始数，但是取值的时候要从index=0开始)

int scount =wb.getNumberOfSheets();

System.out.println("sheet页的个数为："+scount);for (int a =0;a

String sheetName=wb.getSheetName(a);

System.out.println("第"+(a+1)+"个sheet页的名字为"+sheetName+",内容如下：");//得到excel工作表对象(0代表第一个sheet页)

XSSFSheet sheet =wb.getSheetAt(a);

XSSFSheet sheet1= wb.getSheet("第一个sheet页");//预定义单元格的值

String c = "";//得到工作表的有效行数(行数从0开始数，取值是从index=0开始)

int rcount =sheet.getLastRowNum();

System.out.println("第"+(a+1)+"个sheet页有"+rcount+"行");for(int i=0;i

XSSFRow row =sheet.getRow(i);if(null!=row){//获取一行(row)的有效单元格(cell)个数(列数从1开始数，取值的时候从index=0开始取)

int ccount =row.getLastCellNum();

System.out.println("第"+(i+1)+"行有"+ccount+"个单元格");for(int j=0;j

XSSFCell cell =row.getCell(j);if(null!=cell){//得到单元格类型

int cellType =cell.getCellType();switch(cellType){caseHSSFCell.CELL_TYPE_STRING:

c=cell.getStringCellValue();if(c.trim().equals("")||c.trim().length()<=0)

c="";break;caseHSSFCell.CELL_TYPE_NUMERIC://如果是日期类型，需要时间转换

if(DateUtil.isCellDateFormatted(cell)){

Date theDate=cell.getDateCellValue();

c=simpleDateFormat.format(theDate);

}else{

c=String.valueOf(cell.getNumericCellValue());

}default:break;

}//String c = cell.getStringCellValue();

System.out.println("第"+(i+1)+"行"+(j+1)+"列的值为："+c+" ");

}else{

System.out.println("第"+(i+1)+"行"+(j+1)+"列的值为空"+" ");

}

}

System.out.println();

}else{

System.out.println("第"+(i+1)+"行的值为空");

}

}

}

}//获取时间

publicString getDate() {

Date d= newDate();

System.out.println(d);

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String currentTime=sdf.format(d);

System.out.println(currentTime);returncurrentTime;

}

@Testpublic voidfun(){

Date d= newDate();

System.out.println(d);

}

}