def print_UP_list(UP_list, UP_meta_info, base):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet
    
    filename = 'UP_print.xlsx'
    wb = Workbook(encoding='mac_roman')
    ws = Worksheet(wb, title = 'UP_list') #creating a sheet inside the workbook
    ws.freeze_panes = 'A2'
    header = ['#', 
              'UP name', 
              'unit', 
              'country', 
              'infrastructure']
    for i in range(6):
        header.append('Category ' + str(i))
    ws.append(header)
    
    for i in range(len(UP_list)):
        UP = UP_list[i]
        line = [i + base, 
                UP_list[i], 
                UP_meta_info[UP]['unit'], 
                UP_meta_info[UP]['Country'], 
                UP_meta_info[UP]['Infrastructure']]
        for j in range(6):
            try:
                line.append(UP_meta_info[UP]['Category type'][j])
            except IndexError:
                break
        ws.append(line)
    print 'saving in excel sheet named: ' + filename
    wb.add_sheet(ws)
    wb.save(filename)
Exemplo n.º 2
0
def print_UP_list(UP_list, UP_meta_info, base):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet

    filename = 'UP_print.xlsx'
    wb = Workbook(encoding='mac_roman')
    ws = Worksheet(wb, title='UP_list')  #creating a sheet inside the workbook
    ws.freeze_panes = 'A2'
    header = ['#', 'UP name', 'unit', 'country', 'infrastructure']
    for i in range(6):
        header.append('Category ' + str(i))
    ws.append(header)

    for i in range(len(UP_list)):
        UP = UP_list[i]
        line = [
            i + base, UP_list[i], UP_meta_info[UP]['unit'],
            UP_meta_info[UP]['Country'], UP_meta_info[UP]['Infrastructure']
        ]
        for j in range(6):
            try:
                line.append(UP_meta_info[UP]['Category type'][j])
            except IndexError:
                break
        ws.append(line)
    print 'saving in excel sheet named: ' + filename
    wb.add_sheet(ws)
    wb.save(filename)
Exemplo n.º 3
0
def print_CFs(CF_matrices, EF_list, CF_categories):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet
    
    result_filename = 'CF_print.xlsx'
    wb = Workbook() #creating a workbook
    for method in CF_matrices:
        ws = Worksheet(wb, title = method) #creating a sheet inside the workbook
        ws.freeze_panes = 'D2'
        header = ['compartment', 
              'substance', 
              'subcompartment']
        for category in CF_categories[method]:
            header.append(category)
        ws.append(header)
        for EF in EF_list:
            matrix_column = EF_list.index(EF)
            compartment, substance, subcompartment = EF
            line = [compartment, substance, subcompartment]
            for category in CF_categories[method]:
                matrix_line = CF_categories[method].index(category)
                CF = CF_matrices[method][matrix_line, matrix_column]
                line.append(CF)
            ws.append(line)
    print 'saving in excel sheet named: ' + result_filename
    wb.add_sheet(ws)
    wb.save(result_filename)
Exemplo n.º 4
0
	def create_business_workbook(self):

		workbook = Workbook()

		for xlsx in self.business_xlsx:
			wb = openpyxl.load_workbook(xlsx)
			ws = wb.get_active_sheet()

			if re.search(r'NEW', xlsx):
				ws.title = 'New'
			elif re.search(r'Reopened', xlsx):
				ws.title = 'Reopened'
			elif re.search(r'As', xlsx):
				ws.title = 'As Designed'
			elif re.search(r'FIXED', xlsx):
				ws.title = 'Fixed'
			elif re.search(r'CANCELLED',xlsx):
				ws.title = 'Cancelled'
			elif re.search(r'CLOSED', xlsx):
				ws.title = 'Closed'
			elif re.search(r'UNR', xlsx):
				ws.title = 'Unreproducable'

			workbook.add_sheet(ws)

		self.business_workbook = workbook
