private void btnImport_Click(object sender, EventArgs e)
{
if (txtFilePath.Text.Equals(string.Empty))
{
MessageBox.Show("请先选择文件。");
return;
}
int sheetsCount = ExcelUtils.GetSheetsCount(txtFilePath.Text);
DataTable dtGame = new DataTable();;
DataTable dtGameDetail = new DataTable();
string errorMsg = string.Empty;
DAGame daGame = new DAGame();
Game myGame = new Game();
DataTable dtGameList = new DataTable();
dtGameList.Columns.Add("ID");
dtGameList.Columns.Add("NAME");
DataRow dr = dtGameList.NewRow();
for (int i = 0; i <= sheetsCount - 1; i++)
{
//比赛信息
dtGame = new DataTable();
dtGame.Columns.Add("League");
dtGame.Columns.Add("Home");
dtGame.Columns.Add("Visitor");
dtGame.Columns.Add("Gametime");
dtGame.Columns.Add("Score");
dtGame.Columns.Add("Result");
dtGame.Columns.Add("FinalSP");
//赔率信息
dtGameDetail = new DataTable();
dtGameDetail.Columns.Add("Changetime");
dtGameDetail.Columns.Add("B");
dtGameDetail.Columns.Add("C");
dtGameDetail.Columns.Add("D");
dtGameDetail.Columns.Add("Win");
dtGameDetail.Columns.Add("Draw");
dtGameDetail.Columns.Add("Lose");
dtGameDetail.Columns.Add("Changeminute");
//读取比赛信息
try
{
ExcelUtils.headRowCount = 1;
ExcelUtils.dataRowCount = 1;
ExcelUtils.dataColCount = 7;
ExcelUtils.dtExcel = dtGame;
ExcelUtils.indexDatatimeCol = "3,4";
dtGame = ExcelUtils.TranslateToTable(txtFilePath.Text, i);
}
catch
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
//读取赔率信息
try
{
ExcelUtils.headRowCount = 4;
ExcelUtils.dataRowCount = 0;
ExcelUtils.dataColCount = 8;
ExcelUtils.dtExcel = dtGameDetail;
ExcelUtils.indexDatatimeCol = "0";
dtGameDetail = ExcelUtils.TranslateToTable(txtFilePath.Text, i);
}
catch
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
myGame = new Game();
int result = daGame.GetMaxGameSN();
if (result == -1)
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
myGame.SN = result + 1;
myGame.League = dtGame.Rows[0]["League"].ToString();
myGame.Home = dtGame.Rows[0]["Home"].ToString();
myGame.Visitor = dtGame.Rows[0]["Visitor"].ToString();
myGame.GameTime = DateTime.Parse(dtGame.Rows[0]["Gametime"].ToString());
myGame.Score = dtGame.Rows[0]["Score"].ToString();
myGame.Result = dtGame.Rows[0]["Result"].ToString();
myGame.FinalSP = dtGame.Rows[0]["FinalSP"].ToString();
result = daGame.InsertGameInfo(myGame, dtGameDetail);
if (result == -1)
{
errorMsg += (i + 1).ToString() + "，";
continue;
}
dr = dtGameList.NewRow();
dr[0] = myGame.SN;
dr[1] = (i + 1).ToString() + "," + myGame.League + "---" + myGame.Home + " VS " + myGame.Visitor;
dtGameList.Rows.Add(dr);
}
SetCmbGame(dtGameList);
if (!string.IsNullOrEmpty(errorMsg))
{
MessageBox.Show("第 " + errorMsg.TrimEnd('，') + " 个Sheet导入失败。");
}
}
/// <summary>
/// 转换为DataTable(内存流)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream)
{
ExcelUtils utils = new ExcelUtils(stream);
return(utils.ToDataTable(0));
}
/// <summary>
/// 获取sheets数量
/// </summary>
public static int GetSheetsCount(string path)
{
ExcelUtils utils = new ExcelUtils(path);
return(utils.book.Sheets.Count);
}
/// <summary>
/// 转换为DataTable(文件路径)
/// </summary>
public static DataTable TranslateToTable(string path)
{
ExcelUtils utils = new ExcelUtils(path);
return(utils.ToDataTable(0));
}
/// <summary>
/// 转换为DataTable(内存流+表名)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
{
ExcelUtils utils = new ExcelUtils(stream);
return(utils.ToDataTable(sheetName));
}
/// <summary>
/// ת��ΪDataTable(�ļ�·��+������)
/// </summary>
public static DataTable TranslateToTable(string path, int sheetIndex)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.ToDataTable(sheetIndex);
}
/// <summary>
/// 转换为DataTable(文件路径+表索引)
/// </summary>
public static DataTable TranslateToTable(string path, int sheetIndex)
{
ExcelUtils utils = new ExcelUtils(path);
return(utils.ToDataTable(sheetIndex));
}
/// <summary>
/// ת��ΪDataTable(�ļ�·��+����)
/// </summary>
public static DataTable TranslateToTable(string path, string sheetName)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.ToDataTable(sheetName);
}
/// <summary>
/// ��ȡsheets����
/// </summary>
public static int GetSheetsCount(string path)
{
ExcelUtils utils = new ExcelUtils(path);
return utils.book.Sheets.Count;
}
/// <summary>
/// ת��ΪDataTable(�ڴ���)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream)
{
ExcelUtils utils = new ExcelUtils(stream);
return utils.ToDataTable(0);
}
/// <summary>
/// ת��ΪDataTable(�ڴ���+������)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, int sheetIndex)
{
ExcelUtils utils = new ExcelUtils(stream);
return utils.ToDataTable(sheetIndex);
}
/// <summary>
/// ת��ΪDataTable(�ڴ���+����)
/// </summary>
public static DataTable TranslateToTable(System.IO.Stream stream, string sheetName)
{
ExcelUtils utils = new ExcelUtils(stream);
return utils.ToDataTable(sheetName);
}
