Macro FCSpreadSheet Extract

From FreeCAD Documentation
Revision as of 18:47, 7 July 2019 by Mario52 (talk | contribs) (icon)
Jump to navigation Jump to search
Other languages:
English • ‎français • ‎italiano

Macro FCSpreadsheet Extract.png Macro_FCSpreadsheet_Extract

Description
This macro save the spreadsheet data in a csv file with the formula or in a xml file.

Macro version: 0.1
Last modified: 2018-05-05
FreeCAD version: 0.17 and below
Download: ToolBar Icon
Author
Mario52
Download
ToolBar Icon
Links
Macro Version
0.1
Date last modified
2018-05-05
FreeCAD Version(s)
0.17 and below
Default shortcut
None
See also
None


Description

This macro save the spreadsheet data in a csv file with the formula or in a xml file.

Use

Run the macro

Script

The icon for the tool bar to place in the same directory of the macro Macro FCSpreadsheet Extract.png

Macro_FCSpreadSheet_Extract.FCMacro

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
"""
***************************************************************************
*   Copyright (c) 2018 <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                   *
***************************************************************************
"""
#
__title__   = "FCSpreadSheet_Extract"
__author__  = "Mario52"
__url__     = "http://www.freecadweb.org/index-fr.html"
__Wiki__    = "https://www.freecadweb.org/wiki/index.php?title=Macro_FCSpreadSheet_Extract"
__version__ = "00.01"
__date__    = "05/05/2018"

import PySide
import re
from PySide import QtGui ,QtCore
from PySide.QtGui import *
from PySide.QtCore import *
import copy

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 = 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)
            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)
            dummy   = int(re.split('[A-Z]+', i, flags=re.IGNORECASE)[1])     # line
            if dummy > linesMax:
                linesMax = dummy                                             # lines max
        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 = PySide.QtGui.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
            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("\n")
    
    saveDataInFile(dataToSave)
    del dataToSave[:]
    
    print "__Fin FCSpreadSheet_Extract__________\n"
except Exception:
    print "Not SpreadSheet in the document or Error"