post完成导出Excel

get以为参数有长度限制所以如果传送参数比较多的话，还是得使用post传值。

public JsonResult Export(GridPager pager, DateTime? beginDate, DateTime? endDate, string SAPPO, string ProductCode, string PackNo, string LotNo, string MaterialCode, string MaterialPackNo, string MaterialLotNo,string PickStatus)
        {
            List<PM_IngredientsModel> list = m_BLL.GetListExprot(ref pager, beginDate, endDate, SAPPO, ProductCode, PackNo, LotNo, MaterialCode, MaterialPackNo, MaterialLotNo, 2, PickStatus);
            JArray jObjects = new JArray();
            foreach (var item in list)
            {
                var jo = new JObject();
                jo.Add("单据日期", item.ProduceDate);
                jo.Add("单据号", item.DocNumber);
                jo.Add("工单号", item.SAPNo);
                jo.Add("成品品番", item.ProductCode);
                jo.Add("成品品名", item.ProductName);
                jo.Add("品番", item.MaterialCode);
                jo.Add("品名", item.MaterialName);
                jo.Add("现品票票号", item.PackNo);
                jo.Add("现品票批次", item.PackLotNo);
                jo.Add("创建时间", item.CreateTime);
                jObjects.Add(jo);
            }
            var dt = JsonConvert.DeserializeObject<System.Data.DataTable>(jObjects.ToString());
            var exportFileName = string.Concat(
                "File",
                DateTime.Now.ToString("yyyyMMddHHmmss"),
                ".xlsx");
            //直接生成Excel表格
            var a = toexcel(exportFileName, dt, exportFileName);
            return Json(JsonHandler.CreateMessage(1, "OK", "/Upload/FileDownload/" + exportFileName));
        }

public bool toexcel(string excelname, System.Data.DataTable dt, string path)
        {
            bool re = false;
            if (dt.Rows.Count < 0)
            {
                return false;
            }
            else
            {
                //创建新的excel
                Microsoft.Office.Interop.Excel.Application exApp = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook exBook = exApp.Workbooks.Add(true);
                Microsoft.Office.Interop.Excel.Worksheet exSheet = exBook.Worksheets[1];

                string[] aaa = new string[dt.Columns.Count];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    //exSheet.Cells[1][i+1] = dt.Columns[i].ColumnName.ToString();
                    aaa[i] = dt.Columns[i].ColumnName.ToString();
                    exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]].Value2 = aaa;
                }
                int rowNum = dt.Rows.Count;
                int colNum = dt.Columns.Count;
                string[,] finalData = new string[rowNum, colNum];
                for (int i = 0; i < rowNum; i++)
                {
                    for (int j = 0; j < colNum; j++)
                    {
                        finalData[i, j] = dt.Rows[i][j].ToString();

                    }
                }
                exSheet.Range[exSheet.Cells[2, 1], exSheet.Cells[rowNum + 1, colNum]].Value2 = finalData;
                exSheet.Range[exSheet.Cells[2, 1], exSheet.Cells[rowNum + 1, colNum]].NumberFormatLocal = "@ ";

                exBook.Saved = true;

                exBook.SaveAs(@"D:\StanleyBarCode\Upload\FileDownload\" + path);
                exBook.Close();
                if (exApp != null)
                {
                    exApp.Workbooks.Close();
                    exApp.Quit();
                }
                re = true;
            }
            return re;
        }

exBook.SaveAs();可以不写参数，默认生成到文档中。

这里其实就已经写好Excel了，但是需要返回一个路径给前段，做一个下载效果。

前段代码如下

$.post("@Url.Action("Export")", { beginDate: $("#beginDate").val(), endDate: $("#endDate").val(), SAPPO: $("#SAPPO").val(), ProductCode: $("#ProductCode").val(), PackNo: $("#PackNo").val(), LotNo: $("#LotNo").val(), MaterialCode: $("#MaterialCode").val(), MaterialPackNo: $("#MaterialPackNo").val(), MaterialLotNo: $("#MaterialLotNo").val(), PickStatus: $("#PickStatus").val() }, function (data) {
                         window.location = data.value;
                     }, "json");

直接访问写好的路径就ok，效果图如下