package com.mark;

import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import java.sql.*;

public class Read {
	public List list;

	boolean flag = false;

	Conn c;

	Connection con;

	Statement stmt;

	PreparedStatement pstmt;

	ResultSet rs;

	Vo vo;

	int num = 0;

	public boolean insert(List list) {
		String sql = "insert into excel values(?,?,?)";
		try {
			c = new Conn();
			con = c.getConn();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				vo = (Vo) list.get(i);
				pstmt.setString(1, vo.getId());
				pstmt.setString(2, vo.getNum1());
				pstmt.setString(3, vo.getNum2());
				pstmt.executeUpdate();
			}
			con.commit();
			flag = true;
		} catch (Exception e) {
			try {
				con.rollback();
			} catch (Exception w) {
				w.printStackTrace();
			}
			flag = false;
			e.printStackTrace();
		} finally {
			Conn.closeConn();
		}
		return flag;
	}

	public List readData(String filename) {
		list = new ArrayList();
		Workbook book = null;
		try {
			book = Workbook.getWorkbook(new java.io.File(filename));
			// 獲得第一個工作表對象
			Sheet sheet = book.getSheet(0);
			// 得到第一列第一行的單元格
			int columnum = sheet.getColumns(); // 得到列數(shù)
			int rownum = sheet.getRows(); // 得到行數(shù)
			for (int i = 0; i < rownum; i++) // 循環(huán)進行讀寫
			{

				Vo vo = new Vo();
				vo.setId(sheet.getCell(0, i).getContents());
				vo.setNum1(sheet.getCell(1, i).getContents());
				vo.setNum2(sheet.getCell(2, i).getContents());
				list.add(vo);
			}
		} catch (Exception e) {
			System.out.println(e);
		} finally {
			book.close();
		}
		return list;
	}

}