10 examples of 'read xlsx in python' in Python

34
def read_excel_file(f):
35
    dialect = csv.Sniffer().sniff(codecs.EncodedFile(f, "utf-8").read(1024))
36
    #f.open()
37
    return UnicodeCsvReader(codecs.EncodedFile(f, "utf-8"),
38
                            "utf-8", dialect=dialect)

68
def worksheet_from_excel(excel_sheet):
69
    worksheet = Worksheet()
70
    for col in range(excel_sheet.ncols):
71
        for row in range(excel_sheet.nrows):
72
            cell = excel_sheet.cell(row, col)
73
            if cell.ctype == XL_CELL_ERROR:
74
                formula = '=%s' % (error_text_from_code[cell.value], )
75
            elif cell.ctype == XL_CELL_DATE:
76
                formula = '=DateTime(%s, %s, %s, %s, %s, %s)' % xldate_as_tuple(
77
                    cell.value, excel_sheet.book.datemode)
78
            else:
79
                formula = unicode(excel_sheet.cell(row, col).value)
80
            worksheet[col + 1, row + 1].formula = formula
81
    return worksheet

14
def process_spreadsheet(input_file_path):
15
	"""Processes a spreadsheet at a given path, returning the datablocks found.
16
17
	Datablocks come in the form (read,block_type,data_block)
18
19
	read is a string identifying which data set the block is associated with
20
	block_type is a string identifying the type of metadata block
21
		Data - A 96 well plate grid of measurements.
22
		ControlData - A 96 well plate grid of measurements, for a control read.
23
		Metadata - A list of key-value pairs, applying globally to the read.
24
		other - A 96 well plate grid of some well-specific metadata item - label.
25
	datablock - A 1D List or Dictionary
26
27
	"""
28
	W = px.load_workbook(input_file_path, use_iterators = True, read_only=True)
29
30
	# p = W.get_sheet_by_name(name = 'Sheet1')
31
32
	data_blocks = []
33
	for sheet in W:
34
		data_block_indices = _locate_data_blocks(sheet)
35
		for index in data_block_indices:
36
			data_blocks.append( _collect_data_block(sheet, index))
37
38
	return data_blocks

13
def test_xlsx(self):
14
    with open('examples/test.xlsx', 'rb') as f:
15
        output = xlsx.xlsx2csv(f)
16
17
    with open('examples/testxlsx_converted.csv', 'r') as f:
18
        self.assertEquals(f.read(), output)

211
def __init__(self, f):
212
    if isinstance(f, basestring):
213
        filename = f
214
    elif not isinstance(f, file):
215
        tmp = NamedTemporaryFile(mode='wb', suffix='xlsx', delete=False)
216
        filename = tmp.name
217
        tmp.write(f.read())
218
        tmp.close()
219
    else:
220
        filename = f
221
222
    self.wb = openpyxl.reader.excel.load_workbook(filename, use_iterators=True)
223
    self.worksheets_by_title = {}
224
    self.worksheets = []
225
    for worksheet in self.wb.worksheets:
226
        ws = WorksheetJSONReader(worksheet)
227
        self.worksheets_by_title[worksheet.title] = ws
228
        self.worksheets.append(ws)

56
def open_excel_input_file(self, path):
57
    if not path or path == '-':
58
        if six.PY2:
59
            return six.BytesIO(sys.stdin.read())
60
        else:
61
            return six.BytesIO(sys.stdin.buffer.read())
62
    else:
63
        return open(path, 'rb')

94
def test_multiple_sheet(self):
95
    b = self.reader_class()
96
    b.open(self.merged_book_file)
97
    sheets = b.read_all()
98
    for key in sheets:
99
        sheets[key] = list(sheets[key])
100
    self.assertEqual(sheets, self.expected_sheets)

30
def import_openpyxl(self):
31
    """Import the requests module """
32
    try:
33
        import openpyxl
34
        from openpyxl import load_workbook
35
    except ImportError:
36
        print("openpyxl module is not installed"\
37
                   "Please install openpyxl module to"\
38
                   "perform any activities related to parsing xl sheets")
39
    else:
40
        self.openpyxl = openpyxl
41
        self.load = load_workbook

89
def test_non_default_sheet_as_single_sheet_plain_reader(self):
90
    r = pe.load(self.testfile, "Sheet2")
91
    data = list(r.rows())
92
    assert data == self.content["Sheet2"]

18
def run(self, excel_file=None):
19
20
    self.replace_defaults(excel_file, None, None)
21
22
    excel = excel_reader.ExcelReader(self._excel_file)
23
24
    return excel.get_sheets()