C# ExcelQueryFactory.WorksheetNoHeader方法代码示例

        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 where_is_null()
        {
            var factory = new ExcelQueryFactory(_excelFileName.Replace("NoHeader.xls", "Companies.xlsx"));
            var oldCompanies = from c in factory.WorksheetNoHeader("NullCells")
                               where c[2] == null
                               select c;

            Assert.AreEqual(2, oldCompanies.Count(), "Company Count");
        }

 /// <summary>
 /// Read Spreadsheet data.
 /// </summary>
 /// <param name="filePath"></param>
 /// <returns></returns>
 public static ICollection<CosmoSheet> ReadSpreadsheetCosmo(string filePath)
 {
     var excel = new ExcelQueryFactory(filePath);
     var lstCosmo = (from ico in excel.WorksheetNoHeader() select new CosmoSheet
         {
             CompleteName = ico[0],
             Address = ico[1],
             CityStateZip = ico[2]
         }).ToList();
     return lstCosmo;
 }

        private void Load()
        {
            if (String.IsNullOrWhiteSpace(path))
            {
                keywords = null;
                return;
            }

            var excel = new ExcelQueryFactory(path);
            if (excel == null) return;
            keywords = from c in excel.WorksheetNoHeader()
                       select c[0].Value.ToString();
        }

        public void no_header_connection_string()
        {
            var excel = new ExcelQueryFactory("spreadsheet.xls");
            var companies = from c in excel.WorksheetNoHeader()
                            select c;

            try { companies.GetEnumerator(); }
            catch (OleDbException) { }
            var expected = string.Format(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""",
                "spreadsheet.xls");
            Assert.AreEqual(expected, GetConnectionString());
        }

 private IList<string[]> ReadAll(string path)
 {
     var excel = new ExcelQueryFactory(path);
     var worksheet = excel.WorksheetNoHeader(0);
     if (worksheet != null) {
         var data = from row in worksheet
                    where row[0] != String.Empty ||
                          row[1] != String.Empty
                    select new string[] {
                         row[0].Value.ToString(),
                         row[1].Value.ToString()
                    };
         return data.ToList();
     }
     return new List<string[]>();
 }

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

            var excel = new ExcelQueryFactory(inputFile);
            var rows = (from c in excel.WorksheetNoHeader("Payouts")
                select c).ToArray();

            //Skip the first row (header)
            //Skip the last row (totals)
            rows = rows.Take(rows.Length - 1).Skip(1).ToArray();

            var outputFile = GeneratorFileHelper.InitializeOutputJsFile("Payouts.js");
            var builder = new StringBuilder();
            builder.AppendLine();
            builder.AppendLine("Boxcars.Data.payouts = [");

            for (var inputRowIndex = 0; inputRowIndex < rows.Length; inputRowIndex++)
            {
                var isLastRow = inputRowIndex == rows.Length - 1;
                builder.AppendFormat("//{0}\n\t[", inputRowIndex);

                //Skip the first cell (row header)
                var cells = rows[inputRowIndex].Skip(1).ToArray();
                for (var inputColumnIndex = 0; inputColumnIndex < cells.Length; inputColumnIndex++)
                {
                    var isLastCell = inputColumnIndex == cells.Length - 1;
                    var stringValue = cells[inputColumnIndex].Value.ToString();
                    var decimalValue = decimal.Parse(stringValue) * 1000;
                    builder.AppendFormat("\"${0:n0}\"", decimalValue);
                    var separator = string.Empty;
                    var makeNewLine = (inputColumnIndex%10) == 9 && (!isLastCell);
                    if (makeNewLine)
                        separator = ",\n\t";
                    else if (!isLastCell)
                        separator = ",";
                    builder.Append(separator);
                }
                builder.AppendLine(isLastRow ? "]" : "],");
            }
            builder.AppendLine("];");

            File.AppendAllText(outputFile, builder.ToString());
        }

 public void distinct_on_no_header_with_selected_column()
 {
     var excel = new ExcelQueryFactory("");
     var companies = (from c in excel.WorksheetNoHeader()
                      select c[0]).Distinct().ToList();
 }

 private List<KeyValue> GetExcelHeader(string fileName)
 {
     List<ExcelMapping> excelMapList = new List<ExcelMapping>();
     var excel = new ExcelQueryFactory(fileName);
     var headers = excel.WorksheetNoHeader(0).FirstOrDefault();
     List<KeyValue> keyValueColl = new  List<KeyValue>();
     headers.ForEach(p=>keyValueColl.Add(new KeyValue{ Key =  p.Value.ToString(), Value = p.Value.ToString()}));
     return keyValueColl;
 }

        public ActionResult ImportOperateLogWithExcel(HttpPostedFileBase excel_file)
        {
            string file_name = System.IO.Path.GetFileName(excel_file.FileName);

            try
            {
                string filePhysicalPath = Server.MapPath("~/TempFile/" + file_name);
                excel_file.SaveAs(filePhysicalPath);
                var excel = new ExcelQueryFactory(filePhysicalPath);

                var logs = from c in excel.WorksheetNoHeader()
                           select c;

                foreach (var log in logs)
                {
                    T_ZC_MemberLog member_log = new T_ZC_MemberLog();
                    member_log.StartDateTime = Convert.ToDateTime(log[1]);
                    member_log.EndDateTime = Convert.ToDateTime(log[2]);
                    member_log.Message = log[3].ToString();
                    member_log.OperateType = GetValueOfEnumByName(log[4].ToString(),typeof(OperateTypes));
                    member_log.GenerateModule = GetValueOfEnumByName(log[5].ToString(), typeof(GenerateSystem));
                    member_log.MemberID = Convert.ToInt32(log[6]);
                    db.T_ZC_MemberLog.Add(member_log);
                }
                db.SaveChanges();
                return ReturnJson(true, "导入成功", "", "", true, "");
            }
            catch(Exception e)
            {
                return ReturnJson(false, e.Message, "", "", false, "");
            }
        }

        public void no_header_readonly_with_Jet_DatabaseEngine_connection_string()
        {
            var excel = new ExcelQueryFactory("spreadsheet.xls")
            {
                DatabaseEngine = ExcelDatabaseEngine.Ace,
                ReadOnly = true
            };

            var companies = from c in excel.WorksheetNoHeader()
                            select c;

            try { companies.GetEnumerator(); }
            catch (OleDbException) { }
            var expected =
                [email protected]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={"spreadsheet.xls"
                    };Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;READONLY=TRUE""";
            Assert.AreEqual(expected, GetConnectionString());
        }

        private static Dictionary<LinguisticObjectType, List<RowNoHeader>> _loadRawData(string filePath)
        {
            var excel = new ExcelQueryFactory(filePath);

            return (new[]
            {
                SubjectsSheetName,
                PredicatesSheetName,
                UniformsSheetName,
                MeaningPartsSheetName,
                ChainsFromSubjectSheetName,
                ChainsFromPredicateSheetName,
                ChainsFromMeaningPartSheetName
            }).ToDictionary(_getObjectType, s => excel.WorksheetNoHeader(s).ToList());
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            CleanDestination();

            _fileList = new List<string>();
            btnStart.Enabled = false;

            if (string.IsNullOrEmpty(txtExcelFile.Text) || string.IsNullOrEmpty(txtFirstPDF.Text) ||
                string.IsNullOrEmpty(txtSecondPDF.Text) || string.IsNullOrEmpty(txtDestinationFolder.Text))
            {
                MessageBox.Show("One or more files not specified.");
                return;
            }

            var pathToExcelFile = txtExcelFile.Text;
            var excelFile = new ExcelQueryFactory(pathToExcelFile);
            var columnValues = from a in excelFile.WorksheetNoHeader(0) select a;

            var listRows = new List<ExcelRow>();

            var setNumber = 1;
            var increment = 1;
            foreach (var a in columnValues)
            {
                listRows.Add(
                    new ExcelRow
                    {
                        Index = increment,
                        SetNumber = setNumber,
                        ColumnValue = a[1].Value.ToString(),
                        FileName = string.IsNullOrWhiteSpace(a[0].Value.ToString()) ? "" : a[0].Value.ToString()
                    }
                );

                if (!a[1].Value.ToString().EndsWith(","))
                    setNumber++;

                increment++;
            }

            for (var i = 1; i < increment - 1; i++)
            {
                var sbSearch = new StringBuilder();
                _searchValues.Clear();

                foreach (var item in from item in listRows.Where(item => item.SetNumber == i) let index = item.Index let text = item.ColumnValue select item)
                {
                    sbSearch.Append(item.ColumnValue);

                    _searchValues.Add(new KeyValuePair<int, string>(item.Index, item.ColumnValue));

                    if (!string.IsNullOrEmpty(item.FileName))
                    {
                        _currentFile = item.FileName;
                        _actualRow = item.SetNumber;
                    }
                }

                if (!string.IsNullOrEmpty(sbSearch.ToString()))
                    ProcessPdf(StringComparison.Ordinal, txtFirstPDF.Text, txtDestinationFolder.Text + "\\" + GetFileName(_currentFile, txtFirstPDF.Text) + ".pdf", sbSearch.ToString(), i, _searchValues);

                if (!string.IsNullOrEmpty(txtSecondPDF.Text) && !string.IsNullOrEmpty(sbSearch.ToString()))
                    ProcessPdf(StringComparison.Ordinal, txtSecondPDF.Text, txtDestinationFolder.Text + "\\" + GetFileName(_currentFile, txtSecondPDF.Text) + ".pdf", sbSearch.ToString(), i, _searchValues);

            }

            #region Update Excel...

            lblMsg.Visible = true;
            foreach (var item in _foundValuesInFirstPdf)
            {
                var myCommand = new OleDbCommand();
                string sqlUpdate = null;
                var count = item.Value.Split(',').Count();

                var cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelFile.Text + ";Extended Properties='Excel 8.0;HDR=NO;'";
                var myConnection = new OleDbConnection(cnn);
                myConnection.Open();
                myCommand.Connection = myConnection;
                sqlUpdate = "UPDATE [Sheet1$H" + item.Key + ":H" + item.Key + "] SET F1='" + item.Value + "'";
                myCommand.CommandText = sqlUpdate;
                myCommand.ExecuteNonQuery();

                sqlUpdate = "UPDATE [Sheet1$J" + item.Key + ":J" + item.Key + "] SET F1='" + count + "'";
                myCommand.CommandText = sqlUpdate;
                myCommand.ExecuteNonQuery();

                myConnection.Close();

            }

            foreach (var item in _foundValuesInSecindPdf)
            {
                var myCommand = new OleDbCommand();
                string sqlUpdate = null;
                var count = item.Value.Split(',').Count();

                var cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtExcelFile.Text + ";Extended Properties='Excel 8.0;HDR=NO;'";
                var myConnection = new OleDbConnection(cnn);
                myConnection.Open();
//.........这里部分代码省略.........

        /// <summary>
        /// Загрузка данных и отсев пустых строк и заголовков. На выходе только строки с частями предложений
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private static List<RowNoHeader> loadData(string fileName, string sheetName)
        {
            var excel = new ExcelQueryFactory(fileName);
            int val;

            var isSheetExist = excel.GetWorksheetNames().Any(s => s == sheetName);

            var rawData =
                (isSheetExist ? excel.WorksheetNoHeader(sheetName) : excel.WorksheetNoHeader(0))
                    .Where(row => row[0] != null).ToList();

            var rawSentenceParts =
                rawData.Where(row => row[0].Value is String && Int32.TryParse(row[0].Value as String, out val))
                    .ToList();

            return rawSentenceParts;
        }

        public void UpdateDepartments()
        {

            var excel = new ExcelQueryFactory();
            string[] ex_names = MvcApplication.Config("excel_name").Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

            for (int ex_name = 0; ex_name < ex_names.Length; ex_name++)
            {
                excel.FileName = ex_names[ex_name];

                var result_employees = from c
                                in excel.WorksheetNoHeader(0)
                                       select c;


                List<Department> existing_deps = (List<Department>)GetSession.QueryOver<Department>().List();
                List<string> existing_dep_list = new List<string>();

                foreach (var dep in existing_deps)
                {
                    if (!existing_dep_list.Contains(dep.Name))
                    {
                        existing_dep_list.Add(dep.Name);

                    }
                }


                foreach (var new_dep in result_employees)
                {
                    if (!string.IsNullOrEmpty(new_dep[4].ToString().Trim()) && !existing_dep_list.Contains(new_dep[4].ToString().Trim()))
                    {
                        DepartmentFormModel model = new DepartmentFormModel()
                        {
                            Name = new_dep[4].ToString().Trim()
                        };

                        var item = Mapper.Map<DepartmentFormModel, Department>(model);
                        GetSession.Save(item);
                        existing_dep_list.Add(new_dep[4].ToString().Trim());
                    }
                }

            }
        }