private void testChemicalListToolStripMenuItem_Click(object sender, EventArgs e)
{
List <DSSToxChemicals> chemicals = new List <DSSToxChemicals>();
// Open the document for editing.
string fileName = documentPath + "\\DSSTox_ToxCastRelease_20151019.xlsx";
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart wbPart = document.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = wbPart.WorksheetParts.First();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = wsPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text = string.Empty;
bool first = true;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
chemicals.Add(new DSSToxChemicals(text));
}
first = false;
text = string.Empty;
}
document.Close();
}
foreach (DSSToxChemicals chem in chemicals)
{
if (!string.IsNullOrEmpty(chem.Structure_SMILES))
{
ChemInfo.Molecule mol = new ChemInfo.Molecule(chem.Structure_SMILES);
if (mol != null)
{
foreach (ChemInfo.FunctionalGroup f in this.fGroups)
{
if ((f.Name != "ESTER-SULFIDE") || (f.Name != "KETENIMINE"))
{
mol.FindFunctionalGroup(f);
}
}
}
chem.AddFunctionalGroups(mol.FunctionalGroups);
}
}
fileName = documentPath + "\\chemicals.json";
System.IO.File.WriteAllText(fileName, Newtonsoft.Json.JsonConvert.SerializeObject(chemicals, Newtonsoft.Json.Formatting.Indented));
}
private void Button1_Click(object sender, EventArgs e)
{
// Open the document for editing.
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument =
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(@"C:\Users\fmogh\OneDrive\Desktop\Excel\Farzad.xlsx", false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = c.CellValue.Text;
Console.Write(text + " ");
}
}
}
}
// The DOM approach.
// Note that the code below works only for cells that contain numeric values.
//
static void ReadExcelFileDOM(string fileName)
{
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = System.Linq.Enumerable.First(workbookPart.WorksheetParts);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = System.Linq.Enumerable.First(worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>());
string text;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = c.CellValue.Text;
System.Console.Write(text + " ");
}
}
System.Console.WriteLine();
System.Console.ReadKey();
}
}
// The SAX approach.
static void ReadExcelFileSAX(string fileName)
{
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = System.Linq.Enumerable.First(workbookPart.WorksheetParts);
DocumentFormat.OpenXml.OpenXmlReader reader = DocumentFormat.OpenXml.OpenXmlReader.Create(worksheetPart);
string text;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.CellValue))
{
text = reader.GetText();
System.Console.Write(text + " ");
}
}
System.Console.WriteLine();
System.Console.ReadKey();
}
}
private void OpenFunctionGroupExcelResource()
{
// Reads functional Groups from Excel file.
//List<string> functionalGroupStrs = new List<string>();// SustainableChemistry.Properties.Resources.Full_Functional_Group_List;
string fileName = "..\\..\\Data\\Full Functional Group List 20180731.xlsx";
FunctionalGroups.Columns.Add("Name", typeof(System.String));
FunctionalGroups.Columns.Add("Smart", typeof(System.String));
FunctionalGroups.Columns.Add("Image", typeof(System.Drawing.Image));
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, false))
{
DocumentFormat.OpenXml.Packaging.WorkbookPart wbPart = document.WorkbookPart;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = GetWorkSheetFromSheet(wbPart, GetSheetFromName(wbPart, "Full Functional Group List")).Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
string text = string.Empty;
bool first = true;
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
System.Data.DataRow row = FunctionalGroups.NewRow();
ChemInfo.FunctionalGroup temp = fGroups.Add(text, row);
FunctionalGroups.Rows.Add(row);
//string filename = "..\\..\\Images\\" + temp.Name.ToLower() + ".jpg";
//if (System.IO.File.Exists(filename)) temp.Image = System.Drawing.Image.FromFile(filename);
}
text = string.Empty;
first = false;
}
//using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(string.Format("Data Source=..\\..\\Data\\SustainableChemistry.sqlite;Version=3;;New=False;Compress=True;Max Pool Size=100;")))
//{
// con.Open();
// using (System.Data.SQLite.SQLiteTransaction transaction = con.BeginTransaction())
// {
// foreach (DataRow row in FunctionalGroups.Rows)
// {
// using (System.Data.SQLite.SQLiteCommand sqlitecommand = new System.Data.SQLite.SQLiteCommand("insert into table(fh,ch,mt,pn) values ('" + Convert.ToString(row[0]) + "','" + Convert.ToString(row[1]) + "','"
// + Convert.ToString(row[2]) + "','" + Convert.ToString(row[3]) + "')", con))
// {
// sqlitecommand.ExecuteNonQuery();
// }
// }
// transaction.Commit();
// //new General().WriteApplicationLog("Data successfully imported.");
// //return true;
// }
//}
sheetData = GetWorkSheetFromSheet(wbPart, GetSheetFromName(wbPart, "Reaction List")).Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
text = string.Empty;
first = true;
NamedReactions.Columns.Add("Name", typeof(System.String));
NamedReactions.Columns.Add("FunctionalGroup", typeof(System.String));
NamedReactions.Columns.Add("Image", typeof(System.Drawing.Image));
NamedReactions.Columns.Add("URL", typeof(System.String));
NamedReactions.Columns.Add("ReactantA", typeof(System.String));
NamedReactions.Columns.Add("ReactantB", typeof(System.String));
NamedReactions.Columns.Add("ReactantC", typeof(System.String));
NamedReactions.Columns.Add("Product", typeof(System.String));
NamedReactions.Columns.Add("Heat", typeof(System.String));
NamedReactions.Columns.Add("AcidBase", typeof(System.String));
NamedReactions.Columns.Add("Catalyst", typeof(System.String));
NamedReactions.Columns.Add("Solvent", typeof(System.String));
NamedReactions.Columns.Add("ByProducts", typeof(System.String));
foreach (DocumentFormat.OpenXml.Spreadsheet.Row r in sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>())
{
if (!first)
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
text = text + this.GetExcelCellValue(c, wbPart) + '\t';
}
System.Data.DataRow row = NamedReactions.NewRow();
fGroups.AddReaction(new ChemInfo.NamedReaction(text, row));
NamedReactions.Rows.Add(row);
}
text = string.Empty;
first = false;
}
document.Close();
}
//// This next line creates a list of strings that don't have images. Can be commented out!
//List<string> missingImages = new List<string>();
//// Creates the collection of functional groups.
//foreach (string line in functionalGroupStrs)
//{
// ChemInfo.FunctionalGroup temp = fGroups.Add(line);
// string filename = documentPath + "\\Images\\" + temp.Name.ToLower() + ".jpg";
// if (System.IO.File.Exists(filename)) temp.Image = System.Drawing.Image.FromFile(filename);
// //this line adds the missing image to the list of missing images. Can be commented out.
// else missingImages.Add(temp.Name);
//}
//// Writes the missing images to a file.
//// Write the string array to a new file named "WriteLines.txt".
//using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(documentPath + @"\MissingImages.txt"))
//{
// foreach (string line in missingImages)
// outputFile.WriteLine(line);
//}
//string[] imageFiles = System.IO.Directory.GetFiles(documentPath + "\\Images\\");
//string[] groupNames = fGroups.FunctionalGroups;
//List<string> extraImages = new List<string>();
//foreach (string name in imageFiles)
//{
// string temp = name.Replace(documentPath + "\\Images\\", string.Empty);
// temp = temp.Replace(".jpg", string.Empty);
// bool add = true;
// foreach (string gName in groupNames)
// {
// if (temp.ToUpper() == gName.ToUpper()) add = false;
// }
// if (add) extraImages.Add(temp);
//}
//// Write the string array to a new file named "WriteLines.txt".
//using (System.IO.StreamWriter outputFile = new System.IO.StreamWriter(documentPath + @"\ExtraImages.txt"))
//{
// foreach (string line in extraImages)
// outputFile.WriteLine(line);
//}
}
public static DocumentFormat.OpenXml.Spreadsheet.Worksheet GetWorkSheetFromSheet(DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
var worksheetPart = (DocumentFormat.OpenXml.Packaging.WorksheetPart)workbookPart.GetPartById(sheet.Id);
return(worksheetPart.Worksheet);
}
public static DocumentFormat.OpenXml.Spreadsheet.Sheet GetSheetFromName(DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, string sheetName)
{
return(workbookPart.Workbook.Sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>()
.FirstOrDefault(s => s.Name.HasValue && s.Name.Value == sheetName));
}
public static DocumentFormat.OpenXml.Spreadsheet.SharedStringItem GetSharedStringItemById(DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart, int id)
{
return(workbookPart.SharedStringTablePart.SharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>().ElementAt(id));
}
private string GetExcelCellValue(DocumentFormat.OpenXml.Spreadsheet.Cell cell, DocumentFormat.OpenXml.Packaging.WorkbookPart wbpart)
{
string retVal = string.Empty;
if (cell.DataType != null)
{
if (cell.DataType == DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString)
{
int id = -1;
if (Int32.TryParse(cell.InnerText, out id))
{
DocumentFormat.OpenXml.Spreadsheet.SharedStringItem item = GetSharedStringItemById(wbpart, id);
if (item.Text != null)
{
retVal = item.Text.Text;
}
else if (item.InnerText != null)
{
retVal = item.InnerText;
}
else if (item.InnerXml != null)
{
retVal = item.InnerXml;
}
}
}
else
{
retVal = cell.InnerText;
}
}
return(retVal);
}
