2

me and a colleague are working on an assignment that requires a simple data transfer data of a SQL Server to MS Excel; using C#. I was able to develop the "foundation" of what I believe works/ However I'm unable to run the program. Assistance is appreciated!

namespace ProjectLab1
{ 
    class Program
    {
    protected void page_load(object sender, EventArgs e)
    {

    }
    protected void btnExport_Click(object sender, EventArgs e)
    {
        string strDelimiter = ddlExportFormat.SelectedValue == "COMMA DELIMITED" ? " ," : "|";
        string conString = "Driver={MySQL ODBC 5.3 ANSI Driver};"
           + "Server=****;Port=****;"
           + "Database=****;"
           + "uid=***;pwd=****";
        StringBuilder sb = new StringBuilder();
        using (OdbcConnection connection = new OdbcConnection(conString))
            connection.Open();
        {
            string theQuery = "SELECT * FROM item i, inventory v where i.invent_id=v.invent_id";
            OdbcDataAdapter DataAdapter = new OdbcDataAdapter(theQuery, connection);
            DataSet ds = new DataSet();
            DataAdapter.Fill(ds, "items");

            ds.Tables[0].TableName = "ITEM";
            ds.Tables[1].TableName = "QUANT";
            ds.Tables[2].TableName = "SIZE";
            ds.Tables[3].TableName = "COLOR";
            ds.Tables[4].TableName = "PRICE\n";

        }

        foreach (DataRow itemDR in ds.Table["ITEMS"].Rows)
        {
            int itemId = Comvert.ToInt32(itemDR["ITEMS"]);
            sb.Append(itemId.ToString() + strDelimiter);
            sb.Append(itemDR["ITEMS"].ToString() + strDelimiter);
            sb.Append(itemDR["QUANT"].ToString() + strDelimiter);
            sb.Append(itemDR["SIZE"].ToString() + strDelimiter);
            sb.Append(itemDR["COLOR"].ToString() + strDelimiter);
            sb.Append(itemDR["PRICE\n"].ToString() + strDelimiter);
            sb.Append("\r\n");
        }



        {
            string strFileName = "thefile.xls";


            StreamWriter file = new StreamWriter(@"C:\Users\debom_000\Desktop\Data\" + strFileName);
            file.WriteLine(sb.ToString());
            File.Close();
            connection.Close(); // Close connection

            //Have program pause to keep from closing console window



        }
    }
}

}

Fredou
  • 19,848
  • 10
  • 58
  • 113
OldZero
  • 37
  • 5
  • Did you add System.Reflection and System.IO to your namespaces? – J.S. Orris Mar 16 '15 at 00:28
  • I have added System.IO by 'using System.IO' but not System.Reflection. How would I implement that into the code? – OldZero Mar 16 '15 at 00:38
  • 1
    Consider using EPPlus [link](http://epplus.codeplex.com/). It is by far the most easy way to create good working Excel files. – masterw Mar 16 '15 at 07:01
  • @OldZero You can also use Interop.Excel - Read through the answers here: https://social.msdn.microsoft.com/Forums/vstudio/en-US/ef11a193-54f3-407b-9374-9f5770fd9fd7/writing-to-excel-using-c?forum=csharpgeneral – J.S. Orris Mar 16 '15 at 14:34
  • @Jeff Orris - This looks really close to what I'm trying to do. Only thing is that I know after the program compiles (if I can get it to work) needs to produce a CSV file **for MS Excel**. – OldZero Mar 17 '15 at 08:24
  • @OldZero Just write 2 different methods WriteToExcel(); and WriteToCSV(); you can write to CSV like this: http://stackoverflow.com/questions/18757097/writing-data-into-csv-file – J.S. Orris Mar 17 '15 at 16:23

1 Answers1

0

Hi if you are using MySQL you can use the built in SELECT INTO OUTFILE to generate excel file.

SELECT * INTO OUTFILE "C:\Users\debom_000\Desktop\Data\export_table_data_excel.xls" 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
FROM database_name.table_name;
RBB08
  • 126
  • 5