private void openExcel_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
ofd.ValidateNames = true;
ofd.CheckPathExists = true;
ofd.CheckFileExists = true;
if (ofd.ShowDialog() == DialogResult.OK)
{
string strFileName = ofd.FileName;
try
{
excelDt = NPOIExcel.readExcel(strFileName, true);
if (excelDt != null && excelDt.Rows.Count > 0)
{
LogRichTextBox.logMesg("文件『" + strFileName + "』读取成功！共读取【" + excelDt.Rows.Count.ToString() + "】条记录。");
}
else
{
LogRichTextBox.logMesg("文件『" + strFileName + "』读取失败！请检查EXCEL文件是否有数据。", 2);
}
}
catch (Exception ex)
{
LogRichTextBox.logMesg("文件『" + strFileName + "』读取失败！失败原因为：" + ex.ToString(), 2);
return;
}
}
}
public string getExcelData(string path)
{
string ErrMsg = "";
try
{
if (FileHelper.GetFileName(path).Split('.')[1] != "xls")
{
return("");
}
if (FileHelper.IsExistFile(path))
{
NPOIExcel npio = new NPOIExcel();
DataTable dt = npio.ExcelToTableToSpe(path, 8);
DataHandler(dt, path, out ErrMsg);
}
else
{
ErrMsg = "文件不存在";
}
}
catch (Exception ex)
{
ErrMsg = path + ":" + ex.Message;
}
return(ErrMsg);
}
public void ToDataTableTest()
{
string _path = @"D:\Employees_20161011165659.xls";
DataTable _table = NPOIExcel.ToDataTable(_path, 0, 1, 2);
Assert.IsTrue(_table.Rows.Count > 0);
}
public Stream GenerateDataTable(IEnumerable <客戶聯絡人> data)
{
// var data = RepositoryHelper.GetCustsExcelViewRepository();
var query = data.Select(p => new { p.Id, p.姓名, p.手機, p.職稱, p.Email, p.電話, p.客戶資料.客戶名稱 });
return(NPOIExcel.RenderListToExcel(query.ToList()));
}
public ActionResult Import(string id)
{
try
{
HttpFileCollectionBase files = Request.Files;
DataTable dt = CommClass.CreateTB("UserName:System.String,UserID:System.String,UserPwd:System.String,UserPermission:System.String,UserState:System.Int32,Remark:System.String");
string str = NPOIExcel.ImportExcelFile(files, dt, 1, 0);
List <M_User> lstUser = new JavaScriptSerializer().Deserialize <List <M_User> >(str);
bool iSucc = SQLDBHelperClient.CreateUser().Add(lstUser);
if (iSucc)
{
ViewBag.Succ = "1";
ViewBag.Msg = "数据添加成功!";
return(View());
}
else
{
ViewBag.Succ = "0";
ViewBag.Msg = "数据添加失败!";
return(View());
}
}
catch (Exception ex)
{
ViewBag.Succ = "0";
ViewBag.Msg = "数据添加失败!";
return(View());
}
}
private void openExcel_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
ofd.ValidateNames = true;
ofd.CheckPathExists = true;
ofd.CheckFileExists = true;
if (ofd.ShowDialog() == DialogResult.OK)
{
string strFileName = ofd.FileName;
try
{
excelDt = NPOIExcel.readExcel(strFileName, true);
int index = excelDt.Rows.Count - 1;
string heji = excelDt.Rows[index]["单据日期"].ToString();
if (heji.Equals("合计"))
{
excelDt.Rows.RemoveAt(index);
}
LogRichTextBox.logMesg("文件『" + strFileName + "』读取成功！共读取【" + excelDt.Rows.Count.ToString() + "】条记录。");
}
catch (Exception ex)
{
LogRichTextBox.logMesg("文件『" + strFileName + "』读取失败！失败原因为：" + ex.ToString(), 2);
return;
}
impU8.Enabled = checkData();
}
}
public ActionResult UploadExcel(HttpPostedFileBase file)
{
if (file == null)
{
return(Content("没有文件！", "text/plain"));
}
var rootPath = Request.MapPath("~/Upload/Excel/User");
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath); //不存在就创建文件夹
}
var fileName = Path.Combine(rootPath, Path.GetFileName(file.FileName));
try
{
file.SaveAs(fileName);
DataTable dtData = ExcelHelper.Import(fileName, 1);
var list = new NPOIExcel().ConvertTo <UserExcelInPut>(dtData, true).ToList();
userApp.ExcelImport(list);
return(Content("上传成功！", "text/plain"));
}
catch (Exception ex)
{
return(Content("上传异常 ！", "text/plain"));
}
}
public ActionResult ToExcel()
{
ITopClient client = new TopClientDefault();
Dictionary <string, string> dic = new Dictionary <string, string>();
string CompanyID = this.CompanyID;
dic.Add("CompanyID", CompanyID);
string result = client.Execute(DepartApiName.DepartApiName_GetList, dic);
string returnValue = string.Empty;
if (!result.IsEmpty())
{
DataListResult <SysDepartEntity> dataResult = JsonConvert.DeserializeObject <DataListResult <SysDepartEntity> >(result);
List <SysDepartEntity> listResult = dataResult.Result;
if (!listResult.IsNullOrEmpty())
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("部门名"));
dt.Columns.Add(new DataColumn("上级部门"));
dt.Columns.Add(new DataColumn("创建时间"));
dt.Columns.Add(new DataColumn("部门编号"));
foreach (SysDepartEntity t in listResult.Remove(item => item.DepartName == "Root"))
{
DataRow row = dt.NewRow();
row[0] = t.DepartName;
row[1] = t.ParentName;
row[2] = t.CreateTime.To("yyyy-MM-dd");
row[3] = t.DepartNum;
dt.Rows.Add(row);
}
string filePath = Server.MapPath("~/UploadFile/");
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = string.Format("部门管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"));
NPOIExcel excel = new NPOIExcel("部门管理", "部门管理", System.IO.Path.Combine(filePath, filename));
excel.ToExcel(dt);
returnValue = ("/UploadFile/" + filename).Escape();
}
}
DataResult returnResult = null;
if (!returnValue.IsEmpty())
{
returnResult = new DataResult()
{
Code = 1000, Message = returnValue
};
}
else
{
returnResult = new DataResult()
{
Code = 1001, Message = "没有任何数据导出"
};
}
return(Content(JsonHelper.SerializeObject(returnResult)));
}
public void ToDataTableTest()
{
//D:\NOPI.xlsx
string _path = string.Format(@"{0}NOPI.xlsx", UnitTestHelper.GetExecuteDirectory());
DataTable _table = NPOIExcel.ToDataTable(_path, 0, 0, 1);
Assert.IsTrue(_table.Rows.Count > 0);
}
public ActionResult ToExcel()
{
PageInfo pageInfo = new Git.Framework.DataTypes.PageInfo()
{
PageIndex = 1, PageSize = Int32.MaxValue
};
string roleName = WebUtil.GetFormValue <string>("roleName", string.Empty);
SysRoleProvider provider = new SysRoleProvider();
SysRoleEntity entity = new SysRoleEntity();
List <SysRoleEntity> list = provider.GetList();
List <SysRoleEntity> listResult = new List <SysRoleEntity>();
if (!list.IsNullOrEmpty())
{
listResult = list.Where(a => a.RoleNum.Contains(roleName) || a.RoleName.Contains(roleName)).OrderByDescending(a => a.ID).ToList();
}
if (!listResult.IsNullOrEmpty())
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("序号"));
dt.Columns.Add(new DataColumn("角色编号"));
dt.Columns.Add(new DataColumn("角色名"));
dt.Columns.Add(new DataColumn("创建时间"));
dt.Columns.Add(new DataColumn("备注"));
int count = 1;
foreach (SysRoleEntity t in listResult)
{
DataRow row = dt.NewRow();
row[0] = count;
row[1] = t.RoleNum;
row[2] = t.RoleName;
row[3] = t.CreateTime;
row[4] = t.Remark;
count++;
dt.Rows.Add(row);
}
string filePath = Server.MapPath("~/UploadFiles/");
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = string.Format("角色管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
NPOIExcel excel = new NPOIExcel("角色管理", "角色", System.IO.Path.Combine(filePath, filename));
excel.ToExcel(dt);
this.ReturnJson.AddProperty("Path", ("/UploadFiles/" + filename).Escape());
}
else
{
this.ReturnJson.AddProperty("d", "无数据导出!")
;
}
return(Content(this.ReturnJson.ToString()));
}
/// <summary>
/// 获取Export表数据
/// <summary>
/// <param name="queryJson">查询参数</param>
/// <returns></returns>
public FileResult Export2(Pagination pagination, string queryJson)
{
pagination.page = 1;
pagination.records = 0;
pagination.rows = 999999;
pagination.sidx = "";
pagination.sord = "ASC";
var datas = materialsSumIBLL.GetInventoryDetail(pagination, queryJson);
var dt = AsDataTable(datas);
var queryParam = queryJson.ToJObject();
var StartTime = "";
var EndTime = "";
if (!queryParam["start"].ToString().IsEmpty() && !queryParam["end"].ToString().IsEmpty())
{
StartTime = queryParam["start"].ToString();
EndTime = queryParam["end"].ToString();
}
else
{
StartTime = queryParam["StartTime"].ToString();
EndTime = queryParam["EndTime"].ToString();
}
DateTime StartTimes = queryParam["StartTime"].ToDate();
DateTime EndTimes = queryParam["EndTime"].ToDate();
string starttime = StartTimes.ToString("yyyyMMdd");
string endtime = EndTimes.ToString("yyyyMMdd");
DateTime Start = queryParam["start"].ToDate();
DateTime End = queryParam["end"].ToDate();
string start = Start.ToString("yyyyMMdd");
string end = End.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "库存明细统计", "库存明细统计", StartTime, EndTime);
if (!queryParam["start"].ToString().IsEmpty() && !queryParam["end"].ToString().IsEmpty())
{
if (start == end)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", start + "_库存明细统计.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", start + "-" + end + "_库存明细统计.xls"));
}
}
else
{
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_库存明细统计.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_库存明细统计.xls"));
}
}
}
public void DownLoad(string queryJson, string companyId)
{
var expression = ExtLinq.True <RefundOrderViewModel>();
var queryParam = queryJson.ToJObject();
if (!queryParam["begintime"].IsEmpty())
{
var begintime = queryParam["begintime"].ToDate();
//开始时间
expression = expression.And(t => t.F_RefundTime >= begintime);
}
if (!queryParam["endtime"].IsEmpty())
{
var endtime = queryParam["endtime"].ToDate().AddDays(1);
//结束时间
expression = expression.And(t => t.F_RefundTime < endtime);
}
if (!queryParam["keyword"].IsEmpty())
{
var keyword = queryParam["keyword"].ToString();
//用户卡号、表计编码、客户姓名
expression = expression.And(t => t.F_UserCard.Contains(keyword) || t.F_MeterCode.Contains(keyword) || t.F_CustomerName.Contains(keyword));
}
if (!string.IsNullOrEmpty(companyId))
{
expression = expression.And(t => t.F_OwnerId == companyId);
}
var recordList = _viewService.IQueryable(expression).OrderByDescending(x => x.F_CreatorTime);
var dt = new DataTable();
dt.Columns.AddRange(new[]
{
new DataColumn("订单号"),
new DataColumn("用户卡号"),
new DataColumn("表计编码"),
new DataColumn("退费金额"),
new DataColumn("本次余额"),
new DataColumn("客户名称"),
new DataColumn("经办人"),
new DataColumn("退费时间"),
});
foreach (var en in recordList)
{
var row = dt.NewRow();
row[0] = en.F_OrderNumber;
row[1] = en.F_UserCard;
row[2] = en.F_MeterCode;
row[3] = en.F_Money;
row[4] = en.F_Balance;
row[5] = en.F_CustomerName;
row[6] = en.F_CreatorUserName;
row[7] = en.F_RefundTime;
dt.Rows.Add(row);
}
NPOIExcel.ExportByWeb(dt, "退费记录", "退费记录.xls");
}
public void ToExcelTest()
{
string _xlsPath = string.Format(@"D:\Employees_{0}.xls", DateTime.Now.FormatDate(12));
DataTable _queryResult = sqlHelper.ExecuteDataTable(@"SELECT * FROM [Employees]", null);
NPOIExcel.ToExcel(_queryResult, "Hello", "Employees", _xlsPath);
bool _actual = File.Exists(_xlsPath);
Assert.IsTrue(_actual);
}
public void FillData(DateTime startDate, DateTime endDate, string filepath, string filename)
{
var table1 = GetMonthDate(startDate, endDate);
var excel = new NPOIExcel();
excel.ToExcel(table1, "1-1预处理部分", filepath, 4, 3);
var table2 = _waterMObservationApp.GetMonthDate(startDate, endDate);
excel.ToExcel(table2, "1-2水机", filepath, 5, 3);
}
public ActionResult XFExcel(string starttime)
{
OrganizeApp organizeApp = new OrganizeApp();
DataTable dt = new DataTable();
dt.Columns.Add("部门");
dt.Columns.Add("姓名");
DateTime now = DateTime.Now;
if (!string.IsNullOrEmpty(starttime))
{
now = starttime.ToDate();
}
now = new DateTime(now.Year, now.Month, 1);
for (int i = 1; i <= now.AddMonths(1).AddDays(-1).Day; i++)
{
dt.Columns.Add(i + "日");
}
foreach (var item in userApp.GetList(new Pagination()
{
page = 1, rows = 1000, sord = "asc", sidx = "F_DepartmentId asc,F_CreatorTime desc"
}, ""))
{
DataRow dr = dt.NewRow();
dr["部门"] = organizeApp.GetForm(item.F_DepartmentId).F_FullName;
dr["姓名"] = item.F_RealName;
for (int i = 1; i <= now.AddMonths(1).AddDays(-1).Day; i++)
{
string times = "";
if (!string.IsNullOrEmpty(item.F_PosCard))
{
foreach (var attr in posApp.GetList(item.F_PosCard.Trim(), new DateTime(now.Year, now.Month, i), new DateTime(now.Year, now.Month, i)))
{
times += attr.PosTime.ToString("HH:mm:ss") + " \r\n";
}
}
dr[i + "日"] = times;
}
dt.Rows.Add(dr);
}
NPOIExcel npoiexel = new NPOIExcel();
string fileDir = DateTime.Now.ToString("yyyyMMdd");
string fileName = "XF" + Guid.NewGuid().ToString("N") + ".xls";
string destDir = Server.MapPath(@"/XlsTemp") + "\\" + fileDir + "\\";
if (!Directory.Exists(destDir))
{
Directory.CreateDirectory(destDir);
}
npoiexel.ToExcel(dt, now.ToString("yyyy年MM月") + "的消费机数据", "Sheet1", destDir + fileName);
return(Content("/XlsTemp/" + fileDir + "/" + fileName));
}
public void DownLoad()
{
var expression = ExtLinq.True <IOTEntity>();
var iotList = _service.IQueryable();
var dt = new DataTable();
if (iotList != null && iotList.Any())
{
dt.Columns.AddRange(new[]
{
new DataColumn("序号"),
new DataColumn("批次"),
new DataColumn("采购日期"),
new DataColumn("IEMI"),
new DataColumn("SN"),
new DataColumn("IMSI"),
new DataColumn("ICCID"),
new DataColumn("卡号"),
new DataColumn("开户日期"),
new DataColumn("开户经办人"),
new DataColumn("使用硬件"),
new DataColumn("货号"),
new DataColumn("表类型"),
new DataColumn("使用客户"),
new DataColumn("模组所在地"),
new DataColumn("余额"),
new DataColumn("客户使用数量")
});
foreach (var en in iotList)
{
var row = dt.NewRow();
row[0] = en.M_ID;
row[1] = en.M_Batch;
row[2] = en.M_PurchaseTime;
row[3] = en.M_IMEI;
row[4] = en.M_SN;
row[5] = en.M_IMSI;
row[6] = en.M_ICCID;
row[7] = en.M_CardID;
row[8] = en.M_OpenAccountTime;
row[9] = en.M_OpenAccountUserName;
row[10] = en.M_HardwareName;
row[11] = en.M_ArtNo;
row[12] = en.M_MeterTypeName;
row[13] = en.M_CustomerName;
row[14] = en.M_Location;
row[15] = en.M_Balance;
row[16] = en.M_Remarks;
dt.Rows.Add(row);
}
}
NPOIExcel.ExportByWeb(dt, "M6310模组记录", "M6310模组记录.xls");
}
public async Task <List <ImportScheduleModel> > ImportDatas(DateTime startDate, string filePath)
{
var defaultDialysisType = await _itemsDetailApp.GetDefaultDialysisType() ?? "HD";
var list = new List <ImportScheduleModel>();
NPOIExcel excel = new NPOIExcel();
var dialysisTypes = (await _itemsDetailApp.GetItemList("DialysisType")).Select(t => t.F_ItemCode).OrderByDescending(t => t.Length).ToList();
var records = excel.ToListForSchedule("排班明细", filePath, dialysisTypes, 4, 0);
//var groups =
var groups = (await _itemsDetailApp.GetItemList("BedGroup")).Select(t => t.F_ItemCode).ToList();
var beds = await _dialysisMachineApp.GetList();
var patients = await _patientApp.GetList();
foreach (var item in records.GroupBy(t => t.F_GroupName))
{
if (!groups.Contains(item.Key))
{
continue;
}
foreach (var child in item.GroupBy(t => t.F_DialysisBedNo))
{
var findBed = beds.Find(t => t.F_GroupName == item.Key && t.F_DialylisBedNo == child.Key);
if (findBed == null)
{
continue;
}
foreach (var ele in child)
{
var patient = patients.Find(t => t.F_Name == ele.F_Name.Trim());
if (patient == null)
{
continue;
}
list.Add(new ImportScheduleModel
{
F_GroupName = item.Key,
F_DialysisBedNo = child.Key,
F_BId = findBed.F_Id,
F_VisitDate = startDate.AddDays(ele.DayOfWeek - 1),
F_VisitNo = ele.F_VisitNo,
F_PId = patient.F_Id,
F_DialysisNo = patient.F_DialysisNo,
F_Name = patient.F_Name,
F_DialysisType = ele.F_DialysisType ?? defaultDialysisType,
F_Sort = findBed.F_ShowOrder.ToInt()
});
}
}
}
return(list);
}
public FileResult NoOutExport(string classId, string startTime, string endTime)
{
IDictionary <string, string> parms = new Dictionary <string, string>();
var exportSql = new ReportHelper().CreateSql("Dorm_NoOutReport", classId, startTime, endTime, ref parms);
var dbParameter = CreateParms(parms);
var reports = LateReturnReportApp.GetDataTable(exportSql, dbParameter);
var ms = new NPOIExcel().ToExcelStream(reports, "长时间未出报表");
ms.Seek(0, SeekOrigin.Begin);
var filename = "长时间未出报表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
return(File(ms, "application/ms-excel", filename));
}
public bool UpLoad(Stream stream, string companyId)
{
var dataItems = (Dictionary <string, object>) new ItemsDetailApp().GetDataItemList();
var count = 2;
var ds = NPOIExcel.ImportExceltoDs(stream);
if (ds == null)
{
return(false);
}
var dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(false);
}
foreach (DataRow item in dt.Rows)
{
try
{
var row = item;
if (string.IsNullOrEmpty(row[0] as string) || string.IsNullOrEmpty(row[1] as string))
{
continue;
}
var channel = new ChannelApp().GetFormByName((string)row[0]);
if (channel == null)
{
throw new Exception("当前设备不存在");
}
var meter = _meterApp.GetFormByMeterCode((string)row[1], companyId);
if (meter == null)
{
throw new Exception("当前表计不存在");
}
var channeMeterEntity = new ChannelMeterEntity
{
F_ArchiveId = meter.F_Id,
F_ChannelId = channel.F_Id,
};
var channelMeter = _viewService.FindEntity(x => x.F_ArchiveId == meter.F_Id && x.F_DeleteMark != true);
SubmitForm(channeMeterEntity, channelMeter == null ? "" : channelMeter.F_Id);
count++;
}
catch (Exception ex)
{
throw new Exception("表格行" + count + "数据不正确," + ex.Message);
}
}
return(count > 2);
}
public void DownLoad(string queryJson, string companyId)
{
var dataItems = (Dictionary <string, object>) new ItemsDetailApp().GetDataItemList();
var expression = ExtLinq.True <ReportStatisticsViewModel>();
var queryParam = queryJson.ToJObject();
if (!queryParam["device"].IsEmpty())
{
var device = queryParam["device"].ToString();
//设备类型
expression = expression.And(t => t.F_MeterType == device);
}
if (!string.IsNullOrEmpty(companyId))
{
expression = expression.And(t => t.F_OwnerId == companyId);
}
var recordList = _viewService.IQueryable(expression).OrderByDescending(x => x.F_ReportCycle);
var dt = new DataTable();
dt.Columns.AddRange(new[]
{
new DataColumn("表类型"),
new DataColumn("周期"),
new DataColumn("总用量"),
new DataColumn("总计费"),
new DataColumn("总收入"),
new DataColumn("上线表数"),
new DataColumn("抄表次数"),
});
foreach (var en in recordList)
{
var row = dt.NewRow();
if (dataItems != null)
{
row[0] = ((Dictionary <string, string>)dataItems["DeviceType"])[en.F_MeterType];
}
else
{
row[0] = en.F_MeterType;
}
row[1] = en.F_ReportCycle;
row[2] = en.F_SumDosage;
row[3] = en.F_SumBill;
row[4] = en.F_SumPay;
row[5] = en.F_CountDevice;
row[6] = en.F_CountRecord;
dt.Rows.Add(row);
}
NPOIExcel.ExportByWeb(dt, "用量统计", "用量统计.xls");
}
public Stream GenerateDataTable(IEnumerable <客戶銀行資訊> data)
{
var query = data.Select(x => new
{
Id = x.Id,
銀行代碼 = x.銀行代碼,
銀行名稱 = x.銀行名稱,
分行代碼 = x.分行代碼.HasValue?x.分行代碼.Value:0,
帳戶名稱 = x.帳戶名稱,
帳戶號碼 = x.帳戶號碼,
客戶名稱 = x.客戶資料.客戶名稱
});
return(NPOIExcel.RenderListToExcel(query.ToList()));
}
public ActionResult Excel(DateTime?starttime, DateTime?endtime, bool?status)
{
NPOIExcel ex = new NPOIExcel();
var data = customerTranApp.GetList(starttime, endtime, status);
List <CustomerTransactionSimple> lst = data.Select(t => new CustomerTransactionSimple()
{
BillDate = t.BillDate, SourceName = t.Source, CustomerName = t.CustomerName, CustomerTelphone = t.Telphone, CustomerAddress = t.Address, TranStatus = t.TradStatus == true ? "成交" : "未成交", Remark = t.Remark
}).ToList();
DataTable dt = ListToDataTable <CustomerTransactionSimple>(lst);
string filePath = Server.MapPath("~/Temp/客户跟单统计.xls");
ex.ToExcel(dt, "客户跟单统计", "", filePath);
return(Success("操作成功"));
}
public virtual void DownLoad(string queryJson)
{
var dataItems = (Dictionary <string, object>) new ItemsDetailApp().GetDataItemList();
queryJson = HttpUtility.UrlDecode(queryJson, System.Text.Encoding.UTF8);
var companyId = OperatorProvider.Provider.GetCurrent().CompanyId;
var recordList = _readRecordApp.GetNonOnlineList(null, queryJson, companyId);
var dt = new DataTable();
dt.Columns.AddRange(new[]
{
new DataColumn("表类型"),
new DataColumn("生产厂商"),
new DataColumn("用户卡号"),
new DataColumn("表计编码"),
new DataColumn("客户名称"),
new DataColumn("上次底数"),
new DataColumn("上次上线"),
new DataColumn("联系方式"),
new DataColumn("安装地址"),
new DataColumn("备注"),
});
foreach (var en in recordList)
{
var row = dt.NewRow();
if (dataItems != null)
{
row[0] = ((Dictionary <string, string>)dataItems["DeviceType"])[en.F_MeterType];
}
else
{
row[0] = "";
}
row[1] = en.F_Factor;
row[2] = en.F_UserCard;
row[3] = en.F_MeterCode;
row[4] = en.F_CustomerName;
row[5] = en.F_TotalDosage == null ? "无" : ((decimal)en.F_TotalDosage).ToString("0.00");
row[6] = en.F_LastReadTime == null ? "无" : ((DateTime)en.F_LastReadTime).ToString("yyyy-MM-dd HH:mm:ss");
row[7] = en.F_MobilePhone;
row[8] = en.F_CustomerAddress;
row[9] = en.F_Description;
dt.Rows.Add(row);
}
NPOIExcel.ExportByWeb(dt, "上线统计", "上线统计.xls");
}
protected List <OrderPandianWithMonth> FetchOrders()
{
var ordersList = new List <OrderPandianWithMonth>();
foreach (var file in this.GetExcelFiles())
{
using (var excel = new NPOIExcel(file.FullName))
{
var table = excel.ExcelToDataTable(null, true);
var orderItems = this.ResolveOrders(table, file.FullName);
if (orderItems != null && orderItems.Any())
{
ordersList.AddRange(orderItems);
}
}
}
return(ordersList);
}
public ActionResult ToCheckExcel()
{
string orderNum = WebUtil.GetFormValue <string>("OrderNum", string.Empty);
List <CheckDataEntity> listResult = new CheckDataProvider().GetCheckOrder(orderNum);
if (!listResult.IsNullOrEmpty())
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("库位名称"));
dt.Columns.Add(new DataColumn("库位编号"));
dt.Columns.Add(new DataColumn("产品编码"));
dt.Columns.Add(new DataColumn("产品条码"));
dt.Columns.Add(new DataColumn("产品名称"));
dt.Columns.Add(new DataColumn("批次"));
dt.Columns.Add(new DataColumn("盘点数"));
foreach (CheckDataEntity t in listResult)
{
DataRow row = dt.NewRow();
row[0] = t.LocalName;
row[1] = t.LocalNum;
row[2] = t.ProductNum;
row[3] = t.BarCode;
row[4] = t.ProductName;
row[5] = t.BatchNum;
row[6] = "";
dt.Rows.Add(row);
}
string filePath = Server.MapPath("~/UploadFiles/");
if (!System.IO.Directory.Exists(filePath))
{
System.IO.Directory.CreateDirectory(filePath);
}
string filename = string.Format("盘点管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
NPOIExcel excel = new NPOIExcel("盘点管理", "盘点单", System.IO.Path.Combine(filePath, filename));
excel.ToExcel(dt);
this.ReturnJson.AddProperty("Path", ("/UploadFiles/" + filename).Escape());
}
else
{
this.ReturnJson.AddProperty("d", "无数据导出!");
}
return(Content(this.ReturnJson.ToString()));
}
public bool PushPandianReport(int monthNum, string pandianFolder)
{
var lst = Instance.PandianStatisticServerOptSet.ToList();
foreach (var item in lst)
{
System.Threading.ThreadPool.QueueUserWorkItem(o =>
{
var dt = item.PushMonthReport(monthNum, DateTime.Now.Year);
if (dt != null && dt.Rows.Count > 0)
{
var filename = System.IO.Path.Combine(pandianFolder, "pandian", $"ERP-{item.ServerName}-{monthNum}月份盘点订单{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
NPOIExcel.Export(dt, filename);
if (Environment.UserInteractive)
{
Console.WriteLine($"ERP-{item.ServerName}-{monthNum}月份盘点订单生成成功。文件名:{filename}");
}
}
});
}
//按渠道生成对账单
var prolst = Instance.ProductStatisticServerOptSet.ToList();
foreach (var pro in prolst)
{
System.Threading.ThreadPool.QueueUserWorkItem(o =>
{
var dt = pro.PushMonthReport(monthNum, DateTime.Now.Year);
if (dt != null && dt.Rows.Count > 0)
{
var filename = System.IO.Path.Combine(pandianFolder, "pandian", $"ERP-{pro.ServerName}-{monthNum}月份盘点订单{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
NPOIExcel.Export(dt, filename);
if (Environment.UserInteractive)
{
Console.WriteLine($"ERP-{pro.ServerName}-{monthNum}月份盘点订单生成成功。文件名:{filename}");
}
}
});
}
return(true);
}
private void testLoadExcel()
{
NPOIExcel excel = new NPOIExcel(path);
excel["測試表單2"][1, 1].setValue("測試字串1");
excel["測試表單2"][3, 1].setValue("測試字串2");
excel["測試表單2"][5, 1].setValue("測試字串3");
excel["測試表單2"][1, 3].setValue(10001);
excel["測試表單2"][3, 3].setValue(10002);
excel["測試表單2"][5, 3].setValue(10003);
excel["測試表單2"][2, 2].setValue(99.001);
excel["測試表單2"][4, 2].setValue(99.002);
excel["測試表單2"][6, 2].setValue(99.003);
excel["測試表單2"][2, 4].setValue(DateTime.Now.ToString());
excel["測試表單2"][4, 4].setValue(DateTime.Now.ToString());
excel["測試表單2"][6, 4].setValue(DateTime.Now.ToString());
excel.save(path);
}
public bool ImportEx(string filePath, params string[] arr)
{
DataTable dt = NPOIExcel.ExcelToTable(filePath);
if (dt == null)
{
throw new Exception("数据导入异常");
}
if (SaveData(dt, arr))
{
//删除excel
//File.Delete(filePath);
return(true);
}
else
{
return(false);
}
}
// 发送用户头像到闸机
public void SendUserHeadIco(string[] userId)
{
var filepath = AppDomain.CurrentDomain.BaseDirectory + "\\HeadIcoTemp\\";
var dt = DownImage(userId, filepath);
var zipfile = zipFile(filepath);
if (zipfile == null)
{
throw new Exception("上传头像文件出错！");
}
DHAccount.PUSH_DH_BATCHPHOTO_ZIP(zipfile);
var result = new NPOIExcel().ToExcel(dt, "导入用户" + DateTime.Now.ToString("yyyyMMddHHmmss"), "用户", filepath + "user.xls");
if (!result)
{
throw new Exception("上传Excel出错！");
}
DHAccount.PUSH_DH_STUDENT_EXCEL(filepath + "user.xls");
}
public FileResult Export(Pagination pagination, string queryJson)
{
var datas = orgResMangerIBLL.GetProductRateList(pagination, queryJson);
var dt = AsDataTable(datas);
var queryParam = queryJson.ToJObject();
DateTime StartTime = queryParam["StartTime"].ToDate();
DateTime EndTime = queryParam["EndTime"].ToDate();
string starttime = StartTime.ToString("yyyyMMdd");
string endtime = EndTime.ToString("yyyyMMdd");
var ms = NPOIExcel.ToExcelMoreheader(dt, "物料出成率列表", "物料出成率列表", starttime, endtime);
if (starttime == endtime)
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "_物料出成率列表.xls"));
}
else
{
return(File(ms.GetBuffer(), "application/vnd.ms-excel", starttime + "-" + endtime + "_物料出成率列表.xls"));
}
}
