code/Data/Excel.cs

Go to the documentation of this file.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Sylloge.Data
{
   public static class Excel
   {
      #region Events

      // Add any custom events here

      #endregion

      #region Constants

      public const string DEFAULT_EXCELNAME = "default.xls";

      #endregion

      #region Local Attributes

      private static OleDbDataAdapter Adapter { get; set; }
      private static OleDbConnection Conn { get; set; }
      public static DataSet Dataset { get; set; }
      private static bool IsOpen { get; set; }
      private static DataTable Table { get; set; }

      #endregion

      #region Class Methods and Properties

      public static void Dispose()
      {
         Excel.Close();
      }

      private static void CreateNewInstances()
      {
         Excel.Adapter = new OleDbDataAdapter();
         Excel.Conn = new OleDbConnection();
         Excel.Adapter.DeleteCommand = new OleDbCommand();
         Excel.Adapter.InsertCommand = new OleDbCommand();
         Excel.Adapter.SelectCommand = new OleDbCommand();
         Excel.Adapter.UpdateCommand = new OleDbCommand();
         Excel.Dataset = new DataSet();
         Excel.Table = new DataTable();
      }

      public static void Close()
      {
         if (Conn != null) { Conn.Close(); Conn.Dispose(); }
         if (Adapter != null) { Adapter.Dispose(); }
         if (Dataset != null) { Dataset.Dispose(); }
         if (Table != null) { Table.Dispose(); }
         Adapter = null;
         Dataset = null;
         Conn = null;
         Table = null;
         IsOpen = false;
      }
      
      public static DataTable GetDataFromFile(string fileName)
      {
         return Excel.GetDataFromFile(fileName, true, 0);
      }

      public static DataTable GetDataFromFile(string fileName, int activeSheetIndex)
      {
         return Excel.GetDataFromFile(fileName, false, activeSheetIndex);
      }

      public static DataTable GetDataFromFile(string fileName, bool isCSV, int activeSheetIndex)
      {
         DataTable Data = null;
         if (isCSV) {
            Data = new DataTable("CSV");
            System.IO.FileStream FStream = null;
            try {
               FStream = new System.IO.FileStream(fileName, System.IO.FileMode.Open, System.IO.FileAccess.ReadWrite, System.IO.FileShare.ReadWrite);
            } catch (Exception ex) {
               string ErrorString = "There was an error trying to open the selected file. This can " +
                  "happen when the selected file is already open, please ensure the file is not opened " +
                  "in any other applications (like Excel)" + Environment.NewLine + "Error: " + ex.Message;
               Sylloge.App.Out(ErrorString);
               return null;
            }
            System.IO.StreamReader Reader = new System.IO.StreamReader(FStream);
            string[] Headers = Reader.ReadLine().Split(',');
            foreach (string Header in Headers) {
               Data.Columns.Add(Header);
               Application.DoEvents();
            }
            string[] Values = null;
            while (!Reader.EndOfStream) {
               Values = Reader.ReadLine().Split(',');
               if (Values.Length > Data.Columns.Count) {
                  for (int i = Data.Columns.Count; i < Values.Length; i++) {
                     Data.Columns.Add(new DataColumn(("column" + i.ToString("00"))));
                     Application.DoEvents();
                  }
               }
               Data.Rows.Add(Values);
               Application.DoEvents();
            }
            Reader.Close();
            FStream.Close();
         } else {
            if (Excel.Open(fileName)) {
               DataTable[] NewData = Excel.GetAll();
               Excel.Close(); // Once we have the data, we don't need the Excel object any more
               Data = NewData[activeSheetIndex]; // Index of 0 says first sheet
               string TableName = (new System.IO.FileInfo(fileName)).Name;
               Data.TableName = TableName.Substring(0, TableName.LastIndexOf('.'));
            } else {
               Sylloge.App.Out("There was an error trying to get the data from the file.");
            }
         }
         return Data;
      }
          
      public static string Name { get; set; }

      private static bool False()
      {
         Table.Clear();
         Dataset.Clear();
         return false;
      }

      public static bool Open()
      {
         return Excel.Open(Excel.Name, true);
      }
          
      public static bool Open(string fileName)
      {
         return Excel.Open(fileName, true);
      }

      public static bool Open(string fileName, bool hasHeaders)
      {
         Excel.Close();
         Excel.CreateNewInstances();
         Excel.Name = fileName;
         try {
            string Ver = "8.0";
            string Provider = "Microsoft.Jet.OLEDB.4.0";
            if ((new System.IO.FileInfo(fileName)).Extension.ToLower() == ".xlsx") {
               Provider = "Microsoft.ACE.OLEDB.12.0";
               Ver = "12.0 Xml"; 
            }
            Excel.Conn.ConnectionString = "Provider=" + Provider + ";Data Source=" + fileName + ";Extended Properties=\"Excel " + Ver + ";HDR=" + (hasHeaders ? "YES" : "NO") + ";\""; // Excel 8 = 97-03 (for backward compatibilty)
            Excel.Conn.Open();
            Excel.Adapter.DeleteCommand.Connection = Conn;
            Excel.Adapter.InsertCommand.Connection = Conn;
            Excel.Adapter.SelectCommand.Connection = Conn;
            Excel.Adapter.UpdateCommand.Connection = Conn;
            Excel.IsOpen = true;
         } catch (Exception ex) {
            // Reason for this could be invalid password
            Sylloge.App.Out("There was an error while trying to open the Excel file.", ex);
            Excel.IsOpen = false;
            return false;
         }
         return Excel.IsOpen;
      }

      public static long Size()
      {
         long Value = 0;
         try {
            if (System.IO.File.Exists(Excel.Name)) {
               Value = new System.IO.FileInfo(Excel.Name).Length;
            }
         } catch (Exception ex) {
            Console.WriteLine(ex.Message);
         }
         return Value;
      }

      #endregion

      #region Importing

      public static DataTable[] GetAll()
      {
         DataTable Tables = Excel.Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (Tables == null) { return null; }
         List<string> Sheets = new List<string>();
         foreach (DataRow Row in Tables.Rows) {
            Sheets.Add(Row["TABLE_NAME"].ToString());
         }
         List<DataTable> TableList = new List<DataTable>();
         foreach (string Table in Sheets) {
            DataTable NextTable = Excel.Query("SELECT * FROM [" + Table + "]", Table);
            if (NextTable == null || NextTable.Columns.Count == 0 || NextTable.Rows.Count == 0) { continue; }
            DataTable Tmp = new DataTable(NextTable.TableName);
            foreach (DataColumn Column in NextTable.Columns) {
               Tmp.Columns.Add(Column.ColumnName);
            }
            foreach (DataRow Row in NextTable.Rows) {
               Tmp.ImportRow(Row);
            }
            TableList.Add(Tmp);
         }
         return TableList.ToArray();
      }

      public static string[] GetTableNames()
      {
         DataTable Tables = Excel.Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (Tables == null) { return null; }
         List<string> Sheets = new List<string>();
         foreach (DataRow Row in Tables.Rows) {
            Sheets.Add(Row["TABLE_NAME"].ToString());
         }
         return Sheets.ToArray();
      }

      public static string GetReleventTable()
      {
         DataTable Tables = Excel.Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
         if (Tables == null) { return null; }
         foreach (DataRow Row in Tables.Rows) {
            if (!Row["TABLE_NAME"].ToString().ToLower().Contains("sheet")) {
               return "[" + Row["TABLE_NAME"].ToString() + "]";
            }
         }
         return "[Sheet1$]";
      }

      public static DataTable Query(string query)
      {
         try {
            string table = Excel.GetReleventTable();
            Excel.Dataset.Clear();
            Excel.Adapter.SelectCommand.CommandText = query;
            Excel.Adapter.SelectCommand.ExecuteNonQuery();
            Excel.Adapter.Fill(Excel.Dataset, table);
            return Dataset.Tables[table];
         } catch (System.Data.OleDb.OleDbException oleEx) {
            // If this happens, it could be a number of reasons, but typically its becuase
            // the OLE adapter could not find a piece of data (like a Sheet or table)
            Sylloge.App.Out(oleEx.Message);
         } catch (Exception ex) {
            Sylloge.App.Out("There was a programming error trying to access the Excel data." + Environment.NewLine + ex.Message);
         }
         return null;
      }
          
      public static DataTable Query(string query, string table)
      {
         try {
            Excel.Dataset.Clear();
            Excel.Adapter.SelectCommand.CommandText = query;
            Excel.Adapter.SelectCommand.ExecuteNonQuery();
            Excel.Adapter.Fill(Excel.Dataset, table);
            return Dataset.Tables[table];
         } catch (System.Data.OleDb.OleDbException oleEx) {
            // If this happens, it could be a number of reasons, but typically its becuase
            // the OLE adapter could not find a piece of data (like a Sheet or table)
            Sylloge.App.Out(oleEx.Message);
         } catch (Exception ex) {
            Sylloge.App.Out("There was a programming error trying to access the Excel data.", ex);
         }
         return null;
      }

      #endregion

      #region Exporting

      public static bool CreateTable(string tableName, string[] columns)
      {
         string Create = "CREATE TABLE " + tableName + " (";
         for (int FieldCount = 0; FieldCount < columns.Length; FieldCount++) {
            Create += "[" + columns[FieldCount] + "] char(255)";
            if (FieldCount < columns.Length - 1) { Create += ", "; }
         }
         Create += ")";
         try {
            Excel.Dataset.Clear();
            Excel.Adapter.InsertCommand.CommandText = Create;
            Excel.Adapter.InsertCommand.ExecuteNonQuery();
         } catch (Exception ex) {
            Sylloge.App.Out("There was an error exporting the data: " + ex.Message);
            return false;
         }
         return true;
      }

      public static bool AddItem(string tableName, string[] columns, object item)
      {
         // TODO: Change the 'object' item to whatever you want to add and update this section of code
         try {
            string ItemValue = string.Empty;
            string Query = "INSERT INTO [" + tableName + "$] (";
            for (int Index = 0; Index < columns.Length; Index++) {
               Query += "[" + columns[Index] + "]";
               if (Index < columns.Length - 1) { Query += ","; }
            }
            if (Query.Substring(Query.Length - 1, 1) == ",") { Query = Query.Substring(0, Query.Length - 1); }
            Query += ") VALUES (";
            Query += "'" + ((int)item).ToString() + "',";
            Query += "'" + ((string)item) + "'";
            Query += ")";
            Excel.Dataset.Clear();
            Excel.Adapter.InsertCommand.CommandText = Query;
            Excel.Adapter.InsertCommand.ExecuteNonQuery();
         } catch (Exception ex) {
            Sylloge.App.Out("There was an error trying to add the item" + ex.Message);
            return false;
         }
         return true;
      }

      #endregion
   }
}