c# how to read write excel spreedsheet

用.net读写excel文件有很多种方法，这里介绍一种比较简单的:

使用Microsoft.Office.Interop.Excel

下面是一个我项目中的一个读取函数的删减版本，要用上using Excel = Microsoft.Office.Interop.Excel;

除了读取一个Range里的东西，下面的函数演示了怎么枚举所有的行列，这样就可以不用事先知道Range了。

private void LoadExcelFile(string annotationFile)
        {
            Excel.Workbooks objBooks;
            Excel.Sheets objSheets;
            Excel._Worksheet objSheet;
            Excel.Range range;
            
            try
            {
                // Instantiate Excel and start a new workbook.
                objApp = new Excel.Application();
                objBooks = objApp.Workbooks;
                objBook = objBooks.Open(annotationFile, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                objSheets = objBook.Worksheets;
                objSheet = (Excel._Worksheet)objSheets.get_Item(1);


                //Get a range of data.
                range = objSheet.get_Range("A1", Missing.Value);
                range = range.get_Resize(1, 1);
                double videoTimeOffset = (double)range.get_Value(Missing.Value);

                //Another way of getting data
                string output = "";
                int rowIndex = 3;
                //object colIndex2 = 11;
                while (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 != null)
                {
                    string tmp = "";
                    int lapID;
                    SwimLap.StrokeTypeEnum strokeType;
                    string lapType;  //Warm Up/Down Main
                    
                    tmp = (((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 1]).Value2.ToString();
                    if (tmp.Equals(" ") || tmp.Equals("-"))
                        lapID = 0;
                    else
                        lapID = Convert.ToInt32(tmp);

                    tmp = (((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 2]).Value2.ToString();
                    strokeType = SwimLap.GetStrokeTypeEnum(tmp);

                    lapType = (((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2 == null) ? "-" : ((Excel.Range)objSheet.Cells[rowIndex, 3]).Value2.ToString();

                    
                    SwimLap sl = new SwimLap(videoTimeOffset,lapID,strokeType,lapType,lapTime,lapVideoStartTime,lapVideoEndTime,strokeCount,confidence,isTurn,isVideoFullLap,startType,strokeRate);

                    ss.AddLap(sl);

                    rowIndex++;
                    
                }

                objBooks.Close();
                objApp.Quit();

            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);
                MessageBox.Show(errorMessage, "Error");
            }
        }

c# .Net XML validate xml file using xsd schema

I talked about using XML file in the last few posts, and especially pointed out the advantage of using xml as interface.
For example: Your mate send you a xml file which contains the information in a format that you defined. You can easily
read and write code without actually getting the xml file because you design the schema.
Now that I successfully create such file, I was wondering the ability to handle errors. I am more aware that what I am gonna
process (xml file) is actually violate the designed format. So I did some research, and did the following things:

• Generate the schema xsd file for my designed xml file
• Before processing, using schema xsd file to validate against xml file
• Report error
  

Here are some code examples:

if (Validate(strFile, Properties.Settings.Default.schema) != true)
       {
            MessageBox.Show("XML file doesn't matches the schema! Please verify the input format!");
            return;
       }

        static int numErrors = 0;
        static string msgError = "";

        private static void ErrorHandler(object sender, ValidationEventArgs args)
        {
            msgError = msgError + "\r\n" + args.Message;
            numErrors++;
        }

        private static bool Validate(string xmlFilename, string xsdFilename)
        {
            return Validate(GetFileStream(xmlFilename), GetFileStream(xsdFilename));
        }

        private static void ClearErrorMessage()
        {
            msgError = "";
            numErrors = 0;
        }

        // returns a stream of the contents of the given filename
        private static Stream GetFileStream(string filename)
        {
            try
            {
                return new FileStream(filename, FileMode.Open, FileAccess.Read);
            }
            catch
            {
                return null;
            }
        }

        private static bool Validate(Stream xml, Stream xsd)
        {
            ClearErrorMessage();
            try
            {
                XmlTextReader tr = new XmlTextReader(xsd);
                XmlSchemaSet schema = new XmlSchemaSet();
                schema.Add(null, tr);

                XmlReaderSettings settings = new XmlReaderSettings();
                settings.ValidationType = ValidationType.Schema;
                settings.Schemas.Add(schema);
                settings.ValidationFlags |= XmlSchemaValidationFlags.ReportValidationWarnings;
                settings.ValidationEventHandler += new ValidationEventHandler(ErrorHandler);
                XmlReader reader = XmlReader.Create(xml, settings);

                // Validate XML data
                while (reader.Read())
                    ;
                reader.Close();

                // exception if validation failed
                if (numErrors > 0)
                    throw new Exception(msgError);

                tr.Close();
                return true;
            }
            catch
            {
                msgError = "Validation failed\r\n" + msgError;
                return false;
            }
        }

c# .Net XML using XmlDataDocument

Quite often, I need to parse files. I noticed that xml file is really easy to use and simple to define the format.
Last week, I send my mate an xml file scheme and he worked on putting data into xml file. I then read the xml
file and get out the data I need.
Here is some example code:

                    XmlDataDocument xmlDoc = new XmlDataDocument();
                    xmlDoc.Load(strFile);
                    XmlNamespaceManager nsMgr = new XmlNamespaceManager(xmlDoc.NameTable);
                    nsMgr.AddNamespace("xs", "http://www.w3.org/2001/XMLSchema");
                    nsMgr.AddNamespace("def", "http://www.w3.org/2001/XMLSchema");
                    XmlNodeList nodes = xmlDoc.SelectNodes("/def:xml_DataSet/def:something", nsMgr);
                    foreach (XmlNode n in nodes)
                    {
                        foreach (XmlAttribute a in n.Attributes)
                        {
                            switch (a.Name)
                            {
                            }
                        }

                        foreach (XmlNode m in n.ChildNodes)
                        {
                            switch (m.Name)
                            {
                            }
                        }
                     }

XML Tools Visual XPath

Visual XPath is a graphical way of generating XPath query results. It is a pretty cool that saves you time wondering how can I select a node.
You can download it from http://weblogs.asp.net/nleghari/articles/visualxpath.aspx