def autofit_excel(wkbk, list_of_sheetnames_or_number=None):
"""
Autofits excel sheets columns and rows
Arguments:
wkbk {xlwings workbook} -- Xlwings Workbook object
Keyword Arguments:
list_of_sheetnames_or_number {list} -- List of strings or integer (default: {None})
"""
try:
wb = xw.Workbook(wkbk)
print(wb.fullname)
if list_of_sheetnames_or_number:
for i in list_of_sheetnames_or_number:
xw.Sheet(i).autofit()
else:
for i in xw.Sheet.all():
i.autofit()
except TypeError:
print(
"Excel file could not be found, Available excel files are as follows:"
)
wb = xw.Workbook()
if len(wb.xl_app.Workbooks) == 1:
print("There is no opened Excel Workbook")
else:
for i in wb.xl_app.Workbooks:
print(i.Name)
wb.close()
def _make_sample_workbook(sheetname, addr, value):
import xlwings as xw
wb = xw.Workbook()
xw.Sheet(1).name = sheetname
xw.Range(addr).value = value
return wb
def __init__(self, excel_file):
# Constructor requires the name of the excel file
# note: this must be the full filename including path
self.wb = xlwings.Workbook(excel_file)
self.controlsheet = 'control'
self.datasheet = 'data'
# populate the basic parameters for the model
self.start_row = int(xlwings.Range(self.controlsheet, 'B3').value)
self.end_row = int(xlwings.Range(self.controlsheet, 'B4').value)
self.timesteps = self.end_row - self.start_row + 1
self.interim_prefix = str(xlwings.Range(self.controlsheet, 'B8').value)
self.topkml = str(xlwings.Range(self.controlsheet, 'B5').value)
self.bottomkml = str(xlwings.Range(self.controlsheet, 'B6').value)
self.oputkml = str(xlwings.Range(self.controlsheet, 'B7').value)
# get debugging boolean
debug_cell = str(xlwings.Range(self.controlsheet, 'B9').value)
if debug_cell == 'Y' or debug_cell == 'y':
self.debug = True
else:
self.debug = False
self.message('EXCEL link established, passing control to model core')
return
def _open_my_workbook():
excel_fname = os.path.join(
os.path.dirname(__file__),
'%s.xlsm' % os.path.splitext(os.path.basename(__file__))[0])
wb_path = os.path.abspath(excel_fname)
wb = xw.Workbook(wb_path)
return wb
def _get_active_workbook():
from win32com.client import dynamic #@UnusedImport
com_app = dynamic.Dispatch('Excel.Application') #@UndefinedVariable
com_wb = com_app.ActiveWorkbook
wb = xw.Workbook(xl_workbook=com_wb)
return wb
def get_active_workbook():
from win32com.client import dynamic
com_app = dynamic.Dispatch("Excel.Application")
com_wb = com_app.ActiveWorkbook
wb = xw.Workbook(xl_workbook=com_wb)
return wb
def test_excel_runner_call_from_python(self):
import xlwings as xw
from ..excel import FuefitExcelRunner
wb = xw.Workbook(from_my_path('..', 'excel', 'FuefitExcelRunner'))
try:
FuefitExcelRunner.main()
finally:
close_workbook(wb)
def ExcelReadtoList():
import tkinter as tk
from tkinter import filedialog
root = tk.Tk()
root.withdraw()
F = filedialog.askopenfilename()
import xlwings as xw
wb = xw.Workbook(F)
Results = {}
Result = {}
endRow = 500
endCol = 100
sheetNo = 1
while True:
try:
carrier = xw.Sheet(sheetNo).name
Results[carrier] = xw.Range(sheetNo, (1, 1),
(endRow, endCol)).value
maxCol = 1
for i in range(0, endRow):
countCol = endCol - 1
for j in range(endCol - 1, -1, -1):
if Results[carrier][i][j] != None:
break
else:
countCol -= 1
if maxCol < countCol:
maxCol = countCol
maxRow = 1
for i in range(0, endCol):
countRow = endRow - 1
for j in range(endRow - 1, -1, -1):
if Results[carrier][j][i] != None:
break
else:
countRow -= 1
if maxRow < countRow:
maxRow = countRow
Result[carrier] = xw.Range(sheetNo, (1, 1),
(maxRow + 1, maxCol + 1)).value
sheetNo += 1
except:
wb.close()
print(
'Completed!\nBe noted maximum of %d rows and %d columns have been tested!!!'
% (endRow, endCol))
return Result
def cycle_files():
files = get_file_list()
new_path = "RS_small_pckg\\uploads"
for file in files:
wb = xw.Workbook(file, app_visible=False)
set_row_vals()
wb.xl_workbook.SaveAs(file.replace("RS_small_pckg", new_path),
xw.FileFormat.xlExcel8)
def get_Workbook(wrkb_fname):
"""
:param str wrkb_fname: if missing return the active excel-workbook
"""
if wrkb_fname:
wb = xw.Workbook(wrkb_fname)
else:
wb = get_active_workbook()
return wb
def xlswriter():
import xlsxwriter as xw
#新建excel
workbook = xw.Workbook('myexcel.xlsx')
#新建工作薄
worksheet = workbook.add_worksheet()
#写入数据
worksheet.wirte('A1', 1)
#关闭保存
workbook.close()
def test_xlwings_smoketest(self):
import xlwings as xw
sheetname = 'shitt'
addr = 'f6'
table = pd.DataFrame([[1, 2], [True, 'off']], columns=list('ab'))
wb = xw.Workbook()
try:
xw.Sheet(1).name = sheetname
xw.Range(addr).value = table
finally:
close_workbook(wb)
def test_xlwings_smoketest(self):
import xlwings as xw
sheetname = "shitt"
addr = "f6"
table = pd.DataFrame([[1, 2], [True, "off"]], columns=list("ab"))
wb = xw.Workbook()
try:
xw.Sheet(1).name = sheetname
xw.Range(addr).value = table
finally:
close_workbook(wb)
def readExcel(self):
self.wb = xw.Workbook(self.excelPath)
self.sheet = xw.Sheet('PythonOrder')
self.range = xw.Range(self.excelArea)
self.__orderList = [OrderedDict()]
self.__tempParaList = []
for row in self.range.value:
paraName, paraValue = row[0], row[1]
if paraName is not None:
if paraName in self.__tempParaList:
self.__tempParaList = []
self.__orderList.append(OrderedDict())
self.__tempParaList.append(paraName)
self.__orderList[-1][str(paraName)] = self._paraType[str(
paraName)](paraValue)
def ms_Receipts(row):
wb_output = xw.Workbook(row['FilePath'])
f_copy_formula = wb.macro('Copy_Formula')
# tab Receipt Report
c_new = xw.Range('Receipt Report', 'A3').vertical.last_cell.offset(
1).get_address()
n_row_start = xw.Range('Receipt Report', 'A3').vertical.last_cell.row
write_in_chunks(wb_output, 'Receipt Report',
c_new, dict_df['Receipt Report'].values)
n_row_end = xw.Range('Receipt Report', 'A3').vertical.last_cell.row
f_copy_formula('Receipt Report',
'BU' + str(n_row_start) + ':CE' + str(n_row_start),
'BU' + str(n_row_start) + ':CE' + str(n_row_end))
xw.Range('Receipt Report', 'CD3:CD' + str(n_row_start)).value = xw.Range('Receipt Report', 'CD3:CD' + str(n_row_start)).value
# tab PIM Vendors
xw.Range('PIM Vendors', 'A1').table.clear_contents()
xw.Range('PIM Vendors', 'A1').value = dict_df[
'PIM Vendors'].columns.tolist()
xw.Range('PIM Vendors', 'A2').value = dict_df['PIM Vendors'].values
# tab PIM Product
xw.Range('PIM Products', 'A1').table.clear_contents()
xw.Range('PIM Products', 'A1').value = dict_df[
'PIM Products'].columns.tolist()
write_in_chunks(wb_output, 'PIM Products', 'A2',
dict_df['PIM Products'].values)
# tab PIM Sample
xw.Range('PIM Samples', 'A1').table.clear_contents()
xw.Range('PIM Samples', 'A1').value = dict_df[
'PIM Samples'].columns.tolist()
write_in_chunks(wb_output, 'PIM Samples', 'A2',
dict_df['PIM Samples'].values)
# save
wb_output.save()
wb_output.close()
return "Success"
def close(self):
"""Save and close excel file and add specified formatting
"""
self.pdwriter.close()
# add excel formatting for each dataframe
wb = xlwings.Workbook(self._path)
for sheet_name, spacing, column_formats, row_formats, add_color_rows, \
autofit, include_index, include_header in zip(self._sheet_name,
self._spacing,
self._column_formats,
self._row_formats,
self._add_color_rows,
self._autofit,
self._include_index,
self._include_header):
_add_excel_formatting(sheet_name, spacing, column_formats,
row_formats, add_color_rows, autofit,
include_index, include_header)
wb.save()
wb.close()
def ms_Sales(row):
wb_output = xw.Workbook(row['FilePath'])
f_copy_formula = wb.macro('Copy_Formula')
# tab PIM Vendors
xw.Range('PIM Vendor', 'A1').table.clear_contents()
xw.Range('PIM Vendor', 'A1').value = dict_df[
'PIM Vendors'].columns.tolist()
xw.Range('PIM Vendor', 'A2').value = dict_df['PIM Vendors'].values
# tab PIM Product
xw.Range('PIM Product', 'B1').table.clear_contents()
xw.Range('PIM Product', 'B1').value = dict_df[
'PIM Products'].columns.tolist()
#xw.Range('PIM Product','B2').value = dict_df['PIM Products'].values
write_in_chunks(wb_output, 'PIM Product', 'B2',
dict_df['PIM Products'].values)
n_row = xw.Range('PIM Product', 'B2').vertical.last_cell.row
xw.Range('PIM Product', 'A3').vertical.clear_contents()
f_copy_formula('PIM Product', 'A2', 'A2:A' + str(n_row))
# tab Looker Pull
xw.Range('Looker Pull', 'D2:L2').vertical.clear_contents()
xw.Range('Looker Pull', 'D2').value = dict_df[
'Sales, Discounts, Points'].values
n_row = xw.Range('Looker Pull', 'D2').vertical.last_cell.row
#xw.Range('Looker Pull','D' + str(n_row + 1)).vertical.clear_contents()
xw.Range('Looker Pull', 'A3:C3').vertical.clear_contents()
f_copy_formula('Looker Pull', 'A2:C2', 'A2:C' + str(n_row))
xw.Range('Looker Pull', 'M3:O3').vertical.clear_contents()
f_copy_formula('Looker Pull', 'M2:O2', 'M2:O' + str(n_row))
xw.Range('Looker Pull', str(n_row + 1) +
":" + str(n_row + 1)).clear_contents()
# save
wb_output.save()
wb_output.close()
return "Success"
def to_xls(path):
fcsv = glob.glob(path + "/CSV Data/*.txt")[-1] # File containing CSV
fxls = glob.glob(path +
"/Layout/*.xlsx")[-1] # xlsx file layout for report
wb = xw.Workbook(fxls)
data = file2df(fcsv)
for index, row in data.iterrows():
loc = data[0][index]
value = data[1][index]
if np.isnan(data[4][index]):
if np.isnan(data[3][index]):
color = color_OK
else:
color = color_NOK
elif np.isnan(data[6][index]):
color = color_OK
else:
color = color_NOK
xw.Range(str(loc)).value = value
xw.Range(str(loc)).color = color
os.system("pause")
wb.xl_workbook.PrintOut()
wb.close()
import xlwings as xw
import pandas as pd
from pandas.util.testing import assert_frame_equal
from bokeh.client import push_session
from bokeh.charts import Line, Bar
from bokeh.charts.operations import blend
from bokeh.models import Paragraph
from bokeh.io import curdoc, hplot, vplot
wb = xw.Workbook() # Creates a connection with a new workbook
# write example data to notebook
xw.Range('A1').value = pd.DataFrame({
'Italy': [
3016.17, 3114.73, 3128.31, 3137.38, 3089.51, 3016.32, 2942.62, 2735.05,
2813.51
],
'Japan': [
4004.67, 3963.47, 4089.39, 4073.75, 4068.52, 4031.99, 3880.45, 3700.22,
3883.046557
],
'Brazil': [
1084.48, 1075.76, 1092.31, 1096.13, 1140.61, 1158.39, 1186.27, 1240.22,
1297.91
],
'USA': [
8056.55, 7825.18, 7838.52, 7788.32, 7875.28, 7840.53, 7691.69, 7749.23,
7481.02
],
'year': [2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008],
})
[u'USIM5'], [u'VALE3'], [u'VALE5'], [u'VIVT4'], [u'WEGE3']]
}
cash = 100E3
# DB = DataBaseLoader('Bovespa_2010_2016.pkl')
start_aq = (2014, 1, 1)
end_aq = (2016, 4, 27)
AqRange = [start_aq, end_aq]
start_op = (2015, 1, 1)
end_op = (2016, 4, 27)
OpRange = [start_op, end_op]
eachStock = 0
xl.Workbook()
for stocks in indice['IBrX50']:
# ==============================================================================
trader = robot.backtest_simulation_acquisition(StrategyLocation,
stocks,
cash,
AqRange,
OpRange,
risklib.bet_all,
brokerfee=14.9,
leverage=1.,
interest=1.1913,
stop_win=None,
stop_loss=None,
operation_period=0,
#os.system("pause")
def file2df(fname):
"""CSV import function"""
data = pd.read_csv(
fname,
header=None,
sep=None,
index_col=False,
)
return data
wb = xw.Workbook(fxls)
data = file2df(fcsv)
for index, row in data.iterrows():
# os.system("pause")
loc = data[0][index]
value = data[1][index]
if np.isnan(data[4][index]):
if np.isnan(data[3][index]):
color = color_OK
else:
color = color_NOK
elif np.isnan(data[6][index]):
color = color_OK
else:
color = color_NOK
#color = (255,255,255)
if min == 0:
min = len(tmplist)
minname = key
if len(tmplist) > max:
max = len(tmplist)
maxname = key
elif len(tmplist) < min:
min = len(tmplist)
minname = key
columns = list(sorted(outdict[maxname].keys()))
xlout.append(columns)
row = []
for key in sorted(outdict.keys()):
row = [key]
for col in columns:
try:
row.append(outdict[key][col])
except KeyError:
row.append('')
xlout.append(row)
wb = xw.Workbook()
rownum = 1
for i in range(0, len(xlout)):
xw.Range('Sheet1', 'A' + str(rownum)).value = xlout[i]
rownum += 1
def openWorkbook(path):
'''
Abre un workbook y retorna un objecto workbook de xlwings.
'''
wb = xw.Workbook(path)
return wb
print 'Indicators calculated.'
trader.set_database(indicators)
trader.set_workingdays(working_days)
workmemory = data_handler.workmemory_builder(stocks)
if current_date <= trader.last_operational_date:
print 'You have already operated in this date.'
ans = input('Should I reprint your orders? 1 for yes 0 for no.')
if ans:
xlwings.Workbook()
index = 1
for stock in trader.operation_log.keys():
xlwings.Range(1, (index, 1)).value = stock
xlwings.Range(1, (index, 4)).value = 'OPERATION'
xlwings.Range(1, (index, 5)).value = 'VOLUME'
xlwings.Range(1, (index, 6)).value = 'PRICE'
xlwings.Range(1, (index, 7)).value = 'INFERENCE'
index += 1
operations = trader.operation_log[stock]
#!python
print("Setting:", k, cell_map[k], v)
xw.Range("ANN(PGA)", cell_map[k], wkb=wb).value = v
# Force a re-calculation. This is the same as Ctrl + Alt + F9
wb.xl_app.CalculateFull()
def get_results(wb):
d = {}
# Collect PGA and PGV
for key, row in zip(["pga", "pgv"], [13, 10]):
d[key] = xw.Range("ANN(PGA)", "O%d" % row, wkb=wb).value
# Collect response spectrum
for key, rc in zip(["periods", "spec_accels"], "NO"):
d[key] = xw.Range("ANN(PGA)", "%s17:%s78" % (rc, rc), wkb=wb).value
return d
wb_fname = os.path.abspath("10518_2013_9481_MOESM1_ESM.xlsx")
tests = []
for p in iter_parameters(params):
wb = xw.Workbook(wb_fname)
load_params(wb, **p)
tests.append({"params": p, "results": get_results(wb)})
fname = "../tests/data/dbc14_tests.json.gz"
with gzip.open(fname, "wt") as fp:
json.dump(tests, fp, indent=4)
def setUp(self):
xl_file = os.path.join(this_dir, 'prime_numbers.xlsm')
self.wb = xw.Workbook(xl_file)
# Map functions
self.is_prime = self.wb.macro('Module1.is_prime')
def project_list(file):
# 작업 내용: 현재 예실대비 표에서 유지되는 당기 진행 과제 목록을 인쇄한다..
# 시트 타입: 시트이름이 문자면 요약 시트고, 숫자면 개별과제 시트로 구별한다.
# Test 방법: shtCount 변수에 적은 값을 넣어 테스트 해 본 후 전체를 실행한다.
print file
wb = xw.Workbook(file)
# 둘 다 같음.
sumShtName = "과제목록"
# 둘 다 같음.
formShtName = "공통서식"
# 둘 다 같음.
shtCount = 20 # 테스트 할 시트 수... =20-12\
#VB ''shtCount = Sheets.Count ''당기 총 시트 수... 테스트 동안은 코멘트 상태로
# shtCount = xw.Sheet.count() # 당기 총 시트 수... 테스트 동안은 코멘트 상태로
#VB If shtCount > Sheets.Count Then
#VB shtCount = Sheets.Count
#VB End If
if shtCount > xw.Sheet.count():
shtCount = xw.Sheet.count()
print "진짜 시트 개수 : ", xw.Sheet.count(), "테스트 시트 개수 : " , shtCount
#VB ''--------------------------------------------------------------------------------
# --------------------------------------------------------------------------------
#VB Dim nameRow As Integer: nameRow = 1 ''표이름
nameRow = 1 # 표이름
#VB Dim yearRow As Integer: yearRow = 2 ''당기연도
yearRow = 2 # 당기연도
#VB Dim supplyRow As Integer ''공급표
#VB Dim demandRow As Integer ''수요표
#VB Dim listRow As Integer ''목록표
#VB Dim listContRow As Integer ''목록 내용
#VB Dim i As Integer
#VB Dim j As Integer
#VB Dim usedRows As Integer
#VB Dim projName As String
#VB Dim sumShtCount As Integer ''시트 이름 갯수, non-numeric
#VB Dim shtName As String
#VB Dim sumSht As Worksheet ''요약 시트
#VB Dim formSht As Worksheet
#VB Dim ws As Worksheet ''개별 시트
#VB ''상수값 초기화, Run time 에서
#VB Sheets(formShtName).Activate
#VB Set formSht = Sheets(formShtName)
xw.Sheet(formShtName).activate()
formSht = xw.Sheet(formShtName)
#VB Sheets(sumShtName).Activate
#VB Set sumSht = Sheets(sumShtName)
xw.Sheet(sumShtName).activate()
sumSht = xw.Sheet(sumShtName)
#VB Set sumSht = Application.ActiveSheet
# sumSht = Application.ActiveSheet <= 주석 풀어야 함. <= 안 써도 될 듯
#VB usedRows = sumSht.UsedRange.Rows.Count
# https://github.com/ZoomerAnalytics/xlwings/issues/112
usedRows = len(sumSht.xl_sheet.UsedRange.Rows)
#VB supplyRow = WorksheetFunction.Match("공급표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0)
#VB demandRow = WorksheetFunction.Match("수요표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0)
#VB listRow = WorksheetFunction.Match("목록표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0)
#VB listContRow = WorksheetFunction.Match("목록식1", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0)
supplyRow = get_match_row(u"공급표", [1,2], usedRows)
demandRow = get_match_row(u"수요표", [1,2], usedRows)
listRow = get_match_row(u"목록표", [1,2], usedRows)
listContRow = get_match_row(u"목록식1", [1,2], usedRows)
#print supplyRow
#print demandRow
#print listRow
#print listContRow
#VB ''목록표 머리줄과 내용 사이 지움
#VB sumSht.Range("A" & listRow + 1 & ":AC" & listContRow - 1).Clear
xw.Range("A"+str(listRow+1)+":AC"+str(listContRow - 1)).clear()
# 둘 다 같음.
sumShtCount = 0
#VB For i = 1 To shtCount
for i in xrange(1, shtCount+1):
#VB shtName = Sheets.Item(i).Name
shtName = xw.Sheet(i).name
print shtName
#VB ''다른 공통요약 시트에는 붙여넣기 안하고,
#VB ''개별 시트에만 붙여넣기 한다
#VB If Not IsNumeric(shtName) Then
#VB sumShtCount = sumShtCount + 1
#VB GoTo Next_wsBuf
#VB End If
if not shtName.isdigit():
sumShtCount += 1
continue
#VB Set ws = Sheets(shtName)
#ws = xw.Sheet(shtName)
#VB ws.Range("A" & nameRow & ":AC" & nameRow + 8).Clear
#VB ''이전에 사용한 I/F Buf를 지운다,
#VB ''총 9행 지운다.
xw.Range(shtName, "A"+str(nameRow)+":AC"+str(nameRow+8)).clear()
#VB ''개별시트 마다 요약표 이름을 등록함.
#VB sumSht.Range("B2") = sumShtName ''B2: 요약표 이름
xw.Range(sumShtName, "B2").value = sumShtName
print xw.Range(sumShtName, "B2").value, sumShtName
#VB sumSht.Range("A" & nameRow & ":AC" & yearRow).Copy _
#VB ws.Range("A" & nameRow)
xw.Range(shtName,"A"+str(nameRow)).value = xw.Range(sumShtName,"A"+str(nameRow)+":AC"+str(yearRow)).value
#VB sumSht.Range("A" & supplyRow & ":AC" & supplyRow + 2).Copy _
#VB ws.Range("A" & supplyRow)
# sdr1982 - 공급표의 아래 부분(supplyRow+1)은 값을 보내고 윗 부분(supplyRow)은 수식을 보냄.
xw.Sheet(shtName).activate()
xw.Range(shtName,"A"+str(supplyRow+1)).value = xw.Range(sumShtName,"A"+str(supplyRow+1)+":AC"+str(supplyRow+2)).value
xw.Range(shtName,"A"+str(supplyRow)+":AC"+str(supplyRow+1)).formula = xw.Range(sumShtName,"A"+str(supplyRow)+":AC"+str(supplyRow+1)).formula
#VB ''공통서식.과제기간,당기기간
#VB formSht.Range("B10:R15").Copy ws.Range("B10")
xw.Range(shtName,"B10").value = xw.Range(formShtName,"B10:R15").value
#VB ''Form시트에서 함수형 셀 복사, 서식과 수식 C&P
#VB ''formSht.Range("C1:T2").Copy ''한 번 copy해서 각기 다른 기능을 골라쓴다.
#VB ''ws.Range("C1").PasteSpecial xlPasteFormats
#VB ''ws.Range("C1").PasteSpecial xlPasteFormulas
#VB Next_wsBuf:
#VB Next i
#VB '' 과제번호 시트 목록을 개별 시트 C열에 작성한다.
#VB '' 다른 공통요약 시트들은 그대로 통과한다.
#VB '' 작성중인 공통요약 시트에서 이전 목록 내용을 지운 후, 다시 채운다
#VB sumSht.Range("C" & listContRow & ":AC" & usedRows).Clear
xw.Sheet(sumShtName).activate()
xw.Range(sumShtName, "C"+str(listContRow)+":AC"+str(usedRows)).clear()
# 둘 다 같음.
rowCount = listContRow #''
#VB For i = 1 To shtCount
shtSumCount = 0
for i in xrange(1, shtCount+1):
#VB shtName = Sheets.Item(i).Name
shtName = xw.Sheet(i).name
#VB If Not IsNumeric(shtName) Then
#VB sumShtCount = sumShtCount + 1
#VB GoTo Next_wsBuf
#VB End If
if not shtName.isdigit():
shtSumCount += 1
continue
#VB ''과제번호 열에 서식과 값 붙여넣기
#VB ''과제번호 열에 서식 먼저 붙여넣기
#VB sumSht.Range("C" & demandRow + 1).Copy ''중단점. 목록식1> 과제번호
#VB sumSht.Range("C" & rowCount + 0).PasteSpecial xlPasteFormats
xw.Range(sumShtName,"C"+str(rowCount)).value = xw.Range(sumShtName, "C"+str(demandRow+1)).value
#VB ''sumSht.Range("C" & demandRow + 2).Copy ''목록식2> 과제번호
#VB ''sumSht.Range("C" & rowCount + 1).PasteSpecial xlPasteFormats
#VB ''그리고 과제번호 열에 값 붙여넣기
#VB sumSht.Range("C" & rowCount + 0) = shtName
xw.Range(sumShtName,"C"+str(rowCount)).value = shtName
#VB ''sumSht.Range("C" & rowCount + 1) = shtName
#VB ''sumSht.Range("C" & rowCount + 2) = ""
# 둘 다 같음.
rowCount = rowCount + 1
#VB NextPIDList:
#VB Next i
#VB ''공통요약 셀에 서식 붙여넣기
#VB sumSht.Range("B" & listRow) = "목록표"
xw.Range(sumShtName,"B"+str(listRow)).value = "목록표"
print "B"+str(listRow), "목록표"
#VB sumSht.Range("A" & listContRow) = "listContRow"
xw.Range(sumShtName, "A"+str(listContRow)).value = "listContRow"
print "A"+str(listContRow), "listContRow"
#VB sumSht.Range("B" & listRow).Font.Color = RGB(255, 0, 0)
#xw.Range(sumShtName,"B"+str(listRow)).color = (255,0,0)
#VB sumSht.Range("B" & listRow).Font.Size = 12
#VB sumSht.Range("B" & listRow).HorizontalAlignment = xlCenter
#VB '' 공통요약 머리줄은 한 번만 인쇄한다.
#VB sumSht.Range("C" & demandRow & ":U" & demandRow).Copy
#VB sumSht.Range("C" & listRow).PasteSpecial (xlPasteAll)
xw.Range(sumShtName,"C"+str(listRow)).value = xw.Range(sumShtName, "C"+str(demandRow)+":U"+str(demandRow)).value
#VB ''--------------------------------------------------------------------------------
#VB ''수요표의 수요식1를 목록표의 목록식1에 붙여넣기 한다.
#VB sumSht.Range("D" & demandRow + 1 & ":U" & demandRow + 1).Copy
#VB sumSht.Range("D" & listContRow + 0).PasteSpecial xlPasteAllUsingSourceTheme
# TODO - 수식 복사 함수
xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula = xw.Range(sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1)).formula
#print sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1), xw.Range(sumShtName,"D"+str(demandRow+1)+":U"+str(demandRow+1)).formula
#src_tuples = xw.Range(sumShtName,"D"+str(demandRow+1)+":U"+str(demandRow+1)).formula
#tar = []
#for tuple in src_tuples:
# for s in tuple:
# tar.append(s.replace("C11","C"+str(listContRow)))
#xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula = tar
#VB ''sumSht.Range("D" & demandRow + 2 & ":U" & demandRow + 2).Copy
#VB ''sumSht.Range("D" & listContRow + 1).PasteSpecial xlPasteAllUsingSourceTheme
#VB ''sumSht.Range("D" & listContRow + 2) = ""
# 둘 다 같음.
rowCount = listContRow + 1
#VB For i = shtSumCount + 1 To shtCount ''PID 첫번은 이미 기록, n-1개 추가
print "shtSumCount", shtSumCount, "shtCount", shtCount
for i in xrange(shtSumCount + 1, shtCount+1):
#VB shtName = Application.Sheets(i).Name
shtName = xw.Sheet(i).name
#VB If Not IsNumeric(shtName) Then
#VB GoTo NextListCont
#VB End If
if not shtName.isdigit():
continue
# 다음 줄 부터 수식으로 복사하는 방법 알 필요 있음 ㄷㄷ
#VB ''첫번 인스턴스에서 서식과 수식을 복사해서 시트 갯수 만큼 붙여넣기한다
#VB ''행 서식과 수식 붙여넣기 (과제번호는 앞서 완료)
#VB sumSht.Range("D" & listContRow & ":U" & listContRow).Copy
#VB sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormats
#try:
# xw.Application(wb).xl_app.Run('sumShtName = "과제목록"')
# xw.Application(wb).xl_app.Run('Set sumSht = Sheets(sumShtName)')
# xw.Application(wb).xl_app.Run('sumSht.Range("D" & listContRow & ":U" & listContRow).Copy')
# xw.Application(wb).xl_app.Run('sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormats')
#except pythoncom.com_error as e:
# print e
# print e[0]
# print e[1]
# print e[2]
# print e[2][2]
# break
#xw.Range(sumShtName,"D"+str(rowCount)).value = xw.Range(sumShtName, "D"+str(listContRow)+":U"+str(listContRow)).value
#VB ''행 수식 붙여넣기 (과제번호는 앞서 완료)
#VB ''sumSht.Range("D" & listContRow + 0 & ":U" & listContRow + 0).Copy
#VB sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormulas
#print sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1), xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula
src_tuples = xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula
tar = []
for tuple in src_tuples:
for s in tuple:
tar.append(s.replace("C11","C"+str(rowCount)))
xw.Range(sumShtName,"D"+str(rowCount)+":U"+str(rowCount)).formula = tar
#print sumShtName, "D"+str(rowCount), xw.Range(sumShtName,"D"+str(rowCount)).formula
#print sumShtName, "D"+str(rowCount), xw.Range(sumShtName,"D"+str(rowCount)).value
#print sumShtName, "D"+str(listContRow), xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula
# 둘 다 같음.
rowCount = rowCount + 1
check()
def set_execute_time():
xw.Range('B1').value = datetime.now().strftime("%Y/%m/%d %H:%M:%S")
def check():
# get target url list
targets = xw.Range('A3').table.value
# check each url
for target in targets:
if target[0] == "No":
continue
else:
result = uc.url_check(target[1])
target[2] = result[0]
target[3] = result[1]
# set the result
xw.Range('A3').value = targets
if __name__ == "__main__":
# ready xls file
file_name = "URLCheckTool.xlsm"
wb = xw.Workbook(file_name)
set_execute_time()
check()
