Esempio n. 1
0
    //导出数据
    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, "数据导出错误!");
        }
    }
Esempio n. 2
0
    //导入保存
    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);
    }