//private void excelNo()
//{
// BookBasicBll bookBasicBll = new BookBasicBll();
// except.Columns.Add("书号", typeof(string));
// except.Columns.Add("id", typeof(string));
// except.Columns.Add("ISBN", typeof(string));
// except.Columns.Add("书名", typeof(string));
// except.Columns.Add("供应商", typeof(string));
// except.Columns.Add("出版日期", typeof(string));
// except.Columns.Add("单价", typeof(double));
// except.Columns.Add("预收数量", typeof(string));
// except.Columns.Add("进货折扣", typeof(string));
// except.Columns.Add("销售折扣", typeof(string));
// except.Columns.Add("备注", typeof(string));
// DataRowCollection count = addBookId().Rows;
// int counts = 0;
// foreach (DataRow row in count)//遍历excel数据集
// {
// try
// {
// string isbn = row[2].ToString().Trim();
// string bookName = ToSBC(row[3].ToString().Trim());
// double price = Convert.ToDouble(row[6]);
// DataRow[] rows = excelToDt().Select(string.Format("ISBN='{0}' and 书名='{1}' and 单价={2}", isbn, bookName, price));
// if (rows.Length == 1)//判断如果DataRow.Length为0，即该行excel数据不存在于表A中，就插入到dt3
// {
// //except.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]);
// BookBasicData basicData = new BookBasicData();
// basicData.BookNum = row[0].ToString();
// basicData.Isbn = isbn;
// basicData.BookName = bookName;
// basicData.Publisher = row[4].ToString();
// basicData.Time = row[5].ToString();
// //basicData.PublishTime = Convert.ToDateTime(row[5]);
// basicData.Price = Convert.ToDouble(row[6]);
// basicData.Catalog = row[7].ToString();
// basicData.Author = row[8].ToString();
// basicData.Remarks = row[9].ToString();
// basicData.Dentification = row[10].ToString();
// Result result = bookBasicBll.Insert(basicData);
// if (result == Result.添加失败)
// {
// Response.Write("导入失败，可能重复导入");
// Response.End();
// }
// else
// {
// counts++;
// }
// }
// else
// {
// excelToDt().Rows.RemoveAt();
// }
// }
// catch (Exception ex)
// {
// Response.Write(ex);
// Response.End();
// }
// }
// int cf = row - counts;
// Response.Write("导入成功，共导入数据" + counts + "条数据，共有重复数据" + cf + "条");
// Response.End();
//}
private void differentDt()
{
BookBasicBll bookBasicBll = new BookBasicBll();
int j = bookBasicBll.SelectCount();
//数据库无数据时直接导入excel
if (j <= 0)
{
//except = addBookId();
//except= GetDistinctSelf(addBookId(), "ISBN", "书名", "单价");
excelNo();
}
else
{
//except.Columns.Add("书号", typeof(string));
//except.Columns.Add("id", typeof(string));
//except.Columns.Add("ISBN", typeof(string));
//except.Columns.Add("书名", typeof(string));
//except.Columns.Add("供应商", typeof(string));
//except.Columns.Add("出版日期", typeof(string));
//except.Columns.Add("单价", typeof(double));
//except.Columns.Add("预收数量", typeof(string));
//except.Columns.Add("进货折扣", typeof(string));
//except.Columns.Add("销售折扣", typeof(string));
//except.Columns.Add("备注", typeof(string));
BookBasicData bookId = bookbll.getBookNum();
DataRowCollection count = addBookId().Rows;
int counts = 0;
DataTable dataTable = bookBasicBll.Select();
bool isNull = false;
int rowls = 0;
int kz = 0;
foreach (DataRow row in count)//遍历excel数据集
{
try
{
string isbn = row[2].ToString().Trim();
string bookName = ToSBC(row[3].ToString().Trim());
string supplier = row[4].ToString().Trim();
string accept = row[8].ToString().Trim();
string sale = row[9].ToString().Trim();
string price = row[6].ToString().Trim();
if (price == "" || isbn == "" || bookName == "")
{
price = "0";
isNull = true;
kz++;
continue;
}
DataRow[] rows = dataTable.Select(string.Format("ISBN='{0}' and bookName='{1}' and supplier='{2}' and price='{3}' and author='{4}' and remarks='{5}'", isbn, bookName, supplier, price, accept, sale));
//DataRow[] rows = dataTable.Select(string.Format("ISBN='{0}' and bookName='{1}' and price={2}", isbn, bookName, Convert.ToDouble(price)));
if (rows.Length == 0)//判断如果DataRow.Length为0，即该行excel数据不存在于表A中，就插入到dt3
{
//except.Rows.Add(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10]);
BookBasicData basicData = new BookBasicData();
basicData.BookNum = row[0].ToString();
basicData.Isbn = isbn;
basicData.BookName = bookName;
basicData.Publisher = row[4].ToString();
basicData.Time = row[5].ToString();
//basicData.PublishTime = Convert.ToDateTime(row[5]);
basicData.Price = Convert.ToDouble(price);
basicData.Catalog = row[7].ToString();
basicData.Author = row[8].ToString();
basicData.Remarks = row[9].ToString();
basicData.Dentification = row[10].ToString();
basicData.Remarks1 = row[11].ToString();
basicData.Remarks2 = row[12].ToString();
basicData.Remarks3 = row[13].ToString();
Result result = bookBasicBll.Insert(basicData);
if (result == Result.添加失败)
{
Response.Write("远程服务器未响应");
Response.End();
}
else
{
counts++;
}
}
rowls++;
}
catch (Exception ex)
{
Response.Write(ex);
Response.End();
}
}
string s = last;
Result reg = bookbll.updateBookNum(s); //更新书号
//bookId.NewBookNum = bookId.NewBookNum.Substring(bookId.NewBookNum.Length - 8);
//last = last.ToString().Substring(last.ToString().Length - 8);
//if (Convert.ToInt64(bookId.NewBookNum) < Convert.ToInt64(last))
//{
// Result reg = bookbll.updateBookNum(s); //更新书号
//}
Session["path"] = null; //清除路径session
int cf = row - counts;
if (counts == 0)
{
if (isNull)
{
Response.Write("导入成功，共导入数据" + counts + "条数据，共有重复数据" + cf + "条，共有错误数据" + kz.ToString());
Response.End();
}
else
{
Response.Write("导入失败，共导入数据" + counts + "条数据，共有重复数据" + cf + "条");
Response.End();
}
}
else
{
Response.Write("导入成功，共导入数据" + counts + "条数据，共有重复数据" + cf + "条");
Response.End();
}
}
}
//某字段table去重方法
private DataTable GetDistinctSelf(DataTable SourceDt, string field1, string field2, string field3)
{
int j = SourceDt.Rows.Count;
int counts = 0;
if (j > 1)
{
int k = j - 1;
int i = 0;
while (i <= k)
{
DataRow dr = SourceDt.Rows[i];
string isbn = dr[field1].ToString();
string bookName = dr[field2].ToString();
double price = Convert.ToDouble(dr[field3]);
DataRow[] rows = SourceDt.Select(string.Format("{0}='{3}' and {1}='{4}' and {2}={5}", field1, field2, field3, isbn, ToSBC(bookName), price));
//if (rows.Length > 1)
//{
// SourceDt.Rows.RemoveAt(i);
// k = k - 1;
//}
//else
//{
// i++;
//}
if (rows.Length == 1)
{
BookBasicData basicData = new BookBasicData();
basicData.BookNum = dr[0].ToString();
basicData.Isbn = isbn;
basicData.BookName = bookName;
basicData.Publisher = dr[4].ToString();
basicData.Time = dr[5].ToString();
//basicData.PublishTime = Convert.ToDateTime(rows[5]);
basicData.Price = Convert.ToDouble(dr[6]);
basicData.Catalog = dr[7].ToString();
basicData.Author = dr[8].ToString();
basicData.Remarks = dr[9].ToString();
basicData.Dentification = dr[10].ToString();
Result result = bookbll.Insert(basicData);
if (result == Result.添加失败)
{
Response.Write("导入失败，可能重复导入");
Response.End();
}
else
{
counts++;
}
i++;
}
else
{
SourceDt.Rows.RemoveAt(i);
k = k - 1;
}
}
int cf = row - counts;
Response.Write("导入成功，共导入数据" + counts + "条数据，共有重复数据" + cf + "条");
Response.End();
}
return(SourceDt);
}