C# and excel

  • Thread starter Thread starter Carlitos
  • Start date Start date
C

Carlitos

Hello guys,
I am quite new to C# and the .NET environment. I am working in visual C#
2005 profesional edition and excel 2003. I have several excel file templates.
This files are being used and manipulated to display invoices, etc. The data
is comming from sql server 2005.
I want to be able to work with these templates using C#. I want to open and
manipulate these files.
I need information on how to accomplish this. Any site to read about this?
Any information how is the best way to do this? Any comment will be
appreciated.
Thanks
 
Carlitos said:
Hello guys,
I am quite new to C# and the .NET environment. I am working in visual C#
2005 profesional edition and excel 2003. I have several excel file templates.
This files are being used and manipulated to display invoices, etc. The data
is comming from sql server 2005.
I want to be able to work with these templates using C#. I want to open and
manipulate these files.
I need information on how to accomplish this. Any site to read about this?
Any information how is the best way to do this? Any comment will be
appreciated.
Thanks

I can post some vb.net code that you might be able to adapt.
 
Carlitos said:
Doofy,
If you don't mind. Thanks.

Ok, this ought to give you several different ways to writing to cells.
You have to pull in Microsoft.Office.Interop to work with the excel files.

I was writing to one spreadsheet, but was doing it in a number of ways
to different worksheets. On one worksheet, I was having to put info
into discrete cells of a specifically formatted display. Not just row
and column, but at specific spots. Thus all the row and column
variables defined here at the beginning of the code.

In other parts I was putting info in with a header row then a data row.
In another place I was putting info in with a header column then a
data column.

If you look at Sub get_chart_bar_Promo_Code(), I was re-dimensioning
the data series information for a bar chart, i.e. changing how many rows
were used by the chart. This was particularly annoying because I had to
feed info to some intermediate objects (series and ranges I believe) and
then feed into the final object, instead of just being able to do one
long multi-layered object call on one line (with the delimiting
periods). (hope that makes sense)

In the function add_date_to_Chart, I was appending the date to the
chart's title.

In the Sub get_Chart_Pipeline_PTD_Enroll(), I was reading the field name
from the database and writing it, then writing the field value below it.
I did this so I could read any table structure, and not just some
predefined field sequence. You could adapt that to add any number of
rows beneath the column header. I was just writing one row.

So, depending on what you want to do, you should find at least something
here.

This is a console app. No forms.

Have fun. I'll try to keep checking the thread in case you have questions.





Imports Microsoft.Office.Interop
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Imports System.IO


Module Scorecard_VB2
Dim cn As SqlConnection
Dim strConn, strSQL As String
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rng As Excel.Range

Dim rdr As SqlDataReader
Dim as_of_date As Date



Dim col2006 As Integer = 3
Dim col2007 As Integer = 4
Dim col2008 As Integer = 5
Dim colPTD As Integer = 6 'might not use of all tally across

Dim colMonth_3 As Integer = 8
Dim colMonth_2 As Integer = 9
Dim colMonth_1 As Integer = 10

Dim colWeek_1 As Integer = 12
Dim colWeek_2 As Integer = 13
Dim colWeek_3 As Integer = 14
Dim colWeek_4 As Integer = 15


'Notes
Dim rowNotes_Info As Integer = 7
Dim rowNotes_Enroll As Integer = 8
Dim rowNotes_Enroll_Status As Integer = 9
Dim rowNotes_Price As Integer = 10
Dim rowNotes_Billing As Integer = 11
Dim rowNotes_Website As Integer = 12
Dim rowNotes_Other As Integer = 13

'Enrollment Status - Cumulative
Dim rowEnroll_Info As Integer = 17
Dim rowEnroll_Web As Integer = 18
Dim rowEnroll_Reply As Integer = 19
Dim rowEnroll_Phone As Integer = 20

Dim rowEnroll_Cum_Sig_Pending As Integer = 22
Dim rowEnroll_Cum_PreQual As Integer = 23
Dim rowEnroll_Cum_CMO_Req As Integer = 24
Dim rowEnroll_Cum_CMO_Req_Pend As Integer = 25
Dim rowEnroll_Cum_CMO_Completions As Integer = 26
Dim rowEnroll_Cum_Rate_Chg_Req As Integer = 27
Dim rowEnroll_Cum_Rate_Chg_Sched As Integer = 28


