使用EPPlus实现Excel导出功能

前端代码

<div style="float: left; margin-top: 10px; margin-right: 30px; ">
    <button class="btn btn-primary" type="button" onclick="Export()">
        导出
    </button>
</div>
<script type="text/javascript">
function Export() {
        var data = {};
        data["CODE"] = $("#CODE").val();
        data["FACTORYNAME"] = $("#FACTORYNAME").val();
        data["CREATEDON"] = $("#CREATEDON").val();
        data["CREATEDON1"] = $("#CREATEDON1").val();
        data["STATE"] = $("#STATE").val();

        $.ajax({
            url: "/PersonnelModule/YH_SPEED_APPLY/Export?r=" + Math.round(Math.random() * 10000),
            data: data,
            type: "POST",
            cache: false,
            async: false,
            dataType: "json",
            success: function (result) {
                if (result.url == "") {
                    alert("导出失败");
                }
                else {
                    window.location.href = result.url;
                }
            }
        });
    }
</script>      

后端代码

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<Hashtable> ListData = new List<Hashtable>();
            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<Hashtable> 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 = "申请分厂";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申请日期";
    excelWorksheet.Column(columnNumber).Width = 20.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申请人";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申请类型";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申请机台";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "状态";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "客户名称";
    excelWorksheet.Column(columnNumber).Width = 25.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "色号";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "品名";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "米数";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "申请车速";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "磁棒";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "车速申请的原因";
    excelWorksheet.Column(columnNumber).Width = 25.0;
    excelWorksheet.Cells[lineNumber, ++columnNumber].Value = "其他备注";
    excelWorksheet.Column(columnNumber).Width = 15.0;
    excelWorksheet.Cells[lineNumber, 1, lineNumber, columnNumber].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    //绘制Excel列表
    lineNumber++; 
    if (list.Count > 0)
    {
        foreach (var items in list)
        {
            columnNumber = 0;
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["FACTORYNAME"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CREATEDON"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CREATEDNAME"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["TYPE"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["MACHINE"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["STATE"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["CUSTOMERNAME"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["COLORCODE"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["PRODUCTNAME"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["METERS"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["SPEED"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["MAGNETICBAR"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["REASON"].ToString());
            DrawExcelListCell(excelWorksheet, lineNumber, ++columnNumber, items["REMARK"].ToString());

            excelWorksheet.Row(lineNumber).Height = 20.0;
            lineNumber++;
        }
    }
}
//绘制列表单元格
public void DrawExcelListCell(ExcelWorksheet excelWorksheet, int lineNumber, int columnNumber, string value)
{
    using (ExcelRange range = excelWorksheet.Cells[lineNumber, columnNumber])
    {
        range.Value = value;
        range.Style.Font.SetFromFont(new Font("宋体", 10f, FontStyle.Regular));
        range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
        range.Style.WrapText = true;
    }
 
}