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