Solved

Get SQL  Server data into Excel

Posted on 2006-07-19
10
279 Views
Last Modified: 2010-04-23
Hi,

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

Thanks

narmi2
0
Comment
Question by:narmi2
  • 4
  • 4
  • 2
10 Comments
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
 
LVL 1

Author Comment

by:narmi2
Comment Utility
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
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
What version of VS are you using?
0
 
LVL 1

Author Comment

by:narmi2
Comment Utility
2003
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:narmi2
Comment Utility
Dunno.  We don't have it anyway, so can't use those tools.
0
 
LVL 24

Expert Comment

by:Jeff Certain
Comment Utility
It's a free add-in... so you can use them.
0
 
LVL 5

Expert Comment

by:mydasx
Comment Utility
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
 
LVL 5

Accepted Solution

by:
mydasx earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:narmi2
Comment Utility
Where do I get this free addon from for vs.net 2003?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now