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