描述：简单的对excel的第一个Sheet表的读写操作
依赖包：apache.poi/poi-3.14、apache.poi/poi-ooxml-3.14
package excel;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import static okhttp3.internal.http.HttpDate.format;
public class excelTest1 {
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 = new SimpleDateFormat(
DATE_OUTPUT_PATTERNS);
@Test
public void createExcelTest() throws Exception {
excelTest1 ce = new excelTest1();
//ce.createExcelxls(path);
ce.createExcelxlsx(path1);
}
@Test
public void getExcelTest() throws Exception{
excelTest1 ce = new excelTest1();
ce.getExcelxlsx(path2);
}
//创建excel.xls
public void createExcelxls(String path) throws Exception {
//创建excel对象
HSSFWorkbook wb = new HSSFWorkbook();
//用文件对象创建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 = new FileOutputStream(path);
wb.write(output);
output.flush();
}
//创建excel.xlsx
public void createExcelxlsx(String path) throws Exception {
//创建excel对象
XSSFWorkbook wb = new XSSFWorkbook();
//用文件对象创建sheet对象
XSSFSheet sheet = wb.createSheet("这是第一个sheet页");
//用sheet对象创建行对象
XSSFRow row = sheet.createRow(0);
//创建单元格样式
CellStyle cellStyle = wb.createCellStyle();
//构造数据
List<Object> list = new ArrayList<>();
list.add("这是String");
list.add(1);
list.add(getDate());
int length = list.size();
for(int n=0;n<length;n++){
FileOutputStream output = new FileOutputStream(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) throws Exception{
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(path));
//得到excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到sheet页个数（从1开始数，但是取值的时候要从index=0开始）
int scount = wb.getNumberOfSheets();
System.out.println("sheet页的个数为："+scount);
for (int a =0;a<scount;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<rcount;i++){
//得到excel工作表的行
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<ccount;j++){
//得到excel工作表指定行的单元格
HSSFCell cell = row.getCell(j);
if(null!=cell){
//得到单元格类型
int cellType = cell.getCellType();
switch (cellType){
case HSSFCell.CELL_TYPE_STRING:
c = cell.getStringCellValue();
if(c.trim().equals("")||c.trim().length()<=0)
c="";
break;
case HSSFCell.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) throws Exception{
//得到excel工作簿对象
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));
//得到sheet页个数（从1开始数，但是取值的时候要从index=0开始）
int scount = wb.getNumberOfSheets();
System.out.println("sheet页的个数为："+scount);
for (int a =0;a<scount;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<rcount;i++){
//得到excel工作表的行
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<ccount;j++){
//得到excel工作表指定行的单元格
XSSFCell cell = row.getCell(j);
if(null!=cell){
//得到单元格类型
int cellType = cell.getCellType();
switch (cellType){
case HSSFCell.CELL_TYPE_STRING:
c = cell.getStringCellValue();
if(c.trim().equals("")||c.trim().length()<=0)
c="";
break;
case HSSFCell.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)+"行的值为空");
}
}
}
}
//获取时间
public String getDate() {
Date d = new Date();
System.out.println(d);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String currentTime = sdf.format(d);
System.out.println(currentTime);
return currentTime;
}
@Test
public void fun(){
Date d = new Date();
System.out.println(d);
}
}