'cumulative Active
Dim rowEnroll_Cum_Active As Integer = 29

'Enrollment Status - Snapshot
Dim rowEnroll_Snap_Active As Integer = 29



Dim rowEnroll_Snap_Rejected As Integer = 31
Dim rowEnroll_Snap_CMO_Pending As Integer = 32
Dim rowEnroll_Snap_CMO_Compl As Integer = 33
Dim rowEnroll_Snap_Rate_Chg_Req As Integer = 34
Dim rowEnroll_Snap_Active_Next_Bill As Integer = 35
Dim rowEnroll_Snap_Other As Integer = 36
Dim rowEnroll_Snap_Term_Before As Integer = 37
Dim rowEnroll_Snap_Term_After As Integer = 38

'Load Guard Events
Dim rowLG_10_Hours As Integer = 40
Dim rowLG_10_Days As Integer = 41
Dim rowLG_14_Hours As Integer = 42
Dim rowLG_14_Days As Integer = 43

'Load Guard Signups
Dim rowLG_Signups As Integer = 45

'charts monthly
Dim colJan As Integer = 2
Dim colFeb As Integer = 3
Dim colMar As Integer = 4
Dim colApr As Integer = 5
Dim colMay As Integer = 6
Dim colJun As Integer = 7
Dim colJul As Integer = 8
Dim colAug As Integer = 9
Dim colSep As Integer = 10
Dim colOct As Integer = 11
Dim colNov As Integer = 12
Dim colDec As Integer = 13

Dim rowTitles As Integer = 100
Dim rowEnrolling As Integer = 101
Dim rowActive As Integer = 102

'Enrollment charts
Dim colPTD_Signups As Integer = 2
Dim colActive_PTD As Integer = 3
Dim colReply_Card As Integer = 4

Dim colWeb_Signups As Integer = 5
Dim colPhone_Signups_sig_Received As Integer = 6
Dim colPending_Qual As Integer = 7
Dim colRequest_Installation As Integer = 8
Dim colPending_Installation As Integer = 9
Dim colInstalled As Integer = 10
Dim colRequested_Rate_Change As Integer = 11
Dim colPending_Rate_Change As Integer = 12
Dim colActive_Next_Bill_Date As Integer = 13
Dim colRejected As Integer = 14
Dim colTerminate_Before_Active As Integer = 15
Dim colTerminate_After_Active As Integer = 16
Dim colRequest_Termination As Integer = 17
Dim colPending_Termination As Integer = 18


Sub Main(ByVal args() As String)
'arg should be FROM file name
Dim newFileName As String
Dim fromName As String
Dim cmdLineVar As Boolean
Dim fso As New FileStream("c:\scorecard_infile.txt",
FileMode.Open, FileAccess.Read)
Dim srReader As New StreamReader(fso)


'if file names given on
Try

cmdLineVar = False

Catch ex As Exception
cmdLineVar = True
End Try

fromName = srReader.ReadLine()


newFileName = Left(fromName, fromName.Length - 4) & "_" &
Year(Now) & "_" & Month(Now) & "_" & Day(Now) & ".xls"



End Sub


Sub copy_Scorecard_Template(ByVal fromName As String, ByVal toName
As String)
Try
File.Copy(fromName, toName, True)
Catch ex As DirectoryNotFoundException
Console.WriteLine(fromName & " not found.")
End Try

End Sub




Sub make_DB_Connection_RRTP()
strConn = "Data Source=Sql
Server;Server=bogus;Database=bogusDB;user=bogus;password=boguspw"
cn = New SqlConnection(strConn)
Try
cn.Open()
'Console.Write("Connection Attempt Succeeded")
Catch ex As SqlException
'Console.Write("Connection Attempt Failed")
'Console.Write(" {0}", ex.Message)
Return
End Try
End Sub
Sub open_Scorecard_Spreadsheet(ByVal fileName As String)
xl = New Excel.Application
Try
xl.Workbooks.Open(fileName)
Catch ex As Exception
'Console.WriteLine("File not found.")
End Try
End Sub


Sub get_RRTP_Scorecard_Cumulative_Data()
strSQL = "Select * from RRTP_Scorecard"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()
Try
ws = xl.Worksheets("Finished Scorecard")

Do While rdr.Read()

Select Case rdr("Order_No")
Case 1 '2006
write_Finished_Scorecard_Cumulative(col2006)

