为OOO Calc电子表格中的每一行创建新的txt/html文件 [英] Create new txt/html file for each row in OOO Calc spreadsheet

问题描述

我想从Calc电子表格的每一行创建一个新的txt文件(实际上是html文件).我从Excel电子表格中找到了一些可以做到这一点的答案，但是它们不适用于Calc.

I would like to create a new txt file (html file really) from each row of a Calc spreadsheet. I have found a few answers that do this from an Excel spreadsheet but they do not work with Calc.

我没有Excel.我正在使用OOO 4.

I do not have Excel. I am using OOO 4.

我在Calc中尝试了此Excel宏，但收到错误-范围是未知数据类型.研究这一点似乎表明Excel宏在OOO Calc中不能很好地工作.我发现有话说，如果我在选项中启用了可执行代码"，Excel宏可能会起作用，但这无济于事.下面的宏仅假设2列，我试图以此作为起点.

I tried this Excel macro in Calc but get an error - Range is an unknown data type. Researching this seems to say that Excel macros won't work well in OOO Calc. I had found something saying if I enabled 'Executable Code' in Options, Excel macros may work, but that didn't help. The Macro below assumes only 2 columns, I was trying to get it to work as a starting point.

将Excel行输出到一系列文本文件

Sub Export_Files()
Dim sExportFolder, sFN
Dim rArticleName As Range
Dim rDisclaimer As Range
Dim oSh As Worksheet
Dim oFS As Object
Dim oTxt As Object

'sExportFolder = path to the folder you want to export to
'oSh = The sheet where your data is stored
sExportFolder = "C:\Disclaimers"
Set oSh = Sheet1

Set oFS = CreateObject("Scripting.Filesystemobject")

For Each rArticleName In oSh.UsedRange.Columns("A").Cells
    Set rDisclaimer = rArticleName.Offset(, 1)

    'Add .txt to the article name as a file name
    sFN = rArticleName.Value & ".txt"
    Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
    oTxt.Write rDisclaimer.Value
    oTxt.Close
Next
End Sub

我的工作表有多列(举例来说，我们可以说6列).我想用第1列中的值命名每个文件，然后让文件本身包含每个其他列的内容-每个都在新行中.理想情况下，这将适用于空单元格(将只是空白行)，因此我可以根据需要添加新的空白行以分隔内容.

My sheet has multiple columns (we can say 6 for example purposes). I would like to name each file with the value in Column 1, and then have the file itself contain each additional columns' content - each on a new line. Ideally this would work with empty cells (would just be a blank line) so I could add new blank lines as needed to separate stuff.

工作表有400行，所以我希望最终以400个文件结尾-每行一个.

The sheet has 400 rows, so I want that to end up as 400 files - one for each row.

电子表格包含来自CMS的博客内容-我查询数据库以获取标题，摘要，正文，类别等，并将其放入excel电子表格中.因此，给定单元格中的某些内容可能很长，并且包含html.其中也包含逗号和制表符，因此是Excel Spreadsheet，而不是CSV.

The spreadsheet contains the content of my blog from a CMS - I queried the database to get the title, summary, body, categories etc and put those into the excel spreadsheet. So some of the content in a given cell may be very long and contain html. There are also commas and tabs in it, thus the Excel Spreadsheet rather than a CSV.

我的目标是使用我可以从中得到的单个文件输入Jekyll来重新创建我的博客.我知道Jekyll有进口商，但是我的CMS不是其中之一(DNN/Ventrian).我没有找到直接将excel工作表导入jekyll的方法.

My goal is to use the individual files I can get out of this to feed into Jekyll to recreate my blog. I am aware there are importers for Jekyll, but my CMS wasn't one of them (DNN/Ventrian). I didn't see a way to import an excel sheet directly into jekyll.

我希望所有数据都导出到文件中-整个工作表不是某个范围或任何范围.

I want all data to export into the files - it is not a certain range or anything, the whole sheet.

推荐答案

这似乎可行-尽管有时会出错，我认为是因为我用作文件名的值有问题.当我使用它时，我的i = 1到50，更改它可以控制执行的行数.有一些注释掉的Print语句有助于查看发生的情况-它们只是在弹出窗口中打印到屏幕上.

This seems to work - though it sometimes errors, I think because the value that I am using as the filename has a problem. I have i = 1 to 50 while I play with it, changing it controls how many rows it does. There are a couple of commented out Print statements that were helpful in seeing what was going on - they just print to the screen in a little pop up.

对此进行了以下修改: https://forum.openoffice.org /en/forum/viewtopic.php?t=34074

This is modified from: https://forum.openoffice.org/en/forum/viewtopic.php?t=34074

  sub saveas2
 Dim oDocOptions(0) as New com.sun.star.beans.PropertyValue

'stores the document objects
Dim oDoc as Object
Dim oSheet as Object
Dim sSavePath as String
Dim sFileName as string
Dim sFullPath as String
Dim sSaveLink as String
Dim oSaveOptions(1) as New com.sun.star.beans.PropertyValue
Dim c as Integer
 for c = 1 to 50

'stores the script doc's open settings


'setup the settings to open the crm script file with
 oDocOptions(0).Name = "Hidden"
 oDocOptions(0).Value = True
 ' print c
 ' print (thisComponent.getSheets.getByName("Query").getCellRangeByName("E" & c).getString)
 'open a blank spreadsheet to build the script with
  oDoc = starDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, oDocOptions())

  'setup the first sheet of the new doc
  oSheet = oDoc.sheets().getByIndex(0)

  'print (thisComponent.getSheets.getByName("Query").getCellRangeByName("E" & c).getString)
   oSheet.getCellRangeByName("A6").String = (thisComponent.getSheets.getByName("Query").getCellRangeByName("A" & c).getString)   
   oSheet.getCellRangeByName("A1").String = (thisComponent.getSheets.getByName("Query").getCellRangeByName("E" & c).getString)
   oSheet.getCellRangeByName("A2").String = (thisComponent.getSheets.getByName("Query").getCellRangeByName("C" & c).getString)
   oSheet.getCellRangeByName("A3").String = (thisComponent.getSheets.getByName("Query").getCellRangeByName("F" & c).getString)
   oSheet.getCellRangeByName("A4").String = (thisComponent.getSheets.getByName("Query").getCellRangeByName("G" & c).getString)

  'rebuild the options to save CRM doc with


  'set the options to save the file
   oSaveOptions(0).Name = "FilterName"
   oSaveOptions(0).Value = "Text - txt - csv (StarCalc)" 'THIS ALLOWS IT TO BE SAVED AS A TXT FILE
   oSaveOptions(1).Name = "FilterOptions"
   oSaveOptions(1).Value = "59,0,11,1," 'THIS IS THE FORMATTING I USED TO ACHIEVE THE NO DELIMITER AND ASCII FORMAT

   'store the save location


    sFileName = (thisComponent.getSheets.getByName("Query").getCellRangeByName("A" & c).getString)
    sSavePath = "j:\test\"
    sFullpath = sSavePath & sFileName & ".html"
    'Print sFullPath

   'build the link of the file to get
    sSaveLink = ConvertToURL(sFullPath)

    'save the CRM script file
  oDoc.storeAsURL(sSaveLink , oSaveOptions())

  'close the CRM script file
   oDoc.Close(True)

   next c
   end sub

这篇关于为OOO Calc电子表格中的每一行创建新的txt/html文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！