如何将所有行数据导出到csv文件？ [英] How to export all rows data to csv file?

问题描述

编辑：



我正在尝试制作读取excel文件的小程序并将excel文件的单列数据写入csv文件。所有事情都很完美，dgv在表单中也显示了所有行，但是当我检查csv文件时，我发现并非所有行都被复制。 excel文件中有近6000行，但是当在csv文件中复制数据时，只有5706行最后一些行没有复制错误发现对象引用无法设置为对象实例，下面是粗体代码行，需要sugessions。



谢谢！



这里是从excel文件读取数据的代码，

Edited:

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 error found that is "object reference could not set to the instance of object" at following bold line of code , needs sugessions .

thank you!

here is the code for read data from excel file,

public void excel()
{
 StreamReader sr = new StreamReader("C:\\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;
  }

这里是导出数据的方法

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();// error line
          //replace comma's with spaces
          value = value.Replace(',', ' ');
          //replace embedded newlines with spaces
         value = value.Replace(Environment.NewLine, " ");

          swOut.Write(value);
          }
        }
         swOut.Flush();
         swOut.Close();
        }
      }
      catch { }
     }

推荐答案

）;
OleDbDataAdapter adp = new OleDbDataAdapter（stbQuery.ToString（），con）;

DataSet dsXLS = new DataSet（）;
adp.Fill（dsXLS）;

DataView dvEmp = new DataView（dsXLS.Tables [ 0 ]）;

dataGridView1 .DataSource = dvEmp;
}

"); OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con); DataSet dsXLS = new DataSet(); adp.Fill(dsXLS); DataView dvEmp = new DataView(dsXLS.Tables[0]); dataGridView1.DataSource = dvEmp; }

这里是导出数据的方法

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();// error line
          //replace comma's with spaces
          value = value.Replace(',', ' ');
          //replace embedded newlines with spaces
         value = value.Replace(Environment.NewLine, " ");

          swOut.Write(value);
          }
        }
         swOut.Flush();
         swOut.Close();
        }
      }
      catch { }
     }

检查它是否为空值。

如果单元格包含null - 如果数据源为null则完全有效 - 那么当您尝试调用ToString时，您将收到错误。

所以替换它：

Check it for null values.
If the cell contains a null - which is perfectly valid if the datasource is a null - then when you try to call ToString you will get an error.
So replace this:

value = dr.Cells[i].Value.ToString();

这个：

with this:

value = (dr.Cells[i].Value ?? "").ToString();

哪个会用空字符串替换空值。

Which will replace null values with an empty string.

这篇关于如何将所有行数据导出到csv文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！