csv 만들기

2016. 12. 27. 17:17

1. 첫번째 방법

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using MySql.Data.MySqlClient;

namespace CreateToCSV
{
    class Program
    {
        static void Main(string[] args)
        {

            DataSet ds = new DataSet();

            string connString = string.Format(@"Data Source = {0};Database={1};User ID={2};Password={3};allow user variables=true",
                "xxxxxxxxx", "1_account", "root", "xxxxxxxxxx");
            using(MySqlConnection conn = new MySqlConnection(connString))
            {
                string query = "SELECT " +
               "*FROM " +
               "VISIT_T";

                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 120;

                MySqlDataAdapter da = new MySqlDataAdapter(cmd);

                try
                {
                    conn.Open();
                    da.Fill(ds);
                    conn.Close();
                }
                catch (System.Exception ex)
                {
                    conn.Close();
                }
            }

            StreamWriter sw = new StreamWriter("CSV.csv");
            WriteToStream(sw, ds.Tables[0], false, false);

            Console.ReadKey();
        }

        public static void WriteToStream(TextWriter stream, DataTable table, bool header, bool quoteall)
        {
            if (header)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    WriteItem(stream, table.Columns[i].Caption, quoteall);
                    if (i < table.Columns.Count - 1)
                        stream.Write(',');
                    else
                        stream.Write("\r\n");
                }
            }
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    WriteItem(stream, row[i], quoteall);
                    if (i < table.Columns.Count - 1)
                        stream.Write(',');
                    else
                        stream.Write("\r\n");
                }
            }
            stream.Flush();
            stream.Close();
        }

        private static void WriteItem(TextWriter stream, object item, bool quoteall)
        {
            if (item == null)
                return;
            string s = item.ToString();
            if (quoteall || s.IndexOfAny("\",\x0A\x0D".ToCharArray()) > -1)
                stream.Write("\"" + s.Replace("\"", "\"\"") + "\"");
            else
                stream.Write(s);
            stream.Flush();
        }
    }
}

2. 두번째 방법

참조 : http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv

찾아본 결과 아래만큼 간결하고 속도가 빠른 것이 없음. 간단히 설명하면 DataTable에서 LINQ를 사용해 string 배열로 뽑아낸 다음 Join으로 ',' 구분자로 붙이고 StringBuilder에 넣었다가 텍스트 파일로 만듦.

.net >>>>>>>>>>>>>>>= 3.5
StringBuilder sb = new StringBuilder();

string[] columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName).
ToArray();
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
    string[] fields = row.ItemArray.Select(field => field.ToString()).
                                    ToArray();
    sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

.net <<<<<<<<<<<<<<<<= 3.5

.net >>>>>>>>>>>>>>>= 4.0

StringBuilder sb = new StringBuilder();

IEnumerable<string> columnNames = dt.Columns.Cast<DataColumn>().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));

foreach (DataRow row in dt.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}

File.WriteAllText("test.csv", sb.ToString());

.net <<<<<<<<<<<<<<<<= 4.0

특수한 문자를 없앨 때는 아래처럼.
foreach (DataRow row in dt.Rows)
{
    IEnumerable<string> fields = row.ItemArray.Select(field =>
      string.Concat("\"", field.ToString().Replace("\"", "\"\""), "\""));
    sb.AppendLine(string.Join(",", fields));
}

csv 파일을 만들 때 메모리를 너무 많이 잡아먹는다면 File.AppendTextAll() 사용하여 몇줄 단위로 쓰게~

저작자표시

'IT > C#' 카테고리의 다른 글

pdf 변환 다운로드 (0)	2017.02.10
로딩바 화면 구현 (0)	2017.01.17
그리드뷰(GridView) 필드 동적 생성 (0)	2015.06.22
팀파운데이션 매핑 제거 (0)	2015.06.19
Managedpipelinehandler error (0)	2015.01.20