asked on

is there a way to format a .CSV file created with a vb.net app?

Hi,

I'm trying to find out if there is a way to format a .CSV file created with a vb.net app. The code works in that it writes the file, but I would like to have the Headers in bold and I'm not sure if that can be done, or if so how to do it. I'll include my code. If someone could look over it and tell me if there is a way to do this I would really appreciate it. Thanks!

'File Header line
        vHTML = vHTML.Append("Department,")
        vHTML = vHTML.Append("Course,")
        vHTML = vHTML.Append("Term,")
        vHTML = vHTML.Append("Author,")
        vHTML = vHTML.Append("ISBN,")
        vHTML = vHTML.Append("Title,")
        vHTML = vHTML.Append("Edition,")
        vHTML = vHTML.Append("Publisher,")
        vHTML = vHTML.Append("Publication Date,")
        vHTML = vHTML.Append("Is New,")
        vHTML = vHTML.Append(vbCrLf)
 
        Myconn.Open()
        Mycomm = New SqlCommand(s, Myconn)
        Try
            dtr = Mycomm.ExecuteReader()
            If dtr.HasRows Then
                Success = True
                While dtr.Read
                    Dim vDept, vCourse, vTerm, vAuthor, vISBN, vTitle, vEditor, vPublisher, vPubDate, vIsNew
                    vDept = Replace(dtr("Department"), "''", "'")
                    vDept = Replace(vDept, ",", "")
                    vCourse = Replace(dtr("Course"), "''", "'")
                    vCourse = Replace(vCourse, ",", "")
                    vTerm = Replace(dtr("Term"), "''", "'")
                    vTerm = Replace(vTerm, ",", "")
                    vAuthor = Replace(dtr("Author"), "''", "'")
                    vAuthor = Replace(vAuthor, ",", "")
                    vISBN = Replace(dtr("ISBN"), "''", "'")
                    vISBN = Replace(vISBN, ",", "")
                    vTitle = Replace(dtr("Title"), "''", "'")
                    vTitle = Replace(vTitle, ",", "")
                    vEditor = Replace(dtr("Editor"), "''", "'")
                    vEditor = Replace(vEditor, ",", "")
                    vPublisher = Replace(dtr("Publisher"), "''", "'")
                    vPublisher = Replace(vPublisher, ",", "")
                    vPubDate = Replace(dtr("PublicationDate"), "''", "'")
                    vPubDate = Replace(vPubDate, ",", "")
                    vIsNew = Replace(dtr("IsNew"), "''", "'")
                    vIsNew = Replace(vIsNew, ",", "")
 
                    vHTML = vHTML.Append(Trim(vDept))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vCourse))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vTerm))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vAuthor))
                    vHTML = vHTML.Append(",=""")
                    vHTML = vHTML.Append(Trim(vISBN))
                    vHTML = vHTML.Append(""",")
                    vHTML = vHTML.Append(Trim(vTitle))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vEditor))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vPublisher))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vPubDate))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(Trim(vIsNew))
                    vHTML = vHTML.Append(",")
                    vHTML = vHTML.Append(vbCrLf)
                End While
            Else
                Success = False
                Myconn.Close()
                lblLink.Text = "No Results Found"
            End If
        Catch ex As Exception
            Success = False
            'Response.Write(sql & "<br>")
            Response.Write(ex.Message & "<br>")
        End Try
        dtr.Close()
        Myconn.Close()
        Dim fileExcel As String
        Dim nRandom As Random = New Random(DateTime.Now.Millisecond)
        fileExcel = "t" & nRandom.Next().ToString()
        Dim vFileName = "ExcelFiles/" & fileExcel & "_books.csv"
        Try
            Dim FILENAME As String = Server.MapPath(vFileName)
            Dim objStreamWriter As StreamWriter
            objStreamWriter = File.CreateText(FILENAME)
            objStreamWriter.WriteLine(vHTML)
            objStreamWriter.Close()
            lblLink.Visible = True
            lblLink.Text = "Excel file located <a href='" & vFileName & "'target=""_blank"">here</a>."
        Catch
            'Response.Write("<p><strong>An Error Occurred:</strong> " & ex.Message & "</p>" & vbCrLf)
            'If you get an error, make sure the directory has write permissions!
            lblStatus.Visible = True
            lblStatus.Text = "An error occurred attempting to produce the Excel file."
        End Try

Open in new window

ASKER CERTIFIED SOLUTION

cdbeste

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cdbeste

CSV.. Which means Comma Seperated Values

are generally used to transfer data from
one application to another..

cdbeste

Here is a method to convert CSV to XLS

add this call to the end of your function

ConvertCSVtoXL "C:\YourDocs\YourFile.csv"

Public Sub ConvertCSVtoXL(strCSVPath As String)
 
Dim appExcel As Excel.Application
 
'Switch to Microsoft Excel so it won't go away when you finish.
    On Error Resume Next
    AppActivate "Microsoft Excel"
    
    'If Excel isn't running, start and activate it
    If Err Then
    Shell "c:\Program Files\Microsoft Office\Office\" _
        & "Excel /Automation", vbHide
    AppActivate "Microsoft Excel"
    End If
    On Error GoTo 0
    
    'Get an Application object so you can automate Excel.
    Set appExcel = GetObject(, "Excel.Application")
 
    With appExcel
    
    .Workbooks.Open FileName:=strCSVPath
 
    ' here is where you can apply formatting
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
 
    ActiveWorkbook.SaveAs FileName:=Left(strCSVPath, Len(strCSVPath) - 3) & "xls" _
        , FileFormat:=xlNormal
    End With
    
    appExcel.Quit
Set appExcel = Nothing
    
    MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
    "filename with an XLS extension"
 
End Sub

Open in new window