51Testing软件测试论坛

标题: 使用Apache的POI读取Excel单元格内容 [打印本页]

作者: 元芳你怎么看 时间: 2018-8-7 17:10
标题: 使用Apache的POI读取Excel单元格内容
package com.excel.poi.apache;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.openxml4j.opc.*;

public class ReadExcelTools {

// public static String readpath = null;
// public static XSSFWorkbook readworkbook = null;
// public static Sheet readsheet  = null;

// static double[] colorCellIndex = new double[20];
// static double[][] cellContent = new double[20][20]; //测试数据存储用数组

public static XSSFWorkbook readFile(String filepath) throws FileNotFoundException, IOException,InvalidFormatException{
// OPCPackage pkg = OPCPackage.open(new File(filepath)); //打开EXCEL文件
InputStream pkg = new FileInputStream(filepath);
XSSFWorkbook wb = new XSSFWorkbook(pkg);

   return wb;

}

//读取指定位置的单元格内容

public static double readAppointCell(XSSFWorkbook wb,int sheetNum,int rowNum,int cellNum){
double cellContent;

XSSFSheet sheet = wb.getSheetAt(sheetNum);
XSSFRow row = sheet.getRow(rowNum);
XSSFCell cell = row.getCell(cellNum);
cellContent = cell.getNumericCellValue();

return cellContent;
}

//读取所有测试数据内容

// public static double[][] readWholeFile(XSSFWorkbook wb,int sheetNum){
// Sheet sheet = wb.getSheetAt(sheetNum);
// int rowNum = sheet.getLastRowNum();
// int columnNum = sheet.getRow(0).getLastCellNum();
//
// double[][] contents=new double[20][11];
// for(int i=0;i<rowNum;i++){
// for(int j=0;j<columnNum;j++){
//
// contents[i][j] = sheet.getRow(i+1).getCell(j).getNumericCellValue();
// }
//
// }
//
// return contents;
//
// }

public static ArrayList<String[]> readWholeFile(XSSFWorkbook wb,int sheetNum){
Sheet sheet = wb.getSheetAt(sheetNum);
int rowNum = sheet.getLastRowNum();
int columnNum = sheet.getRow(0).getLastCellNum();
ArrayList<String[]> list = new ArrayList<String[]>();

// System.out.println(rowNum + "****"+columnNum);
for(int i=0;i<rowNum;i++){
String[] sgroup = new String[columnNum];
for(int j=0;j<columnNum;j++){
sgroup[j] = sheet.getRow(i+1).getCell(j).toString();
// System.out.println("#"+sgroup[j]);
// contents[i][j] = sheet.getRow(i+1).getCell(j).getNumericCellValue();
}
list.add(sgroup);

}

return list;

}

public static void main(String[] args){

XSSFWorkbook testDataFile = new XSSFWorkbook();
try{
testDataFile = ReadExcelTools.readFile("D:\\Practice\\testdata.xlsx");
}catch (FileNotFoundException e){
e.printStackTrace();
}catch(IOException e){
e.printStackTrace();
}catch(InvalidFormatException e){
e.printStackTrace();
}

ArrayList<String[]> testData = readWholeFile(testDataFile,0);

for(int i=0;i<testData.size();i++){
String[] s = testData.get(i);
for(int j = 0;j<s.length;j++){
System.out.println(s[j]);
}
}

try{
testDataFile.close();
}catch(IOException e){
e.printStackTrace();
}
// for(int i=0;i<testData.size();i++){
// String[] s = testData.get(i);
// for(int j = 0;j<s.length;j++){
// System.out.println(s[j]);
// }
// }
//
// for(int i = 0;i<testData.length;i++){
// for (int j = 0;j<testData[i].length;j++){
// System.out.println(testData[i][j]);
// }
// }
}

//
// //读取公式sheet名称，选择金融公式
// public static Sheet readFormulaSheet(XSSFWorkbook wb){
//    for (int i=0;i<wb.getNumberOfSheets();i++){ //打印sheet名称，即金融方案公式名称
//    String s  = wb.getSheetName(i);
//    System.out.println("金融方案编码：" + i + "=" + s);
//    }
//
////    int[] index = {0,0,0,0,0} ;
////    for (int i = 0;i<wb.getNumberOfSheets();i++){
////    index[i] = i;
////    }
////
//    //选择使用哪个金融公式
//    Scanner out = new Scanner(System.in);
//    System.out.println("请输入要测试测金融方案编码：");
//    int a = out.nextInt();
//
//    Sheet sheetPath = wb.getSheetAt(a);
//
//    return sheetPath;
// }
//
// //读取公式中需要输入的单元格位置；
//
// public void readInputCell(){
//
// }
//
//
// //读取测试数据sheet方法
// public static Sheet readTestSheet(XSSFWorkbook wb){
//
// Sheet sheetPath = wb.getSheetAt(0);
//       return sheetPath;
// }
//
// //读取测试数据内容的方法
// public static void readTestDataCell(Sheet sheet){
// int rowLength = sheet.getLastRowNum(); //工作表行数
// Row row = sheet.getRow(0);
// int cellLength = row.getLastCellNum();  //工作表列数
//
// System.out.println(rowLength);
// System.out.println(cellLength);
//
// for(int i=0;i<rowLength;i++){ //将测试数据的单元格内容以浮点数存入数组
// for(int j=0;j<cellLength;j++){
// cellContent[i][j]=sheet.getRow(i+1).getCell(j).getNumericCellValue();
//// System.out.println(cellIndex[i][j]);
// }
// }
//
// }
//
//
// //读取公式中需要填写内容的单元格位置
//// public class KeyValuePair{
//// public String Jian;
//// public double Zhi;
//// KeyValuePair(String k,double v){
//// Jian = k;
//// Zhi = v;
//// }
//// }
////
//// public ArrayList<KeyValuePair>  readColorCell(int readLine){
////
//// ArrayList<KeyValuePair> list = new ArrayList<KeyValuePair>();
//// return list;
//// }
////
//
//
//
// public static void main(String[] args){
//
// readpath = "D:\\Practice\\testdata.xlsx";
// XSSFWorkbook  readworkbook=null;
// Sheet readsheet = null;
//
// try{
// readworkbook = readFile(readpath);
// }catch(FileNotFoundException e){
// e.printStackTrace();
// }catch(IOException e){
// e.printStackTrace();
// }catch(InvalidFormatException e){
// e.printStackTrace();
// }
//
// //System.out.println(sheet1);
//
// readsheet = readFormulaSheet(readworkbook);
// readTestDataCell(readsheet);
//
// }

}

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)