Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# VB VB.NET SSIS
I had a requirement of creating an excel file dynamically using SSIS and I succeeded in doing so. Now there are two constraints.
 
1) I have to fetch the query from a table which will be the data for the excel sheet.
i.e, I have a table and I have some reports with report id as primary key and every report has different queries and the respective query has to be called in with respect to the report id
 
Table looks like(t_myTable) :
 
Report_ID	Report_Name	Query
1	        rep_1	        select * from table1
2	        rep_2	        select * from table2
3	        rep_3	        select * from table3
4	        rep_4	        select * from table4
 
the query I'll be using in the back-end is
Select Query from t_myTable where Report_ID=1
this query should fetch me the query in that column and it should be executed. How do I implement this functionality?
 
Help me with the logic.
 
2)The excel I've created should have borders i.e, the region where the data is present should have border.
Consider my data starts from cell A1 and it ends in the cell E50 there should be a border differentiating this region with the rest of the empty cells.
Number of cells are undefined as they are created dynamically.
 
Help me with the logic and syntax for this
 

Thanks in advance Smile | :)
Sadiq
Posted 3-Jan-13 3:17am
Edited 6-Jan-13 21:58pm
__TR__28.9K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hello Sadiq,
 
I am not quite sure of the Point 1; however you can get some hint for the point #2 from the code mentioned below:
 
Try
            Dim excelApp As New Microsoft.Office.Interop.Excel.Application
 
            If (Not File.Exists(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestApp.xlsx"))) Then
                File.Create(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "TestApp.xlsx"))
            End If
 
            Dim excelBook As Workbook = excelApp.Workbooks.Open(AppDomain.CurrentDomain.BaseDirectory & "TestApp.xlsx")
 
            With excelBook
                Dim sheet As Worksheet = excelBook.ActiveSheet
                sheet.Range("A1", "D4").BorderAround(, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic)
            End With
 
            excelBook.Save()
        Catch ex As Exception
            MessageBox.Show("Exception occurred : " & Environment.NewLine & ex.Message)
        End Try
 
Please let me know with details of what you need about point #1.
  Permalink  
Comments
Maciej Los at 7-Jan-13 2:05am
   
Comment from OP:
Hi Debasis,
Thanks for the answer about excel borders

My first requirement is that I have a table in which I have kept a set of sql queries in a column and I have an SSIS package which will run 4 times a week, say it runs on Monday, Tuesday, Wednesday and Friday. On Monday it fetches a set of query, on Tuesday it fetches different set of query and vice-versa for Wednesday and Friday. For time being I used ExecuteScalar() to fetch query
 
Try
cmdCommand.CommandText = strSQL.ToString()
cmdCommand.CommandType = CommandType.Text
cmdCommand.Connection = ConnOledb
drReader = Nothing
newReader = Nothing
cmdCommand.CommandText = newReader.ToString()
cmdCommand.CommandType = CommandType.Text
newReader = cmdCommand.ExecuteReader()
If newReader.HasRows Then
dtDetails.Load(newReader)
End If
newReader.Close()

drReader = cmdCommand.ExecuteReader()
If drReader.HasRows Then
dtDetails.Load(drReader)
End If
drReader.Close()

Catch ex As Exception
drReader = Nothing
End Try

but if i have to fetch two rows of data ,in my case I need to fetch two queries and execute it then how do I do it?

Is my requirement clear enough?

Thanks.
SadiqMohammed at 7-Jan-13 4:41am
   
Hi Debasis,
Thanks for the answer about excel borders Smile | :)

My first requirement is that I have a table in which I have kept a set of sql queries in a column and I have an SSIS package which will run 4 times a week, say it runs on Monday, Tuesday, Wednesday and Friday. On Monday it fetches a set of query, on Tuesday it fetches different set of query and vice-versa for Wednesday and Friday. For time being I used ExecuteScalar() to fetch query
Collapse | Copy Code
 
Try
cmdCommand.CommandText = strSQL.ToString()
cmdCommand.CommandType = CommandType.Text
cmdCommand.Connection = ConnOledb
drReader = Nothing
newReader = Nothing
cmdCommand.CommandText = newReader.ToString()
cmdCommand.CommandType = CommandType.Text
newReader = cmdCommand.ExecuteReader()
If newReader.HasRows Then
dtDetails.Load(newReader)
End If
newReader.Close()

drReader = cmdCommand.ExecuteReader()
If drReader.HasRows Then
dtDetails.Load(drReader)
End If
drReader.Close()

Catch ex As Exception
drReader = Nothing
End Try
 

but if i have to fetch two rows of data ,in my case I need to fetch two queries and execute it then how do I do it?

Is my requirement clear enough?

 
Thanks.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

you can try using Excel Package. its quite simple to use
 
put all the data that you pull into a datatable then count the number of rows.
Imports OfficeOpenXml
 
Using rng As ExcelRange = ws.Cells(1, 1, 1, data.Columns.Count - 1) 'ws.Cells("A1:K1")
rng.Style.Border.Left.Style = Style.ExcelBorderStyle.Thin
rng.Style.Border.Left.Color.SetColor(Color.Black)
 
 End Using
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 5,170
1 DamithSL 4,357
2 Maciej Los 3,750
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,851


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 7 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100