/// <summary> /// Creates the workbook /// </summary> /// <returns>Spreadsheet created</returns> public static DocumentFormat.OpenXml.Packaging.SpreadsheetDocument CreateWorkbook(Stream stream) { DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadSheet = null; DocumentFormat.OpenXml.Packaging.SharedStringTablePart sharedStringTablePart; DocumentFormat.OpenXml.Packaging.WorkbookStylesPart workbookStylesPart; // Create the Excel workbook spreadSheet = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, false); // Create the parts and the corresponding objects // Workbook spreadSheet.AddWorkbookPart(); spreadSheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook(); spreadSheet.WorkbookPart.Workbook.Save(); // Shared string table sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.SharedStringTablePart>(); sharedStringTablePart.SharedStringTable = new DocumentFormat.OpenXml.Spreadsheet.SharedStringTable(); sharedStringTablePart.SharedStringTable.Save(); // Sheets collection spreadSheet.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets(); spreadSheet.WorkbookPart.Workbook.Save(); // Stylesheet workbookStylesPart = spreadSheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorkbookStylesPart>(); workbookStylesPart.Stylesheet = new DocumentFormat.OpenXml.Spreadsheet.Stylesheet(); workbookStylesPart.Stylesheet.Save(); return(spreadSheet); }
/// <summary> /// Adds a predefined style from the given xml /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="xml">Style definition as xml</param> /// <returns>True if succesful</returns> public static bool AddPredefinedStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string xml) { spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.InnerXml = xml; spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save(); return(true); }
/// <summary> /// Opens workbook in read-only mode. /// <remarks> /// Can open files that are already opened in other programs. /// </remarks> /// </summary> /// <param name="fileName">Workbook file name.</param> /// <returns>True if success.</returns> public bool OpenWorkbook(string fileName) { if (string.IsNullOrEmpty(fileName)) { throw new ArgumentNullException("fileName", "File name cannot be null"); } CleanupResources(); this.fileName = fileName; bool result = false; try { fileStream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.ReadWrite); spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileStream, false); //this.spreadsheetDocument.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true; //this.spreadsheetDocument.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true; result = spreadsheetDocument != null; } finally { if (!result) { CleanupResources(); } } return(result); }
private void CleanupResources() { fileName = null; currentCell = null; if (reader != null) { reader.Close(); reader.Dispose(); reader = null; } if (spreadsheetDocument != null) { spreadsheetDocument.Close(); spreadsheetDocument.Dispose(); spreadsheetDocument = null; } if (fileStream != null) { fileStream.Close(); fileStream.Dispose(); fileStream = null; } sharedStrings = null; }
/// <summary> /// Opens workbook from file. /// </summary> public bool OpenWorkbook(string fileName) { CloseWorkbook(); spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(fileName, true); return(spreadsheetDocument != null); }
/// <summary> /// Adds a list of strings to the shared strings table. /// </summary> /// <param name="spreadsheet">The spreadsheet</param> /// <param name="stringList">Strings to add</param> /// <returns></returns> public static bool AddSharedStrings(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, System.Collections.Generic.List <string> stringList) { foreach (string item in stringList) { Excel.AddSharedString(spreadsheet, item, false); } spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Save(); return(true); }
/// <summary> /// Sets a cell value with double number /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="doubleValue">Double value</param> /// <param name="styleIndex">Style to use</param> /// <param name="save">Save the worksheet</param> /// <returns>True if succesful</returns> public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, double doubleValue, uint?styleIndex, bool save = true) { #if EN_US_CULTURE string columnValue = doubleValue.ToString(); #else string columnValue = doubleValue.ToString().Replace(",", "."); #endif return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Number, columnValue, styleIndex, save)); }
/// <summary> /// Frees associated resources. /// </summary> protected virtual void Dispose(bool disposing) { if (disposing) { if (spreadsheetDocument != null) { spreadsheetDocument.Close(); spreadsheetDocument.Dispose(); spreadsheetDocument = null; } } }
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)); }
/// <summary> /// Returns the index of a shared string. /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="stringItem">String to search for</param> /// <returns>Index of a shared string. -1 if not found</returns> public static int IndexOfSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem) { DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable; bool found = false; int index = 0; foreach (DocumentFormat.OpenXml.Spreadsheet.SharedStringItem sharedString in sharedStringTable.Elements <DocumentFormat.OpenXml.Spreadsheet.SharedStringItem>()) { if (sharedString.InnerText == stringItem) { found = true; break;; } index++; } return(found ? index : -1); }
/// <summary> /// Add a single string to shared strings table. /// Shared string table is created if it doesn't exist. /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="stringItem">string to add</param> /// <param name="save">Save the shared string table</param> /// <returns></returns> public static bool AddSharedString(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string stringItem, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable; if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count()) { sharedStringTable.AppendChild( new DocumentFormat.OpenXml.Spreadsheet.SharedStringItem( new DocumentFormat.OpenXml.Spreadsheet.Text(stringItem))); // Save the changes if (save) { sharedStringTable.Save(); } } return(true); }
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(); } }
/// <summary> /// Adds a new worksheet to the workbook /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="name">Name of the worksheet</param> /// <returns>True if succesful</returns> public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name) { DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(); DocumentFormat.OpenXml.Spreadsheet.Sheet sheet; DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart; // Add the worksheetpart worksheetPart = spreadsheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>(); worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData()); worksheetPart.Worksheet.Save(); // Add the sheet and make relation to workbook sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1), Name = name }; sheets.Append(sheet); spreadsheet.WorkbookPart.Workbook.Save(); return(true); }
/// <summary> /// Sets a string value to a cell /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="stringValue">String value to set</param> /// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param> /// <param name="save">Save the worksheet</param> /// <returns>True if succesful</returns> public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, string stringValue, bool useSharedString, bool save = true) { string columnValue = stringValue; DocumentFormat.OpenXml.Spreadsheet.CellValues cellValueType; // Add the shared string if necessary if (useSharedString) { if (Excel.IndexOfSharedString(spreadsheet, stringValue) == -1) { Excel.AddSharedString(spreadsheet, stringValue, true); } columnValue = Excel.IndexOfSharedString(spreadsheet, stringValue).ToString(); cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString; } else { cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String; } return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, cellValueType, columnValue, null, save)); }
/// <summary> /// Adds the basic styles to the workbook /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <returns>True if succesful</returns> public static bool AddBasicStyles(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet) { DocumentFormat.OpenXml.Spreadsheet.Stylesheet stylesheet = spreadsheet.WorkbookPart.WorkbookStylesPart.Stylesheet; // Numbering formats (x:numFmts) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>(new DocumentFormat.OpenXml.Spreadsheet.NumberingFormats(), 0); // Currency stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.NumberingFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.NumberingFormat>( new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat() { NumberFormatId = 164, FormatCode = "#,##0.00" + "\\ \"" + System.Globalization.CultureInfo.CurrentUICulture.NumberFormat.CurrencySymbol + "\"" }, 0); // Fonts (x:fonts) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fonts>(new DocumentFormat.OpenXml.Spreadsheet.Fonts(), 1); stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fonts>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Font>( new DocumentFormat.OpenXml.Spreadsheet.Font() { FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" } }, 0); // Fills (x:fills) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fills>(new DocumentFormat.OpenXml.Spreadsheet.Fills(), 2); stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Fills>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Fill>( new DocumentFormat.OpenXml.Spreadsheet.Fill() { PatternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.PatternValues>() { Value = DocumentFormat.OpenXml.Spreadsheet.PatternValues.None } } }, 0); // Borders (x:borders) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.Borders>(new DocumentFormat.OpenXml.Spreadsheet.Borders(), 3); stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Borders>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.Border>( new DocumentFormat.OpenXml.Spreadsheet.Border() { LeftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(), RightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(), TopBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(), BottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(), DiagonalBorder = new DocumentFormat.OpenXml.Spreadsheet.DiagonalBorder() }, 0); // Cell style formats (x:CellStyleXfs) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats(), 4); stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellStyleFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>( new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 }, 0); // Cell formats (x:CellXfs) stylesheet.InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormats>(new DocumentFormat.OpenXml.Spreadsheet.CellFormats(), 5); // General text stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>( new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { FormatId = 0, NumberFormatId = 0 }, 0); // Date stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>( new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 22, FontId = 0, FillId = 0, BorderId = 0 }, 1); // Currency stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>( new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 164, FontId = 0, FillId = 0, BorderId = 0 }, 2); // Percentage stylesheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.CellFormats>().InsertAt <DocumentFormat.OpenXml.Spreadsheet.CellFormat>( new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { ApplyNumberFormat = true, FormatId = 0, NumberFormatId = 10, FontId = 0, FillId = 0, BorderId = 0 }, 3); stylesheet.Save(); return(true); }
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); //} }
/// <summary> /// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="valueType">Type of the value</param> /// <param name="value">The actual value</param> /// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param> /// <param name="save">Save the worksheet?</param> /// <returns>True if succesful</returns> private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true) { DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>(); DocumentFormat.OpenXml.Spreadsheet.Row row; DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null; DocumentFormat.OpenXml.Spreadsheet.Cell cell; DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null; DocumentFormat.OpenXml.Spreadsheet.Columns columns; DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null; string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex; // Check if the row exists, create if necessary if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First(); } else { row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex }; //sheetData.Append(row); for (uint counter = rowIndex - 1; counter > 0; counter--) { previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault(); if (previousRow != null) { break; } } sheetData.InsertAfter(row, previousRow); } // Check if the cell exists, create if necessary if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0) { cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First(); } else { // Find the previous existing cell in the row for (uint counter = columnIndex - 1; counter > 0; counter--) { previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault(); if (previousCell != null) { break; } } cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress }; row.InsertAfter(cell, previousCell); } // Check if the column collection exists columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault(); if (columns == null) { columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0); } // Check if the column exists if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0) { // Find the previous existing column in the columns for (uint counter = columnIndex - 1; counter > 0; counter--) { previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault(); if (previousColumn != null) { break; } } columns.InsertAfter( new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = columnIndex, Max = columnIndex, CustomWidth = true, Width = 9 }, previousColumn); } // Add the value cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value); if (styleIndex != null) { cell.StyleIndex = styleIndex; } if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date) { cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType); } if (save) { worksheet.Save(); } return(true); }
/// <summary> /// Sets a cell value with boolean value /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="boolValue">Boolean value</param> /// <param name="styleIndex">Style to use</param> /// <param name="save">Save the worksheet</param> /// <returns>True if succesful</returns> public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, bool boolValue, uint?styleIndex, bool save = true) { string columnValue = boolValue ? "1" : "0"; return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean, columnValue, styleIndex, save)); }
/// <summary> /// Sets a cell value with a date /// </summary> /// <param name="spreadsheet">Spreadsheet to use</param> /// <param name="worksheet">Worksheet to use</param> /// <param name="columnIndex">Index of the column</param> /// <param name="rowIndex">Index of the row</param> /// <param name="datetimeValue">DateTime value</param> /// <param name="styleIndex">Style to use</param> /// <param name="save">Save the worksheet</param> /// <returns>True if succesful</returns> public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, System.DateTime datetimeValue, uint?styleIndex, bool save = true) { var columnValue = datetimeValue.ToOADate().ToString(CultureInfo.InvariantCulture); return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Date, columnValue, styleIndex, save)); }