Exemplo n.º 5
0
	def create_regular_workbook(self):

		workbook = Workbook()

		for xlsx in self.regular_xlsx:
			wb = openpyxl.load_workbook(xlsx)
			ws = wb.get_active_sheet()

			if re.search(r'Open\+bugs',xlsx):
				ws.title = 'Open'
			elif re.search(r'Resolved\+I', xlsx):
				ws.title = 'Resolved'
			elif re.search(r'Closed\+I', xlsx):
				ws.title = 'Closed'
			elif re.search(r'Open\+QC', xlsx):
				ws.title = 'Open QC'
			elif re.search(r'CANCELLED', xlsx):
				ws.title = 'Cancelled'
			elif re.search(r'Resolved\+QC', xlsx):
				ws.title = 'Resolved QC'
			elif re.search(r'Closed\+QC', xlsx):
				ws.title = 'Closed QC'

			workbook.add_sheet(ws)
		
		self.regular_workbook = workbook
Exemplo n.º 6
0
def print_CFs(CF_matrices, EF_list, CF_categories):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet

    result_filename = 'CF_print.xlsx'
    wb = Workbook()  #creating a workbook
    for method in CF_matrices:
        ws = Worksheet(wb, title=method)  #creating a sheet inside the workbook
        ws.freeze_panes = 'D2'
        header = ['compartment', 'substance', 'subcompartment']
        for category in CF_categories[method]:
            header.append(category)
        ws.append(header)
        for EF in EF_list:
            matrix_column = EF_list.index(EF)
            compartment, substance, subcompartment = EF
            line = [compartment, substance, subcompartment]
            for category in CF_categories[method]:
                matrix_line = CF_categories[method].index(category)
                CF = CF_matrices[method][matrix_line, matrix_column]
                line.append(CF)
            ws.append(line)
    print 'saving in excel sheet named: ' + result_filename
    wb.add_sheet(ws)
    wb.save(result_filename)
Exemplo n.º 7
0
def print_EF_list(EF_list):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet

    result_filename = 'EF_print.xlsx'
    wb = Workbook()  #creating a workbook
    ws = Worksheet(wb, title='EF_list')  #creating a sheet inside the workbook
    ws.freeze_panes = 'A2'
    header = ['compartment', 'substance', 'subcompartment']
    ws.append(header)
    for EF in EF_list:
        ws.append(EF)
    print 'saving in excel sheet named: ' + result_filename
    wb.add_sheet(ws)
    wb.save(result_filename)
def print_EF_list(EF_list):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet
    
    result_filename = 'EF_print.xlsx'
    wb = Workbook() #creating a workbook
    ws = Worksheet(wb, title = 'EF_list') #creating a sheet inside the workbook
    ws.freeze_panes = 'A2'
    header = ['compartment', 
          'substance', 
          'subcompartment']
    ws.append(header)
    for EF in EF_list:
        ws.append(EF)
    print 'saving in excel sheet named: ' + result_filename
    wb.add_sheet(ws)
    wb.save(result_filename)
Exemplo n.º 9
0
def print_mat(MAT,nom):
	from openpyxl.workbook import Workbook
	from openpyxl.worksheet import Worksheet
	from numpy import array
	import csv
	import os
	
	wb = Workbook(encoding = 'mac_roman') #creating a workbook
	ws = Worksheet(wb, title = nom) #creating a sheet inside the workbook
    
	mat=MAT
	for row in range(len(mat)):
		ws.append(mat[row].tolist());

	wb.add_sheet(ws)
	wb.save(nom+'.xlsx')
    	
	os.startfile(nom+".xlsx")
