private void btnExportDetail_Click(object sender, EventArgs e)
{
if (this.winGridView1.gridView1.RowCount == 0)
{
return;
}
#region 构造数据列表
//HandNo,ItemNo,ItemName,MapNo,Specification,Material,ItemBigType,ItemType,Unit,Price,Quantity,Amount,Source,StoragePos,UsagePos,d.WareHouse,d.Dept
string columns = "货单号,项目编号,项目名称,图号,规格型号,材质,备件属类,备件类别,单位,单价|decimal,数量|int,金额|decimal,来源,库位,使用位置,库房,部门";
DataTable dtDetail = DataTableHelper.CreateTable(columns);
for (int i = 0; i < winGridView1.gridView1.RowCount; i++)
{
string ID = winGridView1.gridView1.GetRowCellDisplayText(i, "ID");
if (!string.IsNullOrEmpty(ID))
{
DataTable dt = BLLFactory <PurchaseDetail> .Instance.GetPurchaseDetailReportByID(Convert.ToInt32(ID));
dt.Rows.Add(dt.NewRow());
//复制到中文列的表中
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow r = dtDetail.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
r[j] = dt.Rows[k][j];
}
dtDetail.Rows.Add(r);
}
}
}
#endregion
#region 导出数据操作
SpecialDirectories sp = new SpecialDirectories();
string fileToSave = FileDialogHelper.SaveExcel(string.Format("出库单明细({0})", DateTime.Now.ToString("yyyy-MM-dd")), sp.Desktop);
if (string.IsNullOrEmpty(fileToSave))
{
return;
}
try
{
string fileName = fileToSave;
string outError = "";
AsposeExcelTools.DataTableToExcel2(dtDetail, fileName, out outError);
if (!string.IsNullOrEmpty(outError))
{
MessageDxUtil.ShowError(outError);
LogTextHelper.Error(outError);
}
else
{
Process.Start(fileName);
}
}
catch (Exception ex)
{
MessageDxUtil.ShowError(ex.Message);
LogTextHelper.Error(ex);
}
#endregion
}
public void Export(HttpContext context)
{
string start_time = context.Request["start_time"];
string end_time = context.Request["end_time"];
int PageSize = Convert.ToInt32(context.Request["rows"]);
int PageIndex = Convert.ToInt32(context.Request["page"]);
StringBuilder commandText = new StringBuilder();
string where = "";
if (string.IsNullOrEmpty(start_time))
{
DateTime t = DateTime.Now.AddMonths(-1);
start_time = t.ToString("yyyy-MM-dd");
}
string StartTime = start_time.Substring(0, 10);
if (string.IsNullOrEmpty(end_time))
{
DateTime t = DateTime.Now;
end_time = t.ToString("yyyy-MM-dd");
}
string EndTime = end_time.Substring(0, 10);
string sidx = RequstString("sidx"); //排序名称
string sort = RequstString("sord"); //排序方式
if ("-1" == sort)
{
sort = "id";
}
if ("-1" == order)
{
order = "asc";
}
string err = "";
string json = "";
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/生产线报警趋势报表.xlsx");
try
{
int StartIndex = 1;
int EndIndex = -1;
int totalcount = 0;
DataTable resTable = Production_AlarmTrendReport_BLL.getTable(StartTime, EndTime, PageSize, StartIndex, EndIndex, sort, order, where, out totalcount);
// ExcelHelper.ExportDTtoExcel(resTable, "生产线报警趋势报表", fileName);
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
json = "{\"Result\":\"" + ss + "\"}";
}
catch (Exception e)
{
string ss1 = "false";
json = "{\"Result\":\"" + ss1 + "\"}";
}
context.Response.ContentType = "json";
context.Response.Write(json);
}
void Export() //点检查询结果导出
{
string json = "";
DataTable ResTable = new DataTable();
JavaScriptSerializer s = new JavaScriptSerializer();
HttpRequest request = HttpContext.Current.Request;
string AssemblyLine = request.Params["fl_name"];
string Station = request.Params["st_no"];
string StartTime = request.Params["StartTime"];
string EndTime = request.Params["EndTime"];
int PageIndex = Convert.ToInt32(request["page"]);
int PageSize = Convert.ToInt32(request["rows"]);
string where = "";
if (!string.IsNullOrEmpty(AssemblyLine))
{
where = " and a.StationNO LIKE '" + AssemblyLine + "%'";
}
if (!string.IsNullOrEmpty(Station))
{
where = " and a.StationNO = '" + Station + "'";
}
if (!string.IsNullOrEmpty(StartTime))
{
where += " and cast(a.CreateTime as datetime) > '" + StartTime + "'";
}
if (!string.IsNullOrEmpty(EndTime))
{
where += " and cast(a.CreateTime as datetime) < '" + EndTime + "'";
}
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
int totalcount;
ResTable = checkReport_BLL.getTableExcel(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
//}
try
{
#region 导出代码
//ExcelHelper.ExportDTtoExcel(ResTable, "", HttpContext.Current.Request.MapPath("~/App_Data/点检记录报表.xlsx"));
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/点检记录报表.xlsx");
string err = "";
AsposeExcelTools.DataTableToExcel2(ResTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
json = ss;
#endregion
}
catch
{
json = "false";
}
Response.Write(json);
}
public void ProcessRequest(HttpContext context)
{
JavaScriptSerializer s = new JavaScriptSerializer();
HttpRequest request = HttpContext.Current.Request;
string StartTime = request["StartTime"];
string EndTime = request["EndTime"];
string OrderCode = request["OrderCode"];
string StationNo = request["StationNo"];
string method = request["method"];
if (string.IsNullOrEmpty(StartTime))
{
DateTime t = DateTime.Now.AddMonths(-1);
StartTime = t.ToString("yyyy-MM-dd HH:mm:ss");
}
if (string.IsNullOrEmpty(EndTime))
{
DateTime t = DateTime.Now;
EndTime = t.ToString("yyyy-MM-dd HH:mm:ss");
}
if (OrderCode == "请选择")
{
OrderCode = "";
}
if (StationNo == "请选择")
{
StationNo = "FSA210";
}
int totalcount;
string JsonStr = "";
DateTime date = DateTime.Now;
string JsonStr3 = "";
string neirong = "";
string yee = "";
int PageIndex = Convert.ToInt32(request["page"]);
int PageSize = Convert.ToInt32(request["rows"]);
if (string.IsNullOrWhiteSpace(method))
{
JsonStr3 = checkRepair_BLL.getTableString(StartTime, EndTime, OrderCode, StationNo, PageIndex, out totalcount);
context.Response.ContentType = "json";
context.Response.Write(JsonStr3);
}
else if (method == "GetListNew")
{
JsonStr3 = checkRepair_BLL.GetListNew(StartTime, EndTime, OrderCode, StationNo, PageIndex, PageSize, out totalcount);
context.Response.ContentType = "json";
context.Response.Write(JsonStr3);
}
#region 导出代码
else if (method == "Export")
{
DataTable ResTable4 = checkRepair_BLL.getTableExcel(StartTime, EndTime, OrderCode, StationNo, PageIndex, out totalcount);
string str = "";
try
{
// ExcelHelper.ExportDTtoExcel(ResTable4, "", HttpContext.Current.Request.MapPath("~/App_Data/检测返修报表.xlsx"));
AsposeExcelTools.DataTableToExcel2(ResTable4, HttpContext.Current.Request.MapPath("~/App_Data/检测返修报表.xlsx"), out str);
if (str.Length < 1)
{
JsonStr3 = "true";
}
else
{
JsonStr3 = "false";
}
}
catch
{
JsonStr3 = "false";
}
context.Response.ContentType = "json";
context.Response.Write(JsonStr3);
}
#endregion
}
public void Export(HttpContext context)
{
string start_time = context.Request["start_time"];
string end_time = context.Request["end_time"];
string fl_id = context.Request["fl_id"];
int PageSize = Convert.ToInt32(context.Request["rows"]);
int PageIndex = Convert.ToInt32(context.Request["page"]);
StringBuilder commandText = new StringBuilder();
string where = "";
if (string.IsNullOrEmpty(start_time))
{
DateTime t = DateTime.Now;
start_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string StartTime = start_time;
if (string.IsNullOrEmpty(end_time))
{
DateTime t = DateTime.Now;
end_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string EndTime = end_time;
// DataListModel<Production_AlarmModel> userList = Production_AlarmTrendReport_BLL.GetWaringListNew(fl_id, StartTime, EndTime, StartIndex, EndIndex);
// string json = JSONTools.ScriptSerialize<DataListModel<Production_AlarmModel>>(userList);
string json = "";
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/报警信息报表.xlsx");
try
{
int StartIndex = 1;
int EndIndex = -1;
int totalcount = 0;
DataTable resTable = Production_AlarmTrendReport_BLL.GetWaringDataTable(fl_id, StartTime, EndTime, StartIndex, EndIndex);
//ExcelHelper.ExportDTtoExcel(resTable, "报警信息报表", fileName);
string err = "";
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
///
//////
json = "{\"Result\":\"" + ss + "\"}";
}
catch (Exception e)
{
string ss1 = "false";
json = "{\"Result\":\"" + ss1 + "\"}";
}
context.Response.ContentType = "json";
context.Response.Write(json);
}
/// <summary>
/// 使用背景线程导出Excel文档
/// </summary>
private void bg_DoWork(object sender, DoWorkEventArgs e)
{
DataTable table = new DataTable();
if (AllToExport != null && isExportAllPage)
{
if (AllToExport is DataView)
{
DataView dv = (DataView)AllToExport;//默认导出显示内容
table = dv.ToTable();
}
else if (AllToExport is DataTable)
{
table = AllToExport as DataTable;
}
else
{
table = ReflectionUtil.CreateTable(AllToExport);
}
//解析标题
string originalName = string.Empty;
foreach (DataColumn column in table.Columns)
{
originalName = column.Caption;
if (columnNameAlias.ContainsKey(originalName.ToUpper()))
{
column.Caption = columnNameAlias[originalName.ToUpper()];
column.ColumnName = columnNameAlias[originalName.ToUpper()];
}
}
//for (int i = 0; i < this.gridView1.Columns.Count; i++)
//{
// if (!this.gridView1.Columns[i].Visible)
// {
// table.Columns.Remove(this.gridView1.Columns[i].FieldName);
// }
//}
}
else
{
DataColumn column;
DataRow row;
for (int i = 0; i < this.gridView1.Columns.Count; i++)
{
if (this.gridView1.Columns[i].Visible)
{
column = new DataColumn(this.gridView1.Columns[i].FieldName, typeof(string));
column.Caption = this.gridView1.Columns[i].Caption;
table.Columns.Add(column);
}
}
object cellValue = "";
string fieldName = "";
for (int i = 0; i < gridView1.RowCount; i++)
{
row = table.NewRow();
for (int j = 0; j < gridView1.Columns.Count; j++)
{
if (this.gridView1.Columns[j].Visible)
{
fieldName = gridView1.Columns[j].FieldName;
cellValue = gridView1.GetRowCellValue(i, fieldName);
row[fieldName] = cellValue ?? "";
}
}
table.Rows.Add(row);
}
}
string outError = "";
AsposeExcelTools.DataTableToExcel2(table, (String)e.Argument, out outError);
}
public void ProcessRequest(HttpContext context)
{
HttpRequest request = System.Web.HttpContext.Current.Request;
string method = request.Params["method"];
string AssemblyLine = request.Params["fl_name"]; //流水线
string Station = request.Params["st_no"]; //工位
string StartTime = request["StartTime"];
string EndTime = request["EndTime"];
string OrderId = request["OrderId"];
string scancode = request["scancodenum"];
string SortFlag = request["sort"];
string sortOrder = request["order"];
int PageSize = Convert.ToInt32(request["rows"]);
int PageIndex = Convert.ToInt32(request["page"]);
//测试数据
//StartTime = "2017-08-01 16:51";
//EndTime = "2017-08-03 16:51";
int totalcount;
DataTable resTable = new DataTable();
//ygy
string wherestr = " ";
string JsonStr = "";
if (!string.IsNullOrWhiteSpace(AssemblyLine))
{
wherestr += " and fl_name ='" + AssemblyLine + "'";
}
if (!string.IsNullOrWhiteSpace(Station))
{
wherestr += " and st_no='" + Station + "'";
}
if (!string.IsNullOrWhiteSpace(OrderId))
{
wherestr += " and or_no like '%" + OrderId + @"%'";
}
if (!string.IsNullOrWhiteSpace(scancode))
{
wherestr += " and scanCode like '%" + scancode + @"%'";
}
if (!string.IsNullOrWhiteSpace(StartTime))
{
wherestr += " and cast(step_startTime as datetime) >= '" + StartTime + "' ";
}
if (!string.IsNullOrWhiteSpace(EndTime))
{
wherestr += " and cast(step_endTime as datetime) <= '" + EndTime + @"'";
}
//查询
#region
if (string.IsNullOrWhiteSpace(method))
{
sort = SortFlag;
order = sortOrder;
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
//string
JsonStr = mg_sys_logBll.getList(PageSize, StartIndex, EndIndex, sort, order, wherestr);
context.Response.ContentType = "text/plain";
context.Response.Write(JsonStr);
}
#endregion
//导出
#region
if (method == "Export")
{
if ("-1" == sort)
{
sort = " st_no,step_startTime";
}
if ("-1" == order)
{
order = "asc";
}
string json = "";
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/步骤日志报表.xlsx");
try
{
int StartIndex = 1;
int EndIndex = -1;
resTable = mg_sys_logBll.getList(PageSize, StartIndex, EndIndex, sort, order, wherestr, out totalcount);
//ExcelHelper.ExportDTtoExcel(resTable, "步骤日志报表", fileName);
string err = "";
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
json = "{\"Result\":\"" + ss + "\"}";
}
catch (Exception e)
{
string ss1 = "false";
json = "{\"Result\":\"" + ss1 + "\"}";
}
context.Response.ContentType = "json";
context.Response.Write(json);
}
#endregion
}
/// <summary>
/// 导出Excel的操作
/// </summary>
private void btnExport_Click(object sender, EventArgs e)
{
string file = FileDialogHelper.SaveExcel(string.Format("{0}.xls", moduleName));
if (!string.IsNullOrEmpty(file))
{
string where = GetConditionSql();
List <StaffInfo> list = CallerFactory <IStaffService> .Instance.Find(where);
DataTable dtNew = DataTableHelper.CreateTable("序号|int,Id,Number,Name,Gender,Birthday,NativePlace,Nationality,IdentityCard,Phone,OfficePhone,Email,HomeAddress,Political,PartyDate,Education,Degree,WorkingDate,Marriage,ChildStatus,Titles,Duty,JobType,Introduce,Remark,AttachId,CompanyId,DepartmentId,PositionId,Creator,CreatorId,CreateTime,EditorId,Deleted,Enabled");
DataRow dr;
int j = 1;
for (int i = 0; i < list.Count; i++)
{
dr = dtNew.NewRow();
dr["序号"] = j++;
dr["Id"] = list[i].Id;
dr["Number"] = list[i].Number;
dr["Name"] = list[i].Name;
dr["Gender"] = list[i].Gender;
dr["Birthday"] = list[i].Birthday;
dr["NativePlace"] = list[i].NativePlace;
dr["Nationality"] = list[i].Nationality;
dr["IdentityCard"] = list[i].IdentityCard;
dr["Phone"] = list[i].Phone;
dr["OfficePhone"] = list[i].OfficePhone;
dr["Email"] = list[i].Email;
dr["HomeAddress"] = list[i].HomeAddress;
dr["Political"] = list[i].Political;
dr["PartyDate"] = list[i].PartyDate;
dr["Education"] = list[i].Education;
dr["Degree"] = list[i].Degree;
dr["WorkingDate"] = list[i].WorkingDate;
dr["Marriage"] = list[i].Marriage;
dr["ChildStatus"] = list[i].ChildStatus;
dr["Titles"] = list[i].Titles;
dr["Duty"] = list[i].Duty;
dr["JobType"] = list[i].JobType;
dr["Introduce"] = list[i].Introduce;
dr["Remark"] = list[i].Remark;
dr["AttachId"] = list[i].AttachId;
dr["CompanyId"] = list[i].CompanyId;
dr["DepartmentId"] = list[i].DepartmentId;
dr["PositionId"] = list[i].PositionId;
dr["Creator"] = list[i].Creator;
dr["CreatorId"] = list[i].CreatorId;
dr["CreateTime"] = list[i].CreateTime;
dr["EditorId"] = list[i].EditorId;
dr["Deleted"] = list[i].Deleted;
dr["Enabled"] = list[i].Enabled;
dtNew.Rows.Add(dr);
}
try
{
string error = "";
AsposeExcelTools.DataTableToExcel2(dtNew, file, out error);
if (!string.IsNullOrEmpty(error))
{
MessageDxUtil.ShowError(string.Format("导出Excel出现错误：{0}", error));
}
else
{
if (MessageDxUtil.ShowYesNoAndTips("导出成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(file);
}
}
}
catch (Exception ex)
{
LogTextHelper.Error(ex);
MessageDxUtil.ShowError(ex.Message);
}
}
}
/// <summary>
/// 导出Excel的操作
/// </summary>
private void btnExport_Click(object sender, EventArgs e)
{
string file = FileDialogHelper.SaveExcel(string.Format("{0}.xls", moduleName));
if (!string.IsNullOrEmpty(file))
{
string where = GetConditionSql();
List <AddressInfo> list = new List <AddressInfo>();
TreeNode selectedNode = this.treeView1.SelectedNode;
if (isUserGroupName && selectedNode != null)
{
string groupName = "";
//只有是数据库的分组才使用条件
if (selectedNode.Text != "所有联系人" && selectedNode.Text != "未分组联系人")
{
groupName = this.treeView1.SelectedNode.Text;
}
list = BLLFactory <Address> .Instance.FindByGroupName(LoginUserInfo.ID.ToString(), groupName);
}
else
{
list = BLLFactory <Address> .Instance.Find(where);
}
DataTable dtNew = DataTableHelper.CreateTable("序号|int,姓名,性别,出生日期,手机,电子邮箱,QQ,家庭电话,办公电话,家庭住址,办公地址,传真号码,公司单位,部门,职位,其他,备注,创建人,创建时间");
DataRow dr;
int j = 1;
for (int i = 0; i < list.Count; i++)
{
dr = dtNew.NewRow();
dr["序号"] = j++;
dr["姓名"] = list[i].Name;
dr["性别"] = list[i].Sex;
dr["出生日期"] = list[i].Birthdate;
dr["手机"] = list[i].Mobile;
dr["电子邮箱"] = list[i].Email;
dr["QQ"] = list[i].QQ;
dr["家庭电话"] = list[i].HomeTelephone;
dr["办公电话"] = list[i].OfficeTelephone;
dr["家庭住址"] = list[i].HomeAddress;
dr["办公地址"] = list[i].OfficeAddress;
dr["传真号码"] = list[i].Fax;
dr["公司单位"] = list[i].Company;
dr["部门"] = list[i].Dept;
dr["职位"] = list[i].Position;
dr["其他"] = list[i].Other;
dr["备注"] = list[i].Note;
dr["创建人"] = list[i].Creator;
dr["创建时间"] = list[i].CreateTime;
dtNew.Rows.Add(dr);
}
try
{
string error = "";
AsposeExcelTools.DataTableToExcel2(dtNew, file, out error);
if (!string.IsNullOrEmpty(error))
{
MessageDxUtil.ShowError(string.Format("导出Excel出现错误：{0}", error));
}
else
{
if (MessageDxUtil.ShowYesNoAndTips("导出成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(file);
}
}
}
catch (Exception ex)
{
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(FrmAddress));
MessageDxUtil.ShowError(ex.Message);
}
}
}
/// <summary>
/// 从Excel文件导入(角色)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private bool DoImport(string filePath)
{
bool returnValue = false;
// 鼠标忙碌状态
this.Cursor = Cursors.WaitCursor;
try
{
//将Excel表转换为DataTable
string error = "";
DataTable dataTable = new DataTable();
AsposeExcelTools.ExcelFileToDataTable(filePath, out dataTable, out error);
//检查Excell转为DataTable是否成功
if (error != "")
{
MessageBox.Show(error, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
//是否有记录
if (dataTable.Rows.Count > 0)
{
//将第一行数据给数据列置名称，以便于识别和调用数据。
int columnsCount = 0;
for (columnsCount = 0; columnsCount < dataTable.Columns.Count; columnsCount++)
{
dataTable.Columns[columnsCount].ColumnName = dataTable.Rows[0][columnsCount].ToString().Trim();
}
//循环处理每行数据
int rowsCount = 0;
int errorCount = 0;
BaseRoleEntity tempentity;
string statusCode = string.Empty;
string statusMessage = string.Empty;
DataTable dt = DotNetService.Instance.RoleService.GetDataTable(UserInfo);
for (rowsCount = 1; rowsCount < dataTable.Rows.Count; rowsCount++)
{
//角色名称不允许导入重复项
bool exists = BaseBusinessLogic.Exists(dt, BaseRoleEntity.FieldRealName, dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString());
if (!exists)
{
//清空实体
tempentity = new BaseRoleEntity();
//给实体赋值
tempentity.RealName = dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString();
tempentity.Code = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCode].ToString();
tempentity.Description = dataTable.Rows[rowsCount][BaseRoleEntity.FieldDescription].ToString();
tempentity.Enabled = int.Parse(dataTable.Rows[rowsCount][BaseRoleEntity.FieldEnabled].ToString());
tempentity.CategoryCode = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCategoryCode].ToString();
tempentity.AllowDelete = 1;
tempentity.AllowEdit = 1;
tempentity.DeletionStateCode = 0;
tempentity.IsVisible = 1;
DotNetService.Instance.RoleService.Add(UserInfo, tempentity, out statusCode, out statusMessage);
}
else
{
errorCount++;
}
}
this.Changed = true;
returnValue = true;
MessageBox.Show("共有" + (dataTable.Rows.Count - 1) + "条记录，"
+ (dataTable.Rows.Count - 1 - errorCount).ToString()
+ "条记录被成功导入！", "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
this.ProcessException(ex);
}
finally
{
// 设置鼠标默认状态
this.Cursor = Cursors.Default;
}
return(returnValue);
}
public ActionResult Import(string guid)
{
CommonResult result = new CommonResult();
result.Success = false;
try
{
var attach = BLLFactory <FileUpload> .Instance.GetByAttachGUID(guid).FirstOrDefault();
if (attach != null)
{
var filename = Server.MapPath("~\\" + attach.BasePath + "\\" + attach.SavePath);
var dt = new System.Data.DataTable();
string err = null;
if (AsposeExcelTools.ExcelFileToDataTable(filename, out dt, out err))
{
//清空中间表
BLLFactory <Core.BLL.MidConcentrator> .Instance.DeleteByCondition("1=1");
BLLFactory <Core.BLL.MidCustomerMeter> .Instance.DeleteByCondition("1=1");
//采集器信息
var entity = new Core.Entity.MidConcentrator();
var row = dt.Rows[2];
var nvcName = row[0].ToString();
var nvcAddr = row[1].ToString();
var vcAddr = row[2].ToString();
var intProtocol = row[3].ToString().ToInt();
var intCount = row[4].ToString().ToInt();
var intCommMode = row[5].ToString().ToInt();
var intCOM = row[6].ToString().ToInt();
var vcParam = row[7].ToString();
var vcSimNo = row[8].ToString();
var intStatus = row[9].ToString().ToInt();
var clientIP = "127.0.0.1";
entity.VcClientIP = clientIP;
entity.IntCOM = intCOM;
entity.IntCommMode = intCommMode;
entity.IntCount = intCount;
entity.IntProtocol = intProtocol;
entity.IntStatus = intStatus;
entity.NvcAddr = nvcAddr;
entity.NvcName = nvcName;
entity.VcAddr = vcAddr;
entity.VcParam = vcParam;
entity.VcSimNo = vcSimNo;
//DbTransaction dbTransaction = BLLFactory<Core.BLL.MidConcentrator>.Instance.CreateTransaction();
BLLFactory <Core.BLL.MidConcentrator> .Instance.Insert(entity);
//dbTransaction.Commit();
//客户及表信息
for (int i = 5; i < dt.Rows.Count; i++)
{
var item = dt.Rows[i];
var IntCustCode = item[0].ToString().ToInt();
var NvcName = item[1].ToString();
var NvcAddr = item[2].ToString();
var VcTelNo = item[3].ToString();
var VcMobile = item[4].ToString();
var VcIDNo = item[5].ToString();
var IntPriceNo = item[6].ToString().ToInt();
var VcCustType = item[7].ToString();
var IntStatusUser = item[8].ToString().ToInt();
var DteOpen = Convert.ToDateTime(item[9].ToString());
var NvcInvName = item[10].ToString();
var NvcInvAddr = item[11].ToString();
var NvcVillage = item[12].ToString();
var VcBuilding = item[13].ToString();
var IntUnitNum = item[14].ToString().ToInt();
var IntRoomNum = item[15].ToString().ToInt();
var IntNumber = item[16].ToString().ToInt();
var VcAddr = item[17].ToString();
var NvcAddrIns = item[18].ToString();
var VcAssetNo = item[19].ToString();
var VcBarCode = item[20].ToString();
var IntOrig = item[21].ToString().ToInt();
var IntChannal = item[22].ToString().ToInt();
var IntProtocol = item[23].ToString().ToInt();
var DtCreate = Convert.ToDateTime(item[24].ToString());
var IntCycle = item[25].ToString().ToInt();
var IntState = item[26].ToString().ToInt();
var mMeter = new Core.Entity.MidCustomerMeter();
mMeter.DtCreate = DateTime.Now;
mMeter.DteOpen = DateTime.Now;
mMeter.IntChannal = IntChannal;
mMeter.IntCustCode = IntCustCode;
mMeter.IntCycle = IntCycle;
mMeter.IntNumber = IntNumber;
mMeter.IntOrig = IntOrig;
mMeter.IntPriceNo = IntPriceNo;
mMeter.IntProtocol = IntProtocol;
mMeter.IntRoomNum = IntRoomNum;
mMeter.IntState = IntState;
mMeter.IntStatus = IntStatusUser;
mMeter.IntUnitNum = IntUnitNum;
//mMeter.IntUserID=
mMeter.NvcAddr = NvcAddr;
mMeter.NvcAddrIns = NvcAddrIns;
mMeter.NvcCustType = VcCustType;
mMeter.NvcInvAddr = NvcInvAddr;
mMeter.NvcInvName = NvcInvName;
mMeter.NvcName = NvcName;
mMeter.NvcVillage = NvcVillage;
mMeter.VcAddr = VcAddr;
mMeter.VcAssetNo = VcAssetNo;
mMeter.VcBarCode = VcBarCode;
mMeter.VcBuilding = VcBuilding;
mMeter.VcClientIP = clientIP;
//mMeter.VcContractNo=
mMeter.VcIDNo = VcIDNo;
mMeter.VcMobile = VcMobile;
mMeter.VcTelNo = VcTelNo;
BLLFactory <Core.BLL.MidCustomerMeter> .Instance.Insert(mMeter);
//BLLFactory<Core.BLL.MidCustomerMeter>.Instance.Insert(mMeter, dbTransaction);
}
//dbTransaction.Commit();
//@sClientIP VARCHAR(64), --< !--客户端IP-- >
//@iConcFlag INTEGER, --< !--采集器标志0:如果已存在相同采集器，则提示错误，终止导入档案信息 1:如果已存在同地址采集器，将客户与表信息导入，表挂接在同地址采集器下-- >
//@iMeterFlag INTEGER, --< !--一户多表标志0:一户一表，MidCustomerMeter.IntCustCode唯一 1:一户多表，MidCustomerMeter.IntCustCode相同的记录认为是同一客户-- >
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@sClientIP", SqlDbType.VarChar, 64)
{
Value = clientIP
});
param.Add(new SqlParameter("@iConcFlag", SqlDbType.VarChar, 64)
{
Value = 1
});
param.Add(new SqlParameter("@iMeterFlag", SqlDbType.VarChar, 64)
{
Value = 1
});
param.Add(new SqlParameter("@sReturn", SqlDbType.VarChar, 256)
{
Direction = ParameterDirection.Output
});
//param[0].Value = clientIP;
//param[1].Value = 1;
//param[2].Value = 1;
//param[3].Direction = ParameterDirection.Output;
BLLFactory <Core.BLL.ArcConcentratorInfo> .Instance.ExecStoreProc("up_ImportArchive", param);
if (param[3].Value.ToString() == "0")
{
result.Success = true;
}
else
{
result.ErrorMessage = "执行up_ImportArchive存储过程出错!错误如下:" + param[3].Value.ToString();
}
}
else
{
result.ErrorMessage = err;
}
}
}
catch (Exception ex)
{
result.ErrorMessage = ex.Message;
}
return(ToJsonContent(result));
}
public void Export(HttpContext context)
{
string StartTime = context.Request["start_time"];
string EndTime = context.Request["end_time"];
if (string.IsNullOrEmpty(StartTime))
{
DateTime t = DateTime.Now;
StartTime = t.AddDays(-1).ToString("yyyy-MM-dd hh:mm:ss");
}
if (string.IsNullOrEmpty(EndTime))
{
DateTime t = DateTime.Now;
EndTime = t.ToString("yyyy-MM-dd hh:mm:ss");
}
string clid = context.Request["clnameid"];
int clnameid = 0;
if (string.IsNullOrEmpty(clid))
{
clnameid = 0;
}
else
{
clnameid = Convert.ToInt32(clid);
}
string clname = context.Request["clname"];
int PageSize = Convert.ToInt32(context.Request["rows"]);
int PageIndex = Convert.ToInt32(context.Request["page"]);
string sidx = RequstString("sidx"); //排序名称
string sort = RequstString("sord"); //排序方式
if ("-1" == sort)
{
sort = "id";
}
if ("-1" == order)
{
order = "asc";
}
string json = "";
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/生产报表.xlsx");
try
{
int StartIndex = 1;
int EndIndex = -1;
int totalcount = 0;
DataTable resTable = Production_Report_BLL.getTable(StartTime, EndTime, clnameid, clname, StartIndex, EndIndex, out totalcount);
//ExcelHelper.ExportDTtoExcel(resTable, "生产报表", fileName);
string err = "";
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
json = "{\"Result\":\"" + ss + "\"}";
}
catch (Exception e)
{
string ss1 = "false";
json = "{\"Result\":\"" + ss1 + "\"}";
}
context.Response.ContentType = "json";
context.Response.Write(json);
}
private void btnExportBill_Click(object sender, EventArgs e)
{
#region 准备数据
string columns = @"流水号,备注,供货商,操作员,库房名称,备件编号（pm码）,备件名称,图号,规格型号,材质,备件属类,备件类别,单位,最新单价（元）,出库数量,总价,出库日期,来源,库位,部门,使用位置";
DataTable dt = DataTableHelper.CreateTable(columns);
DataRow row = null;
for (int i = 0; i < this.lvwDetail.gridView1.RowCount; i++)
{
PurchaseDetailInfo info = lvwDetail.gridView1.GetRow(i) as PurchaseDetailInfo;
if (info != null)
{
row = dt.NewRow();
/*row["流水号"] = this.txtHandNo.Text;
* row["备注"] = this.txtNote.Text;
* row["供货商"] = this.txtPickingPeople.Text;
* row["操作员"] = this.txtCreator.Text;
* row["出库日期"] = this.txtCreateDate.DateTime.ToString();
* row["库房名称"] = info.WareHouse;
* row["备件编号（pm码）"] = info.ItemNo;
* row["备件名称"] = info.ItemName;
* row["图号"] = info.MapNo;
* row["规格型号"] = info.Specification;
* row["材质"] = info.Material;
* row["备件属类"] = info.ItemBigType;
* row["备件类别"] = info.ItemType;
* row["单位"] = info.Unit;
* row["最新单价（元）"] = info.Price.ToString("C2");
* row["出库数量"] = info.Quantity.ToString();
* row["总价"] = info.Amount.ToString("C2");
* row["来源"] = info.Source;
* row["库位"] = info.StoragePos;
* row["部门"] = info.Dept;
* row["使用位置"] = info.UsagePos;*/
dt.Rows.Add(row);
}
}
#endregion
#region 导出数据
try
{
SpecialDirectories sp = new SpecialDirectories();
string fileName = FileDialogHelper.SaveExcel(string.Format("出库单({0})", DateTimeHelper.GetServerDate()), sp.Desktop);
if (string.IsNullOrEmpty(fileName))
{
return;
}
string outError = "";
AsposeExcelTools.DataTableToExcel2(dt, fileName, out outError);
if (!string.IsNullOrEmpty(outError))
{
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, outError, typeof(FrmPurchase));
MessageDxUtil.ShowError(outError);
}
else
{
Process.Start(fileName);
}
}
catch (Exception ex)
{
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(FrmPurchase));
MessageDxUtil.ShowError(ex.Message);
}
#endregion
}
public void ProcessRequest(HttpContext context)
{
HttpRequest request = System.Web.HttpContext.Current.Request;
string OrderType = request["OrderType"];
string SerialNumber = request["SerialNumber"];
string start_time = context.Request["StartTime"];
string end_time = context.Request["EndTime"];
int SortFlag = Convert.ToInt32(request["SortFlag"]);
int PageSize = Convert.ToInt32(request["rows"]);
int PageIndex = Convert.ToInt32(request["page"]);
string method = request["method"];
sort = request["sort"];
order = request["order"];
string where = "";
if (string.IsNullOrEmpty(start_time))
{
DateTime t = DateTime.Now;
start_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string StartTime = start_time.Substring(0, 17) + "00";
if (string.IsNullOrEmpty(end_time))
{
DateTime t = DateTime.Now;
end_time = t.ToString("yyyy-MM-dd HH:mm:ss");
}
string EndTime = end_time.Substring(0, 17) + "59";
where += " and a.CreateTime >='" + StartTime + "'";
where += " and a.CreateTime <='" + EndTime + "'";
if (!string.IsNullOrEmpty(OrderType))
{
where += " and a.OrderType = " + OrderType + " ";
}
if (!string.IsNullOrEmpty(SerialNumber))
{
where += " and a.SerialNumber like '%" + SerialNumber + "%' ";
}
string JsonStr = "";
if (string.IsNullOrWhiteSpace(method))
{
int StartIndex = PageSize * (PageIndex - 1) + 1;
int EndIndex = StartIndex + PageSize - 1;
int totalcount;
DataTable resTable = mg_CustomerOrderBLL.getTable(PageSize, PageIndex, StartIndex, EndIndex, sort, order, where, out totalcount);
// DataTable resTable1 = GetPagedTable(resTable, PageIndex, PageSize);
JsonStr = FunCommon.DataTableToJson2(totalcount, resTable);
context.Response.ContentType = "text/plain";
context.Response.Write(JsonStr);
context.Response.End();
}
if ("Export" == method)
{
try
{
int totalcount;
DataTable resTable = mg_CustomerOrderBLL.getTableExcel(sort, order, where, out totalcount);
//ExcelHelper.ExportDTtoExcel(resTable, "", HttpContext.Current.Request.MapPath("~/App_Data/客户订单报表.xlsx"));
string fileName = HttpContext.Current.Request.MapPath("~/App_Data/客户订单报表.xlsx");
string err = "";
AsposeExcelTools.DataTableToExcel2(resTable, fileName, out err);
string ss = "true";
if (err.Length < 1)
{
ss = "true";
}
else
{
ss = "false";
}
JsonStr = ss;
}
catch
{
JsonStr = "false";
}
context.Response.ContentType = "json";
context.Response.Write(JsonStr);
context.Response.End();
}
}
/// <summary>
/// 导出Excel的操作
/// </summary>
private void btnExport_Click(object sender, EventArgs e)
{
if (!HasFunction("User/export"))
{
MessageDxUtil.ShowError(Const.NoAuthMsg);
return;
}
string file = FileDialogHelper.SaveExcel(string.Format("{0}.xls", moduleName));
if (!string.IsNullOrEmpty(file))
{
List <UserInfo> list = new List <UserInfo>();
TreeNode selectedNode = this.treeRole.SelectedNode;
if (isUseRoleSearch && selectedNode != null && selectedNode.Tag != null)
{
string roleId = selectedNode.Tag.ToString();
if (!string.IsNullOrEmpty(roleId))
{
list = BLLFactory <User> .Instance.GetUsersByRole(roleId.ToInt32());
}
}
else
{
string where = GetConditionSql();
list = BLLFactory <User> .Instance.Find(where);
}
DataTable dtNew = DataTableHelper.CreateTable("序号|int,用户编码,用户名/登录名,用户全名,用户呢称,是否过期,职务头衔,身份证号码,移动电话,办公电话,家庭电话,邮件地址,住址,办公地址,性别,出生日期,QQ号码,个性签名,审核状态,备注,自定义字段,默认部门名称,所属公司名称,排序码");
DataRow dr;
int j = 1;
DateTime dtDefault = Convert.ToDateTime("1900-01-01");
for (int i = 0; i < list.Count; i++)
{
dr = dtNew.NewRow();
dr["序号"] = j++;
dr["用户编码"] = list[i].HandNo;
dr["用户名/登录名"] = list[i].Name;
dr["用户全名"] = list[i].FullName;
dr["用户呢称"] = list[i].Nickname;
dr["是否过期"] = list[i].IsExpire ? "1" : "0";
dr["职务头衔"] = list[i].Title;
dr["身份证号码"] = list[i].IdentityCard;
dr["移动电话"] = list[i].MobilePhone;
dr["办公电话"] = list[i].OfficePhone;
dr["家庭电话"] = list[i].HomePhone;
dr["邮件地址"] = list[i].Email;
dr["住址"] = list[i].Address;
dr["办公地址"] = list[i].WorkAddr;
dr["性别"] = list[i].Gender;
if (list[i].Birthday > dtDefault)
{
dr["出生日期"] = list[i].Birthday;
}
dr["QQ号码"] = list[i].QQ;
dr["个性签名"] = list[i].Signature;
dr["审核状态"] = list[i].AuditStatus;
dr["备注"] = list[i].Note;
dr["自定义字段"] = list[i].CustomField;
dr["默认部门名称"] = list[i].DeptName;
dr["所属公司名称"] = list[i].CompanyName;
dr["排序码"] = list[i].Seq;
dtNew.Rows.Add(dr);
}
try
{
string error = "";
AsposeExcelTools.DataTableToExcel2(dtNew, file, out error);
if (!string.IsNullOrEmpty(error))
{
MessageDxUtil.ShowError(string.Format("导出Excel出现错误：{0}", error));
}
else
{
if (MessageDxUtil.ShowYesNoAndTips("导出成功，是否打开文件？") == System.Windows.Forms.DialogResult.Yes)
{
System.Diagnostics.Process.Start(file);
}
}
}
catch (Exception ex)
{
LogHelper.WriteLog(LogLevel.LOG_LEVEL_CRIT, ex, typeof(FrmUser));
MessageDxUtil.ShowError(ex.Message);
}
}
}
