import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Locale;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.DateCell;
import java.sql.*;
public class ReadExcelSheet {

	public void init(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			contentReading(fs);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	//Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}

	public void contentReading(FileInputStream fileInputStream) throws ClassNotFoundException, SQLException {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[] = null;
		int rowCount = '0';
		int columnCount = '0';
		DateCell dc = null;
		int totalSheet = 0;

		try {
                        Class.forName("com.mysql.jdbc.Driver");
                        Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/demo","root","mysql");

                        



			ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);

			totalSheet = workbook.getNumberOfSheets();
			if(totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				for(int j=0;j<totalSheet ;j++) {
					System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
				}
			}

			//Getting Default Sheet i.e. 0
			s = workbook.getSheet(0);

			//Reading Individual Cell
			getHeadingFromXlsFile(s);

			//Total Total No Of Rows in Sheet, will return you no of rows that are occupied with some data
			System.out.println("Total Rows inside Sheet:" + s.getRows());
			rowCount = s.getRows();

			//Total Total No Of Columns in Sheet
			System.out.println("Total Column inside Sheet:" + s.getColumns());
			columnCount = s.getColumns();
                        System.out.println("-------------total column:"+columnCount);
			//Reading Individual Row Content
			for (int i = 1; i < rowCount; i++) {
                            PreparedStatement preparedStatement=con.prepareStatement("insert into  customer values(?,?,?,?,?,?,?,?)");
				//Get Individual Row
				rowData = s.getRow(i);
				if (rowData[0].getContents().length() != 0) { // the first date column must not null
					for (int j = 0; j < columnCount; j++) {
						switch (j) {
						case 0:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(1,rowData[j].getContents() );
                                                        break;
						case 1:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(2,rowData[j].getContents() );
                                                        break;
						case 2:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(3,rowData[j].getContents() );
                                                        break;
                                                case 3:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(4,rowData[j].getContents() );
                                                        break;
						case 4:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(5,rowData[j].getContents() );
                                                        break;
						case 5:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(6,rowData[j].getContents() );
                                                        break;
                                                case 6:
							System.out.print( rowData[j].getContents()+"\t");
                                                        preparedStatement.setString(7,rowData[j].getContents() );
                                                        break;
						case 7:
							System.out.print( rowData[j].getContents()+"\n");
                                                        preparedStatement.setString(8,rowData[j].getContents() );
                                                        break;

						default:
							break;
						}

					}
                                        preparedStatement.executeUpdate();
				}
			}
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}

		public static void main(String[] args) {
		try {
			ReadExcelSheet xlReader = new ReadExcelSheet();
			xlReader.init("C:/Users/Ranjeet/Desktop/SampleData.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}