0

Good afternoon, we have a small problem with performance of generating excel.

  • First, we was creating excel cell by cell - it is ... let's say unacceptable.
  • Second, we started insert into excel with one command - range creating and it is much faster, but still not perfect so we are searching next solutions.

Because we can load XML file from database, we tried used XSLT and from these two files create xls file. It is nice, but after open this file there is error message shown (it is because of problem or bug in registry). User has to accept this message and after excel is opened. We want to eliminate this error message. However we don't know how.

We was thinking about convert this xls file into xlsx but we are unable to do it becouse we can't install office on server (we cannot use Interop) and OpenXML libraries don't know work with normal xls file. So my question is:

  1. Is possible to generate from XML file with using of some XLST (or something) the xlsx file?
  2. Eventually can what files do we need to create and zip together if we want create xlsx file?

Thank you for information

Ademar
  • 1,418
  • 1
  • 17
  • 27
  • 2
    Do you need special formatting or anything? If not, you can create a simple .csv file. – itsme86 Jul 03 '14 at 16:09
  • 1
    Using the COM interface for excel is going to be MUCH slower than just writing the file. I would recommend reading about excel file formats or creating sample files till you know exactly how to create the file. Then create it directly in code (don't use automation/COM) – drew_w Jul 03 '14 at 16:11
  • may be your error is related to TypeGuessRows http://madbuildertools.blogspot.fr/2013/08/import-from-excel-mixed-colums-issue.html – tschmit007 Jul 03 '14 at 16:16
  • Sorry but I can't understand why you can't produce xlsx. Do you have to read xls to produce the output file ? – tschmit007 Jul 03 '14 at 16:18
  • @tschmit007, yes, this is it probably, but I cannot change it on client's machines. And not, I don't have to - let's say I have data in database and I want to save it to excel - but solutions above are too slow so I am searching some next and faster. – Ademar Jul 03 '14 at 16:29

1 Answers1

1

You mention not being able to use the OpenXML libraries because they don't work with .xls files, but you also say "creating cell by cell", which implies that you are generating the file from scratch. Where is the xls file coming from? You mention excel opening, but then say you can't install it on the server. So, it appears to me that a user is uploading an xls file to your server, and then you are doing something with it and giving it back to them? If that is the case and you must be able to read/write an xls file without installing office, then I would suggest using ExcelLibrary, as mentioned in this post

Indeed, creating an xlsx file is much magnitudes faster with the open xml sdk.

Community
  • 1
  • 1
Justin Killen
  • 728
  • 6
  • 19
  • Alternatively if it's only the data you're after, you could also just connect to it using ODBC. A few example connection strings can be found at https://www.connectionstrings.com/excel/ – Justin Killen Jul 03 '14 at 16:40
  • If I creates excel cell by cell it creates xlsx file. When I was searching faster way to do it, I have try load xml from database and create xls file from it. (XML + XSLT = xls - it is from where it came from). So after I was trying to convert it to xlsx. About instalation - When I was trying to run application using interop it didn't find needed libriaries, so I was thinking that I can't use it. If I understand correctly, I can upload application with interop clesses in usage without office instaled on server? (Importnat is office on client's computer? If yes it can solve my problem-probably – Ademar Jul 03 '14 at 17:21
  • If you use the standard interops, then they are COM automation wrappers to the excel program so excel must be installed. Basically your program opens excel and sends all the commands to it and excel does all the actual work. This are slow and notoriously problematic. The much better way is to create an xlsx file using the open xml sdk. The open xml sdk deals directly with the file, so no excel installation is needed, only the runtime libraries for open xml itself (I'm pretty sure you bundle these with your application, but I'm not 100%). – Justin Killen Jul 03 '14 at 17:28
  • Also worth mentioning is third party libraries like ExcelLibrary where created to try to avoid the COM automation. Now that the open xml sdk is available first party, there is no need to use the third party libraries with the exception that the open xml sdk does not support the legacy xls format. – Justin Killen Jul 03 '14 at 17:39