示例#1
0
 def generate(self):
     self.activate()
     repBook = Workbook()
     currentSheet = repBook.active
     for row in self.sheet.values:
         if row[0] == "Tiempo":
             currentSheet.append(["Tiempo", "Sensor 1" , "Sensor 2" , "Sensor 3" , "Sensor 4" ])
         else:
             a = row[0].split()[0].split('-')
             print(self.fmes.get())
             if int(a[0]) < int(self.fanio.get())-2000:
                 pass
             elif int(a[0]) > int(self.sanio.get())-2000:
                 break
             elif int(a[1]) < int(self.fmes.get()):
                 pass                
             elif int(a[1]) > int(self.smes.get()):
                 break
             elif int(a[2]) < int(self.fdia.get()):
                 pass                
             elif int(a[2]) > int(self.sdia.get()):
                 break
             else:
                 currentSheet.append(row)
     repBook.save("Reporte.xlsx")
示例#2
0
    def write(self):
        self.workbook = Workbook()
        self.sheet = self.workbook.get_active_sheet()

        if not len(self.header):
            raise BRException("Header not provided")

        if self.data and len(self.header) != len(self.data[0]):
            raise BRException("Header and Data must be in same format")

        # Write headers
        row = 1
        col = 1
        for col_header in self.header:
            self.sheet.cell(row=row, column=col, value=col_header)
            col += 1

        row += 1
        # Now write data
        for data_row in self.data:
            col = 1
            for col_value in data_row:
                col_value = col_value if col_value else ''
                self.sheet.cell(row=row, column=col, value=col_value)
                col += 1
            row += 1
        if self.response:
            self.workbook.save(self.response)
        else:
            if self.file_name:
                self.workbook.save(self.file_name)
示例#3
0
def createOutputExcel(outputData):

    wb = Workbook()
    ws = wb.active
    ws.title = 'Budget Projection'
    columnNames = [
        "Name", "SA Rate", "Deltek Rate", "Avg Run", "Hours", "Dollars"
    ]
    for w in range(1, nTotalNumWeeks + 1):
        nThYear = int(w / 54)
        nYear = 2020 + int(nThYear)
        nWeek = w - (53 * nThYear)
        columnNames.append(
            "Week " + str(nWeek) + " \n[" +
            date.fromisocalendar(nYear, nWeek, 7).strftime("%m/%d/%y") + "]")
    ws.freeze_panes = 'G3'

    headerCnt = len(columnNames)
    for cells in ws.iter_rows(1, 1, 1, headerCnt):
        for cell in cells:
            cell.value = columnNames[cell.column - 1]
            cell.alignment = Alignment(wrapText=True, horizontal='center')

    rowNum = 2
    rowCnt = len(outputData) + 1
    for name, stats in outputData.items():
        for cells in ws.iter_rows(rowNum, rowNum, 1, headerCnt):
            cells[0].value = name
            ws.column_dimensions[cells[0].column_letter].width = 25
            cells[1].value = stats['SpringAhead Rate']
            cells[1].number_format = '"$"#'
            ws.column_dimensions[cells[1].column_letter].width = 8
            cells[2].value = stats['Deltek Rate']
            cells[2].number_format = '"$"#'
            ws.column_dimensions[cells[2].column_letter].width = 8
            cells[3].value = stats['Avg Run']
            cells[3].number_format = '0.00'
            ws.column_dimensions[cells[3].column_letter].width = 7
            cells[4].value = stats['Hours']
            cells[4].number_format = '#,##0.00'
            ws.column_dimensions[cells[4].column_letter].width = 9
            cells[5].value = stats['Dollars']
            cells[5].number_format = '"$"#,##0.00'
            ws.column_dimensions[cells[5].column_letter].width = 14
            for w in range(1, nTotalNumWeeks + 1):
                cells[w + 5].value = float(stats[w])
                if rowNum > rowCnt:
                    cells[w + 5].number_format = '"$"#,##0.00'
                else:
                    cells[w + 5].number_format = '#,##0.00'
                ws.column_dimensions[cells[w + 5].column_letter].width = 12
                ws.column_dimensions[cells[w + 5].column_letter].height = 29

        rowNum = rowNum + 1

    setExcelFormulas(ws, rowCnt, headerCnt)

    wb.save(outputFile)
 def updateScriptFileAndCaseResult(self, workB: Workbook,
                                   testCaseResult: TestCaseResult,
                                   caseResult):
     workB.save(self.CurrentScriptPath)
     logging.info('save scenario file: "{0}" successfully'.format(
         self.CurrentScriptPath))
     testCaseResult.CaseResult = caseResult
     testCaseResult.EndTime = Tool.CurrentTime()
     self.testPlanResult.TestCaseResultCollection.append(testCaseResult)
