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);
}
}
public CuMarketingModel(CuMarketingModel prev)
{
PrevPeriod = prev;
}