C#写csv文件

时间:2024-03-08 07:55:05

1、在项目中经常需要把报表下载为csv格式的文件,如何在C#中写csv文件,以下为一个简化的例子,不使用任何控件,旨在说明用法。

前端view

下载结果

2、创建一个MVC项目(Intranet Application),项目结构如下

3、各部分代码:

3.1、定义实体

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcDownLoadCsv.Models
{
    public class Person
    {
        public string Name { get; set; }
        public string Code { get; set; }
        public string Department { get; set; }
    }
}
View Code

3.2、定义共用方法,将object数据转换为HtmlString,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MvcDownLoadCsv.Extensions
{
    public static class DataTypeExtensions
    {
        public static HtmlString ToHtmlCsv(this object o)
        {
            if (o == null) return new HtmlString("");

            var s = o.ToString();

            if (s.Length == 0) return new HtmlString(s);

            s = s.Replace("\"", "\"\"");
            HtmlString hs = new HtmlString(s.ToString());

            return new HtmlString(string.Concat("\"", s, "\""));

        }

    }
}
View Code

3.3、定义公用方法,把Model数据转换为csv格式文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

namespace MvcDownLoadCsv
{
    public class CsvDownloadResult : ActionResult
    {

        public CsvDownloadResult(string name, object model)
        {

            this.name = name;
            this.model = model;

        }

        private string name;
        private object model;

        public override void ExecuteResult(ControllerContext context)
        {

            context.HttpContext.Response.Clear();
            context.HttpContext.Response.AddHeader("content-disposition", string.Format("attachment; filename=My.{0}.csv", name));
            context.HttpContext.Response.ContentEncoding = Encoding.GetEncoding(1252);// Use ANSI encoding
            context.HttpContext.Response.ContentType = "application/vnd.ms-excel";

            var csv = "";

            using (StringWriter sw = new StringWriter())
            {

                var viewResult = ViewEngines.Engines.FindPartialView(context, name + "Csv");
                var viewContext = new ViewContext(context, viewResult.View, new ViewDataDictionary(model), new TempDataDictionary(), sw);
                viewResult.View.Render(viewContext, sw);

                csv = sw.GetStringBuilder().ToString();

                csv = Regex.Replace(csv, "\r\n[ ]+\r\n[ ]+", "\r\n");
                csv = Regex.Replace(csv, ",\r\n[ ]*", ",");

            }

            context.HttpContext.Response.Write(csv);

            context.HttpContext.Response.End();

        }

    }
}
View Code

此例子中context最后为:

\Person Report\"\r\n\r\n\"Name\",\"Person Code\",\"Person Department\",\r\n\"a\" ,\"1\" ,\"Developer\" ,\r\n\"b\" ,\"2\" ,\"Test\" ,\r\n\"c\" ,\"3\" ,\"HR\" ,\r\n"

\r是回车,英文是Carriage return,表示使光标下移一格

\n是换行,英文是New line,表示使光标到行首

\r\n表示回车换行

 

3.4、定义csv文件格式

@using MvcDownLoadCsv.Extensions
@model List<MvcDownLoadCsv.Models.Person>
"Person Report"
@if (Model.Count == 0)
{<text>"No Person find."</text>
}
else
{<text>
"Name",
"Person Code",
"Person Department",
</text>

    foreach (var item in Model)
    {
        
        @item.Name.ToHtmlCsv() <text>,</text>
        @item.Code.ToHtmlCsv() <text>,</text>
        @item.Department.ToHtmlCsv() <text>,</text>
        <text></text>
    }
}
View Code

3.5、后台方法,构造数据,定义响应前端下载的方法

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MvcDownLoadCsv.Models;

namespace MvcDownLoadCsv.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";
           
            return View(GetPerson());
        }

        public ActionResult About()
        {
            return View();
        }

        public List<Person> GetPerson()
        {
            List<Person> lst = new List<Person>()
            {
                new Person() { Name = "a", Code = "1", Department = "Developer" },
                new Person() { Name = "b", Code = "2", Department = "Test" },
                new Person() { Name = "c", Code = "3", Department = "HR" }
            };
            return lst;
        }

        [HttpPost]
        public ActionResult DownloadCsv()
        {
            //Person为定义报表格式的cshtml文件名称, GetPerson()返回的是报表数据
            return new CsvDownloadResult("Person", GetPerson());
        }

    }

   
}
View Code

3.6、前端页面view

@model List<MvcDownLoadCsv.Models.Person>
@{
    ViewBag.Title = "Home Page";
}
    <script type="text/javascript">

        $(document).ready(function () {
            $("#downloadData").click(function (e) {
                e.preventDefault();
                $("#downloadCriteria")[0].submit();
            });

            $("#copyData").toggle(!!(document.body.createControlRange)).click(function (e) {
                e.preventDefault();
                try {
                    var range = document.body.createControlRange();
                    range.add($("#pis")[0]);
                    range.execCommand("Copy");
                    alert("Report data was copied to the clipboard.");
                }
                catch (e) { alert("Failed to copy the report data to the clipboard:\n\n" + e.message); }
            });
        });
    </script>

<h2>@ViewBag.Message</h2>
<p>
    To learn more about ASP.NET MVC visit <a href="http://asp.net/mvc" title="ASP.NET MVC Website">http://asp.net/mvc</a>.
</p>

 <div  style="height:30px" class="actions">
        @if (Model.Count > 0)
        {
            <a href="javascript:void(0);" id="downloadData" class="action" title="Download Data to Excel">download</a>
            <a href="" id="copyData" class="action" title="Copy Results to Clipboard">copy</a>
            <a href="" id="downloadDataForPopup" class="action" title="Download Data to Excel" style="display:none">downloadForPopup</a>
        }
 </div>



 <div class="fieldset">
    <form action="@Url.Action("DownloadCsv", "Home")" method="post" id="downloadCriteria">
        
    </form>
<table id="projects" class="data">
    <tr>
        <th>Name</th>
        <th>Code</th>
        <th>Department</th>
    </tr>
    @{
       
        foreach (var item in Model)
        {
        <tr>
            <td>@item.Name</td>
            <td>@item.Code</td>
            <td>@item.Department</td>
        </tr>
        }
    }
</table>
</div>
View Code