Three Methods to Read Excel with ASP.NET RSS

• • Reply
  

  S.Green

  Member

  2 Points

  3 Posts

  Three Methods to Read Excel with ASP.NET

  Sep 13, 2011 04:00 AM|LINK

  Introduction

   

  Recently, I conclude three methods about how to read Excel with ASP.NET more efficiently and I will show it to you.

   

  Methods to Read Excel

   

  1. Use OleDB to Read Excel

   

  Take Excel file as data source to get data.

   

  Code as following:

  public DataSet ExcelToDS(string Path)
  {
  string strConn = "Provider=
  Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";
  "+"Extended Properties=Excel 8.0;";
  OleDbConnection conn = new OleDbConnection(strConn);
  conn.Open();
  string strExcel = "";
  OleDbDataAdapter myCommand = null;
  DataSet ds = null;
  strExcel="select * from [sheet1$]";
  myCommand = new OleDbDataAdapter(strExcel, strConn);
  ds = new DataSet();
  myCommand.Fill(ds,"table1");
  return ds;
  }

  By using the following method, we can get Excel worksheet, sheet([sheet1$]) if it is not fixed.

  string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
  +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
  OleDbConnection conn = new OleDbConnection(strConn);
  DataTable schemaTable = objConn.GetOleDbSchemaTable
  (System.Data.OleDb.OleDbSchemaGuid.Tables,null);
  string tableName=schemaTable.Rows[0][2].ToString().Trim(); 
  
  
  

  Also, we can write data in Excel.

  public void DSToExcel(string Path,DataSet oldds)
  {
  //Get DataSet which is to collect Excel. Focus on getting Excel structure in DataSet.
  string strCon = " Provider = 
  Microsoft.Jet.OLEDB.4.0 ; Data Source =
  "+path1+";Extended Properties=Excel 8.0" ;
  OleDbConnection myConn = new OleDbConnection(strCon) ;
  string strCom="select * from [Sheet1$]";
  myConn.Open ( ) ;
  OleDbDataAdapter myCommand = 
  new OleDbDataAdapter ( strCom, myConn ) ;
  ystem.Data.OleDb.OleDbCommandBuilder builder=
  new OleDbCommandBuilder(myCommand);
  //Use QuotePrefix and QuoteSuffix when generating InsertComment for builder.
  builder.QuotePrefix="[";     //Get Metacharactor in insert sentence (initial position).
  builder.QuoteSuffix="]"; //Get Metacharactor in insert sentence (end position).
  DataSet newds=new DataSet();
  myCommand.Fill(newds ,"Table1") ;
  for(int i=0;i<oldds.Tables[0].Rows.Count;i++)
  {
    //We cannot use ImportRow method to import one row to news because ImportRow remains the original settings of DataRow and DataRowState is not changed. 
  //After using ImprotRow, we can get value in news but it cannot be updated to Excel because of all the imported rows DataRowState!=Added
  
     DataRow nrow=aDataSet.Tables["Table1"].NewRow();
    for(int j=0;j<newds.Tables[0].Columns.Count;j++)
     {
      nrow[j]=oldds.Tables[0].Rows[i][j];
     }
     newds.Tables["Table1"].Rows.Add(nrow);
  }
  myCommand.Update(newds,"Table1");
  myConn.Close();
  }  
  

  2. Add com as Reference: Microsoft.Office.Interop.Excel.dll

   

  Firstly, we need to get Excel.dll and copy Excel.ext to bin category of DotNet, cmb to this category. Then, run TlbUmp EXCEL.EXE Excel.dll to get dll file. Next, add dll file in project.

  //Read Excel Method (Read data with range)
  
  private void OpenExcel(string strFileName)
  {
       object missing = System.Reflection.Missing.Value;
       Application excel = new Application();//lauch excel application
      if (excel == null)
       {
           Response.Write("<script>alert('Can't access excel')</script>");
       }
      else
       {
           excel.Visible = false;   excel.UserControl = true;
          // Open one Excel file with read only. 
           Workbook wb = excel.Application.Workbooks.
  Open(strFileName, missing, true, missing, missing, missing,
            missing, missing, missing, true, missing, missing,
  missing, missing, missing);
          //Get the first workbook.
           Worksheet ws = (Worksheet)wb.Worksheets.get_Item(1);
          //Get all record rows, including title column.
          int rowsint = ws.UsedRange.Cells.Rows.Count; //Get amount of rows.
          //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//Get amount of columns.
          //Get data range, not including title column.
           Range rng1 = ws.Cells.get_Range("B2", "B" + rowsint);   //item
           Range rng2 = ws.Cells.get_Range("K2", "K" + rowsint);  //Customer
           object[,] arryItem= (object[,])rng1.Value2;   //get range's value
           object[,] arryCus = (object[,])rng2.Value2;   
          //Assign the new value to one array.
           string[,] arry = new string[rowsint-1, 2];
          for (int i = 1; i <= rowsint-1; i++)
           {
              //Item_Code column
               arry[i - 1, 0] =arryItem[i, 1].ToString();
              //Customer_Name column
               arry[i - 1, 1] = arryCus[i, 1].ToString();
           }
           Response.Write(arry[0, 0] + "   /   " + arry[0, 1] + "#" 
  + arry[rowsint - 2, 0] + "   /   " + arry[rowsint - 2, 1]);
       }
        excel.Quit();   excel = null;
       Process[] procs = Process.GetProcessesByName("excel");
       foreach (Process pro in procs)
       {
           pro.Kill();//Kill process.
       }
       GC.Collect();
  }
  
  

  3. Convert Excel to CSV and Use File Stream to Read

   

  Add namespace using System.Text and using System.IO as reference.

             FileStream fs = new FileStream("d:\\Customer.csv",
  FileMode.Open, FileAccess.Read, FileShare.None);
             StreamReader sr = new StreamReader
  (fs, System.Text.Encoding.GetEncoding(936));
            string str = "";
            string s = Console.ReadLine();
            while (str != null)
             {     str = sr.ReadLine();
                 string[] xu = new String[2];
                  xu = str.Split(',');
                 string ser = xu[0];
                 string dse = xu[1];                if (ser == s)
                  {   Console.WriteLine(dse);break;
                  }
             }    sr.Close();
  
  
  

  Also, we can import data in database to one txt file.

   

  Code as following:

  //txt file name
  string fn = DateTime.Now.ToString
  ("yyyyMMddHHmmss") + "-" + "PO014" + ".txt";
  OleDbConnection con = new OleDbConnection(conStr);
  con.Open();
  string sql = "select  
  ITEM,REQD_DATE,QTY,PUR_FLG,PO_NUM from TSD_PO014";        
  /OleDbCommand mycom =
  new OleDbCommand("select * from TSD_PO014", mycon);
  //OleDbDataReader myreader = 
  mycom.ExecuteReader();   //We can also use Reader to read data.
  DataSet ds = new DataSet();
  OleDbDataAdapter oda = new OleDbDataAdapter(sql, con);
  oda.Fill(ds, "PO014");
  DataTable dt = ds.Tables[0];
  FileStream fs = new FileStream(Server.MapPath
  ("download/" + fn), FileMode.Create, FileAccess.ReadWrite);
  StreamWriter strmWriter = new StreamWriter(fs);    //Save to txt file.
  //Write title to .txt file.
  //for (int i = 0; i <dt.Columns.Count;i++)
  //{
  //     strmWriter.Write(dt.Columns[i].ColumnName + "   ");
  //}
  foreach (DataRow dr in dt.Rows)
  {
      string str0, str1, str2, str3;
      string str = "|";  //Separate data with "|".
       str0 = dr[0].ToString();
       str1 = dr[1].ToString();
       str2 = dr[2].ToString();
       str3 = dr[3].ToString();
       str4 = dr[4].ToString().Trim();
       strmWriter.Write(str0);
       strmWriter.Write(str);
       strmWriter.Write(str1);
       strmWriter.Write(str);
       strmWriter.Write(str2);
       strmWriter.Write(str);
       strmWriter.Write(str3);
       strmWriter.WriteLine();  //New line.
  }
  strmWriter.Flush();
  strmWriter.Close();
  if (con.State == ConnectionState.Open)
  {
       con.Close();
  }
  

  I hope that the three methods can be helpful for you! Thanks.

   

  My another tip in Codeproject about ASP.NET recommend: Manipulate Excel with ASP.NET.