Exemplo n.º 10
0
class ExcelWriter(object):
    """
    Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
    openpyxl for xlsx.  See DataFrame.to_excel for typical usage.

    Parameters
    ----------
    path : string
        Path to xls file
    """
    def __init__(self, path):
        self.use_xlsx = True
        if path.endswith('.xls'):
            self.use_xlsx = False
            import xlwt
            self.book = xlwt.Workbook()
            self.fm_datetime = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:MM:SS')
            self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
        else:
            from openpyxl.workbook import Workbook
            self.book = Workbook(optimized_write = True)
        self.path = path
        self.sheets = {}
        self.cur_sheet = None

    def save(self):
        """
        Save workbook to disk
        """
        self.book.save(self.path)

    def writerow(self, row, sheet_name=None):
        """
        Write the given row into Excel an excel sheet

        Parameters
        ----------
        row : list
            Row of data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        """
        if sheet_name is None:
            sheet_name = self.cur_sheet
        if sheet_name is None:  # pragma: no cover
            raise Exception('Must pass explicit sheet_name or set '
                            'cur_sheet property')
        if self.use_xlsx:
            self._writerow_xlsx(row, sheet_name)
        else:
            self._writerow_xls(row, sheet_name)

    def _writerow_xls(self, row, sheet_name):
        if sheet_name in self.sheets:
            sheet, row_idx = self.sheets[sheet_name]
        else:
            sheet = self.book.add_sheet(sheet_name)
            row_idx = 0
        sheetrow = sheet.row(row_idx)
        for i, val in enumerate(row):
            if isinstance(val, (datetime.datetime, datetime.date)):
                if isinstance(val, datetime.datetime):
                    sheetrow.write(i,val, self.fm_datetime)
                else:
                    sheetrow.write(i,val, self.fm_date)
            elif isinstance(val, np.int64):
                sheetrow.write(i,int(val))
            elif isinstance(val, np.bool8):
                sheetrow.write(i,bool(val))
            else:
                sheetrow.write(i,val)
        row_idx += 1
        if row_idx == 1000:
            sheet.flush_row_data()
        self.sheets[sheet_name] = (sheet, row_idx)

    def _writerow_xlsx(self, row, sheet_name):
        if sheet_name in self.sheets:
            sheet, row_idx = self.sheets[sheet_name]
        else:
            sheet = self.book.create_sheet()
            sheet.title = sheet_name
            row_idx = 0

        conv_row = []
        for val in row:
            if isinstance(val, np.int64):
                val = int(val)
            elif isinstance(val, np.bool8):
                val = bool(val)
            conv_row.append(val)
        sheet.append(conv_row)
        row_idx += 1
        self.sheets[sheet_name] = (sheet, row_idx)
Exemplo n.º 11
0
def test_add_correct_sheet():
    wb = Workbook()
    new_sheet = wb.create_sheet(0)
    wb.add_sheet(new_sheet)
    eq_(new_sheet, wb.worksheets[2])
Exemplo n.º 12
0
def test_add_incorrect_sheet():
    wb = Workbook()
    wb.add_sheet("Test")
Exemplo n.º 13
0
def test_add_invalid_worksheet_class_instance():
    wb = Workbook()
    ws = AlternativeWorksheet(parent_workbook=wb)
    with pytest.raises(TypeError):
        wb.add_sheet(worksheet=ws)
Exemplo n.º 14
0
    try:
        # get workbook-level information
        wb.properties = read_properties_core(archive.read(ARC_CORE))
        try:
            string_table = read_string_table(archive.read(ARC_SHARED_STRINGS))
        except KeyError:
            string_table = {}
        style_table = read_style_table(archive.read(ARC_STYLE))

        # get worksheets
        wb.worksheets = []  # remove preset worksheet
        sheet_names = read_sheets_titles(archive.read(ARC_APP))
        for i, sheet_name in enumerate(sheet_names):
            sheet_codename = 'sheet%d.xml' % (i + 1)
            worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, sheet_codename)

            if not use_iterators:
                new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table)
            else:
                xml_source = unpack_worksheet(archive, worksheet_path)
                new_ws = read_worksheet(xml_source, wb, sheet_name, string_table, style_table, filename, sheet_codename)
                #new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table, filename, sheet_codename)
            wb.add_sheet(new_ws, index = i)

        wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb)
    except KeyError, e:
        raise InvalidFileException(unicode(e))
    finally:
        archive.close()
    return wb
Exemplo n.º 15
0
def test_add_invalid_worksheet_class_instance():
    wb = Workbook()
    ws = AlternativeWorksheet(parent_workbook=wb)
    wb.add_sheet(worksheet=ws)
