网站-管理NuGet程序包-搜索‘NPOI’点击安装
ExcelHelpers 导入
protected void Button2_Click(object sender, EventArgs e)
{
bool b = Uploads(FileUpload1); // 上传excel文件
if (!b)
{
return;
}
try
{
string name = FileUpload1.FileName;
string filepath = Server.MapPath("Upload/") + name;
DataTable dt = new DataTable();
dt = ExcelHelpers.Import(filepath);
foreach (DataRow row in dt.Rows)
{
bll_About.Insert_About("[guid],[title],[shortcontent],[content],[language],[hot],[grade],[img1],[img1a],[recommend],[state],[typeid],[clickcount],[author],[source],[addTime],[field1],[field2],[field3],[field4],[field5]", "'','" + row[0].ToString() + "','','','china',0,1,'','',0,0,1,0,'','','" + DateTime.Now + "','" + row[1].ToString() + "','','','',''");
}
Jscript.AlertAndRedirect("提示：操作成功！", PageUrl);
}
catch (Exception)
{
Jscript.AlertAndRedirect("提示：操作失败！", PageUrl);
}
}
private bool Uploads(FileUpload myFileUpload)
{
bool flag = false;
//是否允许上载
bool fileAllow = false;
//设定允许上载的扩展文件名类型
string[] allowExtensions = { ".xls", ".xlsx" };
//取得网站根目录路径
string path = HttpContext.Current.Request.MapPath("Upload/");
if (myFileUpload.HasFile)
{
string fileExtension = System.IO.Path.GetExtension(myFileUpload.FileName).ToLower();
for (int i = 0; i < allowExtensions.Length; i++)
{
if (fileExtension == allowExtensions[i])
{
fileAllow = true;
}
}
if (fileAllow)
{
try
{
//存储文件到文件夹
myFileUpload.SaveAs(path + myFileUpload.FileName);
//lblMes.Text = "文件导入成功";
flag = true;
}
catch (Exception ex)
{
// lblMes.Text += ex.Message;
flag = false;
}
}
else
{
// lblMes.Text = "不允许上载：" + myFileUpload.PostedFile.FileName + "，只能上传xls的文件，请检查！";
flag = false;
}
}
else
{
// lblMes.Text = "请选择要导入的excel文件!";
flag = false;
}
return flag;
}
ExcelHelpers 导出
private void Bind()
{
List<Model.Di> list = bll_Di.Query_Di(" * ", SqlWhere, "");
DataTable dt = new DataTable();
dt.Columns.Add("序号");
dt.Columns.Add("订单号");
dt.Columns.Add("微信openid");
dt.Columns.Add("支付方式");
dt.Columns.Add("付款状态");
dt.Columns.Add("收货人");
dt.Columns.Add("送货地址");
dt.Columns.Add("联系电话");
dt.Columns.Add("发票类型");
dt.Columns.Add("发票抬头");
dt.Columns.Add("发票内容");
dt.Columns.Add("运单信息");
dt.Columns.Add("支付返回流水号");
dt.Columns.Add("付款时间");
dt.Columns.Add("人民币金额（实收）");
dt.Columns.Add("备注");
foreach (var item in list)
{
DataRow dr_1 = dt.NewRow();
dr_1["序号"] = item.id;
dr_1["订单号"] = item.title;
dr_1["微信openid"] = item.shortcontent;
dr_1["支付方式"] = item.hot;
dr_1["付款状态"] = item.recommend;
dr_1["收货人"] = item.field1;
dr_1["送货地址"] = item.field2;
dr_1["联系电话"] = item.field3;
dr_1["发票类型"] = item.field4;
dr_1["发票抬头"] = item.field5;
dr_1["发票内容"] = item.field6;
dr_1["运单信息"] = item.field7;
dr_1["支付返回流水号"] = item.field8;
dr_1["付款时间"] = item.field9;
dr_1["人民币金额（实收）"] = item.field12;
dr_1["备注"] = item.content;
dt.Rows.Add(dr_1);
}
string excelName = "订单" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//第二种导出方法需要".xlsx"格式
string title = DateTime.Now.ToString("yyyy-MM-dd") + "订单";
try
{
ExcelHelpers.ExportByWeb(dt, title, excelName);
// ExcelHelpers.DataTableToExcelAndDownload(dt, excelName, System.Web.HttpContext.Current.Server.MapPath("../Upload/Excel.xlsx"));
}
catch (Exception ex)
{
}
}