示例#5
0
 def _xlsx_save(self, result: [dict], file_name: str):
     """data is saved in excel format"""
     wb = Workbook()
     sheet = wb.active
     for i, ldict in enumerate(result, 1):
         if i == 1:
             title_list: list = list(ldict.keys())
             sheet.append(title_list)
         sheet.append(list(ldict.values()))
     wb.save(fr"{file_name}.xlsx")
示例#6
0
 def btnDownloadClicked(self):
     result_file = Workbook()
     sheet1 = result_file.active
     sheet1.title = 'result'
     idx = ["업무(결과값)", "수행 내용", "이름", "직급", "직급 NO."]
     for i in range(5):
         sheet1.cell(1, i+1).value = idx[i]
     for cell in self.result:
         sheet1.append(cell)
     result_file.save('./result' + datetime.today().strftime("%Y%m%d%H%M%S") + '.xlsx') 
     QtWidgets.QMessageBox.about(self, "저장완료", "파일이 저장되었습니다.")  
示例#7
0
def checkHolds(port, driver):
    driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ0087_00000:_idsc00001:_idsc00003_6:_idsc00007").click()
#     driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:portId").send_keys(port)
    driver.find_element_by_id("clayView:ns_7_40O00G3VMR0D00A4BG79CQ00Q4_00000:_idsc00001:_idsc00020").click()
    
    onHold = []
    bonds = []
    done = False    
    j=2
    driver.implicitly_wait(0)
    try:
        driver.find_element_by_css_selector("span[title='Page 1']").click()
    except:
        pass
    driver.implicitly_wait(60)  
    while not done:
        cells = driver.find_elements_by_css_selector("table[class='datat']>tbody>tr>td")
        i=0
        while i<min(1500, len(cells)):
            driver.execute_script("arguments[0].scrollIntoView();", cells[i])
#             bonds.append(cells[i+3].text)
#             if not todaysDate in cells[i+10].text:
            if "Y" in cells[i+14].text:
                onHold.append(cells[i+3].text)
#                 cells[i+3].find_element_by_tag_name("input").send_keys(port)
            i+=15
#             else:
#                 break
        driver.implicitly_wait(0)
        try:
            driver.find_element_by_css_selector("span[title='Page " +str(j)+ "']").click()
            j+=1
        except:
            done=True
        driver.implicitly_wait(60)  
    
    if len(bonds)>0:
        filepath = r"J:\INBOND CLOSURE TRACKING\Open Bonds - " + datetime.now().strftime("%Y-%m-%d %H-%M-%S") + ".xlsx"  
        wb = Workbook()
        ws = wb.active
        for i in range(len(bonds)):
            ws.cell(row=i+1, column=1).value = bonds[i]
        wb.save(filepath)
    
    if len(onHold)>0:
        print("IN-BOND #S ON HOLD:")
        holdString = "IN-BOND #S ON HOLD:\n"
        for bond in onHold:
            print(bond)
            holdString = holdString+bond + "\n"
        
        HelperFunctions.popUpOK(holdString)
示例#8
0
 def __write_wb(self, wb_name, worksheet_data):
             
     dest_wb = Workbook(write_only=True)
             
     for ws_name, ws_data in worksheet_data.iteritems():
         if ws_data == []:
             continue
         
         ws = dest_wb.create_sheet(title=ws_name)            
         ws_data_transposed = zip(*ws_data)
         for row in ws_data_transposed:
             ws.append(row)
     
     dest_wb.save(os.path.join(self.output_dir, wb_name))
示例#9
0
文件: xls.py 项目: zalihat/opennem
def convert_to_xlxs(content: Any) -> Workbook:
    """Convert old workbook formats xls into xlxs"""
    xlsBook = xlrd.open_workbook(file_contents=content)
    workbook = Workbook()

    for i in range(0, xlsBook.nsheets):
        xlsSheet = xlsBook.sheet_by_index(i)
        sheet = workbook.active if i == 0 else workbook.create_sheet()
        sheet.title = xlsSheet.name

        for row in range(0, xlsSheet.nrows):
            for col in range(0, xlsSheet.ncols):
                sheet.cell(row=row + 1,
                           column=col + 1).value = xlsSheet.cell_value(
                               row, col)

    return workbook
