Click here to Skip to main content
15,901,035 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to
C#
Select Random Rows of Excel file and delete those random rows by using C#
Posted
Comments
OriginalGriff 20-Dec-15 6:59am    
What have you tried?
Where are you stuck?
What help do you need?
Mohammadsadiq994 22-Dec-15 8:34am    
protected void btnrandom_Click(object sender, EventArgs e)
{
Excel.Range[] rows = RandomRows(2, @"C:\test\Iris.xls");

DataTable dt = new DataTable();

bool ColumnsCreated = false;

foreach (Excel.Range row in rows)
{
object[,] values = row.Value;

int columnCount = values.Length;

if (!ColumnsCreated)
{
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn(String.Format("Column {0}", i));
dt.Columns.Add(dc);
ColumnsCreated = true;
}
}

DataRow dr = dt.NewRow();

for (int i = 0; i < columnCount; i++)
{
dr[String.Format("Column {0}", i)] = values[1, i + 1];
}

dt.Rows.Add(dr);
grdrandom.DataSource = dt;
}

}
private Excel.Range[] RandomRows(int randomRowsToGet, string worksheetLocation, int worksheetNumber = 1, int lowestRow = 0, int highestRow = 1000)
{

Excel.Range[] rows = new Excel.Range[randomRowsToGet];

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(worksheetLocation);
Excel.Worksheet worksheet = workbook.Worksheets[worksheetNumber];

List<int> rowNumbers = new List<int>();

bool allUniqueNumbers = false;

Random random = new Random();

while (!allUniqueNumbers)
{
int nextNumber = random.Next(lowestRow, highestRow);

if (!rowNumbers.Contains(nextNumber))
rowNumbers.Add(nextNumber);

if (rowNumbers.Count == randomRowsToGet)
allUniqueNumbers = true;
}

for (int i = 0; i < randomRowsToGet; i++)
{
rows[i] = worksheet.UsedRange.Rows[rowNumbers[i]];
}

Marshal.ReleaseComObject(excel);

return rows;
}


I have tried this i have stuck the code is randomly reading the rows of excel file but it is not showing it to the gridview thanks
Richard MacCutchan 20-Dec-15 8:28am    
OK, I did that and now my spreadsheet is a mess.
Kornfeld Eliyahu Peter 20-Dec-15 9:22am    
If you do it long enough you will have a plain spreadsheet - no messy at all :-)
Dave Kreskowiak 20-Dec-15 10:29am    
That's nice. You have our permission to proceed with this little project.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900