导出Excel的2个方法

导出到Excel的两种方法

第一种：

1、首先创建Excle模板，另存为 “xml”文件。使用记事本等编辑软件打开文件的代码。然后另存为视图文件“Export.cshtml”;

2、控制器操作

 1         public ActionResult Export()
 2         {
 3             #region Excel下载设置
 4             Response.Clear();
 5             Response.ClearContent();
 6             Response.Buffer = true;
 7             Response.ContentEncoding = System.Text.Encoding.UTF8;
 8             Response.ContentType = "application/ms-excel";
 9             string downloadFileName = "文件名" + ".xls";
10             if (Request.UserAgent != null && Request.UserAgent.ToLower().IndexOf("msie", System.StringComparison.CurrentCultureIgnoreCase) > -1)
11             {
12                 downloadFileName = HttpUtility.UrlPathEncode(downloadFileName);
13             }
14             if (Request.UserAgent != null && Request.UserAgent.ToLower().IndexOf("firefox", System.StringComparison.CurrentCultureIgnoreCase) > -1)
15             {
16                 Response.AddHeader("Content-Disposition", "attachment;filename="" + downloadFileName + """);
17             }
18             else
19                 Response.AddHeader("Content-Disposition", "attachment;filename=" + downloadFileName);
20 
21             #endregion
22             return View();
23         }

View Code

3、添加一个页面，用来点击导出按钮，触发导出事件

<a type="button" class="btn btn-orange" data-icon='file-excel-o' href="javascript:" onclick="ConfirmAndExport('@(Url.Action("Export"))', '您确定要导出吗？')" >导出</a>

4、jquery：

 1 function ConfirmAndExport(url, msg) {
 2             $(this).alertmsg('confirm',msg, {
 3                 okCall: function () {
 4                     var data = $("#pagerForm", $.CurrentNavtab).serialize();
 5                     var inputs = '';
 6                     jQuery.each(data.split('&'), function () {
 7                         var pair = this.split('=');
 8                         inputs += '<input type="hidden" name="' + pair[0] + '" value="' + pair[1] + '" />';
 9                     });
10                     jQuery('<form action="' + url + '" method="post">' + inputs + '</form>').appendTo('body').submit().remove();
11                 }
12             });
13             return false;
14         }

 1 function ConfirmAndExport(url) {
 2     alertMsg.confirm("确定要导出当前数据吗？", {
 3         okCall: function () {
 4             var data = $("#pagerForm", navTab.getCurrentPanel()).serialize();
 5             var inputs = '';
 6             jQuery.each(data.split('&'), function () {
 7                 var pair = this.split('=');
 8                 inputs += '<input type="hidden" name="' + pair[0] + '" value="' + pair[1] + '" />';
 9             });
10             jQuery('<form action="' + url + '" method="post">' + inputs + '</form>').appendTo('body').submit().remove();
11         }
12     });
13     return false;
14 }


