导出datatable到excel然后让web客户端下载到本地

末蓝、 2022-07-13 03:48 253阅读 0赞

下载excel在webform以及winform之间的区别还真是不少，之前我在9you做多了winform，会用到interop来下载word和excel，甚至在webbrower控件上显示这些信息，到了2010以后openxml大行其道后，我们越来越熟悉用openxml来扣ExcelWorksheet来得到里面的内容，再通过LoadFromDataTable来填充到我们的excel，更有摄甚着，可以用parellal的task来做异步并行来提高导出多个表到工作簿，但是这些一旦用来在web则完全不是那会事情了，因为下载的excel永远在服务器本地，我们需要公布其服务器的地址来给客户下载。之前在东方财富用了这个方法，但是没有领导这个简单的流操作才是避开权限的关键。不用去用a标签的type为file来指定下载地址，这样可以由客户端自己决定保存到哪里。我这里有两种方法来实现下载，NPOI和Myxls，NPO用在mvc上比较好用，而Myxls用在webform的Page上比较好用。

/// <summary>
            /// NPOI插件保存excel到网页，用于MVC4
            /// </summary>
            /// <returns></returns>
            public ActionResult DownLoadExcel()
            {
                string Path = AppDomain.CurrentDomain.BaseDirectory + "Resource\\Config\\DatetimeConfig.xml";
                XElement xele = XElement.Load(Path);
                if ((string)xele.Attribute("content") != "")
                {
                    DateTime dateTime = DateTime.Parse((string) xele.Attribute("content"));
                    TimeSpan span = DateTime.Now - dateTime;
                    if (span.TotalMinutes < 1)
                    {
                        return new EmptyResult(); 
    
                    }
                }
                //延迟30分钟再可以提交
             
                string sql = "SELECT  [UserID],[UserCode],[UserName],[ParentID],[Position],[Mobile],[Email],[Levels],[AttentionTime]FROM Users WHERE CreateStatus = 1 AND(AttentionState = 1); ";
                DataTable dt = ObjectContainer.Instance.GetObject<ISqlExportService>().GetDataTableFromSql(Request, sql);
    
                //创建Excel文件的对象  
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //添加一个sheet  
                NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
                //给sheet1添加第一行的头部标题  
                NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
                //row1.RowStyle.FillBackgroundColor = "";  
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                //将数据逐步写入sheet1各个行  
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());
                    }
                }
                string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间  
                // 写入到客户端   
                System.IO.MemoryStream ms = new System.IO.MemoryStream();
                book.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                DateTime = DateTime.Now;
                xele.SetAttributeValue("content", DateTime);
                xele.Save(AppDomain.CurrentDomain.BaseDirectory + "Resource\\Config\\DatetimeConfig.xml");
                return File(ms, "application/vnd.ms-excel", strdate + "Excel.xls");
            }

然后前端用ActionReult或者FileResult都可以返回，@Html.ActionLink("点击导出Excel", "DownLoadExcel", "SqlExportService")

而webform可以用MyXls插件，这两个都可以在Nuget上得到。

/// <summary>
            /// MyXls组件保存网页Excel，用于webform的Page.Response
            /// </summary>
            public void ExportExcelForPercentForWeb(string sheetName, string xlsname)
            {
                XlsDocument xls = new XlsDocument();
                Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
                try
                {
                    string sql = "SELECT  [UserID],[UserCode],[UserName],[ParentID],[Position],[Mobile],[Email],[Levels],[AttentionTime]FROM Users WHERE CreateStatus = 1 AND(AttentionState = 1); ";
                    DataTable table = ObjectContainer.Instance.GetObject<ISqlExportService>().GetDataTableFromSql(Request, sql);
    
    
                    if (table == null || table.Rows.Count == 0) { return; }
                    //XlsDocument xls = new XlsDocument();
                    //Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
    
                    //填充表头  
                    foreach (DataColumn col in table.Columns)
                    {
                        sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
                    }
    
                    //填充内容  
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
                        }
                    }
    
                    //保存  
                    //xls.FileName = xlsname;
                    //xls.Save();
    
                    #region 客户端保存
                    using (MemoryStream ms = new MemoryStream())
                    {
                        xls.Save(ms);
                        ms.Flush();
                        ms.Position = 0;
                        sheet = null;
                        xls = null;
                        HttpResponse response = System.Web.HttpContext.Current.Response;
                        response.Clear();
    
                        response.Charset = "UTF-8";
                        response.ContentType = "application/vnd.ms-excel";//"application/vnd.ms-excel";
                        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + xlsname));
                        //System.Web.HttpContext.Current.Response.WriteFile(fi.FullName);
                        byte[] data = ms.ToArray();
                        System.Web.HttpContext.Current.Response.BinaryWrite(data);
                       
                    }
                    #endregion
                    //xls = null;
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    sheet = null;
                    xls = null;
                }
            }

当然MyXls也能保存到服务器本地。

/// <summary>
            /// MyXls组件保存本地Excel，用于webform的Page.Response
            /// </summary>
            public  void ExportExcelForPercent(string sheetName, string xlsname)
            {
                string sql = "SELECT  [UserID],[UserCode],[UserName],[ParentID],[Position],[Mobile],[Email],[Levels],[AttentionTime]FROM Users WHERE CreateStatus = 1 AND(AttentionState = 1); ";
                DataTable table = ObjectContainer.Instance.GetObject<ISqlExportService>().GetDataTableFromSql(Request, sql);
              
    
                if (table == null || table.Rows.Count == 0) { return; }
                XlsDocument xls = new XlsDocument();
                Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
    
                //填充表头  
                foreach (DataColumn col in table.Columns)
                {
                    sheet.Cells.Add(1, col.Ordinal + 1, col.ColumnName);
                }
    
                //填充内容  
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        sheet.Cells.Add(i + 2, j + 1, table.Rows[i][j].ToString());
                    }
                }
    
                //保存  
                xls.FileName = xlsname;
                xls.Save();
                xls = null;
            }

更多的内容可以看我的例子，https://github.com/orichisonic/SqlToExcel.git