Case 2 '2008
write_Finished_Scorecard_Cumulative(col2008)
as_of_date = rdr("end_date")
ws.Range("A2").Value = rdr("end_date")

Case 3 'PTD -
write_Finished_Scorecard_Cumulative(colPTD)

Case 4 ' month -3
write_Finished_Scorecard_Cumulative(colMonth_3)
'write month h4
ws.Range("h4").Value = rdr("Column_title")

Case 5 ' month -2
write_Finished_Scorecard_Cumulative(colMonth_2)
'write month i4
ws.Range("i4").Value = rdr("Column_title")

Case 6 ' month -1
write_Finished_Scorecard_Cumulative(colMonth_1)
'write month j4
ws.Range("j4").Value = rdr("Column_title")

Case 7 ' week -1
write_Finished_Scorecard_Cumulative(colWeek_1)
ws.Range("l3").Value =
Month(rdr("start_date")).ToString & "/" & Day(rdr("start_date")).ToString
ws.Range("l5").Value =
Month(rdr("end_date")).ToString & "/" & Day(rdr("end_date")).ToString

Case 8 ' week -2
write_Finished_Scorecard_Cumulative(colWeek_2)
ws.Range("m3").Value =
Month(rdr("start_date")).ToString & "/" & Day(rdr("start_date")).ToString
ws.Range("m5").Value =
Month(rdr("end_date")).ToString & "/" & Day(rdr("end_date")).ToString


Case 9 ' week -3
write_Finished_Scorecard_Cumulative(colWeek_3)
ws.Range("n3").Value =
Month(rdr("start_date")).ToString & "/" & Day(rdr("start_date")).ToString
ws.Range("n5").Value =
Month(rdr("end_date")).ToString & "/" & Day(rdr("end_date")).ToString

Case 10 ' week -4
write_Finished_Scorecard_Cumulative(colWeek_4)
ws.Range("o3").Value =
Month(rdr("start_date")).ToString & "/" & Day(rdr("start_date")).ToString
ws.Range("o5").Value =
Month(rdr("end_date")).ToString & "/" & Day(rdr("end_date")).ToString


Case 11 ' This week - not written
Case 12 'last year
write_Finished_Scorecard_Cumulative(col2007)
End Select

Loop

rdr.Close()


Catch ex As Exception
'Console.WriteLine("Reader not reading RRTP_Scorecard"
& ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()

End Try
End Using
End Sub



Sub get_Chart_Active_Participants()
'active
strSQL = "Select * from RRTP_Scorecard_Monthly where category =
'active' and year = '" & Year(as_of_date) & "'"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()

Try
'ws = xl.Worksheets("Finished Scorecard")
rdr.Read()

For a As Integer = colJan To colDec
If (a - 1) <= Month(as_of_date) Then
ws.Cells(rowActive, a).value =
rdr(ws.Cells(rowTitles, a).value)
End If
Next

rdr.Close()


Catch ex As Exception
'Console.WriteLine("Reader not reading
RRTP_Scorecard_Monthly" & ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
End Using

'Enrolling
strSQL = "Select * from RRTP_Scorecard_Monthly where category =
'Enrolling' and year = '" & Year(as_of_date) & "'"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()
Try
'ws = xl.Worksheets("Finished Scorecard")
rdr.Read()

For a As Integer = colJan To colDec
If (a - 1) = Month(as_of_date) Then
ws.Cells(rowEnrolling, a).value =
rdr(ws.Cells(rowTitles, a).value)
End If
Next

rdr.Close()


Catch ex As Exception
'Console.WriteLine("Reader not reading
RRTP_Scorecard_Monthly" & ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
End Using
add_date_to_Chart()



End Sub



Sub get_Chart_YTD_Monthly_Enroll()
'active
strSQL = "Select * from RRTP_Scorecard_Monthly where category =
'Enrollments' and year = '" & Year(as_of_date) & "'"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()

Try

rdr.Read()

For a As Integer = colJan To colDec
If (a - 1) <= Month(as_of_date) Then
ws.Cells(rowActive, a).value =
rdr(ws.Cells(rowTitles, a).value)
End If
Next

rdr.Close()


Catch ex As Exception
'Console.WriteLine("Reader not reading
RRTP_Scorecard_Monthly" & ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
End Using

