0

I have been using both libreoffice and excel and I can run a vbs that opens excel invisibly and does this, but my company is going to move over to libreoffice. The issue with libreoffice, which is nice enough to have a built in converter, is that it does not separate sheets, as in, it only reads the front sheet. For single sheet workbooks I have been successful in using LO, but in multi-sheet xls or even ods, it is useless. I don't mind using java, powershell, command prompt, vbs, etc, I just want to use something that is more or less native to win7 and my company. I know java is not win7 native, but more or less everyone has it, so I count it in.

user3377627
  • 363
  • 3
  • 7
  • 22
  • Are you willing to do some programming here? And what degree of automation do you require? I.e. does this need to be something that runs from the command line or in unattended contexts? What's the end goal you're trying to get to here? There are various libraries and programs that can read and convert xls/xlsx; would help to know more about your use case. – Andrew Janke Apr 20 '15 at 06:40
  • In our data center we support various facilities in the eastern seaboard and require having a lot of info on hand. I made a powershell run utility that grabs data from various sources, facilitates other functions and runs other special utilities that may be needed from time to time. It automates a lot of what is done. What I currently have, works fine with excel, but like stated before, we will be leaving that software. It compares the xls source file to the converted csv file and if the xls is newer, it archives the current csv and converts the xls into a new csv file with the same name. – user3377627 Apr 20 '15 at 07:47
  • The source files are updated as necessary and to keep my utility's data up to date, if a user sees a discrepancy, they can refresh my utility, an option available to them in my utility, which currently runs the batch script that runs a vbs script that updates the compares and updates the csv files as necessary (some things are easier done in one over the other and somethings just can be done in cmd) and then refreshes the utility with the updated info. Like I said, vbs works fine with methods that can be used with excel, but aside from using an outside CLI utility, that may require approval – user3377627 Apr 20 '15 at 07:51
  • before being able to use, I'd rather use anything already native to win7, or normally found in win7, like Java. Currently Libreoffice only offers a CLI parameter to convert only the front page/sheet of a workbook. This is not useful to me for some of these source files. In the end the entire process of comparing and contrasting the src and destination files, as well as the conversion of any chosen worksheet in a workbook, needs to be automated through a script. – user3377627 Apr 20 '15 at 07:56

1 Answers1

0

Sounds like you need to move from VBS that is "automating" the Excel application itself, to a small program that uses an Excel-reading library to parse the XLS files.

There's no such library "native" to Windows 7 in the sense that it ships with the stock OS. (Office doesn't either, for that matter.) But there are lots of libraries out there for it, including some that build on free redistributables from Microsoft. Sounds like C#/VB.Net or Java would both be good languages for you. There's probably even VBS bindings for some of this.

So go browsing for libraries. This is already covered in a lot of places, because tons of people want to extract data from Excel like this. Apache POI is a well-known one for Java, and you can find others browsing through similar SO or forum questions.

If you want to fully automate the process, you'll have better luck writing a program using a library like this instead of trying to script a desktop application with inadequate support.

Reading Excel files from C#

How to read and write excel file in java

You could even dig through the LibreOffice source code to see what library they're using for importing Excel. (Surely their full Calc program does a better job at importing Excel files than that converter utility.)

https://www.libreoffice.org/about-us/source-code/

Community
  • 1
  • 1
Andrew Janke
  • 23,508
  • 5
  • 56
  • 85
  • Thanks for the java mention. Exe, jar, etc need to be approved, but a .java/.class does not. I know .jar is more of a file archive, but anything that is one click and run, like an exe, they like to going through a promotion process. This is why I mostly have kept my utilities as ps1, bat and vbs. I never really thought much about java, but I'll look into that. – user3377627 Apr 20 '15 at 09:06