Dear Alex,
I'm using Excel to report exclusively (no Cristal Reports). Admittedly I have not come across sophisticated reports like you seem to have but I have been using this approach in vfp extensively and for many years as well and I have never felt that I was missing anything. I know how Cristal Report adds columns and rows and merges cells in such a way that it becomes almost unusable as an excel spreadsheet. I have had written many excel programs to "disenclave" this cristal report garbage in order to use the data in the spreadsheets.
Excel in VB.Net is slightly more intricate than in VFP, but once you solve the problems, it seems to work very similarly.
I'm copying my "clsExcel.vb" class here for you to check out. If you implement new, dispose, finalize and releaseobject in the same way as I have, you will find that the rest is really wrapping Excel commands. I have developed these wrapping methods in an "as needed" manner. There is room for improvement here, undoubtedly.
Ah. Don't forget, to instantiate the class you need to use the "using" construct.
using o as clsExcel
some code
end using
Have a look, and ask if there something that is not clear. Hopefully I can answer your questions.
Hope this helps,
Marc
Imports Microsoft.Office.Interop
Class clsExcel
Implements IDisposable
Public oApp As New Excel.Application
Public oWBs As Excel.Workbooks
Public oWB As Excel.Workbook
Public Selection As Excel.Range
Public originalCulture As System.Globalization.CultureInfo
Dim thisThread As System.Threading.Thread
Dim lQuit As Boolean = True
Dim lDisposed As Boolean = False
Public cPrinter As String
Public nCopies As Integer
Public cXlsRoot As String
Sub New()
Me.thisThread = System.Threading.Thread.CurrentThread
Me.originalCulture = thisThread.CurrentCulture
Me.thisThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
Me.oWBs = Me.oApp.Workbooks
Me.oApp.DisplayAlerts = False
End Sub
Sub Dispose() Implements IDisposable.Dispose
If Me.lDisposed Then Exit Sub
If lQuit Then Me.oApp.Quit()
Me.releaseObject(Me.oWB)
Me.releaseObject(Me.oWBs)
Me.releaseObject(Me.oApp)
Me.thisThread.CurrentCulture = Me.originalCulture
Me.lDisposed = True
End Sub
Protected Overrides Sub finalize()
Me.Dispose()
End Sub
Public Sub releaseObject(ByVal o As Object)
If o Is Nothing Then Exit Sub
Runtime.InteropServices.Marshal.FinalReleaseComObject(o)
o = Nothing
End Sub
Public Function openWorkbook(ByVal cPath) As Excel.Workbook
Me.oWB = Me.oWBs.Open(cPath, , True)
Return Me.oWB
End Function
Public Sub setString(ByVal cRng As String, ByVal cValue As String)
Me.setString(cRng, cValue, 0)
End Sub
Public Sub setstring(ByVal cRng As String, ByVal cValue As String, ByVal nRow As Integer)
Me.oApp.Range(cRng)(nRow + 1) = cValue
End Sub
Public Sub setNum(ByVal cRng As String, ByVal n As Double)
Me.setString(cRng, n, 0)
End Sub
Public Sub setNum(ByVal cRng As String, ByVal n As Double, ByVal nRow As Integer)
Me.oApp.Range(cRng)(nRow + 1) = n
End Sub
Public Sub setVisible()
Me.oApp.Visible = True
Me.lQuit = False
End Sub
Public Sub printOut()
If Me.cPrinter = "Preview" Then
Me.setVisible()
Me.saveAs()
Exit Sub
End If
If Me.nCopies = 0 Then
Exit Sub
End If
Me.oWB.PrintOut(, , Me.nCopies, False, Me.cPrinter)
End Sub
Public Sub saveAs()
If modB040Config.lPreviewSaves = False Then Exit Sub
Dim c As String = modB040Config.cDocsFolder & Me.cXlsRoot
Dim c1 As String : c1 = c & ".xls"
If IO.File.Exists(c1) Then
Dim n As Integer : n = 1
Do
c1 = (c & "_" & Right("000" & n, 3) & ".xls")
n += 1
Loop Until IO.File.Exists(c1) = False
End If
Me.oWB.SaveAs(c1)
End Sub
Public Sub pasteToRange(ByVal cRange As String, ByVal c As String)
Me.pasteToRange(cRange, c, 2)
End Sub
Public Sub pasteToRange(ByVal cRange As String, ByVal c As String, ByVal nOffset As Integer)
Clipboard.SetText(c)
Me.oApp.Range(cRange)(nOffset).Select()
Me.oApp.ActiveSheet.paste()
End Sub
Public Sub fmt2Dec(ByVal cRange As String, ByVal nLines As Integer)
Dim nCol As Integer : nCol = Me.oApp.Range(cRange).Column
Dim nRow As Integer : nRow = Me.oApp.Range(cRange).Row
With Me.oApp
.Range(.Cells(nRow + 1, nCol), .Cells(nRow + nLines, nCol)).NumberFormat = "#,##0.00"
End With
End Sub
Public Sub fmt2Dec(ByVal cRange As String)
Me.fmt2Dec(cRange, 0)
End Sub
Public Sub fmt2Dec()
Me.Selection.NumberFormat = "#,##0.00"
End Sub
Public Function nUsedrangeRow()
Return Me.oApp.ActiveSheet.usedrange.rows.count
End Function
Public Function nUsedRangeColumn()
Return Me.oApp.ActiveSheet.usedrange.columns.count
End Function
Public Sub borderSingle()
Dim nRow As Integer = Me.nUsedrangeRow
Dim nCol As Integer = Me.nUsedRangeColumn
With Me.oApp
.Range(.Cells(nRow, 1), .Cells(nRow, nCol)).Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
End With
End Sub
Public Sub borderSingle(ByVal nRow)
Dim nCol As Integer = Me.nUsedRangeColumn
With Me.oApp
.Range(.Cells(nRow, 1), .Cells(nRow, nCol)).Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
End With
End Sub
Public Sub borderDouble()
Dim nRow As Integer = Me.nUsedrangeRow()
Dim nCol As Integer = Me.nUsedRangeColumn()
With Me.oApp
.Range(.Cells(nRow, 1), .Cells(nRow, nCol)).Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
.Range(.Cells(nRow, 1), .Cells(nRow, nCol)).Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlDouble
End With
End Sub
Public Sub sumRange(ByVal cRange As String, ByVal nLines As Integer)
Dim nCol As Integer : nCol = oApp.Range(cRange).Column
Dim nRow As Integer : nRow = oApp.ActiveSheet.usedrange.rows.count + 1
oApp.Cells(nRow, nCol).formular1c1 = "=sum(R[" & -nLines & "]C:R[-1]C)"
End Sub
Public Sub nameRange(ByVal cName As String, ByVal nRow As Integer, ByVal nCol As Integer)
oApp.Cells(nRow, nCol).select()
oApp.Names.Add(cName, oApp.Selection)
End Sub
Public Sub justifyRight(ByVal cRange As String, ByVal nOffset As Integer)
oApp.Range(cRange)(nOffset + 1).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End Sub
Public Sub justifyRight()
Me.Selection.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
End Sub
Public Sub fmtBold(ByVal cRange As String)
oApp.Range(cRange).Font.Bold = True
End Sub
Public Sub fmtBold()
Me.Selection.Font.Bold = True
End Sub
Public Sub selectRange(ByVal nRow As Integer, ByVal nCol As Integer)
Me.oApp.Cells(nRow, nCol).select()
Me.Selection = Me.oApp.Selection
End Sub
Public Sub selectRange(ByVal nRow1 As Integer, ByVal nCol1 As Integer, ByVal nRow2 As Integer, ByVal nCol2 As Integer)
With Me.oApp
.Range(.Cells(nRow1, nCol1), .Cells(nRow2, nCol2)).Select()
End With
Me.Selection = Me.oApp.Selection
End Sub
Public Function nRow(ByVal cRange As String) As Integer
Return Me.oApp.Range(cRange).Row
End Function
Public Function nColumn(ByVal cRange As String) As Integer
Return Me.oApp.Range(cRange).Column
End Function
Public Sub insertRows(ByVal nRow As Integer, ByVal nRows As Integer)
Me.oApp.Rows(nRow + 1 & ":" & nRow + nRows).select()
Me.oApp.Selection.insert(Excel.XlDirection.xlDown, 1)
End Sub
Sub fitLastPage(ByVal nMinLines As Integer)
Dim s As Excel.Worksheet = Me.oApp.ActiveSheet
Dim nPageBreaks As Integer = s.HPageBreaks.count
If nPageBreaks = 0 Then Exit Sub
Dim nLastPagebreak = s.HPageBreaks(nPageBreaks).Location.Row - 1
Dim nUsedRows As Integer = Me.nUsedrangeRow - 1
If nUsedRows - nLastPagebreak < nMinLines Then
With s.PageSetup
.Zoom = False
.FitToPagesTall = nPageBreaks
End With
End If
End Sub
End Class>Hi all,
> 1. i use vb.net , write a crystal report. I want to write a export function to export excel. i know crystal report build-in export excel function .
>But if i use this method, many data and column heading move to other column , how can i do ?
> 2. if i think use crystal report export to text file first, then read the text file and use code generate new excel file, but crystal report can't
>export to text file , i try export to doc ,excel, rtf format and use notepad open ,but many many strange code, how can i do?
> 3. The last method, i want to use dataset to use sql , because my crystal report. group by 2 feilds, many many data to group the first.
>After i also use code to generate excel .......
>
>After , i need to write many many export excel function in other table , In 1,2,3 method , i want to know among method performance is better and
>easy to handle, Anyone can give some idea to me, thank you .......
If things have the tendency to go your way, do not worry. It won't last. Jules Renard.
