public JsonResult ExportExcelAllLocaltion(int accountId, int parentId, int provinceId, string keySearch)
{
try
{
var res = OfficeHelper.CreateGeneratedFile(accountId, parentId, provinceId, keySearch);
if (res.Status)
{
return(Json(new
{
status = true,
Data = res.Data,
totalCount = 0,
totalRow = 0
}, JsonRequestBehavior.AllowGet));
}
return(Json(new
{
status = false,
Data = res.Data,
totalCount = 0,
totalRow = 0
}, JsonRequestBehavior.AllowGet));
}
catch
{
return(Json(new
{
status = false,
totalCount = 0,
totalRow = 0
}, JsonRequestBehavior.AllowGet));
}
}
protected void ExportExcel_Btn_Click(object sender, EventArgs e)
{
StringBuilder sb = new StringBuilder();
OfficeHelper helper = new OfficeHelper();
DataTable demodt = new DataTable();
InitDataColums(demodt);
DataTable dt = SelByWhere();
foreach (DataRow dtrow in dt.Rows)
{
DataRow dr = demodt.NewRow();
dr["PaymentID"] = dtrow["PaymentID"];
dr["UserName"] = dtrow["UserName"];
dr["PaymentNum"] = dtrow["PaymentNum"];
dr["PayPlatName"] = dtrow["PayPlatName"];
dr["MoneyPay"] = dtrow["MoneyPay"];
dr["MoneyTrue"] = dtrow["MoneyTrue"];
dr["StatusStr"] = dtrow["Status"];
dr["CStatusStr"] = dtrow["CStatus"];
dr["SuccessTime"] = dtrow["SuccessTime"];
demodt.Rows.Add(dr);
}
string columnames = "ID,会员名,订单号,支付平台,金额,实际金额,交易状态,处理结果,完成时间";
SafeSC.DownStr(helper.ExportExcel(demodt, columnames), DateTime.Now.ToString("yyyyMMdd") + "充值信息表.xls");
}
protected void DownToExcel_B_Click(object sender, EventArgs e)
{
OfficeHelper office = new OfficeHelper();
string name = ZType.ToLower().Equals("comment") ? "评论排行.xls" : "点击排行.xls";
SafeC.DownStr(office.ExportExcel(GetDt(), "标题,点击数,评论数,录入者,录入时间"), DateTime.Now.ToString("yyyyMMdd") + "点击排行.xls", Encoding.Default);
}
public async Task <string> ExcelImportEPPlusModelJsonAsync()
{
IFormFileCollection files = Request.Form.Files;
List <Article> articles = new List <Article>();
int code = 0;
string msg = "失败！";
var file = files[0];
string path = ConfigHelper.GetSectionValue("FileMap:FilePath") + files[0].FileName;
using (FileStream fs = System.IO.File.Create(path))
{
await file.CopyToAsync(fs);
fs.Flush();
}
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start(); //开始监视代码运行时间
FileInfo fileExcel = new FileInfo(path);
articles = OfficeHelper.LoadFromExcel <Article>(fileExcel).ToList();
TimeSpan timespan = watch.Elapsed; //获取当前实例测量得出的总时间
watch.Stop(); //停止监视
code = 1; msg = "成功！";
string json = CommonHelper.GetJsonResult(code, msg, new { articles, timespan });
return(json);
}
public static int LeafletImage2JPG(string imagePath, string fileID, string taskID)
{
int highHeightUnit = 0;
Bitmap fromImage = (Bitmap)Image.FromFile(imagePath);
imgHeight = 0; listBitmaps = new Dictionary <string, Bitmap>();
string root = Path.Combine(OfficeHelper.GetFolderPath(taskID, "Office"), fileID);
if (!Directory.Exists(@root))
{
Directory.CreateDirectory(@root);
}
List <Bitmap> listImage = new List <Bitmap>();
if (fromImage != null)
{
listImage.Add(fromImage);
highHeightUnit = MergerImageAndCut(listImage, root, true);
foreach (KeyValuePair <string, Bitmap> bitmaps in listBitmaps)
{
Bitmap bitmap = bitmaps.Value;
if (bitmap != null)
{
ImageFormat imageFormat = ImageFormat.Jpeg;
bitmap.Save(bitmaps.Key, imageFormat);
}
}
}
return(highHeightUnit);
}
private void Btn_Export_Click(object sender, RoutedEventArgs e)
{
DataSet ds = OfficeHelper.ReadExcel("D:\\123.xls", "Sheet1");
File.Create("D:\\会员名单.xls");
OfficeHelper.WriteExcel("D:\\会员名单.xls", ds, "Sheet1");
}
public string ModelExportEPPlusExcel()
{
string code = "fail";
List <Article> articleList = new List <Article>();
for (int x = 0; x < 100000; x++)
{
Article article = new Article();
article.Context = "内容：" + x;
article.Id = x + 1;
article.CreateTime = DateTime.Now;
article.Title = "标题：" + x;
articleList.Add(article);
}
string fileName = "MyModelExcel.xlsx";
string[] nameStrs = new string[4] {
"ID", "标题", "内容", "时间"
}; //按照模型先后顺序，赋值需要的名称
string savePath = "wwwroot/Excel"; //相对路径
string msg = "Excel/" + fileName; //文件返回地址，出错就返回错误信息。
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start(); //开始监视代码运行时间
bool b = OfficeHelper.ModelExportEPPlusExcel(articleList, savePath, fileName, nameStrs, ref msg);
TimeSpan timespan = watch.Elapsed; //获取当前实例测量得出的总时间
watch.Stop(); //停止监视
if (b)
{
code = "success";
}
return("{\"code\":\"" + code + "\",\"msg\":\"" + msg + "\",\"timeSeconds\":\"" + timespan.TotalSeconds + "\"}");
}
public void ContentManage_ToExcel()
{
VM_ContentManage vm = FillVMContentManage(ref err);
if (!string.IsNullOrEmpty(err))
{
function.WriteErrMsg(err); return;
}
DataTable dt = vm.setting.dt;
dt.Columns.Add("ShowTitle");
dt.Columns.Add("ShowElite");
dt.Columns.Add("ShowStatus");
foreach (DataRow dr in dt.Rows)
{
dr["ShowTitle"] = NodeID == 0 ? "[" + dr["NodeName"] + "]" + dr["Title"] : dr["Title"];
dr["ShowElite"] = conhelp.GetElite(dr["EliteLevel"].ToString());
dr["ShowStatus"] = conhelp.GetStatus(DataConvert.CLng(dr["Status"]));
}
DataTable newDt = dt.DefaultView.ToTable(false, "GeneralID,ShowTitle,Inputer,Hits,ShowElite,ShowStatus,CreateTime,UpDateTime".Split(','));
string columnames = "ID,标题,录入者,点击数,推荐,状态,添加时间,修改时间";
M_Node nodeMod = nodeBll.SelReturnModel(NodeID);
string nodename = nodeMod == null ? "" : "[" + nodeMod.NodeName + "]";
SafeSC.DownStr(OfficeHelper.ExportExcel(newDt, columnames), DateTime.Now.ToString("yyyyMMdd") + nodename + "内容管理表.xls");
}
private static void btn2Excel_click(object sender, System.EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel file;*.xlsx|*.xlsx";
sfd.ShowDialog();
int colCount = listview.Columns.Count;
string[] headers = new string[colCount];
for (int i = 1; i < colCount; i++)
{
headers[i] = listview.Columns[i].Text;
}
int rowCout = listview.Items.Count;
string[][] vals = new string[rowCout][];
for (int i = 0; i < rowCout; i++)
{
vals[i] = new string[colCount];
for (int j = 1; j < colCount; j++)
{
vals[i][j] = listview.Items[i].SubItems[j].Text;
}
}
OfficeHelper.saveToExcel(sfd.FileName, headers, vals);
}
public static int LeafletPdf2JPG(string pdfPath, string fileID, string taskID)
{
//Aspose.Pdf.Document pdf = new Aspose.Pdf.Document(pdfPath);
curIndex = 1;
int highHeightUnit = 0;
string root = Path.Combine(OfficeHelper.GetFolderPath(taskID, "Office"), fileID);
imgHeight = 0; listBitmaps = new Dictionary <string, Bitmap>();
if (!Directory.Exists(@root))
{
Directory.CreateDirectory(@root);
}
/*O2S.Components.PDFRender4NET.dll方式*/
PDFFile pdfFile = PDFFile.Open(pdfPath);
int pageCount = pdfFile.PageCount;
List <Bitmap> listImage = new List <Bitmap>();
for (int i = 1; i <= pageCount; i++)
{
Bitmap bitmap = pdfFile.GetPageImage(i - 1, 56 * (int)Definition.Two);
if (bitmap != null)
{
listImage.Add(bitmap);
}
}
highHeightUnit = MergerImageAndCut(listImage, root, true);
/*Aspose.Pdf方式,比较慢*/
//for (int pageCount = 1; pageCount <= pdf.Pages.Count; pageCount++)
//{
// string fileName = root + "\\" + pageCount + ".jpg";
// using (FileStream imageStream = new FileStream(fileName, FileMode.Create))
// {
// Aspose.Pdf.Devices.Resolution resolution = new Aspose.Pdf.Devices.Resolution(300);
// Aspose.Pdf.Devices.PngDevice pngDevice = new Aspose.Pdf.Devices.PngDevice(resolution);
// pngDevice.Process(pdf.Pages[pageCount], imageStream);
// Image image = Image.FromStream(imageStream);
// imageStream.Close();
// if (image != null)
// ImgCutSave(image, root, pageCount); //代码截图
// }
// if (File.Exists(fileName))
// File.Delete(fileName);
//}
foreach (KeyValuePair <string, Bitmap> bitmaps in listBitmaps)
{
Bitmap bitmap = bitmaps.Value;
if (bitmap != null)
{
ImageFormat imageFormat = ImageFormat.Jpeg;
bitmap.Save(bitmaps.Key, imageFormat);
}
}
return(highHeightUnit);
}
protected void ExportExcel_Btn_Click(object sender, EventArgs e)
{
OfficeHelper helper = new OfficeHelper();
DataTable dt = SelByWhere();
DataTable newDt = dt.DefaultView.ToTable(false, "PaymentID,UserName,PaymentNum,PayPlatName,MoneyPay,MoneyTrue,Status,CStatus,SuccessTime".Split(','));
string columnames = "ID,会员名,订单号,支付平台,金额,实际金额,交易状态,处理结果,完成时间";
SafeSC.DownStr(helper.ExportExcel(newDt, columnames), DateTime.Now.ToString("yyyyMMdd") + "充值信息表.xls");
}
private void Btn_Import_Click(object sender, RoutedEventArgs e)
{
DataSet ds = OfficeHelper.ReadExcel("D:\\123.xls", "Sheet1");
for (int i = 0; i < ds.Tables.Count; i++)
{
//这里做处理
}
}
/// <summary>
/// 导出EXCEL
/// </summary>
/// <param name="day"></param>
/// <param name="pay_state"></param>
/// <param name="order_state"></param>
public void ExportExcel(int day, int pay_state, int order_state)
{
var order_list = OrderCache($"orderlist_{day}", day);
if (order_list.Any())
{
order_list = order_list.Where(n => n.pay_state == pay_state && n.order_state == order_state).ToList();
List <dynamic> list = new List <dynamic>();
if (order_list.Any())
{
foreach (var item in order_list)
{
var one = new
{
item.id,
product_name = item.product.name,
pay_state = Enum.GetName(typeof(Pay_state), item.pay_state),
order_state = Enum.GetName(typeof(Order_state), item.order_state),
user_name = item.user.name,
item.order_number,
item.wx_order_num,
item.user_remark,
item.add_time,
item.pay_time,
item.refund_number,
item.pay_account,
item.count,
item.product.price,
item.order_money
};
list.Add(one);
}
if (list.Any())
{
DataTable dt = ListToDataTable.List2DataTable <dynamic>(list);
if (dt != null && dt.Rows.Count > 0)
{
dt.Columns["id"].ColumnName = "id";
dt.Columns["product_name"].ColumnName = "产品名称";
dt.Columns["user_name"].ColumnName = "用户名称";
dt.Columns["count"].ColumnName = "产品个数";
dt.Columns["price"].ColumnName = "产品价格";
dt.Columns["order_money"].ColumnName = "订单金额";
dt.Columns["pay_state"].ColumnName = "支付状态";
dt.Columns["order_state"].ColumnName = "订单状态";
dt.Columns["order_number"].ColumnName = "订单号";
dt.Columns["wx_order_num"].ColumnName = "微信订单号";
dt.Columns["add_time"].ColumnName = "下单时间";
dt.Columns["pay_time"].ColumnName = "支付时间";
dt.Columns["pay_account"].ColumnName = "付款账号";
dt.Columns["refund_number"].ColumnName = "退单号";
}
bool hh = OfficeHelper.ExportExcelWithAspose(dt, $"{day}天内订单信息{DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒")}", "", true);
}
}
}
}
/// <summary>
/// 统计考勤
/// </summary>
public static void Atten()
{
IAdministratorContract _adminContract = EntityContract._adminContract;
IAttendanceContract _attenContract = EntityContract._attenContract;
IAttendanceStatisticsContract _attenStatisticsContract = EntityContract._attenStatisticsContract;
DateTime current = DateTime.Now;
List <Administrator> listAdmin = _adminContract.Administrators.Where(x => x.IsDeleted == false && x.IsEnabled == true).ToList();
List <Attendance> listAtten = _attenContract.Attendances.Where(x => x.IsDeleted == false && x.IsEnabled == true && x.AttendanceTime.Year == current.Year && x.AttendanceTime.Month == current.Month && x.AttendanceTime.Day == current.Day).ToList();
List <int> listAdminId = listAdmin.Select(x => x.Id).ToList();
List <AttendanceStatistics> listStatistics = _attenStatisticsContract.AttendanceStatisticses.Where(x => listAdminId.Contains(x.AdminId)).ToList();
Dictionary <string, int> dic = GetHoliday();
WorkTime workTime;
List <Attendance> listEntity = new List <Attendance>();
List <AttendanceStatistics> listStatisticsAdd = new List <AttendanceStatistics>();
List <AttendanceStatistics> listStatisticsUpdate = new List <AttendanceStatistics>();
foreach (Administrator admin in listAdmin)
{
workTime = GetWorkTime(admin);
bool res = OfficeHelper.IsWorkDay(current, workTime, dic);
if (res == true)
{
Attendance atten = GetAtten(listAtten, current, admin.Id, workTime.IsPersonalTime);
if (atten.Id == 0)
{
listEntity.Add(atten);
}
AttendanceStatistics statistics = GetAttenStatistics(listStatistics, workTime, atten, admin.Id);
if (statistics.Id == 0)
{
listStatisticsAdd.Add(statistics);
}
else
{
listStatisticsUpdate.Add(statistics);
}
}
else
{
string strDate = current.ToString("yyyy/MM/dd 00:00:00");
DateTime date = DateTime.Parse(strDate);
IOvertimeContract _overTimeContract = DependencyResolver.Current.GetService <IOvertimeContract>();
int count = _overTimeContract.Overtimes.Where(x => x.AdminId == admin.Id && x.VerifyType == (int)VerifyFlag.Pass && date.CompareTo(x.StartTime) >= 0 && date.CompareTo(x.EndTime) <= 0).Count();
if (count > 0)
{
Attendance atten = GetAtten(listAtten, current, admin.Id, workTime.IsPersonalTime);
if (atten != null)
{
listEntity.Add(atten);
}
}
}
}
_attenContract.Add(listEntity, listStatisticsAdd, listStatisticsUpdate);
}
public OnlineOrdersResultDto <UpTotalModel> ImportExcel(IFormFile formFile)
{
OnlineOrdersResultDto <UpTotalModel> result = new OnlineOrdersResultDto <UpTotalModel>()
{
StatusCode = 0, Message = "上传Excel失败", Ext1 = new UpTotalModel()
{
}
};
if (Request.Form.Files == null || Request.Form.Files.Count == 0)
{
result.Message = "上传的Excel文件为空";
return(result);
}
try
{
IFormFile fileinput = Request.Form.Files[0];
var filename = ContentDispositionHeaderValue.Parse(fileinput.ContentDisposition).FileName; // 原文件名（包括路径）
var extName = filename.Substring(filename.LastIndexOf('.')).Replace("\"", ""); // 扩展名
if (extName.ToLower() != ".xls" && extName.ToLower() != ".xlsx")
{
result.Message = "上传文件格式不正确";
return(result);
}
string shortfilename = $"{Guid.NewGuid()}{extName}"; // 新文件名
string fileSavePath = hostingEnvironment.WebRootPath + @"\upload\stock\"; //文件临时目录，导入完成后 删除
filename = fileSavePath + shortfilename; // 新文件名（包括路径）
if (!Directory.Exists(fileSavePath))
{
Directory.CreateDirectory(fileSavePath);
}
using (FileStream fs = System.IO.File.Create(filename)) // 创建新文件
{
fileinput.CopyTo(fs); // 复制文件
fs.Flush(); // 清空缓冲区数据
}
//读取Excel中的文件到DataTable中
using (Stream sm = System.IO.File.OpenRead(filename))
{
DataTable dt = OfficeHelper.ReadStreamToDataTable(sm, extName, null, true, 4);
List <UploadModel> models = dt.GetUpLoad();
result.StatusCode = 1;
result.Message = "上传Excel成功";
result.Ext1.FileName = shortfilename;
result.Ext1.listResult = models;
result.Ext1.listHead = ConstUpLoadDic.StockHead.GetUpLoadHead();
}
}
catch (Exception ex)
{
result.StatusCode = 2;
result.Message = ex.Message;
}
return(result);
}
protected void DownWord_B_Click(object sender, EventArgs e)
{
StringWriter sw = new StringWriter();
var mu = buser.GetLogin();
string url = "ExportIDCOrder.aspx?id=" + Mid + "&uname=" + mu.UserName + "&upwd=" + mu.UserPwd;
string vpath = "/UploadFiles/IDC订单详情-" + Mid + ".doc";
Server.Execute(url, sw);
SafeSC.DownFile(OfficeHelper.W_HtmlToWord(sw.ToString(), vpath));
}
protected void DownDoc_Btn_Click(object sender, EventArgs e)
{
M_Plat_Comp compMod = compBll.SelReturnModel(Mid);
string html = Request.Form["html_hid"];
html = "<html><head></head><body>" + html + "</body></html>";
string vpath = "/UploadFiles/Plat/CompReport/" + compMod.CompName + ".doc";
vpath = OfficeHelper.W_HtmlToWord(html, vpath);
SafeSC.DownFile(vpath);
}
public void ListMapExcel()
{
var list = CreateTestData1List();
var oldJson = JsonConvert.SerializeObject(list);
var dt = OfficeHelper.ToDatatable(list, new Dictionary <string, Expression <Func <TestData1, object> > > {
{ "Id", it => it.Id },
{ "Name", it => it.Name },
{ "Time", it => it.Time }
});
DataTable newTable;
byte[] bs;
using (var memoryStream = new MemoryStream())
{
var ds = new DataSet();
ds.Tables.Add(dt);
NPOIHelper.DatasetToExcel(ds, memoryStream, ExcelFormat.Xlsx);
bs = memoryStream.ToArray();
}
using (var memoryStream = new MemoryStream(bs))
{
newTable = NPOIHelper.ExcelToDataset(memoryStream).Tables[0];
}
int successCount = 0;
List <string> messageList = new List <string>(newTable.Rows.Count);
List <TestData1> testData1List = new List <TestData1>(newTable.Rows.Count);
foreach (var item in OfficeHelper.ConvertFromDatatable(newTable, new Dictionary <string, Func <TestData1, object, DataRow, bool> > {
{ "Id", (it, obj, row) => OfficeHelper.TryConvertThen <int>(obj, v => it.Id = v) },
{ "Name", (it, obj, row) => OfficeHelper.TryConvertThen <string>(obj, v => it.Name = v) },
{ "Time", (it, obj, row) => OfficeHelper.TryConvertThen <DateTimeOffset>(obj, v => it.Time = v) }
}))
{
if (item.IsSuccess)
{
successCount++;
testData1List.Add(item.Data);
}
else
{
messageList.Add(item.Message);
}
}
if (successCount == testData1List.Count)
{
var newJson = JsonConvert.SerializeObject(testData1List);
//Assert.AreEqual(oldJson, newJson); 由于DateTime转换到Excel精度丢失，无法保证导入导出的数据完全一致
}
else
{
Assert.Fail();
}
}
protected void makeWordBtn_Click(object sender, EventArgs e)
{
if (!Directory.Exists(Server.MapPath(wordLink)))
{
Directory.CreateDirectory(Server.MapPath(wordLink));
}
string vpath = wordLink + GeneralID + ".doc";
string html = GetContentHtml();
OfficeHelper.W_HtmlToWord(html, vpath);
Response.Redirect("EditContent.aspx?GeneralID=" + GeneralID + "&tab=1");
}
public string Content_WordApi(int gid, string op)
{
M_CommonData conMod = contentBll.SelReturnModel(gid);
M_APIResult retMod = new M_APIResult(M_APIResult.Failed);
if (conMod == null || conMod.GeneralID < 1)
{
retMod.result = "内容[" + gid + "]不存在"; return(retMod.ToString());
}
switch (op)
{
case "create":
{
string pdfdir = "";
if (string.IsNullOrEmpty(conMod.Rtype))
{
conMod.Rtype = ZLHelper.GetUploadDir_System("Content", "Word") + conMod.GeneralID + ".docx";
}
pdfdir = Path.GetDirectoryName(Server.MapPath(conMod.Rtype));
if (!Directory.Exists(pdfdir))
{
Directory.CreateDirectory(pdfdir);
}
//------------------------
string html = GetContentHtml(conMod);
OfficeHelper.W_HtmlToWord(html, conMod.Rtype);
contentBll.UpdateByID(conMod);
retMod.result = conMod.Rtype;
retMod.retcode = M_APIResult.Success;
}
break;
case "del":
if (!string.IsNullOrEmpty(conMod.Rtype))
{
if (System.IO.File.Exists(Server.MapPath(conMod.Rtype)))
{
System.IO.File.Delete(Server.MapPath(conMod.Rtype));
}
conMod.Rtype = "";
contentBll.UpdateByID(conMod);
retMod.retcode = M_APIResult.Success;
}
break;
default:
retMod.retmsg = "[" + op + "]不存在";
break;
}
return(retMod.ToString());
}
private void RunPlotQueue()
{
FileRepository repo = new FileRepository();
FileTask task = null;
while ((task = repo.GetTask("")) != null)
{
if (task.ExtName.Equals("dwg", StringComparison.OrdinalIgnoreCase))
{
try
{
label2.Text = string.Format("正在获取文件...[{0}]", task.Name);
//1.从filestore获取文件
byte[] bytes = FileStoreHelper.GetFile(task.ID);
FileStoreHelper.SaveFileBuffer(bytes, Path.Combine(OfficeHelper.GetFolderPath(task.ID, "Files"), task.Name));
//2.设置文件数据库状态为进行中
repo.StartTask(task.ID);
//3.开始进行转换
label2.Text = string.Format("正在进行格式打印...[{0}]", task.Name);
var imgDTO = OfficeHelper.InitDTO(task.Name, bytes.Length, task.ID);
var result = FileConverter.Exec(imgDTO);
bool isSucc = false;
if (result != null && result.status)
{
//4.设置转图层次，并生成json文件
imgDTO.Versions[0].ImageZoomLevel = result.ZoomLevel;
OfficeHelper.WriteJsonFile(imgDTO);
isSucc = true;
}
Application.DoEvents();
//5.回置状态
label2.Text = "最近：[" + imgDTO.Name + "]打印" + (isSucc ? "成功":"失败") + "，打印时间（" + DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分") + "）";
repo.EndTask(task.ID, isSucc ? ResultStatus.Success:ResultStatus.Error);
//6.删除原文件
if (File.Exists(imgDTO.Versions[0].FullPath))
{
File.Delete(imgDTO.Versions[0].FullPath);
}
}
catch (Exception ex)
{
// 记录日志
LogWriter.Info(string.Format(ex.Message, DateTime.Now.ToString(), task.ID, task.Name, ex.StackTrace));
repo.EndTask(task.ID, ResultStatus.Error);
}
}
}
}
public FileStreamResult TestExport()
{
ExportAdmin param = new ExportAdmin();
param.Title = "这是一个测试";
param.Items = new List <ExportAdminItem>();
param.FileName = "exportdemo.xls";
param.FilterContent = "记录筛选的内容";
param.Items.Add(new ExportAdminItem()
{
Username = "******",
Nickname = "nick1",
CreateTime = DateTime.Now,
LoginTime = DateTime.Now,
Money = 100,
//NoInclude = 1000
});
param.Items.Add(new ExportAdminItem()
{
Username = "******",
Nickname = "nick2",
CreateTime = DateTime.Now,
LoginTime = DateTime.Now,
Money = 50.2321M,
//NoInclude = 1000
});
param.Items.Add(new ExportAdminItem()
{
Username = "******",
Nickname = "nick3",
CreateTime = DateTime.Now,
LoginTime = DateTime.Now,
Money = -100.2M,
//NoInclude = 1000
});
OfficeHelper bll = new OfficeHelper();
IWorkbook book = bll.ExportExcel <ExportAdminItem>(param);
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Position = 0;
FileStreamResult fResult = new FileStreamResult(ms, "application/octet-stream")
{
FileDownloadName = param.FileName
};
return(fResult);
}
protected void Export_B_Click(object sender, EventArgs e)
{
OfficeHelper office = new OfficeHelper();
DataTable dt = contentBll.GroupDayCount(DataConverter.CLng(Request.Form["curNid_hid"]), Convert.ToInt32(Request.Form["years"]), Convert.ToInt32(Request.Form["months"]), Convert.ToInt32(Request.Form["model"]));
dt.Columns.Add("Date");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["Date"] = DataConverter.CDate(dt.Rows[i]["Years"] + "-" + dt.Rows[i]["Months"] + "-" + dt.Rows[i]["Days"]).ToString("yyyy-MM-dd");;
}
DataTable newDt = dt.DefaultView.ToTable(false, "Date,PCount,ComCount,Hits".Split(','));
SafeC.DownStr(office.ExportExcel(newDt, "日期,发稿量,评论数,点击数"), DateTime.Now.ToString("yyyyMMdd") + "时间统计.xls", Encoding.Default);
}
public static List <T> Import <T>(T t, string path)
{
DataTable dt = OfficeHelper.ReadExcelToDataTable(path);
var lst = new List <T>(1000);
System.Type tt = t.GetType(); //获取指定名称的类型
object ff = Activator.CreateInstance(tt, null); //创建指定类型实例
PropertyInfo[] fields = ff.GetType().GetProperties(); //获取指定对象的所有公共属性
List <MapNameAttribute> attributes = new List <MapNameAttribute>(20);
foreach (var item in fields)
{
var temps = item.GetCustomAttributes(typeof(MapNameAttribute), true);
if (temps != null)
{
foreach (var temp in temps)
{
attributes.Add(temp as MapNameAttribute);
}
}
}
for (int i = 1; i < dt.Rows.Count; i++)
{
object obj = Activator.CreateInstance(tt, null);
var dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
var column = dt.Columns[j];
var columnMap = attributes.FirstOrDefault(a => a.MapName == column.ColumnName.TryTrim());
// 没找到匹配的字段就忽略
if (columnMap == null)
{
continue;
}
foreach (PropertyInfo p in fields)
{
if (columnMap.Name != p.Name)
{
continue;
}
p.SetValue(obj, Convert.ChangeType(dr[column.ColumnName], p.PropertyType), null);//给对象赋值
}
}
lst.Add((T)obj);
}
return(lst);
}
public ChangePaymentType GetChangePaymentTypeData(string officeNumber)
{
var result = new ChangePaymentType();
var companyId = OfficeHelper.GetCompanyId(officeNumber);
var list = this.GetPaymentTypes(companyId);
var office = new OfficeHelper().GetOfficeById(officeNumber);
var paymentTypes = (from item in list orderby item.Description select item).ToList();
result.PaymentTypes = paymentTypes;
result.PaymentDetails = new List <PaymentTransactionLite>();
result.PaymentDetails = this.GetDetails(officeNumber) as List <PaymentTransactionLite>;
result.IsTodayClosed = new DailyClosingIt2Manager().IsTodayClosed(officeNumber, office.TimeZone, office.UseDST);
return(result);
}
public ActionResult ImportXlsx(HttpPostedFile import)
{
DataSet ds = null;
try
{
if (import != null && import.ContentLength > 0)
{
ds = NPOIHelper.ExcelToDataset(import.InputStream);
}
}
catch (Exception)
{
throw;
}
if (ds.Tables?.Count > 0)
{
var dt = ds.Tables[0];
int successCount = 0;
List <string> messageList = new List <string>(dt.Rows.Count);
List <TestData1> testData1List = new List <TestData1>(dt.Rows.Count);
foreach (var item in OfficeHelper.ConvertFromDatatable(dt, new Dictionary <string, Func <TestData1, object, DataRow, bool> > {
{ "Id", (it, obj, row) => OfficeHelper.TryConvertThen <int>(obj, v => it.Id = v) },
{ "Name", (it, obj, row) => OfficeHelper.TryConvertThen <string>(obj, v => it.Name = v) },
{ "Time", (it, obj, row) => OfficeHelper.TryConvertThen <DateTime>(obj, v => it.Time = v) }
}))
{
if (item.IsSuccess)
{
successCount++;
testData1List.Add(item.Data);
}
else
{
messageList.Add(item.Message);
}
}
//下面部分只是为了展示出导入的数据
ViewData["successCount"] = successCount;
ViewData["messageList"] = messageList;
ViewData["testData1List"] = testData1List;
return(View("Index", CreateTestData1List()));
}
else
{
throw new Exception("所上传文件中不包含工作表！");
}
}
public int FileToImage(string filePath, string categoryId)
{
int back = 0;
if (!string.IsNullOrEmpty(filePath))
{
string imagefilePath = Path.Combine(GlobalConfiguration.WebRootPath, "imgfile", categoryId);
int lastSplitX = filePath.LastIndexOf("/");
int lastSplitP = filePath.LastIndexOf(".");
string mediaName = filePath.Substring(lastSplitX + 1, (lastSplitP - lastSplitX - 1));
//string pdfpath = imagefilePath+"\\" + mediaName + ".pdf";
try
{
string mediaPath = Path.Combine(imagefilePath, mediaName) + "\\";
if (Path.GetExtension(filePath).ToLower() == ".doc" ||
Path.GetExtension(filePath).ToLower() == ".docx")
{
//OfficeHelper.Word2PDF(filePath, pdfpath);
//if (!Directory.Exists(mediaPath))
//{
// Directory.CreateDirectory(mediaPath);
//}
//SwfHelper.Pdf2Img(pdfpath, mediaPath);
//FileInfo file = new FileInfo(pdfpath);
//file.Delete();
if (!Directory.Exists(mediaPath))
{
Directory.CreateDirectory(mediaPath);
}
OfficeHelper.Word2Jpg(filePath, mediaPath);
}
else if (Path.GetExtension(filePath).ToLower() == ".pdf")
{
if (!Directory.Exists(mediaPath))
{
Directory.CreateDirectory(mediaPath);
}
SwfHelper.Pdf2Img(filePath, mediaPath);
}
back = Directory.GetFiles(mediaPath).Length;
}
catch (Exception ex)
{
// TODO 记录日志
}
}
return(back);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string wordName = HttpUtility.UrlDecode(Request["Name"] ?? "");
string wordDir = "/UploadFiles/auto/outToWord/";
if (string.IsNullOrEmpty(wordName))
{
wordName = function.GetRandomString(6);
}
SafeSC.CreateDir(wordDir);
string wordPath = wordDir + wordName + ".docx";
SafeSC.DownFile(OfficeHelper.W_HtmlToWord("<html>" + Html + "</html>", wordPath));
}
}
protected void DownExcel_Btn_Click(object sender, EventArgs e)
{
M_Pub_Excel excelMod = new M_Pub_Excel();
B_Pub_Excel excelBll = new B_Pub_Excel();
M_Pub pubinfo = pub.GetSelect(PubID);
excelMod = excelBll.SelByTbName(pubinfo.PubTableName);
if (excelMod == null)
{
function.WriteErrMsg("尚未为表：" + pubinfo.PubTableName + "指定导出规则,请先<a href='PubExcel.aspx?mid=" + pubinfo.PubModelID + "'>点此设定导出规则</a>");
}
OfficeHelper ofHelper = new OfficeHelper();
SafeC.DownStr(ofHelper.GetExcelByDT(GetDT(), excelMod.Fields, excelMod.CNames), DateTime.Now.ToString("yyyyMMdd") + "互动信息.xls", Encoding.Default);
}
private int getTotalWordCount(string path_ = "%")
{
int count = 0;
//var info = OfficeHelper.GetWordDocumentStats(@"D:\Temp\!Downloads\5cd3_b_04_public_statement.docx");
var info = OfficeHelper.GetWordDocumentStats(@path_);
Console.WriteLine("Characters: " + info.Characters);
Console.WriteLine("Words: " + info.Words);
Console.WriteLine("Pages: " + info.Pages);
Console.WriteLine("Paragraphs: " + info.Paragraphs);
Console.WriteLine("Lines: " + info.Lines);
count = info.Words;
return(count);
}
