.NET中的CSV导入导出

导入代码，从csv文件得到datatable

         ///   <summary>
         ///  Get Data From Csv File 
         ///  (Through StreamReader)
         ///   </summary>
         ///   <returns></returns>
         private   bool  GetData(Stream inputStream,  out   string  errMessage,  out  DataTable dtFile)
        {
            errMessage  =  String.Empty;
            dtFile  =   new  DataTable();


             // this.FileUploadImport.PostedFile.InputStream.CanSeek;
             // StreamReader sr = new StreamReader(this.FileUploadImport.PostedFile.InputStream);  // update by hwx 11/12/2010
            StreamReader sr  =   new  StreamReader(inputStream);
             int  iColumnCount  =   19 ; // the column count in the file
             int  iRow  =   1 ; //  the row number is being read
             int  iColumn  =   0 ; //  the column number is being read
             string  strStandardTitle  =   string .Empty;  //  the title as it is

             // read title row
             string  strTitle  =  sr.ReadLine();

             // string[] strRowTitle = strTitle.Split(new char[] { ',' });
             string [] strRowTitle  =   new   string [iColumnCount];
             string  strCharTitle;
             int  iCellNumberTitle  =   0 ;
             bool  blnQuoteTitle  =   false ;
             for  ( int  i  =   0 ; i  <  strTitle.Length; i ++ )
            {
                strCharTitle  =  strTitle.Substring(i,  1 );
                 if  (strCharTitle  ==   " , " ) //  "," is the seperation symbol of csv file,
                {
                     if  ( ! blnQuoteTitle)
                    {
                        iCellNumberTitle ++ ; //  out of the "" range, "," is the seperation symbol
                         if  (iCellNumberTitle  >=  iColumnCount) //  too many column in this line
                        {
                             break ;
                        }
                    }
                     else
                    {
                        strRowTitle[iCellNumberTitle]  +=  strCharTitle;
                    }
                }
                 else   if  (strCharTitle  ==   " \ "" )//  " \ ""   is  the transfer symbol of csv file,
                {
                    blnQuoteTitle  =   ! blnQuoteTitle;
                     if  (blnQuoteTitle  &&  i  >   0   &&  strTitle.Substring(i  -   1 ,  1 )  ==   " \ "" )//in the  ""  range and there is an transfer symbol before
                    {
                        strRowTitle[iCellNumberTitle]  +=  strCharTitle;
                    }
                }
                 else
                {
                    strRowTitle[iCellNumberTitle]  +=  strCharTitle;
                }
            }
             // read the content
             if  (strRowTitle.Length  ==  iColumnCount)
            {
                 foreach  ( string  strCell  in  strRowTitle)
                {
                    iColumn ++ ;
                     if  (strCell.Trim()  !=   string .Empty)
                    {
                        dtFile.Columns.Add(strCell); // add new column with name to the data table

                    }
                     else   // file error:blank title
                    {

                        errMessage  +=   " The cell  "   +  iColumn.ToString()  +   "  is blank in the header row.\r\n " ;
                    }
                }
                 if  (dtFile.Columns.Count  ==  iColumnCount) // make sure that no blank header or error header
                {
                     // read content row
                     string  strLine;
                     while  ( ! sr.EndOfStream)
                    {
                        iRow ++ ;
                        iColumn  =   0 ;
                        DataRow dr  =  dtFile.NewRow();
                        strLine  =  sr.ReadLine();

                         // read csv file line by line
                         string [] strRow  =   new   string [iColumnCount];
                         string  strChar;
                         int  iCellNumber  =   0 ;
                         bool  blnQuote  =   false ; // whether in the "" range
                         for  ( int  i  =   0 ; i  <  strLine.Length; i ++ )
                        {
                            strChar  =  strLine.Substring(i,  1 );
                             if  (strChar  ==   " , " ) //  "," is the seperation symbol of csv file,
                            {
                                 if  ( ! blnQuote)
                                {
                                    iCellNumber ++ ; //  out of the "" range, "," is the seperation symbol
                                     if  (iCellNumber  >=  iColumnCount) //  too many column in this line
                                    {
                                         break ;
                                    }
                                }
                                 else
                                {
                                    strRow[iCellNumber]  +=  strChar;
                                }
                            }
                             else   if  (strChar  ==   " \ "" )//  " \ ""   is  the transfer symbol of csv file,
                            {
                                blnQuote  =   ! blnQuote;
                                 if  (blnQuote  &&  i  >   0   &&  strLine.Substring(i  -   1 ,  1 )  ==   " \ "" )//in the  ""  range and there is an transfer symbol before
                                {
                                    strRow[iCellNumber]  +=  strChar;
                                }
                            }
                             else
                            {
                                strRow[iCellNumber]  +=  strChar;
                            }
                        }
                         if  (iCellNumber  +   1   ==  iColumnCount)
                        {
                             foreach  ( string  strCell  in  strRow)
                            {
                                iColumn ++ ;
                                 if  (strCell  !=   null   &&  strCell.Trim()  !=   string .Empty)
                                {
                                    dr[strRowTitle[iColumn  -   1 ]]  =  strCell.Trim();
                                }
                                 else // file error:blank cell
                                {
                                    dr[strRowTitle[iColumn  -   1 ]]  =  String.Empty;
                                     // errMessage += "The column \"" + strRowTitle[iColumn - 1] + "\" is blank in row " + iRow.ToString() + ".\r\n";
                                }
                            }
                        }
                         else //  file error:the column count of current row do not equal to title's
                        {
                            errMessage  +=   " There are more or less cells than title row in the row  "   +  iRow.ToString()  +   " .\r\n " ;
                        }
                        dtFile.Rows.Add(dr);
                    }
                }
            }
             else   // file error:the count of columns in the file don't equal it should be
            {
                errMessage  +=   " There are an incorrect number of columns in the header row compared to the template file.\r\n " ;
            }
            sr.Close();
            sr.Dispose();

            errMessage  =  errMessage.Replace( " \r\n " ,  " <br> " );
             return  errMessage  ==  String.Empty  ?   true  :  false ;
        }


         ///   <summary>
         ///  Get dataset from csv file.
         ///   </summary>
         ///   <param name="filepath"></param>
         ///   <param name="filename"></param>
         ///   <returns> Data Set </returns>
         private  DataSet GetDatasetFromCsv( string  filepath,  string  filename)
        {
             string  strconn  =   @" driver={microsoft text driver (*.txt; *.csv)};dbq= " ;
            strconn  +=  filepath;                                                         // filepath, for example: c:\
            strconn  +=   " ;extensions=asc,csv,tab,txt; " ;
            OdbcConnection objconn  =   new  OdbcConnection(strconn);
            DataSet dscsv  =   new  DataSet();
             try
            {
                 string  strsql  =   " select * from  "   +  filename;                      // filename, for example: 1.csv
                OdbcDataAdapter odbccsvdataadapter  =   new  OdbcDataAdapter(strsql, objconn);

                odbccsvdataadapter.Fill(dscsv);
                 return  dscsv;
            }
             catch  (Exception ex)
            {
                 throw  ex;
            }
        }

 

csv导出代码

         ///   <summary>
         ///  Export to Csv File from dataset
         ///   </summary>
         ///   <param name="src"></param>
         ///   <param name="folderName"> folderName </param>
         ///   <param name="strFileName"> strFileName </param>
         ///   <returns></returns>
         public   bool  ExportToCsv(DataSet src,  string  folderName,  string  strFileName)
        {
             string  csv  =  String.Empty;

            StreamWriter writer  =   null ;

             string  fileName  =  Server.MapPath( " / " )  +  folderName  +   " \\ "   +  strFileName;

             try
            {
                 if  (src  ==   null   ||  src.Tables.Count  ==   0 )  throw   new  Exception( " dataset is null or has not table in dataset " );


                 for  ( int  i  =   0 ; i  <  src.Tables.Count; i ++ )
                {

                     if  (i  >   0 )

                        fileName  =  fileName.Substring( 0 , fileName.IndexOf( ' . ' ))  +  i  +  fileName.Substring(fileName.IndexOf( " . " ));



                    writer  =   new  StreamWriter(fileName);

                    DataTable dt  =  src.Tables[i];

                    StringBuilder sb  =   new  StringBuilder();

                     for  ( int  j  =   0 ; j  <  dt.Columns.Count; j ++ )
                    {

                         string  colName  =  dt.Columns[j].ColumnName;

                         if  (colName.IndexOf( ' , ' )  >   - 1 )

                            colName  =  colName.Insert( 0 ,  " \ "" ).Insert(colName.Length + 1,  " \ "" );

                        sb.Append(colName);

                         if  ( ! colName.Equals( "" ))

                             if  (j  !=  dt.Columns.Count  -   1 )

                                sb.Append( " , " );

                    }

                    writer.WriteLine(sb.ToString());

                    sb  =   new  StringBuilder();

                     string  temp  =   "" ;

                     for  ( int  j  =   0 ; j  <  dt.Rows.Count; j ++ )
                    {

                        DataRow dr  =  dt.Rows[j];

                         for  ( int  k  =   0 ; k  <  dt.Columns.Count; k ++ )
                        {

                             object  o  =  dr[k];

                             if  (o  !=   null )

                                temp  =  o.ToString();

                             if  (temp.IndexOf( ' , ' )  >   - 1 )

                                temp  =  temp.Insert( 0 ,  " \ "" ).Insert(temp.Length + 1,  " \ "" );

                            sb.Append(temp);

                             if  (k  !=  dt.Columns.Count  -   1 )

                                sb.Append( " , " );

                        }

                        writer.WriteLine(sb.ToString());

                        sb  =   new  StringBuilder();
                        csv  =  sb.ToString();
                    }

                    writer.Close();

                }

                 string  strFilePath  =  Server.MapPath( " / " )  +  folderName;
                 if  ( ! Directory.Exists(strFilePath))
                {
                    Directory.CreateDirectory(strFilePath);
                }

                strFullFileName  =  Server.MapPath( " / " )  +  folderName  +   " \\ "   +  fileName;
                 // FullFileName = Server.MapPath(FileName);
                 // FileName
                FileInfo DownloadFile  =   new  FileInfo(strFullFileName);
                 if  (DownloadFile.Exists)
                {
                    Response.Clear();
                    Response.ClearHeaders();
                    Response.Buffer  =   false ;
                    Response.ContentType  =   " application/octet-stream " ;
                     // Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));
                    Response.AppendHeader( " Content-Disposition " ,  " attachment;filename= "   +  HttpUtility.UrlEncode(fileName, System.Text.Encoding.ASCII));
                    Response.AppendHeader( " Content-Length " , DownloadFile.Length.ToString());
                    Response.WriteFile(DownloadFile.FullName);
                    Response.Flush();
                     // Response.End();
                }
                 else
                {
                     // not exist
                     throw   new  Exception( " Export csv file does not exist! " );
                }


            }

             catch  (Exception ex)
            {

                 throw   new  Exception( " Save csv error " , ex);

            }

             finally
            {

                 if  (writer  !=   null ) writer.Close();

            }

             return   true ;

        }

         ///   <summary>
         ///  List to DataTable
         ///   </summary>
         ///   <param name="entitys"> entitys list </param>
         ///   <returns></returns>
         public  DataTable ListToDataTable(List < T >  entitys)
        {

             //
             if  (entitys  ==   null   ||  entitys.Count  <   1 )
            {
                 throw   new  Exception( " list is null " );
            }

             // get first Propertie
            Type entityType  =  entitys[ 0 ].GetType();
            PropertyInfo[] entityProperties  =  entityType.GetProperties();

             // DataTable structure
             //
            DataTable dt  =   new  DataTable();
             for  ( int  i  =   0 ; i  <  entityProperties.Length; i ++ )
            {
                 // dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }

             // add entity to DataTable
             foreach  ( object  entity  in  entitys)
            {
                 // check type
                 if  (entity.GetType()  !=  entityType)
                {
                     throw   new  Exception( " type not same " );
                }
                 object [] entityValues  =   new   object [entityProperties.Length];
                 for  ( int  i  =   0 ; i  <  entityProperties.Length; i ++ )
                {
                    entityValues[i]  =  entityProperties[i].GetValue(entity,  null );

                }
                dt.Rows.Add(entityValues);
            }
             return  dt;
        }

 

 

转载于:https://www.cnblogs.com/hwx0807/archive/2010/12/21/1912625.html