Welcome to : TechnicalPage.Net: Read Excel

Showing posts with label Read Excel. Show all posts

ExcelData

Get Excel data of respective types using ITERATION:

import java.io.File;

import java.io.FileInputStream;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCellData {

public static void main(String[] args) throws Exception {

FileInputStream file = new FileInputStream(new File("Excel file with path.xlsx"));

XSSFWorkbook workbook = new XSSFWorkbook(file);

XSSFSheet sheet = workbook.getSheetAt(0);

              Double numericValue_Cell= sheet.getRow(3).getCell(3).getNumericCellValue();

// a particular cell value

System.out.println("numericValue_Cell = "+numericValue_Cell);

String stringValue_Cell = sheet.getRow(2).getCell(2).getStringCellValue();

System.out.println("stringValue_Cell = "+stringValue_Cell);

boolean booleanValue_Cell = sheet.getRow(4).getCell(1).getBooleanCellValue();

System.out.println("booleanValue_Cell = "+booleanValue_Cell);

String formulaValue_Cell = sheet.getRow(3).getCell(3).getCellFormula();

System.out.println("formulaValue_Cell = "+formulaValue_Cell);

System.out.println("\n-----Iteration Part -----\n");

//To read all the values of respective type

Iterator iterateROWS = sheet.iterator();

while(iterateROWS.hasNext()) {

Row row = iterateROWS.next();

Iterator iterateColumns = row.cellIterator();

while(iterateColumns.hasNext()) {

Cell cell = iterateColumns.next();

switch(cell.getCellType()) {

//To get only numberic(double) values used in the sheet

case Cell.CELL_TYPE_NUMERIC:

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

System.out.println("numericValue_All = "+numericValue_All);

break ;

//To get only String values used in the sheet

case Cell.CELL_TYPE_STRING:

String stringValue_All = String.valueOf(cell.getStringCellValue());

System.out.println("stringValue_All = "+stringValue_All);

break ;

//To get only boolean values used in the sheet

case Cell.CELL_TYPE_BOOLEAN:

String booleanValue_All = String.valueOf(cell.getBooleanCellValue());

System.out.println("booleanValue_All = "+booleanValue_All);

break ;

//To get only formulas used in the sheet

case Cell.CELL_TYPE_FORMULA:

String formulaValue_All = String.valueOf(cell.getCellFormula());

System.out.println("formulaValue_All = "+formulaValue_All);

break ;

}}}}}

Output:

numericValue_Cell = 479.0

stringValue_Cell = TTT

booleanValue_Cell = true

formulaValue_Cell = C5*D3+C1

-----Iteration Part -----

numericValue_All = 123.0

numericValue_All = 56.0

numericValue_All = 100.1

numericValue_All = 2.0

numericValue_All = 55.0

numericValue_All = 11.01

numericValue_All = 66.44

stringValue_All = Aaa

stringValue_All = Bbb

stringValue_All = yyyy

stringValue_All = eee

stringValue_All = DDD

stringValue_All = TTT

stringValue_All = SSS

stringValue_All = ggg

booleanValue_All = true

booleanValue_All = false

formulaValue_All = C5*D3+C1

formulaValue_All = C1+C4

Java Code for Lotteries in the format of MegaMillions or PowerBall part 2 of 2

In this tutorial, we find out the highly probable lottery numbers based on the most recent 20 draws. This code is written in the format of Mega Millions or PowerBall. In this, we write the most recent 20 draws in Excel, sheet1 for first 5 numbers and sheet6 for 6^th number. Our code reads 5 numbers randomly from sheet1 and 1 number randomly from sheet6. While reading the 5 numbers, it might read same number again, so we have taken care of this situation in our code to avoid any repetition of the number.

In Part 1 of this tutorial, we have discussed about different approach. Click here to go to part 1

package packageName;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.util.Iterator;

import java.util.Random;

import org.apache.poi.ss.usermodel.Cell;

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.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.gargoylesoftware.htmlunit.javascript.host.Console;

