随笔 - 290  文章 - 10  评论 - 84  2

这个是客户端表格导出伪Excel文档. 不知道为啥,超过200条,不能导出,显示网络错误

 

$.extend($.fn.datagrid.methods, {  
        //超过200条,不能导出,显示网络错误??
        toExcel: function(jq, filename){  
            return jq.each(function(){  
                var uri = 'data:application/vnd.ms-excel;base64,'
                    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
                    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
                    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

                var alink = $('<a style="display:none"></a>').appendTo('body');
                var view = $(this).datagrid('getPanel').find('div.datagrid-view');

                var ignoreColumnIndex = 2;//前3列不导出
                //冻结的表格
                var table = view.find('div.datagrid-view2 table.datagrid-btable').clone();
                var tbody = table.find('>tbody');
                view.find('div.datagrid-view1 table.datagrid-btable>tbody>tr').each(function (index) {                   
                        $(this).clone().children().prependTo(tbody.children('tr:eq(' + index + ')'));
                });
                //非冻结表格
                var head = view.find('div.datagrid-view2 table.datagrid-htable').clone();
                var hbody = head.find('>tbody');
                view.find('div.datagrid-view1 table.datagrid-htable>tbody>tr').each(function (index) {
                        $(this).clone().children().prependTo(hbody.children('tr:eq(' + index + ')'));
                });
                hbody.prependTo(table);

                var ctx = { worksheet: name || 'Worksheet', table: table.html() || '' };
                alink[0].href = uri + base64(format(template, ctx));
                alink[0].download = filename;
                alink[0].click();
                alink.remove();  
            })  
        },
        toExcelUnfreeze: function (jq, filename) {
            return jq.each(function () {
                var uri = 'data:application/vnd.ms-excel;base64,'
                    , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="utf-8"/><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
                    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
                    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

                var alink = $('<a style="display:none"></a>').appendTo('body');
                var view = $(this).datagrid('getPanel').find('div.datagrid-view');
                //非冻结列的table body
                var table = view.find('div.datagrid-view2 table.datagrid-btable').clone();

                //非冻结列的table header
                var head = view.find('div.datagrid-view2 table.datagrid-htable').clone();
                var hbody = head.find('>tbody');

                hbody.prependTo(table);

                var ctx = { worksheet: filename || 'Worksheet', table: table.html() || '' };
                alink[0].href = uri + base64(format(template, ctx));
                alink[0].download = filename;
                alink[0].click();
                alink.remove();
            })
        },
    })

 

改成服务器端导出

            var qry = GetData(keys, values);

            //如果 path2 不包括根(例如,如果 path2 没有以分隔符或驱动器规格起始),则结果是两个路径的串联,具有介于其间的分隔符。如果 path2 包括根,则返回 path2。
            string wwwroot = Path.Combine(_host.ContentRootPath, "wwwroot"); //改在wwwroot目录下,方便在新窗口打开


            string Template = "template.xlsx";
            string templateFilePath = Path.Combine(wwwroot, Template);
            string pureFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff_") + "Achievement";
            //每天一个文件夹
            string destFilePath = wwwroot + "\\Achievement\\" + pureFileName + ".xlsx";
            try
            {

                using (ExcelPackage ep = new ExcelPackage(new FileInfo(templateFilePath)))
                {
                    ExcelWorksheet ws = ep.Workbook.Worksheets[0]; //第1张Sheet


                    int colStart = ws.Dimension.Start.Column;  //工作区开始列,start=1
                    int colEnd = ws.Dimension.End.Column;       //工作区结束列
                    int rowStart = ws.Dimension.Start.Row;       //工作区开始行号,start=1
                    int rowEnd = ws.Dimension.End.Row;       //工作区结束行号

                    var lst = qry.ToList();
                    for (int i = 0; i < lst.Count; i++)
                    {
                        int j = 1;
                        ws.Cells[i + 2, j++].Value = lst[i].BelongDate;
                        ws.Cells[i + 2, j++].Value = lst[i].Region;
                        ws.Cells[i + 2, j++].Value = lst[i].DepName;
                        ws.Cells[i + 2, j++].Value = lst[i].ProjectName;
                        ws.Cells[i + 2, j++].Value = lst[i].Remark;

                    }

                    ws.Calculate(); //重算公式
                    ep.SaveAs(new FileInfo(destFilePath));

                    return Download(destFilePath);
                    
                }
            }
            catch (Exception ex)
            {
                return Json(new { isSuccess = false, message = "失败:" + ex.Message }, "text/html");
            }
        }

之前导出时,还用了一个错误方法,这个方法在数据量少时,还能正常导出,数据量超过几M就显示网络错误.

            FileInfo DownloadFile = new FileInfo(FullFileName);
            if (DownloadFile.Exists)
            {
                Response.Clear();
                Response.Headers.Clear();
                Response.ContentType = "application/octet-stream";
                Response.Headers.Add("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.Name, System.Text.Encoding.UTF8));
                Response.Headers.Add("Content-Length", DownloadFile.Length.ToString());
                Response.SendFileAsync(DownloadFile.FullName);
            }
            return Content("")

改成这样就正常了

            FileInfo DownloadFile = new FileInfo(FullFileName);
            return File(file2byte(FullFileName), "application/octet-stream", HttpUtility.UrlEncode(DownloadFile.Name, System.Text.Encoding.UTF8));

        //从文件读取 byte
         public byte[] file2byte(string fileName)
         {            
             // 打开文件
             FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
             // 读取文件的 byte[]
             byte[] bytes = new byte[fileStream.Length];
             fileStream.Read(bytes, 0, bytes.Length);
             fileStream.Close();
            return bytes;

         }

  

 

posted on 2019-03-26 14:54  Gu  阅读(...)  评论(... 编辑 收藏