TA的每日心情 | 奋斗 2018-8-7 16:39 |
---|
签到天数: 4 天 连续签到: 1 天 [LV.2]测试排长
|
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);
//
// }
}
|
|