public string WriteBrochures(string directory)
{
string Result = null;
if (Directory.Exists(directory) == false)
{
return(Result);
}
BrochuresCollection bc = BrochuresCollection.Create();
SpecificBrochuresCollection SBC = SpecificBrochuresCollection.Create();
foreach (Brochures b in bc.BList)
{
if (b.FolderName == null)
{
continue;
}
if (b.PostCode.Equals("Postcode") || b.Price.Equals("Price"))
{
Header = b;
continue;
}
StringBuilder FolderPath = new StringBuilder(directory);
FolderPath.Append("\\" + b.FolderName + "\\");
try
{
Directory.CreateDirectory(FolderPath.ToString());
}
catch (IOException e)
{
Result += "Can not Create Folders ," + e.Message;
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "CAN NOT CREATE FOLDERS"
});
}
FolderPath.Clear();
SBC.Add(b);
}
foreach (BrochuresCollection fbc in SBC.sbcList)
{
Result += WriteCollectionInExcel(directory, fbc);
}
return(Result);
}
void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
panel1.Enabled = false;
ExcelHandlercs handler = new ExcelHandlercs();
handler.InformationDownloadEvent += new InformationDownloadHandler(handler_InformationDownloadEvent);
if (radioButtonSplit.Checked == true)
{
LogResult += "\n\n" + handler.ReadBrochures(textBoxInput.Text, textBoxWSName.Text);
BrochuresCollection bc = BrochuresCollection.Create();
LogResult += "\n\n" + handler.WriteBrochures(textBoxOutput.Text);
}
else
{
LogResult += handler.Merge(textBoxInput.Text, textBoxOutput.Text);
}
}
public string ReadBrochures(string FileName, string WSName)
{
string Result = null;
application.DisplayAlerts = false;
workbook = application.Workbooks.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
try
{
worksheet = (Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
Result += "\nCan not identify Worksheet" + e.Message;
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "Can not identify Worksheet" + e.Message
});
return(Result);
}
int lastRow;
//Range excelRange = worksheet.UsedRange;
try
{
lastRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
}
catch (Exception e)
{
Result += "\nCan not Find Records in File, " + e.Message;
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "Can not Find Records" + e.Message
});
return(Result);
}
BrochuresCollection bCollection = BrochuresCollection.Create();
for (int i = 1; i <= lastRow; i++)
{
Brochures brochure = new Brochures();
// System.Array valueArray = (System.Array)excelRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);
System.Array valueArray = null;
try
{
valueArray = (System.Array)worksheet.get_Range("A" + i.ToString(), "L" + i.ToString()).Cells.Value2;
}
catch (Exception e)
{
Result += "\nCan not Retrieve " + i + " Record.," + e.Message;
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "CAN NOT RELEASE RESOURCES"
});
return(Result);
}
try
{
brochure.PostCode = valueArray.GetValue(1, 5).ToString();
brochure.Price = valueArray.GetValue(1, 6).ToString();
brochure.PropertyType = valueArray.GetValue(1, 10).ToString();
brochure.FindCode();
if (brochure.PostCode.Equals("Postcode") || brochure.Price.Equals("Price"))
{
Header.PostCode = brochure.PostCode;
Header.Price = brochure.Price;
Header.PropertyType = brochure.PropertyType;
}
OnInformationDownload(new EventArguments()
{
Name = "Reading Records", Time = DateTime.Now, Details = "Reading " + i + "//" + lastRow + " Record in " + brochure.FolderName
});
bCollection.BList.Add(brochure);
}
catch (Exception)
{
Result += "\nCan not Record Read Value at " + i + ".";
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "CAN NOT READ VALUE"
});
}
}
try
{
Marshal.ReleaseComObject(worksheet);
workbook.Close();
Marshal.ReleaseComObject(workbook);
application.Quit();
Marshal.ReleaseComObject(application);
GC.Collect();
KillProcesses("EXCEL");
}
catch (Exception)
{
Result += "\nCan not Release Resources.";
OnInformationDownload(new EventArguments()
{
Name = "ERROR", Time = DateTime.Now, Details = "CAN NOT RELEASE RESOURCES"
});
}
return(Result);
}
/*
* There is no difference between this function and another ...This will be removed when getting all information
*
*/
private List <Brochures> GetBrochuresFromExcel(string FileName)
{
List <Brochures> brochuresList = new List <Brochures>();
application = new Application();
application.DisplayAlerts = false;
if (File.Exists(FileName))
{
workbook = application.Workbooks.Open(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
return(null);
}
try
{
worksheet = (Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
throw new Exception(e.Message);
}
int lastRow;
OnInformationDownload(new EventArguments()
{
Name = "Reading sub records", Time = DateTime.Now, Details = "Reading records in " + FileName
});
//Range excelRange = worksheet.UsedRange;
try
{
lastRow = worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
BrochuresCollection bCollection = BrochuresCollection.Create();
for (int i = 1; i < lastRow; i++)
{
Brochures brochure = new Brochures();
System.Array valueArray = null;
try
{
valueArray = (System.Array)worksheet.get_Range("A" + i.ToString(), "D" + i.ToString()).Cells.EntireRow.Value2;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
try
{
string FolderName = null;
string Price = null;
if (valueArray != null)
{
try
{
FolderName = valueArray.GetValue(1, 1).ToString();
Price = valueArray.GetValue(1, 3).ToString();
}
catch (NullReferenceException)
{
}
if (String.IsNullOrEmpty(FolderName) || String.IsNullOrEmpty(Price))
{
continue;
}
brochure.PostCode = valueArray.GetValue(1, 2).ToString();
brochure.PropertyType = valueArray.GetValue(1, 4).ToString();
brochure.Price = Price;
brochure.FolderName = FolderName;
brochuresList.Add(brochure);
}
}
catch (Exception)
{
}
}
try
{
Marshal.ReleaseComObject(worksheet);
workbook.Close();
Marshal.ReleaseComObject(workbook);
application.Quit();
Marshal.ReleaseComObject(application);
GC.Collect();
KillProcesses("EXCEL");
}
catch (Exception)
{
}
return(brochuresList);
}