none
sql results to a excel file in C# (no ssis package)

    Question

  • Hello all
    I have a code that execute a sql and bring the results to a txt file. Now I have been asked to put the result in a excel sheet. could you please advise the faster way to implement that. Thank you. Your help is appreciated. // See current code that needs to be change to a excel sheet instead.
                String queryString = "SELECT... ";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(queryString, connection);
                    SqlDataReader sqlReader;
                    TextWriter tw = new StreamWriter(filepath);
                    tw.WriteLine("HEADER1 | HEADER2 | HEADER3");
                    sqlReader = command.ExecuteReader();
                    while (sqlReader.Read())
                    {//TODO
                        String Line;
                        Line=sqlReader[0]+"|"+sqlReader[1]+"|"+sqlReader[2];
                        //Console.WriteLine(Line);
                        tw.WriteLine(Line);// \n no needed since we are using Writeline
                    }
                    sqlReader.Close();
                    tw.Close();
                    connection.Close();

    Wednesday, February 23, 2011 4:13 PM

Answers

All replies

  • If you review this thread, it may help you. http://groups.google.com/group/DotNetDevelopment/browse_thread/thread/4a00f1d46994352a?pli=1

    There are a few different ways described.


    Louis
    Wednesday, February 23, 2011 4:30 PM
  • Hi msofteagle,

    Thank you for posting.

    I think there are many ways can achieve your goal read the data from database and write them to excel, but IMHO I don’t very agree with you about the fastest way to read and write way. I think you can find more suited way for you to accomplish your task in this article.

    If you have any question, please feel free to let me know.

    Sincerely,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 24, 2011 6:26 AM
  • Yes please. Please advise with some examples. I would like to keep it simple. for example a "select field1, field2 from table1", execute this through c# and save the result in a excel file file1.xls Sheet1 showing the results with the 2 columns field1, field2.

    Thank you so much for your help

    Thursday, February 24, 2011 7:11 PM
  • Hi msofteagle,

    I suggest you can check the previous link I've posted. It's very helpful for you to solve your question.

    Here's my sample code. I think you should modify somewhere and hope it helps.

    string str =@"Here you can enter the path of Excel file you want to save.";
    object misValue = System.Reflection.Missing.Value;
    Excel.Application myExcel = new Excel.Application();
    Excel.Workbooks myWorkbooks = (Excel.Workbooks)myExcel.Workbooks;
    Excel.Workbook myWorkbook = (Excel.Workbook)(myWorkbooks.Add(misValue));
    
    Excel.Sheets mySheets = (Excel.Sheets)myWorkbook.Worksheets;
    Excel.Worksheet myWorksheet = (Excel.Worksheet)(mySheets.get_Item(1));
    Excel.Range myRange = myWorksheet.get_Range("A1", misValue);
    Excel.QueryTables myQuerytables = myWorksheet.QueryTables;
    Excel.QueryTable myQueryTable = (Excel.QueryTable)myQuerytables.Add("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source= DataSource here;", myRange, "SQL query statement here");
    myQueryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
          
    myWorkbook.SaveAs(str, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
    myWorkbook.Close(false, misValue, misValue);
    myExcel.Quit();
    

    Sincerely,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, February 25, 2011 9:27 AM
  • Any update? Has your question been resolved?

    Best Regards,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 03, 2011 3:06 PM
  • Ok, thank you very much for your help. i am using your code. But it is creating a empty excel file with the name i provided. i check the select statement in the db and it is returning data. however the excel file is empty. Please advise

                String queryString = "select field1, field 2 from table";
                string str = @"c:\test\excelfile1.xls";//@"Here you can enter the path of Excel file you want to save.";
                object misValue = System.Reflection.Missing.Value;
                Excel.Application myExcel = new Excel.Application();
                Excel.Workbooks myWorkbooks = (Excel.Workbooks)myExcel.Workbooks;
                Excel.Workbook myWorkbook = (Excel.Workbook)(myWorkbooks.Add(misValue));

                Excel.Sheets mySheets = (Excel.Sheets)myWorkbook.Worksheets;
                Excel.Worksheet myWorksheet = (Excel.Worksheet)(mySheets.get_Item(1));
                Excel.Range myRange = myWorksheet.get_Range("A1", misValue);
                Excel.QueryTables myQuerytables = myWorksheet.QueryTables;
                Excel.QueryTable myQueryTable = (Excel.QueryTable)myQuerytables.Add("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source= DBserver;Initial Catalog=dbname;User=good;Password=good;", myRange, queryString);
                myQueryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;

                myWorkbook.SaveAs(str, misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
                myWorkbook.Close(false, misValue, misValue);
                myExcel.Quit();

    Thursday, March 03, 2011 5:28 PM
  • Thursday, March 03, 2011 6:12 PM
  • Larcolais Gong

    Could you please advise..

    Thank you

    Monday, March 14, 2011 9:56 PM