/// <summary>
/// 导出excel数据
/// </summary>
/// <param name="filename"></param>
/// <param name="serieNo"></param>
/// <returns></returns>
public ActionResult ExportExcelChart(string filename, string serieNo)
{
ChartData chartData = null;// TempDataUtil.getChartData(serieNo+"_large");
if (chartData == null)
{
chartData = TempDataUtil.getChartData(serieNo);
}
IList<ExcelData> datas = new List<ExcelData>();
ExcelData data = new ExcelData();
string reportname = string.Empty;
bool isMulti = false;
if (chartData != null)
{
isMulti = ReportBuilder.isMulti(chartData.units);
reportname = (filename.Equals("chart") ? "" : filename) + chartData.name.Replace("/", "").Replace(" ", "");
IList<string> xList = new List<string>();
string xname = Resources.SunResource.CUSTOMREPORT_CHART_TIME;
if (chartData.names.Length > 0 && !string.IsNullOrEmpty(chartData.names[0]))
{
xname = chartData.names[0] + "[" + chartData.units[0] + "]";
}
xList.Add(xname);
int n = 1;// chartData.categories.Length > 100 ? 12 : 1;
for (int k = 0; k < chartData.categories.Length; k = k + n)
{
string x = chartData.categories[k];
xList.Add(x);
}
data.Rows.Add(xList);
IList<string> tmps = null;
string[] series = new string[chartData.series.Count()];
for (int i = 0; i < chartData.series.Count(); i++)
{
if (i > 0 && isMulti)
{
data = new ExcelData();
}
tmps = new List<string>();
string yname = chartData.series[i].name;
tmps.Add(yname);
for (int k = 0; k < chartData.series[i].data.Length; k = k + n)
{
float? f = chartData.series[i].data[k];
tmps.Add(f == null ? string.Empty : StringUtil.formatDouble(StringUtil.stringtoDouble(f.ToString()), "0.00"));
}
data.Rows.Add(tmps);
if (isMulti)
{
int slen = yname.IndexOf("[");
int endLen = yname.IndexOf("]");
if (slen > -1)
{
data.Title = yname.Substring(slen + 1, endLen - slen - 1);
}
else
{
data.Title = yname;
}
data.series = new String[] { chartData.series[i].name };
data.chartType = chartData.series[i].type;
datas.Add(data);
}
else
{
series[i] = chartData.series[i].name;
}
}
if (!isMulti && chartData.series.Count() > 0)
{
int slen = chartData.series[0].name.IndexOf("[");
int endLen = chartData.series[0].name.IndexOf("]");
if (slen > -1)
{
data.Title = chartData.series[0].name.Substring(slen + 1, endLen - slen - 1);
}
else
{
data.Title = chartData.series[0].name;
}
data.series = series;
data.chartType = chartData.series[0].type;
datas.Add(data);
}
}
ExcelStreamWriter xlsWriter = new ExcelStreamWriter(reportname, datas, chartData.units, reportname, "", isMulti);
//散列图暂不生成
if (chartData.series.Length > 0 && "scatter".Equals(chartData.series[0].type))
{
xlsWriter.Save(false);
}
else
{
xlsWriter.Save(true);
}
return File(xlsWriter.FullName, "text/xls; charset=UTF-8", urlcode(xlsWriter.FileName));
}
/// <summary>
/// 导出excel数据
/// </summary>
/// <param name="filename"></param>
/// <param name="serieNo"></param>
/// <returns></returns>
public ActionResult DownLoadExcelRunData(IList<string> allmts, IDictionary<string, IDictionary<string, string>> timemtMap, string filename)
{
IList<ExcelData> datas = new List<ExcelData>();
ExcelData data = new ExcelData();
bool isMulti = false;
//标题行
IList<string> xList = new List<string>();
xList.Add(" " + filename + " ");
xList.Add(Resources.SunResource.REPORT_TIME);
foreach (string key in timemtMap.Keys)
{
xList.Add(key);
}
data.Rows.Add(xList);
datas.Add(data);
IList<string> tmps = null;
foreach (string mtkey in allmts)
{
data = new ExcelData();
tmps = new List<string>();
tmps.Add(" ");
tmps.Add(mtkey);
foreach (string key in timemtMap.Keys)
{
//第一列是时间
tmps.Add(timemtMap[key][mtkey]);
}
data.Rows.Add(tmps);
datas.Add(data);
}
ExcelStreamWriter xlsWriter = new ExcelStreamWriter(filename, datas, new string[] { }, "", "", isMulti);
xlsWriter.Save(false);
return File(xlsWriter.FullName, "text/xls; charset=UTF-8", urlcode(xlsWriter.FileName));
}
public ActionResult Users_Output(string id)
{
int no = 0;
int.TryParse(id, out no);
Pager page = new Pager() { PageSize = ComConst.PageSize, PageIndex = no };
UserService userInfoService = UserService.GetInstance();
Hashtable para = new Hashtable();
para["page"] = page;
para["startDate"] = Request.QueryString["sd"];
para["endDate"] = Request.QueryString["ed"];
para["hasplants"] = Request.QueryString["hasplants"];
IList<User> users = userInfoService.GetUsersByPage(para);
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData data = new ExcelData();
IList<string> temp = new List<string>();
temp.Add("用户名");
temp.Add("注册电站");
temp.Add("国家");
temp.Add("性别");
temp.Add("创建时间");
data.Rows.Add(temp);
foreach (User user in users)
{
temp = new List<string>();
temp.Add(user.username);
temp.Add(user.HasPlantsStr);
temp.Add(user.country);
temp.Add(user.sex.Equals("0") ? "男" : "女");
temp.Add(user.createDate.ToString(ComConst.DateFormat));
data.Rows.Add(temp);
}
eDatas.Add(data);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("用户列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Collector_Output(string id, string sd, string ed)
{
Hashtable hashpara = new Hashtable();
hashpara["sd"] = sd;
hashpara["ed"] = ed;
int no = 0;
int.TryParse(id, out no);
Pager page = new Pager() { PageSize = ComConst.PageSize, PageIndex = no };
hashpara["page"] = page;
CollectorInfoService collectorInfoService = CollectorInfoService.GetInstance();
IList<Collector> collectorInfos = collectorInfoService.GetCollectorsByPage(hashpara);
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData data = new ExcelData();
IList<string> temp = new List<string>();
temp.Add("采集器编号");
temp.Add("密码");
temp.Add("分配电站");
temp.Add("电站时间");
temp.Add("导入时间");
temp.Add("是否启用");
temp.Add("工作状态");
data.Rows.Add(temp);
foreach (Collector collector in collectorInfos)
{
if (collector.plantID.Equals(0) == false)
collector.plant = plantService.GetPlantInfoById(collector.plantID);
else
collector.plant = null;
temp = new List<string>();
temp.Add(collector.code);
temp.Add(collector.password);
temp.Add(collector.plant == null ? "未分配电站" : collector.plant.name);
temp.Add(collector.plant == null ? "未分配电站" : DateTime.Now.AddHours(collector.plant.timezone).ToString("yy/MM/dd HH:mm"));
temp.Add(collector.importDate.ToString("yy/MM/dd HH:mm"));
temp.Add(collector.isUsed.Equals(true) ? "yes" : "no");
temp.Add(collector.plant == null ? "停止" : (collector.plant.isWork ? "工作" : "停止"));
data.Rows.Add(temp);
}
eDatas.Add(data);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("采集器列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Plants_Output(string id)
{
int no;
int.TryParse(id, out no);
Pager page = new Pager() { PageSize = ComConst.PageSize, PageIndex = no };
PlantService service = PlantService.GetInstance();
IList<Plant> plants = service.GetPlantByPage(page);
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData data = new ExcelData();
IList<string> temp = new List<string>();
temp.Add("名称");
temp.Add("国家");
temp.Add("城市");
temp.Add("电话");
temp.Add("制造商");
temp.Add("型号类型");
data.Rows.Add(temp);
foreach (Plant plant in plants)
{
temp = new List<string>();
temp.Add(plant.name);
temp.Add(plant.country);
temp.Add(plant.city);
temp.Add(plant.phone);
temp.Add(plant.manufacturer);
temp.Add(plant.module_type);
data.Rows.Add(temp);
}
eDatas.Add(data);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.Save("电站列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult AllPlants_Excel(string country, string city, string items, string design_power_start, string design_power_end, string area, string estartdate, string eenddate, string uname)
{
string uids = "0,";
if (string.IsNullOrEmpty(uname) == false)
{
IList<User> users = userService.GetUsersLikeuname(uname);
foreach (User user in users)
{
uids += string.Format("{0},", user.id);
}
}
uids += "0";
IList<string> rowData = new List<string>();
Hashtable table = new Hashtable();
Plant pnt = new Plant();
pnt.area = area;
pnt.country = country;
pnt.city = city;
Pager page = new Pager() { PageSize = ComConst.PageSize, PageIndex = 1 };
ViewData["page"] = page;
table.Add("plant", pnt);
table.Add("design_power_start", design_power_start);
table.Add("design_power_end", design_power_end);
table.Add("page", page);
table.Add("uids", uids);
IList<Plant> plants = plantService.QueryPagePlants(table);
ViewData["items"] = items;
ExcelData eData = BuildPlantDetails(plants, items, estartdate, eenddate);
IList<ExcelData> eDatas = new List<ExcelData>();
for (int x = 0; x < eData.Rows.Count - 1; x++)
eData.Rows[x].RemoveAt(eData.Rows[x].Count - 1);
for (int i = 0; i < eData.Rows[eData.Rows.Count - 1].Count; i++)
eData.Rows[eData.Rows.Count - 1][i] = eData.Rows[eData.Rows.Count - 1][i].Replace("<br />", string.Empty);
eDatas.Add(eData);
ExcelStreamWriter exlWriter = new ExcelStreamWriter(eDatas);
exlWriter.align = true;
exlWriter.Save("查询电站列表");
return File(exlWriter.FullName, "text/xlsx; charset=UTF-8", urlcode(exlWriter.FileName));
}
public ActionResult Down_Device_inv(string pname, string mcode, string showitems, string design_power_start, string design_power_end, string orderby, string estartdate, string eenddate)
{
double design_start = 0;
double design_end = 0;
double.TryParse(design_power_start, out design_start);
double.TryParse(design_power_end, out design_end);
IList<Device> devices = load_device(pname, mcode, "1", DeviceData.INVERTER_CODE, false, orderby);
if (!(design_end == 0 && design_start == 0))
devices = devices.Where(model => model.designPower >= design_start && model.designPower <= design_end).ToList<Device>();
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData eData = BuildInvDetails(devices, showitems, estartdate, eenddate);
for (int i = 0; i < eData.Rows[eData.Rows.Count - 1].Count; i++)
eData.Rows[eData.Rows.Count - 1][i] = eData.Rows[eData.Rows.Count - 1][i].Replace("<br />", string.Empty);
eDatas.Add(eData);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("逆变器列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Down_Device_HLX(string pname, string mname, string orderby)
{
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData eData = new ExcelData();
IList<string> rowData = null;
rowData = new List<string>();
rowData.Add("所属电站");
rowData.Add("名称");
rowData.Add("设备型号");
rowData.Add("国家");
rowData.Add("城市");
rowData.Add("接入传感器路数");
rowData.Add("直流母线电压 ");
rowData.Add("总电流");
eData.Rows.Add(rowData);
IList<Device> devices = load_device(pname, mname, string.Empty, DeviceData.HUILIUXIANG_CODE, false, orderby);
foreach (Device d in devices)
{
rowData = new List<string>();
rowData.Add(d.plant.name);
rowData.Add(string.IsNullOrEmpty(d.name) ? d.fullName : d.name);
rowData.Add(d.xinhaoName);
rowData.Add(d.plant.country);
rowData.Add(d.plant.city);
rowData.Add(d.getMonitor(MonitorType.MIC_BUSBAR_CGQLINENUM));
rowData.Add(d.getMonitor(MonitorType.MIC_BUSBAR_DCUXVOLT));
rowData.Add(d.getMonitor(MonitorType.MIC_BUSBAR_TOTALCURRENT));
eData.Rows.Add(rowData);
}
eDatas.Add(eData);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("汇流箱列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Down_Device_EM(string pname, string mname, string orderby)
{
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData eData = new ExcelData();
IList<string> rowData = null;
rowData = new List<string>();
rowData.Add("所属电站");
rowData.Add("名称");
rowData.Add("设备型号");
rowData.Add("国家");
rowData.Add("城市");
rowData.Add("当前日照");
rowData.Add("当前风速");
eData.Rows.Add(rowData);
IList<Device> devices = load_device(pname, mname, string.Empty, DeviceData.ENVRIOMENTMONITOR_CODE, false, orderby);
foreach (Device d in devices)
{
rowData = new List<string>();
rowData.Add(d.plant.name);
rowData.Add(string.IsNullOrEmpty(d.name) ? d.fullName : d.name);
rowData.Add(d.xinhaoName);
rowData.Add(d.plant.country);
rowData.Add(d.plant.city);
rowData.Add(d.getMonitor(MonitorType.MIC_DETECTOR_SUNLINGHT));
rowData.Add(d.getMonitor(MonitorType.MIC_DETECTOR_WINDSPEED));
eData.Rows.Add(rowData);
}
eDatas.Add(eData);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("环境检测仪列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Down_Device_Ammeter(string pname, string mname, string orderby)
{
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData eData = new ExcelData();
IList<string> rowData = null;
rowData = new List<string>();
rowData.Add("所属电站");
rowData.Add("名称");
rowData.Add("设备型号");
rowData.Add("国家");
rowData.Add("城市");
rowData.Add("正向有功电度");
eData.Rows.Add(rowData);
IList<Device> devices = load_device(pname, mname, string.Empty, DeviceData.AMMETER_CODE, false, orderby);
foreach (Device d in devices)
{
rowData = new List<string>();
rowData.Add(d.plant.name);
rowData.Add(string.IsNullOrEmpty(d.name) ? d.fullName : d.name);
rowData.Add(d.xinhaoName);
rowData.Add(d.plant.country);
rowData.Add(d.plant.city);
rowData.Add(d.getMonitor(MonitorType.MIC_AMMETER_POSITIVEACTIVEPOWERDEGREE));
eData.Rows.Add(rowData);
}
eDatas.Add(eData);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.align = true;
writer.Save("电表列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
public ActionResult Users_Output(string id)
{
int no = 0;
int.TryParse(id, out no);
Pager page = new Pager() { PageSize = ComConst.PageSize, PageIndex = no };
UserService userInfoService = UserService.GetInstance();
IList<User> users = userInfoService.GetUsersByPage(page);
IList<ExcelData> eDatas = new List<ExcelData>();
ExcelData data = new ExcelData();
IList<string> temp = new List<string>();
temp.Add("用户名");
temp.Add("国家");
temp.Add("性别");
data.Rows.Add(temp);
foreach (User user in users)
{
temp = new List<string>();
temp.Add(user.username);
temp.Add(user.country);
temp.Add(user.sex.Equals("0") ? "男" : "女");
data.Rows.Add(temp);
}
eDatas.Add(data);
ExcelStreamWriter writer = new ExcelStreamWriter(eDatas);
writer.Save("用户列表");
return File(writer.FullName, "text/xlsx; charset=UTF-8", urlcode(writer.FileName));
}