Exemplo n.º 16
0
def write_results_disaggregation_UP(impact_method, full_results_UP, CF_categories, UP_meta_info, UP_list, all_system_scores, 
        all_unit_scores, level_reached, system_scores, system_number,systems):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet
    from construct_path import construct_path
    from copy import copy
    
    filename = 'disaggregation_' + impact_method + '_system' + str(system_number) + '.xlsx'
    wb = Workbook(encoding = 'mac_roman') #creating a workbook
    ws = Worksheet(wb, title = 'result') #creating a sheet inside the workbook
    
    #creation of headers
    header2 = ['instance ID', 
              'parent instance ID', 
              'unit process', 
              'demand', 
              'unit', 
              'pruned',
              'level', 
              'infrastructure']
    for impact_category in CF_categories:
        header2.append(impact_category)
    for i in range(1, level_reached + 1):
        header2.append('path level ' + str(i))
    header2.append('country')
    for i in range(4):
        header2.append('category ' + str(i))
    
    header1 = []
    for i in range(header2.index(CF_categories[0])):
        header1.append('')
    for impact_category in CF_categories:
        matrix_line = CF_categories.index(impact_category)
        header1.append(str(system_scores[matrix_line,0]))
    ws.append(header1) #writing the header
    ws.append(header2) #writing the header
    
    #content of the file
    for instance_ID in range(len(full_results_UP)): #for each instance of the full_results
        path = construct_path(full_results_UP, instance_ID, UP_list, impact_method, system_number) #construction of the path between the instance and the RF
        level = len(path) - 1 #level will be incremented from zero
        matrix_column = full_results_UP[instance_ID]['UP_number']
        UP_name = UP_list[matrix_column]
        demand = copy(full_results_UP[instance_ID]['demand'])
        #fetching the info to fill the line
        line = [instance_ID, 
                full_results_UP[instance_ID]['parent'], 
                UP_name, 
                demand, 
                UP_meta_info[UP_name]['unit'], 
                full_results_UP[instance_ID]['pruned'], 
                level, 
                UP_meta_info[UP_name]['Infrastructure']]
        for impact_category in CF_categories: #system or unit scores.  
            matrix_line = CF_categories.index(impact_category)
            if full_results_UP[instance_ID]['pruned'] == 1:
                line.append(all_system_scores[matrix_line, matrix_column] * demand)
            else:
                line.append(all_unit_scores[matrix_line, matrix_column] * demand)
        for i in range(1, level_reached + 1): #path
            try:
                line.append(path[i])
            except IndexError:
                line.append('')
        #complementary info
        line.append(UP_meta_info[UP_name]['Country'])
        for i in range(4):
            try:
                line.append(UP_meta_info[UP_name]['Category type'][i])
            except IndexError:
                line.append('')
        #print line
        ws.append(line) #writing the header
    ws.freeze_panes = 'D3'
    wb.add_sheet(ws)
    wb.save(filename)
Exemplo n.º 17
0
class ExcelWriter(object):
    """
    Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
    openpyxl for xlsx.  See DataFrame.to_excel for typical usage.

    Parameters
    ----------
    path : string
        Path to xls file
    """
    def __init__(self, path):
        self.use_xlsx = True
        if path.endswith('.xls'):
            self.use_xlsx = False
            import xlwt
            self.book = xlwt.Workbook()
            self.fm_datetime = xlwt.easyxf(
                num_format_str='YYYY-MM-DD HH:MM:SS')
            self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
        else:
            from openpyxl.workbook import Workbook
            self.book = Workbook(optimized_write=True)
        self.path = path
        self.sheets = {}
        self.cur_sheet = None

    def save(self):
        """
        Save workbook to disk
        """
        self.book.save(self.path)

    def writerow(self, row, sheet_name=None):
        """
        Write the given row into Excel an excel sheet

        Parameters
        ----------
        row : list
            Row of data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        """
        if sheet_name is None:
            sheet_name = self.cur_sheet
        if sheet_name is None:  # pragma: no cover
            raise Exception('Must pass explicit sheet_name or set '
                            'cur_sheet property')
        if self.use_xlsx:
            self._writerow_xlsx(row, sheet_name)
        else:
            self._writerow_xls(row, sheet_name)

    def _writerow_xls(self, row, sheet_name):
        if sheet_name in self.sheets:
            sheet, row_idx = self.sheets[sheet_name]
        else:
            sheet = self.book.add_sheet(sheet_name)
            row_idx = 0
        sheetrow = sheet.row(row_idx)
        for i, val in enumerate(row):
            if isinstance(val, (datetime.datetime, datetime.date)):
                if isinstance(val, datetime.datetime):
                    sheetrow.write(i, val, self.fm_datetime)
                else:
                    sheetrow.write(i, val, self.fm_date)
            elif isinstance(val, np.int64):
                sheetrow.write(i, int(val))
            elif isinstance(val, np.bool8):
                sheetrow.write(i, bool(val))
            else:
                sheetrow.write(i, val)
        row_idx += 1
        if row_idx == 1000:
            sheet.flush_row_data()
        self.sheets[sheet_name] = (sheet, row_idx)

    def _writerow_xlsx(self, row, sheet_name):
        if sheet_name in self.sheets:
            sheet, row_idx = self.sheets[sheet_name]
        else:
            sheet = self.book.create_sheet()
            sheet.title = sheet_name
            row_idx = 0

        conv_row = []
        for val in row:
            if isinstance(val, np.int64):
                val = int(val)
            elif isinstance(val, np.bool8):
                val = bool(val)
            conv_row.append(val)
        sheet.append(conv_row)
        row_idx += 1
        self.sheets[sheet_name] = (sheet, row_idx)
