public ActionResult DeleteConfirmed(int id)
{
excel excel = db.excel.Find(id);
db.excel.Remove(excel);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult Edit([Bind(Include = "ID,CustomerName,Email,Residence,SalesPerson,ItemBought,MonthBought,Price,Qty,Sales")] excel excel)
{
if (ModelState.IsValid)
{
db.Entry(excel).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(excel));
}
public ActionResult Edit([Bind(Include = "ID,CreatedBy,EquipmentName,ReleaseDate,TypeofAssets,Barcode,Location,RoomName")] excel excel)
{
if (ModelState.IsValid)
{
db.Entry(excel).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(excel));
}
public ActionResult Create([Bind(Include = "ID,CustomerName,Email,Residence,SalesPerson,ItemBought,MonthBought,Price,Qty,Sales")] excel excel)
{
if (ModelState.IsValid)
{
db.excel.Add(excel);
db.SaveChanges();
return(RedirectToAction("Index"));
}
return(View(excel));
}
public string Excel2Oracle(XSSFSheet sheet, string tableName, int cot, ref string comment, ref string primaryKey)
{
var str = "";
var primarykey = "";
for (int i = 0; i <= cot; i++)
{
excel sFC = new excel();
sFC.name = sheet.GetRow(i).GetCell(1) == null ? "" : sheet.GetRow(i).GetCell(1).ToString();
if (string.IsNullOrEmpty(sFC.name))
{
cot -= 1;
str = str.Remove(str.Length - 3, 2);
continue;
}
str += (sFC.name + " ");
sFC.desc = sheet.GetRow(i).GetCell(2) == null ? "" : sheet.GetRow(i).GetCell(2).ToString();
sFC.type = sheet.GetRow(i).GetCell(3) == null ? "" : sheet.GetRow(i).GetCell(3).ToString();
str += sFC.type;
sFC.isNull = sheet.GetRow(i).GetCell(5) == null ? "" : sheet.GetRow(i).GetCell(5).ToString();
var length = sheet.GetRow(i).GetCell(4) == null ? null : sheet.GetRow(i).GetCell(4).ToString();
sFC.defaultContext = sheet.GetRow(i).GetCell(6) == null ? "" : sheet.GetRow(i).GetCell(6).ToString();
if (sFC.type.ToLower() != "date" && !string.IsNullOrEmpty(length))
{
sFC.length = Convert.ToInt32(length);
str += $"({sFC.length}) ";
}
if (sFC.defaultContext.Length != 0)
{
str += (" DEFAULT " + sFC.defaultContext + " ");
}
if (sFC.name == "ID")
{
primarykey = sheet.GetRow(i).GetCell(7) == null ? "" : sheet.GetRow(i).GetCell(7).ToString();
primaryKey = primarykey;
}
if (sFC.isNull.Length != 0)
{
str += (" NOT NULL ");
}
if (i != cot)
{
str += " ,\n";
}
comment += $"COMMENT ON COLUMN {tableName}.{sFC.name} IS '{sFC.desc}';\r\n";
}
//str += $"CONSTRAINT {primarykey} PRIMARY KEY ( id ) ENABLE\n";
return(str);
}
void worker_DoWork(object sender, DoWorkEventArgs e)
{
List <string[]> cards = us.GetCards();
this.Invoke((MethodInvoker) delegate {
this.progressBar1.Maximum = cards.Count;
this.progressBar1.Minimum = 0;
this.progressBar1.Value = 0;
});
List <Model> ModelList = new List <Model>();
for (int i = 0; i < cards.Count; i++)
{
ModelList.Add(us.GetModel(cards[i][0], cards[i][1]));
worker.ReportProgress(i + 1);
}
if (ModelList.Count > 0)
{
this.Invoke((MethodInvoker) delegate
{
this.progressBar1.Maximum = ModelList.Count;
this.progressBar1.Minimum = 0;
this.progressBar1.Value = 0;
});
excel ex = new excel(ModelFile);
List <ManualResetEvent> manualEvents = new List <ManualResetEvent>();
for (int i = 0; i < ModelList.Count; i++)
{
AsyObject obj = new AsyObject();
obj.model = ModelList[i];
obj.ex = ex;
ManualResetEvent ma = new ManualResetEvent(false);
manualEvents.Add(ma);
obj.doevent = ma;
ThreadPool.QueueUserWorkItem(AsyncOperation, obj);
if (manualEvents.Count >= 60)
{
WaitHandle.WaitAll(manualEvents.ToArray());
manualEvents.Clear();
}
}
if (manualEvents.Count > 0)
{
WaitHandle.WaitAll(manualEvents.ToArray());
}
e.Result = ex.exportDir;
}
}
// GET: Assets/Edit/5
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
excel excel = db.excel.Find(id);
if (excel == null)
{
return(HttpNotFound());
}
return(View(excel));
}
public string Excel2MysqlMssql(XSSFSheet sheet, int cot, int dbType)
{
var str = "";
for (int i = 1; i <= cot; i++)
{
excel sFC = new excel();
sFC.name = sheet.GetRow(i).GetCell(0) == null ? "" : sheet.GetRow(i).GetCell(0).ToString();
if (sFC.name.Length == 0 || sFC.name == "" || sFC.name == null)
{
cot -= 1;
str = str.Remove(str.Length - 3, 2);
continue;
}
str += (sFC.name + " ");
sFC.desc = sheet.GetRow(i).GetCell(1) == null ? "" : sheet.GetRow(i).GetCell(1).ToString();
sFC.type = sheet.GetRow(i).GetCell(2) == null ? "" : sheet.GetRow(i).GetCell(2).ToString();
str += (sFC.type + " ");
sFC.isNull = sheet.GetRow(i).GetCell(3) == null ? "" : sheet.GetRow(i).GetCell(3).ToString();
sFC.defaultContext = sheet.GetRow(i).GetCell(4) == null ? "" : sheet.GetRow(i).GetCell(4).ToString();
if (sFC.isNull.Length != 0)
{
str += ("NOT NULL ");
}
else
{
str += (" NULL ");
}
if (sFC.defaultContext.Length != 0)
{
str += ("DEFAULT " + sFC.defaultContext + " ");
}
if (sFC.name == "ID")
{
str += "primary key ";
}
if (dbType != 2)
{
str += ("COMMENT '" + sFC.desc + "' ");
}
if (i != cot)
{
str += " ,\n";
}
}
return(str);
}
private bool Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string a = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string b = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string conStr = "";
string sheetName;
DataTable dt = new DataTable();
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(a, FilePath, isHDR);
break;
case ".xlsx": //Excel 07
conStr = string.Format(b, FilePath, isHDR);
break;
}
//Get the name of the First Sheet.
using (OleDbConnection con2 = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = con2;
con2.Open();
DataTable dtExcelSchema = con2.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con2.Close();
}
}
//Read Data from the First Sheet.
using (OleDbConnection con1 = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = new OleDbCommand())
{
using (OleDbDataAdapter oda = new OleDbDataAdapter())
{
cmd.CommandText = "SELECT * From [" + sheetName + "]";
cmd.Connection = con1;
con1.Open();
oda.SelectCommand = cmd;
oda.Fill(dt);
con1.Close();
}
}
}
int count_row = 0;
bool error_insert = false;
foreach (DataRow r in dt.Rows)
{
excel ex = new excel();
count_row++;
if (count_row == 1)
{
ex.fecha = fecha_g = r[1].ToString();
}
if (count_row >= 6 && r[6].ToString() != "")
{
ex.fecha = fecha_g;
try { ex.c_f = Convert.ToDouble(r[1].ToString().Replace(".", "")); } catch { ex.c_f = 0; }
try { ex.bod_usd = Convert.ToDouble(r[2].ToString().Replace(".", "")); } catch { ex.bod_usd = 0; }
try { ex.cm_stgo = Convert.ToDouble(r[3].ToString().Replace(".", "")); } catch { ex.cm_stgo = 0; }
try { ex.cm_qta = Convert.ToDouble(r[4].ToString().Replace(".", "")); } catch { ex.cm_qta = 0; }
try { ex.arica = Convert.ToDouble(r[5].ToString().Replace(".", "")); } catch { ex.arica = 0; }
try { ex.cod_producto = r[6].ToString(); } catch { ex.cod_producto = ""; }
try { ex.producto = r[7].ToString(); } catch { ex.producto = ""; }
try { ex.pack = r[8].ToString(); } catch { ex.pack = ""; }
try { ex.cajas_pallet = Convert.ToInt32(r[9].ToString().Replace(".", "")); } catch { ex.cajas_pallet = 0; }
try { ex.pallets_camion = Convert.ToInt32(r[10].ToString().Replace(".", "")); } catch { ex.pallets_camion = 0; }
try { ex.camio_v_metropol = Convert.ToInt32(r[11].ToString().Replace(".", "")); } catch { ex.camio_v_metropol = 0; }
try { ex.int_out_cm_qt_lv = Convert.ToInt32(r[12].ToString().Replace(".", "")); } catch { ex.int_out_cm_qt_lv = 0; }
try { ex.camio_has_viii = Convert.ToInt32(r[13].ToString().Replace(".", "")); } catch { ex.camio_has_viii = 0; }
try { ex.quillota = Convert.ToInt32(r[14].ToString().Replace(".", "")); } catch { ex.quillota = 0; }
try { ex.bod_sn_anton = Convert.ToInt32(r[15].ToString().Replace(".", "")); } catch { ex.bod_sn_anton = 0; }
try { ex.arica_ = Convert.ToInt32(r[16].ToString().Replace(".", "")); } catch { ex.arica_ = 0; }
try { ex.arica_usd = Convert.ToInt32(r[17].ToString().Replace(".", "")); } catch { ex.arica_usd = 0; }
//string estado = ReporteRNegocio.insert_datos_excel(ex);
//if (estado != "OK")
//{
// error_insert = true;
// break;
//}
}
}
if (error_insert)
{
l_mensaje.Text = "Error al cargar Excel";
}
else
{
l_mensaje.Text = "Excel cargado!";
}
return(true);
}
public static void 供应商手机号导出(Dictionary<string, string> dics, HttpResponseBase rs)
{
var excel = new excel(SpreadsheetVersion.EXCEL97);
excel.导出供应商手机号(dics,rs);
}
public static void 导出专家信息(HttpResponseBase rs)
{
var excel = new excel(SpreadsheetVersion.EXCEL97);
excel.专家信息(rs);
}
public static void 导出需求计划(long id, HttpResponseBase rs)
{
var excel = new excel(SpreadsheetVersion.EXCEL97);
excel.需求计划(id, rs);
}
private static void SetStyle(string val, ref excel.Range range)
{
range.Borders.Weight = 2;
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.HorizontalAlignment = 3;
range.ColumnWidth = val.Length * 5;
range.Value2 = val;
}
private void SetStyle(string val, ref excel.Range range)
{
range.Borders.Weight = 2;
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
range.Font.Size = 12;
range.RowHeight = 40;
range.HorizontalAlignment = 3;
range.ColumnWidth = 12;
range.WrapText = true;
range.Value2 = val;
}
private void SetRowStyle(string filePath, string val, ref excel.Range range, DataSet ds, DataSet ds_com, int r, int i)
{
range.Borders.Weight = 2;
range.Value2 = val;
range.Font.Size = 9;
range.RowHeight = 30;
range.HorizontalAlignment = -4152;
range.WrapText = true;
if ((filePath.IndexOf("一体化中心收入") > 0) && (ds_com != null))
{
string str = ds.Tables[0].Rows[r]["单位名称"].ToString();
if (ds_com.Tables[0].Select("name='" + str + "'").Length > 0)
{
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
if (i == 0)
{
range.HorizontalAlignment = -4131;
}
}
if (i == 1)
{
range.HorizontalAlignment = -4131;
}
}
if ((filePath.IndexOf("地税局基建环节征收") > 0) && (i < 7))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("地税局销售环节征收") > 0) && (i < 7))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("耕地占用税征收") > 0) && (i < 4))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("国税局建筑环节征收") > 0) && (i < 6))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("契税征收") > 0) && (i < 5))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("土地出让金宗地收入") > 0) && (i < 5))
{
range.HorizontalAlignment = -4131;
}
if ((filePath.IndexOf("一体化征收") > 0) && (i < 7))
{
range.HorizontalAlignment = -4131;
}
}
