locked
Help needed to programatically convert Excel 2007 to CSV RRS feed

  • Question

  • User-718146471 posted

    I'm really stuck tryign to figure this out. Converting XLS to CSV is no big deal or at least it seems that way. However, trying to go xlsx to csv seems to be more difficult. For grins, I grabbed some code that is supposed to read a sample xlsx file and convert that into a gridview called GridView1. I figure it can't be much different to turn that into CSV.

    Web.Config:
    
        <connectionStrings>
            <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>
            <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ExcelFile.xlsx;Extended Properties=Excel 12.0;HDR=0;"/>
        </connectionStrings>
    
    Code Behind:
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Configuration;
    using System.Data.OleDb;
    using System.Data;
    
    namespace ConvertExcel_trunk
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                
                string connString = ConfigurationManager.ConnectionStrings["xlsx"].ConnectionString;
                // Create the connection object 
                OleDbConnection oledbConn = new OleDbConnection(connString);
    
                try
                {
                    // Open connection
                    oledbConn.Open();
                    // Create OleDbCommand object and select data from worksheet Sheet1
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
                    
                    // Create new OleDbDataAdapter 
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    // Create a DataSet which will hold the data extracted from the worksheet.
                    DataSet ds = new DataSet();
                    // Fill the DataSet from the data extracted from the worksheet.
                    oleda.Fill(ds, "Mystuff");
                    // Bind the data to the GridView
                    GridView1.DataSource = ds.Tables[0].DefaultView;
                    GridView1.DataBind();
                }
                catch
                {
    
                }                
                finally
                {
                    oledbConn.Close();
                }
            }
        }
    }


     

    Friday, February 11, 2011 8:34 AM

Answers

  • User-359936451 posted

    Try this, update fields as necessary....

     

        Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
            Response.Clear()
    
            Response.Buffer = True
    
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv")
            Response.Charset = ""
            Response.ContentType = "application/text"
    
            GridView1.AllowPaging = False
            GridView1.DataBind()
    
            Dim sb As New StringBuilder()
            For k As Integer = 0 To GridView1.Columns.Count - 1
                'add separator 
                sb.Append(GridView1.Columns(k).HeaderText + ","c)
            Next
    
            'append new line 
            sb.Append(vbCr & vbLf)
            For i As Integer = 0 To GridView1.Rows.Count - 1
                For k As Integer = 0 To GridView1.Columns.Count - 1
                    'add separator 
                    sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)
                Next
                'append new line 
                sb.Append(vbCr & vbLf)
            Next
    
            Response.Output.Write(sb.ToString())
            Response.Flush()
            Response.End()
        End Sub


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 11, 2011 11:55 AM
  • User-1171043462 posted

    Do you have any code for C#? 
     

     

    Refer here

    http://www.aspsnippets.com/Articles/Export-GridView-To-Word-Excel-PDF-CSV-Formats-in-ASP.Net.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 8:18 AM
  • User-718146471 posted

    This is what I ended up using which seems to work very well, perhaps this will aid someone else:

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    
    public partial class Config_convert : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFile.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
            // Create the connection object    
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            // Open connection   
    
            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string
    
            string strSQL = "SELECT * FROM [Sheet1$]";
    
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.
    
            OleDbCommand cmd = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
            dataAdapter.SelectCommand = cmd;
    
            // Create new OleDbDataAdapter    
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.   
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.   
            oleda.Fill(ds, "Mystuff");
            // Bind the data to the GridView   
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        protected void bntConvert_Click(object sender, EventArgs e)
        {    
            try   
            {     
                //Create CSV file     
                StreamWriter objSw = new StreamWriter("C:/exported.csv");   
                //get table from GridView1   
                DataTable objDt = ((DataSet)GridView1.DataSource).Tables[0];    
                //Get No Of Column in GridView      
                int NoOfColumn = objDt.Columns.Count;   
                //Create Header     
                for (int i = 0; i < NoOfColumn; i++)   
                {      
                    objSw.Write(objDt.Columns[i]);   
                    //check not last column     
                    if (i < NoOfColumn - 1)     
                    {           
                        objSw.Write(",");  
                    }     
                }      
                objSw.Write(objSw.NewLine);   
                //Create Data     
                foreach (DataRow dr in objDt.Rows)   
                {       
                    for (int i = 0; i < NoOfColumn; i++)   
                    {        
                        objSw.Write(dr[i].ToString());      
                        if (i < NoOfColumn - 1)    
                        {          
                            objSw.Write(",");     
                        }         
                    }        
                    objSw.Write(objSw.NewLine);   
                }    
                objSw.Close();   
            }      
            catch (Exception ex)   
            {      
                Response.Write("Can Not Generate CSV File"); 
            }    
        }
    }


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 11:14 AM

