Home Forums WinForms controls Xceed Grid for WinForms Exporting data from Xceed Grid

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • User (Old forums)
    Member
    Post count: 23064
    #12645 |

    Anyone has ever done this?
    I’m interested in exporting to Word, Excel and HTML.

    Imported from legacy forums. Posted by Martha (had 6150 views)

    Xceed Support
    Member
    Post count: 5658

    Hi Martha,

    There is currently no built-in functionality that will allow you to export the grid’s content to Excel however we do have a sample on the knowledge base that demonstrates how to copy and paste as well as drag and drop rows to and from Excel. It might help point you in the right direction!

    Here is the link : <a href=”http://www.xceedsoft.com/kb/result.asp?id=422″>http://www.xceedsoft.com/kb/result.asp?id=422</a&gt;

    Imported from legacy forums. Posted by Jenny [Xceed] (had 774 views)

    User (Old forums)
    Member
    Post count: 23064

    Martha: The following code will export to a text file formatted for easy import to Excel. grdDisplay is the Xceed.Grid.GridControl object. It may not be _exactly_ what you wanted, but there’s some pretty cool stuff that surely will get to started. It serves our purpose just fine.

    Mark Bonafe

    public void Export2TabbedFile()
    {
    // Create the FileStream and StreamWriter object to write
    // the recordset contents to file.
    System.Windows.Forms.SaveFileDialog saveDialog = new System.Windows.Forms.SaveFileDialog();
    saveDialog.Title = “Export Grid Contents to File:”;
    saveDialog.DefaultExt = “txt”;
    System.Windows.Forms.DialogResult result = saveDialog.ShowDialog(this);
    string fileName;
    if (result == System.Windows.Forms.DialogResult.OK)
    {
    fileName = saveDialog.FileName;
    }
    else
    {
    return;
    }

    System.IO.FileStream fs = new System.IO.FileStream(fileName, System.IO.FileMode.Create);
    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Unicode);

    // Write the field names (headers) as the first line in the text file.
    for (int colIndex = 0; colIndex < grdDisplay.Columns.Count; colIndex++)
    {
    for (int col = 0; col < grdDisplay.Columns.Count; col++)
    {
    if (grdDisplay.Columns[col].Visible)
    {
    if (grdDisplay.Columns[col].VisibleIndex == colIndex)
    {
    WriteText(sw,grdDisplay.Columns[col].Title + “\t”);
    break;
    }
    }
    }
    }
    WriteTextLine(sw,””);

    // Get the rows as sorted in the grid
    if (grdDisplay.Groups.Count > 0)
    {
    for (int grp = 0; grp < grdDisplay.Groups.Count; grp++)
    {
    WriteTextLine(sw,grdDisplay.Groups[grp].GroupByRowTitle + “: ” + grdDisplay.Groups[grp].Title);
    ExportGroup(grdDisplay, grdDisplay.Groups[grp], sw);
    }
    }
    else
    {
    Xceed.Grid.Collections.ReadOnlyDataRowList gridRows = grdDisplay.GetSortedDataRows(true);
    ExportRows(grdDisplay, gridRows, sw);
    }

    // Close the FileStream.
    fs.Close();
    MessageBox.Show(“File exported!”,”Success”,System.Windows.Forms.MessageBoxButtons.OK,System.Windows.Forms.MessageBoxIcon.Information);
    }

    private void ExportGroup(Xceed.Grid.GridControl exportGrid, Xceed.Grid.Group exportGroup, System.IO.StreamWriter sw)
    {
    for (int grp = 0; grp < exportGroup.Groups.Count; grp++)
    {
    sw.WriteLine(exportGroup.Groups[grp].GroupByRowTitle + “: ” + exportGroup.Groups[grp].Title);
    if (exportGroup.Groups[grp].Groups.Count > 0)
    ExportGroup(exportGrid, exportGroup.Groups[grp], sw);
    else
    {
    Xceed.Grid.Collections.ReadOnlyDataRowList gridRows = exportGroup.Groups[grp].GetSortedDataRows(false);
    ExportRows(exportGrid, gridRows, sw);
    }
    }
    }

    private void ExportRows(Xceed.Grid.GridControl exportGrid, Xceed.Grid.Collections.ReadOnlyDataRowList gridRows, System.IO.StreamWriter sw)
    {
    int col;
    int row;
    int colIndex = 0;
    for (row = 0; row < gridRows.Count; row++)
    {
    if (gridRows[row].Visible)
    {
    for (colIndex = 0; colIndex < exportGrid.Columns.Count; colIndex++)
    {
    for (col = 0; col < exportGrid.Columns.Count; col++)
    {
    if (exportGrid.Columns[col].Visible)
    {
    if (exportGrid.Columns[col].VisibleIndex == colIndex)
    {
    if (gridRows[row].Cells[col].CellViewer == null)
    sw.Write(gridRows[row].Cells[col].Value.ToString() + “\t”);
    else
    {
    // First, figure out which type of CellViewer is being used
    // Second, perform a lookup on it
    // Third, write the text
    string text2Write = gridRows[row].Cells[col].Value.ToString();
    if (gridRows[row].Cells[col].CellViewer is Xceed.Grid.Editors.GridComboBox)
    {
    Xceed.Grid.Editors.GridComboBox cbxCombo;
    cbxCombo = (Xceed.Grid.Editors.GridComboBox)gridRows[row].Cells[col].CellViewer;
    System.Data.DataView dv;
    try
    {
    dv = (System.Data.DataView)cbxCombo.DataSource;
    text2Write = FindComboEntry(dv, gridRows[row].Cells[col].Value,cbxCombo.ValueMember, cbxCombo.DisplayMember);
    }
    catch (InvalidCastException)
    {
    try
    {
    dv = ((System.Data.DataTable)cbxCombo.DataSource).DefaultView;
    text2Write = FindComboEntry(dv, gridRows[row].Cells[col].Value, cbxCombo.ValueMember, cbxCombo.DisplayMember);
    }

    Imported from legacy forums. Posted by MBonafe (had 710 views)

    User (Old forums)
    Member
    Post count: 23064

    To copy grid data to the clipboard in a format that Excel and Word recognize, you might consider using HTML.

    1. Constructing the HTML-string:

    StringBuilder str = new StringBuilder();
    str.Append(“<table>”); // begin table
    // pseudocode
    for each row you want to add
    {
    str.Append(“<tr>”); // begin row
    for each cell you want to add
    {
    str.Append(“<td>”); // begin cell
    str.Append(the_value_in_the_cell);
    str.Append(“</td>”); // end cell
    }
    str.Append(“</tr>”); // end row
    }
    str.Append(“</table>”); // end table

    2. Copying the HTML to the Clipboard:

    See the message I wrote at the microsoft.public.dotnet.framework newsgroup: <a href=”http://groups.google.com/groups?th=5cc22c613a75e726″>http://groups.google.com/groups?th=5cc22c613a75e726</a&gt;.

    Imported from legacy forums. Posted by Tommy (had 622 views)

    User (Old forums)
    Member
    Post count: 23064

    MBonafe:

    Is the WriteText() method a custom method or a .net method?

    Imported from legacy forums. Posted by C# (had 318 views)

    User (Old forums)
    Member
    Post count: 23064

    Martha,

    I, too, was disappointed to hear that there was no “Export to Excel” feature — and that none is even in the works. I created a custom component that inherited from the grid and added this method to do just that, using OLEDB (for better or worse).

    Be sure to include these…
    using System.Data;
    using System.Data.OleDb;

    public void ExportToExcel()
    {
    if(DataRows.Count > 0)
    {
    //Setup
    OleDbConnection conn = new OleDbConnection();
    OleDbCommand cmd = new OleDbCommand();
    string strFileName = “”;
    string strConnectionString = “”;
    string strCommandText = “”;
    bool boolDropTableFirst = false;

    //Setup Save File Dialog Properties
    dlgSaveFile.Filter = “Excel files (*.xls)|*.xls”;

    //If user clicked OK, save file
    if(dlgSaveFile.ShowDialog() == DialogResult.OK)
    {
    //Setup
    strFileName = dlgSaveFile.FileName;

    //Check to see if file already exists
    if(File.Exists(strFileName))
    {
    //If so, delete existing sheet within file
    strCommandText = “DROP TABLE Sheet1”;
    boolDropTableFirst = true;
    }

    //Create connection string, open connection
    strConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strFileName + “;Extended Properties=\”Excel 8.0;HDR=YES;\””;
    conn.ConnectionString = strConnectionString;
    conn.Open();
    cmd.Connection = conn;

    //If boolDropTableFirst, delete existing sheet within file
    if(boolDropTableFirst)
    {
    //Execute drop
    try
    {
    cmd.CommandText = strCommandText;
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    MessageBox.Show(strCommandText + Environment.NewLine + ex.Message);
    throw ex;
    }
    }

    //Start to build Create Table sheet
    strCommandText = “CREATE TABLE Sheet1 (“;

    //Add column names and data types
    for(int i=0; i<Columns.Count; i++)
    {
    if(Columns[i].Visible)
    strCommandText = strCommandText + Columns[i].Title + ” ” + Columns[i].DataType.ToString().Replace(“System.”,””) + “, “;
    }

    //Remove last comma
    strCommandText = strCommandText.Remove(strCommandText.Length-2, 2) + “)”;
    cmd.CommandText = strCommandText;

    try
    {
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    MessageBox.Show(strCommandText + Environment.NewLine + ex.Message);
    throw ex;
    }

    if (true)
    {

    //Add data to grid
    for(int j=0; j<DataRows.Count; j++)
    {
    strCommandText = “INSERT INTO Sheet1 VALUES (“;

    for(int k=0; k<Columns.Count; k++)
    {
    if(Columns[k].Visible)
    {
    //Base insert string on data type of column
    if((Columns[k].DataType.ToString() == “System.Decimal”) || (Columns[k].DataType.ToString() == “System.Single”) || (Columns[k].DataType.ToString() == “System.Double”) || (Columns[k].DataType.ToString().IndexOf(“Int”) > -1))
    if(DataRows[j].Cells[k].Value.ToString().Length == 0)
    strCommandText = strCommandText + “NULL, “;
    else
    strCommandText = strCommandText + DataRows[j].Cells[k].Value + “, “;
    else
    if(DataRows[j].Cells[k].Value.ToString().Length == 0)
    strCommandText = strCommandText + “NULL, “;
    else
    strCommandText = strCommandText + “‘” + DataRows[j].Cells[k].Value + “‘, “;
    }
    }

    //Clean up Command Text
    strCommandText = strCommandText.Remove(strCommandText.Length-2, 2) + “)”;
    cmd.CommandText = strCommandText;

    //Execute Command Text
    try
    {
    cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
    MessageBox.Show(strCommandText + Environment.NewLine + ex.Message);
    throw ex;
    }
    }
    }

    //Cleanup
    MessageBox.Show(“Export Complete.”);
    conn.Close();
    }
    else
    MessageBox.Show(“Nothing to Export!” + System.Environment.NewLine + System.Environment.NewLine + Title + ” contains no data.”, “Warning”, MessageBoxButtons.OK, MessageBoxIcon.Warning);
    }
    }

    Imported from legacy forums. Posted by billmonti (had 485 views)

    User (Old forums)
    Member
    Post count: 23064

    Not sure if this has already been covered but to export to Excel from any grid one could simply instantiate an instance of Excel and control it directly. This way you can access all of the Excel functionality – opening, saving, writing, reading, charts, etc., etc. – from within your app. Although Excel is only exposed as a COM object, you can Interop it and use it from .NET.

    Just a slight caveat… if you create a visible instance of Excel and start to write data to it, then moving the focus from your app to the Excel instance can create Excel exceptions. Work around this by creating hidden instances of Excel, write your data then make it visible.

    Just a thought…

    p.s. I too would like to see “Export to Excel” functionality. Saying that, the Xceed Grid control is very impressive.

    Imported from legacy forums. Posted by John (had 6589 views)

Viewing 7 posts - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.