Skip to content

Commit 973fbca

Browse files
Ste452Ste452
committed
Modifying my old repository to submit my work
1 parent 8b2a635 commit 973fbca

File tree

203 files changed

+163050
-0
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

203 files changed

+163050
-0
lines changed

‎.gitignore

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,3 +158,5 @@ cython_debug/
158158
# and can be added to the global gitignore or merged into this file. For a more nuclear
159159
# option (not recommended) you can uncomment the following to ignore the entire idea folder.
160160
#.idea/
161+
162+
*CEP_Interface_Grafica

‎10_section_Advanced_Python_Excel_Breaking_Files_and_Creating_Emails/Breaking+Files+and+Emails.ipynb

Lines changed: 398 additions & 0 deletions
Large diffs are not rendered by default.

‎10_section_Advanced_Python_Excel_Breaking_Files_and_Creating_Emails/excel/Email_list.xlsx

10.1 KB
Binary file not shown.

‎10_section_Advanced_Python_Excel_Breaking_Files_and_Creating_Emails/excel/break.xlsx

5.61 KB
Binary file not shown.

‎11_section_Python_Excel_Consolidating_Files_with_pandas_and_openpyxl/Consolidating+Files.ipynb

Lines changed: 226 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,226 @@
1+
{
2+
"cells": [
3+
{
4+
"cell_type": "code",
5+
"execution_count": 1,
6+
"id": "67abde77",
7+
"metadata": {},
8+
"outputs": [
9+
{
10+
"name": "stdout",
11+
"output_type": "stream",
12+
"text": [
13+
"Requirement already satisfied: pandas in c:\\users\\aluno\\anaconda3\\lib\\site-packages (1.2.4)\n",
14+
"Requirement already satisfied: numpy>=1.16.5 in c:\\users\\aluno\\anaconda3\\lib\\site-packages (from pandas) (1.20.1)\n",
15+
"Requirement already satisfied: python-dateutil>=2.7.3 in c:\\users\\aluno\\anaconda3\\lib\\site-packages (from pandas) (2.8.1)\n",
16+
"Requirement already satisfied: pytz>=2017.3 in c:\\users\\aluno\\anaconda3\\lib\\site-packages (from pandas) (2021.1)\n",
17+
"Requirement already satisfied: six>=1.5 in c:\\users\\aluno\\anaconda3\\lib\\site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)\n"
18+
]
19+
}
20+
],
21+
"source": [
22+
"!pip install pandas"
23+
]
24+
},
25+
{
26+
"cell_type": "code",
27+
"execution_count": 5,
28+
"id": "4e9f75e1",
29+
"metadata": {},
30+
"outputs": [
31+
{
32+
"name": "stdout",
33+
"output_type": "stream",
34+
"text": [
35+
"['Amanda Martins.xlsx', 'Eliane Moreira.xlsx', 'Leonardo Almeida.xlsx', 'Nicolas Pereira.xlsx']\n",
36+
"----- #### -------- ###### ---------\n",
37+
"----- #### -------- ###### ---------\n",
38+
"['C:\\\\Users\\\\Aluno\\\\Desktop\\\\Curso RPA\\\\Consolindando Arquivos\\\\Excel\\\\Amanda Martins.xlsx', 'C:\\\\Users\\\\Aluno\\\\Desktop\\\\Curso RPA\\\\Consolindando Arquivos\\\\Excel\\\\Eliane Moreira.xlsx', 'C:\\\\Users\\\\Aluno\\\\Desktop\\\\Curso RPA\\\\Consolindando Arquivos\\\\Excel\\\\Leonardo Almeida.xlsx', 'C:\\\\Users\\\\Aluno\\\\Desktop\\\\Curso RPA\\\\Consolindando Arquivos\\\\Excel\\\\Nicolas Pereira.xlsx']\n"
39+
]
40+
}
41+
],
42+
"source": [
43+
"import pandas as opcoesDoPanda\n",
44+
"import os\n",
45+
"\n",
46+
"#Caminho onde estão os arquivos\n",
47+
"caminhoArquivos = r\"C:\\Users\\Aluno\\Desktop\\Curso RPA\\Consolindando Arquivos\\Excel\"\n",
48+
"\n",
49+
"#Variável onde estão todos os arquivos\n",
50+
"listaArquivos = os.listdir(caminhoArquivos)\n",
51+
"\n",
52+
"print(listaArquivos)\n",
53+
"\n",
54+
"#Listando todos os arquivos + o caminho\n",
55+
"listaCaminhoEArquivoExcel = [caminhoArquivos + '\\\\' + arquivo for arquivo in listaArquivos if arquivo[-4:] == 'xlsx' ]\n",
56+
"\n",
57+
"print(\"----- #### -------- ###### ---------\")\n",
58+
"print(\"----- #### -------- ###### ---------\")\n",
59+
"print(listaCaminhoEArquivoExcel)\n",
60+
"\n",
61+
"#Criando um DataFrame para trabalhar com os dados dos arquivos\n",
62+
"dadosArquivo = opcoesDoPanda.DataFrame()\n",
63+
"\n",
64+
"#Copiando todos os dados dos arquivos em DadosArquivo\n",
65+
"for arquivo in listaCaminhoEArquivoExcel:\n",
66+
" dados = opcoesDoPanda.read_excel(arquivo)\n",
67+
" dadosArquivo = dadosArquivo.append(dados)\n",
68+
" \n",
69+
"#Criando uma nova planilha e passando os dados dos arquivos\n",
70+
"dadosArquivo.to_excel(r\"C:\\Users\\Aluno\\Desktop\\Curso RPA\\Consolindando Arquivos\\Arquivo Consolidado.xlsx\")"
71+
]
72+
},
73+
{
74+
"cell_type": "code",
75+
"execution_count": 19,
76+
"id": "09de1470",
77+
"metadata": {},
78+
"outputs": [
79+
{
80+
"name": "stdout",
81+
"output_type": "stream",
82+
"text": [
83+
"17\n"
84+
]
85+
}
86+
],
87+
"source": [
88+
"import pandas as opcoesDoPanda\n",
89+
"import os\n",
90+
"\n",
91+
"#Caminho onde estão os arquivos\n",
92+
"caminhoArquivos = r\"C:\\Users\\Aluno\\Desktop\\Curso RPA\\Consolindando Arquivos\\Excel\"\n",
93+
"\n",
94+
"#Variável onde estão todos os arquivos\n",
95+
"listaArquivos = os.listdir(caminhoArquivos)\n",
96+
"\n",
97+
"#Listando todos os arquivos + o caminho\n",
98+
"listaCaminhoEArquivoExcel = [caminhoArquivos + '\\\\' + arquivo for arquivo in listaArquivos if arquivo[-4:] == 'xlsx' ]\n",
99+
"\n",
100+
"#Criando um DataFrame para trabalhar com os dados dos arquivos\n",
101+
"dadosArquivo = opcoesDoPanda.DataFrame()\n",
102+
"\n",
103+
"#Copiando todos os dados dos arquivos em DadosArquivo\n",
104+
"for arquivo in listaCaminhoEArquivoExcel:\n",
105+
" dados = opcoesDoPanda.read_excel(arquivo)\n",
106+
" dadosArquivo = dadosArquivo.append(dados)\n",
107+
" \n",
108+
"#Criando uma nova planilha e passando os dados dos arquivos\n",
109+
"dadosArquivo.to_excel(r\"C:\\Users\\Aluno\\Desktop\\Curso RPA\\Consolindando Arquivos\\Arquivo Consolidado.xlsx\")\n",
110+
"\n",
111+
"#################################################################\n",
112+
"\n",
113+
"from openpyxl import load_workbook\n",
114+
"from openpyxl import workbook\n",
115+
"\n",
116+
"from openpyxl.styles import Color, PatternFill, Font, Border, Side\n",
117+
"from openpyxl.styles import colors\n",
118+
"from openpyxl.cell import Cell\n",
119+
"\n",
120+
"caminhoArquivoDadosSistema = \"C:\\\\Users\\\\Aluno\\\\Desktop\\\\Curso RPA\\\\Consolindando Arquivos\\\\Arquivo Consolidado.xlsx\"\n",
121+
"planilhaDadosSistema = load_workbook(filename=caminhoArquivoDadosSistema)\n",
122+
"\n",
123+
"#Seleciona a sheet1\n",
124+
"sheetPlanilhaDadosSistema = planilhaDadosSistema['Sheet1']\n",
125+
"\n",
126+
"#Deleta a coluna A\n",
127+
"sheetPlanilhaDadosSistema.delete_cols(1)\n",
128+
"\n",
129+
"#Renomeia o nome da sheet\n",
130+
"sheetPlanilhaDadosSistema.title = 'Dados Consolidados'\n",
131+
"\n",
132+
"#Aumenta a largura das colunas A e B\n",
133+
"sheetPlanilhaDadosSistema.column_dimensions['A'].width = 35\n",
134+
"sheetPlanilhaDadosSistema.column_dimensions['B'].width = 40\n",
135+
"\n",
136+
"#Criando preenchimento cor Cinza fraco\n",
137+
"corCinza = PatternFill(start_color='00FFFFFF',\n",
138+
" end_color='00FFFFFF',\n",
139+
" fill_type='solid')\n",
140+
"\n",
141+
"#Criando preenchimento cor Amarela\n",
142+
"corAmerelo = PatternFill(start_color='00FFFFCC',\n",
143+
" end_color='00FFFFCC',\n",
144+
" fill_type='solid')\n",
145+
"\n",
146+
"#Coloca borda preta na célula\n",
147+
"bfFina = Side(border_style='thin', color='000000')\n",
148+
"borda = Border(left=bfFina, right=bfFina, top=bfFina, bottom=bfFina)\n",
149+
"\n",
150+
"#Colocando cores e borda e fundo nas celulas A1, B1 e C1\n",
151+
"sheetPlanilhaDadosSistema['A1'].fill = corAmerelo\n",
152+
"sheetPlanilhaDadosSistema['B1'].fill = corAmerelo\n",
153+
"sheetPlanilhaDadosSistema['C1'].fill = corAmerelo\n",
154+
"sheetPlanilhaDadosSistema['A1'].border = borda\n",
155+
"sheetPlanilhaDadosSistema['B1'].border = borda\n",
156+
"sheetPlanilhaDadosSistema['C1'].border = borda\n",
157+
"\n",
158+
"#Dando um for linha por linha para pintar as bordas e o fundo\n",
159+
"for linha in range(2, len(sheetPlanilhaDadosSistema['A']) + 1):\n",
160+
" \n",
161+
" #Criando celula A2, B2, C2 e assim por diante de acordo com o número da linha\n",
162+
" celulaColunaA = 'A' + str(linha)\n",
163+
" celulaColunaB = 'B' + str(linha)\n",
164+
" celulaColunaC = 'C' + str(linha)\n",
165+
" \n",
166+
" #Colorindo o fundo de cinza claro das linhas\n",
167+
" sheetPlanilhaDadosSistema[celulaColunaA].fill = corCinza\n",
168+
" sheetPlanilhaDadosSistema[celulaColunaB].fill = corCinza\n",
169+
" sheetPlanilhaDadosSistema[celulaColunaC].fill = corCinza\n",
170+
" \n",
171+
" #Colocando a borda preta nas linhas\n",
172+
" sheetPlanilhaDadosSistema[celulaColunaA].border = borda\n",
173+
" sheetPlanilhaDadosSistema[celulaColunaB].border = borda\n",
174+
" sheetPlanilhaDadosSistema[celulaColunaC].border = borda\n",
175+
" \n",
176+
"#Achando a ultima linha e somando + 1\n",
177+
"ultimaLinhaMaisUm = linha + 1\n",
178+
"print(ultimaLinhaMaisUm)\n",
179+
"\n",
180+
"#Desenhando a coluna C + a última linha para usar na variável\n",
181+
"celulaUltimaLinha = 'C' + str(ultimaLinhaMaisUm)\n",
182+
"\n",
183+
"#Desenhando a fúrmula da soma\n",
184+
"formulaSoma = \"=SUM(C2:C\" + str(linha) + \")\"\n",
185+
"\n",
186+
"#Imprimindo a fórmula de soma na planilha\n",
187+
"sheetPlanilhaDadosSistema[celulaUltimaLinha] = formulaSoma\n",
188+
"\n",
189+
"#Salva a planilha com as alterações\n",
190+
"planilhaDadosSistema.save(filename=caminhoArquivoDadosSistema)\n",
191+
"\n",
192+
"#Abre a planilha\n",
193+
"os.startfile(caminhoArquivoDadosSistema)"
194+
]
195+
},
196+
{
197+
"cell_type": "code",
198+
"execution_count": null,
199+
"id": "d2e23777",
200+
"metadata": {},
201+
"outputs": [],
202+
"source": []
203+
}
204+
],
205+
"metadata": {
206+
"kernelspec": {
207+
"display_name": "Python 3",
208+
"language": "python",
209+
"name": "python3"
210+
},
211+
"language_info": {
212+
"codemirror_mode": {
213+
"name": "ipython",
214+
"version": 3
215+
},
216+
"file_extension": ".py",
217+
"mimetype": "text/x-python",
218+
"name": "python",
219+
"nbconvert_exporter": "python",
220+
"pygments_lexer": "ipython3",
221+
"version": "3.8.8"
222+
}
223+
},
224+
"nbformat": 4,
225+
"nbformat_minor": 5
226+
}

‎11_section_Python_Excel_Consolidating_Files_with_pandas_and_openpyxl/excel/Amanda+Martins.xlsx

4.96 KB
Binary file not shown.

‎11_section_Python_Excel_Consolidating_Files_with_pandas_and_openpyxl/excel/Eliane+Moreira.xlsx

5.05 KB
Binary file not shown.

‎11_section_Python_Excel_Consolidating_Files_with_pandas_and_openpyxl/excel/Leonardo+Almeida.xlsx

5.07 KB
Binary file not shown.

‎11_section_Python_Excel_Consolidating_Files_with_pandas_and_openpyxl/excel/Nicolas+Pereira.xlsx

4.99 KB
Binary file not shown.

0 commit comments

Comments
 (0)