public object Clone()
{
Cell cell = new Cell();
cell.CellFormat = this.CellFormat;
cell.Formula = this.Formula;
cell.valid = this.Valid;
cell.Value = this.Value;
cell.Error = this.Error;
cell.ErrorString = this.ErrorString;
return cell;
}
void SpreadsheetView_ColumnWidthChanged(object sender, DataGridViewColumnEventArgs e)
{
int col = e.Column.Index;
SpreadsheetModel model = spreadsheetModel;
for (int i = 0; i < RowCount; i++)
{
Cell cell = model.Cells[i, col];
if (cell == null)
{
cell = new Cell();
model.Cells[i,col] = cell;
}
cell.CellFormat.CellWidth = this.Columns[col].Width;
}
}
private void ExecuteChangeFont(FontEventArgs e)
{
Console.WriteLine("here");
foreach (DataGridViewCell cell in ActiveWS.Spreadsheet.SelectedCells)
{
if (cell.RowIndex >= 0 && cell.ColumnIndex >= 0)
{
Cell c = ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex];
if (c == null)
{
c = new Cell();
ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex] = c;
}
c.CellFormat.CellFont = new Font((e.FamilyName == null) ? c.CellFormat.CellFont.Name : e.FamilyName,
(e.Size == -1) ? c.CellFormat.CellFont.Size : e.Size,
c.CellFormat.CellFont.Style);
}
ActiveWS.Spreadsheet.RefreshCell(new CellKey(cell.RowIndex, cell.ColumnIndex));
}
}
public void ExecuteFormatCells(string action)
{
FontStyle s = 0;
float sizeChange = 0;
Cell c;
foreach (DataGridViewCell cell in ActiveWS.Spreadsheet.SelectedCells)
{
sizeChange = 0;
s = 0;
c = null;
if (cell.RowIndex >= 0 && cell.ColumnIndex >= 0)
{
c = ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex];
if (c == null)
{
c = new Cell();
ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex] = c;
}
s = c.CellFormat.CellFont.Style;
switch (action)
{
case "bold":
if ((s & FontStyle.Bold) == FontStyle.Bold)
s = s & ~FontStyle.Bold;
else
s |= FontStyle.Bold;
break;
case "italic":
if ((s & FontStyle.Italic) == FontStyle.Italic)
s = s & ~FontStyle.Italic;
else
s |= FontStyle.Italic;
break;
case "underline":
if ((s & FontStyle.Underline) == FontStyle.Underline)
s = s & ~FontStyle.Underline;
else
s |= FontStyle.Underline;
break;
case "increaseFont":
sizeChange++;
break;
case "decreaseFont":
sizeChange--;
break;
}
if (c.CellFormat.CellFont.Size + sizeChange > 300 || c.CellFormat.CellFont.Size + sizeChange < 4)
sizeChange = 0;
Console.WriteLine(c.CellFormat.CellFont.Size);
c.CellFormat.CellFont = new Font(c.CellFormat.CellFont.Name,
c.CellFormat.CellFont.Size + sizeChange,
s);
Console.WriteLine(c.CellFormat.CellFont.Size);
}
ActiveWS.Spreadsheet.RefreshCell(new CellKey(cell.RowIndex, cell.ColumnIndex));
}
//ActiveWS.Spreadsheet.RefreshView();
}
public void ExecuteSelectCellColor(Color cellColor)
{
foreach (DataGridViewCell cell in ActiveWS.Spreadsheet.SelectedCells)
{
if (cell.RowIndex >= 0 && cell.ColumnIndex >= 0)
{
Cell c = ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex];
if (c == null)
{
c = new Cell();
ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex] = c;
}
c.CellFormat.CellColor = cellColor;
}
ActiveWS.Spreadsheet.RefreshCell(new CellKey(cell.RowIndex, cell.ColumnIndex));
}
}
private void ExecuteAssignFormula(TextBox formulaBox)
{
foreach (DataGridViewCell cell in ActiveWS.Spreadsheet.SelectedCells)
{
if (cell.RowIndex >= 0 && cell.ColumnIndex >= 0)
{
Cell c = ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex];
if (c == null)
{
c = new Cell();
ActiveWS.Spreadsheet.SpreadsheetModel.Cells[cell.RowIndex, cell.ColumnIndex] = c;
}
c.Formula = formulaBox.Text;
SpreadsheetControl.Instance.CellChanged(new CellKey(cell.RowIndex, cell.ColumnIndex));
}
ActiveWS.Spreadsheet.RefreshCell(new CellKey(cell.RowIndex, cell.ColumnIndex));
}
ActiveWS.Spreadsheet.RefreshView();
}
private bool ReadBook()
{
int i = 0;
int currentRow, currentColumn;
XmlDocument doc = new XmlDocument();
try
{
doc.Load(filename);
XmlNodeList sheetList = doc.GetElementsByTagName("sheet");
// Traverse the List of Sheets
foreach (XmlNode sheet in sheetList)
{
CellCollection cells = new CellCollection();
XmlElement sheetElement = (XmlElement)sheet;
String sheetName = "";
if (sheetElement.HasAttributes)
{
if (sheetElement.Attributes[0].Name == "name")
sheetName = sheetElement.Attributes["name"].InnerText;
}
XmlNodeList columnList = sheetElement.GetElementsByTagName("column");
// Traverse the List of Columns
foreach (XmlNode column in columnList)
{
XmlElement columnElement = (XmlElement)column;
String columnNumber = "";
if (columnElement.HasAttributes)
{
if (columnElement.Attributes[0].Name == "index")
columnNumber = columnElement.Attributes["index"].InnerText;
}
currentColumn = Int32.Parse(columnNumber);
XmlNodeList rowList = columnElement.GetElementsByTagName("row");
// Traverse the List of Rows
foreach (XmlNode row in rowList)
{
XmlElement rowElement = (XmlElement)row;
String rowNumber = "";
if (rowElement.HasAttributes)
{
if (rowElement.Attributes[0].Name == "index")
rowNumber = rowElement.Attributes["index"].InnerText;
}
currentRow = Int32.Parse(rowNumber);
for (int j = 0; j < rowElement.Attributes.Count; j++)
{
//MessageBox.Show("Row: " + rowNumber + " Column: " + columnNumber + "\n");
if (rowElement.GetElementsByTagName("content").Count != 0)
{
XmlElement contentElement = (XmlElement)rowElement.GetElementsByTagName("content")[0];
Cell cell = new Cell();
if (contentElement.HasAttribute("CellFormat"))
{
String settings = contentElement.GetAttribute("CellFormat");
cell.CellFormat = CellFormatFactory.createCellFormat(settings);
}
if (contentElement.GetElementsByTagName("Formula").Count != 0)
{
String formula = contentElement.GetElementsByTagName("Formula")[0].InnerText;
cell.Formula = formula;
}
if (contentElement.GetElementsByTagName("Value").Count != 0)
{
String value = contentElement.GetElementsByTagName("Value")[0].InnerText;
cell.Value = value;
}
cells[currentRow, currentColumn] = cell;
}
}
}
}
/*XmlNodeList graphs = doc.GetElementsByTagName("graph");
XmlTextReader textReader;
foreach (XmlNode graph in graphs)
{
try
{
textReader = new XmlTextReader(new StringReader(graph.InnerXml));
textReader.Read();
Graphs.GraphControl g = new ExcelClone.Graphs.GraphControl();
g.ReadXml(textReader);
//Controller.Instance.MainForm.WorksheetsTabControl.SelectedTab.Controls.Add(gc);
//gc.BringToFront();
tabs[i].Controls.Add(g);
g.BringToFront();
tabs[i].Refresh();
//Controller.Instance.MainForm.Controls.Add(g);
//g.BringToFront();
/*foreach (Control c in tabs[i].Controls)
{
if (c is Graphs.GraphControl)
c.BringToFront();
}*/
/*
}
catch (Exception e)
{
MessageBox.Show("Error: (" + e.GetType().ToString() + "): " + e.Message + Environment.NewLine + e.ToString(), "Error");
}
}*/
// Add current CellCollection to book
book.Add(new SpreadsheetModel(cells));
++i;
}
}
catch (Exception e)
{
MessageBox.Show("Error: (" + e.GetType().ToString() + "): " + e.Message + Environment.NewLine + e.ToString(), "Error");
return false;
}
return true;
}
private bool WriteBook()
{
int i = 0;
Cell theCell = new Cell();
Cell defaultCell = new Cell();
double tempDouble = new double();
bool blank = false;
fileStream = new FileStream(filename, FileMode.Create);
textWriter = new XmlTextWriter(fileStream, Encoding.Unicode);
try
{
textWriter.WriteStartElement(filename.Substring(filename.LastIndexOf("\\") + 1,
filename.LastIndexOf(".") - filename.LastIndexOf("\\") - 1));
for (int bookIndex = 0; bookIndex < book.Count; bookIndex++)
{
textWriter.WriteStartElement("sheet");
textWriter.WriteAttributeString("index", bookIndex.ToString());
int bookRows = book[bookIndex].Cells.Rows;
int bookColumns = book[bookIndex].Cells.Columns;
for (int column = 0; column < bookColumns; column++)
{
textWriter.WriteStartElement("column");
textWriter.WriteAttributeString("index", column.ToString());
//textWriter.WriteAttributeString("width", sv[column, 0].Size.Width.ToString());
//sv[0,0].Size.Height;
for (int row = 0; row < bookRows; row++)
{
theCell = book[bookIndex].Cells[row, column];
/*IS CELL DEFAULT CHECK*/
blank = false;
if (theCell == null || theCell.Value == null) { blank = true; }
else if (theCell.Value.Equals("")) { blank = true; }
/*CELL FORMAT needs to be instantiated*/
//if (theCell.CellFormat.IsDefault == false) { blank = true; }
if (blank == true)
{//skip writing the row, go on to the next cell.
}
else
{
textWriter.WriteStartElement("row");
textWriter.WriteAttributeString("index", row.ToString());
//textWriter.WriteAttributeString("height", sv[0, row].Size.Height.ToString());
if (theCell != null)
{
/*CONTENT TYPE CHECK*/
textWriter.WriteStartElement("content");
if (theCell.Formula.Contains("="))
{
textWriter.WriteAttributeString("type", "Formula");
}
else
{
try
{
tempDouble = double.Parse(theCell.Value);
textWriter.WriteAttributeString("type", "Number");
}
catch (System.FormatException e)
{
textWriter.WriteAttributeString("type", "Text");
}
}
textWriter.WriteAttributeString("CellFormat", theCell.CellFormat.serialize());
//FORMULA CHECK
if (theCell.Formula.Equals("") || theCell.Formula.Equals(null))
{ } //ignore
else
{
textWriter.WriteElementString("Formula", theCell.Formula.ToString());
}
//VALUE CHECK
if (theCell.Value.Equals("") || theCell.Value.Equals(null))
{ } //ignore
else
{
textWriter.WriteElementString("Value", theCell.Value.ToString());
}
textWriter.WriteEndElement();//end of content type element?
}//end of theCell != null if
/*****Where do all these other write elements correspond to?*/
textWriter.WriteEndElement();
}//end of blank cell check if/else
}//end of row loop
textWriter.WriteEndElement();
}//end of column loop
//NATHAN SULLIVAN: Test code for graphs
System.Windows.Forms.TabControl.TabPageCollection tabs = Controller.Instance.GetMainTabPageCollection();
foreach (Control c in tabs[i].Controls)
{
if (c is Graphs.GraphControl) //try to serialize all graph controls
{
textWriter.WriteStartElement("graph");
((Graphs.GraphControl)c).WriteXml(textWriter);
textWriter.WriteEndElement();
}
}
textWriter.WriteEndElement();
++i;
}
textWriter.WriteEndElement();
textWriter.Flush();
}
catch (Exception e)
{
MessageBox.Show("Error: (" + e.GetType().ToString() + "): " +
e.Message + Environment.NewLine +
"Debug Data: " + e.ToString(), "Error");
}
finally
{
if (textWriter != null)
{
textWriter.Close();
textWriter = null;
}
}
return true;
}
private bool WriteBook()
{
Cell theCell = new Cell();
Cell defaultCell = new Cell();
Cell tempCell = new Cell();
/**** TEST Code*/
CellCollection cc = new CellCollection();
FontFamily blahFamily = new FontFamily("Arial");
Font blahFont = new Font(blahFamily, 13);
CellFormat blahFormat = new CellFormat(blahFont,Color.Orange, Color.Green);
Font f = new Font(FontFamily.GenericSansSerif, 12.0F, FontStyle.Bold);
f = new Font(f, f.Style | FontStyle.Italic);
CellFormat blahFormat2 = new CellFormat(f, Color.Black, Color.Black);
//blahFormat.CellColor = Color.Yellow;
//blahFormat.TextColor = Color.Purple;
//tempCell.CellFormat = blahFormat;
cc[0, 0] = new Cell("= SUM()", blahFormat);
cc[0, 0].Value = "42";
cc[0, 1] = new Cell("", tempCell.CellFormat);
cc[0, 2] = new Cell("", tempCell.CellFormat);
cc[1, 0] = new Cell("", tempCell.CellFormat);
cc[1, 0].Value = "blah";
cc[1, 1] = new Cell("= AVG()", tempCell.CellFormat);
cc[1, 1].Value = "47";
cc[1, 2] = new Cell("", blahFormat2);
cc[1, 2].Value = "99";
cc[2, 0] = new Cell("", tempCell.CellFormat);
cc[2, 0].Value = "YourMother";
cc[2, 1] = new Cell("", tempCell.CellFormat);
cc[2, 2] = new Cell("", tempCell.CellFormat);
book[0] = new SpreadsheetModel(cc);
//Cell theCell = new Cell();
//END TEST CODE
double tempDouble = new double();
bool blank = false;
fileStream = new FileStream(filename, FileMode.Create);
textWriter = new XmlTextWriter(fileStream, Encoding.Unicode);
try
{
textWriter.WriteStartElement(filename.Substring(filename.LastIndexOf("\\") + 1,
filename.LastIndexOf(".") - filename.LastIndexOf("\\") - 1));
textWriter.WriteStartElement("sheet");
textWriter.WriteStartElement("author");
textWriter.WriteAttributeString("name", "Nathan Benjamin");
textWriter.WriteEndElement();
textWriter.WriteString(Environment.NewLine + "other metadata" + Environment.NewLine);
int bookRows = book[0].Cells.Rows;
int bookColumns = book[0].Cells.Columns;
for (int column = 0; column < bookColumns; column++)
{
textWriter.WriteStartElement("column");
textWriter.WriteAttributeString("index", column.ToString());
for (int row = 0; row < bookRows; row++)
{
theCell = book[0].Cells[row, column];
/*IS CELL DEFAULT CHECK*/
blank = false;
if (theCell.Value == null) { blank = true; }
else if (theCell.Value.Equals("")) { blank = true; }
/*CELL FORMAT needs to be instantiated*/
//if (theCell.CellFormat.IsDefault == false) { blank = true; }
if (blank == true)
{//skip writing the row, go on to the next cell.
}
else
{
textWriter.WriteStartElement("row");
textWriter.WriteAttributeString("index", row.ToString());
if (theCell != null)
{
/*CONTENT TYPE CHECK*/
textWriter.WriteStartElement("content");
if (theCell.Formula.Contains("="))
{
textWriter.WriteAttributeString("type", "Formula");
}
else
{
try
{
tempDouble = double.Parse(theCell.Value);
textWriter.WriteAttributeString("type", "Number");
}
catch (System.FormatException e)
{
textWriter.WriteAttributeString("type", "Text");
}
}
/*IF THE CELLFORMAT IS DEFAULT, WE WRITE NOTHING ABOUT CELL FORMAT
if(theCell.CellFormat.IsDefault){//skip the cell
}
else{*/
//Each Format attribute is compared to the default cell. If that .Equals method returns false
//Then theCell has an attribute different from defaultCell, and that attribute must be written.
//CellColor
if (theCell.CellFormat.CellColor.Equals(defaultCell.CellFormat.CellColor) == false)
textWriter.WriteAttributeString("CellColor", theCell.CellFormat.CellColor.ToString());
//TextColor
if (theCell.CellFormat.TextColor.Equals(defaultCell.CellFormat.TextColor) == false)
textWriter.WriteAttributeString("TextColor", theCell.CellFormat.TextColor.ToString());
//Bold
if (theCell.CellFormat.CellFont.Bold.Equals(defaultCell.CellFormat.CellFont.Bold) == false)
textWriter.WriteAttributeString("Bold", "true");
//Italics
if (theCell.CellFormat.CellFont.Italic.Equals(defaultCell.CellFormat.CellFont.Italic) == false)
textWriter.WriteAttributeString("Italic", "true");
//Underline
if (theCell.CellFormat.CellFont.Underline.Equals(defaultCell.CellFormat.CellFont.Underline) == false)
textWriter.WriteAttributeString("Underline", "true");
//FontFamily
if (theCell.CellFormat.CellFont.FontFamily.Equals(defaultCell.CellFormat.CellFont.FontFamily) == false)
textWriter.WriteAttributeString("FontFamily", theCell.CellFormat.CellFont.FontFamily.ToString());
//Size
if (theCell.CellFormat.CellFont.Size.Equals(defaultCell.CellFormat.CellFont.Size) == false)
textWriter.WriteAttributeString("Size", theCell.CellFormat.CellFont.Size.ToString());
/*}//end of Format Check
*/
//FORMULA CHECK
if (theCell.Formula.Equals("") || theCell.Formula.Equals(null))
{} //ignore
else{
textWriter.WriteElementString("Formula", theCell.Formula.ToString());
}
//VALUE CHECK
if (theCell.Value.Equals("") || theCell.Value.Equals(null))
{ } //ignore
else
{
textWriter.WriteElementString("Value", theCell.Value.ToString());
}
textWriter.WriteEndElement();//end of content type element?
}//end of theCell != null if
/*****Where do all these other write elements correspond to?*/
textWriter.WriteEndElement();
}//end of blank cell check if/else
}//end of row loop
textWriter.WriteEndElement();
}//end of column loop
textWriter.WriteEndElement();
textWriter.WriteEndElement();
textWriter.Flush();
}
catch (Exception e)
{
MessageBox.Show("Error: (" + e.GetType().ToString() + "): " +
e.Message + Environment.NewLine +
"Debug Data: " + e.ToString(), "Error");
}
finally
{
if (textWriter != null)
{
textWriter.Close();
textWriter = null;
}
}
return true;
}
private bool ReadBook()
{
int i = 0;
int currentRow, currentColumn;
XmlDocument doc = new XmlDocument();
try
{
doc.Load(filename);
XmlNodeList sheetList = doc.GetElementsByTagName("sheet");
// Traverse the List of Sheets
foreach (XmlNode sheet in sheetList)
{
CellCollection cells = new CellCollection();
XmlElement sheetElement = (XmlElement)sheet;
String sheetName = "";
if (sheetElement.HasAttributes)
{
if (sheetElement.Attributes[0].Name == "name")
sheetName = sheetElement.Attributes["name"].InnerText;
}
XmlNodeList columnList = sheetElement.GetElementsByTagName("column");
// Traverse the List of Columns
foreach (XmlNode column in columnList)
{
XmlElement columnElement = (XmlElement)column;
String columnNumber = "";
if (columnElement.HasAttributes)
{
if (columnElement.Attributes[0].Name == "index")
columnNumber = columnElement.Attributes["index"].InnerText;
}
currentColumn = Int32.Parse(columnNumber);
XmlNodeList rowList = columnElement.GetElementsByTagName("row");
// Traverse the List of Rows
foreach (XmlNode row in rowList)
{
XmlElement rowElement = (XmlElement)row;
String rowNumber = "";
if (rowElement.HasAttributes)
{
if (rowElement.Attributes[0].Name == "index")
rowNumber = rowElement.Attributes["index"].InnerText;
}
currentRow = Int32.Parse(rowNumber);
for (int j = 0; j < rowElement.Attributes.Count; j++)
{
//MessageBox.Show("Row: " + rowNumber + " Column: " + columnNumber + "\n");
if (rowElement.GetElementsByTagName("content").Count != 0)
{
XmlElement contentElement = (XmlElement)rowElement.GetElementsByTagName("content")[0];
Cell cell = new Cell();
if (contentElement.HasAttribute("CellFormat"))
{
String settings = contentElement.GetAttribute("CellFormat");
cell.CellFormat = CellFormatFactory.createCellFormat(settings);
}
if (contentElement.GetElementsByTagName("Formula").Count != 0)
{
String formula = contentElement.GetElementsByTagName("Formula")[0].InnerText;
cell.Formula = formula;
}
if (contentElement.GetElementsByTagName("Value").Count != 0)
{
String value = contentElement.GetElementsByTagName("Value")[0].InnerText;
cell.Value = value;
}
cells[currentRow, currentColumn] = cell;
}
}
}
}
// Add current CellCollection to book
book.Add(new SpreadsheetModel(cells));
}
}
catch (Exception e)
{
MessageBox.Show("Error: (" + e.GetType().ToString() + "): " + e.Message, "Error");
}
return true;
}
void SpreadsheetView_RowHeightChanged(object sender, DataGridViewRowEventArgs e)
{
int row = e.Row.Index;
SpreadsheetModel model = spreadsheetModel;
for(int i = 0; i < ColumnCount; i++) {
Cell cell = model.Cells[row,i];
if (cell == null)
{
cell = new Cell();
model.Cells[row,i] = cell;
}
cell.CellFormat.CellHeight = this.Rows[row].Height;
}
}
void SpreadsheetView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
Console.WriteLine("called");
int row = e.RowIndex;
int col = e.ColumnIndex;
if (row < 0 || col < 0)
return;
SpreadsheetModel model = spreadsheetModel;
Cell cell = model.Cells[row, col];
if (cell == null)
{
cell = new Cell();
model.Cells[row, col] = cell;
}
cell.Formula = this.Rows[row].Cells[col].Value + "";
SpreadsheetControl.Instance.CellChanged(new CellKey(row, col));
//cell.Value = Controller.Instance.Parser.Parse(MakeColumnLabel(col) + row + ":" + cell.Formula);
this.Rows[row].Cells[col].Value = model.Cells[row, col].Value;
}
void SpreadsheetView_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
int row = e.RowIndex;
int col = e.ColumnIndex;
SpreadsheetModel model = spreadsheetModel;
Cell cell = model.Cells[row, col];
if (cell == null)
{
cell = new Cell();
model.Cells[row, col] = cell;
}
this.Rows[row].Cells[col].Value = cell.Formula;
}
void SpreadsheetView_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
int row = e.RowIndex;
int col = e.ColumnIndex;
char cellCol = (char)('A' + col);
string cellPos = "";
cellPos += cellCol;
cellPos += (row+1);
SpreadsheetModel model = spreadsheetModel;
Cell cell = model.Cells[row, col];
if (cell == null)
{
cell = new Cell();
model.Cells[row, col] = cell;
}
Controller.Instance.MainForm.SelectedCellBox.Text = cellPos;
Controller.Instance.MainForm.FormulaBox.Text = cell.Formula;
Controller.Instance.MainForm.FontSizeSelectionBox.SelectedIndex = (int)cell.CellFormat.CellFont.Size;
int i = Controller.Instance.MainForm.FontSelectionBox.Items.IndexOf(cell.CellFormat.CellFont.Name);
Controller.Instance.MainForm.FontSelectionBox.SelectedIndex = i;
}