readExcel

package com.legend.xlsx;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;




public class ReadExcelUtils {

private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
//用户名
private static String user = "scott";
//密码
private static String password = "tiger";
public static Connection conn;
public static PreparedStatement ps;
public static ResultSet rs;
public static Statement st;

public static void main(String[] args) throws IOException, SQLException {
String File = "D:\\apple.xlsx";
FileCheck(File);
ReadExcel(File);
}
public static void getConnection() {
try {
//初始化驱动包     
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();    
}
}

public static void ReadExcel(String fileName) throws IOException, SQLException {
Student2 stu = new Student2();
InputStream in = new FileInputStream(fileName);
Workbook wb = CheckExcelType(fileName);//检查Excel文件
Sheet sheet = wb.getSheetAt(0); //获取文件的指定工作表,默认第一个
List list = new ArrayList<>();
Row row = null;
Cell cell = null;
int totalRows = sheet.getPhysicalNumberOfRows(); // 总行数
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();//总列数
for (int i = 1; i < totalRows; i++) {
//创建一个数组 用来存储每一列的值
String[] str = new String[totalCells];
row = sheet.getRow(i);
for (int j = 0; j < totalCells; j++) { // 从第二行，开始，过滤表头
cell = (Cell) sheet.getCellComment(j, i); //获取批注里面的内容
cell = row.getCell(j);
// list.add(cell);
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {//字符串
list.add(cell.getStringCellValue());
// stu.setName(cell.getStringCellValue())  ;
}else if (cell.getCellStyle().getDataFormat() == 14) { // 时间格式
// list.add(cell.getDateCellValue());
// stu.setBirthday(cell.getDateCellValue());
SimpleDateFormat myFmt = new SimpleDateFormat("yyyy-MM-dd");
list.add(myFmt.format(cell.getDateCellValue()));

}else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { //数字
list.add((int)cell.getNumericCellValue());
// stu.setAge((int)cell.getNumericCellValue());
}



}
try {
getConnection();
String sql = "INSERT INTO classes(name,age,sex,birthday)"
+ "VALUES(" + "'"+ list.get(0)+"'" + "," +list.get(1)+","+"'" +list.get(2)+"'" + "," +"to_date('"+ list.get(3)+"','" +"yyyy-mm-dd')" +")";
st = (Statement)conn.createStatement();
 int count = st.executeUpdate(sql); // 执行插入操作的sql语句，并返回插入数据的个数     
 System.out.println("向student表中插入 " + count + " 条数据"); //输出插入操作的处理结果  
list.clear();
} catch (Exception e) {
e.printStackTrace();
}finally{
conn.close();
}
}
}

// 判断是不是Excel 07以上版本
public static Workbook CheckExcelType(String filename) throws IOException {
Workbook workbook = null;
InputStream in = new FileInputStream(filename); //创建输入流
if (filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(in);
}else {
new IOException("文件格式不一样:不是.xlsx类型");
}
return workbook;
}

// 判断是否有非法文件传入
public static void FileCheck(String filepath) throws FileNotFoundException {
      File file = new File(filepath);
      if (!filepath.endsWith("xlsx") || filepath.endsWith("csv")) {
      throw new FileNotFoundException("传入的文件不是指定的Excel类型");
}
     }
}