使用EPPlus实现Excel导出功能

白茶一清欢

于 2024-05-16 10:31:43 发布

阅读量360

点赞数 3

分类专栏：程序员文章标签： excel

本文链接：https://blog.csdn.net/dgayuiafahf/article/details/138950832

版权

程序员专栏收录该内容

179 篇文章 0 订阅

订阅专栏

cache: false,

async: false,

dataType: “json”,

success: function (result) {

if (result.url == “”) {

alert(“导出失败”);

}

else {

window.location.href = result.url;

}

});

}

后端代码

using OfficeOpenXml;

using OfficeOpenXml.Style;

using System.Drawing;

//Excel导出

public ActionResult Export(string CUSTOMERNAME, string CODE, string FACTORYNAME, string CREATEDON, string CREATEDON1, string STATE, bootstrapTableParam bootstrapTableParam)

{

using (MiniPoco.Database db = new MiniPoco.Database(“PDDA”))

{

try

{

string sqlWhere = " where 1 = 1 ";

if (!string.IsNullOrEmpty(CODE))

{

sqlWhere += string.Format(@" and A.CODE like ‘%{0}%’ ", CODE);

}

if (!string.IsNullOrEmpty(CUSTOMERNAME))

{

sqlWhere += string.Format(@" and A.CUSTOMERNAME like ‘%{0}%’ ", CUSTOMERNAME);

}

if (!string.IsNullOrEmpty(FACTORYNAME))

{

sqlWhere += string.Format(@" and A.FACTORYNAME = ‘{0}’ ", FACTORYNAME);

}

if (!string.IsNullOrEmpty(CREATEDON))

{

sqlWhere += string.Format(@" and to_date(to_char(A.CREATEDON,‘yyyy-MM-dd’),‘yyyy-MM-dd’) >= to_date(‘{0}’,‘yyyy-MM-dd’)", CREATEDON);

}

if (!string.IsNullOrEmpty(CREATEDON1))

{

sqlWhere += string.Format(@" and to_date(to_char(A.CREATEDON,‘yyyy-MM-dd’),‘yyyy-MM-dd’) <= to_date(‘{0}’,‘yyyy-MM-dd’)", CREATEDON1);

}

if (!string.IsNullOrEmpty(STATE))

{

sqlWhere += string.Format(@" and A.STATE =‘{0}’ ", STATE);

}

//加载要导出的数据

List ListData = new List();

string sql = string.Format(@“select A.* from YH_SPEED_APPLY A {0} order by A.CREATEDON desc”, sqlWhere);

ListData = db.GetHashtable(sql);

//设置要导出的位置

string fileName = string.Format(“YH_SPEED_APPLY_{0}.xlsx”, DateTime.Now.ToString(“yyyyMMddHHmmss”));

string serverPath = Server.MapPath(“~/Upload/DownloadExcal/YH_SPEED_APPLY/”);

if (!Directory.Exists(serverPath))

{

Directory.CreateDirectory(serverPath);

}

string fileUrl = Path.Combine(serverPath, fileName);

FileInfo info = new FileInfo(fileUrl);

if (info.Exists)

{

info.Delete();

info = new FileInfo(fileUrl);

}

using (ExcelPackage package = new ExcelPackage(info))

{

ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(“印花车速申请”);

ListToExcel(worksheet, ListData);

package.Save();

}

string path = “/Upload/DownloadExcal/YH_SPEED_APPLY/” + fileName;

Hashtable result = new Hashtable();

result[“url”] = path;

return Json(result);

}

catch (Exception ex)

{

return Content(ex.Message);

}

//列表转为Excel

public void ListToExcel(ExcelWorksheet excelWorksheet, List list)

{

//定义Excel行数

int lineNumber = 1, columnNumber = 1;

//绘制Excel总标题

excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Value = “印花车速申请”;

excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Style.Font.SetFromFont(new Font(“宋体”, 20f, FontStyle.Regular));

excelWorksheet.Cells[lineNumber, 1, lineNumber, 14].Merge = true;

//绘制Excel列表标题

lineNumber++; columnNumber = 0;

excelWorksheet.Cells[lineNumber, ++columnNumber].Value = “申请分厂”;