crawford Posted July 25, 2023 Share Posted July 25, 2023 Hi Everyone, I am trying to automate exporting and modifying worksheet data into a specific format to be imported to a label printing software. The end product needs to be a CSV, I have already created a python script that accomplishes this by modifying an excel sheet of the worksheet. The next step is getting this to run directly in VW and skip that middle step but I am not sure how to approach accessing the worksheet directly in vw. Here is what I have that runs externally, the end will hopefully be necessary since I will need less interface inside vw. Is this something that is possible? Thanks! Ian Script Sample.txt Quote Link to comment
Pat Stanford Posted July 25, 2023 Share Posted July 25, 2023 I don't think there is a way to automate the export of a worksheet to either a CSV or Excel file. But accessing the data is really not that hard and you could then write your own CSV file exactly how you need it and avoid the post processing. If you don't mind struggling through a little Vectorscript, the attached thread contains a script that reads the data (and the forumula) in every cell of a worksheet (actually 2 worksheets). This should give you what you need to extract the data. Then all you need is to use the File Handling functions to write that extracted data back out into a file. Ask again if you need more help. 1 Quote Link to comment
tbexon Posted August 1, 2023 Share Posted August 1, 2023 So if I'm reading this correctly you want to be able to Run a command in VWX that will export a set of data as a CSV to then import this into your label printer software? First question is where is the Data coming from? Is the data in an already existing worksheet or do you need to get it yourself? If the latter is the case you have a couple of options: You can either build a worksheet that collects the data for you or you can get the data yourself using the ForEachObjectInLayer() command to cycle through all the relevant objects and retrieve the data you need. You would then write this data to a worksheet (have a look at these commands). You can then export the worksheet to CSV. Below is the code I use to achieve this. It will export the CSV to the specified directory. def exportWStoCSV(ws, FilePath, **kwargs): ''' subRowNo: specify sub row to extract data from HeaderRowNo: States which row the Column Headers are in :param ws: string :param FilePath: string :param kwargs: :return: ''' import csv try: h = ws vs.TargetSprdSheet(h) # Makes ws active vs.RecalculateWS(h) # recalculates the worksheet except: return False if 'subRowNo' in kwargs: if 'subRowNo' in kwargs: subrowNo = kwargs['subRowNo'] subRowCount = vs.GetWSSubrowCount(h, subrowNo) # Counts the total Sub Rows in Row 2 subRowCountRange = list(range(subRowCount + 1)) # Creates a itertable range for N.o of subRows zz = subRowCountRange.pop() # Removes the last value from list to account for 0 index number_of_rows_and_cols = list(vs.GetWSRowColumnCount(h)) # Counts total Rows and Columns in Worksheet totalColumnsRange = list(range(number_of_rows_and_cols[1])) # Creates a itertable range for N.o of columns totalRowRange = list(range(number_of_rows_and_cols[0])) # Creates a itertable range for N.o of rows if 'HeaderRowNo' in kwargs: headerRow = kwargs['HeaderRowNo'] # States which row the Column Headers are in else: headerRow = 1 # States which row the Column Headers are in headersList = [vs.GetWSCellStringN(h, headerRow, eachColumn + 1) for eachColumn in totalColumnsRange] # Creates a list with all the Column Headers if 'subRowNo' in kwargs: list_of_cells = [[vs.GetWSSubrowCellString(h, 2, eachColumn + 1, eachSubRow + 1) for eachColumn in totalColumnsRange] for eachSubRow in subRowCountRange] # Creates a List containing a list of each row e.g overalllist = [[Row1],[Row2],[Row3]] else: list_of_cells = [ [vs.GetWSCellStringN(h, eachRow +1, eachColumn + 1) for eachColumn in totalColumnsRange] for eachRow in totalRowRange] # Creates a List containing a list of each row e.g overalllist = [[Row1],[Row2],[Row3]] list_of_cells.pop(0) # Removes header row try: with open(FilePath, 'w', newline='', encoding='utf-8') as csvfile: # opens or Creates CSV Called Loom Data writer = csv.writer(csvfile) writer.writerow(headersList) # Writes the Headers to the file. for eachList in list_of_cells: writer.writerow(eachList) # Writes each Row to CSV return True except Exception as e: vs.AlrtDialog("Error Occured, CSV Not Written! Error Message to follow.") vs.AlrtDialog(str("Error Msg: {}".format(e))) If you're collecting the data yourself don't necessarily need to write the data to a worksheet you could just build the data into the required format for csvwriter. However I find for a better user experience it's good to show them the data within VWX aswell. Hope that's helpful! 2 Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.