Pour the data in SQLRead into Excel

zhaozj2021-02-16  90

When writing a web-based reporter program, print this block, I originally used "Crystal Report" to do, but unfortunately, I understand the crystal report software is a known semi-solving, I can only see if I can't print. Sustard. Transfer to Excel to do a report.

The following is the source code of the example:

'First quote Excel 10.0 object library in the project

Imports Excelimports System.DataImports System.Data.sqlclientpublic Class Cycbbcx

......

'Define a SqldataReader Dim Dtcyjjc2 as SqlClient.sqldataReader

...... Private Sub Button2_Click (Byval E AS System.EventArgs) Handles Button2.Click Me.cmdsql2 = New SqlClient.sqlCommand () me.cmdsql2.connection = me.con

Me.cmdsql2.CommandText = "SELECT cyjmc, zsj1.zsjmc as zsjmc1, zsj2.zsjmc as zsjmc2, zsj3.zsjmc as zsjmc3, zsj4.zsjmc as zsjmc4, tcrq, ycyl, kfcw FROM cyjmcs, zsj1, zsj2, zsj3, zsj4 where (cyjmcs.zsjid1 = zsj1.zsjid) and (cyjmcs.zsjid2 = zsj2.zsjid) and (cyjmcs.zsjid3 = zsj3.zsjid) and (cyjmcs.zsjid4 = zsj4.zsjid) "me.con.open () dtcyjc2 = me .cmdsql2.ExecuteReader Dim strSource As String Dim strDst As String 'dim On Error Resume Next strSource = Server.MapPath ( ".// templet / cyjjc.xls")' xyzrb.xls template file is strDst = Server.MapPath ( ". //temp/temp.xls ") 'Temp.xls is to open the target file filecopy (strsource, strdst)" to create an Excel object

'XlApp = New Excel.Application () xlApp.DisplayAlerts = False' without warning xlApp.Application.Visible = False 'does not display interface xlApp.Workbooks.Open (strDst) Dim xlAppBook = xlApp.ActiveWorkbook Dim xlAppSheets = xlApp.Worksheets Dim XLAPPSHEET = xlapp.sheets (1) 'DIM XLAPPSHEET1 = xlapp.activecell.cells.Range Dim DTReader As SqldataReader Dim i, J AS Integer i = 3dim biaoti as string = "Oil Mine" "Generation Title with XLAPPSHEET

.Cells (1, 1) = (Biaoti)

End with while dtcyjjc2.read ()

WITH XLAPPSHEET 'for i = 8 to 17' for J = 1 to 2 '.Cells (i, j) .Font = CSNG (DTReader.Item (J - 1)) .Cells (i, 1) = (dtcyjjc2 (" Cyjmc ")) .Cells (i, 2) = (DTcyjjc2 (" ZSJMC1 ")) .Cells (i, 3) = (dtcyjjc2 (" ZSJMC2 ")) .Cells (i, 4) = (DTcyJJC2 (" ZSJMC3 " ))) .Cells (i, 5) = (DTcyjMC2 ("ZSJMC4")) .Cells (i, 6) = (DTcyjjc2 ("Ycyl")) .Cells (i, 7) = (DTcyjjc2 ("KFCW")) .Cells (i, 8) = (DTCYJJC2 ("TCRQ")) i = i 1

End With End While xlApp.ActiveWorkbook.Save () 'xlApp.ActiveWorkbook.WebPagePreview () xlApp.Workbooks.Close ()' Open (strDst) xlApp.Quit () xlAppBook = Nothing xlAppSheets = Nothing xlAppSheet = Nothing

XLAPP = Nothing dtcyjjc2.close ()

Me.con.close ()

'The following code is used to open a new window, display the generated Excel file in the new window

Dim scriptString As String = "