Skip to main content
added 4 characters in body
Source Link
Jamal
  • 34.9k
  • 13
  • 133
  • 237

I've got an Excel file from which I have to read out data to an object to serialize. So far I came up with this solution, and I'm curious if there are any clearer solutions?.

I've got an Excel file from which I have to read out data to an object to serialize. So far I came up with this solution, I'm curious if there are any clearer solutions?

I've got an Excel file from which I have to read out data to an object to serialize. So far I came up with this solution, and I'm curious if there are any clearer solutions.

Source Link

Excel to serializable object

I've got an Excel file from which I have to read out data to an object to serialize. So far I came up with this solution, I'm curious if there are any clearer solutions?

public myClassFromXsd excelToMyClassFromXsd(String excelFile)
            {
                Excel.Application excelApp = new Excel.Application();            
                Excel.Workbook workBook = excelApp.Workbooks.Open(excelFile);
                Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets[1];
                    
                myClassFromXsd spec = new myClassFromXsd();
                UserHeader user = new UserHeader();
                ResultHeader header = new ResultHeader();
                anotherClassFromXsd specOp = new anotherClassFromXsd();
                List<anotherClassFromXsd> specOpList = new List<anotherClassFromXsd>();
                thisIsAClassToo tcd = new thisIsAClassToo();            
    
                excelApp.Visible = false;            
    
                tcd.orderNumber = Convert.ToString(workSheet.get_Range("B3").Value); 
                switch (((String)workSheet.get_Range("B4").Value).ToUpper())
                {
                    case "I":
                        tcd.typeOfThis = EnumFromXsd.I;
                        break;
                    case "E":
                        tcd.typeOfThis = EnumFromXsd.E;
                        break;
                    case "D":
                        tcd.typeOfThis = EnumFromXsd.D;
                        break;
                    default:
                        break;
                }
                switch (((String)workSheet.get_Range("B5").Value).ToUpper())
                {
                    case "I":
                        tcd.othersCanAlter = true;
                        break;
                    case "N":
                        tcd.othersCanAlter = false;
                        break;                
                    default:
                        break;
                }
                tcd.oneProperty = Convert.ToString(workSheet.get_Range("B6").Value);
                tcd.onePropertyText = Convert.ToString(workSheet.get_Range("B7").Value);
                tcd.Name = Convert.ToString(workSheet.get_Range("B10").Value);
                tcd.vatNum = Convert.ToString(workSheet.get_Range("B11").Value);
                tcd.city = Convert.ToString(workSheet.get_Range("B12").Value);                  
    
                
                specOp.index = 1;
                specOp.operation = Operation.create;
                specOp.Item = tcd;
                specOpList.Add(specOp);    
                
                header.requestId = Guid.NewGuid().ToString(); 
                header.timestamp = DateTime.Now.ToString("yyyy-mm-ddTH:mm:sszzz");              
                    
                
                user.user = "TestUser";
                user.passwordHash = "BA32521232AED23798735C78739273GDHS238238723";
                
                spec.header = header;
                spec.user = user;
                spec.specOps = specOpList;
    
                excelApp.Quit();
                return spec;
            }       

And the serializer and deserializer methods:

public void serializeXml(myClassFromXsd mcfx)
        {
            using (StreamWriter writer = new StreamWriter("e:\\test.xml", false))
            {

                XmlSerializer serializer = new XmlSerializer(typeof(myClassFromXsd));
                serializer.Serialize(writer, mcfx);
            }
        }

        public myClassFromXsd deserializeXml(String xmlFile)
        {
            myClassFromXsd mcfx = new myClassFromXsd();
            XmlSerializer serializer = new XmlSerializer(typeof(myClassFromXsd));
            StreamReader reader = new StreamReader(xmlFile);
            mcfx = (myClassFromXsd)serializer.Deserialize(reader);
            reader.Close();
            return mcfx;
        }