'Enrolling
strSQL = "Select * from RRTP_Scorecard_Monthly where category =
'Enrollments Cumulative' and year = '" & Year(as_of_date) & "'"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()
Try
'ws = xl.Worksheets("Finished Scorecard")
rdr.Read()

For a As Integer = colJan To colDec
If (a - 1) <= Month(as_of_date) Then
ws.Cells(rowEnrolling, a).value =
rdr(ws.Cells(rowTitles, a).value)
End If
Next

rdr.Close()


Catch ex As Exception
'Console.WriteLine("Reader not reading
RRTP_Scorecard_Monthly" & ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
End Using
add_date_to_Chart()



End Sub



Sub get_Chart_Pipeline_PTD_Enroll()
'In this query, pending install = pending install + request for
install + Util Qual
strSQL = "Select * from vwRRTP_Scorecard_PTD_Enrollment_Status"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()

Try

rdr.Read()
For a = 1 To rdr.FieldCount
'write in field name
ws.Cells(rowTitles, a + 1).value = rdr.GetName(a -
1).ToString
ws.Cells(rowEnrolling, a + 1).value =
rdr.GetValue(a - 1)

Next
Catch ex As Exception
'Console.WriteLine("Reader not reading
vwRRTP_Scorecard_YTD_Enrollment_Status" & ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
rdr.Close()

End Using
add_date_to_Chart()
End Sub


Sub get_chart_bar_Promo_Code()
Dim rowCnt As Integer
Dim oSeries As Excel.Series


strSQL = "Select * from RRTP_Pie_Promo_Code_Enrollment order by
cnt asc"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()

Try
rowCnt = rowEnrolling
Do While rdr.Read()
'write promo code
If Len(rdr("Promo_code").ToString) = 0 Then
ws.Cells(rowCnt, 2).value = "Unassigned"
Else
ws.Cells(rowCnt, 2).value = Trim(rdr("Promo_Code"))
End If

'write count
ws.Cells(rowCnt, 4).value = rdr("cnt")

rowCnt += 1

Loop

'create signup total formula two rows below list
ws.Cells(rowCnt + 2, 4).value = "=Sum(d" & rowEnrolling
& ":d" & rowCnt - 1 & ")"
rdr.Close()

'create percentage values
For a = rowEnrolling To rowCnt - 1
ws.Cells(a, 6).value = "=(d" & a & "/d" & rowCnt +
2 & ")"

Next
'set data range
oSeries = ws.ChartObjects(1).chart.seriescollection(1)
'names
oSeries.XValues = ws.Range("b" & rowEnrolling, "b" &
rowCnt - 1)
'values
oSeries.Values = ws.Range("f" & rowEnrolling, "f" &
rowCnt - 1)

Catch ex As Exception
xl.Workbooks.Close()
End Try
End Using
add_date_to_Chart()
End Sub


Sub get_Chart_Pie_Enrollment_Source()
Dim rowCnt As Integer

'In this query, pending install = pending install + request for
install + Util Qual
strSQL = "Select enrolled_By, cnt from RRTP_Pie_Enrolled_By"
Using cmd As New SqlCommand(strSQL, cn)
rdr = cmd.ExecuteReader()
Try
rowCnt = rowTitles
Do While rdr.Read()

'write in field name
rowCnt += 1

ws.Cells(rowCnt, 2).value = rdr("enrolled_BY")
ws.Cells(rowCnt, 3).value = rdr("cnt")

Loop

Catch ex As Exception
'Console.WriteLine("Reader not reading Pie_Enrolled_By"
& ex.Message)
'Console.ReadLine()
xl.Workbooks.Close()
End Try
rdr.Close()

End Using
add_date_to_Chart()
End Sub


Sub open_Worksheet(ByVal wsName As String)

ws = xl.Worksheets(wsName)

End Sub

Sub close_Scorecard()
Dim wkb As Excel.Workbook
For Each wkb In xl.Application.Workbooks
wkb.Save()
Next wkb


xl.Workbooks.Close()
xl = Nothing


End Sub


Sub add_date_to_Chart()
If InStr(ws.ChartObjects(1).chart.charttitle.text, "/") = 0 Then
ws.ChartObjects(1).chart.charttitle.text =
ws.ChartObjects(1).chart.charttitle.text & " (as of " &
Format(CDate(Left(as_of_date.ToString, InStr(as_of_date.ToString, " ") -
1)), "MMM dd yyyy") & ")"

End If
End Sub
End Module
 
Back
Top