Save and load DataGridView Content and Style in C#

Question

I have a DataGridView with many columns and rows, the user is able to right click in a cell and select an option from a ContextMenuStrip. The options are in Colors like Red, Blue, Green, etc, if the user selects, for example, Red, the selected cell sets its BackColor to Red, and the user is also able to write a value in that cell. Well, my problem is that, I cannot find a way to save all the content and style, so if the user re-opens the for, the dataGridView wil have its last settings (including the BackColor and ForeColor of the cells).

I tried this to save the content, it gave me error, and I don't know how to open it.

 private void button4_Click(object sender, EventArgs e)
    {
        SaveFileDialog svd = new SaveFileDialog();
        svd.Filter = "XML Files (*.xml)|*.xml";

        if(svd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {
            DataTable dt = ((DataView)this.dataGridView1.DataSource).Table;
            dt.WriteXml(svd.FileName);
        }
    }

If there is a better way to save the content and style, it is welcome too. Thanks in Advance

There's probably a better way, but you could create a "cell settings" class that has a few properties (value, backcolor, forecolor). Then using object serialization, you could save these settings to xml on form closing and read these settings in the form constructor. — OhBeWise
Could you give me an example, just with the backcolor of the cells(to save the color setting)? — ChrisCreateBoss
Are you looking for a complete working example? Or did you discover that Color doesn't serialize the same way? The former will take more time than the latter is why I am asking. — OhBeWise
Just the loop that I should use, like foreach(cell in dataGridView){ .... } — ChrisCreateBoss
And.. if there is a way to save the dataGridView as an Excel file with the backcolor of each cell included, it would be perfect. — ChrisCreateBoss

OhBeWise OhBeWise · Accepted Answer · 2015-02-04T18:37:59

If persistent cell formatting is all you want, the following type of object serialization will work for you.

Create a class to serialize your desired properties:

public class SavedSettings
{
    [XmlIgnore]
    public Color ForeColor { get; set; }

    [XmlElement("ForeColor")]
    public int ForeColorARGB
    {
      get { return this.ForeColor.ToArgb(); }
      set { this.ForeColor = Color.FromArgb(value); }
    }

    [XmlIgnore]
    public Color BackColor { get; set; }

    [XmlElement("BackColor")]
    public int BackColorARGB
    {
      get { return this.BackColor.ToArgb(); }
      set { this.BackColor = Color.FromArgb(value); }
    }

    public object Value { get; set; }
}

Within your main class, load any saved settings from xml:

public List<SavedSettings> Settings { get; set; }

private void ReadXML()
{
  System.Xml.Serialization.XmlSerializer reader =
      new System.Xml.Serialization.XmlSerializer(typeof(List<SavedSettings>));

  if (File.Exists(@"SavedSettings.xml"))
  {
    System.IO.StreamReader file = new System.IO.StreamReader(
      @"SavedSettings.xml");
    this.Settings = (List<SavedSettings>)reader.Deserialize(file);
    file.Close();
  }
}

private void LoadDGV()
{
  this.ReadXML();

  if (this.Settings != null)
  {
    // This assumes your dgv has added columns already.
    int rows = this.Settings.Count / this.dataGridView1.Columns.Count;
    int cols = this.dataGridView1.Columns.Count;

    this.dataGridView1.Rows.AddCopies(0, rows);

    for (int i = 0; i < this.Settings.Count; i++)
    {
      int row = i / cols;
      int col = i % cols;
      this.dataGridView1[col, row].Style.BackColor = this.Settings[i].BackColor;
      this.dataGridView1[col, row].Style.ForeColor = this.Settings[i].ForeColor;
      this.dataGridView1[col, row].Value = this.Settings[i].Value;
    }
  }
}

Then, when you're ready to save, reload your cell settings into the object array and serialize it:

private void SaveSettings()
{
  this.Settings = new List<SavedSettings>();

  foreach (DataGridViewRow row in this.dataGridView1.Rows)
  {
    if (!row.IsNewRow)
    {
      foreach (DataGridViewCell cell in row.Cells)
      {
        SavedSettings setting = new SavedSettings();
        setting.BackColor = cell.Style.BackColor.ToArgb() == 0 ? Color.White : cell.Style.BackColor;
        setting.ForeColor = cell.Style.ForeColor.ToArgb() == 0 ? Color.Black :  cell.Style.ForeColor; ;
        setting.Value = cell.Value;

        this.Settings.Add(setting);
      }
    }
  }

  this.WriteXML();
}

private void WriteXML()
{
  System.Xml.Serialization.XmlSerializer writer =
      new System.Xml.Serialization.XmlSerializer(typeof(List<SavedSettings>));

  System.IO.StreamWriter file = new System.IO.StreamWriter(@"SavedSettings.xml");
  writer.Serialize(file, this.Settings);
  file.Close();
}

Note This will allow certain properties to persist. Surely there are better methods and I'd love to learn them as time allows, but this example can do the job. As for the additional Excel requirement, I have not tried that to date. I would supplement your question to reflect that requirement to attract Excel-oriented answers.

Save and load DataGridView Content and Style in C#

1 Answers