In Part 1 and Part 2 of this series, we have used column mappings and index mapping to generate our csv files. These approach requires us to loop thru each colum to create the header, the loop thru again to create the rows. The problem with this approach is the amount of time spent in coding such procedures, well, its not that long... but considere this, if you could do something in a short amount of time with shorter lines of code and changing from one approach to another approach would make your life as a programmer a little bit better.. wouldn't you consider using that approach?
This article is really not about creating CSV files but is about data transformation to Micrsoft Excel files.
Now lets look at our code for this example.
Download the C# sample project: CreateCSVCSpart3.zip (16.77 KB)
Download the VB.NET sample project: CreateCSVVBpart3.zip (17.66 KB)
[C# Version]
private void createButton_Click(object sender, System.EventArgs e)
{
//create the command object for the connection to the database
//also assign the sql statement to be use in querying our database
SqlCommand command = new SqlCommand(sqlStatementField.Text,new SqlConnection(connectionStringField.Text));
//open the connection
command.Connection.Open();
//create the sqldataadapter that would hold the result set
SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
//create our datatable
DataTable dataTable = new DataTable();
//fill the datatable with the values fetched from our query
sqlAdapter.Fill(dataTable);
//set the conttent type of the file to be downloaded
Response.ContentType = "Application/x-msexcel";
//add the response headers
Response.AddHeader("content-disposition", "attachment; filename=\"" + filenameField.Text + "\"");
//create our datagrid object
DataGrid csvGrid = new DataGrid();
//set the datasource for datatable
csvGrid.DataSource = dataTable;
//bind the values in our datatable to our grid
csvGrid.DataBind();
//create our stringwriter object
StringWriter stringWriter = new StringWriter();
//create our htmltextwriter
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
//render the contents of our datagrid to the htmlwriter
csvGrid.RenderControl(htmlWriter);
//since the convert to string the value of our stringwriter object.
Response.Write(stringWriter.ToString());
//close the connection
command.Connection.Close();
//end the http response
Response.End();
}
[VB.NET Version]
Private Sub createButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles createButton.Click
'create the command object for the connection to the database
'also assign the sql statement to be use in querying our database
Dim command As SqlCommand = New SqlCommand(sqlStatementField.Text, New SqlConnection(connectionStringField.Text))
'open the connection
command.Connection.Open()
'create the sqldataadapter that would hold the result set
Dim sqlAdapter As SqlDataAdapter = New SqlDataAdapter(command)
'create our datatable
Dim dataTable As DataTable = New DataTable
'fill the datatable with the values fetched from our query
sqlAdapter.Fill(dataTable)
'set the conttent type of the file to be downloaded
Response.ContentType = "Application/x-msexcel"
'add the response headers
Response.AddHeader("content-disposition", "attachment; filename=""" & filenameField.Text & """")
'add the contents of the result set to the response stream and generate our csv file
'create our datagrid object
Dim csvGrid As DataGrid = New DataGrid
'set the datasource for datatable
csvGrid.DataSource = dataTable
'bind the values in our datatable to our grid
csvGrid.DataBind()
'create our stringwriter object
Dim stringWriter As System.IO.StringWriter = New System.IO.StringWriter
'create our htmltextwriter
Dim htmlWriter As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWriter)
'render the contents of our datagrid to the htmlwriter
csvGrid.RenderControl(htmlWriter)
'since the convert to string the value of our stringwriter object.
Response.Write(stringWriter.ToString())
'close the connection
command.Connection.Close()
'end the http response
Response.End()
End Sub
A screenshot of the resulting file is shown below:
Most of the time the requirement is really isn't creating a CSV file, but a file that contains data from a database that could be openned by a user using common office productivity tools such as Microsoft Excel. In this case, we can use this approach. This approach doesnt actually generates csv files but html tables that when opened in excel is automatically converted to a readable data since Excel can automatically recognize and parse HTML documents.
Download the C# sample project: CreateCSVCSpart3.zip (16.77 KB)
Download the VB.NET sample project: CreateCSVVBpart3.zip (17.66 KB)