Solved

Get SQL  Server data into Excel

Posted on 2006-07-19
10
283 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
ID: 17138644
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
ID: 17138675
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
ID: 17138778
What version of VS are you using?
0
 
LVL 1

Author Comment

by:narmi2
ID: 17138934
2003
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17139191
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

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

Expert Comment

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

Expert Comment

by:mydasx
ID: 17139761
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
ID: 17139778
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
ID: 17144592
Where do I get this free addon from for vs.net 2003?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

16 Experts available now in Live!

Get 1:1 Help Now