private void button5_Click(object sender, EventArgs e)
{
string strFile_jisuancanshu = "";
OpenFileDialog ofd = new OpenFileDialog();
ofd.InitialDirectory = "D:\\开题\\shuju";
//ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
ofd.ValidateNames = true;
ofd.CheckPathExists = true;
ofd.CheckFileExists = true;
//strFileA_xishu = "D:\\开题\\shuju\\20120712_20m+\\1000\\2012-07-12-10_56_36.txt ";
if (ofd.ShowDialog() != DialogResult.OK)
{
MessageBox.Show("请选择数据文件");
// return;
}
strFile_jisuancanshu = ofd.FileName;
ps5000example.ExcelHelp excelhelp = new ExcelHelp();
//MessageBox.Show("您当前选择数据为" + strFileName8.ToString());
Jisuancanshu = excelhelp.LoadDataFromExcel(strFile_jisuancanshu);
JisuancanshuFor_1_3 = excelhelp.LoadDataFromExcel_sheel2(strFile_jisuancanshu);
if (Jisuancanshu != null && JisuancanshuFor_1_3 != null)
{
MessageBox.Show("读取数据成功");
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
if (listRes.Count == 0)
{
PageFunc.AjaxAlert(this.Page, "数据为空，请先统计查询！");
}
else
{
DateTime dtStart = DateTime.Parse(txtDateStart.Text);
DateTime dtEnd = DateTime.Parse(txtDateEnd.Text);
string fileName = string.Format("{0}-{1}_{2}_{3}_{4}.xls",
dtStart.ToString("yyyy-MM-dd"),
dtEnd.ToString("yyyy-MM-dd"),
string.IsNullOrEmpty(ddlAccount.SelectedValue)?"全部帐号":ddlAccount.SelectedValue,
string.IsNullOrEmpty(ddlAccount.SelectedValue) ? "全部推广位" : ddlAppKeyID.SelectedItem.Text,
DateTime.Now.ToString("yyyy-MM-dd"));
ExcelHelp <Model.TljStatXls> excelH = new ExcelHelp <Model.TljStatXls>();
Hashtable ht = new Hashtable();
ht.Add("dotime", "日期");
ht.Add("yj", "预估佣金");
ht.Add("yjds", "预估到手佣金");
ht.Add("use_amount", "淘礼金支出");
ht.Add("md_amount", "免单金额");
ht.Add("ykresult", "盈亏情况");
ht.Add("md_count", "免单数量");
excelH.getExcel(listRes, ht, fileName);
}
}
protected void Page_Load(object sender, EventArgs e)
{
tealogin = (Teacher)Session["user"];
state = Convert.ToInt32(Session["state"]);
string op = Context.Request["op"];
if (op == "add")
{
saveTeacher();
}
if (op == "change")
{
saveChange();
}
//删除教师
if (op == "del")
{
delTeal();
Search();
getdata(Search());
}
if (op == "import")
{
int collegeId;
if (state == 0)
{
collegeId = Convert.ToInt32(Context.Request["collegeId"]);
}
else
{
collegeId = tealogin.college.ColID;
}
string path = Security.Decrypt(Request["fileName"]);
DataTable dt = TableHelper.GetDistinctSelf(ExcelHelp.excelToDt(path, "excel"), "工号");
int i = ImportHelper.Teacher(dt, collegeId);
int row = ExcelHelp.excelToDt(path, "excel").Rows.Count;
int repeat = row - i;
if (i > 0)
{
LogHelper.Info(this.GetType(), tealogin.TeaAccount + "教师信息导入 -" + i + " " + "条信息");
Response.Write("导入成功，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
else
{
Response.Write("导入失败，总数据有" + row + "条，共导入" + i + "条数据，重复数据有" + repeat + "条");
Response.End();
}
}
//if (op == "upload")
//{
// upload();
//}
if (!IsPostBack)
{
Search();
getdata(Search());
}
colds = colbll.Select();
}
/// <summary>
/// excel读到table
/// </summary>
/// <returns></returns>
private DataTable excelToDt()
{
DataTable dt1 = new DataTable();
int regId;
if (user.RoleId.RoleName == "超级管理员")
{
regId = Convert.ToInt32(Request["regId"]);
}
else
{
regId = user.ReginId.RegionId;
}
string path = Session["path"].ToString();
try
{
dt1 = ExcelHelp.excelToDt(path, "excel");
DataColumn dc = new DataColumn("地区ID", typeof(int));
dc.DefaultValue = regId;
dt1.Columns.Add(dc);
row = dt1.Rows.Count;
}
catch (Exception ex)
{
Response.Write(ex);
Response.End();
}
return(dt1);
}
/// <summary>
/// 订单导出
/// </summary>
/// <param name="businessName">商家名称</param>
/// <param name="orderNo">订单号</param>
/// <param name="userAccount">下单人</param>
/// <param name="orderStatus">订单状态</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns></returns>
public ActionResult OrderExport(string QueryBusinessName, string QueryOrderNo, string QueryUserName, int QueryOrderStatusId, string QueryStartTime, string QueryEndTime)
{
try
{
var list = _orderService.OrderExpert(QueryBusinessName, QueryOrderNo, QueryUserName, QueryOrderStatusId, QueryStartTime, QueryEndTime, int.Parse(Loginer.BusinessId));
var result = OrderToOrderExpert(list);
var dt = EntityConverter <OrderExpert> .CreateDataTableByAnyListEntity(result);
if (dt == null || dt.Rows.Count <= 0)
{
return(Content("<script type='text/javascript'>alert('无查询结果，请检查。'); window.location.href = '/Order/List'</script>"));
}
string[] expColNames = { "OrderId", "OrderNo", "RealAmount", "NickName", "PhoneNo", "BusinessName", "StatusName", "OrderTime" };
Dictionary <string, string> expColAsNames = new Dictionary <string, string>()
{
{ "OrderId", "序号" },
{ "OrderNo", "订单编号" },
{ "RealAmount", "订单金额" },
{ "NickName", "下单人" },
{ "PhoneNo", "下单人电话" },
{ "BusinessName", "商家名称" },
{ "StatusName", "订单状态" },
{ "OrderTime", "创建时间" }
};
var ms = ExcelHelp.ToExcel(dt, null, expColNames, expColAsNames, null, 0);
return(File(ms, "application/vnd.ms-excel", ExcelHelp.CreateFileName("D")));
}
catch (Exception ex)
{
//Logger.Error("OrdersController--->OrderExport：" + ex.ToString());
}
return(Content("<script type='text/javascript'>alert('Execl生成失败，请检查。'); window.location.href = '/Order/List'</script>"));
}
private void bt_dataout_Click(object sender, RoutedEventArgs e)
{
List <BorrowHistory> tl = (List <BorrowHistory>) this.gd_borrow.ItemsSource;
if (tl == null || tl.Count <= 0)
{
return;
}
SaveFileDialog saveFileDialog = new SaveFileDialog();
BorrowOutToExcel uoe = new BorrowOutToExcel();
ExcelHelp _excelHelper = new ExcelHelp();
saveFileDialog.Filter = "Excel (*.xls)|*.XLS";
if ((bool)(saveFileDialog.ShowDialog()))
{
try
{
_excelHelper.SaveToExcel(saveFileDialog.FileName, uoe.ToTable(tl));
}
catch (Exception ex)
{
MessageBox.Show("导出失败：" + ex.Message);
return;
}
MessageBox.Show("导出成功");
}
}
private void infoout_Click(object sender, RoutedEventArgs e)
{
List <ToolModel> tl = (List <ToolModel>) this.dataGrid1.ItemsSource;
if (tl == null || tl.Count <= 0)
{
return;
}
WrenchExcelOut weo = new WrenchExcelOut();
SaveFileDialog saveFileDialog = new SaveFileDialog();
ExcelHelp _excelHelper = new ExcelHelp();
saveFileDialog.Filter = "Excel (*.XLS)|*.xls";
if ((bool)(saveFileDialog.ShowDialog()))
{
try
{
_excelHelper.SaveToExcel(saveFileDialog.FileName, weo.ToTable(tl));
MessageBox.Show("导出成功");
}
catch (Exception ex)
{
MessageBox.Show("导出失败！");
return;
}
}
}
public async Task <ResponseObject <string> > ExportBomTemplate()
{
try
{
CurrentUser currentUser = TokenManager.GetCurentUserbyToken(HttpContext.Request.Headers);
var result = await _service.ExportBomTemplate(359, currentUser);
//return result;
ExportTemplateVM <TMMBOMDetailQueryExcelModel> model = new ExportTemplateVM <TMMBOMDetailQueryExcelModel>();
model.Entitys = result.Data.TMMBOMDetailQueryExcelList == null ? new List <TMMBOMDetailQueryExcelModel>() : result.Data.TMMBOMDetailQueryExcelList;
model.Columns = new List <string>()
{
"ItemName", "MaterialName", "PartName", "LengthValue", "WidthValue", "NumValue", "WideValue", "LossValue", "SingleValue"
};
model.Titles = new List <string>()
{
"ItemName", "MaterialName", "PartName", "LengthValue", "WidthValue", "NumValue", "WideValue", "LossValue", "SingleValue"
};
model.HeaderText = "BOM模板";
model.TableName = "BOM模板";
model.FillRow = 6;
model.Path = Path.Combine(AppContext.BaseDirectory, "Template", "ExportBomTemplate.xlsx");
Stream stream = ExcelHelp.ExportExcelByTemplate <TMMBOMDetailQueryExcelModel>(model);
string fileName = "生产单模板-有配色方案.xlsx";
Dictionary <string, string> headsSend = new Dictionary <string, string>();
headsSend.Add(TokenConfig.Instace.TokenKey, Request.Headers[TokenConfig.Instace.TokenKey]);
var filePath = RestfulApiClient.UploadFile(RestfulApiClient.UplaodUrl, "api/files/upload", headsSend, stream, fileName);
return(ResponseUtil <string> .SuccessResult(filePath.ToString()));
}
catch (Exception ex)
{
return(ResponseUtil <string> .FailResult(null, ex.ToString()));
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.openFileDialog.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
int row = 3;
string tempFile = System.IO.Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase,
Definition.TemplateName);
using (ExcelHelp xls = new ExcelHelp(tempFile))
{
xls.BeginUpdate();
Excel.Worksheet sheet = xls.WorkBook.Sheets[Definition.SheetName];
string[] fileNames = this.openFileDialog.FileNames;
var assmFiles = fileNames.OrderBy(x => System.IO.Path.GetFileName(x));
foreach (string fileName in assmFiles)
{
xmldoc = GetXmlDocument(fileName);
textBox1.Text += fileName + "を抽出しています..." + System.Environment.NewLine;
Application.DoEvents();
row = ReflectAssembly(xls, sheet, fileName, row);
}
xls.EndUpdate();
if (this.saveFileDialog1.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
xls.Save(this.saveFileDialog1.FileName);
}
}
}
}
public SearchCheckViewModel()
{
_excelHelper = new ExcelHelp();
_dataGridSource = new DataView();
ImportCmd = new RelayCommand <object>(ImportCmdExcute, ImportCmdCanExcute);
ExportCmd = new RelayCommand <object>(ExportCmdExcute, ExportCmdCanExcute);
}
private void button2_Click(object sender, EventArgs e)
{
string strsql = "select top 100 WaterMeterAddress,RechargeCount,CreateUserName,CreateTime from [dbo].[MeiShan_Install_WaterUserMeter]";
var data = GetDataTable(strsql);
string path = @"D:\" + "欠费" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xlsx";
ExcelHelp.TableToExcelForXLSX(data, path);
}
private void exportDetail()
{
User user = (User)Session["user"];
int regionId = user.ReginId.RegionId;
string roleName = user.RoleId.RoleName;
string strWhere = "";
string groupbyType = Request["groupbyType"];
string supplier = Request["supplier"];
string regionName = Request["regionName"];
if (groupbyType == "state" || groupbyType == null)
{
groupbyType = "supplier";
}
if (supplier != "" && supplier != null)
{
strWhere = "supplier='" + supplier + "'";
}
if (regionName != "" && regionName != null)
{
strWhere = "regionName='" + regionName + "'";
}
if (roleName != "超级管理员")
{
if (strWhere == "" || strWhere == null)
{
strWhere = "regionId=" + regionId;
}
else
{
strWhere += "and regionId=" + regionId;
}
}
string str = "";
if (strWhere == "" || strWhere == null)
{
str = groupbyType + " like'%'";
}
else
{
str = strWhere;
}
string file = "书籍库存明细导出" + DateTime.Now.ToString("yyyyMMddHHmmss");
DataTable dt = StockBll.bookStockDe(str, groupbyType);
int count = dt.Rows.Count;
if (count > 0)
{
ExcelHelp.dtToExcelForXlsxByNpoi(dt, file);
}
else
{
Response.Write("<script language='javascript'>alert('查询不到数据，不能执行导出操作!')</script>");
}
}
private DataTable npoi()
{
string path = Session["path"].ToString();
DataTable dtNpoi = new DataTable();
dtNpoi = ExcelHelp.excelToDt(path, "excel");
dtNpoi.Columns.Add("id").SetOrdinal(0);
row = dtNpoi.Rows.Count;
return(dtNpoi);
}
/// <summary>
///上传时候保存Excel
/// </summary>
/// <param name="files"></param>
/// <returns></returns>
public string Save(string[] files)
{
if (files == null || files.Length == 0)
{
return("未选择文件！");
}
string billCodes = "";
//try
//{
foreach (var item in files)
{
DataTable dt = ExcelHelp.ExcelToDT(item);
string typeTemp = dt.Rows[0][0].ToString(); //首行首列
string ColumnName = dt.Columns[0].ColumnName; //第一个列名
//如果是批量导入交货单
if (ColumnName == "交货")
{
billCodes = AddManyGiveBills(dt);
}
else
{
//单张单据
dt.Rows.RemoveAt(0); //删除标题行
switch (typeTemp)
{
case "转仓单号":
AddTransferBill(dt);
break;
case "退仓单号":
AddBackInput(dt);
break;
case "交货单号":
AddGiveBill(dt);
break;
case "退货单号":
AddBackOutput(dt);
break;
}
}
//删除文件
File.Delete(item);
}
return(billCodes == "" ? "导入成功" : "导入异常：" + billCodes);
//}
//catch(Exception e)
//{
// return e.ToString();
//}
}
/// <summary>
/// 画面初期化する
/// </summary>
private void InitControls()
{
try
{
this.Cursor = Cursors.WaitCursor;
//テーブル一覧表示
this.cmbTableSheets.Items.Clear();
using (ExcelHelp xls = new ExcelHelp(Config.DbTableListSettingFile))
{
foreach (Excel.Worksheet sheet in xls.WorkBook.Sheets)
{
if (sheet.Visible == Excel.XlSheetVisibility.xlSheetVisible)
{
this.cmbTableSheets.Items.Add(sheet.Name);
}
}
}
if (this.cmbTableSheets.Items.Count > 0)
{
this.cmbTableSheets.SelectedIndex = 0;
}
//設計書種別
List <BindListItem> layoutTypes = new List <BindListItem>();
layoutTypes.Add(new BindListItem("データベース設計書(LIVE用)", LayoutType.Live));
layoutTypes.Add(new BindListItem("データベース設計書(SEED用)", LayoutType.Seed));
this.cmbLayoutType.Items.Clear();
this.cmbLayoutType.DisplayMember = "DisplayName";
this.cmbLayoutType.ValueMember = "Value";
foreach (BindListItem item in layoutTypes)
{
this.cmbLayoutType.Items.Add(item);
}
if (this.cmbLayoutType.Items.Count > 0)
{
this.cmbLayoutType.SelectedIndex = 0;
}
//オプションズ
List <BindListItem> options = new List <BindListItem>();
options.Add(new BindListItem("テーブルの削除スクリプトを生成する", ScriptOptions.DropTables));
options.Add(new BindListItem("コメントの削除スクリプトを生成する", ScriptOptions.DropDropDescriptions));
options.Add(new BindListItem("テーブルの作成スクリプトを生成する", ScriptOptions.CreateTables));
options.Add(new BindListItem("コメントの作成スクリプトを生成する", ScriptOptions.CreateDropDescriptions));
this.chkOptions.Items.Clear();
foreach (BindListItem item in options)
{
this.chkOptions.Items.Add(item, true);
}
}
finally
{
this.Cursor = Cursors.Default;
}
}
/// <summary>
/// Propertyを出力する
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet"></param>
/// <param name="type"></param>
/// <param name="row"></param>
/// <returns></returns>
private int WriteProperties(ExcelHelp xls, Excel.Worksheet sheet, Type type, int row)
{
bool isWriteAttribute = false;
string tableName = "";
if (type.Namespace.Equals("DenshowDataAccessInterface.Dto") ||
type.Namespace.Equals("DenshowDataAccessInterface.Entity"))
{
isWriteAttribute = true;
tableName = getAttribute(type.GetTypeInfo());
}
var props = type.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static)
.Where(x => x.DeclaringType == type)
.OrderBy(x => x.Name);
foreach (var prop in props)
{
if (prop.IsSpecialName)
{
string name = prop.Name;
}
string attr = getAttribute(prop);
WriteClassHeader(xls, sheet, type, row, false);
//TypeKind
xls.WriteValue(sheet, Definition.TypeKind, row, GetMemberType(prop.MemberType));
//TypeName
xls.WriteValue(sheet, Definition.TypeName, row, getTypeName(prop.PropertyType));
//BingAttibute
xls.WriteValue(sheet, Definition.BingAttibute, row, prop.CanRead ? "+" : "-");
//Name
xls.WriteValue(sheet, Definition.MemberName, row, prop.Name);
//Paramter
xls.WriteValue(sheet, Definition.MethodParams, row, "-");
//summary
string summary = GetSummary(prop);
if (!string.IsNullOrEmpty(summary))
{
xls.WriteValue(sheet, Definition.MemberSummary, row, summary);
}
//TableName
if (isWriteAttribute)
{
xls.WriteValue(sheet, Definition.TableName, row, tableName);
xls.WriteValue(sheet, Definition.ColumName, row, getAttribute(prop));
}
row++;
}
return(row);
}
//Classを書き出す
private void WriteClassHeader(ExcelHelp xls, Excel.Worksheet sheet, Type type, int row, bool isHeader)
{
//NameSpace
xls.WriteValue(sheet, Definition.NameSpace, row, type.Namespace);
//className
xls.WriteValue(sheet, Definition.ClassName, row, type.Name);
//baseclass
string buff = "";
if (type.BaseType != null && type.BaseType != typeof(object))
{
buff += type.BaseType.Name;
}
else
{
buff = GetInterfaces(type);
}
xls.WriteValue(sheet, Definition.BaseClass, row, string.IsNullOrEmpty(buff) ? "-" : buff);
if (isHeader)
{
//Field Count
int fldCount = type.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static)
.Where(x => x.DeclaringType == type && !x.Name.Contains("__")).Count();
fldCount += type.GetProperties(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static)
.Where(x => x.DeclaringType == type).Count();
xls.WriteValue(sheet, Definition.FieldCount, row, fldCount.ToString());
//Method Count
var Methods = type.GetMethods(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static)
.Where(x => x.DeclaringType == type && (!x.IsSpecialName) && (!x.Name.Contains("__")) && (!x.Name.Equals("InitializeComponent")));
xls.WriteValue(sheet, Definition.MethodCount, row, Methods.Count().ToString());
//TypeKind
xls.WriteValue(sheet, Definition.TypeKind, row, GetMemberType(type.MemberType));
//公開／非公開
xls.WriteValue(sheet, Definition.BingAttibute, row, "-");
//MemberName
xls.WriteValue(sheet, Definition.MemberName, row, "-");
//MethodParams
xls.WriteValue(sheet, Definition.MethodParams, row, "-");
//戻り値タイプ
xls.WriteValue(sheet, Definition.TypeName, row, "-");
//summary
string summary = GetSummary(type.GetTypeInfo());
if (!string.IsNullOrEmpty(summary))
{
xls.WriteValue(sheet, Definition.ClassSummary, row, summary);
}
}
WriteLayerName(xls, sheet, type, row);
}
private int ReflectAssembly(ExcelHelp xls, Excel.Worksheet sheet, string filePath, int row)
{
var asm = Assembly.LoadFrom(filePath);
var types = asm.GetTypes().OrderBy(x => x.Namespace + "." + x.Name);
foreach (Type type in types)
{
if (!type.IsNested && !type.IsAutoClass && !type.IsInterface && !string.IsNullOrEmpty(type.Namespace))
{
row = WriteTypeInfo(xls, sheet, type, row);
}
}
return(row);
}
/// <summary>
/// 导出交货单
/// </summary>
/// <param name="billId"></param>
/// <returns></returns>
private MemoryStream ExportGiveBill(Guid billId, string TemplateUrl)
{
GiveBill bill = CurrentDBSession.GiveBillDal.LoadEntities(a => a.Id == billId).FirstOrDefault();
if (bill == null)
{
return(null);
}
//根据模板创建Datatable
DataTable dt = ExcelHelp.ExcelToDT(TemplateUrl);
foreach (var record in bill.Record)
{
DataRow dr = dt.NewRow();
dr["LBBillCode"] = bill.LBBillCode;
dr["LBBillDate"] = bill.LBBillDate;
dr["LBTaskBillCode"] = bill.LBTaskBillCode;
dr["LBLine"] = bill.LBLine;
dr["LBContacts"] = bill.LBContacts;
dr["LBPhone"] = bill.LBPhone;
dr["LBMailCode"] = bill.LBMailCode;
dr["LBCustomerCode"] = bill.LBCustomerCode;
dr["LBCustomerName"] = bill.LBCustomerName;
dr["LBSendAddress"] = bill.LBSendAddress;
dr["LBRemark"] = bill.LBRemark;
dr["CreateDate"] = bill.CreateDate;
dr["ChargePerson"] = bill.ChargePerson;
dr["MakePerson"] = bill.MakePerson;
dr["Warehouse"] = bill.Warehouse;
dr["WarehouseId"] = bill.WarehouseId;
dr["LoadGoodsType"] = bill.LoadGoodsType;
dr["OutputType"] = bill.OutputType;
dr["OutputTypeId"] = bill.OutputTypeId;
dr["BusinessType"] = bill.BusinessType;
dr["LineWay"] = bill.LineWay;
dr["Remark"] = bill.Remark;
dr["Department"] = bill.Department;
dr["DepartmentId"] = bill.DepartmentId;
dr["Company"] = bill.Company;
dr["CompanyId"] = bill.CompanyId;
//子表
dr["ItemCode"] = record.ItemCode;
dr["ItemLocationId"] = record.ItemLocationId;
dr["ItemBatch"] = record.ItemBatch;
dr["Count"] = record.Count;
dt.Rows.Add(dr);
}
return(NPIOHelper.RenderToMemory(dt, "sheet1"));
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T">集合列表里对象的类型</typeparam>
/// <param name="list">数据列表</param>
/// <returns></returns>
protected FileResult ToExcel(DataTable list)
{
#region 获取XML
XMLHelp xmlhelp = new Common.XMLHelp(string.Format("/XmlConfig/excel/{0}-{1}.xml", ControllerContext.RouteData.DataTokens["area"].ToString(), ControllerContext.RouteData.Values["controller"].ToString()));
var fields = xmlhelp.GetList <Xml_Field>().Select(a => new KeyValuePair <string, string>(a.code, a.name)).ToList();
#endregion
var dirPath = Server.MapPath("/upload/toexcel/");
if (System.IO.Directory.Exists(dirPath) == false)
{
System.IO.Directory.CreateDirectory(dirPath);
}
var filePath = string.Format("{0}{1}.xls", dirPath, System.Guid.NewGuid().ToString());
ExcelHelp.ToExcelByNPOI(list, filePath, fields);
return(File(filePath, "application/vnd.ms-excel"));
}
/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T">集合列表里对象的类型</typeparam>
/// <param name="list">数据列表</param>
/// <returns></returns>
protected FileResult ToExcel(DataTable list, string xmlFileName)
{
#region 获取XML
XMLHelp xmlhelp = new XMLHelp(string.Format("/XmlConfig/excel/{0}.xml", xmlFileName));
var fields = xmlhelp.GetList <Xml_Field>().Select(a => new KeyValuePair <string, string>(a.code, a.name)).ToList();
#endregion
var dirPath = Server.MapPath("/upload/toexcel/");
if (System.IO.Directory.Exists(dirPath) == false)
{
System.IO.Directory.CreateDirectory(dirPath);
}
var filePath = string.Format("{0}{1}.xls", dirPath, WebTools.getGUID());
ExcelHelp.ToExcelByNPOI(list, filePath, fields);
return(File(filePath, "application/vnd.ms-excel"));
}
public void DaoIn()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
ExcelHelp _excelHelper = new ExcelHelp();
openFileDialog.Filter = "Excel (*.XLS)|*.xls";
int i = 0, j = 0;
if ((bool)(openFileDialog.ShowDialog()))
{
try
{
DataTable dt = _excelHelper.LoadExcel(openFileDialog.FileName);
List <wrench> s = ListWrench(dt);
if (s != null && s.Count > 0)
{
foreach (wrench w in s)
{
try
{
if (Wrench.add(w))
{
i++;
}
else
{
j++;
LogUtil.WriteLog(typeof(string), "序号为 " + w.id.ToString() + "到入失败：");
}
}
catch
{
j++;
LogUtil.WriteLog(typeof(wrench), w.wrenchBarCode + "导入失败！");
}
}
MessageBox.Show("成功导入" + i.ToString() + "条" + " 失败" + j.ToString() + "条");
}
// Wrench.addlist(ListWrench (dt));
}
catch (Exception ex)
{
LogUtil.WriteLog(typeof(string), "工具批量导入失败：" + ex);
MessageBox.Show("批量导入工具信息失败,填写信息有误");
return;
}
}
}
/// <summary>
/// メソッドを出力する
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet"></param>
/// <param name="type"></param>
/// <param name="row"></param>
/// <returns></returns>
private int WriteMethods(ExcelHelp xls, Excel.Worksheet sheet, Type type, int row)
{
var methods = type.GetMethods(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static)
.Where(x => x.DeclaringType == type && (!x.IsSpecialName) && (!x.Name.Contains("__")) && (!x.Name.Equals("InitializeComponent")))
.OrderBy(x => x.Name);
foreach (var method in methods)
{
if (method.IsSpecialName || method.IsPrivate)
{
string name = method.Name;
}
WriteClassHeader(xls, sheet, type, row, false);
//TypeKind
xls.WriteValue(sheet, Definition.TypeKind, row, GetMemberType(method.MemberType));
//TypeName
xls.WriteValue(sheet, Definition.TypeName, row, getTypeName(method.ReturnType));
//BingAttibute
xls.WriteValue(sheet, Definition.BingAttibute, row, method.IsPublic ? "+" : "-");
//Name
xls.WriteValue(sheet, Definition.MemberName, row, method.Name);
var pms = method.GetParameters();
string buff = "";
foreach (var param in pms)
{
if (!string.IsNullOrEmpty(buff))
{
buff += ", ";
}
buff += string.Format("{0} : {1}", param.Name, getTypeName(param.ParameterType));
}
//Paramter
xls.WriteValue(sheet, Definition.MethodParams, row, string.IsNullOrEmpty(buff) ? "-" : buff);
//Static
if (method.IsStatic)
{
xls.WriteValue(sheet, Definition.Memo, row, "static");
}
//summary
string summary = GetSummary(method);
if (!string.IsNullOrEmpty(summary))
{
xls.WriteValue(sheet, Definition.MemberSummary, row, summary);
}
row++;
}
return(row);
}
public string ExportExcel()
{
try {
string allSql = getAllSql(SystemSet.EumReportType.ZRS.ToString(), (Hashtable)Session["ZRS_HtStrWhere"]);
if (!string.IsNullOrWhiteSpace(allSql))
{
return(ExcelHelp.DataTableToExcel(new T_Other_BLL().GetQueryList(allSql)));
}
else
{
return(SystemSet._RETURN_FAILURE_VALUE + "KEY=ZrsCertificateSql 获取脚本失败");
}
} catch (Exception ex) {
return(SystemSet._RETURN_FAILURE_VALUE + ex.Message);
}
}
public string ExportEfileConvertLogToExcel()
{
try {
string allSql = PublicModel.getExportExcelSql("EfileConvertSql");
if (!string.IsNullOrWhiteSpace(allSql))
{
return(ExcelHelp.DataTableToExcel(new T_Other_BLL().GetQueryList(string.Format(allSql, Common.Session.GetSession("EfileConvertsqlWhere")))));
}
else
{
return(SystemSet._RETURN_FAILURE_VALUE + "KEY=EfileConvertSql 获取脚本失败");
}
} catch (Exception ex) {
return(SystemSet._RETURN_FAILURE_VALUE + ex.Message);
}
}
protected void btnExcel_Click(object sender, EventArgs e)
{
var excelList = new List <Model.OrderXls>();
foreach (var i in listAll)
{
excelList.Add(new Model.OrderXls
{
ProductName = i.ProductName,
ProductID = i.ProductID,
CreateTime = i.CreateTime,
SettlementTime = i.SettlementTime,
PayMoney = i.PayMoney,
OrderStatus = i.OrderStatus,
CommissionRate = i.CommissionRate,
Effect = i.Effect,
lijin = i.lijin,
realshouru = i.realshouru,
AppName = new TljEntities().appkeys.Where(m => m.AdzoneId == i.AdID).FirstOrDefault() != null?new TljEntities().appkeys.Where(m => m.AdzoneId == i.AdID).FirstOrDefault().AppName:""
});
}
DateTime dtStart = DateTime.Parse(txtDateStart.Text);
DateTime dtEnd = DateTime.Parse(txtDateEnd.Text);
string fileName = string.Format("{0}-{1}_{2}_{3}_{4}.xls",
dtStart.ToString("yyyy-MM-dd"),
dtEnd.ToString("yyyy-MM-dd"),
string.IsNullOrEmpty(ddlAccount.SelectedValue) ? "全部帐号" : ddlAccount.SelectedValue,
string.IsNullOrEmpty(ddlAccount.SelectedValue) ? "全部推广位" : ddlAppKeyID.SelectedItem.Text,
DateTime.Now.ToString("yyyy-MM-dd"));
ExcelHelp <Model.OrderXls> excelH = new ExcelHelp <Model.OrderXls>();
Hashtable ht = new Hashtable();
ht.Add("ProductName", "商品名称");
ht.Add("ProductID", "商品ID");
ht.Add("CreateTime", "创建时间");
ht.Add("SettlementTime", "结算时间");
ht.Add("PayMoney", "付款金额");
ht.Add("OrderStatus", "订单状态");
ht.Add("CommissionRate", "提成比例");
ht.Add("Effect", "预估佣金");
ht.Add("lijin", "礼金");
ht.Add("realshouru", "实际收益");
ht.Add("AppName", "推广位");
excelH.getExcel(excelList, ht, fileName);
}
public async Task <ResponseObject <string> > ExportStaReport(string requestObject)
{
try
{
var request = JsonConvert.DeserializeObject <RequestGet>(requestObject);
CurrentUser currentUser = TokenManager.GetCurentUserbyToken(HttpContext.Request.Headers);
var result = await _inventoryReportService.LoadReport(request, currentUser);
ExportTemplateVM <InventoryResultModel> model = new ExportTemplateVM <InventoryResultModel>();
model.Entitys = result.Data == null ? new List <InventoryResultModel>() : result.Data;
model.Columns = new List <string>()
{
"MaterialCode", "MaterialName", "Spec", "ColorName", "BatchNo",
"WarehouseCode", "WarehouseName", "BaseUnitName", "BaseUnitNumber", "WarehouseUnitName", "WarehouseAmount", "WarehouseRate", "InDate",
"ShelfLife", "ValidDate", "Remark",
};
model.Titles = new List <string>()
{
"MaterialCode", "MaterialName", "Spec", "ColorName", "BatchNo",
"WarehouseCode", "WarehouseName", "BaseUnitName", "BaseUnitNumber", "WarehouseUnitName", "WarehouseAmount", "WarehouseRate", "InDate",
"ShelfLife", "ValidDate", "Remark",
};
model.HeaderText = "库存统计";
model.TableName = "库存统计";
model.FillRow = 3;
model.Path = Path.Combine(AppContext.BaseDirectory, "Template", "InventoryReport.xlsx");
Stream stream = ExcelHelp.ExportExcelByTemplate <InventoryResultModel>(model);
string fileName = "库存统计.xlsx";
Dictionary <string, string> headsSend = new Dictionary <string, string>();
headsSend.Add(TokenConfig.Instace.TokenKey, Request.Headers[TokenConfig.Instace.TokenKey]);
var filePath = RestfulApiClient.UploadFile(RestfulApiClient.UplaodUrl, "api/files/upload", headsSend, stream, fileName);
return(ResponseUtil <string> .SuccessResult(filePath.ToString()));
}
catch (Exception ex)
{
return(ResponseUtil <string> .FailResult(null, ex.ToString()));
}
}
/// <summary>
/// コンストラクタを出力する
/// </summary>
/// <param name="xls"></param>
/// <param name="sheet"></param>
/// <param name="type"></param>
/// <param name="row"></param>
/// <returns></returns>
private int WriteConstructor(ExcelHelp xls, Excel.Worksheet sheet, Type type, int row)
{
var ctors = type.GetConstructors();
foreach (var ctor in ctors)
{
WriteClassHeader(xls, sheet, type, row, false);
//TypeKind
xls.WriteValue(sheet, Definition.TypeKind, row, GetMemberType(ctor.MemberType));
//TypeName
xls.WriteValue(sheet, Definition.TypeName, row, "-");
//BingAttibute
xls.WriteValue(sheet, Definition.BingAttibute, row, ctor.IsPublic ? "+" : "-");
//Name
xls.WriteValue(sheet, Definition.MemberName, row, type.Name);
var pms = ctor.GetParameters();
string buff = "";
foreach (var param in pms)
{
if (!string.IsNullOrEmpty(buff))
{
buff += ", ";
}
buff += string.Format("{0} : {1}", param.Name, getTypeName(param.ParameterType));
}
//Paramter
xls.WriteValue(sheet, Definition.MethodParams, row, string.IsNullOrEmpty(buff) ? "-" : buff);
//Static
if (ctor.IsStatic)
{
xls.WriteValue(sheet, Definition.Memo, row, "static");
}
//summary
string summary = GetSummary(ctor);
if (!string.IsNullOrEmpty(summary))
{
xls.WriteValue(sheet, Definition.MemberSummary, row, summary);
}
else
{
xls.WriteValue(sheet, Definition.MemberSummary, row, "コンストラクタ");
}
row++;
}
return(row);
}
private void thêmDanhSáchToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog op = new OpenFileDialog();
op.Filter = "xls, xlsx|*.xls;*.xlsx";
try
{
if (op.ShowDialog() == DialogResult.OK)
{
//load info be so 1
DataTable table = ExcelHelp.getDataTableExcel(op.FileName);//new DataTable();
//khoi tao tinh tong 3 bia
tong3Bia = new int[table.Rows.Count];
/*
* table.Columns.Add("Tên", typeof(string));
* table.Columns.Add("Lần 1", typeof(int));
* table.Columns.Add("Lần 2", typeof(int));
* table.Columns.Add("Lần 3", typeof(int));
* table.Columns.Add("Tổng", typeof(string));
* table.Rows.Add("Nguyễn Văn A", null, null, null, null);
* table.Rows.Add("Nguyễn Văn b", null, null, null, null);
* table.Rows.Add("Nguyễn Văn c", null, null, null, null);
*/
_dtgScore.DataSource = table;
//mo rong phan ten
_dtgScore.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
_lblName.Text = table.Rows[0][0].ToString();
//load info be so 2
DataTable table2 = ExcelHelp.getDataTableExcel(op.FileName);//new DataTable();
_dtgScore2.DataSource = table2;
_dtgScore2.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
_lblName2.Text = table.Rows[0][0].ToString();
//load info be so 3
DataTable table3 = ExcelHelp.getDataTableExcel(op.FileName);//new DataTable();
_dtgScore3.DataSource = table3;
_dtgScore3.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells;
_lblName3.Text = table.Rows[0][0].ToString();
}
}
catch (Exception ex)
{ }
}
/// <summary>
/// 判断重复数据
/// </summary>
public void check()
{
string path = Session["path"].ToString();
DataTable dt = ExcelHelp.excelToDt(path, "excel");
string[] huojiaId = { "货架编号" };
if (DataTableHelper.isRepeatDt(dt, huojiaId))
{
Response.Write("重复数据");
Response.End();
}
else
{
Response.Write("上传成功");
Response.End();
}
}
void Export(bool p_IsTemplate)
{
try
{
DataTable dt = AssetInfo.getExAssetList();
DataTable dtTemp = dt.Copy();
if (dt.Rows.Count > 0)
{
//��ȡ·��
string strPath = AppDomain.CurrentDomain.BaseDirectory.Substring(0, AppDomain.CurrentDomain.BaseDirectory.Length - 1);
strPath = strPath.Replace("/", "\\");
DateTime time1 = DateTime.Now;
string temlpate = strPath + "\\template\\������������.xls";
strPath = strPath + "\\excel";
List<ExcelColumInfo> ExcelColumns = new List<ExcelColumInfo>();
foreach (DataColumn dc in dt.Columns)
{
ExcelColumInfo colinfo = new ExcelColumInfo();
colinfo.HeadName = dc.ColumnName;
colinfo.TableColumnName = dc.ColumnName;
colinfo.Width = 20;
colinfo.DataType = DataType.Text;
if (dc.ColumnName == "MCDate" || dc.ColumnName == "WriteDate")
colinfo.DataType = DataType.DateTime;//ShareddedRate
else if (dc.ColumnName == "Processes" || dc.ColumnName == "Reject" || dc.ColumnName == "RejectRate" || dc.ColumnName == "Sharedded" || dc.ColumnName == "ShareddedRate" || dc.ColumnName == "RejShareddedRateect" || dc.ColumnName == "RunHour")
colinfo.DataType = DataType.Number;
ExcelColumns.Add(colinfo);
}
ExcelHelp eh = new ExcelHelp();
byte[] byteResult;
//�������ݵ�byte�ֽ�����
if (!p_IsTemplate)
byteResult = eh.ExportData(dt, ExcelColumns, "�칫��Ʒ����", strPath, 5000);
else
byteResult = eh.ExportData(dt, ExcelColumns, "�칫��Ʒ����", strPath, 5000, temlpate);
DateTime time2 = DateTime.Now;
TimeSpan ts1 = new TimeSpan(time1.Ticks);
TimeSpan ts2 = new TimeSpan(time2.Ticks);
TimeSpan ts = ts1.Subtract(ts2).Duration();
string dateDiff = "����1�����ݱ����" + dt.Rows.Count.ToString() + "����¼����ʱ" + ts.Days.ToString() + "��"
+ ts.Hours.ToString() + "Сʱ"
+ ts.Minutes.ToString() + "����"
+ ts.Seconds.ToString() + "��";
//lblOledb.Text = dateDiff;
//�ͻ��������ļ�
Response.Clear();
//Response.Buffer = true;
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// ���ͷ��Ϣ��Ϊ"�ļ�����/���Ϊ"�Ի���ָ��Ĭ���ļ���
Response.AddHeader("Content-Disposition", "attachment; filename = " + Server.UrlEncode("�칫��Ʒ����.xls"));
// ���ͷ��Ϣ��ָ���ļ���С����������ܹ���ʾ���ؽ���
Response.AddHeader("Content-Length", byteResult.Length.ToString());
// ָ�����ص���һ�����ܱ��ͻ��˶�ȡ���������뱻����
Response.ContentType = "application/ms-excel";
// ���ļ������͵��ͻ���
//Response.WriteFile(file.FullName);
Response.BinaryWrite(byteResult);
Response.Flush();
// ֹͣҳ���ִ��
//GC.Collect();
//Response.End();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
else
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script language='javascript'>alert('����Ϊ�յ���ʧ�ܣ�');</script>");
}
}
catch (Exception Ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "Save", "alert('��������ʧ�ܣ�" + Ex.Message + "');", true);
}
}
