• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

Get SQL Server data into Excel


What the best way to get SQL Server data into Excel, format the data, and perform many calculations?


  • 4
  • 4
  • 2
1 Solution
Jeff CertainCommented:
Use DTS (in SQL server) to export the data as a comma-delimited file. Import it into Excel. Add the calculations you need to the spreadsheet.

All of these thigs are already provided as functionality within the various components. Why make it hard on yourself and reinvent the wheel?
narmi2Author Commented:
It's not for me, the USERS want to open a template, or click on a button which will do it all for them. :)
Jeff CertainCommented:
What version of VS are you using?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

narmi2Author Commented:
Jeff CertainCommented:
That's a shame... Visual Studio Tools for Office would have made this a lot easier.  I wonder if they can be used with 2003?
narmi2Author Commented:
Dunno.  We don't have it anyway, so can't use those tools.
Jeff CertainCommented:
It's a free add-in... so you can use them.

ok so you know you need an app to abstract said process for your users.
so, DTS is out.

You will need:  
An ADO controller (hand built) to pluck your data from the database
Custom containers (classes) to store the data in memory
An OLEDB controller to write to excel

These complex calculations, do they need to happen on the excel sheet, or is the excel sheet just a string dump?
Here is an example of how to write to excel

you just include the excel interop into your project excel.dll.  Make sure you copy local.

past that here is an article for using excel from MS


This is my excel controller

Imports Excel
Imports System.Collections.Generic
Imports System.IO

Namespace WinShed.Data
    Public Delegate Sub ExcelWriteProgressEventHandler()

    Public Class ExcelSpreadSheetController
#Region "Events"
        Public Event ExcelRowWrittenMessageEvent As ExcelWriteProgressEventHandler
#End Region

#Region "Spread Sheet Logic"
        Public Function CreateSpreadSheet(ByRef sched As System.Data.DataTable, ByRef xlsPath As String) As Boolean
            Dim retVal As Boolean = True
            Dim row As Integer = 1

            Dim app As New Excel.Application
            Dim exbook As Excel.Workbook
            Dim exsheet As Excel.Worksheet
            exbook = app.Workbooks.Add
            exsheet = CType(exbook.Sheets(1), Worksheet)
            Dim style1 As Excel.Style = exbook.Styles.Add("Style1")
            style1.Font.Size = 9
            style1.NumberFormat = "@"

                exsheet.Cells(row, 1) = "COL1"
                exsheet.Cells(row, 2) = "COL2"
                exsheet.Cells(row, 3) = "COL3"

                For Each schedRow As DataRow In sched.Rows
                row += 1
                Me.PutValueIntoCellAndFormat(exsheet, style1, row, 1, schedRow("Col1").ToString())
                Me.PutValueIntoCellAndFormat(exsheet, style1, row, 2, schedRow("Col2").ToString())
                Me.PutValueIntoCellAndFormat(exsheet, style1, row, 3, schedRow("Col3").ToString())
                RaiseEvent ExcelRowWrittenMessageEvent()

                If (File.Exists(xlsPath)) Then
                End If
            Catch ex As Exception
                retVal = False
            End Try
            Return retVal
        End Function

        Private Sub PutValueIntoCellAndFormat(ByRef sheet As Excel.Worksheet, ByRef style As Excel.Style, ByRef row As Integer, ByRef col As Integer, ByRef val As String)
            Dim cell As Excel.Range = CType(sheet.Cells(row, col), Range)
            cell.Style = style
            cell.Value = val
        End Sub
#End Region
    End Class
End Namespace
narmi2Author Commented:
Where do I get this free addon from for vs.net 2003?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now