CODEWITHSUNDEEP
pythonxlsxwriter
BedSon
BedSon

Reputation: 57

Read more xlsx files and write into new one in one loop with python xlsxwriter?

I am working on one task where we have many xlsx files each with about 100 rows and I would like to merge them into one new big xlsx file with xlsxwriter. Is it possible to do it with one loop which would read and write simultaneuosly ?

I can read the files, I can create a new one. On the first run I could write all cells into new file but when I checked the file, it is overwriting the actual values with the last read file. So I got only part where number of rows variable is not the same as in previous file.

Here is the code I wrote:

#!/usr/bin/env python3

import os
import time
import xlrd
import xlsxwriter
from datetime import datetime
from datetime import date

def print_values_to():

    loc = ("dir/")

    wr_workbook = xlsxwriter.Workbook('All_Year_All_Values.xlsx')
    wr_worksheet = wr_workbook.add_worksheet('Test')
    # --------------------------------------------------------

    all_rows = 0

    for file in os.listdir(loc):
        print(loc + file)
        workbook = xlrd.open_workbook(loc + file)

        sheet = workbook.sheet_by_index(0)

        number_of_rows = sheet.nrows
        number_of_columns = sheet.ncols

        all_rows = all_rows + number_of_rows

        dropped_numbers = []

        for i in range(number_of_rows):    # -------- number / number_of_rows
            if i == 0:
                all_rows = all_rows - 1
                continue

            for x in range(number_of_columns):
                type_value = sheet.cell_value(i, x)

                if isinstance(type_value, float):

                    changed_to_integer = int(sheet.cell_value(i, x))     # ----
                    values = changed_to_integer       # -----

                elif isinstance(type_value, str):
                    new_date = datetime.strptime(type_value, "%d %B %Y")
                    right_format = new_date.strftime("%Y-%m-%d")
                    values = right_format

                # write into new excel file
                wr_worksheet.write(i, x, values)

                # list of all values
                dropped_numbers.append(values)

            # print them on the console
            print(dropped_numbers)

            # Writing into new excel
           # wr_worksheet.write(i, x, values)

            # clear list of values for another run
            dropped_numbers = []

        print("Number of all rows: ", number_of_rows)
        print("\n")

    wr_workbook.close()

I went through the xlsxwrite guidance but it didnt tell exactly that it is not possible. So I still hoping that I could arrange it somehow.

For any idea many thaanks.

Upvotes: 0

Views: 601

Answers (1)

BedSon
BedSon

Reputation: 57

me again. But now, with an answer. This was really stupid solution. One simple variable incrementation did a trick. Right after the first loop. I just added p = p + 1 and wualaa all data are in one xlsx file.

So on the top:

for i in range(number_of_rows): # -------- number / number_of_rows p = p + 1

and for writer just changed the row counter:

wr_worksheet.write(p, x, values)

aaaaaaaaaaah... Many thanks.

Upvotes: 1

Related Questions