public class ProbableNumber {

public static void main(String[] args) throws Exception {

Random rand = new Random();

File file = new File("path of Excel file.xlsx"); // import "java.io.File" at this point

System.out.println("file is = " + file);

FileInputStream fis = new FileInputStream(file); // import "java.io.FileInputStream" at this point

System.out.println("fis is = " + fis);

Workbook wb = new XSSFWorkbook(fis);

Sheet excelSheet = wb.getSheet("Sheet1");

System.out.println("Sheet is = " + excelSheet);

int lastRow = excelSheet.getLastRowNum();

System.out.println("Last row is = " + lastRow);

System.out.println("First row is = " + excelSheet.getFirstRowNum());

int numOfRows = lastRow + 1; // because the rows start from 0

System.out.println("Number of rows is = " + numOfRows);

// to find the number of column

Row row = excelSheet.getRow(lastRow); // import "org.apache.poi.ss.usermodel.Row" at this point.

// Find the last cell in the sheet to find number of column.

int lastCell = row.getLastCellNum();

System.out.println("Last cell is = " + lastCell);

System.out.println("Number of columns is = " + lastCell);

int[] goodNumber = new int[5];

int[] uniqueNumberNoDuplicate = new int[1]; // Let’s keep the array size 1, the size will grow according to the “for loop”.

int flag = 0;

for(int i=0;i<=4;i++){

//to find the row of random number

int x1 = rand.nextInt(numOfRows);

String x = Integer.toString(x1);

System.out.println("The row for random number is = " + x);

//To find the column of random number

int y1 = rand.nextInt(lastCell);

String y = Integer.toString(y1);

System.out.println("The column for random number is = " + y);

//To find the value of the respective cell

String randomCELL = x.concat(y);

double randomCell_Value = excelSheet.getRow(x1).getCell(y1).getNumericCellValue();

int int_randomCELL = (int)randomCell_Value; //Typecasting , coverts double to int value.

System.out.println("cell value for "+ i+"th number = "+int_randomCELL);

//first do below comparision and then only add in the array

//if same number is repeated, then to replace the number

//every time, add the generated number in an array and every time a new number is created , compare that with the existing number and if it is unique , then add to the array.

do{

if(i>=1){

for(int a=0; a<=uniqueNumberNoDuplicate.length; a++){

if(int_randomCELL == goodNumber[a] )

flag = 1;

}

if(flag == 1){

System.out.println("This number is duplicate of already picked number, regenerate another number.");

x1 = rand.nextInt(numOfRows);

x = Integer.toString(x1);

System.out.println("The random number for row is = " + x);

//To find the column

y1 = rand.nextInt(lastCell);

y = Integer.toString(y1);

System.out.println("The random number for column is = " + y);

randomCELL = x.concat(y);

randomCell_Value = excelSheet.getRow(x1).getCell(y1).getNumericCellValue();

int_randomCELL = (int)randomCell_Value; //Typecasting , coverts double to int value.

System.out.println("The new number = " + int_randomCELL);

flag = 0;

}

}while(flag == 1);

goodNumber[i] = int_randomCELL;

}

//For the sixth number

excelSheet = wb.getSheet("Sheet6");

System.out.println("Sheet is = " + excelSheet);

lastRow = excelSheet.getLastRowNum();

System.out.println("Last row is = " + lastRow);

System.out.println("First row is = " + excelSheet.getFirstRowNum());

numOfRows = lastRow + 1; // because the rows start from 0

System.out.println("Number of rows is = " + numOfRows);

//to find the row

int x1 = rand.nextInt(numOfRows);

String x = Integer.toString(x1);

System.out.println("The random number for row is = " + x);

//The cell value

double randomCell_Value = excelSheet.getRow(x1).getCell(0).getNumericCellValue();

int sixth_Number = (int)randomCell_Value; //Typecasting , coverts double to int value.

System.out.println("The 6th number is = "+sixth_Number);

System.out.println("Good Luck with this number = "+goodNumber[0]+" "+goodNumber[1]+" "+goodNumber[2]+" "+goodNumber[3]+" "+goodNumber[4]+" "+sixth_Number);

// }

}

INPUT:

Output:

http://www.technicalpage.net/search/label/SQL

ExcelData

Java Code for Lotteries in the format of MegaMillions or PowerBall part 2 of 2