示例#1
0
        public int Import2Dt(Worksheet ws)
        {
            var cells       = ws.Cells;
            var productName = ws.Name;
            var sheetNo     = ws.Index;

            var      count = 0;
            DateTime maxReDate;
            int      maxId;

            using (var con = new OracleConnection(Connectionstr))
            {
                var cmd = new OracleCommand("SELECT max(RE_DATE)  FROM CUSTEELMARKETINGOUTPUTDATA where cnname='" + ws.Name + "'", con);
                var da  = new OracleDataAdapter(cmd);
                var ds  = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count == 0 || ds.Tables[0].Rows[0][0] == DBNull.Value)
                {
                    return(0);
                }
                maxReDate = Convert.ToDateTime(ds.Tables[0].Rows[0][0]);
            }
            using (var con = new OracleConnection(Connectionstr))
            {
                var cmd = new OracleCommand("SELECT max(ID)  FROM CUSTEELMARKETINGOUTPUTDATA ", con);
                var da  = new OracleDataAdapter(cmd);
                var ds  = new DataSet();
                da.Fill(ds);
                maxId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }

            CuMarketingModel prev = new CuMarketingModel();

            using (var con = new OracleConnection(Connectionstr))
            {
                con.Open();
                for (int i = 4; i < cells.Rows.Count; i++)
                {
                    var curDate = cells[i, 0].Value;
                    if (curDate == null || string.IsNullOrEmpty(curDate.ToString()))
                    {
                        continue;
                    }
                    var reDate = Convert.ToDateTime(curDate);
                    if (reDate <= maxReDate)
                    {
                        continue;
                    }
                    var temp = new CuMarketingModel(prev);
                    maxId++;
                    temp.id                    = maxId;
                    temp.code                  = "CuMar_" + sheetNo.ToString("0000");
                    temp.cnname                = productName;
                    temp.re_date               = reDate;
                    temp.sheet_no              = sheetNo;
                    temp.unit                  = "吨";
                    temp.Output_YTD            = GetDec(cells[i, 1].Value);
                    temp.Sell_Totle_YTD        = GetDec(cells[i, 2].Value);
                    temp.Sell_Direct_YTD       = GetDec(cells[i, 3].Value);
                    temp.Sell_Distribution_YTD = GetDec(cells[i, 4].Value);
                    temp.Sell_Retail_YTD       = GetDec(cells[i, 5].Value);
                    temp.Sell_Branch_YTD       = GetDec(cells[i, 6].Value);
                    temp.Sell_Export_YTD       = GetDec(cells[i, 7].Value);
                    temp.Stock_Opening         = GetDec(cells[i, 8].Value);
                    temp.Stock_Closing         = GetDec(cells[i, 9].Value);
                    try
                    {
                        maxId++;
                        string operationSql = "INSERT INTO CusteelMarketingOutputData values(" + maxId +
                                              ",'" + temp.code + "','" + temp.cnname + "','" + temp.re_date.ToString("dd-MMM-yyyy") + "',"
                                              + temp.sheet_no + ",'" + temp.unit + "'," + temp.Output_Month + "," +
                                              temp.Sell_Totle_Month + "," +
                                              temp.Sell_Direct_Month + "," +
                                              temp.Sell_Distribution_Month + "," +
                                              temp.Sell_Retail_Month + "," +
                                              temp.Sell_Branch_Month + "," +
                                              temp.Sell_Export_Month + "," +
                                              temp.Stock_Increase_Month + "," +
                                              temp.Output_YTD + "," +
                                              temp.Sell_Totle_YTD + "," +
                                              temp.Sell_Direct_YTD + "," +
                                              temp.Sell_Distribution_YTD + "," +
                                              temp.Sell_Retail_YTD + "," +
                                              temp.Sell_Branch_YTD + "," +
                                              temp.Sell_Export_YTD + "," +
                                              temp.Stock_Opening + "," +
                                              temp.Stock_Closing + "," +
                                              "sysdate) ";
                        var cmd = new OracleCommand(operationSql, con);
                        cmd.ExecuteNonQuery();
                    }
                    catch (OracleException e)
                    {
                        con.Close();
                        throw new Exception(e.Message);
                    }
                    count++;
                }
                con.Close();
                return(count);
            }
        }
示例#2
0
 public CuMarketingModel(CuMarketingModel prev)
 {
     PrevPeriod = prev;
 }