?
Solved

Get SQL  Server data into Excel

Posted on 2006-07-19
10
Medium Priority
?
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 2000 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

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.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

764 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