Don't like ads? PRO users don't see any ads ;-)
Guest

Untitled

By: a guest on Aug 10th, 2012  |  syntax: None  |  size: 1.91 KB  |  hits: 6  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How to make the GetExcel function fast?
  2. public DataSet GetExcel(string fileName)
  3. {
  4.     Application oXL;
  5.     Workbook oWB;
  6.     Worksheet oSheet;
  7.     Range oRng;
  8.     try
  9.     {
  10.         //  creat a Application object
  11.         oXL = new ApplicationClass();
  12.         //   get   WorkBook  object
  13.         oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
  14.                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
  15.                 Missing.Value, Missing.Value);
  16.  
  17.  
  18.         //   get   WorkSheet object
  19.         oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
  20.         System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
  21.         DataSet ds = new DataSet();
  22.         ds.Tables.Add(dt);
  23.         DataRow dr;
  24.  
  25.  
  26.         StringBuilder sb = new StringBuilder();
  27.         int jValue = oSheet.UsedRange.Cells.Columns.Count;
  28.         int iValue = oSheet.UsedRange.Cells.Rows.Count;
  29.         //  get data columns
  30.         for (int j = 1; j <= jValue; j++)
  31.         {
  32.             dt.Columns.Add("column" + j, System.Type.GetType("System.String"));
  33.         }
  34.  
  35.  
  36.         //string colString = sb.ToString().Trim();
  37.         //string[] colArray = colString.Split(':');
  38.  
  39.  
  40.         //  get data in cell
  41.         for (int i = 1; i <= iValue; i++)
  42.         {
  43.             dr = ds.Tables["dtExcel"].NewRow();
  44.             for (int j = 1; j <= jValue; j++)
  45.             {
  46.                 oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
  47.                 string strValue = oRng.Text.ToString();
  48.                 dr["column" + j] = strValue;
  49.             }
  50.             ds.Tables["dtExcel"].Rows.Add(dr);
  51.         }
  52.         oWB.Close();
  53.         return ds;
  54.     }
  55.     catch (Exception ex)
  56.     {
  57.         AlertMessage(String.Format("Error: {0}", ex.Message.ToString()));
  58.         return null;
  59.     }
  60.     finally
  61.     {
  62.         Dispose();
  63.     }
  64. }