static void Main(string[] args)
{
const string excelPath = "c:/temp/excel/Employees.xlsx";
var excel = new ExcelQueryFactory(excelPath);
var employees = from e in excel.Worksheet<Employee>("Employee")
select e;
Console.WriteLine("All Employees");
Console.WriteLine("");
WriteInfo(employees);
Console.WriteLine("");
Console.WriteLine("");
var managers = from e in excel.Worksheet<Employee>("Employee")
where e.IsManager
select e;
Console.WriteLine("Managers");
Console.WriteLine("");
WriteInfo(managers);
Console.WriteLine("");
Console.WriteLine("");
var regularEmployees = from e in excel.Worksheet<Employee>("Employee")
where e.IsManager == false
select e;
Console.WriteLine("Regular Employees");
Console.WriteLine("");
WriteInfo(regularEmployees);
Console.WriteLine("");
Console.Read();
}
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 s()
{
_repo = new ExcelQueryFactory { FileName = _excelFileName };
_repo.AddMapping<ChineseNameColumn>(x => x.ChineseColumn, "第一列");
_repo.AddMapping<ChineseNameColumn>(x => x.DoubleColumn, "第二列（复杂的：浮点-》");
_repo.AddMapping<ChineseNameColumn>(x => x.ThirdColumn, "第3列：\"待引号的\"");
}
private void btnSelectFile_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = Environment.CurrentDirectory;
// openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
openFileDialog.Filter = "Excel(*.xlsx)|*.xlsx|Excel 2000-2003(*.xls)|*.xls|CSV(*.csv)|*.csv|所有文件(*.*)|*.*";
if (openFileDialog.ShowDialog(this) == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
string FileName = openFileDialog.FileName;
var execelfile = new ExcelQueryFactory(FileName);
this.txtContact.Text = FileName;
this.txtContact.Enabled = false;
List<string> sheetNames= execelfile.GetWorksheetNames().ToList();
//星级信息
List<Row> agentStar = execelfile.Worksheet(0).ToList(); ;
if (agentStar != null && agentStar.Count > 0)
{
this.btnImport.Enabled = true;
dgAgentStar.Rows.Clear();
dgAgentStar.Columns.Clear();
foreach (String coloumn in agentStar[0].ColumnNames)
{
this.dgAgentStar.Columns.Add(coloumn, coloumn);
}
for (int i = 0; i < agentStar.Count; i++)
{
if (String.IsNullOrEmpty(agentStar[i][0]))
{
continue;
}
dgAgentStar.Rows.Add();
DataGridViewRow row = dgAgentStar.Rows[i];
foreach (String coloumn in agentStar[0].ColumnNames)
{
row.Cells[coloumn].Value = agentStar[i][coloumn];
}
}
}
dgAgentStar.ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False;
dgAgentStar.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dgAgentStar.AutoResizeColumns();
Cursor.Current = Cursors.Default;
}
}
public ActionResult Index()
{
foreach (string upload in Request.Files)
{
// if (!Request.Files[upload]) continue;
string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/";
string filename = Path.GetFileName(Request.Files[upload].FileName);
string pathToExcelFile = Path.Combine(path, filename);
string sheetName = "Sheet1";
Request.Files[upload].SaveAs(pathToExcelFile);
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var data = from a in excelFile.Worksheet<ExcelData>(sheetName) select a;
using (var ctx = new Context())
{
foreach (var a in data)
{
ctx.ExcelData.Add(a);
//Console.WriteLine(string.Format(artistInfo, a.Account, a.Account));
}
ctx.SaveChanges();
}
}
return View();
}
public List<string> Import(string path, IEnumerable<string> regions)
{
var factory = new ExcelQueryFactory {FileName = path};
var message = (from region in regions
let stats = (from c in factory.Worksheet<CdmaRegionStatExcel>(region)
where c.StatDate > DateTime.Today.AddDays(-30) && c.StatDate <= DateTime.Today
select c).ToList()
let count = _regionStatRepository.Import<ICdmaRegionStatRepository, CdmaRegionStat, CdmaRegionStatExcel>(stats)
select "完成导入区域：'" + region + "'的日常指标导入" + count + "条").ToList();
var topDrops = (from c in factory.Worksheet<TopDrop2GCellExcel>(TopDrop2GCellExcel.SheetName)
select c).ToList();
var drops = _top2GRepository.Import<ITopDrop2GCellRepository, TopDrop2GCell, TopDrop2GCellExcel>(topDrops);
message.Add("完成TOP掉话小区导入" + drops + "个");
var topConnections = (from c in factory.Worksheet<TopConnection3GCellExcel>(TopConnection3GCellExcel.SheetName)
select c).ToList();
var connections =
_top3GRepository.Import<ITopConnection3GRepository, TopConnection3GCell, TopConnection3GCellExcel>(
topConnections);
message.Add("完成TOP连接小区导入" + connections + "个");
var topConnection2Gs = (from c in factory.Worksheet<TopConnection2GExcel>(TopConnection2GExcel.SheetName)
select c).ToList();
var connection2Gs =
_topConnection2GRepository.Import<ITopConnection2GRepository, TopConnection2GCell, TopConnection2GExcel>
(topConnection2Gs);
message.Add("完成TOP呼建小区导入" + connection2Gs + "个");
return message;
}
protected void btnUpload_FileSelected(object sender, EventArgs e)
{
if (btnUpload.HasFile)
{
string fileName = btnUpload.ShortFileName;
if (!fileName.EndsWith(".xlsx"))
{
Alert.Show("只支持xlsx文件类型！");
return;
}
string path = ConfigHelper.GetStringProperty("DataFilePath", @"D:\root\Int\data\") + "tmp\\" + fileName;
btnUpload.SaveAs(path);
var execelfile = new ExcelQueryFactory(path);
execelfile.AddMapping<UserInfo>(x => x.Code, "登录帐号");
execelfile.AddMapping<UserInfo>(x => x.Name, "姓名");
execelfile.AddMapping<UserInfo>(x => x.NameEn, "英文名");
execelfile.AddMapping<UserInfo>(x => x.Departments, "部门");
execelfile.AddMapping<UserInfo>(x => x.JobName, "职位");
execelfile.AddMapping<UserInfo>(x => x.Phone, "电话");
execelfile.AddMapping<UserInfo>(x => x.Email, "电子邮箱");
execelfile.AddMapping<UserInfo>(x => x.LeaderCode, "上级领导");
var list = execelfile.Worksheet<UserInfo>(0).ToList();
Import(list);
this.userGrid.PageIndex = 0;
GridBind();
Alert.Show("上传成功");
}
}
public bool ImportStudentFromExcel(string filePath)
{
bool result = false;
if (!string.IsNullOrEmpty(filePath))
{
var excelFile = new ExcelQueryFactory(filePath);
List<string> sheetnames = excelFile.GetWorksheetNames().ToList();
//excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.Remarks, PayrollItemDataEntry.Remarks.ToString());
//excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.filePath, PayrollItemDataEntry.filePath.ToString());
//excelFile.AddMapping<PayrollItemDataEntryCustom>(x => x.sheetNames, "tstSheet1");//PayrollItemDataEntry.sheetNames.ToString()
excelFile.AddMapping<Student>(x => x.SID, "SID");
excelFile.AddMapping<Student>(x => x.Name,"Name");
excelFile.AddMapping<Student>(x => x.Minor, "Minor");
excelFile.AddMapping<Student>(x => x.Major, "Major");
excelFile.AddMapping<Student>(x => x.Division, "Division");
excelFile.StrictMapping = StrictMappingType.ClassStrict;
excelFile.TrimSpaces = TrimSpacesType.Both;
excelFile.ReadOnly = true;
var AllExcellData = (from ExcelData in excelFile.Worksheet(0)
select ExcelData).ToList();
}
return result;
}
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();
}
private static List<PuzzleGame> GetGamesFromPuzzleGameWorksheet(ExcelQueryFactory excel, int puzzleSubGroupId)
{
//Get all games for a sub group
var games = from c in excel.Worksheet("PuzzleGame")
select new
{
PuzzleGameId = c["PuzzleGameId"].Cast<int>(),
PuzzleSubGroupId = c["PuzzleSubGroupId"].Cast<int>(),
Word = c["Word"].Cast<string>(),
Hint = c["Hint"].Cast<string>()
};
var gamelist = games.ToList();
var grouped = from c in gamelist
where c.PuzzleSubGroupId == puzzleSubGroupId
group c by c.PuzzleGameId
into g
select new {PuzzleGameId = g.Key, Games = g};
var puzzleGames = new List<PuzzleGame>();
foreach (var gamesSet in grouped)
{
var puzzleGame = new PuzzleGame()
{PuzzleGameId = gamesSet.PuzzleGameId, Words = new Dictionary<string, string>()};
foreach (var game in gamesSet.Games)
{
puzzleGame.Words.Add(game.Word, game.Hint);
Console.WriteLine(game.Word);
}
puzzleGames.Add(puzzleGame);
}
return puzzleGames;
}
public ActionResult ReconciliationUpload(Guid fileid)
{
var model = new List<UploadReconciliationModel>();
var uploadFilePath = ConfigurationManager.AppSettings["UploadFilePath"];
if (!Directory.Exists(uploadFilePath))
{
Directory.CreateDirectory(uploadFilePath);
}
var fileNamePrefix = fileid.ToString();
var file = System.Web.HttpContext.Current.Request.Files[0];
if (file.ContentLength > 0)
{
var fileName = Path.GetFileName(file.FileName);
//var fileExtension = Path.GetExtension(fileName);
//var newFileName = fileNamePrefix + fileExtension;
//var fileFullPath = uploadFilePath + newFileName;
var fileFullPath = uploadFilePath + fileNamePrefix;
try
{
file.SaveAs(fileFullPath);
//return RedirectToAction("Reconciliation", new { filePath = uploadFilePath });
var excel = new ExcelQueryFactory(fileFullPath);
model = excel.Worksheet<UploadReconciliationModel>(0).ToList();
}
catch (Exception)
{
System.IO.File.Delete(fileFullPath);
}
}
return View(model.ToArray());
}
public static IList<CollectionImportModel> Read(string fileName)
{
var data = new ExcelQueryFactory(fileName);
SetupColumnMapping(data);
SetupTransforms(data);
return data.Worksheet<CollectionImportModel>().ToList();
}
public static IEnumerable<Route> Import(string filename, ClimbrContext context)
{
var excel = new ExcelQueryFactory(filename);
var sheetRoutes = excel.Worksheet<SheetRoute>()
.Where(r => r.Grade != "#REF!" && r.Grade != null)
.ToList();
for (int i = 1; i < sheetRoutes.Count; i++)
{
if (sheetRoutes[i].Line == null || sheetRoutes[i].Line == "#REF!")
{
sheetRoutes[i].Line = sheetRoutes[i - 1].Line;
}
}
var routes = sheetRoutes.Select(
sr => new Route
{
Color = context.Colors.Single(c => c.Name.Equals(sr.Colour, StringComparison.InvariantCultureIgnoreCase)),
Grade = context.Grades.Single(g => g.Name.Equals(sr.Grade, StringComparison.InvariantCultureIgnoreCase)),
Name = sr.Line
})
.ToList();
return routes;
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fileDialog = new OpenFileDialog();
if (fileDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return;
}
var sheet = new ExcelQueryFactory();
sheet.FileName = fileDialog.FileName;
var dinvList = sheet.Worksheet<DINV_HDR>(0);
foreach (var item in dinvList)
{
}
var list = sheet.Worksheet(0).ToList();
foreach (var item in list)
{
}
foreach (var item in sheet.Worksheet(0))
{
}
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog fdlg = new OpenFileDialog();
if (DialogResult.OK == fdlg.ShowDialog())
{
var excel = new ExcelQueryFactory(fdlg.FileName);
var indianaCompanies = from c in excel.Worksheet("Sheet1")
select c;
int ix = 0;
int il = 0;
foreach (var item in indianaCompanies)
{
TblInfor newInfo = new TblInfor();
string content = "<b>" + item[1].ToString().Trim() + "</b> <br />"
+ "" + item[10].ToString().Trim()
+ "<br>Khu vực: " + item[9].ToString().Trim()
+ "<br>ĐC: " + item[4].ToString().Trim()
+ "<br> Số Phòng: " + item[6].ToString().Trim()
+ "<br> ĐT: " + item[2].ToString().Trim() + " - FAX: " + item[3].ToString().Trim()
+ "<br> Email: " + item[5].ToString().Trim()
+ "<br> Chủ đầu tư: " + item[7].ToString().Trim()
+ "<br>Giám đốc: " + item[8].ToString().Trim();
string tag = "Khách sạn, khach san, ks, " + item[11].ToString().Trim() + ", " + item[2].ToString().Trim();
newInfo.CreateDate = DateTime.Now;
newInfo.CreateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100");
newInfo.ID = Guid.NewGuid();
newInfo.Category = Categories.KhachSan;
newInfo.Location = item[9].ToString().Trim();
newInfo.InfoContent = HtmlRemoval.StripTagsRegexCompiled(content);
newInfo.InfoContentHtml = content;
newInfo.InfoTag = tag;
newInfo.InfoTitle = item[1].ToString().Trim() + " " + item[9].ToString().Trim();
newInfo.InfoType = InformationType.NoLimit;
newInfo.Status = InformationStatus.Approved;
newInfo.UpdateDate = DateTime.Now;
newInfo.UpdateUserID = Guid.Parse("b0b6997c-282b-4aff-b286-b0820cdb0100");
try
{
BBLInfo b = new BBLInfo();
b.InsertOrUpdate(newInfo);
ix++;
}
catch (Exception ex)
{
Console.WriteLine("E - > " + ex.Message);
// DevExpress.XtraEditors.XtraMessageBox.Show(DevExpress.LookAndFeel.UserLookAndFeel.Default, this.FindForm(), ex.Message, "Có sự cố", MessageBoxButtons.OK, MessageBoxIcon.Warning);
il++;
}
}
Console.WriteLine(ix + " | " + il);
}
}
public void bad_column_mapping_in_where_clause()
{
var excel = new ExcelQueryFactory(_excelFileName);
excel.AddMapping<CompanyWithCity>(x => x.City, "Town");
var list = (from x in excel.Worksheet<CompanyWithCity>("Sheet1")
where x.City == "Omaha"
select x).ToList();
}
public void GetWorksheetNames_does_not_include_builtin_worksheets()
{
var excel = new ExcelQueryFactory(_excelFileWithBuiltinWorksheets);
var worksheetNames = excel.GetWorksheetNames();
Assert.AreEqual(
"AutoFiltered, ColumnMappings, MoreCompanies, NullCells, Paul's Worksheet, Sheet1",
string.Join(", ", worksheetNames.ToArray()));
}
static void Main(string[] args)
{
var file = @"NamedRange.xlsb";
var excel = new ExcelQueryFactory(file);
var unique = excel.NamedRange("NamedRangeUniqueCell");
var multiple = excel.NamedRange("NamedRangeMultipleCells");
}
//[Test] This test is no longer passing. I believe it has something to do with my computer settings
public void GetWorksheetNames_does_not_include_named_ranges()
{
var excel = new ExcelQueryFactory(_excelFileWithNamedRanges);
var worksheetNames = excel.GetWorksheetNames();
Assert.AreEqual(
"Tabelle1, Tabelle3, WS2",
string.Join(", ", worksheetNames.ToArray()));
}
public ManipuladorExcel(string nombreArchivo)
{
_excel = new ExcelQueryFactory(nombreArchivo)
{
ReadOnly = true,
UsePersistentConnection = true
};
}
private IEnumerable<ExcelRow> GetExcelRowEnumerableFromExcelFile(string filePath)
{
var excel = new ExcelQueryFactory();
excel.FileName = filePath;
IEnumerable<ExcelRow> query = from e in excel.Worksheet<ExcelRow>(0)
select e;
return query;
}
public void range_csv_file_throws_exception()
{
var csvFile = @"C:\ExcelFiles\NoHeaderRange.csv";
var excel = new ExcelQueryFactory(csvFile);
var companies = (from c in excel.WorksheetRangeNoHeader("B9", "E15")
select c).ToList();
}
public void bad_column_in_sum_aggregate()
{
var excel = new ExcelQueryFactory(_excelFileName);
excel.AddMapping<CompanyWithCity>(x => x.EmployeeCount, "Employees");
var list = (from x in excel.Worksheet<CompanyWithCity>("Sheet1")
select x)
.Sum(x => x.EmployeeCount);
}
public void ImportCdmaParameters(string path)
{
var repo = new ExcelQueryFactory { FileName = path };
BtsExcels = (from c in repo.Worksheet<BtsExcel>("基站级")
select c).ToList();
CdmaCellExcels = (from c in repo.Worksheet<CdmaCellExcel>("小区级")
select c).ToList();
}
public void use_row_no_header_where_null()
{
var factory = new ExcelQueryFactory(_excelFileName + "x");
var companies = from c in factory.WorksheetRangeNoHeader("A1", "D4", "NullCells")
where c[2] == null
select c;
Assert.AreEqual(2, companies.Count(), "Count");
}
public void use_row_where_null()
{
var factory = new ExcelQueryFactory(_excelFileName + "x");
var companies = from c in factory.NamedRange("NullCellCompanies")
where c["EmployeeCount"] == null
select c;
Assert.AreEqual(2, companies.Count(), "Count");
}
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()));
}
private static void AddLibrariesToDataStore(ExcelQueryFactory file)
{
var allLibraries = from f in file.Worksheet<FlatLibrary>()
select FlatLibraryToLibrary(f);
var libraryList = allLibraries.ToList().Distinct();
Console.WriteLine(allLibraries.Count());
}
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");
}
public void GetWorksheetNames_returns_worksheet_names()
{
var excel = new ExcelQueryFactory(_excelFileName);
var worksheetNames = excel.GetWorksheetNames();
Assert.AreEqual(
"ColumnMappings, IMEX Table, More Companies, Null Dates, Range1, Sheet1, TrimSpaces",
string.Join(", ", worksheetNames.ToArray()));
}
public XLSLoader(string fileName,
string sheetName = "бакалавр",
TeachersModelContainer context = null)
{
if (context == null)
{
_context = new ModelContext();
}
else
{
_context = context;
}
_excel = new ExcelQueryFactory(fileName);
SheetName = sheetName;
_rawData = (from c in _excel.WorksheetNoHeader(SheetName)
select c).ToList();
_moduleOffsets = new Dictionary <Module, List <int> >();
_groupOffsets = new Dictionary <Group, int>();
}
public void Null_ConstantExpression_in_where_expression_should_not_throw_exception()
{
// The C# 6 compiler made some changes to the expression tree output.
// This changes are largely transparent to consumers if they adhere
// to the Liskov Substitution Principle. This test is to specifically
// address a bug in which the code didn't allow the derived TypedConstantExpression
// to be substituted for a ConstantExpression. This resulted in a downstream InvalidCastException
// when the LinqToExcel invoked Converter.ChangeType since TypedConstantExpression
// does not implement IConvertible.
var excel = new ExcelQueryFactory(_excelFileName);
excel.AddMapping <Company>(x => x.IsActive, "Active");
var companies = (from c in excel.Worksheet <CompanyNullable>()
where c.Name != null
select c).ToList();
Assert.IsNotNull(companies);
}
public void IAllowFieldTypeConversionExceptions_GathersExceptions()
{
var excel = new ExcelQueryFactory(_excelFileName, new LogManagerFactory());
excel.AddMapping <Company>(x => x.IsActive, "Active");
var companies = (from c in excel.Worksheet <CompanyBadWithAllowFieldTypeConversionExceptions>()
where c.Name == "ACME"
select c).ToList();
Assert.AreEqual(1, companies.Count);
var company = companies[0];
Assert.AreEqual("ACME", company.Name);
Assert.AreEqual(2, company.FieldTypeConversionExceptions.Count);
Assert.AreEqual("CEO", company.FieldTypeConversionExceptions[0].ColumnName);
Assert.AreEqual("EmployeeCount", company.FieldTypeConversionExceptions[1].ColumnName);
}
public void WorkSheetNamesAreDecodedCorrectly()
{
var fileName = Path.Combine(_filesDirectory, "WorksheetNames.xlsx");
var workbook = new ExcelQueryFactory(fileName, new LogManagerFactory());
var worksheetNames = workbook.GetWorksheetNames();
CollectionAssert.AreEqual(
new [] {
" ' ",
"$woot$",
"Emb$dded",
"Ends with $'\"",
"Ends with a $",
"Has a $ in it"
},
worksheetNames
);
}
public int GetRowFromEXL(string filepath, string Usertype)
{
//var pathToExcelFile = @"D:\pegqa\TestAutomationProject\PegasusTests\Screenshots\EXLFile\AgentImport.xlsx";
var excelFile = new ExcelQueryFactory(filepath).Worksheet(0);
var artistAlbums = from a in excelFile select a;
int i = 0;
foreach (var a in artistAlbums)
{
if (a["UserType"] == Usertype)
{
i++;
Console.WriteLine("New Check");
}
}
Thread.Sleep(1000);
return(i);
}
public void IAllowFieldTypeConversionExceptions_NoExceptionsWhenFieldsAreGood()
{
var excel = new ExcelQueryFactory(_excelFileName, new LogManagerFactory());
excel.AddMapping <Company>(x => x.IsActive, "Active");
var companies = (from c in excel.Worksheet <CompanyGoodWithAllowFieldTypeConversionExceptions>()
where c.Name == "ACME"
select c).ToList();
Assert.AreEqual(1, companies.Count);
var company = companies[0];
Assert.AreEqual("ACME", company.Name);
Assert.AreEqual("Bugs Bunny", company.CEO);
Assert.AreEqual(25, company.EmployeeCount);
Assert.AreEqual(0, company.FieldTypeConversionExceptions.Count);
}
public IEnumerable <CI004_RFDS_SECTOR_IN_CSS> GetListCI004_RFDS_SECTOR_IN_CSS(string filename)
{
var excel = new ExcelQueryFactory();
excel.FileName = filename;
var x = excel.GetWorksheetNames();
var query = (from s in excel.WorksheetRange <CI004_RFDS_SECTOR_IN_CSS>("A1", "XFD1048576", 3) select s).ToList();
//List<CI004_RFDS_SECTOR_IN_CSS> lstRFDS = new List<CI004_RFDS_SECTOR_IN_CSS>();
//foreach (CI004_RFDS_SECTOR_IN_CSS item in query)
//{
// lstRFDS.Add(new CI004_RFDS_SECTOR_IN_CSS
// {
// USID = item.USID,
// RFDS_NAME = item.RFDS_NAME,
// PROGRAM_TYPE = item.PROGRAM_TYPE,
// TECHNOLOGY = item.TECHNOLOGY,
// CARRIER = item.CARRIER,
// SECTOR = item.SECTOR,
// USEID = item.USEID,
// RBSID = item.RBSID,
// DELETE_SOFT_SECTOR = item.DELETE_SOFT_SECTOR,
// CTS_COMMONID = item.CTS_COMMONID,
// SOFT_SECTORID = item.SECTOR,
// CELL_NUMBER_LTE = item.CELL_NUMBER_LTE,
// CID_SAC = item.CID_SAC,
// RFDSID = item.RFDSID,
// SITEMASTER = item.SITEMASTER,
// SECTOR_LATITUDE = item.SECTOR_LATITUDE,
// SECTOR_LONGITUDE = item.SECTOR_LONGITUDE,
// RBSS_ISACTIVE = item.RBSS_ISACTIVE,
// REMARKS = item.REMARKS,
// SPECTRUM_BUCKET_1 = item.SPECTRUM_BUCKET_1,
// SPECTRUM_BUCKET_2 = item.SPECTRUM_BUCKET_2,
// SPECTRUM_USID = item.SPECTRUM_USID
// });
//}
//return lstRFDS;
return(query);
}
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());
}
}
}
}
public void xls_readonly_with_Ace_DatabaseEngine_connection_string()
{
var excel = new ExcelQueryFactory("spreadsheet.xls")
{
DatabaseEngine = ExcelDatabaseEngine.Ace,
ReadOnly = true
};
var companies = from c in excel.Worksheet()
select c;
try { companies.GetEnumerator(); }
catch (OleDbException) { }
var expected =
$@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={"spreadsheet.xls"
};Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;READONLY=TRUE""";
Assert.AreEqual(expected, GetConnectionString());
}
public void GetNamedRanges()
{
var excel = new ExcelQueryFactory(_excelFileWithNamedRanges);
string sheetName = excel.GetWorksheetNames().First();
Assert.AreEqual(sheetName, "Tabelle1");
var namedRanges = excel.GetNamedRanges("NameCell");
// ExcelQueryArgs args = new ExcelQueryArgs
// {
// FileName = excel.FileName,
// WorksheetName = "NameCell",
// ReadOnly = true
// };
// var namedRanges = ExcelUtilities.GetNamedRanges(args);
Assert.AreEqual(
"NameCell",
string.Join(", ", namedRanges.ToArray()));
}
public void no_header_readonly_connection_string_suppress_transactionscope()
{
var excel = new ExcelQueryFactory("spreadsheet.xls", new LogManagerFactory());
excel.ReadOnly = true;
excel.OleDbServices = Query.OleDbServices.AllServicesExceptPoolingAndAutoEnlistment;
var companies = from c in excel.WorksheetNoHeader()
select c;
try { companies.GetEnumerator(); }
catch (OleDbException) { }
string expected = string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};OLE DB Services=-4;Extended Properties=""Excel 12.0;HDR=NO;IMEX=1;READONLY=TRUE""",
"spreadsheet.xls"
);
Assert.AreEqual(expected, GetConnectionString());
}
/// <summary>
/// Card massive import procedure using LinqToExcel
/// </summary>
private void ImportCards()
{
var fileName = string.Concat(Server.MapPath(TmpDir), DateTime.Now.ToString("yyyyMMdd-HHmmss"), ".xls");
try
{
fuImportData.SaveAs(fileName);
var linea = ddlBaseTarjeta.Selected > 0 ? DAOFactory.LineaDAO.FindById(ddlBaseTarjeta.Selected) : null;
var empresa = ddlDistritoTarjeta.Selected > 0 ? DAOFactory.EmpresaDAO.FindById(ddlDistritoTarjeta.Selected) : linea != null ? linea.Empresa : null;
var file = new ExcelQueryFactory(fileName);
var data = from c in file.Worksheet <TarjetaImportador>("Importar") select c;
foreach (var cardImp in data)
{
var card = SetCardDefaultValues(cardImp, empresa, linea);
if (string.IsNullOrEmpty(card.Numero) || string.IsNullOrEmpty(card.Pin))
{
continue;
}
var chofer = !string.IsNullOrEmpty(cardImp.Legajo) ? DAOFactory.EmpleadoDAO.GetByLegajo(ddlDistritoTarjeta.Selected, ddlBaseTarjeta.Selected, cardImp.Legajo.Trim()) : null;
if (chofer != null)
{
chofer.Tarjeta = card;
DAOFactory.EmpleadoDAO.SaveOrUpdate(chofer);
}
else
{
DAOFactory.TarjetaDAO.SaveOrUpdate(card);
}
}
infoLabel1.Mode = InfoLabelMode.INFO;
infoLabel1.Text = @"Se ha finalizado con exito la importacion de Tarjetas.";
}
catch (Exception e) { ShowError(new Exception(CultureManager.GetError(FileFormat), e)); }
finally { File.Delete(fileName); }
}
public List <Student> GetDataFromExcel()
{
List <Student> list = new List <Student>();
Console.WriteLine("Path to excel file :");
string pathToExcelFile = Console.ReadLine();
string ext = Path.GetExtension(pathToExcelFile);
if (ext.ToLower().Equals(".xls") || ext.ToLower().Equals(".xlsx"))
{
Console.WriteLine("Sheet Name :");
string sheetName = Console.ReadLine();
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var dataExcel = from a in excelFile.Worksheet(sheetName) select a;
foreach (var a in dataExcel)
{
try
{
string info = $"ID:{a[0].Value} Name: {a[1].Value}; AvgMark: {a[2].Value}; AccumulationCredit: {a[3].Value}; BirthDay{a[4].Value}";
Console.WriteLine(info);
var ID = a[0].Cast <int>();
var Name = a[1].Value.ToString();
var AvgMark = a[2].Cast <float>();
var AccumulationCredit = a[3].Cast <int>();
var BirthDay = a[4].Cast <DateTime>();
Student student = new Student(ID, Name, BirthDay, AvgMark, AccumulationCredit);
//tree.Insert(new Node<Student>(student));
list.Add(student);
}
catch (Exception ex)
{
Console.WriteLine("Something wrong with data !Please try again !");
}
}
return(list);
}
else
{
Console.WriteLine("This isn't a excel file!");
return(null);
}
}
private void Button_Click(object sender, EventArgs e)
{
string path = "";
DialogResult fileToOpenDialog = openFileDialog1.ShowDialog();
if (fileToOpenDialog == DialogResult.OK) // Test result.
{
path = openFileDialog1.FileName;
}
textBox1.Text = path;
List <Player> players = new List <Player>();
string pathToExcelFile = textBox1.Text;
string sheetName = "Arkusz1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var playersInExcel = from a in excelFile.Worksheet(sheetName) select a;
foreach (var a in playersInExcel)
{
players.Add(
new Player()
{
name = a["Name"],
handicap = Convert.ToInt32(a["handicap"]),
card = new Card()
{
scores = new int[] { Convert.ToInt32(a["h1"]), Convert.ToInt32(a["h2"]), Convert.ToInt32(a["h3"]), Convert.ToInt32(a["h4"]), Convert.ToInt32(a["h5"]), Convert.ToInt32(a["h6"]), Convert.ToInt32(a["h7"]), Convert.ToInt32(a["h8"]), Convert.ToInt32(a["h9"]), Convert.ToInt32(a["h10"]), Convert.ToInt32(a["h11"]), Convert.ToInt32(a["h12"]), Convert.ToInt32(a["h13"]), Convert.ToInt32(a["h14"]), Convert.ToInt32(a["h15"]), Convert.ToInt32(a["h16"]), Convert.ToInt32(a["h17"]), Convert.ToInt32(a["h18"]) }
}
}
);
}
Course course = new Course();
Init(course);
var prog = new Calculate();
result = prog.Go(course, players);
}
/*********
* Function: cmGetCellNames
* Parameters: none
* Returns: cmCell array of cells read from allCells.xlsx file
* Operation Summary:
* Read the xlsx file and edit Huawei cells to cellVendorType Huawei and then return the array
*********/
public static cmCell[] cmGetCellNames()
{
//Opening allCells.xlsx file with read-only mode and mapping to the cmCell Class
var cellNamesFile = new ExcelQueryFactory("excelFiles/allCells.xlsx");
cellNamesFile.ReadOnly = true;
cellNamesFile.UsePersistentConnection = true;
cellNamesFile.AddMapping <cmCell>(x => x.cellName, "Cell");
cmCell[] cellNames;
//the try and catch is for closing connection for allCells.xlsx file
try
{
//Get the Huawei cell names from the file and change to array
var cellNamesHuawei = (from x in cellNamesFile.Worksheet <cmCell>("HU Cells")
where x.cellName != ""
select x).ToArray <cmCell>();
//Get the Ericsson cell names from the file and change to array
var cellNamesEricsson = (from x in cellNamesFile.Worksheet <cmCell>("ER Cells")
where x.cellName != ""
select x).ToArray <cmCell>();
//Change the vendor type for Huawei cells to Huawei as the default vendor type for no-parameters cmCell constructor is Ericsson
for (int i = 0; i < cellNamesHuawei.Length; i++)
{
cellNamesHuawei[i].cellVendor = cellVendorType.Huawei;
}
//Make Ericsson cell names all uppercase
for (int i = 0; i < cellNamesEricsson.Length; i++)
{
cellNamesEricsson[i].cellName = cellNamesEricsson[i].cellName.ToUpper();
}
//Concatinate the two arrays
cellNames = new cmCell[cellNamesEricsson.Length + cellNamesHuawei.Length];
cellNamesEricsson.CopyTo(cellNames, 0);
cellNamesHuawei.CopyTo(cellNames, cellNamesEricsson.Length);
}
finally
{
//Close connection
cellNamesFile.Dispose();
}
return(cellNames);
}
private bool ProcessFile(string securityID, FileUploadType fileType)
{
try
{
// Create connection to excel file
ExcelQueryFactory connection = new ExcelQueryFactory(filePath);
//Query a worksheet with a header row
var rows = from row in connection.Worksheet(0)
select row;
// Fill stock list
foreach (var row in rows)
{
var result = new StockPriceRepository().Add(new StockPriceVm
{
SecurityId = securityID,
Date = Convert.ToDateTime(row[0]),
Open = Convert.ToDecimal(row[1]),
High = Convert.ToDecimal(row[2]),
Low = Convert.ToDecimal(row[3]),
Close = Convert.ToDecimal(row[4]),
WAP = Convert.ToDecimal(row[5]),
NoOfShares = Convert.ToInt32(row[6]),
NoOfTrades = Convert.ToInt32(row[7]),
TotalTurnOver = Convert.ToInt32(row[8]),
DeliverableQty = Convert.ToInt32(row[9])
});
if (!result)
{
return(false);
}
}
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 指定excel table 名稱，
/// 執行EXCEL 裡面的SQL 語法。
/// </summary>
/// <param name="sTable"></param>
private void ExcelQueryByUpdateData(string sTable)
{
//設定EXCEL
var exc = new ExcelQueryFactory(sPath + "\\setting.xlsx");
var sQuery = from x in exc.Worksheet <Setting>("setting")
where x.Table == sTable
select x;
foreach (var name in sQuery)
{
if (name.InitialCtr == null || name.changeCtr == null)
{
lMessage.Text = "設定檔內容有缺無法更新"; break;
}
comm.Del_DBTable(name.InitialCtr);
comm.Insert_SaveDB(name.Table, name.changeCtr);
}
lMessage.Text = "料件檔 - 更新成功\n-完成豪秒數 : " + comm.GET_Run_Timer + "\n-共" + comm.GET_Rows_Coumt + "筆";
}
/// <summary>
/// Uploads the excel.
/// </summary>
/// <param name="excelFile">The excel file.</param>
/// <returns></returns>
public string UploadExcel(HttpPostedFileBase excelFile)
{
string result = string.Empty;
var excel = new ExcelQueryFactory();
excel.FileName = excelFile.FileName;
var details = from x in excel.Worksheet <LevelView>() select x;
foreach (var level in details)
{
int companyId = (int)this.session.GetSessionValue(SessionKey.CompanyId);
level.CompanyId = companyId;
result = this.levelRepository.SaveLevelInfo(level);
}
return(result);
}
private void LoadColumns()
{
var excel = new ExcelQueryFactory(FileName);
var columns = excel.GetColumnNames(CurrentWorkSheet).OrderBy(col => col);
BindColumns(cbDescripcion, columns, true, "Descripcion");
BindColumns(cbCodigo, columns, true, "Codigo");
BindColumns(cbCalle, columns, true, "Calle");
BindColumns(cbAltura, columns, true, "Altura");
BindColumns(cbEsquina, columns, true, "Esquina");
BindColumns(cbPartido, columns, true, "Partido");
BindColumns(cbProvincia, columns, true, "Provincia");
BindColumns(cbLatitud, columns, true, "Latitud");
BindColumns(cbLongitud, columns, true, "Longitud");
BindColumns(cbDesde, columns, true, "VigenciaDesde");
BindColumns(cbHasta, columns, true, "VigenciaHasta");
lblDirs.Text = string.Format(Reporte, 0, 0, "0.00", 0, "0.00", 0, "0.00", 0, excel.Worksheet(CurrentWorkSheet).Count(), 0, "0.00");
SetProgressBar(0);
}
public void Test_Read_Sheet2()
{
_excelFileName = Path.Combine(_excelFilesDirectory, "抱怨量2.xls");
_repo = new ExcelQueryFactory {
FileName = _excelFileName
};
var info = (from c in _repo.Worksheet <ComplainExcel>(_worksheetName)
select c).ToList();
Assert.IsNotNull(info);
Assert.AreEqual(info.Count, 15);
info[0].Longtitute.ShouldBe(112.89, 1E-6);
info[0].Lattitute.ShouldBe(23.3457);
info[1].Longtitute.ShouldBe(0);
info[2].Lattitute.ShouldBe(0);
info[4].Longtitute.ShouldBe(113.1872);
info[4].Lattitute.ShouldBe(23.0057);
info[1].CandidateDistrict.ShouldBe("");
info[2].SerialNumber.ShouldBe("2016022110007894");
}
public void Test_Read_Sheet3()
{
_excelFileName = Path.Combine(_excelFilesDirectory, "抱怨量3.xls");
_repo = new ExcelQueryFactory {
FileName = _excelFileName
};
var info = (from c in _repo.Worksheet <ComplainExcel>(_worksheetName)
select c).ToList();
Assert.IsNotNull(info);
Assert.AreEqual(info.Count, 12);
info[0].Longtitute.ShouldBe(0);
info[0].Lattitute.ShouldBe(0);
info[1].Longtitute.ShouldBe(0);
info[2].Lattitute.ShouldBe(0);
info[3].Longtitute.ShouldBe(113.132);
info[3].Lattitute.ShouldBe(22.0023);
info[1].CandidateDistrict.ShouldBe("");
info[2].SerialNumber.ShouldBe("2016040710008333");
}
private void nhapttlop_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
string pathToExcelFile = ""
+ @openFileDialog1.FileName;
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var docexcel = from a in excelFile.Worksheet <Lop>(sheetName) select a;
foreach (var a in docexcel)
{
Lop lop = new Lop();
lop.addlop(a.Malop, a.Tenlop, a.Makhoa);
}
MessageBox.Show("Đã đọc file excel thành công");
excelFile.Dispose();
}
/**************************************************************/
//CARGA DE DATOS DE CLIENTES - EMPRESA
/**************************************************************/
#region CARGA DE DATOS DE CLIENTES - EMPRESA
public List <Comunes.InterproteccionObjetos> CargaDatosEmpresa(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("Empresa")
let item = new Comunes.InterproteccionObjetos
{
EmpresaNombre = row["EmpresaNombre"].Cast <string>(),
EmpresaDireccion = row["EmpresaDireccion"].Cast <string>(),
EmpresaTipoEmpresa = row["EmpresaTipoEmpresa"].Cast <string>(),
EmpresaTipoBanco = row["EmpresaTipoBanco"].Cast <string>(),
EmpresaRFC = row["EmpresaRFC"].Cast <string>(),
EmpresaNombreContatoUno = row["EmpresaNombreContatoUno"].Cast <string>(),
EmpresaCorreoContatoUno = row["EmpresaCorreoContatoUno"].Cast <string>(),
EmpresaTelefonoContatoUno = row["EmpresaTelefonoContatoUno"].Cast <string>(),
EmpresaNombreContatoDos = row["EmpresaNombreContatoDos"].Cast <string>(),
EmpresaCorreoContatoDos = row["EmpresaCorreoContatoDos"].Cast <string>(),
EmpresaTelefonoContatoDos = row["EmpresaTelefonoContatoDos"].Cast <string>(),
EmpresaNombreContatoTres = row["EmpresaNombreContatoTres"].Cast <string>(),
EmpresaCorreoContatoTres = row["EmpresaCorreoContatoTres"].Cast <string>(),
EmpresaTelefonoContatoTres = row["EmpresaTelefonoContatoTres"].Cast <string>(),
EmpresaNotas = row["EmpresaNotas"].Cast <string>(),
// SECCION DE CLIENTES - CONSULTA EMPRESAS
EmpresaCampoBusquedaEmpresa = row["EmpresaCampoBusquedaEmpresa"].Cast <string>(),
EmpresaCampoBusquedaContacto = row["EmpresaCampoBusquedaContacto"].Cast <string>(),
EmpresaFiltroTipoEmpresa = row["EmpresaFiltroTipoEmpresa"].Cast <string>(),
EmpresaFiltroTipoBanco = row["EmpresaFiltroTipoBanco"].Cast <string>(),
// SECCION GENERAR CÓDIGOS DE ACTIVACIÓN - EMPRESAS
EmpresaGenerarCodigosActivacionCodigoFijo = row["CodigoFijo"].Cast <string>(),
EmpresaGenerarCodigosActivacionCantidadClaves = row["CantidadClaves"].Cast <string>(),
EmpresaGenerarCodigosActivacionTipoPlan = row["TipoPlan"].Cast <string>(),
EmpresaGenerarCodigosActivacionServicioDesde = row["ServicioDesde"].Cast <string>(),
EmpresaGenerarCodigosActivacionServicioHasta = row["ServicioHasta"].Cast <string>(),
EmpresaGenerarCodigosActivacionCodigoMovil = row["CodigoMovil"].Cast <string>(),
EmpresaGenerarCodigosActivacionActivarAntesDel = row["ActivarAntesDel"].Cast <string>(),
EmpresaGenerarCodigosActivacionPeriodoVigencia = row["PeriodoVigencia"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
public IEnumerable <LALTE> GetListLALTE(string filename)
{
var excel = new ExcelQueryFactory();
excel.FileName = filename;
var x = excel.GetWorksheetNames();
var query = (from s in excel.WorksheetRange <LALTE>("A2", "XFD1048576", 0) select s).ToList();
return(query);
//List<LALTE> lstLALTE = new List<LALTE>();
//foreach (LALTE item in query)
//{
// lstLALTE.Add(new LALTE
// {
// _118d_5m_20_54004s = item._118d_5m_20_54004s,
// Section5_E911Information = item.Section5_E911Information,
// USID = item.USID,
// RFDS_NAME = item.RFDS_NAME,
// Program_Type = item.Program_Type,
// Technology = item.Technology,
// SECTOR = item.SECTOR,
// PSAP_ID = item.PSAP_ID,
// E911_PHASE = item.E911_PHASE,
// LMU_REQUIRED = item.LMU_REQUIRED,
// ESRN = item.ESRN,
// DATE_LIVE_PH1 = item.DATE_LIVE_PH1,
// DATE_LIVE_PH2 = item.DATE_LIVE_PH2,
// RFDS_ID = item.RFDS_ID,
// _188167 = item._188167,
// CLL02285 = item.CLL02285,
// LTE_Next_Carrier_2018 = item.LTE_Next_Carrier_2018,
// EAST_MISSION_DRIVE_841 = item.EAST_MISSION_DRIVE_841,
// SAN_GABRIEL = item.SAN_GABRIEL,
// _91776 = item._91776,
// _34d_5m25_81008s = item._34d_5m25_81008s
// });
//}
//return lstLALTE;
}
public string ImportOnlineDemand(string path)
{
var factory = new ExcelQueryFactory {
FileName = path
};
var stats = (from c in factory.Worksheet <OnlineSustainExcel>("汇总表") select c).ToList();
var count =
_onlineSustainRepository.Import <IOnlineSustainRepository, OnlineSustain, OnlineSustainExcel, Town>(stats, _towns,
(towns, stat) =>
{
if (!string.IsNullOrEmpty(stat.Town))
{
var candidateTown =
towns.FirstOrDefault(x => x.DistrictName == stat.District && x.TownName == stat.Town);
if (candidateTown != null)
{
return(candidateTown.Id);
}
}
var candidateTowns = towns.Where(x => x.DistrictName == stat.District).ToList();
if (!candidateTowns.Any())
{
candidateTowns = _towns;
}
var town = ((string.IsNullOrEmpty(stat.Site)
? null
: candidateTowns.FirstOrDefault(x => stat.Site.Contains(x.TownName))) ??
(string.IsNullOrEmpty(stat.Address)
? null
: candidateTowns.FirstOrDefault(x => stat.Address.Contains(x.TownName)))) ??
(string.IsNullOrEmpty(stat.Phenomenon)
? null
: candidateTowns.FirstOrDefault(x => stat.Phenomenon.Contains(x.TownName)));
return(town?.Id ?? candidateTowns.First().Id);
});
var count2 =
_processRepository.Import <IComplainProcessRepository, ComplainProcess, OnlineSustainExcel>(stats);
return("完成在线支撑信息导入" + count + "条; " + "处理记录" + count2 + "条");
}
public ActionResult Import(string filename)
{
ViewBag.filename = filename;
var filepath = Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name + "/" + filename);
if (!System.IO.File.Exists(filepath))
{
Session["FlashMessage"] = "File not found.";
return(View());
}
try
{
int count = 0;
var excel = new ExcelQueryFactory(filepath);
var sheetnames = excel.GetWorksheetNames();
var activities = from c in excel.Worksheet <StudentActivity>(sheetnames.First())
select c;
foreach (var activity in activities)
{
db.StudentActivities.Add(activity);
count++;
}
db.SaveChanges();
Session["FlashMessage"] = count + " record(s) successfully imported.";
//clear files uploaded after import
if (Directory.Exists(Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name)))
{
var files = Directory.GetFiles(Server.MapPath("~/App_Data/Import/StudentActivity/" + User.Identity.Name));
foreach (var file in files)
{
System.IO.File.Delete(file);
}
}
}
catch (Exception e)
{
Session["FlashMessage"] = "Failed to import activities from excel file. <br/><br/>" + HttpUtility.JavaScriptStringEncode(e.Message);
return(View());
}
return(RedirectToAction("Import"));
}
private ExcelDocument _parseExcelData(ExcelDocument excelDocument)
{
var returnValue = new ExcelDocument();
returnValue = excelDocument;
returnValue.Data = new Dictionary <int, List <ExcelColData> >();
//check if excel document and had data into object
if (!string.IsNullOrEmpty(excelDocument.ExcelDocumentPath) && excelDocument.Use == true)
{
string pathToExcelFile = excelDocument.ExcelDocumentPath;
var excel = new ExcelQueryFactory(pathToExcelFile);
var excelData = (from c in excel.Worksheet(excelDocument.WorkSheetName)
select c);
excelDocument.Data = new Dictionary <int, List <ExcelColData> >();
int rowIndex = 0;
foreach (var data in excelData)
{
List <ExcelColData> listExcelColData = new List <ExcelColData>();
int colIndex = 0;
foreach (var itemData in data)
{
ExcelColData excelColData = new ExcelColData();
excelColData.ColIndex = colIndex;
excelColData.Data = itemData.ToString();
listExcelColData.Add(excelColData);
colIndex++;
}
returnValue.Data.Add(rowIndex, listExcelColData);
rowIndex++;
}
}
return(returnValue);
}
/// <summary> 设置xls 映射关系
/// </summary>
/// <param name="userXLS"></param>
/// <returns> 错误列 集合
/// </returns>
private static List <string> SetXlsMapping <VMdl>(ExcelQueryFactory userXLS)
{
List <string> errColumns = new List <string>();
//xls现有数据字段
var firstSheet = userXLS.GetWorksheetNames().First <string>();
List <string> cnlist = userXLS.GetColumnNames(firstSheet).ToList <string>();
Type type = typeof(VMdl);
var ProArr = type.GetProperties();
foreach (var pro in ProArr)
{
var attributeArr = pro.GetCustomAttributes(true);
var title = pro.Name;
var name = pro.Name;
var mapAttribute = attributeArr.OfType <MapFromAttribute>().FirstOrDefault();
if (mapAttribute != null)
{
title = mapAttribute.ColumnName;
}
var requiredAttri = attributeArr.OfType <NecessaryColumnAttribute>().FirstOrDefault();
if (requiredAttri != null)
{
if (!cnlist.Exists(cn => cn == title)) //不存在
{
errColumns.Add(title);
continue;
}
}
if (mapAttribute != null)
{
userXLS.AddMapping(name, title);
}
}
return(errColumns);
}
public IEnumerable <CI004_Waterfall> GetListCI004Waterfall(string filename)
{
var excel = new ExcelQueryFactory();
excel.FileName = filename;
var x = excel.GetWorksheetNames();
var query = (from s in excel.WorksheetRange <CI004_Waterfall>("A2", "XFD1048576", 0) select s).ToList();
return(query);
//List < CI004_Waterfall > lstWaterfall = new List<CI004_Waterfall>();
//foreach (CI004_Waterfall item in query)
//{
// lstWaterfall.Add(new CI004_Waterfall
// {
// USID_SPECTRUM = item.USID_SPECTRUM,
// PACE_NUMBER = item.PACE_NUMBER,
// SITE_NUMBER = item.SITE_NUMBER,
// PACE_NAME = item.PACE_NAME,
// COUNTY = item.COUNTY,
// PRODUCT_SUBGROUP = item.PRODUCT_SUBGROUP,
// USID = item.USID,
// CI004_FORECAST = item.CI004_FORECAST,
// CI004_ACTUAL = item.CI004_ACTUAL,
// CI003_FORECAST = item.CI003_FORECAST,
// CI003_ACTUAL = item.CI003_ACTUAL,
// SPECTRUM = item.SPECTRUM,
// FUNDING_LEVEL = item.FUNDING_LEVEL,
// RFDS_ID = item.RFDS_ID,
// RFDS_STATE_STATUS = item.RFDS_STATE_STATUS,
// SPECTRUM_BUCKET = item.SPECTRUM_BUCKET,
// PLAN_YEAR = item.PLAN_YEAR,
// NOT_IN_CSS = item.NOT_IN_CSS,
// MISSING_COORDINATES = item.MISSING_COORDINATES,
// SECTOR_IN_CSS = item.SECTOR_IN_CSS,
// FINAL_REMARKS = item.FINAL_REMARKS
// });
//}
//return lstWaterfall;
}
//Metodo para extraer la informacion del excel para los casos de prueba de empleados.
public List <Comunes.InterproteccionObjetos> CargaDatosEmpleados(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("Empleados")
let item = new Comunes.InterproteccionObjetos
{
TestCase = row["CasoPrueba"].Cast <string>(),
Usuario = row["Usuario"].Cast <string>(),
Contrasenia = row["Password"].Cast <string>(),
Url = row["Url"].Cast <string>(),
ResultadoEsperado = row["ResultadoEsperado"].Cast <string>(),
Navegador = row["Browser"].Cast <string>(),
NombreUsuario = row["NombreUsuario"].Cast <string>(),
CorreoElectronico = row["CorreoElectronico"].Cast <string>(),
Rol = row["Rol"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