示例#10
0
class ExcelPipeline(object):
    def __init__(self):
        self.wb = Workbook()
        self.ws = self.wb.active
        self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"])
    
    def process_item(self, item, spider):
        line = [
            item.get("type"), item.get("asequence"), item.get("pname"),
            item.get("munit"), item.get("mperson"), item.get("anumber"),
            item.get("awardtype"), item.get("rank")
        ]
        
        self.ws.append(line)
        self.wb.save("landandresourceaward.xlsx")
        
        return item
示例#11
0
def build_excel_response(field_names,data,filename):
    try:
        wb = Workbook(write_only=True) 
        ws = wb.create_sheet()
        ws.append(field_names)
        for item in data:
            ws.append(item.values())
        
        s = StringIO()
        wb.save(s)
        response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = u'attachment; filename={}.xlsx'.format(filename)
        response['Content-Length'] = s.len 
        response.write(s.getvalue())
        s.close()
        
        return response
    except Exception, exc:
        return build_exception_response()
示例#12
0
    def write_xlsx(self, file: Union[str, BinaryIO]) -> None:
        """
        Write the contents in XLSX (Excel) format to a file.

        Args:
            file: filename or file-like object
        """
        if XLSX_VIA_PYEXCEL:  # use pyexcel_xlsx
            data = self._get_pyexcel_data(convert_for_openpyxl)
            pyexcel_xlsx.save_data(file, data)
        else:  # use openpyxl
            # Marginal performance gain with write_only. Does not automatically
            # add a blank sheet
            wb = XLWorkbook(write_only=True)
            valid_name_dict = self.get_pages_with_valid_sheet_names()
            for page, title in valid_name_dict.items():
                ws = wb.create_sheet(title=title)
                page.write_to_openpyxl_xlsx_worksheet(ws)
            wb.save(file)
    def store2xlsx(self):
        '''
        select company info and store to xlsx
        '''
        odb = OperateDB()
        
        # current date
        datee = datetime.datetime.now().strftime("%Y-%m-%d")
        
        fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx')
        
        wb = Workbook()
        sheet = wb.active
#         print sheet.max_row
        # 设置单元格所有框线
        thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(
style='thin'))   
        # 写标题
        for i in range(self.__indexName.__len__()):
            _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i])
        
        stockInfos = odb.selectFromCompany(MyUtil.selectCompany.replace('%s', MyUtil.indexs))
        
        keyList = stockInfos.keys()
        # 按照公司代码排序
        keyList.sort()
#         print keyList

        # 第一行为标题,从第二行开始为实际数据
        rowNum = 2
        for i in keyList:
            for j in range(len(stockInfos[i])):
                _ = sheet.cell(column=j + 1, row=rowNum, value=stockInfos[i][j])
                sheet.cell(column=j + 1, row=rowNum).border = thin_border
            rowNum += 1
            
            # 每100条保存一下
            if int(i) % 100 == 0:
                wb.save(fileAbsPath)
        # 最后保存一下
        wb.save(fileAbsPath)
示例#14
0
    def create(cls) -> ExcelSheet:
        """Creates an excel worksheet from nothing

        Factory method to initialize an object

        Returns
        -------
        excel worksheet:
            an opened new and empty excel worksheet object
        """
        wb = Workbook()
        return cls(wb)
示例#15
0
def convert_to_xlxs(fh: BinaryIO) -> Optional[Workbook]:
    """Convert old workbook formats xls into xlxs"""
    if not _HAVE_XLRD:
        logger.error("xlrd module not installed. Cannot convert XLS file.")
        return None

    file_content = fh.read()
    fh.seek(0)

    xlsBook = xlrd.open_workbook(file_contents=file_content)
    workbook = Workbook()

    for i in range(0, xlsBook.nsheets):
        xlsSheet = xlsBook.sheet_by_index(i)
        sheet = workbook.active if i == 0 else workbook.create_sheet()
        sheet.title = xlsSheet.name

        for row in range(0, xlsSheet.nrows):
            for col in range(0, xlsSheet.ncols):
                sheet.cell(row=row + 1, column=col + 1).value = xlsSheet.cell_value(row, col)

    return workbook
