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

Get SQL Server data into Excel

Hi,

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

Thanks

narmi2
0
narmi2
Asked:
narmi2
  • 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?
0
 
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. :)
0
 
Jeff CertainCommented:
What version of VS are you using?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
narmi2Author Commented:
2003
0
 
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?
0
 
narmi2Author Commented:
Dunno.  We don't have it anyway, so can't use those tools.
0
 
Jeff CertainCommented:
It's a free add-in... so you can use them.
0
 
mydasxCommented:
suggestions:

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?
0
 
mydasxCommented:
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

http://support.microsoft.com/default.aspx?scid=kb;EN-US;306022

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 = "@"

            Try
                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()

                Next
                If (File.Exists(xlsPath)) Then
                    File.Delete(xlsPath)
                End If
                exbook.SaveAs(xlsPath)
            Catch ex As Exception
                retVal = False
            Finally
                exbook.Close()
            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
0
 
narmi2Author Commented:
Where do I get this free addon from for vs.net 2003?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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