Write me an Excel class that will read an excel file consisting of multiple columns and…
- User
Write me an Excel class that will read an excel file consisting of multiple columns and rows that only includes text and numbers without using any external dependencies
- Assistant
In order to create an Excel class to read an Excel file without using any external dependencies, we can use Python's built-in
zipfilemodule to read the file, andxml.etree.ElementTreeto parse the contents. The following class definition will read data from a given XLSX file and store the values in a 2D list format.import zipfile import xml.etree.ElementTree as ET class ExcelReader: def __init__(self, filename): self.filename = filename self.data = [] def read_excel_file(self): # Open the excel zip file archive = zipfile.ZipFile(self.filename, 'r') # Read the "xl/sharedStrings.xml" file shared_strings = archive.open("xl/sharedStrings.xml") root = ET.parse(shared_strings).getroot() texts = [t.text for t in root.iter('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}t')] # Read the "xl/worksheets/sheet1.xml" file sheet = archive.open("xl/worksheets/sheet1.xml") root = ET.parse(sheet).getroot() data = [] for row in root.iter('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}row'): row_data = [] for cell in row: cell_value = cell.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v') if cell_value is not None: cell_type = cell.get('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}t') if cell_type == "s": value = texts[int(cell_value.text)] else: value = cell_value.text row_data.append(value) data.append(row_data) self.data = data def get_data(self): return self.data # Example usage: if __name__ == "__main__": excel_reader = ExcelReader("example.xlsx") excel_reader.read_excel_file() data = excel_reader.get_data() for row in data: print(row)This code assumes the following:
- You are working with an XLSX file (not XLS).
- The data is stored in the first sheet of the workbook.
- The workbook is not password protected.
- The data only includes text and numbers.
This class is a simple implementation and may not cover all use cases. However, it can be a starting point and can be further extended to read and parse more complex Excel files.
- User
I want a c# code
- Assistant
In C#, we can use the
System.IO.PackagingandSystem.Xml.Linqnamespaces to read and parse XLSX files without using any external dependencies. Here's a class that does this:using System; using System.Collections.Generic; using System.IO; using System.IO.Packaging; using System.Linq; using System.Xml.Linq; class ExcelReader { private string _filename; private List<List<object>> _data; public ExcelReader(string filename) { _filename = filename; _data = new List<List<object>>(); } public void ReadExcelFile() { using Package package = Package.Open(_filename, FileMode.Open, FileAccess.Read); XNamespace xRelationships = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"; XNamespace xSpreadsheetMain = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; PackagePart sharedStringsPart = package.GetRelationshipsByType(xRelationships + "sharedStrings") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); XDocument sharedStringsDoc = XDocument.Load(XmlReader.Create(sharedStringsPart.GetStream())); List<string> sharedStrings = sharedStringsDoc.Descendants(xSpreadsheetMain + "t").Select(t => t.Value).ToList(); PackagePart sheetPart = package.GetRelationshipsByType(xRelationships + "worksheet") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); XDocument sheetDoc = XDocument.Load(XmlReader.Create(sheetPart.GetStream())); List<List<object>> data = new List<List<object>>(); foreach (var row in sheetDoc.Descendants(xSpreadsheetMain + "row")) { List<object> rowData = new List<object>(); foreach (var cell in row.Elements(xSpreadsheetMain + "c")) { string cellValue = cell.Elements(xSpreadsheetMain + "v").FirstOrDefault()?.Value; if (cellValue != null) { string cellType = cell.Attribute(xSpreadsheetMain + "t")?.Value; if (cellType == "s") { rowData.Add(sharedStrings[int.Parse(cellValue)]); } else { rowData.Add(cellValue); } } } data.Add(rowData); } _data = data; } public List<List<object>> GetData() { return _data; } } // Example usage: class Program { static void Main(string[] args) { ExcelReader excelReader = new ExcelReader("example.xlsx"); excelReader.ReadExcelFile(); List<List<object>> data = excelReader.GetData(); foreach (List<object> row in data) { Console.WriteLine(string.Join(", ", row)); } } }This code assumes the following:
- You are working with an XLSX file (not XLS).
- The data is stored in the first sheet of the workbook.
- The workbook is not password protected.
- The data only includes text and numbers.
This class is a simple implementation and may not cover all use cases. However, it can be a starting point and can be further extended to read and parse more complex Excel files.
- User
Can you make this read the file from a memory stream?
- Assistant
Certainly! Here's a modified version of the
ExcelReaderclass that can read an XLSX file from aMemoryStream. We'll change the constructor to accept theMemoryStream, and theReadExcelFile()method will use this to load file data.using System; using System.Collections.Generic; using System.IO; using System.IO.Packaging; using System.Linq; using System.Xml.Linq; class ExcelReader { private MemoryStream _memoryStream; private List<List<object>> _data; public ExcelReader(MemoryStream memoryStream) { _memoryStream = memoryStream; _data = new List<List<object>>(); } public void ReadExcelFile() { using Package package = Package.Open(_memoryStream, FileMode.Open, FileAccess.Read); XNamespace xRelationships = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"; XNamespace xSpreadsheetMain = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; PackagePart sharedStringsPart = package.GetRelationshipsByType(xRelationships + "sharedStrings") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); XDocument sharedStringsDoc = XDocument.Load(XmlReader.Create(sharedStringsPart.GetStream())); List<string> sharedStrings = sharedStringsDoc.Descendants(xSpreadsheetMain + "t").Select(t => t.Value).ToList(); PackagePart sheetPart = package.GetRelationshipsByType(xRelationships + "worksheet") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); XDocument sheetDoc = XDocument.Load(XmlReader.Create(sheetPart.GetStream())); List<List<object>> data = new List<List<object>>(); foreach (var row in sheetDoc.Descendants(xSpreadsheetMain + "row")) { List<object> rowData = new List<object>(); foreach (var cell in row.Elements(xSpreadsheetMain + "c")) { string cellValue = cell.Elements(xSpreadsheetMain + "v").FirstOrDefault()?.Value; if (cellValue != null) { string cellType = cell.Attribute(xSpreadsheetMain + "t")?.Value; if (cellType == "s") { rowData.Add(sharedStrings[int.Parse(cellValue)]); } else { rowData.Add(cellValue); } } } data.Add(rowData); } _data = data; } public List<List<object>> GetData() { return _data; } } // Example usage: class Program { static void Main(string[] args) { // Read the Excel file into a MemoryStream using FileStream fileStream = new FileStream("example.xlsx", FileMode.Open); MemoryStream memoryStream = new MemoryStream(); fileStream.CopyTo(memoryStream); // Pass the MemoryStream to ExcelReader ExcelReader excelReader = new ExcelReader(memoryStream); excelReader.ReadExcelFile(); List<List<object>> data = excelReader.GetData(); foreach (List<object> row in data) { Console.WriteLine(string.Join(", ", row)); } } }In the example usage, a file named "example.xlsx" is read into a
MemoryStream, which is then passed to theExcelReaderclass. The rest of the code works the same as before, allowing you to read data from an XLSX file stored in memory. - User
I am getting this error : System.NullReferenceException: 'Object reference not set to an instance of an object.' sharedStringsPart was null. On this line: XDocument sharedStringsDoc = XDocument.Load(XmlReader.Create(sharedStringsPart.GetStream()));
- Assistant
I apologize for the confusion. The error occurs when there is no shared strings part in the XLSX file. We can handle this by checking if
sharedStringsPartis null.Here's an updated version of the
ReadExcelFile()method that handles this case:public void ReadExcelFile() { using Package package = Package.Open(_memoryStream, FileMode.Open, FileAccess.Read); XNamespace xRelationships = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"; XNamespace xSpreadsheetMain = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"; PackagePart sharedStringsPart = package.GetRelationshipsByType(xRelationships + "sharedStrings") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); // Initialize an empty shared strings list List<string> sharedStrings = new List<string>(); // If a shared strings part is present, read the strings if (sharedStringsPart != null) { XDocument sharedStringsDoc = XDocument.Load(XmlReader.Create(sharedStringsPart.GetStream())); sharedStrings = sharedStringsDoc.Descendants(xSpreadsheetMain + "t").Select(t => t.Value).ToList(); } PackagePart sheetPart = package.GetRelationshipsByType(xRelationships + "worksheet") .Select(r => package.GetPart(r.TargetUri)).FirstOrDefault(); XDocument sheetDoc = XDocument.Load(XmlReader.Create(sheetPart.GetStream())); List<List<object>> data = new List<List<object>>(); foreach (var row in sheetDoc.Descendants(xSpreadsheetMain + "row")) { List<object> rowData = new List<object>(); foreach (var cell in row.Elements(xSpreadsheetMain + "c")) { string cellValue = cell.Elements(xSpreadsheetMain + "v").FirstOrDefault()?.Value; if (cellValue != null) { string cellType = cell.Attribute(xSpreadsheetMain + "t")?.Value; if (cellType == "s") { rowData.Add(sharedStrings[int.Parse(cellValue)]); } else { rowData.Add(cellValue); } } } data.Add(rowData); } _data = data; }This modification initializes an empty
sharedStringslist and only loads the shared strings ifsharedStringsPartis not null. It should prevent theSystem.NullReferenceExceptionfrom happening.