Exemplo n.º 18
0
def test_add_correct_sheet():
    wb = Workbook()
    new_sheet = wb.create_sheet(0)
    wb.add_sheet(new_sheet)
    assert new_sheet == wb.worksheets[2]
Exemplo n.º 19
0
class ExcelWriter(object):
    """
    Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
    openpyxl for xlsx.  See DataFrame.to_excel for typical usage.

    Parameters
    ----------
    path : string
        Path to xls file
    """
    def __init__(self, path):
        self.use_xlsx = True
        if path.endswith('.xls'):
            self.use_xlsx = False
            import xlwt
            self.book = xlwt.Workbook()
            self.fm_datetime = xlwt.easyxf(
                num_format_str='YYYY-MM-DD HH:MM:SS')
            self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
        else:
            from openpyxl.workbook import Workbook
            self.book = Workbook()  # optimized_write=True)
            # open pyxl 1.6.1 adds a dummy sheet remove it
            if self.book.worksheets:
                self.book.remove_sheet(self.book.worksheets[0])
        self.path = path
        self.sheets = {}
        self.cur_sheet = None

    def save(self):
        """
        Save workbook to disk
        """
        self.book.save(self.path)

    def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
        """
        Write given formated cells into Excel an excel sheet

        Parameters
        ----------
        cells : generator
            cell of formated data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        startrow: upper left cell row to dump data frame
        startcol: upper left cell column to dump data frame
        """
        if sheet_name is None:
            sheet_name = self.cur_sheet
        if sheet_name is None:  # pragma: no cover
            raise Exception('Must pass explicit sheet_name or set '
                            'cur_sheet property')
        if self.use_xlsx:
            self._writecells_xlsx(cells, sheet_name, startrow, startcol)
        else:
            self._writecells_xls(cells, sheet_name, startrow, startcol)

    def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):

        from openpyxl.cell import get_column_letter

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.create_sheet()
            wks.title = sheet_name
            self.sheets[sheet_name] = wks

        for cell in cells:
            colletter = get_column_letter(startcol + cell.col + 1)
            xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
            xcell.value = _conv_value(cell.val)
            if cell.style:
                style = CellStyleConverter.to_xlsx(cell.style)
                for field in style.__fields__:
                    xcell.style.__setattr__(field,
                                            style.__getattribute__(field))

            if isinstance(cell.val, datetime.datetime):
                xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
            elif isinstance(cell.val, datetime.date):
                xcell.style.number_format.format_code = "YYYY-MM-DD"

            # merging requires openpyxl latest (works on 1.6.1)
            # todo add version check
            if cell.mergestart is not None and cell.mergeend is not None:
                cletterstart = get_column_letter(startcol + cell.col + 1)
                cletterend = get_column_letter(startcol + cell.mergeend + 1)

                wks.merge_cells('%s%s:%s%s' % (cletterstart,
                                               startrow + cell.row + 1,
                                               cletterend,
                                               startrow + cell.mergestart + 1))

    def _writecells_xls(self, cells, sheet_name, startrow, startcol):
        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_sheet(sheet_name)
            self.sheets[sheet_name] = wks

        style_dict = {}

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime.datetime):
                num_format_str = "YYYY-MM-DD HH:MM:SS"
            if isinstance(cell.val, datetime.date):
                num_format_str = "YYYY-MM-DD"

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = CellStyleConverter.to_xls(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.write_merge(startrow + cell.row,
                                startrow + cell.mergestart,
                                startcol + cell.col,
                                startcol + cell.mergeend,
                                val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style)
Exemplo n.º 20
0
def test_add_incorrect_sheet():
    wb = Workbook()
    with pytest.raises(TypeError):
        wb.add_sheet("Test")
Exemplo n.º 21
0
def print_mat_desag(MATA, MATB,incertitude, UP_list_desag,EF_list):
	from openpyxl.workbook import Workbook
	from openpyxl.worksheet import Worksheet
	from copy import copy
	from time import time
	from scipy.sparse import lil_matrix, find

	filename = 'MAT.xlsx'
	wb = Workbook(encoding = 'mac_roman')
	Ws=wb.active
	wb.remove_sheet(Ws)
	
	
	#creation de la matrice A avec sa matrice d'incertitude
	Ws = Worksheet(wb, title = 'Processus')  
	for UP in UP_list_desag:
		line=[UP,UP_list_desag[UP]['origine']]
		line.append(UP_list_desag[UP]['process'])
		line.append(str(UP_list_desag[UP]['enfants']))
		Ws.append(line)	
	wb.add_sheet(Ws)
	
	Ws = Worksheet(wb, title = 'A')  
	Ws.freeze_panes = 'B2'
	mat=MATA.toarray()
	header = ['A']
	for UP in UP_list_desag:
		header.append(UP_list_desag[UP]['process'])
	Ws.append(header)
	for row in range(len(mat)):
		line=[row]
		for col in range(len(mat[row])):
			line.append(mat[row][col])
		Ws.append(line)	
	wb.add_sheet(Ws)
	
	Ws = Worksheet(wb, title = 'incertitude A')
	Ws.freeze_panes = 'B2'
	mat=incertitude['technology']
	header = ['incertitude A']
	for UP in UP_list_desag:
		header.append(UP_list_desag[UP]['process'])
	Ws.append(header)
	for row in range(len(mat)):
		line=[row]
		for col in range(len(mat)):
			line.append(mat[col][row])
		Ws.append(line)	
	wb.add_sheet(Ws)
	
	
	#Creation de B avec sa matrice d'incertitude
	Ws = Worksheet(wb, title = 'Flux elem') 
	Ws.freeze_panes = 'A2'
	header = ['id','compartment', 
          'substance', 
          'subcompartment']
	Ws.append(header)
	for EF in EF_list:
		L=[EF_list.index(EF)]
		L.append(EF)
		Ws.append(L)
	wb.add_sheet(Ws)
    
	Ws = Worksheet(wb, title = 'B')
	Ws.freeze_panes = 'B2'
	mat=MATB.toarray()
	header = ['B']
	for UP in UP_list_desag:
		header.append(UP_list_desag[UP]['process'])
	Ws.append(header)
	for row in range(len(mat)):
		line=[row]
		for col in range(len(mat[row])):
			line.append(mat[row][col])
		Ws.append(line)	
	wb.add_sheet(Ws)

	Ws = Worksheet(wb, title = 'incertitude B')
	Ws.freeze_panes = 'B2'
	mat=incertitude['intervention']
	header = ['incertitude B']
	for UP in UP_list_desag:
		header.append(UP_list_desag[UP]['process'])
	Ws.append(header)
	for row in range(len(mat[0])):
		line=[row]
		for col in range(len(mat)):
			line.append(mat[col][row])
		Ws.append(line)	
	wb.add_sheet(Ws)
	
	
	
	wb.save(filename)
	
	print 'fichier enregistre : MAT.xlsx'
Exemplo n.º 22
0
def test_add_invalid_worksheet_class_instance():
    wb = Workbook()
    ws = AlternativeWorksheet(parent_workbook=wb)
    wb.add_sheet(worksheet=ws)
Exemplo n.º 23
0
def test_add_correct_sheet():
    wb = Workbook()
    new_sheet = wb.create_sheet(0)
    wb.add_sheet(new_sheet)
    eq_(new_sheet, wb.worksheets[2])
Exemplo n.º 24
0
def test_add_incorrect_sheet():
    wb = Workbook()
    wb.add_sheet("Test")
Exemplo n.º 25
0
def test_add_invalid_worksheet_class_instance():
    wb = Workbook()
    ws = AlternativeWorksheet(parent_workbook=wb)
    with pytest.raises(TypeError):
        wb.add_sheet(worksheet=ws)
Exemplo n.º 26
0
        try:
            string_table = read_string_table(archive.read(ARC_SHARED_STRINGS))
        except KeyError:
            string_table = {}
        style_table = read_style_table(archive.read(ARC_STYLE))

        # get worksheets
        wb.worksheets = []  # remove preset worksheet
        sheet_names = read_sheets_titles(archive.read(ARC_APP))
        for i, sheet_name in enumerate(sheet_names):
            sheet_codename = 'sheet%d.xml' % (i + 1)
            worksheet_path = '%s/%s' % (PACKAGE_WORKSHEETS, sheet_codename)

            if not use_iterators:
                new_ws = read_worksheet(archive.read(worksheet_path), wb,
                                        sheet_name, string_table, style_table)
            else:
                xml_source = unpack_worksheet(archive, worksheet_path)
                new_ws = read_worksheet(xml_source, wb, sheet_name,
                                        string_table, style_table, filename,
                                        sheet_codename)
                #new_ws = read_worksheet(archive.read(worksheet_path), wb, sheet_name, string_table, style_table, filename, sheet_codename)
            wb.add_sheet(new_ws, index=i)

        wb._named_ranges = read_named_ranges(archive.read(ARC_WORKBOOK), wb)
    except KeyError, e:
        raise InvalidFileException(unicode(e))
    finally:
        archive.close()
    return wb
Exemplo n.º 27
0
def test_add_incorrect_sheet():
    wb = Workbook()
    with pytest.raises(TypeError):
        wb.add_sheet("Test")
Exemplo n.º 28
0
def test_add_correct_sheet():
    wb = Workbook()
    new_sheet = wb.create_sheet(0)
    wb.add_sheet(new_sheet)
    assert new_sheet == wb.worksheets[2]
Exemplo n.º 29
0
class ExcelWriter(object):
    """
    Class for writing DataFrame objects into excel sheets, uses xlwt for xls,
    openpyxl for xlsx.  See DataFrame.to_excel for typical usage.

    Parameters
    ----------
    path : string
        Path to xls file
    """
    def __init__(self, path):
        self.use_xlsx = True
        if path.endswith('.xls'):
            self.use_xlsx = False
            import xlwt
            self.book = xlwt.Workbook()
            self.fm_datetime = xlwt.easyxf(
                num_format_str='YYYY-MM-DD HH:MM:SS')
            self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
        else:
            from openpyxl.workbook import Workbook
            self.book = Workbook()  # optimized_write=True)
            # open pyxl 1.6.1 adds a dummy sheet remove it
            if self.book.worksheets:
                self.book.remove_sheet(self.book.worksheets[0])
        self.path = path
        self.sheets = {}
        self.cur_sheet = None

    def save(self):
        """
        Save workbook to disk
        """
        self.book.save(self.path)

    def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
        """
        Write given formated cells into Excel an excel sheet

        Parameters
        ----------
        cells : generator
            cell of formated data to save to Excel sheet
        sheet_name : string, default None
            Name of Excel sheet, if None, then use self.cur_sheet
        startrow: upper left cell row to dump data frame
        startcol: upper left cell column to dump data frame
        """
        if sheet_name is None:
            sheet_name = self.cur_sheet
        if sheet_name is None:  # pragma: no cover
            raise Exception('Must pass explicit sheet_name or set '
                            'cur_sheet property')
        if self.use_xlsx:
            self._writecells_xlsx(cells, sheet_name, startrow, startcol)
        else:
            self._writecells_xls(cells, sheet_name, startrow, startcol)

    def _writecells_xlsx(self, cells, sheet_name, startrow, startcol):

        from openpyxl.cell import get_column_letter

        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.create_sheet()
            wks.title = sheet_name
            self.sheets[sheet_name] = wks

        for cell in cells:
            colletter = get_column_letter(startcol + cell.col + 1)
            xcell = wks.cell("%s%s" % (colletter, startrow + cell.row + 1))
            xcell.value = _conv_value(cell.val)
            if cell.style:
                style = CellStyleConverter.to_xlsx(cell.style)
                for field in style.__fields__:
                    xcell.style.__setattr__(field,
                                            style.__getattribute__(field))

            if isinstance(cell.val, datetime.datetime):
                xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
            elif isinstance(cell.val, datetime.date):
                xcell.style.number_format.format_code = "YYYY-MM-DD"

            # merging requires openpyxl latest (works on 1.6.1)
            # todo add version check
            if cell.mergestart is not None and cell.mergeend is not None:
                cletterstart = get_column_letter(startcol + cell.col + 1)
                cletterend = get_column_letter(startcol + cell.mergeend + 1)

                wks.merge_cells('%s%s:%s%s' % (cletterstart,
                                               startrow + cell.row + 1,
                                               cletterend,
                                               startrow + cell.mergestart + 1))

    def _writecells_xls(self, cells, sheet_name, startrow, startcol):
        if sheet_name in self.sheets:
            wks = self.sheets[sheet_name]
        else:
            wks = self.book.add_sheet(sheet_name)
            self.sheets[sheet_name] = wks

        style_dict = {}

        for cell in cells:
            val = _conv_value(cell.val)

            num_format_str = None
            if isinstance(cell.val, datetime.datetime):
                num_format_str = "YYYY-MM-DD HH:MM:SS"
            if isinstance(cell.val, datetime.date):
                num_format_str = "YYYY-MM-DD"

            stylekey = json.dumps(cell.style)
            if num_format_str:
                stylekey += num_format_str

            if stylekey in style_dict:
                style = style_dict[stylekey]
            else:
                style = CellStyleConverter.to_xls(cell.style, num_format_str)
                style_dict[stylekey] = style

            if cell.mergestart is not None and cell.mergeend is not None:
                wks.write_merge(startrow + cell.row,
                                startrow + cell.mergestart,
                                startcol + cell.col,
                                startcol + cell.mergeend,
                                val, style)
            else:
                wks.write(startrow + cell.row,
                          startcol + cell.col,
                          val, style)
def print_mat_desag(MATA, MATB, incertitude, UP_list_desag, EF_list):
    from openpyxl.workbook import Workbook
    from openpyxl.worksheet import Worksheet
    from copy import copy
    from time import time
    from scipy.sparse import lil_matrix, find

    filename = "MAT.xlsx"
    wb = Workbook(encoding="mac_roman")
    Ws = wb.active
    wb.remove_sheet(Ws)

    # creation de la matrice A avec sa matrice d'incertitude
    Ws = Worksheet(wb, title="Processus")
    for UP in UP_list_desag:
        line = [UP, UP_list_desag[UP]["origine"]]
        line.append(UP_list_desag[UP]["process"])
        line.append(str(UP_list_desag[UP]["enfants"]))
        Ws.append(line)
    wb.add_sheet(Ws)

    Ws = Worksheet(wb, title="A")
    Ws.freeze_panes = "B2"
    mat = MATA.toarray()
    header = ["A"]
    for UP in UP_list_desag:
        header.append(UP_list_desag[UP]["process"])
    Ws.append(header)
    for row in range(len(mat)):
        line = [row]
        for col in range(len(mat[row])):
            line.append(mat[row][col])
        Ws.append(line)
    wb.add_sheet(Ws)

    Ws = Worksheet(wb, title="incertitude A")
    Ws.freeze_panes = "B2"
    mat = incertitude["technology"]
    header = ["incertitude A"]
    for UP in UP_list_desag:
        header.append(UP_list_desag[UP]["process"])
    Ws.append(header)
    for row in range(len(mat)):
        line = [row]
        for col in range(len(mat)):
            line.append(mat[col][row])
        Ws.append(line)
    wb.add_sheet(Ws)

    # Creation de B avec sa matrice d'incertitude
    Ws = Worksheet(wb, title="Flux elem")
    Ws.freeze_panes = "A2"
    header = ["id", "compartment", "substance", "subcompartment"]
    Ws.append(header)
    for EF in EF_list:
        L = [EF_list.index(EF)]
        L.append(EF)
        Ws.append(L)
    wb.add_sheet(Ws)

    Ws = Worksheet(wb, title="B")
    Ws.freeze_panes = "B2"
    mat = MATB.toarray()
    header = ["B"]
    for UP in UP_list_desag:
        header.append(UP_list_desag[UP]["process"])
    Ws.append(header)
    for row in range(len(mat)):
        line = [row]
        for col in range(len(mat[row])):
            line.append(mat[row][col])
        Ws.append(line)
    wb.add_sheet(Ws)

    Ws = Worksheet(wb, title="incertitude B")
    Ws.freeze_panes = "B2"
    mat = incertitude["intervention"]
    header = ["incertitude B"]
    for UP in UP_list_desag:
        header.append(UP_list_desag[UP]["process"])
    Ws.append(header)
    for row in range(len(mat[0])):
        line = [row]
        for col in range(len(mat)):
            line.append(mat[col][row])
        Ws.append(line)
    wb.add_sheet(Ws)

    wb.save(filename)

    print "fichier enregistre : MAT.xlsx"