Macro FCSpreadSheet Extract

Macro_FCSpreadsheet_Extract
Description
This macro saves spreadsheet data in to a CSV file with the formula or in to an XML file. Latest version runs only with FC v0.18 and higher (PySide2 Qt5) For the previous version of this macro, see Macro_FCSpreadSheet_Extract.FCMacro and install it manually. Macro version: 0.3 Last modified: 2020-03-14 FreeCAD version: 0.18 or higher Download: ToolBar Icon Author: Mario52
Author
Mario52
Download
ToolBar Icon
Links
Macros recipes How to install macros How to customize toolbars
Macro Version
0.3
Date last modified
2020-03-14
FreeCAD Version(s)
0.18 or higher
Default shortcut
None
See also
None

Description

This macro saves spreadsheet data in to a CSV file with the formula or in to an XML file.

Usage

1. Install the macro via the Addon Manager
2. Execute the macro

Script

The icon for the toolbar to be placed in the same directory of the macro

Macro_FCSpreadSheet_Extract.FCMacro

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
"""
***************************************************************************
*   Copyright (c) 2018 2019 2020 <mario52>                                *
*                                                                         *
*   This file is a supplement to the FreeCAD CAx development system.      *
*                                                                         *
*   This program is free software; you can redistribute it and/or modify  *
*   it under the terms of the GNU Lesser General Public License (LGPL)    *
*   as published by the Free Software Foundation; either version 2 of     *
*   the License, or (at your option) any later version.                   *
*   for detail see the LICENCE text file.                                 *
*                                                                         *
*   This software is distributed in the hope that it will be useful,      *
*   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
*   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
*   GNU Library General Public License for more details.                  *
*                                                                         *
*   You should have received a copy of the GNU Library General Public     *
*   License along with this macro; if not, write to the Free Software     *
*   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  *
*   USA                                                                   *
***************************************************************************
*           WARNING! All changes in this file will be lost and            *  
*                  may cause malfunction of the program                   *
***************************************************************************
"""
#05/05/2018 14/03/2020
#
__title__   = "FCSpreadSheet_Extract"
__author__  = "Mario52"
__url__     = "https://www.freecadweb.org"
__Wiki__    = "https://wiki.freecadweb.org/Macro_FCSpreadSheet_Extract"
__version__ = "00.03"
__date__    = "2020/03/14" #YYYY/MM/DD


#### Test FreeCAD.Version above 0.17 BEGIN ##########################################################################################################
import WebGui
#WebGui.openBrowser("http://www.freecadweb.org/")
try:
    import PySide
    from PySide import QtGui
    import sys, os
    def errorDialog(msg):
        diag = QtGui.QMessageBox(QtGui.QMessageBox.Critical,u"Error FreeCAD Version",msg )
        diag.setWindowFlags(PySide.QtCore.Qt.WindowStaysOnTopHint) # PySide #cette fonction met la fenetre en avant
        diag.setStyleSheet("QLabel {color : #ffffff; background-color : #FF3300;  font: bold}")
        WebGui.openBrowser("https://gist.githubusercontent.com/mario52a/29f7dce6934e8adf5d584bdddd2e469f/raw/ef0b0de56a65afc34a9a1a969969797910f3a004/Macro_FCSpreadSheet_Extract.FCMacro" + "\n\n")
        diag.exec_()
        diag.setStyleSheet("Base")

    if int(FreeCAD.Version()[1]) < 18:      # Version of FreeCAD
        memo = PySide.QtGui.QClipboard()    # Copy the title in to the clipBoard
        memo.setText(__title__)    
        FreeCAD.Console.PrintMessage("https://gist.githubusercontent.com/mario52a/29f7dce6934e8adf5d584bdddd2e469f/raw/ef0b0de56a65afc34a9a1a969969797910f3a004/Macro_FCSpreadSheet_Extract.FCMacro" + "\n\n")
        errorDialog("This version " + __title__ + " works with FreeCAD v0.18 or higher. " + "\n\n"
                    "PySide2 Qt version: 5" + "\n\n"
                    "The macro it was derived from (30/04/2018 for FC v0.17) is displayed in your FreeCAD browser" + "\n\n"
                    "copy and paste it into the FCSpreadSheet_Extract macro or install it manually." + "\n\n"
                    "If the code is not displayed in the FreeCAD browser page," + "\n"
                    "copy the URL displayed in the Report View")
except Exception:
    None
#### Test FreeCAD.Version above 0.17 END ############################################################################################################

import PySide2
from PySide2 import QtWidgets, QtGui ,QtCore 
from PySide2.QtGui import *
from PySide2.QtCore import *
import copy
import re


path = FreeCAD.ConfigGet("UserAppData")
#path = Your path

global ESCAPE
global tableau
global data

########## separator 
ESCAPE = "\t"           #tabulation    #compatible FC
#ESCAPE = ","            #virgule       #compatible FCInfo
#ESCAPE = ";"            #point virgule #option
#ESCAPE = " "            #espace        #option
#ESCAPE = ""             #vide          #option
##########

def decodeColonne(colonne = "A"):                                  # converti la chaine A ... ZZ en numero de colonne ex: A = 1; AA = 27
    colonne  = re.split('[0-9]+', colonne, flags=re.IGNORECASE)[0] # supp the alphanumeric number ex: A2 = A; A12 = A (1A return 0)
    try:
        if len(colonne) > 1:
            return ((ord(colonne[0].upper()) - 64) * 26 ) + (ord(colonne[1].upper()) - 64) # max 2 car (AAAA return 27)
        else:
            return (ord(colonne.upper()) - 64 )
    except Exception:
        return 0

