Java項目之資料與表格的導入代碼開發

開發思路

開發思路解析

代碼開發

/*
*導入表格
*/
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;

import javax.swing.JOptionPane;
import com.ts.dao.SchoolMessageDao;
import com.ts.dao.TeacherMessageDao;
import com.ts.dao.TitleMessageDao;
import com.ts.dao.UserMessageDao;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelTest {
	Workbook workbook = null;
	Sheet sheet = null;
	//初始化對象
	SchoolMessageDao schoolMessageDao = new SchoolMessageDao();
	UserMessageDao userMessageDao = new UserMessageDao();
	TeacherMessageDao  teacherMessageDao = new TeacherMessageDao();
	public void importExcel() {
		try {
			// Excel檔案
			File file = new File("excel/import.xls");
			// 建立workbook
			workbook = Workbook.getWorkbook(file);
			// 擷取sheet頁
			sheet = workbook.getSheet(0);
			// 擷取sheet行數
			int rows = sheet.getRows();
			// 周遊sheet，讀取一條一條資料，存儲在pojo對象中
			String user = null;
			String passward = null;
			String sex=null;
			String name=null;
			String livel=null;
			String sum=null;
			String score=null;
			String logtime=null;
			String sql = null;
			String sql1 = null;
			String sql2 = null;
			for (int i = 1; i < rows; i++) {
				// 直接擷取單元格
				Cell userCell = sheet.getCell(0, i);
				Cell passwardCell = sheet.getCell(1, i);
				Cell sexCell = sheet.getCell(2, i);
				Cell nameCell = sheet.getCell(3, i);
				Cell livelCell = sheet.getCell(4, i);
				Cell sumCell = sheet.getCell(5, i);
				Cell scoreCell = sheet.getCell(6, i);
				Cell logtimeCell = sheet.getCell(7, i);
				// 擷取單元格中的内容
				user = userCell.getContents();
				passward = passwardCell.getContents();
				sex = sexCell.getContents();
				name = nameCell.getContents();
				livel = livelCell.getContents();
				sum = sumCell.getContents();
				score = scoreCell.getContents();
				logtime=logtimeCell.getContents();
				sql = "insert into login values(?,?)";
				sql1 = "insert into User values(?,?,?,?,?)";
				sql2 = "insert into user_game valu"
						+ "es(?,?,?,?,?)";
				SchoolMessageDao.saveUserInfo(sql, user, passward);
			    TeacherMessageDao.saveUserInfo(sql1, user, passward,name,sex,logtime);
				UserMessageDao.saveUserInfo(sql2, user, name,sum,livel,score);
			}
			// 将讀取到的資料存入資料庫
		} catch (Exception e) {
		} finally {
			JOptionPane.showMessageDialog(null,"導入成功");
			workbook.close();
		}
	}
}