All replies

  • User-359936451 posted

    Try this, update fields as necessary....

     

        Protected Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
            Response.Clear()
    
            Response.Buffer = True
    
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv")
            Response.Charset = ""
            Response.ContentType = "application/text"
    
            GridView1.AllowPaging = False
            GridView1.DataBind()
    
            Dim sb As New StringBuilder()
            For k As Integer = 0 To GridView1.Columns.Count - 1
                'add separator 
                sb.Append(GridView1.Columns(k).HeaderText + ","c)
            Next
    
            'append new line 
            sb.Append(vbCr & vbLf)
            For i As Integer = 0 To GridView1.Rows.Count - 1
                For k As Integer = 0 To GridView1.Columns.Count - 1
                    'add separator 
                    sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)
                Next
                'append new line 
                sb.Append(vbCr & vbLf)
            Next
    
            Response.Output.Write(sb.ToString())
            Response.Flush()
            Response.End()
        End Sub


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 11, 2011 11:55 AM
  • User-718146471 posted

    Do you have any code for C#? 

    Monday, February 14, 2011 8:07 AM
  • User-1171043462 posted

    Do you have any code for C#? 
     

     

    Refer here

    http://www.aspsnippets.com/Articles/Export-GridView-To-Word-Excel-PDF-CSV-Formats-in-ASP.Net.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 8:18 AM
  • User-718146471 posted

    Does the OleDB data adapter have a limit on the number of columns it can bring in? I have columns a-z and it won't open in my gridview. 

    Monday, February 14, 2011 8:57 AM
  • User-718146471 posted

    Ok, after digging deeper, I found this connection is throwing an exception of System.InvalidOperationException when I try to open the connection. The connection string I'm using is:

    string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ExcelFile.xlsx;Extended Properties=""Excel 12.0;HDR=YES;"""


    I don't get what's wrong with this... This hard coding is merely a test before I set the connectionstring in my web.config file.

     

    Monday, February 14, 2011 9:49 AM
  • User-718146471 posted

    Ok, I'm getting closer now. I have the gridview populating data. However, when I click Export to CSV button, nothing gets saved from GridView1. Here's my code: maybe I missed something???

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Configuration;
    using System.Data.OleDb;
    using System.Data;
    using System.Text;
    
    public partial class Config_convert : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myreport.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
            // Create the connection object    
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            // Open connection   
    
            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string
    
            string strSQL = "SELECT * FROM [Sheet1$]";
    
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.
    
            OleDbCommand cmd = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
            dataAdapter.SelectCommand = cmd;
    
            // Create new OleDbDataAdapter    
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.   
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.   
            oleda.Fill(ds, "Mystuff");
            // Bind the data to the GridView   
            GridView1.DataSource = ds.Tables[0].DefaultView;
            GridView1.DataBind();   
        }
        protected void bntConvert_Click(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.csv");
            Response.Charset = "";
            Response.ContentType = "application/text";
            GridView1.AllowPaging = false;
            GridView1.DataBind();
            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < GridView1.Columns.Count; k++)
            {
                //add separator
                sb.Append(GridView1.Columns[k].HeaderText + ',');
            }
            //append new line
            sb.Append("\r\n");
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                for (int k = 0; k < GridView1.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
                }
                //append new line
                sb.Append("\r\n");
            }
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();
        }
    }


     

    Monday, February 14, 2011 10:15 AM
  • User-718146471 posted

    I set a couple watch values before the end of the routine of my gridview. It is giving me 159 rows but 0 columns. What I find really bizarre is the fact that the gridview is actually displaying the data. What gives? 

    Monday, February 14, 2011 10:29 AM
  • User-718146471 posted

    This is what I ended up using which seems to work very well, perhaps this will aid someone else:

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    
    public partial class Config_convert : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFile.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
            // Create the connection object    
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            // Open connection   
    
            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string
    
            string strSQL = "SELECT * FROM [Sheet1$]";
    
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.
    
            OleDbCommand cmd = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
            dataAdapter.SelectCommand = cmd;
    
            // Create new OleDbDataAdapter    
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.   
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.   
            oleda.Fill(ds, "Mystuff");
            // Bind the data to the GridView   
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        protected void bntConvert_Click(object sender, EventArgs e)
        {    
            try   
            {     
                //Create CSV file     
                StreamWriter objSw = new StreamWriter("C:/exported.csv");   
                //get table from GridView1   
                DataTable objDt = ((DataSet)GridView1.DataSource).Tables[0];    
                //Get No Of Column in GridView      
                int NoOfColumn = objDt.Columns.Count;   
                //Create Header     
                for (int i = 0; i < NoOfColumn; i++)   
                {      
                    objSw.Write(objDt.Columns[i]);   
                    //check not last column     
                    if (i < NoOfColumn - 1)     
                    {           
                        objSw.Write(",");  
                    }     
                }      
                objSw.Write(objSw.NewLine);   
                //Create Data     
                foreach (DataRow dr in objDt.Rows)   
                {       
                    for (int i = 0; i < NoOfColumn; i++)   
                    {        
                        objSw.Write(dr[i].ToString());      
                        if (i < NoOfColumn - 1)    
                        {          
                            objSw.Write(",");     
                        }         
                    }        
                    objSw.Write(objSw.NewLine);   
                }    
                objSw.Close();   
            }      
            catch (Exception ex)   
            {      
                Response.Write("Can Not Generate CSV File"); 
            }    
        }
    }


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, February 14, 2011 11:14 AM
  • User-359936451 posted

    I think that the bind will dynamically building the columns as needed. You can check this property of the Gridview in Design mode

    AutoGenerateColumns = "False"

    If set to false you will not see a column count until the bind, if I understand this correctly.

     

     

    Thursday, February 17, 2011 9:44 AM