导入和导出到CSV文件

一、手工导出导出

1、winform

void DataGridViewToExcel(DataGridView dataGridView1)
{
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
    saveFileDialog.FilterIndex = 0;
    saveFileDialog.RestoreDirectory = true;
    saveFileDialog.CreatePrompt = true;
    saveFileDialog.Title = "保存为Excel文件";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        Stream stream = saveFileDialog.OpenFile();
        StreamWriter sw = new StreamWriter(stream, System.Text.Encoding.GetEncoding(-0));

        string columnTitle = "";
        try
        {
            for (int i = 0; i < dataGridView1.ColumnCount; i++)
            {
                if (i > 0)
                {
                    columnTitle += "\t";//或者为逗号
                }
                columnTitle += dataGridView1.Columns[i].HeaderText;//写入列标题
            }
            sw.WriteLine(columnTitle);
            for (int j = 0; j < dataGridView1.Rows.Count; j++)
            {
                string columnValue = "";
                for (int k = 0; k < dataGridView1.Columns.Count; k++)
                {
                    if (k > 0)
                    {
                        columnValue += "\t";
                    }
                    columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString();
                }
                sw.WriteLine(columnValue);
            }
            sw.Close();
            stream.Close();
        }
        finally
        {
            sw.Close();
            stream.Close();
        }
    }
}

2、Web导出

不用存磁盘文件<iframe>导出。

string exportFileName = "Export" + DateTime.Now.ToString("yyyyMMddHHmmss");
System.Web.HttpContext context = System.Web.HttpContext.Current;
StringBuilder sb = new StringBuilder();
sb.Append("FirstName,LastName,PhoneNo.,State,TimeZone,ZipCode\n");
for (int i = 0; i < result.PhoneList.Count; i++)
{
    sb.Append(result.PhoneList[i].FirstName + "," + result.PhoneList[i].LastName + "," + result.PhoneList[i].ZipCode + "\n");
}
StringWriter sw = new StringWriter(sb);//一定要StringWriter/StreamWriter才能直接导出
sw.Close();
context.Response.ClearHeaders();
context.Response.Clear();
context.Response.Charset = "UTF-8";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.HeaderEncoding = System.Text.Encoding.UTF8;
context.Response.ContentType = "text/csv";
context.Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF });//防止中文乱码
context.Response.Write(sw);
context.Response.AppendHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode("1.csv", System.Text.Encoding.UTF8).Replace("+", "%20"));
sw.Close();
context.Response.Flush();
context.Response.End();

二、利用LumenWorks.Framework.IO.Csv读取CSV文件

需要引用LumenWorks.Framework.IO.dll，读取的时候编码格式要选对，否则会乱码，表头自己设置

• phatcher/CsvReader: Extended version of Sebastian Lorien's fast CSV Reader (github.com)
• NuGet Gallery | LumenWorksCsvReader 4.0.0

static DataTable GetData(Stream stream)
{
    using (stream)
    {
        using (StreamReader input = new StreamReader(stream, Encoding.GetEncoding("shift_jis")))
        {
            using (CsvReader csv = new CsvReader(input, false))
            {
                DataTable dt = new DataTable();
                int columnCount = csv.FieldCount;
                for (int i = 0; i < columnCount; i++)
                {
                    dt.Columns.Add("col" + i.ToString());
                }
                while (csv.ReadNextRecord())
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < columnCount; i++)
                    {
                        if (!string.IsNullOrWhiteSpace(csv[i]))
                        {
                            dr[i] = csv[i];
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt;
            }
        }
    }
}

基本使用场景

using System.IO;
using LumenWorks.Framework.IO.Csv;
void ReadCsv()
{
    // open the file "data.csv" which is a CSV file with headers
    using (CsvReader csv =
           new CsvReader(new StreamReader("data.csv"), true))
    {
        int fieldCount = csv.FieldCount;

        string[] headers = csv.GetFieldHeaders();
        while (csv.ReadNextRecord())
        {
            for (int i = 0; i < fieldCount; i++)
                Console.Write(string.Format("{0} = {1};",
                              headers[i], csv[i]));
            Console.WriteLine();
        }
    }
}

复杂的数据绑定方案（Windows窗体）

using System.IO;
using LumenWorks.Framework.IO.Csv;
void ReadCsv()
{
    // open the file "data.csv" which is a CSV file with headers
    using (CachedCsvReader csv = new
           CachedCsvReader(new StreamReader("data.csv"), true))
    {
        // Field headers will automatically be used as column names
        myDataGrid.DataSource = csv;
    }
}