将Excel样式表导出为CSV，逗号分隔，单元格为双引号的文本格式

Example - Source:

示例 - 来源:

ID  NAME   TEXT  
01  John   Lore ipsum..

In this case all cells have format General and Lore ipsum.. text have format Text

在这种情况下,所有单元格都具有格式General和Lore ipsum ..文本格式为Text

And I want export this excel stylesheet to csv with comma separated and lore ipsum.. text with double quotes, something like this:

我希望将这个excel样式表导出到带有逗号分隔的csv和带有双引号的lore ipsum ..文本,如下所示:

ID,NAME,TEXT  
01,John,"Lore ipsum.."

1 个解决方案

#1

The following Microsoft article details the procedure you're looking for; http://support.microsoft.com/kb/291296 In the spirit of SO, I'll summarise here.

以下Microsoft文章详细介绍了您正在寻找的过程; http://support.microsoft.com/kb/291296本着SO的精神,我将在这里总结一下。

In Excel (I'm using 2003), navigate to Tools > Macro > Visual Basic Editor. That will launch the editor in a new window.

在Excel(我正在使用2003)中,导航到工具>宏> Visual Basic编辑器。这将在新窗口中启动编辑器。

Next you want Insert > Module. In that window, paste the VB code, as follows:

接下来,您需要插入>模块。在该窗口中,粘贴VB代码,如下所示:

Sub QuoteCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub

Next you'll need to highlight the cells in your spreadsheet that you want to export. Once they've been selected, Run your macro against them. Specify a path to save your file to, and you're done.

接下来,您需要突出显示要导出的电子表格中的单元格。一旦选中它们,就可以针对它们运行宏。指定保存文件的路径,然后就完成了。

Full credits to the author of the article at Microsoft, http://support.microsoft.com/kb/291296.

完全归功于Microsoft的文章作者http://support.microsoft.com/kb/291296。

#1