C# ExcelQueryFactory.GetColumnNames方法代码示例

        static void Main(string[] args)
        {

            // Получение информации из Excel документа с именем SampleData.xls
            string pathFile = "SampleData.xls";
                      
            var excelFile = new ExcelQueryFactory(pathFile);

            // Вывод списка листов (Worksheet) в Excel документе  
            foreach( var item in excelFile.GetWorksheetNames())
            {
                Console.Write(item+" ");
            }
            Console.Write("\n");

            foreach (var item in excelFile.GetWorksheetNames())
            {
                Console.Write(item + ":");
                foreach (var itemRow in excelFile.GetColumnNames(item))
                {
                    Console.Write(itemRow + " ");
                }
                Console.Write("\n");
            }


            // установка документа "только для чтения"
            excelFile.ReadOnly = true;

            Console.ReadKey();
        }

        public IEnumerable<Product> GetProductsFromFile(string filename)
        {
            var excel = new ExcelQueryFactory(filename);
            var worksheetNames = excel.GetWorksheetNames().ToArray();
            //
            //Mapping
            //
            for (int i = 0; i < worksheetNames.Length; i++)
            {
                var columnNames = excel.GetColumnNames(worksheetNames[i]);
                var titleRow = FindTitleRow(excel.WorksheetNoHeader(worksheetNames[i]));

                var codeIndex = 0;
                var priceIndex = GetColumnIndex(titleRow, "Price");

                bool skip = true;
                foreach (var product in excel.WorksheetNoHeader(worksheetNames[i])
                    .Where(x => x[codeIndex] != ""))
                {
                    if (skip)
                    {
                        skip = product[codeIndex].ToString().Trim() != "No.:";
                        continue;
                    }
                    yield return
                        new Product
                        {
                            Code = product[codeIndex].ToString().Trim(),
                            Price = product[priceIndex].Cast<decimal>()
                        };
                }
            }
        }

        public void GetColumnNames_returns_column_names()
        {
            var excel = new ExcelQueryFactory(_excelFileName);

            var columnNames = excel.GetColumnNames("Sheet1");
            Assert.AreEqual(
                "Name, CEO, EmployeeCount, StartDate",
                string.Join(", ", columnNames.ToArray()));
        }

        public void ExcelFielder_ReadExcelFileWithFieldsAtTopRow_ReturnTopRow()
        {
            var mockFieldManager = new Mock<IFieldManager>();
            var fielder = new ExcelFielder(mockFieldManager.Object);
            fielder.FileSource = @"C:\Users\jpinkard\Documents\GitHub\SolutionsForWork\AcumiChart\TestFiles\Excel_FieldInTopRowSample.xlsx";
            fielder.WorksheetSource = "NoDupFields";
            var excel = new ExcelQueryFactory(@"C:\Users\jpinkard\Documents\GitHub\SolutionsForWork\AcumiChart\TestFiles\Excel_FieldInTopRowSample.xlsx");
            var fields = excel.GetColumnNames("NoDupFields");

            fielder.Read();

            Assert.That(mockFieldManager.AvailableFields, Is.EqualTo(fields));
        }

        public void WriteDataFromExcelToXML()
        {
            XDocument doc = new XDocument();
            doc.Add(new XElement("Store"));

            string pathFile = "../../PriceProductBase.xlsx";
            var excelFile = new ExcelQueryFactory(pathFile);

            foreach (var item in excelFile.GetWorksheetNames())
            {
                var category = new XElement(item);
                List<Row> elements;

                if (item == "Prices")
                {
                    elements = (from a in excelFile.Worksheet(item) where a["КодТовара"] != "" select a).Take(5).ToList();
                }
                else
                {
                    elements = (from a in excelFile.Worksheet(item) select a).Take(5).ToList();
                }

                foreach (var el in elements)
                {
                    XElement element = new XElement("Element");
                    foreach(var itemRow in excelFile.GetColumnNames(item))
                    {
                        XElement data = new XElement(itemRow);
                        data.Value = el[itemRow];
                        element.Add(data);
                    }
                    category.Add(element);
                }

                doc.Root.Add(category);
            }

            excelFile.ReadOnly = true;

            doc.Save("PriceProductBase.xml");
        }

        static void Main(string[] args)
        {
            // Получение информации из Excel документа с именем SampleData.xls
            string pathFile = "PriceProductBase.xlsx";

            var excelFile = new ExcelQueryFactory(pathFile);

            string sheetName = "Orders";

            // Вывод списка листов (Worksheet) в Excel документе  

            var infoOrders = from a in excelFile.Worksheet(sheetName)
                             select a;

            foreach (var item in excelFile.GetWorksheetNames())
            {
                foreach (var itemRow in excelFile.GetColumnNames(item))
                {
                    Console.Write(itemRow + " ");
                }
                Console.Write("\n");
            }

            foreach (var item in infoOrders)
            {
                string pattern = "{0};{1};{2};{3};{4};{5};{6};";

                // Вывод информации из столбцов OrderDate и Total
                Console.WriteLine(string.Format(pattern, item["Номер заказа"],
                    item["Номер продукта"], item["F3"], 
                    item["F4"], item["F5"], 
                    item["F6"], item["F7"]));
            }


            // установка документа "только для чтения"
            excelFile.ReadOnly = true;

            Console.ReadKey();
        }

        public void Generate()
        {
            var inputFile = GeneratorFileHelper.GetInputExcelFile();

            var excel = new ExcelQueryFactory(inputFile);
            var columns = excel.GetColumnNames("Payouts").Skip(1);

            var outputFile = GeneratorFileHelper.InitializeOutputJsFile("Cities.js");
            var builder = new StringBuilder("Boxcars.Data.cities = [\n\t");
            var counter = 0;
            foreach (var column in columns)
            {
                builder.AppendFormat("'{0}', ", column);
                counter = (counter + 1)%5;
                if (counter == 0)
                    builder.Append("\n\t");
            }
            //Remove the last comma and space
            builder.Length -= 2;
            builder.Append("\n];");
            File.AppendAllText(outputFile, builder.ToString());
        }

        private static void TestCase10_LinqToExcel()
        {
            //https://github.com/paulyoder/LinqToExcel
            var table = ExcelQueryFactory.Worksheet("Info",
                Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Test.xlsx"));
            var excelQueryFactory =
                new ExcelQueryFactory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),
                    "Test.xlsx"))
                {
                    UsePersistentConnection = true,
                    ReadOnly = true,
                    DatabaseEngine = DatabaseEngine.Ace,
                    TrimSpaces = TrimSpacesType.Both,
                    StrictMapping = StrictMappingType.Both
                };
            var columnNames = excelQueryFactory.GetColumnNames("Info");
            try
            {
                var infos = excelQueryFactory.Worksheet<Information>("Info").Where(p => p.Status == 1);

                var Num = excelQueryFactory.WorksheetRange("A3", "B103", "Sheet1");
            }
            finally
            {
                excelQueryFactory.Dispose();
            }
        }

        /// <summary>
        /// Check if necessary columns ir available
        /// </summary>
        /// <param name="input">ExcelQueryFactory instance</param>
        private void ValidateExcelColumns(ExcelQueryFactory input)
        {
            string notFound = "Not found";
            var columnNames = input.GetColumnNames("Sheet1");

            if (columnNames.Count() == 0)
            {
                throw new ArgumentException("Sheet1 not found");
            }

            if (columnNames.Where(o => o == Feature).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", Feature, notFound));
            }

            if (columnNames.Where(o => o == FinnishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", FinnishText, notFound));
            }

            if (columnNames.Where(o => o == SwedishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", SwedishText, notFound));
            }

            if (columnNames.Where(o => o == EnglishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", EnglishText, notFound));
            }
        }

 public void GetColumnNames_throws_exception_when_filename_not_set()
 {
     var factory = new ExcelQueryFactory();
     factory.GetColumnNames("");
 }

        /// <summary>
        /// Check if necessary columns ir available
        /// </summary>
        /// <param name="input">ExcelQueryFactory instance</param>
        private void ValidateExcelColumns(ExcelQueryFactory input)
        {
            string notFound = "Not found";
            var excelcolumns = input.GetColumnNames("Main").ToList();

            List<string> OriginalColumnNames = new List<string> { "Feature", "Original", "English text", "Finish text", "Swedish text" };

            for (int i = 0; i < OriginalColumnNames.Count(); i++)
            {
                if (excelcolumns[i] != OriginalColumnNames[i])
                {
                    throw new ArgumentException("Column names have been changed or changed order!!!");
                }
            }

            if (excelcolumns.Count() == 0)
            {
                throw new ArgumentException("Main not found");
            }

            if (excelcolumns.Where(o => o == Feature).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", Feature, notFound));
            }

            if (excelcolumns.Where(o => o == FinnishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", FinnishText, notFound));
            }

            if (excelcolumns.Where(o => o == SwedishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", SwedishText, notFound));
            }

            if (excelcolumns.Where(o => o == EnglishText).Count() == 0)
            {
                throw new ArgumentException(string.Format("{0}: {1}", EnglishText, notFound));
            }
        }

        static void Main(string[] args)
        {
            //mongoimport --db DataStore --collection users --file File_2015-04-21-142804.json
            var path = ConfigurationManager.AppSettings["Path"].ToString(); ;
            var sheet = ConfigurationManager.AppSettings["Sheet"].ToString(); ;
            var fileName = ConfigurationManager.AppSettings["FileName"].ToString();

            var excel = new ExcelQueryFactory(path);
            var columns = excel.GetColumnNames(sheet);
            var list = excel.Worksheet(sheet).ToList();

            var headers = new List<string>();

            // get header
            foreach (var c in columns)
            {
                if (!string.IsNullOrEmpty(c.ToString()))
                {
                    var header = c.ToString().Trim().ToLower()
                        .Replace(" ", "")
                        .Replace("/", "")
                        .Replace("(", "")
                        .Replace(")", "");

                    headers.Add(header);
                }
            }

            Console.WriteLine();
            Console.WriteLine("[Convert Excel to JSON]");
            Console.WriteLine("=======================");
            Console.WriteLine("Path         : {0}", path);
            Console.WriteLine("Sheet        : {0}", sheet);
            Console.WriteLine("File Name    : {0}", fileName);
            Console.WriteLine();
            Console.WriteLine("Generating...");
            Console.WriteLine();

            var outputFileName = string.Format("{0}_{1}.json", fileName, DateTime.Now.ToString("yyyy-MM-dd-HHmmss"));
            var outFile = System.IO.File.CreateText(outputFileName);

            foreach (var item in list)
            {
                // generate json
                outFile.WriteLine("{");

                for (int i = 0; i < headers.Count; i++)
                {
                    if (i != (headers.Count - 1))
                    {
                        outFile.WriteLine("    \"{0}\": \"{1}\",", headers[i], item[i].ToString()
                            .Replace("\n", " ")
                            .Replace("\r", " ")
                            .Replace("\"", " ")
                            .Trim());
                    }
                    else
                    {
                        outFile.WriteLine("    \"{0}\": \"{1}\"", headers[i], item[i].ToString()
                            .Replace("\n", " ")
                            .Replace("\r", " ")
                            .Replace("\"", " ")
                            .Trim());
                    }
                }

                outFile.WriteLine("}");
            }

            outFile.Close();

            Console.WriteLine(string.Format("{0} created", outputFileName));
            Console.WriteLine("Press any key to continue...");
            Console.Read();
        }

        private void ValidateExcelColumns(ExcelQueryFactory excel)
        {
            List<string> OriginalColumnNames = new List<string>()
            {
                "ModelName", "PropertyName", "English","Finnish","Swedish","English1", "Finnish1","Swedish1","English2",
                "Finnish2", "Swedish2", "English3" , "Finnish3", "Swedish3"
            };

            var excelcolumns = excel.GetColumnNames("Main").ToList();
            for (int i = 0; i < OriginalColumnNames.Count(); i++)
            {
                if (excelcolumns[i] != OriginalColumnNames[i])
                {
                    throw new ArgumentException("Column names have been changed or changed order!!!");
                }
            }
        }

        private void MapColumns(ExcelQueryFactory excel)
        {
            var columns = excel.GetColumnNames("Main").ToList();

            #region In caseof  Linq to Excel update

            //excel.AddMapping<WebElementExcelModel>(m => m.ModelName, columns[0]);
            //excel.AddMapping<WebElementExcelModel>(m => m.PropertyName, columns[1]);

            //// Prompt
            //excel.AddMapping<WebElementExcelModel>(m => m.Label.EngText, columns[2]);
            //excel.AddMapping<WebElementExcelModel>(m => m.Label.FinText, columns[3]);
            //excel.AddMapping<WebElementExcelModel>(m => m.Label.SweText, columns[4]);

            //// ControlText
            //excel.AddMapping<WebElementExcelModel>(m => m.ControlText.EngText, columns[5]);
            //excel.AddMapping<WebElementExcelModel>(m => m.ControlText.FinText, columns[6]);
            //excel.AddMapping<WebElementExcelModel>(m => m.ControlText.SweText, columns[7]);

            //// Help
            //excel.AddMapping<WebElementExcelModel>(m => m.Help.EngText, columns[8]);
            //excel.AddMapping<WebElementExcelModel>(m => m.Help.FinText, columns[9]);
            //excel.AddMapping<WebElementExcelModel>(m => m.Help.SweText, columns[10]);

            //// EnumText
            //excel.AddMapping<WebElementExcelModel>(m => m.EnumText.EngText, columns[11]);
            //excel.AddMapping<WebElementExcelModel>(m => m.EnumText.FinText, columns[12]);
            //excel.AddMapping<WebElementExcelModel>(m => m.EnumText.SweText, columns[13]);

            #endregion

            excel.AddMapping<WebElementExcelModel>(m => m.ModelName, columns[0]);
            excel.AddMapping<WebElementExcelModel>(m => m.PropertyName, columns[1]);

            // Label
            excel.AddMapping<WebElementExcelModel>(m => m.LabelEngText, columns[2]);
            excel.AddMapping<WebElementExcelModel>(m => m.LabelFinText, columns[3]);
            excel.AddMapping<WebElementExcelModel>(m => m.LabelSweText, columns[4]);

            // ControlText
            excel.AddMapping<WebElementExcelModel>(m => m.ControlEngText, columns[5]);
            excel.AddMapping<WebElementExcelModel>(m => m.ControlFinText, columns[6]);
            excel.AddMapping<WebElementExcelModel>(m => m.ControlSweText, columns[7]);

            // Help
            excel.AddMapping<WebElementExcelModel>(m => m.HelpEngText, columns[8]);
            excel.AddMapping<WebElementExcelModel>(m => m.HelpFinText, columns[9]);
            excel.AddMapping<WebElementExcelModel>(m => m.HelpSweText, columns[10]);

            // EnumText
            excel.AddMapping<WebElementExcelModel>(m => m.EnumEngText, columns[11]);
            excel.AddMapping<WebElementExcelModel>(m => m.EnumFinText, columns[12]);
            excel.AddMapping<WebElementExcelModel>(m => m.EnumSweText, columns[13]);
        }