def decodeOccupation(dataTableau = ""):  # decode the max occupation Colonnes, Lines and give the cell occupation
    #
    #lineMax, colonneMax, cellsOccupation = decodeOccupation(FreeCAD.ActiveDocument.getObject(str(Sheet.Name)))
    #
    try:
        #tyty = unicode(dataTableau.cells.Content, "utf-8")
        tyty = dataTableau.cells.Content
        tyty = tyty.split(">")

        ####
        cellsOccupation = [] 
        cellsSorted     = []                                                 # search the "A1" definition
        for i in tyty[1:-2]:                                                 # split les cases dans la chaine XML
            i = i[i.find('"')+1:]
            i = i[:i.find('"')]
            cellsOccupation.append(i)

        cellsSorted = copy.deepcopy(cellsOccupation)
        cellsSorted.sort()

        ####
        linesMax = 0
        for i in cellsSorted:                                                # recherche le max (ligne et colonne)
            colonnesMax = re.split('[0-9]+', i, flags=re.IGNORECASE)         # colonne max (AA)
            try:
                dummy   = int(re.split('[A-Z]+', i, flags=re.IGNORECASE)[1]) # line
                if dummy > linesMax:
                    linesMax = dummy                                         # lines max
            except Exception:
                None
        del cellsSorted[:]
        ####
        return linesMax, decodeColonne(colonnesMax[0]), cellsOccupation      # return linesMax , colonnesMax, cellsOccupation

    except Exception:
        FreeCAD.Console.PrintError("Error data, Enter object <Sheet object> ex:" + "\n")
        FreeCAD.Console.PrintError("lineMax, colonneMax, cellsOccupation = decodeOccupation(FreeCAD.ActiveDocument.getObject(str(Sheet.Name)))" + "\n")
        return 0, 0, []

def caseTableau(ligne = 1, colonne = 1):                                     # calcule et code la case du tableur ex: 1,1 = A1; 7,7 = G7
    #
    # print caseTableau( 1, 1)
    #
    if ligne < 1: ligne = 1
    if ligne > 16384: ligne = 16384
    if colonne < 1: colonne = 1
    if colonne > 702: colonne = 702
    if (colonne > 26):
        if abs(colonne % 26) == 0:
            return chr(64 + (abs(colonne / 26) -1)) + chr(90) + str(ligne)
        else:
            return chr(64 + (abs(colonne / 26))) + chr(64 + (abs(colonne % 26))) + str(ligne)
    else:
        return chr(colonne + 64) + str(ligne)

def saveDataInFile(data = ""):
    global tableau

    SaveName, Filter = PySide2.QtWidgets.QFileDialog.getSaveFileName(None, "Save a file txt", path, "CSV (*.csv);;XML (*.xml)") # PySide

    if (SaveName == ""):
        App.Console.PrintMessage("Process aborted"+"\n")
    else:
        App.Console.PrintMessage("Registration of "+SaveName+"\n")
        try:
            file = open(SaveName, 'w')
            try:
                if Filter == "XML (*.xml)":
                    ext = "(*.xml)"
                    file.write(tableau.cells.Content)          # save XML
                else:
                    ext = "(*.csv)"
                    for i in data:
#                        file.write(unicode(i).encode("utf-8")) # save CSV
                        file.write(i) # save CSV

                        print(i)

            except Exception:
                App.Console.PrintError("Error write file "+ext+"\n")
            finally:
                file.close()
        except Exception:
            App.Console.PrintError("Error Open file "+SaveName+"\n")

##tableau.getContents("A2")                             #lire une celulle
##print App.ActiveDocument.Spreadsheet.cells.Content    #lire toutes les celulles # XML

try:
    for i in FreeCAD.ActiveDocument.Objects:               # search SpreadSheet in all object
        obj = FreeCAD.ActiveDocument.getObject(i.Name).TypeId.split("::")[0]
        if obj == "Spreadsheet":
            tableau = FreeCAD.ActiveDocument.getObject(str(i.Name))
            print( "Name : ",i.Name," ; Label : ",i.Label)
            break                                          # quit to the first SpreadSheet
    
    linesMax    = decodeOccupation(tableau)[0]             # linesMax
    colonnesMax = decodeOccupation(tableau)[1]             # colonnesMax
    #print decodeOccupation(tableau)[2]                    # display the cells occupied
    
    print( "Number cell : ", linesMax * colonnesMax," (", linesMax," L x ", colonnesMax," C)")
    FreeCADGui.updateGui()                                          # rafraichi l'ecran
    
    dataToSave = []
    for i in range(1, linesMax+1):
        for ii in range(1, colonnesMax+1):
    #        dataToSave.append(unicode(tableau.getContents(caseTableau(i,ii)), 'utf-8') + ESCAPE)
            dataToSave.append(tableau.getContents(caseTableau(i,ii)) + ESCAPE)
        dataToSave.append("\n")
    
    saveDataInFile(dataToSave)
    del dataToSave[:]
    
    print( "__Fin FCSpreadSheet_Extract__________\n")
except Exception:
    print ("No SpreadSheet in the document or Error")