Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# .NET VB.NET
Can I have more than 1 criteria for Dataview.Rowfilter on the same column ?
For example:
Dataview.Rowfilter = "Column1 = " & Var1 & "AND Column1 = " & Var2
 
Thanks
Anil Sharma
Posted 10-Jun-13 21:37pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Yes, You can have multiple condition based on the problem to filter a dataview. But make sure you use the correct operand and the syntax. For example: if it a varchar type then it goes like this
dv.RowFilter = " Status='Active' OR Status='InActive'"
int type
dv.RowFilter = "id > 0 AND index = 4"
  Permalink  
Comments
S. K. Tripathi at 19-Aug-14 6:13am
   
thanks it works for me also....
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Thanx that solved my problem Smile | :)
  Permalink  
Comments
aksjustcool at 3 days ago
   
///app.config







 
///databaseconn.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
 
namespace ExcelBeginner
{
class Query
{
public DataTable MyDataTable(string query, string Connstr)
{
try
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(query, Connstr);
da.Fill(dt);
return (dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
 
////program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
 
namespace BeginExcel
{
static class Program
{
///
/// The main entry point for the application.
///

[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new RzExcel());
}
}
}
 

//// Excel.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace BeginExcel
{
public partial class RzExcel : Form
{
public RzExcel()
{
InitializeComponent();
}
 
object misValue = System.Reflection.Missing.Value;
ExcelBeginner.Query objQuery = new ExcelBeginner.Query();

public void CreatExcelFromDB(string sqlquery, string Connstr, string FileName)
{
 
SqlDataAdapter DA = new SqlDataAdapter(sqlquery, Connstr);
DataSet DS = new DataSet();
DA.Fill(DS);
CreateExcel(DS, FileName);
}
public void CreatExcelFromDS(DataSet DS, string FileName)
{
CreateExcel(DS, FileName);
}
public DataSet ExcelToDT(string _Location)
{
DataSet ds = new DataSet();
var excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", _Location);
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
connection.ConnectionString = excelConnectionString;
DataTable sheets = GetSchemaTable(excelConnectionString);
 
foreach (DataRow r in sheets.Rows)
{
string query = "SELECT * FROM [" + r.ItemArray[0].ToString() + "]";
ds.Clear();
System.Data.OleDb.OleDbDataAdapter data = new System.Data.OleDb.OleDbDataAdapter(query, connection);
data.Fill(ds);
 
}
 
return ds;
}
 



private static void WriteArray(int rows, int columns, Excel.Worksheet worksheet)
{
var data = new object[rows, columns];
for (var row = 1; row <= rows; row++)
{
for (var column = 1; column <= columns; column++)
{
data[row - 1, column - 1] = "Test";
}
}
 
var startCell = (Excel.Range )worksheet.Cells[1, 1];
var endCell = (Excel.Range )worksheet.Cells[rows, columns];
var writeRange = worksheet.Range[startCell, endCell];

writeRange.Value2 = data;
}
static DataTa
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

///assemblyinfo.cs
 

using System.Reflection;
using System.Runtime.CompilerServices;
using System.Runtime.InteropServices;
 
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
[assembly: AssemblyTitle("BeginExcel")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("BeginExcel")]
[assembly: AssemblyCopyright("Copyright ©  2012")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
 
// Setting ComVisible to false makes the types in this assembly not visible
// to COM components.  If you need to access a type in this assembly from
// COM, set the ComVisible attribute to true on that type.
[assembly: ComVisible(false)]
 
// The following GUID is for the ID of the typelib if this project is exposed to COM
[assembly: Guid("53834b51-22bc-47f9-b166-45645eb0dd35")]
 
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Build and Revision Numbers
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]
 

 
////databaseconn.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
 
namespace ExcelBeginner
{
class Query
{
public DataTable MyDataTable(string query, string Connstr)
{
try
{
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(query, Connstr);
da.Fill(dt);
return (dt);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
 
///Excel.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace BeginExcel
{
public partial class RzExcel : Form
{
public RzExcel()
{
InitializeComponent();
}
 
object misValue = System.Reflection.Missing.Value;
ExcelBeginner.Query objQuery = new ExcelBeginner.Query();

public void CreatExcelFromDB(string sqlquery, string Connstr, string FileName)
{
 
SqlDataAdapter DA = new SqlDataAdapter(sqlquery, Connstr);
DataSet DS = new DataSet();
DA.Fill(DS);
CreateExcel(DS, FileName);
}
public void CreatExcelFromDS(DataSet DS, string FileName)
{
CreateExcel(DS, FileName);
}
public DataSet ExcelToDT(string _Location)
{
DataSet ds = new DataSet();
var excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", _Location);
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
connection.ConnectionString = excelConnectionString;
DataTable sheets = GetSchemaTable(excelConnectionString);
 
foreach (DataRow r in sheets.Rows)
{
string query = "SELECT * FROM [" + r.ItemArray[0].ToString() + "]";
ds.Clear();
System.Data.OleDb.OleDbDataAdapter data = new System.Data.OleDb.OleDbDataAdapter(query, connection);
data.Fill(ds);
 
}
 
return ds;
}
 



private static void WriteArray(int rows, int columns, Excel.Worksheet worksheet)
{
var data = new object[rows, columns];
for (var row = 1; row <= rows; row++)
{
for (var column = 1; column <= columns; column++)
{
data[row - 1, column - 1] = "Test";
}
}
 
var startCell = (Excel.Range )worksheet.Cells[1, 1];
var endCell = (Excel.Range )worksheet.Cells[rows, columns];
var writeRange = worksheet.Range[startCell, endCell];

writeRange.Value2 = data;
}
static DataTable GetSchemaTable(string connectionString)
{
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
{
connection.Open();
 
DataTable schemaTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
return schemaTable;
}
}
private void CreateExcel(DataSet DS,string _FileName)
{

Excel.Application xlApp;
Excel.Workbook xlworkBook;
Excel.Range range;
 

object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlworkBook = xlApp.Workbooks.Add(misValue);
Excel.Worksheet xlworkSheet = xlworkBook.ActiveSheet as Excel.Worksheet;
int RRow = 0;
string[,] tempArray = new string[DS.Tables[0].Rows.Count,DS.Tables[0].Columns.Count];
try
{
for (int i = 1; i <= (DS.Tables[0].Rows.Count / 65536 )+ 1; i++)
{
xlworkSheet = (Excel.Worksheet)xlworkBook.Worksheets.get_Item(i);
range = null;
range = xlworkSheet.get_Range("A1", misValue);
range = range.get_Resize(DS.Tables[0].Rows.Count, DS.Tables[0].Columns.Count);
 
if (65536 * i < DS.Tables[0].Rows.Count)
{
if (i <= 1)
{
for (RRow = RRow; RRow <= 65536 * i - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[RRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
}
}
 
range.set_Value(System.Reflection.Missing.Value, tempArray);
//xlworkBook.SaveAs("Myfile.xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
Array.Clear(tempArray, 0, tempArray.Length);
tempArray = new string[DS.Tables[0].Rows.Count, DS.Tables[0].Columns.Count];
 
}
else
{
int tempRow = 0;
for (RRow = RRow; RRow < DS.Tables[0].Rows.Count - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[tempRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
 
}
tempRow++;
}
range.set_Value(System.Reflection.Missing.Value, tempArray);
xlworkBook.SaveAs( _FileName +".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
}
else
{
int tempRow = 0;
for (RRow = RRow; RRow < DS.Tables[0].Rows.Count - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[tempRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
}
tempRow++;
}
}
range.set_Value(System.Reflection.Missing.Value, tempArray);
xlworkBook.SaveAs(_FileName + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}

}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}

xlApp.UserControl = true;
//Process pro = Process.Start(Application.StartupPath );

}
 
private void RzExcel_Load(object sender, EventArgs e)
{
DataSet DS = new DataSet();
/// Columns Created
DS =CreateReportColumns("Col1", "Col2", "Col3", "Col4", "Col5", "Col6");
DS = AddNEwRow(DS.Tables[0], "Value1","Value2","Value3","Value4","Value5","Value6");
}
public DataSet CreateReportColumns( params string[] values)
{
 
DataSet DS = new DataSet();
DS.Tables.Add(MakeDataTable( values));
return DS;
 
}
 
private DataTable MakeDataTable(string[] Values)
{
DataTable _DT = new DataTable();
DataColumn DC = default(DataColumn);
try
{
_DT = new DataTable();
 
for (int i = 0; i < Values.Length; i++)
{
DC = new DataColumn();
DC.ColumnName = Values[i].ToString();
DC.DataType = Type.GetType("System.String");
_DT.Columns.Add(DC);
 
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return _DT;
}
 
private DataSet AddNEwRow(DataTable table, params string[] Values )
{
DataSet DS = new DataSet();
DataRow row;
row = table.NewRow();
int _ColumnCount = table.Columns.Count;
int _ValueCount = Values.Length;

if (_ValueCount = _ColumnCount )
{
for (int i = 0; i < Values.Length; i++)
{
row[table.Columns[i].ToString()] = Values[i].ToString();
}
table.Rows.Add(row);
DS.Tables.Add(table);
 
return DS;
}



}
}
}
 
///program.cs
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
 
namespace BeginExcel
{
    static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new RzExcel());
        }
    }
}
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
 
namespace BeginExcel
{
public partial class RzExcel : Form
{
public RzExcel()
{
InitializeComponent();
}
 
object misValue = System.Reflection.Missing.Value;
ExcelBeginner.Query objQuery = new ExcelBeginner.Query();

public void CreatExcelFromDB(string sqlquery, string Connstr, string FileName)
{
 
SqlDataAdapter DA = new SqlDataAdapter(sqlquery, Connstr);
DataSet DS = new DataSet();
DA.Fill(DS);
CreateExcel(DS, FileName);
}
public void CreatExcelFromDS(DataSet DS, string FileName)
{
CreateExcel(DS, FileName);
}
public DataSet ExcelToDT(string _Location)
{
DataSet ds = new DataSet();
var excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", _Location);
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection();
connection.ConnectionString = excelConnectionString;
DataTable sheets = GetSchemaTable(excelConnectionString);
 
foreach (DataRow r in sheets.Rows)
{
string query = "SELECT * FROM [" + r.ItemArray[0].ToString() + "]";
ds.Clear();
System.Data.OleDb.OleDbDataAdapter data = new System.Data.OleDb.OleDbDataAdapter(query, connection);
data.Fill(ds);
 
}
 
return ds;
}
 



private static void WriteArray(int rows, int columns, Excel.Worksheet worksheet)
{
var data = new object[rows, columns];
for (var row = 1; row <= rows; row++)
{
for (var column = 1; column <= columns; column++)
{
data[row - 1, column - 1] = "Test";
}
}
 
var startCell = (Excel.Range )worksheet.Cells[1, 1];
var endCell = (Excel.Range )worksheet.Cells[rows, columns];
var writeRange = worksheet.Range[startCell, endCell];

writeRange.Value2 = data;
}
static DataTable GetSchemaTable(string connectionString)
{
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
{
connection.Open();
 
DataTable schemaTable = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
return schemaTable;
}
}
private void CreateExcel(DataSet DS,string _FileName)
{

Excel.Application xlApp;
Excel.Workbook xlworkBook;
Excel.Range range;
 

object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
xlworkBook = xlApp.Workbooks.Add(misValue);
Excel.Worksheet xlworkSheet = xlworkBook.ActiveSheet as Excel.Worksheet;
int RRow = 0;
string[,] tempArray = new string[DS.Tables[0].Rows.Count,DS.Tables[0].Columns.Count];
try
{
for (int i = 1; i <= (DS.Tables[0].Rows.Count / 65536 )+ 1; i++)
{
xlworkSheet = (Excel.Worksheet)xlworkBook.Worksheets.get_Item(i);
range = null;
range = xlworkSheet.get_Range("A1", misValue);
range = range.get_Resize(DS.Tables[0].Rows.Count, DS.Tables[0].Columns.Count);
 
if (65536 * i < DS.Tables[0].Rows.Count)
{
if (i <= 1)
{
for (RRow = RRow; RRow <= 65536 * i - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[RRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
}
}
 
range.set_Value(System.Reflection.Missing.Value, tempArray);
//xlworkBook.SaveAs("Myfile.xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
Array.Clear(tempArray, 0, tempArray.Length);
tempArray = new string[DS.Tables[0].Rows.Count, DS.Tables[0].Columns.Count];
 
}
else
{
int tempRow = 0;
for (RRow = RRow; RRow < DS.Tables[0].Rows.Count - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[tempRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
 
}
tempRow++;
}
range.set_Value(System.Reflection.Missing.Value, tempArray);
xlworkBook.SaveAs( _FileName +".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}
}
else
{
int tempRow = 0;
for (RRow = RRow; RRow < DS.Tables[0].Rows.Count - 1; ++RRow)
{
for (int RColm = 0; RColm < DS.Tables[0].Columns.Count - 1; RColm++)
{
tempArray[tempRow, RColm] = DS.Tables[0].Rows[RRow].ItemArray[RColm].ToString();
}
tempRow++;
}
}
range.set_Value(System.Reflection.Missing.Value, tempArray);
xlworkBook.SaveAs(_FileName + ".xlsx", Excel.XlFileFormat.xlXMLSpreadsheet, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
}

}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}

xlApp.UserControl = true;
//Process pro = Process.Start(Application.StartupPath );

}
 
private void RzExcel_Load(object sender, EventArgs e)
{
DataSet DS = new DataSet();
DataTable DT = new DataTable();
/// Columns Created
DT =CreateReportColumns("Col1", "Col2", "Col3", "Col4", "Col5", "Col6");
// DT = AddNEwRow(DS.Tables[0], "Value1","Value2","Value3","Value4","Value5","Value6");
DT = AddNEwRow(DT, "Value1", "Value2", "Value3", "Value4", "Value5", "Value6");
}
public DataTable CreateReportColumns( params string[] values)
{
 
DataSet DS = new DataSet();
DataTable DT = new DataTable();
//DS.Tables.Add(MakeDataTable( values));

DT = MakeDataTable(values);
return DT;
 
}
 
private DataTable MakeDataTable(string[] Values)
{
DataTable _DT = new DataTable();
DataColumn DC = default(DataColumn);
try
{
_DT = new DataTable();
 
for (int i = 0; i < Values.Length; i++)
{
DC = new DataColumn();
DC.ColumnName = Values[i].ToString();
DC.DataType = Type.GetType("System.String");
_DT.Columns.Add(DC);
 
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return _DT;
}
 
private DataTable AddNEwRow(DataTable DT, params string[] Values )
{
DataSet DS = new DataSet();

 
DataRow row;
row = DT.NewRow();
int _ColumnCount = DT.Columns.Count;
int _ValueCount = Values.Length;
 
if (_ValueCount == _ColumnCount)
{
for (int i = 0; i < Values.Length; i++)
{
row[DT.Columns[i].ToString()] = Values[i].ToString();
}
DT.Rows.Add(row);
 
DS.Tables.Add(DT);
 

}
else
{


}
 
return DT;

}
}
}
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 19 Nov 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100