示例#16
0
class ExcelFileWriter(Writter):
    def __init__(self, data, header=[], file_path=None, *args, **kwargs):
        super(ExcelFileWriter, self).__init__(data=data,
                                              header=header,
                                              file_path=file_path,
                                              *args,
                                              **kwargs)

    def write(self):
        self.workbook = Workbook()
        self.sheet = self.workbook.get_active_sheet()

        if not len(self.header):
            raise BRException("Header not provided")

        if self.data and len(self.header) != len(self.data[0]):
            raise BRException("Header and Data must be in same format")

        # Write headers
        row = 1
        col = 1
        for col_header in self.header:
            self.sheet.cell(row=row, column=col, value=col_header)
            col += 1

        row += 1
        # Now write data
        for data_row in self.data:
            col = 1
            for col_value in data_row:
                col_value = col_value if col_value else ''
                self.sheet.cell(row=row, column=col, value=col_value)
                col += 1
            row += 1
        if self.response:
            self.workbook.save(self.response)
        else:
            if self.file_name:
                self.workbook.save(self.file_name)
示例#17
0
    def store2xlsx(self):
        '''
        select company info and store to xlsx
        '''
        odb = OperateDB()

        # current date
        datee = datetime.datetime.now().strftime("%Y-%m-%d")

        fileAbsPath = self.filePath.replace('.xlsx', datee + '.xlsx')

        wb = Workbook()
        sheet = wb.active
        #         print sheet.max_row
        # 设置单元格所有框线
        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        # 写标题
        for i in range(self.__indexName.__len__()):
            _ = sheet.cell(column=i + 1, row=1, value=self.__indexName[i])

        stockInfos = odb.selectFromCompany(
            MyUtil.selectCompany.replace('%s', MyUtil.indexs))

        keyList = stockInfos.keys()
        # 按照公司代码排序
        keyList.sort()
        #         print keyList

        # 第一行为标题,从第二行开始为实际数据
        rowNum = 2
        for i in keyList:
            for j in range(len(stockInfos[i])):
                _ = sheet.cell(column=j + 1,
                               row=rowNum,
                               value=stockInfos[i][j])
                sheet.cell(column=j + 1, row=rowNum).border = thin_border
            rowNum += 1

            # 每100条保存一下
            if int(i) % 100 == 0:
                wb.save(fileAbsPath)
        # 最后保存一下
        wb.save(fileAbsPath)
示例#18
0
directoryDestination.mkdir(exist_ok = True)

bigDict = {}
indexDictionary = {}
columnHeaders = []
isListBuilt = False
wb: Workbook
ws: Worksheet
workbook_filename: str = 'jsonData.xlsx'

try:
    wb = openpyxl.load_workbook(workbook_filename)
    ws = wb['data']
except FileNotFoundError:
    print('Workbook not found, creating new workbook')
    wb = Workbook()
    ws = wb.active
    ws.title = 'data'
    wb.save('jsonData.xlsx')

# program options
options = ['movement','pull data','push data']
for index, operation in enumerate(options):
    print(str(index) + ': ' + operation)

# input
operationIndex: str = input("Pick operation (enter digit):")
print("\n")
print("\n")

directoryToCrawl: Path
示例#19
0
 def __init__(self, name: str):
     self.name = name
     if not os.path.exists(name):
         save_workbook(Workbook(), name)
     self._wb = xl.load_workbook(name, keep_vba=True)
示例#20
0
def createExcelFile(Path, sheetName):
    try:
        wb = Workbook(Path)
        ws = wb[sheetName]
        wb.close()
    except:
        wb = Workbook()
        ws = wb.create_sheet(sheetName)
        wb.save(Path)
        wb = openpyxl.load_workbook(Path)
        wb.remove(wb['Sheet'])
        wb.save(Path)
        wb.close()
示例#21
0
 def __init__(self):
     self.wb = Workbook()
     self.ws = self.wb.active
     self.ws.append(["奖项类型", "获奖序号", "项目名称", "主要完成单位", "主要完成人", "获奖证书编号", "奖种", "等级"])
