Read and Write to the same Excel-File

Question

i'm new here and i hope you are able to help me.

I need an code to read from an excel- and xml-file and compare specific content. I still have the basic of the code and an Idea to read from the xml-file.

the problem in my next step is how to read and write from one xml-file. I first want to read and write simply from one excel-file and do the rest after ist step for step. But i don't know how i have to do it.

Is the fileName at the right place? How can i use ReadExcel and WriteExcel?

In the example I want to read the complete file in an array (3 columns and different length of rows) and change some values.

Thanks for all of your help. :)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;


namespace Converter
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        const string fileName = @"N:\Dokumente\Bachelorarbeit\Dateien\Converter - Kopie\Converter\bin\Debug\Testdatei.xls";

        public MainWindow()
        {
            InitializeComponent();

            //Read xml and fill dict
            XmlTextReader reader = new XmlTextReader("variablegroups.xml");
            string string_xml = "";
            string str1 = "ecatSource";
            string str2 = "name";
            bool inout = false;

            while (reader.Read())
            {
                switch (reader.NodeType)
                {
                    case XmlNodeType.Element:
                        string_xml = reader.Name;
                        break;

                    case XmlNodeType.Text:
                        if (string_xml.Equals(str1))
                        {
                            inout = true;
                        }

                        if ((string_xml.Equals(str1) || string_xml.Equals(str2)) && inout)
                        {
                            Console.Write(reader.Value); //write value
                            Console.Write("\n");
                            if (string_xml.Equals(str2))
                            {
                                inout = false;
                            }
                        }
                        break;
                }
            }
            Console.ReadLine();


            //Example for variables from XML
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07","Run In Turntable Brake-High pressure");
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit08","Run In Turntable Brake-High pressure 2");

            //Read comment from excel
            string feecomment = "TIID.Device1.EtherCAT Simulation.-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07";

            var result = feecomment.Replace("TIID.Device1.EtherCAT Simulation.", "");
            List<string> allcomments = new List<string>();
            allcomments.Add(result);

            foreach (string comment in allcomments)
            {
                if (VarSymbolicDic.ContainsKey(comment))
                {
                    //replace symbolic name in excel
                }
            }
            //write to excel again

        }

        //Read Excel
        public void ReadExcel(string fileName)
        {
            using (var file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {

                var wb = WorkbookFactory.Create(file);
                var sheet = wb.GetSheetAt(wb.ActiveSheetIndex);

                MessageBox.Show("Hallo1");

                //Variables.Clear();
                foreach (IRow row in sheet)
                {

                    //Check if it is a TwinCat Excel File
                    var tag = row.GetCell(0)?.ToString() ?? "";
                    var address = row.GetCell(1)?.ToString() ?? "";
                    var vartype = row.GetCell(2)?.ToString() ?? "";
                    var varfullName = row.GetCell(4)?.ToString() ?? "";
                    var usage = row.GetCell(5)?.ToString() ?? "";
                    var comment = row.GetCell(6)?.ToString() ?? "";


                    //Tag = tag,
                    //        Address = address,
                    //        Type = type,
                    //        Path = varfullName,
                    //        Usage  = usageMode,

                }
                wb.Close();
            }
        }


        //Write result in excel
        public void WriteExcel(string fileName)
        {
            //Create new Excel Workbook
            var workbook = new HSSFWorkbook();

            //Create new Excel Sheet
            var sheet = workbook.CreateSheet("New Sheet");

            //Create a header row
            var headerRow = sheet.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("Symbol");
            headerRow.CreateCell(1).SetCellValue("Adress");
            headerRow.CreateCell(2).SetCellValue("Type");
            headerRow.CreateCell(3).SetCellValue("Comment");
            headerRow.CreateCell(4).SetCellValue("VarFullName");
            headerRow.CreateCell(5).SetCellValue("Usage");

            //(Optional) freeze the header row so it is not scrolled
            sheet.CreateFreezePane(0, 1, 0, 1);

            int rowNumber = 1;
            //Populate the sheet with values from the grid data

                //Create a new Row
                var row = sheet.CreateRow(rowNumber++);

                //Set the Values for Cells
                row.CreateCell(0).SetCellValue("");
                row.CreateCell(1).SetCellValue(""); //Address);
                row.CreateCell(2).SetCellValue(""); //Type.ToString());
                row.CreateCell(3).SetCellValue(""); //Comment);
                row.CreateCell(4).SetCellValue(""); //.Path));
                row.CreateCell(5).SetCellValue(""); //.Usage.ToString()));

            //Write the Workbook to a memory stream


            try
            {
                FileStream fileOut = new FileStream(fileName, FileMode.Create);
                workbook.Write(fileOut);
                fileOut.Flush();
                fileOut.Close();
                workbook.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        Dictionary<string,string> VarSymbolicDic = new  Dictionary<string, string>(); 

    }
}

different app type but could help you out: https://stackoverflow.com/questions/21659069/writing-data-to-an-existing-excel-file-in-c-sharp — jazb, Dec 11 '18 at 06:34

score 0 · Answer 1 · answered Dec 11 '18 at 12:12

No it looks like this.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;


namespace Converter
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {

        public MainWindow()
        {
            InitializeComponent();

            //Read xml and fill dict
            XmlTextReader reader = new XmlTextReader("variablegroups.xml");
            string string_xml = "";
            string str1 = "ecatSource";
            string str2 = "name";
            bool inout = false;

            while (reader.Read())
            {
                switch (reader.NodeType)
                {
                    case XmlNodeType.Element:
                        string_xml = reader.Name;
                        break;

                    case XmlNodeType.Text:
                        if (string_xml.Equals(str1))
                        {
                            inout = true;
                        }

                        if ((string_xml.Equals(str1) || string_xml.Equals(str2)) && inout)
                        {
                            Console.Write(reader.Value); //write value
                            Console.Write("\n");
                            if (string_xml.Equals(str2))
                            {
                                inout = false;
                            }
                        }
                        break;
                }
            }
            Console.ReadLine();


            //Example for variables from XML
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07","Run In Turntable Brake-High pressure");
            //VarSymbolicDic.Add("-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit08","Run In Turntable Brake-High pressure 2");

            //Read comment from excel
            string feecomment = "TIID.Device1.EtherCAT Simulation.-184K1 (CPX-FB38 64Byte).Outputs.QB2.Bit07";

            var result = feecomment.Replace("TIID.Device1.EtherCAT Simulation.", "");
            List<string> allcomments = new List<string>();
            allcomments.Add(result);
            /*
            foreach (string comment in allcomments)
            {
                if (VarSymbolicDic.ContainsKey(comment))
                {
                    //replace symbolic name in excel
                }
            }*/
            //write to excel again

        }
    }

        public class ExcelFile
        {
            private string excelFilePath = @"N:\Dokumente\Bachelorarbeit\Dateien\Converter_Kopie\Converter\bin\Debug\Test_Excel.xls";
            private int rowNumber = 1; // define first row number to enter data in excel

            private Excel.Application myExcelApplication;
            private Excel.Workbook myExcelWorkbook;
            private Excel.Worksheet myExcelWorkSheet;

            public string GetExcelFilePath()
            { return excelFilePath; }

            public void SetExcelFilePath(string value)
            { excelFilePath = value; }

            public int GetRownumber()
            { return rowNumber; }

            public void SetRownumber(int value)
            { rowNumber = value; }
            public void OpenExcel()
            {
                myExcelApplication = null;

                myExcelApplication = new Excel.Application(); // create Excell App
                myExcelApplication.DisplayAlerts = false; // turn off alerts

                myExcelWorkbook = myExcelApplication.Workbooks._Open(excelFilePath, Missing.Value,
                   Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                   Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // open the existing excel file

                int numberOfWorkbooks = myExcelApplication.Workbooks.Count; // get number of workbooks (optional)

                myExcelWorkSheet = (Excel.Worksheet)myExcelWorkbook.Worksheets[1]; // define in which worksheet, do you want to add data
                myExcelWorkSheet.Name = "WorkSheet 1"; // define a name for the worksheet (optinal)

                int numberOfSheets = myExcelWorkbook.Worksheets.Count; // get number of worksheets (optional)
            }

            public void AddDataToExcel(string firstname, string lastname, string language, string email, string company)
            {
                myExcelWorkSheet.Cells[rowNumber, "H"] = firstname;
                myExcelWorkSheet.Cells[rowNumber, "J"] = lastname;
                myExcelWorkSheet.Cells[rowNumber, "Q"] = language;
                myExcelWorkSheet.Cells[rowNumber, "BH"] = email;
                myExcelWorkSheet.Cells[rowNumber, "CH"] = company;
                rowNumber++;  // if you put this method inside a loop, you should increase rownumber by one or what ever is your logic
            }
            public void closeExcel()
            {
                try
                {
                    myExcelWorkbook.SaveAs(excelFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
                                                   Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); // Save data in excel

                    myExcelWorkbook.Close(true, excelFilePath, Missing.Value); // close the worksheet
                }
                finally
                {
                    if (myExcelApplication != null)
                    {
                        myExcelApplication.Quit(); // close the excel application
                    }
                }


            }
        }
}

Read and Write to the same Excel-File

1 Answers1