//另存新檔
protected void Button1_Click(object sender, EventArgs e)
{
ExcelUtil eu = new ExcelUtil("Jeff_Yeh", "Jeff隨手記");
eu.AddGrid(thisOut, "TestA");
eu.AddGrid(Gd2, "TestB");
eu.Export(this, "Jeff");
}
public ActionResult ExportExcel(ParaUnAdvertisementLogFilter filter)
{
try
{
filter.CurrentPage = 1;
filter.PageSize = Int32.MaxValue;
var list = UnAdvertisementLogBo.Instance.ExportExcel(filter);
if (list != null && list.Any())
{
SysLog.Instance.Info(LogStatus.系统日志来源.后台, "联盟广告日志导出Excel", LogStatus.系统日志目标类型.联盟网站, 0, CurrentUser == null ? 0 : CurrentUser.Base.SysNo);
ExcelUtil.Export <CBUnAdvertisementLog>(list);
}
}
catch (Exception ex)
{
SysLog.Instance.Error(LogStatus.系统日志来源.后台, "联盟广告日志导出Excel" + ex.Message, LogStatus.系统日志目标类型.联盟网站, 0, ex, CurrentUser == null ? 0 : CurrentUser.Base.SysNo);
}
return(RedirectToAction("AdvLog"));
}
public FileResult OutToExcel()
{
string sheetname = ConfigurationManager.AppSettings["Sheet"];
List <Expert> list = seeiDb.Experts.ToList();
string filename = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";//获取当前时间
DataTable dt = ExcelUtil.ConvertToDataTable(list);
string[] headers = { "序号", "专家编号", "姓名", "性别", "单位性质", "单位信息", "技术职称", "从事领域", "邮箱", "办公电话", "手机号码",
"通信地址", "入库状态", "相关主页", "业务类别", "备注", "添加时间" };
string[] cellKes = { "sId", "eId", "eName", "gender", "unitProperty", "UnitDetailsOne", "academicTitles", "field", "email", "officePhone", "cellPphone",
"postalAddress", "beStatus", "personalUrl", "Categories", "remark", "addTime" };
MemoryStream ms = ExcelUtil.Export(dt, sheetname, headers, cellKes);
#region 将excel文件保存到服务器指定路径
//string xlsname = ConfigurationManager.AppSettings["Excel"];
// byte[] data = ms.ToArray();//Encoding.UTF8.GetBytes();
// string filePath = Server.MapPath("~/Content/xiaoyicun/" + xlsname + ".xls");
// FileManager.WriteBuffToFile(data, filePath);
#endregion
ms.Seek(0, SeekOrigin.Begin);
return(File(ms, "application/vnd .ms-excel", Url.Encode(filename)));
}
public string Run()
{
running = true;
System.Threading.Thread.Sleep(5000);
running = false;
return("Not Yet!");
DateTime dataProroga = new DateTime(2013, 09, 30);
string proroga = string.Format("{0:dd/MM/yyyy}", dataProroga);
DataTable anomalies = new DataTable();
anomalies.Columns.Add("Velina");
anomalies.Columns.Add("Contratto");
anomalies.Columns.Add("StatoAgente");
anomalies.Columns.Add("Banca");
DataTable lavorate = new DataTable();
lavorate.Columns.Add("Velina");
lavorate.Columns.Add("Contratto");
lavorate.Columns.Add("StatoAgente");
lavorate.Columns.Add("Banca");
DataTable nonlavorateNonEsistenti = new DataTable();
nonlavorateNonEsistenti.Columns.Add("Velina");
nonlavorateNonEsistenti.Columns.Add("Contratto");
nonlavorateNonEsistenti.Columns.Add("StatoAgente");
nonlavorateNonEsistenti.Columns.Add("Banca");
DataTable nonlavorateChiuse = new DataTable();
nonlavorateChiuse.Columns.Add("Velina");
nonlavorateChiuse.Columns.Add("Contratto");
nonlavorateChiuse.Columns.Add("StatoAgente");
nonlavorateChiuse.Columns.Add("Banca");
DataTable giaLavorate = new DataTable();
giaLavorate.Columns.Add("Velina");
giaLavorate.Columns.Add("Contratto");
giaLavorate.Columns.Add("StatoAgente");
giaLavorate.Columns.Add("Banca");
//DateTime proro = Convert.ToDateTime(proroga);
//MessageBox.Show(dataProroga.Date.ToString());
string findError = string.Empty;
for (int i = 0; i < databases.Count; i++)
{
try
{
DataTable exceltable = ExcelUtil.Import(databases[i][2]);
AccesUtil accobj = new AccesUtil();
accobj.InitConnection(databases[i][1]);
DataTable accestable = new DataTable();
foreach (DataRow excelrow in exceltable.Rows)
{
//bool found = false;
accestable = accobj.ExtractValues(string.Format("Select NumeroRapporto, DataChiusuraIncarico, PCSocieta, PC, DataProrogaRichiestaDaSocieta from Rapporti where NumeroRapporto like \"%{0}\"", excelrow[1].ToString().Trim().Substring(excelrow[1].ToString().Trim().Length - 7, 7)));
//if (excelrow[1].ToString().Substring(excelrow[1].ToString().Length - 7, 7).CompareTo(accesrow[0].ToString().Substring(accesrow[0].ToString().Length - 7, 7)) == 0)
if (accestable.Rows.Count > 0)
{
DataRow accesrow = accestable.Rows[0];
for (int r = 0; r < accestable.Rows.Count; r++)
{
if (string.IsNullOrEmpty(accestable.Rows[r][1].ToString()))
{
accesrow = accestable.Rows[r];
break;
}
}
findError = "Error is in 1st part";
//found = true;
if (string.IsNullOrEmpty(accesrow[1].ToString()))
{
findError = "ceva nu a mers la extragerea statopratica " + excelrow[0].ToString();
DataTable statoPratica = accobj.ExtractValues(string.Format("Select stato from pratiche where cod_vel={0}", excelrow[0].ToString()));
string notizia = string.Empty;
if (statoPratica.Rows.Count > 0)
{
notizia = getNota(excelrow[2].ToString().Trim(), statoPratica.Rows[0][0].ToString());
}
else
{
notizia = getNota(excelrow[2].ToString().Trim(), string.Empty);
}
if (!string.IsNullOrEmpty(notizia))
{
findError = "ceva nu a mers la comparatia prorogei " + excelrow[0].ToString();
if (accesrow[4].ToString().CompareTo(dataProroga.ToString()) != 0)
{
//accobj.ExecuteNonQuery(string.Format("Update NotiziaIncaricoPC set LunghezzaNota='{0}', Nota='{1}' where PCSocieta='{2}' and PCCliente='{3}' and DataNotizia=date()", getNota(excelrow[3].ToString().Trim(), statoPratica.Rows[0][0].ToString()).Length.ToString("D3"), getNota(excelrow[3].ToString().Trim(), statoPratica.Rows[0][0].ToString()), accesrow[2].ToString(), accesrow[3].ToString()));
if ((notizia == "220") || (notizia == "222") || (notizia == "568") || (notizia == "297"))
{
notizia = "il pdr e'' irregolare stiamo cercando rientrare dell''esposizione";
accobj.ExecuteNonQuery(string.Format("Insert into NotiziaIncaricoPC (PCSocieta,PCCliente,DataNotizia,LunghezzaNota,Nota,Data_Ultima_Modifica_Soc,Flag_DS,Flag_Old_New) values ('{0}','{1}',date(),'{2}','{3}',date(),'{4}','{5}')", accesrow[2], accesrow[3], notizia.Length.ToString("D3"), notizia, "S", "NEW"));
lavorate.ImportRow(excelrow);
}
else
{
accobj.ExecuteNonQuery(string.Format("Insert into Rapporti_Log Select * from Rapporti where NumeroRapporto='{0}'", accesrow[0].ToString()));
accobj.ExecuteNonQuery(string.Format("Update Rapporti_Log set DataInserimentoGruppo=now() where NumeroRapporto='{0}' and DataProrogaRichiestaDaSocieta is null", accesrow[0].ToString()));
accobj.ExecuteNonQuery(string.Format("Update Rapporti set Flag_DS='{0}', CodiceComunicazione='{1}', DataProrogaRichiestaDaSocieta=DateValue('{2}'), Data_Ultima_Modifica_Soc=date() where NumeroRapporto='{3}'", "S", "B", proroga, accesrow[0].ToString()));
accobj.ExecuteNonQuery(string.Format("Insert into NotiziaIncaricoPC (PCSocieta,PCCliente,DataNotizia,LunghezzaNota,Nota,Data_Ultima_Modifica_Soc,Flag_DS,Flag_Old_New) values ('{0}','{1}',date(),'{2}','{3}',date(),'{4}','{5}')", accesrow[2], accesrow[3], notizia.Length.ToString("D3"), notizia, "S", "NEW"));
lavorate.ImportRow(excelrow);
}
//break;
}
else
{
giaLavorate.ImportRow(excelrow);
//break;
}
}
else
{
anomalies.ImportRow(excelrow);
//break;
}
}
else
{
nonlavorateChiuse.ImportRow(excelrow);
//break;
}
}
else
{
//findError="error is in second part";
AccesUtil accgetnew = new AccesUtil();
DataTable ContrattoNuovo = new DataTable();
string contratto = string.Empty;
accgetnew.InitConnection(databases[i][3]);
ContrattoNuovo = accgetnew.ExtractValues(string.Format("Select NumeroRapportoNew, NumeroRapportoOld from buffer_conversione_rapporti where NumeroRapportoOld like \"%{0}\"", excelrow[1].ToString().Substring(excelrow[1].ToString().Length - 7, 7)));
if (ContrattoNuovo.Rows.Count > 0)
{
contratto = ContrattoNuovo.Rows[0][0].ToString();
}
accgetnew.CloseConnection();
DataTable accesnuovo = accobj.ExtractValues(string.Format("Select NumeroRapporto, DataChiusuraIncarico, PCSocieta, PC, DataProrogaRichiestaDaSocieta from Rapporti where NumeroRapporto='{0}'", contratto));
if (accesnuovo.Rows.Count > 0)
{
if (string.IsNullOrEmpty(accesnuovo.Rows[0][1].ToString()))
{
DataTable statoPraticanuovo = accobj.ExtractValues(string.Format("Select stato from pratiche where cod_vel={0}", excelrow[0].ToString()));
string notizia = string.Empty;
if (statoPraticanuovo.Rows.Count > 0)
{
notizia = getNota(excelrow[2].ToString().Trim(), statoPraticanuovo.Rows[0][0].ToString());
}
else
{
notizia = getNota(excelrow[2].ToString().Trim(), string.Empty);
}
if (!string.IsNullOrEmpty(notizia))
{
if (accesnuovo.Rows[0][4].ToString().CompareTo(dataProroga.ToString()) != 0)
{
//MessageBox.Show(accesnuovo.Rows[0][0].ToString() + " " + accesnuovo.Rows[0][2].ToString() + " " + accesnuovo.Rows[0][3].ToString());
//accobj.ExecuteNonQuery(string.Format("Update NotiziaIncaricoPC set LunghezzaNota='{0}', Nota='{1}' where PCSocieta='{2}' and PCCliente='{3}' and DataNotizia=date()", getNota(excelrow[3].ToString().Trim(), statoPratica.Rows[0][0].ToString()).Length.ToString("D3"), getNota(excelrow[3].ToString().Trim(), statoPratica.Rows[0][0].ToString()), accesrow[2].ToString(), accesrow[3].ToString()));
if ((notizia == "220") || (notizia == "222") || (notizia == "568") || (notizia == "297"))
{
notizia = "il pdr e'' irregolare stiamo cercando rientrare dell''esposizione";
accobj.ExecuteNonQuery(string.Format("Insert into NotiziaIncaricoPC (PCSocieta,PCCliente,DataNotizia,LunghezzaNota,Nota,Data_Ultima_Modifica_Soc,Flag_DS,Flag_Old_New) values ('{0}','{1}',date(),'{2}','{3}',date(),'{4}','{5}')", accesnuovo.Rows[0][2], accesnuovo.Rows[0][3], notizia.Length.ToString("D3"), notizia, "S", "NEW"));
lavorate.ImportRow(excelrow);
}
else
{
accobj.ExecuteNonQuery(string.Format("Insert into Rapporti_Log Select * from Rapporti where NumeroRapporto='{0}'", accesnuovo.Rows[0][0].ToString()));
accobj.ExecuteNonQuery(string.Format("Update Rapporti_Log set DataInserimentoGruppo=now() where NumeroRapporto='{0}' and DataProrogaRichiestaDaSocieta is null", accesnuovo.Rows[0][0].ToString()));
accobj.ExecuteNonQuery(string.Format("Update Rapporti set Flag_DS='{0}', CodiceComunicazione='{1}', DataProrogaRichiestaDaSocieta=DateValue('{2}'), Data_Ultima_Modifica_Soc=date() where NumeroRapporto='{3}'", "S", "B", proroga, accesnuovo.Rows[0][0].ToString()));
accobj.ExecuteNonQuery(string.Format("Insert into NotiziaIncaricoPC (PCSocieta,PCCliente,DataNotizia,LunghezzaNota,Nota,Data_Ultima_Modifica_Soc,Flag_DS,Flag_Old_New) values ('{0}','{1}',date(),'{2}','{3}',date(),'{4}','{5}')", accesnuovo.Rows[0][2], accesnuovo.Rows[0][3], notizia.Length.ToString("D3"), notizia, "S", "NEW"));
lavorate.ImportRow(excelrow);
//break;
}
}
else
{
giaLavorate.ImportRow(excelrow);
}
}
else
{
anomalies.ImportRow(excelrow);
}
}
else
{
nonlavorateChiuse.ImportRow(excelrow);
}
}
else
{
nonlavorateNonEsistenti.ImportRow(excelrow);
}
}
}
accobj.CloseConnection();
}
catch
{
return("Error !!!" + findError);
}
}
ExcelUtil.Export(@"D:\OCTAV\pentru svn\NET_AggiornaProrogheGeisp\anomalies\Anomalie.xlsx", anomalies);
ExcelUtil.Export(@"D:\OCTAV\pentru svn\NET_AggiornaProrogheGeisp\anomalies\Lavorate.xlsx", lavorate);
ExcelUtil.Export(@"D:\OCTAV\pentru svn\NET_AggiornaProrogheGeisp\anomalies\NonLavorateNonEsistono.xlsx", nonlavorateNonEsistenti);
ExcelUtil.Export(@"D:\OCTAV\pentru svn\NET_AggiornaProrogheGeisp\anomalies\NonLavorateChiuse.xlsx", nonlavorateChiuse);
ExcelUtil.Export(@"D:\OCTAV\pentru svn\NET_AggiornaProrogheGeisp\anomalies\GiaLavorate.xlsx", giaLavorate);
return("Done !!!");
}