示例#22
0
for origin_file in origin_folder_glob:
    origin_file_name = origin_file[len(origin_folder) + 1:]
    input_file_name = locals()['origin_file_name'][:-4] + "_input.txt"
    input_file = origin_folder + "_inputs/" + input_file_name
    prepare_tbschema_file(origin_file, input_file)
    server_name = origin_file_name.split('-')[1]
    database_name = origin_file_name.split('-')[2][:-4]

    if server_name not in list(servers_dictionnary.keys()):
        servers_dictionnary[server_name] = Server(server_name, database_name,
                                                  input_file)
    else:
        servers_dictionnary[server_name].add_database_to_dictionnary(
            database_name, input_file)

excel_file = Workbook()
ws = excel_file.active
ws.title = "Summary"

ws.cell(row=1, column=1, value='ACTION')
ws.cell(row=1, column=2, value='COMPL.')
ws.cell(row=1, column=3, value='DB')
ws.cell(row=1, column=4, value='TABLE')

tbl_cat_dict = {}
idx_cat_dict = {}
rvk_cat_dict = {}
grt_cat_dict = {}
server_column = 5
line_number = 2
示例#23
0
def validate_sheet_names(
    *,
    wb: Workbook,
    filename: Union[Path, str],
    sheet_name: str,
    sheet_names: List[str],
    start_time: float,
    original_stdout: io.TextIOWrapper,
    output_url: str
) -> Workbook:
    """
    Parameters
    ----------
    wb : Workbook
        A workbook.
    filename : Union[Path, str]
        The file containing the workbook.
    sheet_name : str
        A sheet name in the workbook.
    sheet_names : List[str]
        The sheet names in the workbook.
    start_time : float
        The start time of the script.
    original_stdout : io.TextIOWrapper
        The buffered text stream for the html output.
    output_url : str
        The html filename.

    Returns
    -------
    wb : Workbook
        The workbook with a revised sheetname.

    Example
    -------
    >>> import datasense as ds
    >>> wb = validate_sheet_names(
    >>>     wb=wb,
    >>>     filename=filename,
    >>>     sheet_name=sheet_name,
    >>>     sheet_names=sheet_names,
    >>>     start_time=start_time,
    >>>     original_stdout=original_stdout,
    >>>     output_url=output_url
    >>> )
    """
    if sheet_name not in sheet_names and len(sheet_names) != 1:
        print('Manually rename one of these sheets:')
        print(wb.sheetnames)
        print('Then re-run script')
        print('XXX File NOT OK XXX')
        stop_time = time.time()
        report_summary(
            start_time=start_time,
            stop_time=stop_time
        )
        exit_script(
            original_stdout=original_stdout,
            output_url=output_url
        )
    elif sheet_name not in sheet_names and len(sheet_names) == 1:
        print('One sheet found and it was re-named.')
        ws = wb.active
        ws.title = sheet_name
        wb.save(filename=filename)
    elif sheet_name in sheet_names and len(sheet_names) != 1:
        sheet_names_removed = [x for x in sheet_names if x != sheet_name]
        for sheet in sheet_names_removed:
            wb.remove(worksheet=wb[sheet])
        print('Sheet names removed:')
        print(sheet_names_removed)
        wb.save(filename=filename)
    return wb
示例#24
0
 def __init__(self, archive):
     self.archive = archive
     self.wb = Workbook()
     self.sheets = []
     self.rels = get_dependents(self.archive, ARC_WORKBOOK_RELS)
示例#25
0
文件: ATO.py 项目: wllkoala/SIHI
        # if no from_loc given, assume last sheet
        if from_loc is None:
            from_loc = len(sheets) - 1
        # if no to_loc given, assume first
        if to_loc is None:
            to_loc = 0
        sheet = sheets.pop(from_loc)
        sheets.insert(to_loc, sheet)


if __name__ == "__main__":
    try:
        ato_report = filedialog.askopenfilename(title="ato_report",
                                                filetypes=[("All files", "*")])
        storage_report = filedialog.askopenfilename(title="storage_report",
                                                    filetypes=[("All files",
                                                                "*")])
        summary_workbook = Workbook()
        ws = summary_workbook.active
        ws.title = "Summary"
        summary_workbook.save("Summary.xlsx")
        ato = ATO_check(ato_report, storage_report)
        ato.start_to_run()
        input("Press <Enter>")
    except Exception as err:
        messagebox.showerror("Warning!", err)
        with open(os.path.join(os.getcwd(), "error.txt"), "w+") as f:
            traceback.print_exc(file=f)
        print(err)
        exit()
            except:
                print _u

    html = etree.HTML(content)

    _as = html.xpath("//table[@id='detail']//a")

    for _a in _as:
        title = _a.xpath('string(.)').strip()
        if title:
            _href = ''.join(_a.xpath('@href'))
            _store.append((title, _href,
                           str(i) + '_'.encode('utf-8') + _u.encode('utf-8')))

