Home Forums WinForms controls Xceed Grid for WinForms Exporting data from Xceed Grid Reply To: Exporting data from Xceed Grid

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)