Using PutCell function from GetExcel.lsp v/s creating csv file with write lines to write a lot of data into excel.

[ShricharanaB]

Hi, I have doubt. 

Back story:

I've created a BOM extractor (without using the -DATAEXTRACT command). The end goal is to have a BOM created with a single click. The said BOM has different components (different block types in the drawing) in different sheets in single excel file, with the relevant data of those components via parameters and attributes. 

Initially, I made it so that the data is written to different csv files for what is supposed to be different sheets in the final excel, then create a final excel sheet and combine all the csv files into that final sheet using a Excel VBA macro and save it. I got it working completely as I need, which takes one button click to create a final BOM file with all sheets.

Then I thought why create all those files and then run a macro through Excel VBA - which might take more time- when I can write the data directly to a single excel file in different sheets. So I used PutCell function (and the functions this requires) in the GetExcel.lsp and achieved this. Its working a hundred percent, still with a single button click.

Now here is my doubt. 

The PutCell version of my code takes more than double the time to create the final BOM file than the csv combining version of the code. ( PutCell version ~ 38 s, CSV version ~ 15 s run on the same AutoCAD file)

Now I've narrowed this delay down to the PutCell function itself (which is used as is in the GetExcel.lsp). Is there any faster way to write data to excel directly? 

Here is how I'm calling the PutCell function. (See attached GetExcel.lsp for full PutCell function)

(foreach catg compLst
		
    	(setq compType (car catg))
    	(setq catg (cdr catg))
    	(addsheet compType acwb excel-app)
    	(setq rowNum "A1")
  	(repeat (length catg)
    		(PutCell rowNum (car catg) excel-app)
    		(setq catg (cdr catg))
    		(setq rowNum (Row+n rowNum 1))
			
 	)
		(setq ipb (+ perc ipb) count (1+ count))
		(acet-ui-progress-safe ipb)
    )

 Here is how CSV is being written.

(foreach record dblist	;repeating for each item in dblist (first is the header and the rest are blocks and attribtues)
		(setq i 0 text "" )
		(while (< i (length record))	;repeating for each item in the record i.e each attribute of record block
			(setq val (cond ((nth i record)) (""))
				text (strcat text val)
				i	 (1+ i)
			)
				(if (< i (length record))
				(setq text (strcat text delimit))
			)
		)
		(write-line text file_w);creating the csv file
	)