print len(_store)

wb = Workbook()
sheet = wb.active
sheet.cell(row=1, column=1, value='标题')
sheet.cell(row=1, column=2, value='链接')
sheet.cell(row=1, column=3, value='出处')

_titles = map(lambda x: x[0], _store)
_links = map(lambda x: urljoin('http://admission.ucas.ac.cn/', x[1]), _store)

for i in range(len(_titles)):
    sheet.cell(row=i + 2, column=1, value=_titles[i])
    sheet.cell(row=i + 2, column=2, value=_links[i])
    sheet.cell(row=i + 2, column=3, value=_store[i][2].encode('utf-8'))
wb.save('kejidaxue3.xlsx')
示例#27
0
    def write_infos(self, infos: List[Info]) -> None:
        'atomically write all the infos'

        def clean(descrizione: str) -> str:
            for prefix in ('AD.COM.LE DA TR. NEL ', 'AD.REG.LE DA TR. NEL ',
                           'TICKET PASTO'):
                if descrizione.startswith(prefix):
                    return f'{prefix}*'

            return descrizione

        # collect all details of all infos:
        details = list(
            sorted({
                clean(additional_detail.descrizione)
                for info in infos
                for additional_detail in info.additional_details
            }))

        # there are 1 + len(keys)-1 + len(details) columns

        header: List[Tuple[E, str]] = [('month', NUMBER_FORMAT_TEXT)]
        for column_header in ColumnHeader:
            if column_header is not ColumnHeader.detail:
                header.append((column_header.name, NUMBER_FORMAT_TEXT))
            else:
                for detail in details:
                    header.append((detail, NUMBER_FORMAT_TEXT))

        rows: List[List[Tuple[E, str]]] = [header]

        for info in infos:
            # group columns by column_header
            columns = {column.header: column for column in info.columns}
            # group additional_details by descrizione
            additional_details = {
                clean(additional_detail.descrizione): additional_detail
                for additional_detail in info.additional_details
            }

            row: List[Tuple[E, str]] = [(info.when, NUMBER_FORMAT_DATE)]
            for column_header in ColumnHeader:
                if column_header is not ColumnHeader.detail:
                    row.append((columns[column_header].howmuch,
                                VALUE_NUMBER_FORMAT[column_header]
                                ) if column_header in columns else (
                                    None, NUMBER_FORMAT_TEXT))
                else:
                    for detail in details:
                        # cosa esportare nell'xls?
                        row.append((additional_details[detail].competenze -
                                    additional_details[detail].trattenute,
                                    NUMBER_FORMAT_NUMBER
                                    ) if detail in additional_details else (
                                        None, NUMBER_FORMAT_TEXT))
            rows.append(row)

        widths: Dict[str, int] = {}
        for row in rows:
            for i, cell in enumerate(row, start=1):
                column_letter = get_column_letter(i)
                widths[column_letter] = max(widths.get(column_letter, 0),
                                            2 * len(str(cell[0])))

        workbook = Workbook(write_only=True)
        try:
            # create the main sheet
            sheet = workbook.create_sheet('main')
            # first set the width of the columns
            for column_letter, width in widths.items():
                sheet.column_dimensions[column_letter].width = width
            # then add the data
            for row in rows:
                sheet.append([
                    self._cell(sheet, value, number_format)
                    for value, number_format in row
                ])
        finally:
            workbook.save(self.name)
