private IDictionary <string, MasterDataLine> readMasterData()
        {
            IDictionary <string, MasterDataLine> masterData = new Dictionary <string, MasterDataLine>();

            using (SpreadsheetDocument ssd = SpreadsheetDocument.Open(this.textBoxMasterDataFile.Text, false))
            {
                WorkbookPart workbookPart = ssd.WorkbookPart;
                // Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();


                Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == textBoxSheetNameMasterData.Text).FirstOrDefault();

                // Throw an exception if there is no sheet.
                if (sheet == null)
                {
                    MessageBox.Show("Text Box Sheet Name Master Data is invalid: Sheet " + textBoxSheetNameMasterData.Text + " does not exist!");
                    return(null);
                }

                WorksheetPart wsPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart;

                if (wsPart == null)
                {
                    MessageBox.Show("No WorksheetPart Found!");
                    return(null);
                }


                int countNullValues = 0;

                for (int i = int.Parse(textBoxRowFromMasterData.Text) - 1; i < int.Parse(textBoxRowToMasterData.Text); i++)
                {
                    Application.DoEvents();
                    Cell cellArticleNumber = wsPart.Worksheet.Descendants <Cell>().
                                             Where(c => c.CellReference == (textBoxArticleNumberColumnMasterData.Text + i)).FirstOrDefault();

                    if (cellArticleNumber == null)
                    {
                        countNullValues++;
                    }
                    else
                    {
                        countNullValues = 0;
                    }

                    if (countNullValues == 20)
                    {
                        break;
                    }

                    Cell cellLength = wsPart.Worksheet.Descendants <Cell>().
                                      Where(c => c.CellReference == (textBoxLengthColumn.Text + i)).FirstOrDefault();

                    Cell cellWidth = wsPart.Worksheet.Descendants <Cell>().
                                     Where(c => c.CellReference == (textBoxWidthColumn.Text + i)).FirstOrDefault();

                    Cell cellThickness = wsPart.Worksheet.Descendants <Cell>().
                                         Where(c => c.CellReference == (textBoxThicknesColumn.Text + i)).FirstOrDefault();

                    if (cellArticleNumber != null)
                    {
                        MasterDataLine masterDataLine = new MasterDataLine();

                        string artNr = GetCellValue(cellArticleNumber);

                        if (!string.IsNullOrWhiteSpace(artNr))
                        {
                            if (masterData.ContainsKey(artNr.ToUpper()))
                            {
                                MessageBox.Show("The Article Number " + artNr + " occures more than once!");
                            }

                            masterDataLine.ArtikelNr = artNr;

                            double d = 0;

                            if (cellLength != null)
                            {
                                double.TryParse(GetCellValue(cellLength), NumberStyles.Number, CultureInfo.CreateSpecificCulture("en-US"), out d);
                                masterDataLine.Length = d;
                            }

                            if (cellWidth != null)
                            {
                                double.TryParse(GetCellValue(cellWidth), NumberStyles.Number, CultureInfo.CreateSpecificCulture("en-US"), out d);
                                masterDataLine.Width = d;
                            }

                            if (cellThickness != null)
                            {
                                double.TryParse(GetCellValue(cellThickness), NumberStyles.Number, CultureInfo.CreateSpecificCulture("en-US"), out d);
                                masterDataLine.Thickness = d;
                            }

                            masterData[masterDataLine.ArtikelNr.ToUpper()] = masterDataLine;
                        }
                    }
                }
            }
            return(masterData);
        }
        private void doCalculation()
        {
            progressBar.Style = ProgressBarStyle.Marquee;
            buttonGo.Enabled  = false;
            buttonOpenMasterDataFile.Enabled             = false;
            buttonForeCastFile.Enabled                   = false;
            buttonCopyToClipboardForExcelPaste.Enabled   = false;
            textBoxMasterDataFile.Enabled                = false;
            textBoxForecastFile.Enabled                  = false;
            textBoxSheetNameMasterData.Enabled           = false;
            textBoxArticleNumberColumnMasterData.Enabled = false;
            textBoxArticleNumberColumnMasterData.Enabled = false;
            textBoxLengthColumn.Enabled                  = false;
            textBoxWidthColumn.Enabled                   = false;
            textBoxThicknesColumn.Enabled                = false;
            textBoxRowFromMasterData.Enabled             = false;
            textBoxRowToMasterData.Enabled               = false;

            IDictionary <string, MasterDataLine> masterData = null;

            try
            {
                masterData = readMasterData();
            }
            catch (IOException e)
            {
                MessageBox.Show("Error While opening the File!\r\n" + e.Message);
                this.result = null;
                restoreGui();
                return;
            }

            if (masterData == null)
            {
                this.result = null;
                restoreGui();
                return;
            }

            IDictionary <string, ForecastLine> forecast = readForecast();

            IList <ResultLine> result = new List <ResultLine>();

            foreach (ForecastLine forecastLine in forecast.Values)
            {
                Application.DoEvents();
                ResultLine resultLine = new ResultLine();

                resultLine.ArtikelNrForecast = forecastLine.ArtikelNr;
                resultLine.Quantities        = forecastLine.Quantities;
                resultLine.Quantity          = forecastLine.Quantities.Sum();

                MasterDataLine masterDataLine = null;

                if (masterData.ContainsKey(forecastLine.ArtikelNr.ToUpper()))
                {
                    masterDataLine = masterData[forecastLine.ArtikelNr.ToUpper()];
                }
                else
                {
                    string artNr = forecastLine.ArtikelNr.Substring(1, forecastLine.ArtikelNr.Length - 3).ToUpper();
                    if (masterData.ContainsKey(artNr))
                    {
                        masterDataLine = masterData[artNr];
                    }
                }

                if (masterDataLine != null)
                {
                    resultLine.ArtikelNrMasterData = masterDataLine.ArtikelNr;
                    resultLine.Length    = masterDataLine.Length;
                    resultLine.Width     = masterDataLine.Width;
                    resultLine.Thickness = masterDataLine.Thickness;
                }

                result.Add(resultLine);
            }

            string s = "";

            foreach (MasterDataLine value in masterData.Values)
            {
                s += value.ToString() + "\r\n";
            }
            textBoxMasterdata.Text = s;

            s = "";
            foreach (ForecastLine value in forecast.Values)
            {
                s += value.ToString() + "\r\n";
            }
            textBoxForecast.Text = s;

            s = "";
            foreach (ResultLine value in result)
            {
                s += value.ToString() + "\r\n";
            }
            textBoxResult.Text = s;

            this.result = result;

            restoreGui();
        }