void DoSQL()
{
Dictionary <string, string> dict = DataManager.Instance.FiledsDict;
//DateTime t_begin = DateTime.Now;
DateTime t1 = DateTime.Now;
DateTime t2 = DateTime.Now;
MySqlHelper.Instance.init();
int fileCount = 1;
int allCount = pathList.Count;
foreach (string fileFullName in pathList)
{
richTextBox1.Invoke(AsyncUIDelegate, new object[] { string.Format("共{1}个文件，当前执行：{0}//{1}\r\n", fileCount, allCount) });
CountNum cn = DataManager.Instance.mParseExcelFile.ExcelToMysql(fileFullName);
fileCount++;
richTextBox1.Invoke(AsyncUIDelegate, new object[] { string.Format("累计新增心悦用户:{0} ,累计重复心悦用户:{1}\r\n", cn.newAddCount, cn.repeatCount) });
//richTextBox1.AppendText(string.Format("累计新增心悦用户:{0} ,累计重复心悦用户:{1}\r\n", qqNewCount, qqRepeatCount));
t2 = DateTime.Now;
TimeSpan ts = t2 - t1;
richTextBox1.Invoke(AsyncUIDelegate, new object[] { string.Format("累计用时：{0}时{1}分{2}秒\r\n", ts.Hours, ts.Minutes, ts.Seconds) });
// richTextBox1.AppendText("累计用时："+ts.Seconds+"\r\n");
}
}
public CountNum ExcelToMysql(string fileName)
{
CountNum cn = new CountNum();
MySqlHelper.Instance.init();
Dictionary <string, string> dict = DataManager.Instance.FiledsDict;
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(cn);
}
IWorkbook workbook = null;
ISheet sheet = null;
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (workbook == null)
{
fs.Close();
return(cn);
}
sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
fs.Close();
return(cn);
}
try
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int rowCount = sheet.LastRowNum;
for (int i = 1; i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
if (row.GetCell(1).ToString() == "" || row.GetCell(4).ToString() == "")
{
continue; //无QQ号等级,无心悦等级 的不处理
}
// "insert into vipxy2 (in_time,{0},{1}) values (now(),{0},{1}) ON DUPLICATE KEY UPDATE update_time = now()",
string sql = "insert into vipxy2 (in_time,{0},{1}) values (now(),{0},{1}) ON DUPLICATE KEY UPDATE update_time = now()";
string str1 = "";
string str2 = "";
for (int j = 0; j < cellCount; j++)
{
string tempStr = row.GetCell(j).ToString().Trim();
if (tempStr == "")
{
continue;
}
str1 += dict[firstRow.GetCell(j).ToString().Trim()] + ",";
str2 += "'" + tempStr + "',";
}
str1 = str1.Substring(0, str1.Length - 1);//去掉最后的逗号
str2 = str2.Substring(0, str2.Length - 1);
sql = string.Format("insert into vipxy2 (in_time,{0}) values (now(),{1}) ON DUPLICATE KEY UPDATE update_time = now()", str1, str2);
int ret = MySqlHelper.Instance.Do(sql);
if (ret == 1)
{
cn.newAddCount++;
}
else if (ret == 2)
{
cn.repeatCount++;
}
}
fs.Close();
}
catch (Exception e)
{
fs.Close();
Console.WriteLine(e.Message);
return(cn);
}
return(cn);
}