第二种、使用npoi
页面代码很简单
就是一个触发下载的按钮
js代码也同上
点击按钮，触发js，跳转到控制器。
然后在控制器里调用要给公共方法，如下：
创建一个公共方法供以后使用：
/// <summary>
/// Excel导出
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string Export(DataTable dt)
{
    try
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet1 = workbook.CreateSheet("导出记录");
        int cellCount = dt.Columns.Count;//列数

        IRow rowHead = sheet1.CreateRow(0);  //创建表头
        //绑定字体样式到表头
        IFont headfont = workbook.CreateFont();
        headfont.FontName = "黑体";
        headfont.Color = HSSFColor.Black.Index;
        headfont.FontHeightInPoints = 11;

        //绑定字体到样式上
        ICellStyle Headstyle = workbook.CreateCellStyle();
        Headstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
        Headstyle.Alignment = HorizontalAlignment.Center; //横向居中

        Headstyle.SetFont(headfont);
        //边框颜色
        Headstyle.BorderBottom = BorderStyle.Thin;
        Headstyle.BottomBorderColor = HSSFColor.Grey40Percent.Index;
        Headstyle.BorderLeft = BorderStyle.Thin;
        Headstyle.LeftBorderColor = HSSFColor.Grey40Percent.Index;
        Headstyle.BorderRight = BorderStyle.Thin;
        Headstyle.RightBorderColor = HSSFColor.Grey40Percent.Index;
        Headstyle.BorderTop = BorderStyle.Thin;
        Headstyle.TopBorderColor = HSSFColor.Grey40Percent.Index;
        //创建表头列
        for (int j = 0; j < cellCount; j++)
        {
            ICell cell = rowHead.CreateCell(j);
            string[] arr = dt.Columns[j].ColumnName.Split('_');
            cell.SetCellValue(arr[0]);
            cell.CellStyle = Headstyle;
            if (arr.Length > 1)
            {
                sheet1.SetColumnWidth(j, Utils.StrToInt(arr[1], 20) * 256);
            }
            else
            {
                sheet1.SetColumnWidth(j, 21 * 256);
            }
        }
        rowHead.Height = 25 * 20;


        //填充内容
        //绑定字体样式到表格内容
        IFont font = workbook.CreateFont();

        //字体样式
        font.FontName = "黑体";
        font.Color = HSSFColor.Black.Index;
        font.FontHeightInPoints = 10;
        ICellStyle style = workbook.CreateCellStyle();
        style.SetFont(font);
        style.WrapText = true;//设置换行这个要先设置
        //垂直居中
        style.VerticalAlignment = VerticalAlignment.Center;
        style.Alignment = HorizontalAlignment.Center;
        //边框样式
        style.BorderBottom = BorderStyle.Thin;
        style.BottomBorderColor = HSSFColor.Grey40Percent.Index;
        style.BorderLeft = BorderStyle.Thin;
        style.LeftBorderColor = HSSFColor.Grey40Percent.Index;
        style.BorderRight = BorderStyle.Thin;
        style.RightBorderColor = HSSFColor.Grey40Percent.Index;
        style.BorderTop = BorderStyle.Thin;
        style.TopBorderColor = HSSFColor.Grey40Percent.Index;

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            IRow row = sheet1.CreateRow((i + 1));
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = row.CreateCell(j);
                cell.SetCellValue(dt.Rows[i][j].ToString());
                cell.CellStyle = style;
            }
            row.Height = 20 * 20;
        }

        string path = Path.Combine("~/Uploads/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/");
        if (!Directory.Exists(HttpContext.Current.Server.MapPath(path)))
        {
            Directory.CreateDirectory(HttpContext.Current.Server.MapPath(path));
        }
        string fileName = Guid.NewGuid().ToString() + ".xlsx";
        var fullPath = path + fileName;
        FileStream sw = File.Create(HttpContext.Current.Server.MapPath(fullPath));
        workbook.Write(sw);
        sw.Close();
        return fullPath;
    }
    catch (Exception ex)
    {
        throw ex;
        return ex.Message;
    }
}

返回下载文件的地址。

那我们如何将List集合转换为DataTable呢？
接着往下看：
public static DataTable List2DataTable<T>(IEnumerable<T> array)
{
    var dt = new DataTable();
    //创建表头
    foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
        dt.Columns.Add(dp.Name, dp.PropertyType);
    foreach (T item in array)
    {
        var Row = dt.NewRow();
        foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T)))
            Row[dp.Name] = dp.GetValue(item);
        dt.Rows.Add(Row);
    }

    return dt;
}


string MapProperty<T>(T t)
{
    var name=new StringBuilder();
    var value = new StringBuilder();
    PropertyInfo[] propertyInfos = t.GetType().GetProperties();
    
    if(propertyInfos.Length>0)
    {
        foreach(var info in propertyInfos)
        {
            name.Append(info.Name);
            name.Append(" = ");
            name.Append(info.GetValue(t)+"	");
            name.Append(info.PropertyType +"
");
        }
    }
    return name.ToString();
}