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();
}