I have using below code and try to display data like this
Column1 column2
aaa "563210,jjj,lll"
but my data display like this
Column1 column2
aaa 563210,jjj,lll
VB.Net
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
| Protected Sub ExportCSV(sender As Object, e As EventArgs) Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString Using con As New SqlConnection(constr) Using cmd As New SqlCommand("SELECT TOP 1 'aaa' column1,'563210,jjj,lll' column2 FROM Customers") Using sda As New SqlDataAdapter() cmd.Connection = con sda.SelectCommand = cmd Using dt As New DataTable() sda.Fill(dt) 'Build the CSV file data as a Comma separated string. Dim csv As String = String.Empty For Each column As DataColumn In dt.Columns 'Add the Header row for CSV file. csv += column.ColumnName + ","c Next 'Add new line. csv += vbCr & vbLf For Each row As DataRow In dt.Rows For Each column As DataColumn In dt.Columns 'Add the Data rows. csv += AddEscapeSequenceInCsvField(row(column.ColumnName).ToString()) + "," Next 'Add new line. csv += vbCr & vbLf Next 'Download the CSV file. Response.Clear() Response.Buffer = True Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv") Response.Charset = "" Response.ContentType = "application/text" Response.Output.Write(csv) Response.Flush() Response.End() End Using End Using End Using End UsingEnd SubPrivate Function AddEscapeSequenceInCsvField(ValueToEscape As String) As String If ValueToEscape.Contains(",") Then 'Return (Convert.ToString("""") & ValueToEscape) + """" Return String.Format("{0}{1}{2}", """""""", ValueToEscape, """""""") ElseIf ValueToEscape.Contains("+") Then Return (Convert.ToString("""") & ValueToEscape) + """" Else Return ValueToEscape End IfEnd Function |
C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
| protected void ExportCSV(object sender, EventArgs e){ string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) { using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 'aaa' column1,'563210,jjj,lll' column2 FROM Customers")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.Connection = con; sda.SelectCommand = cmd; using (DataTable dt = new DataTable()) { sda.Fill(dt); //Build the CSV file data as a Comma separated string. string csv = string.Empty; foreach (DataColumn column in dt.Columns) { //Add the Header row for CSV file. csv += column.ColumnName + ','; } //Add new line. csv += "\r\n"; foreach (DataRow row in dt.Rows) { foreach (DataColumn column in dt.Columns) { //Add the Data rows. csv += AddEscapeSequenceInCsvField(row[column.ColumnName].ToString()) + ","; } //Add new line. csv += "\r\n"; } //Download the CSV file. Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment;filename=SqlExport.csv"); Response.Charset = ""; Response.ContentType = "application/text"; Response.Output.Write(csv); Response.Flush(); Response.End(); } } } }}private string AddEscapeSequenceInCsvField(string ValueToEscape){ if (ValueToEscape.Contains(",")) { //return (Convert.ToString("\"") + ValueToEscape) + "\""; return string.Format("{0}{1}{2}", "\"\"\"", ValueToEscape, "\"\"\""); } else if (ValueToEscape.Contains("+")) { return (Convert.ToString("\"") + ValueToEscape) + "\""; } else { return ValueToEscape; }} |
Screenshot
No comments:
Post a Comment