Not all rows exported in csv file from excel file?
I am trying to make small program which reads excel file and write single column data of excel file to csv file. All things going on perfectly , dgv in form also shows all rows, but when i check csv file I found that not all rows copied. There are nearly 6000 rows in excel file but when data copied in csv file there are only 5706 rows last some rows are not copied why it happend , needs sugessions .thank you!
here is the code for read data from excel file,
public void excel()
{
StreamReader sr = new StreamReader("C:\\Users\\Priya\\Desktop\\k.txt");
string s = sr.ReadLine();
OleDbConnection con = new OleDbConnection(
"provider=Microsoft.Jet.OLEDB.4.0;data source='"+s+"'"
+ ";Extended Properties=Excel 8.0;");
StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT Product FROM [Sheet1$]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);
DataSet dsXLS = new DataSet();
adp.Fill(dsXLS);
DataView dvEmp = new DataView(dsXLS.Tables[0]);
dataGridView1.DataSource = dvEmp;
this.Hide();
}
here is the method for export data,
public void writeCSV(DataGridView gridIn, string outputFile)
{
try
{
//test to see if the DataGridView has any rows
if (gridIn.RowCount > 0)
{
//string value = "";
DataGridViewRow dr = new DataGridViewRow();
StreamWriter swOut = new StreamWriter(outputFile);
//write header rows to csv
for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
{
if (i > 0)
{
swOut.Write(",");
}
swOut.Write(gridIn.Columns[i].HeaderText);
}
swOut.WriteLine();
//write DataGridView rows to csv
for (int j = 0; j <= gridIn.Rows.Count - 1; j++)
{
if (j > 0)
{
swOut.WriteLine();
}
dr = gridIn.Rows[j];
for (int i = 0; i <= gridIn.Columns.Count - 1; i++)
{
if (i > 0)
{
swOut.Write(",");
}
string value = " ";
value = dr.Cells[i].Value.ToString();
//replace comma's with spaces
value = value.Replace(',', ' ');
//replace embedded newlines with spaces
value = value.Replace(Environment.NewLine, " ");
swOut.Write(value);
}
}
swOut.Close();
}
}
catch { }
}
