如何在Excel单元格内使用逗号同时保留逗号作为分隔符（VB实现）

I see exactly what's going on here—you're trying to use commas both as your CSV delimiter and as part of your cell values, which is causing ExcelPackage to split those comma-containing cells into multiple columns incorrectly. The fix involves properly formatting your raw data to follow CSV standards and configuring LoadFromText to recognize quoted fields.

Root Cause #

Right now, when you build your rawData string, you're concatenating values with commas without escaping any commas that exist inside the values themselves. ExcelPackage's default LoadFromText behavior treats every comma as a delimiter, so any value with a comma gets split into separate cells.

Step-by-Step Solution #

1. Add a Helper Function to Escape Values #

First, create a helper function that wraps values in double quotes if they contain commas (or double quotes, which also need escaping in CSV). This follows the official CSV specification for handling delimiters within fields.

Private Function EnquoteIfNeeded(ByVal value As String) As String
    If value Is Nothing Then Return ""
    
    ' Check if the value contains commas or double quotes
    If value.Contains(",") Or value.Contains("""") Then
        ' Escape existing double quotes by doubling them (CSV standard)
        value = value.Replace("""", """""")
        ' Wrap the value in double quotes
        Return """" & value & """"
    End If
    
    Return value
End Function

2. Modify Raw Data Construction #

Update your code that builds the rawData string to use this helper function for each field:

' Replace your existing rawData line with this:
stationI.rawData += EnquoteIfNeeded(rawFileArray(i)) & "," & _
                    EnquoteIfNeeded(stationI.stationName) & "," & _
                    EnquoteIfNeeded(stationI.region) & vbNewLine

3. Configure LoadFromText to Recognize Quoted Fields #

When calling LoadFromText, explicitly set TextLoadOptions to tell ExcelPackage to respect double-quoted fields and use commas as delimiters. Update both your header and data load calls:

' For the header row
Dim headerLoadOptions As New TextLoadOptions() With {
    .Delimiter = ",",
    .Quote = """",
    .HasHeaders = True
}
wsRaw.Cells("A1").LoadFromText(stationsHeader, headerLoadOptions)

' For the data rows
Dim dataLoadOptions As New TextLoadOptions() With {
    .Delimiter = ",",
    .Quote = """",
    .HasHeaders = False
}
wsRaw.Cells("A2").LoadFromText(stationToLoad, dataLoadOptions)

Why This Works #

• The helper function ensures any value with commas is wrapped in double quotes, which tells the CSV parser to treat the entire quoted string as a single cell.
• The TextLoadOptions explicitly tells ExcelPackage to use commas as delimiters and double quotes as field wrappers, aligning with standard CSV behavior.
• Double quotes inside values are escaped by doubling them (e.g., O"Neil becomes "O""Neil"), which is the correct way to handle quotes in CSV.

内容的提问来源于stack exchange，提问作者dkettle01