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()