示例#28
0
def index(request):
    place = "McDonald's"
    state = StateProvince.objects.get(code="MG")
    
    # 1. Filtragem por precisão e duração. 
    # Eliminar registros com precisão maior que 100m e duração maior que 90min.
    visits = Visit.objects.filter(
        precision__lt=100, total_time_in_min__lt=90, 
        place__name=place, place__address__city__state__code=state) 

    count_visits_total = visits.count()
    count_users_total = visits.values('user_id').annotate(
        count=Count('user_id')).count()

    infos = dict()
    series_days_of_week = [
        {'name': 'Sunday', 'values':[]}, 
        {'name': 'Monday', 'values':[]}, 
        {'name': 'Tuesday', 'values':[]}, 
        {'name': 'Wednesday', 'values':[]},
        {'name': 'Thursday', 'values':[]}, 
        {'name': 'Friday', 'values':[]}, 
        {'name': 'Saturday', 'values':[]}
    ]
    series_day_periods = [
        {'name': 'Manhã', 'values':[]}, 
        {'name': 'Almoço', 'values':[]}, 
        {'name': 'Tarde', 'values':[]}, 
        {'name': 'Noite', 'values':[]},
        {'name': 'Madrugada', 'values':[]},
    ]

    for visit in visits:
        key = "{}-{}".format(visit.place, visit.place.address.zip_code)
        
        if not infos.get(key):
            _visits=visits.filter(place=visit.place)
            item = dict(
                visit=visit

                # 2. Contagem de visitas e usuários aos PDVs.
                ,count_visits=_visits.count()
                ,count_users=_visits.values(
                    'user_id').annotate(count=Count('user_id')).count() 

                # 3. Criação de visualização dos dias da semana das visitas aos PDVs.
                ,count_days_of_week=[ 
                    {week_day[1]:_visits.filter(arrival__week_day=week_day[0]).count()}
                    for week_day in Visit.DAYS_OF_WEEK ]
                
                # 4. Criação de visualização dos períodos do dia das visitas aos PDVs. (Manhã, Almoço, Tarde, Noite)
                ,count_day_periods=[
                    {day_period[0]:_visits.filter(
                        arrival__time__range=(day_period[1], day_period[2])).count()}
                    for day_period in Visit.DAY_PERIOD]

                # 5. Criação de visualização de visitas antes e visitas depois de visitas aos PDVs
                
            )
            infos[key] = item

            # generate series to chart days of week
            if item.get('count_visits') > 100:
                for dayweek in item.get('count_days_of_week'):
                    for serie in series_days_of_week:
                        if serie['name'] in dayweek:
                            _values = list(dayweek.items())
                            serie['values'].append(_values[0][1])
                
                for dayperiod in item.get('count_day_periods'):
                    for serie in series_day_periods:
                        if serie['name'] in dayperiod:
                            _values = list(dayperiod.items())
                            serie['values'].append(_values[0][1])

    context = dict(
        infos=infos
        ,count_visits=count_visits_total
        ,count_users=count_users_total
        ,series_days_of_week=series_days_of_week
        ,series_day_periods=series_day_periods
    )
    if 'reports' in request.POST:
        wb = Workbook()
        wb.remove_sheet(wb.worksheets[0])
        sh = wb.create_sheet('vivists')

        sh.append([
            '_ID'
            ,'EUID'
            ,'ARRIVAL'
            ,'DEPARTURE'
            ,'PDV'
            ,'STREET'
            ,'POSTCODE'
            ,'SUBURB'
            ,'CITY'
            ,'VISITS'
            ,'CUSTOMERS'
            ,'COUNT_DAY_PERIODS'
            ,'COUNT_DAYS_OF_WEEK'
        ])

        for item in infos.values():
            # import pdb ; pdb.set_trace()
            visit = item.get('visit')
            sh.append([
                visit.code
                ,visit.user_id
                ,visit.arrival.strftime('%d/%m/%Y')
                ,visit.departure.strftime('%d/%m/%Y')
                ,visit.pdv
                ,visit.place.address.street or ''
                ,visit.place.address.zip_code or ''
                ,visit.place.address.suburb or ''
                ,visit.place.address.city.name
                ,item.get('count_visits')
                ,item.get('count_users')
                ,json.dumps(item.get('count_day_periods'))
                ,json.dumps(item.get('count_days_of_week'))
            ])

        response = HttpResponse(save_virtual_workbook(
            wb), content_type="application/vnd.ms-excel")
        response['Content-Disposition'] = "attachment; filename=visits.xlsx"
        return response

    return render(request, 'index.html', context)
