//导出数据
protected void Button2_Click(object sender, EventArgs e)
{
DataTable dt = UserAccess.ExportUserData(userId);
string fileName = "AA生活记账数据导出(" + userId + ").xlsx";
string savePath = Server.MapPath("/Backup/Export/") + fileName;
try
{
AsposeExport(dt, savePath);
DownExcel(savePath, fileName);
}
catch
{
Utility.Alert(this, "数据导出错误！");
}
}
//导入保存
protected int[] SaveExcel(DataTable dt)
{
int[] arr = new int[2];
all = UserAccess.ExportUserData(userId);
catTypeList = UserCategoryAccess.GetCategoryTypeList(userId);
string dbProviderName = WebConfiguration.DbProviderName;
string dbConnectionString = WebConfiguration.DbConnectionString;
DbProviderFactory factory = DbProviderFactories.GetFactory(dbProviderName);
using (DbConnection conn = factory.CreateConnection())
{
conn.ConnectionString = dbConnectionString;
conn.Open();
DbTransaction tran = conn.BeginTransaction();
DbCommand comm = conn.CreateCommand();
comm.Connection = conn;
comm.Transaction = tran;
try
{
int n1 = 0;
int n2 = 0;
StringBuilder sb = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
string _itemType = GetItemTypeValue(dr["分类"].ToString());
string _itemName = dr["商品名称"].ToString();
int _catTypeId = GetCategoryTypeId(dr["商品类别"].ToString(), catTypeList);
string _itemPrice = dr["商品价格"].ToString();
DateTime _itemBuyDate = DateTime.Parse(dr["购买日期"].ToString());
int _recommend = (dr["推荐否"].ToString() == "否" ? 0 : 1);
ItemEntity item = new ItemEntity();
item.ItemType = _itemType;
item.ItemName = _itemName;
item.CategoryTypeID = _catTypeId;
item.ItemPrice = (_itemPrice == "" ? 0 : Double.Parse(_itemPrice));
item.ItemBuyDate = _itemBuyDate;
item.Recommend = _recommend;
if (CheckRepeat(item, catTypeList))
{
n2++;
continue;
}
sb.AppendLine("INSERT INTO ItemTable(ItemType, ItemName, CategoryTypeID, ItemPrice, ItemBuyDate, UserID, Recommend) VALUES('" +
item.ItemType + "','" + item.ItemName + "','" + item.CategoryTypeID + "','" + item.ItemPrice + "','" +
item.ItemBuyDate.ToString("yyyy-MM-dd") + " " + DateTime.Now.ToString("HH:mm:ss") + "','" + userId + "','" + item.Recommend + "');");
n1++;
}
arr[0] = n1;
arr[1] = n2;
if (sb.ToString() == "")
{
return(arr);
}
comm.CommandText = sb.ToString();
comm.ExecuteNonQuery();
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
finally
{
conn.Close();
}
}
return(arr);
}