Creating borders for excel

Question

4.00/5 (1 vote)

See more:

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) :

SQL

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

SQL

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 :)
Sadiq

Posted 3-Jan-13 2:17am

SadiqMohammed

Updated 6-Jan-13 20:58pm

__TR__

v2

Add a Solution

2 solutions

Add a Solution

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

joe_j · Answer 1 · 2013-01-07T02:28:00

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.

VB

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

Debasis.logica · Answer 2 · 2013-01-06T03:54:00

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.

Posted 6-Jan-13 3:54am

Debasis.logica

Comments

Maciej Los 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 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.

Creating borders for excel

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0

Creating borders for excel

2 solutions

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us