VAGSuite.ExcelInterface.getDataFromXLS C# (CSharp) Method

getDataFromXLS() public method

public getDataFromXLS ( string strFilePath ) : System.Data.DataTable
strFilePath string
return System.Data.DataTable
        public System.Data.DataTable getDataFromXLS(string strFilePath)
        {
            try
            {
                string strConnectionString = string.Empty;
                strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
                //MessageBox.Show(strConnectionString);
                OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
                cnCSV.Open();
                OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [symboldata$]", cnCSV);
                OleDbDataAdapter daCSV = new OleDbDataAdapter();
                daCSV.SelectCommand = cmdSelect;
                System.Data.DataTable dtCSV = new System.Data.DataTable();
                daCSV.Fill(dtCSV);
                cnCSV.Close();
                daCSV = null;
                return dtCSV;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            finally { }
        }

Usage Example

Beispiel #1
0
        private void ImportExcelSymbol(string symbolname, string filename)
        {
            ExcelInterface excelInterface = new ExcelInterface();
            bool issixteenbit = true;
            System.Data.DataTable dt = excelInterface.getDataFromXLS(filename);
            int symbollength = GetSymbolLength(Tools.Instance.m_symbols, symbolname);
            int datalength = symbollength;
            if (issixteenbit) datalength /= 2;
            int[] buffer = new int[datalength];
            int bcount = 0;
            //            for (int rtel = 1; rtel < dt.Rows.Count; rtel++)
            for (int rtel = dt.Rows.Count; rtel >= 1; rtel--)
            {
                try
                {
                    int idx = 0;
                    foreach (object o in dt.Rows[rtel].ItemArray)
                    {
                        if (idx > 0)
                        {
                            if (o != null)
                            {
                                if (o != DBNull.Value)
                                {
                                    if (bcount < buffer.Length)
                                    {
                                        buffer.SetValue(Convert.ToInt32(o), bcount++);
                                    }
                                    else
                                    {
                                        frmInfoBox info = new frmInfoBox("Too much information in file, abort");
                                        return;
                                    }
                                }
                            }
                        }
                        idx++;
                    }
                }
                catch (Exception E)
                {
                    Console.WriteLine("ImportExcelSymbol: " + E.Message);
                }

            }
            if (bcount >= datalength)
            {
                byte[] data = new byte[symbollength];
                int cellcount = 0;
                if (issixteenbit)
                {
                    for (int dcnt = 0; dcnt < buffer.Length; dcnt++)
                    {
                        string bstr1 = "0";
                        string bstr2 = "0";
                        int cellvalue = Convert.ToInt32(buffer.GetValue(dcnt));
                        string svalue = cellvalue.ToString("X4");

                        bstr1 = svalue.Substring(svalue.Length - 4, 2);
                        bstr2 = svalue.Substring(svalue.Length - 2, 2);
                        data.SetValue(Convert.ToByte(bstr1, 16), cellcount++);
                        data.SetValue(Convert.ToByte(bstr2, 16), cellcount++);
                    }
                }
                else
                {
                    for (int dcnt = 0; dcnt < buffer.Length; dcnt++)
                    {
                        int cellvalue = Convert.ToInt32(buffer.GetValue(dcnt));
                        data.SetValue(Convert.ToByte(cellvalue.ToString()), cellcount++);
                    }
                }
                Tools.Instance.savedatatobinary((int)GetSymbolAddress(Tools.Instance.m_symbols, symbolname), symbollength, data, Tools.Instance.m_currentfile, true, Tools.Instance.m_currentFileType);
                Tools.Instance.UpdateChecksum(Tools.Instance.m_currentfile, false);
            }
        }