示例#29
0
def cell_style(
    *,
    wb: Workbook,
    style_name: str = 'cell_style',
    font_name: Optional[str] = 'Lucida Sans',
    font_size: Optional[int] = 11,
    font_bold: Optional[bool] = True,
    font_colour: Optional[str] = '000000',
    horizontal_alignment: Optional[str] = 'center',
    vertical_alignment: Optional[str] = 'center',
    wrap_text: Union[str, bool] = None,
    fill_type: Union[str, bool] = 'solid',
    foreground_colour: Union[str, bool] = 'd9d9d9',
    border_style: Union[str, bool] = None,
    border_colour: Union[str, bool] = None,
    number_format: Union[str, bool] = None
) -> NamedStyle:
    """
    Define a cell style

    Parameters
    ----------
    wb : Workbook
        The workbook in which to define the cell style.
    style_name : str = 'cell_style'
        The name for the cell style.
    font_name : Optional[str] = 'Lucida Sans'
        The font name for the style.
    font_size : Optional[int] = 11
        The font size for the style.
    font_bold : Optional[bool] = True
        A boolean or string to apply bold style.
    font_colour : Optional[str] = 'ffffff'
        The string for the font colour.
    horizontal_alignment : Optional[str] = 'center'
        The string for horizontal alignment.
    vertical_alignment : Optional[str] = 'center'
        The string for vertical alignment.
    wrap_text : Union[str, bool] = None
        A boolean or string to wrap text.
    fill_type : Optional[str] = 'solid'
        The string for the fill type.
    foreground_colour : Optional[str] = 'd9d9d9'
        The string for the foreground colour.
    border_style : Union[str, bool] = None
        A boolean or string to apply a border.
    border_colour : Union[str, bool] = None
        A boolean or string to apply a border colour.
    number_format : Union[str, bool] = None
        A boolean or string to apply a number format.

    Returns
    -------
    row_style : NamedStyle
        The named style.

    Example
    -------
    >>> red_cell_style = ds.cell_style(
    >>>     style_name='red_cell_style',
    >>>     font_colour='ffffff',
    >>>     foreground_colour='c00000'
    >>> )
    >>> wb.add_named_style(red_cell_style)
    >>> for cell in ['C1', 'D1', 'E1']:
    >>>     ws[cell].style = red_cell_style
    """
    cell_style = NamedStyle(name=style_name)
    cell_style.font = Font(
        name=font_name,
        size=font_size,
        bold=font_bold,
        color=font_colour
    )
    cell_style.alignment = Alignment(
        horizontal=horizontal_alignment,
        vertical=vertical_alignment,
        wrap_text=wrap_text
    )
    cell_style.fill = PatternFill(
        fill_type=fill_type,
        fgColor=foreground_colour
    )
    cell_style.border = Border(
        bottom=Side(
            border_style=border_style,
            color=border_colour
        )
    )
    cell_style.number_format = number_format
    wb.add_named_style(cell_style)
    return (wb, cell_style)
driver.find_element(By.XPATH, ".//input[contains(@id,'search')]").send_keys("iphone")
driver.find_element(By.XPATH, ".//input[contains(@value,'Go')]").click()

sleep(3)

phones = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-color-base a-text-normal')]")
prices = driver.find_elements(By.XPATH, ".//span[contains(@class,'a-price-whole')]")

phone_names = []
phone_prices = []

for phone in phones:
    phone_names.append(phone.text)
    
for price in prices:
    phone_prices.append(price.text)
    
final_list = zip(phone_names, phone_prices)

wb = Workbook()
wb['Sheet'].title = 'iPhone Data'
sheet = wb.active

sheet.append(["Phone Name", "Price"])

for i in list(final_list):
    sheet.append(i)

wb.save("iPhonesList.xlsx")

driver.quit()
示例#31
0
 def __init__(self, archive, workbook_part_name):
     self.archive = archive
     self.workbook_part_name = workbook_part_name
     self.wb = Workbook()
     self.sheets = []
示例#32
0
# from openpyxl import Workbook
import openpyxl
import os.path

from openpyxl.workbook.workbook import Workbook

xlsx_file = './Coin_Trading_Bot.xlsx'
sheet = 'trading_bot_revenue'
# workbook 생성 (덮어쓰기)
# write_wb = Workbook()
if os.path.isfile(xlsx_file):
    write_wb = openpyxl.load_workbook(xlsx_file)
else:
    write_wb = Workbook()
    write_ws = write_wb.create_sheet(sheet)

# Set write worksheet
sheet_list = write_wb.sheetnames
flag = 0
for sheet_name in sheet_list:
    if sheet_name == 'Sheet':
        # 기본 생성 시트 제거
        write_wb.remove(write_wb[sheet_name])
    if sheet_name == sheet:
        flag = 1
        break

if flag == 0:
    write_ws = write_wb.create_sheet('trading_bot_revenue')
else:
    write_ws = write_wb['trading_bot_revenue']