public List <PersonalExcelLayout> getPersonalDatos(String rutaDelArchivoExcel)
{
var book = new ExcelQueryFactory(rutaDelArchivoExcel);
book.DatabaseEngine = DatabaseEngine.Ace;
book.ReadOnly = true;
var artistAlbums = from a in book.Worksheet("datos") select a;
foreach (var a in artistAlbums)
{
string artistInfo = "Artist Name: {0}; Album: {1}";
Console.WriteLine(string.Format(artistInfo, a["Nombre"], a["ApellidoMaterno"]));
}
var datos = (from row in book.Worksheet("datos")
let item = new PersonalExcelLayout
{
nombre = row["Nombre"].Cast <String>(),
apellidoMaterno = row["ApellidoMaterno"].Cast <String>(),
apellidoPaterno = row["ApellidoPaterno"].Cast <String>(),
edad = row["Edad"].Cast <int>()
}
select item).ToList();
book.Dispose();
return(datos);
}
public List <Comunes.InterproteccionObjetos> CargaDatosPlanServicio(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("PlanesDeServicio")
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>(),
NombrePlan = row["NombrePlan"].Cast <string>(),
CostoPlan = row["CostoPlan"].Cast <string>(),
Descripcion = row["Descripcion"].Cast <string>(),
ConsutltaReporte = row["ConsutltaReporte"].Cast <string>(),
ConsultaAlertas = row["ConsultaAlertas"].Cast <string>(),
ServicioTarjeta = row["ServicioTarjeta"].Cast <string>(),
Tipo = row["Tipo"].Cast <string>(),
NombreAnterior = row["NombreAnterior"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
/// <summary>
/// Fetch vehicle information based on a specific version
/// </summary>
/// <param name="versao"></param>
/// <returns></returns>
internal static Car FetchCar(Version versao)
{
ExcelQueryFactory excel = null;
Car viatura = null;
try
{
excel = new ExcelQueryFactory(excelSpreadSheet);
viatura = (Car)((from c in excel.Worksheet <Car>(workSheet)
where c.BrandDescription == versao.BrandDescription.Trim() && c.ModelDescription == versao.ModelDescription.Trim() &&
c.VersionDescription == versao.VersionDescription.Trim()
select c).FirstOrDefault());
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw new System.IO.IOException("Problems reading the excel file.");
}
finally
{
if (excel != null)
{
excel.Dispose();
}
}
return(viatura);
}
/// <summary>
/// Fetch versions available for a specific model
/// </summary>
/// <param name="modelo"></param>
/// <returns></returns>
internal static List <Version> FetchVersions(Model modelo)
{
ExcelQueryFactory excel = null;
List <Version> versoes = null;
try
{
excel = new ExcelQueryFactory(excelSpreadSheet);
versoes = new List <Version>(from c in excel.Worksheet <Version>(workSheet)
where c.BrandDescription == modelo.BrandDescription.Trim() && c.ModelDescription == modelo.ModelDescription.Trim()
select c).ToList();
versoes = new List <Version>(versoes.Distinct());
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw new System.IO.IOException("Problems reading the excel file.");
}
finally
{
if (excel != null)
{
excel.Dispose();
}
}
return(versoes);
}
/// <summary>
/// Fetch models available on the excel file
/// </summary>
/// <param name="marca"></param>
/// <returns></returns>
internal static List <Model> FetchModels(Brand marca)
{
ExcelQueryFactory excel = null;
List <Model> modelos = null;
try
{
excel = new ExcelQueryFactory(excelSpreadSheet);
modelos = new List <Model>(from c in excel.Worksheet <Model>(workSheet)
where c.BrandDescription == marca.BrandDescription.Trim()
select c).ToList();
modelos = new List <Model>(modelos.Distinct());
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw new System.IO.IOException("Problems reading the excel file.");
}
finally
{
if (excel != null)
{
excel.Dispose();
}
}
return(modelos);
}
/// <summary>
/// Fetch brands available in the excel file
/// </summary>
/// <returns></returns>
internal static List <Brand> FetchBrands()
{
ExcelQueryFactory excel = null;
List <Brand> marcas = null;
try
{
excel = new ExcelQueryFactory(excelSpreadSheet);
marcas = new List <Brand>(from c in excel.Worksheet <Brand>(workSheet)
select c).ToList();
marcas = new List <Brand>(marcas.Distinct());
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
throw new System.IO.IOException("Problems reading the excel file.");
}
finally
{
if (excel != null)
{
excel.Dispose();
}
}
return(marcas);
}
public List <EntidadHojaExcel> ToEntidadHojaExcel(string pathDelFicheroExcel)
{
var book = new ExcelQueryFactory(pathDelFicheroExcel);
var resultado = (from row in book.Worksheet("Hoja1")
let item = new EntidadHojaExcel
{
codigo_producto = no_null(row["Código de Producto"].Cast <string>()),
nombre_producto = no_null(row["Nombre"].Cast <string>()),
marca = no_null(row["Marca"].Cast <string>()),
valor_compra = Convert.ToDecimal(no_null(row["Precio de compra"].Cast <string>())),
valor_venta = Convert.ToDecimal(no_null(row["Precio de venta"].Cast <string>())),
valor_mayorista = Convert.ToDecimal(no_null(row["Precio mayorista"].Cast <string>())),
fecha_introduccion = DateTime.Today,
fecha_vencimiento = Convert.ToDateTime(no_null(row["Fecha de vencimiento"].Cast <string>())),
stock = Convert.ToInt32(no_null(row["Stock"].Cast <string>())),
stock_minimo = Convert.ToInt32(no_null(row["Stock mínimo"].Cast <string>())),
descripcion = no_null(row["Descripción"].Cast <string>()),
proveedor = no_null(row["Proveedor"].Cast <string>()),
categoria = no_null(row["Categoría"].Cast <string>())
}
select item).ToList();
book.Dispose();
return(resultado);
}
public void Inicia()
{
var book = new ExcelQueryFactory(@"Formato ABB.xlsx");
var nombres = book.GetWorksheetNames();
//var nom = book.Worksheet("Layout").ToList();
var nom5 = book.Worksheet("Layout");
var item = book.Worksheet("Layout").ToList();
string nombre = "";
nombre = item[1][8];
int i = 20;
do
{
Console.WriteLine(item[i][1]);
i++;
} while (item[i][6] != "Product Total");
if (!string.IsNullOrEmpty(item[1][8]))
{
}
//var fecha = DateTime.ParseExact(item[6][7], "dd/MM/dd", CultureInfo.InvariantCulture);
nombre = item[2][1];
//nom.AddRange(new List<Row>(){new Row(){new Cell("Layout") }});
//book.Worksheet("Reporte").data
book.Dispose();
//foreach (var n in nom)
//{
//}
//foreach (var name in nombres)
//{
// var hoja = book.Worksheet(name);
// var nominas = hoja.ToList();
// try
// {
// }
// catch (Exception e)
// {
// Console.WriteLine(e);
// }
//}
book.Dispose();
}
public IEnumerable <string> getSheetsName(string filePath)
{
string patchToexcelFile = "" + @filePath;
ExcelQueryFactory excelFile = new ExcelQueryFactory(patchToexcelFile);
var months = excelFile.GetWorksheetNames();
excelFile.Dispose();
return(months);
}
public List <Persona> ToEntidadNoHeader(string path)
{
var book = new ExcelQueryFactory(path);
var resultado = (from row in book.WorksheetRangeNoHeader("A6", "AD38")
let item = new Persona
{
Id = row[0].Cast <string>(),
Nombre = row[1].Cast <string>(),
Apellido = row[2].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
//Metodo que lee y parsea el archivo excel
public List <EntidadHojaExcel> ToEntidadHojaExcelList(string pathDelFicheroExcel)
{
var book = new ExcelQueryFactory(pathDelFicheroExcel); //estamos buscando la ubicacion del archivo
var resultado = (from row in book.Worksheet("Hoja1") //tomamos la 1erHoja
let item = new EntidadHojaExcel
{
Id = row["Id"].ToString(),
Nombre = row["Nombre"].ToString(),
Apellido = row["Apellido"].ToString()
}
select item).ToList();
book.Dispose(); //cierra y libera la conexion con el archivo
return(resultado); //devolvemos el resultado
}
public List<Client> ToEntidadHojaExcelList(HttpPostedFileBase pathDelFicheroExcel)
{
string archivoruta = @"C:\ArchivosSGR\" + pathDelFicheroExcel.FileName;
var book = new ExcelQueryFactory(archivoruta);
var resultado = (from row in book.Worksheet("Hoja1")
let item = new Client
{
Serie = row["SERIE"].Cast<string>(),
Macaddress1 = row["MACADDRESS2"].Cast<string>()
}
select item).ToList();
book.Dispose();
return resultado;
}
public List <Persona> ToEntidadHojaExelList(string path)
{
var book = new ExcelQueryFactory(path);
var resultado = (from row in book.Worksheet(workSheetName)
let item = new Persona
{
Id = row["Id"].Cast <string>(),
Nombre = row["Nombre"].Cast <string>(),
Apellido = row["Apellido"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
private List <Fijos> ToEntidadHojaExcelList(string pathDelFicheroExcel)
{
var book = new ExcelQueryFactory(pathDelFicheroExcel);
var resultado = (from row in book.Worksheet("propsat")
let item = new Fijos
{
v = row["s"].Cast <double>(),
s = row["s"].Cast <double>(),
h = row["h"].Cast <double>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
/**************************************************************/
//CARGA DE DATOS DE CLIENTES - INDIVIDUOS
/**************************************************************/
#region CARGA DE DATOS DE CLIENTES - INDIVIDUOS
public List <Comunes.InterproteccionObjetos> CargaDatosClientes(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("Clientes")
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>(),
ApPaterno = row["ApellidoPaterno"].Cast <string>(),
ApMaterno = row["ApellidoMaterno"].Cast <string>(),
PrimerNombre = row["PrimerNombre"].Cast <string>(),
SegudoNombre = row["SegundoNombre"].Cast <string>(),
FechaNacimiento = row["FechaNacimiento"].Cast <string>(),
CorreoElectronico = row["CorreoElectronico"].Cast <string>(),
CorreoElectronicoAlterno = row["CorreoElectronicoAlterno"].Cast <string>(),
RFC = row["RFC"].Cast <string>(),
CURP = row["CURP"].Cast <string>(),
TelefonoCasa = row["TelefonoCasa"].Cast <string>(),
TelefonoCelular = row["TelefonoCelular"].Cast <string>(),
Calle = row["Calle"].Cast <string>(),
CodigoPostal = row["CodigoPostal"].Cast <string>(),
Estado = row["Estado"].Cast <string>(),
Ciudad = row["Ciudad"].Cast <string>(),
Delegacion = row["Delegacion"].Cast <string>(),
Colonia = row["Colonia"].Cast <string>(),
//SeleccionarPlan = row["SeleccionaPlan"].Cast<string>(),
// AGREGAR NUEVA FORMA DE PAGO
//ClienteFormaPagoAgregarNueva = row[""].Cast<string>(),
PlanContratado = row ["PlanContratado"].Cast <string>(),
FormaPagoNumeroTarjeta = row["NumeroTarjeta"].Cast <string>(),
FormaPagoVencimientoMM = row["VencimientoMM"].Cast <string>(),
FormaPagoVencimientoAA = row["VencimientoAA"].Cast <string>(),
FormaPagoCVV = row["PagoCVV"].Cast <string>(),
FormaPagoNombreDuenioTarjeta = row["NombreDuenioTarjeta"].Cast <string>()
//ClienteFormaPagoBotonAgregar = row[""].Cast<string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
public List <WebDriversComponentes.YellowBoxObjetos> CargaDatosPrueba(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("UsuariosEscuela")
let item = new WebDriversComponentes.YellowBoxObjetos
{
TestCase = row["CasoPrueba"].Cast <string>(),
Usuario = row["Usuario"].Cast <string>(),
Password = row["Password"].Cast <string>(),
Url = row["Url"].Cast <string>(),
ResultadoEsperado = row["ResultadoEsperado"].Cast <string>(),
Navegador = row["Browser"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
public List<CreacionEquiposModels> ToEntidadHojaExcelList(HttpPostedFileBase pathDelFicheroExcel)
{
string archivoruta = @"C:\ArchivosSGR\" + pathDelFicheroExcel.FileName;
var book = new ExcelQueryFactory(archivoruta);
var resultado = (from row in book.Worksheet("Hoja1")
let item = new CreacionEquiposModels
{
Serie = row["SERIE"].Cast<string>(),
Macaddress1 = row["MACADDRESS2"].Cast<string>()
}
select item).ToList();
book.Dispose();
int empresaUsuario = int.Parse(HttpContext.Session["_SessionEmpresa"].ToString());
ViewData["EmpresaUsuario"] = empresaUsuario;
return resultado;
}
///Mati: Code for Upload ID´s to Ignore Button
private void button1_Click(object sender, EventArgs e)
{
if (ofd.ShowDialog() == DialogResult.OK)
{
var book = new ExcelQueryFactory(ofd.FileName);
List <IgnoreId> IgnoreIdsob = (from row in book.Worksheet("Accounts")
let item = new IgnoreId
{
accounts = row[0].ToString()
}
select item).ToList();
IgnoreIds = (from o in IgnoreIdsob
select o.accounts).ToList();
book.Dispose();
label5.Text = "File Uploaded: Yes";
}
}
public List <Comunes.InterproteccionObjetos> CargaDatos(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("UsuariosPortal")
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>(),
Rol = row["Rol"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
/*********
* 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 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 List <CamposXls> leerArchivoRespuesta(string dNumLote)
{
//LEER DATOS DENTRO DE XLS
//string xls = @"C:\Users\Luis\Documents\"+ dNumLote+".xls";
string xls = @"C:\Users\Luis\Documents\" + dNumLote + ".xls";
//ExcelQueryFactory book = new ExcelQueryFactory(dArchivo);
var book = new ExcelQueryFactory(xls);
var resultado = (from row in book.Worksheet("Resultado")
let item = new CamposXls
{
Archivo = row["ARCHIVO"],
Estatus = row["ESTATUS"],
Descripcion = row["DESCRIPCION"]
//Folio = row["FOLIOCONTROL"]
}
select item).ToList();
book.Dispose();
return(resultado);
}
public List <IQueryable <Daftarcs> > getDataFromFile(string filePath)
{
try
{
List <IQueryable <Daftarcs> > excelData = new List <IQueryable <Daftarcs> >();
string patchToexcelFile = "" + @filePath;
ExcelQueryFactory excelFile = new ExcelQueryFactory(patchToexcelFile);
var months = excelFile.GetWorksheetNames();
foreach (var month in months)
{
var colname = getColumnsName(month, excelFile).ToList();
excelFile.AddMapping <Daftarcs>(x => x.CodeBudget, colname[0]); //کد بودجه
excelFile.AddMapping <Daftarcs>(x => x.DepositOwnerDetail, colname[1].ToString()); //مشخصات صاحب سپرده
excelFile.AddMapping <Daftarcs>(x => x.AccountType, colname[2].ToString()); //نوع حساب
excelFile.AddMapping <Daftarcs>(x => x.PlaceName, colname[3].ToString()); //عنوان واحد ثبتی
excelFile.AddMapping <Daftarcs>(x => x.BillDetailCode, colname[4].ToString()); //شماره قبض سپرده
excelFile.AddMapping <Daftarcs>(x => x.Date, colname[5].ToString()); //تاریخ
excelFile.AddMapping <Daftarcs>(x => x.DepositDetail, colname[6].ToString()); //کد مشخصات سپرده
excelFile.AddMapping <Daftarcs>(x => x.Deposit, colname[7].ToString()); //واریزی
excelFile.AddMapping <Daftarcs>(x => x.Refund, colname[8].ToString()); //استرداد
var monthData = from a in
excelFile.WorksheetRange <Daftarcs>("A2", "J1000", month.ToString())
select a;
excelData.Add(monthData);
}
excelFile.Dispose();
return(excelData);
}
catch (ArgumentException ex)
{
MessageBox.Show(ex.Message);
throw;
}
}
private void btnleer_Click(object sender, EventArgs e)
{
string rutaexel = Application.StartupPath + "\\Datos.xlsx";
var book = new ExcelQueryFactory(rutaexel);
var rs = (from row in book.Worksheet("hoja1")
let item = new Person
{
Id = row[0].Cast <int>(),
nombre = row[1].Cast <string>(),
apellido = row[2].Cast <string>()
}
select item).ToList();
book.Dispose();
tbDatos.Text = "Datos de archivo " + Environment.NewLine;
foreach (var it in rs)
{
tbDatos.Text += it.Id + " " + it.nombre + " " + it.apellido + Environment.NewLine;
}
dgvExel.DataSource = rs;
dgvExel.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
//Metodo para extraer la informacion del excel para los casos de prueba de Notificaciones.
public List <Comunes.InterproteccionObjetos> CargaDatosNotificaciones(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("Notificaciones")
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>(),
Mensaje = row["Mensaje"].Cast <string>(),
Destinatario = row["Destinatario"].Cast <string>(),
FechaEnvio = row["FechaEnvio"].Cast <string>(),
HoraEnvio = row["HoraEnvio"].Cast <string>(),
TipoPrueba = row["TipoPrueba"].Cast <string>(),
}
select item).ToList();
book.Dispose();
return(resultado);
}
public List <EntidadHojaExcel> ToEntidadHojaExcelList(string pathDelFicheroExcel)
{
var book = new ExcelQueryFactory(pathDelFicheroExcel);
var resultado = (from row in book.Worksheet("BDD")
let item = new EntidadHojaExcel
{
nro_recepcion = no_null(row["Nro de recepción"].Cast <string>()),
fecha_recepcion = no_null(row["Fecha de recepción"].Cast <string>()),
remitente = no_null(row["Remitente"].Cast <string>()),
asunto = no_null(row["Asunto"].Cast <string>()),
proveido = no_null(row["Proveído"].Cast <string>()),
destinatario = no_null(row["Destinatario(s)"].Cast <string>()),
prioridad = no_null(row["Prioridad"].Cast <string>()),
fecha_recepcion_destinatario = no_null(row["Fecha recepción destinatario"].Cast <string>()),
fecha_respuesta = no_null(row["Fecha de respuesta"].Cast <string>()),
nro_cite_respuesta = no_null(row["Nro Cite Respuesta"].Cast <string>()),
concluido = no_null(row["¿Concluido?"].Cast <string>())
}
select item).ToList();
book.Dispose();
return(resultado);
}
//Metodo para extraer la informacion del excel para los casos de prueba de Preguntas Frecuentes.
public List <Comunes.InterproteccionObjetos> CargaDatosPreguntasFrecuentes(string pathFichero)
{
var book = new ExcelQueryFactory(pathFichero);
var resultado = (from row in book.Worksheet("PreguntasFrecuentes")
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>(),
Categoria = row["Categoria"].Cast <string>(),
Activar = row["Activar"].Cast <string>(),
Pregunta = row["Pregunta"].Cast <string>(),
Respuesta = row["Respuesta"].Cast <string>(),
TipoPrueba = row["TipoPrueba"].Cast <string>(),
PreguntaEditada = row["PreguntaEditada"].Cast <string>()
}
select item).ToList();
book.Dispose();
return(resultado);
}
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();
}
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();
}
}
private void iconButton3_Click(object sender, EventArgs e)
{
string nombre;
foreach (string g in Modulo.listaFinal)
{
nombre = Modulo.RutaGuardado + "\\" + g + ".dwg";
File.Copy(Modulo.RutaAutocad, nombre, true);
}
List <Ejecucion> listaEjecucion = new List <Ejecucion>();
Ejecucion ejecucion;
foreach (Localidades k in Modulo.matrizRaiz)
{
foreach (string z in Modulo.listaFinal)
{
if (z == k.nombre_plano)
{
ejecucion = new Ejecucion();
ejecucion.loc = k.id;
ejecucion.ruta = k.nombre_plano;
listaEjecucion.Add(ejecucion);
}
}
}
ejecucion = null;
//inicializacion del autocad
Autodesk.AutoCAD.Interop.AcadApplication AcadApp = new AcadApplication();
Autodesk.AutoCAD.Interop.AcadDocument doc;
AcadApp.Visible = true;
string rutaAutocad;
//creacion de los objetos en listas
obExcel f = new obExcel();
IEnumerable <Vanos> vanos;
IEnumerable <Postes> postes;
IEnumerable <Luminarias> luminarias;
IEnumerable <Subestaciones> subestaciones;
IEnumerable <VistasC> vistasCajetines;
IEnumerable <VistasD> vistasDibujo;
IEnumerable <Layers> capas;
IEnumerable <Cajetines> cajetienes;
double[] pto = new double[3];
double[] pto2 = new double[3];
double[] pto3 = new double[3];
double[] pto4 = new double[3];
pto[2] = 0.0;
pto2[2] = 0.0;
pto3[2] = 0.0;
pto4[2] = 0.0;
AcadLine lin;
AcadBlockReference block;
AcadLayer layer;
AcadPViewport vistaCajetin;
AcadPViewport vistaPlano;
AcadAcCmColor color = null;
AcadDimAligned dim;
AcadDimAligned con;
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.17") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.18") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.19") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.20") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.21") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.22") as AcadAcCmColor;
}
catch { };
try
{
color = AcadApp.GetInterfaceObject("AutoCAD.AcCmColor.23") as AcadAcCmColor;
}
catch { };
var book = new ExcelQueryFactory(Modulo.RutaExcel);
listaEjecucion.Distinct <Ejecucion>();
foreach (Ejecucion loc in listaEjecucion)
{
rutaAutocad = Modulo.RutaGuardado + "\\" + loc.ruta + ".dwg";
doc = AcadApp.Documents.Open(rutaAutocad);
vanos = f.VanosData(loc.loc, book);
postes = f.PostesData(loc.loc, book);
luminarias = f.LuminariasData(loc.loc, book);
subestaciones = f.SubestacionesData(loc.loc, book);
vistasCajetines = f.VistasCData(book);
vistasDibujo = f.VistasDData(loc.loc, book);
cajetienes = f.CajetinesData(loc.loc, book);
capas = f.LayersData(book);
foreach (Layers g in capas)
{
color.SetRGB(g.red, g.green, g.blue);
layer = doc.Layers.Add(g.nombre_capa);
layer.TrueColor = color;
}
//dibujamos los vanos
if (VanosBox.Checked)
{
foreach (Vanos g in vanos)
{
pto[0] = g.x_inicial;
pto[1] = g.y_inicial;
pto2[0] = g.x_final;
pto2[1] = g.y_final;
pto3[0] = pto[0] / 2 + pto2[0] / 2;
pto3[1] = pto[1] / 2 + pto2[1] / 2;
lin = doc.ModelSpace.AddLine(pto, pto2);
lin.Layer = "_AereoCond";
dim = doc.ModelSpace.AddDimAligned(pto, pto2, pto3);
dim.StyleName = "ACOT-LP-FRANK";
dim.VerticalTextPosition = AcDimVerticalJustification.acAbove;
dim.Layer = "_CotaCond";
con = doc.ModelSpace.AddDimAligned(pto, pto2, pto3);
con.TextOverride = g.conductor;
con.StyleName = "ACOT-LP-FRANK";
con.VerticalTextPosition = AcDimVerticalJustification.acUnder;
con.Layer = "_TipoCond";
}
}
//metodo de postes
if (Postesbox.Checked)
{
foreach (Postes g in postes)
{
pto[0] = g.x_poste;
pto[1] = g.y_poste;
block = doc.ModelSpace.InsertBlock(pto, g.bloque, 1, 1, 1, 0);
block.Layer = "_Postes";
var blocAtri = block.GetAttributes();
blocAtri[0].TextString = g.altura;
blocAtri[1].TextString = g.estado;
blocAtri[2].TextString = g.material;
blocAtri[3].TextString = g.cod_poste;
}
}
//metodo de luminarias
if (LumBox.Checked)
{
foreach (Luminarias g in luminarias)
{
pto[0] = g.x_luminaria;
pto[1] = g.y_luminaria;
block = doc.ModelSpace.InsertBlock(pto, "luminaria", 1, 1, 1, 0);
block.Layer = "_Luminaria";
var blocAtri = block.GetAttributes();
blocAtri[0].TextString = g.potencia;
blocAtri[1].TextString = g.altura;
blocAtri[2].TextString = g.codigo_luminaria;
}
}
//metodo de subestaciones
if (SedBox.Checked)
{
foreach (Subestaciones g in subestaciones)
{
pto[0] = g.x_subestacion;
pto[1] = g.y_subestacion;
block = doc.ModelSpace.InsertBlock(pto, g.bloque, 25, 25, 25, 0);
block.Layer = "_Seds";
var blocAtri = block.GetAttributes();
blocAtri[0].TextString = g.potencia;
blocAtri[1].TextString = g.codigo_subestacion;
}
}
//metodo de creacion de vistas en cajetines
if (CajBox.Checked)
{
foreach (VistasC g in vistasCajetines)
{
pto[0] = g.x_centro;
pto[1] = g.y_centro;
pto2[0] = g.x_inicial;
pto2[1] = g.y_inicial;
pto3[0] = g.x_final;
pto3[1] = g.y_final;
doc.ActiveSpace = AcActiveSpace.acPaperSpace;
vistaCajetin = doc.PaperSpace.AddPViewport(pto, g.ancho, g.largo);
vistaCajetin.Display(true);
vistaCajetin.ViewportOn = true;
doc.MSpace = true;
AcadApp.ActiveDocument.ActivePViewport = vistaCajetin;
AcadApp.ZoomWindow(pto2, pto3);
doc.MSpace = false;
}
//metodo de creacion de vistas en planos
foreach (VistasD g in vistasDibujo)
{
pto[0] = g.x_centro;
pto[1] = g.y_centro;
pto2[0] = g.x_inicial;
pto2[1] = g.y_inicial;
pto3[0] = g.x_final;
pto3[1] = g.y_final;
doc.ActiveSpace = AcActiveSpace.acPaperSpace;
vistaPlano = doc.PaperSpace.AddPViewport(pto, g.ancho, g.largo);
vistaPlano.Display(true);
vistaPlano.ViewportOn = true;
doc.MSpace = true;
AcadApp.ActiveDocument.ActivePViewport = vistaPlano;
AcadApp.ZoomWindow(pto2, pto3);
doc.MSpace = false;
}
//metodo de cajetines
foreach (Cajetines g in cajetienes)
{
pto[0] = 0;
pto[1] = 0;
block = doc.PaperSpace.InsertBlock(pto, "cajetin", 1, 1, 1, 0);
block.Layer = "_MarcoPlano";
var blocAtri = block.GetAttributes();
blocAtri[0].TextString = g.escala;
blocAtri[1].TextString = g.fecha;
blocAtri[2].TextString = g.plano;
blocAtri[3].TextString = g.n_expediente;
blocAtri[4].TextString = g.departamento;
blocAtri[5].TextString = g.provincia;
blocAtri[6].TextString = g.distrito;
blocAtri[7].TextString = g.revisado;
blocAtri[8].TextString = g.aprobado;
blocAtri[9].TextString = g.dibujado;
blocAtri[10].TextString = g.anexo;
}
}
doc.Save();
doc.Close();
}
book.Dispose();
AcadApp.Quit();
}
public Tuple <string, string> ProcessingForMergingDataForExpressway(string filePathWithName)
{
string[] filePathArr = filePathWithName.Split(new char[] { '\\' });
string fileName = filePathArr[(filePathArr.Length - 1)];
string folderPath = filePathWithName.Remove(filePathWithName.Length - fileName.Length);
var excel = new ExcelQueryFactory(filePathWithName);
var worksheetNames = excel.GetWorksheetNames();
// To find the list of sheets
List <string> sheetNameList = new List <string>();
foreach (var a in worksheetNames)
{
sheetNameList.Add(a);
}
var rowWiseData = from r in excel.Worksheet(sheetNameList[0])
select new { r };
foreach (var data in rowWiseData)
{
//***** Adding Route Name data to list******************
//***** Adding Post data to list******************
//***** Adding latitude data to list******************
//***** Adding longitude data to list******************
if (!string.IsNullOrEmpty(data.r[5]) && !string.IsNullOrEmpty(data.r[36]) && !string.IsNullOrEmpty(data.r[37]) && !string.IsNullOrEmpty(data.r[38]))
{
routeNameList.Add(data.r[5].ToString());
bothRouteNameList.Add(data.r[5].ToString());
postNameList.Add(data.r[36].ToString());
bothPostNameList.Add(data.r[36].ToString());
downDataList.Add("D");
latitudeDataList.Add(data.r[37].ToString());
bothLatitudeDataList.Add(data.r[37].ToString());
longitudeDataList.Add(data.r[38].ToString());
bothLongitudeDataList.Add(data.r[38].ToString());
}
else
{
//Do Nothing
}
}
foreach (var post in downDataList)
{
upDataList.Add("U");
}
routeNameList.Reverse();
bothRouteNameList.AddRange(routeNameList);
postNameList.Reverse();
bothPostNameList.AddRange(postNameList);
upAndDownDataList.AddRange(downDataList);
upAndDownDataList.AddRange(upDataList);
latitudeDataList.Reverse();
bothLatitudeDataList.AddRange(latitudeDataList);
longitudeDataList.Reverse();
bothLongitudeDataList.AddRange(longitudeDataList);
//*********************************************************************Reading and creation of data ends and writing starts******************************
// Load Excel application
Microsoft.Office.Interop.Excel.Application excelWrite = new Microsoft.Office.Interop.Excel.Application();
excelWrite.DisplayAlerts = false;
// Create empty workbook
excelWrite.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excelWrite.ActiveSheet;
workSheet.Name = "Result";
((Range)workSheet.Cells[1, 1]).EntireColumn.ColumnWidth = 19;
((Range)workSheet.Cells[1, 1]).Font.Size = 12;
((Range)workSheet.Cells[1, 1]).Font.Bold = true;
((Range)workSheet.Cells[1, 1]).Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
((Range)workSheet.Cells[1, 2]).EntireColumn.ColumnWidth = 19;
((Range)workSheet.Cells[1, 2]).EntireColumn.NumberFormat = "0.000";
((Range)workSheet.Cells[1, 2]).Font.Size = 12;
((Range)workSheet.Cells[1, 2]).Font.Bold = true;
((Range)workSheet.Cells[1, 3]).EntireColumn.ColumnWidth = 19;
((Range)workSheet.Cells[1, 3]).Font.Size = 12;
((Range)workSheet.Cells[1, 3]).Font.Bold = true;
((Range)workSheet.Cells[1, 4]).EntireColumn.ColumnWidth = 19;
((Range)workSheet.Cells[1, 4]).EntireColumn.NumberFormat = "0.000000000000";
((Range)workSheet.Cells[1, 4]).Font.Size = 12;
((Range)workSheet.Cells[1, 4]).Font.Bold = true;
((Range)workSheet.Cells[1, 5]).EntireColumn.ColumnWidth = 19;
((Range)workSheet.Cells[1, 5]).EntireColumn.NumberFormat = "0.000000000000";
((Range)workSheet.Cells[1, 5]).Font.Size = 12;
((Range)workSheet.Cells[1, 5]).Font.Bold = true;
workSheet.Cells[1, "A"] = "路線名";
workSheet.Cells[1, "B"] = "キロポスト";
workSheet.Cells[1, "C"] = "上下線";
workSheet.Cells[1, "D"] = "緯度";
workSheet.Cells[1, "E"] = "経度 ";
for (int i = 0; i < bothRouteNameList.Count; i++)
{
workSheet.Cells[i + 2, "A"] = bothRouteNameList[i].ToString();
workSheet.Cells[i + 2, "B"] = bothPostNameList[i].ToString();
workSheet.Cells[i + 2, "C"] = upAndDownDataList[i].ToString();
workSheet.Cells[i + 2, "D"] = bothLatitudeDataList[i].ToString();
workSheet.Cells[i + 2, "E"] = bothLongitudeDataList[i].ToString();
}
try
{
excelWrite.DisplayAlerts = false;
workSheet.SaveAs(folderPath + "Result_ForExpressway_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv");
}
catch (Exception)
{
var result = MessageBox.Show("選択した入力ファイルの書式を確認してください。", "エラーメッセージ", MessageBoxButtons.RetryCancel);
switch (result)
{
case DialogResult.Retry: // Retry button pressed
//System.Windows.Forms.Application.Restart();
System.Diagnostics.Process.Start(System.Windows.Forms.Application.ExecutablePath); // to start new instance of application
fObj.Close(); //to close the current instance
break;
case DialogResult.Cancel: // Cancel button pressed
excel.Dispose();
GC.SuppressFinalize(this);
Environment.Exit(1);
break;
default: // Neither Retry nor Cancel pressed (just in case)
MessageBox.Show("もう一度お試しください");
break;
}
}
finally
{
ClearAllEndAll(excelWrite, workSheet);
}
return(Tuple.Create(folderPath, folderPath + "Result_ForExpressway_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv"));
//return <folderPath, (folderPath + "Result_ForExpressway_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv")>;
}
/// <summary>
/// Closes all connections and disposes data.
/// </summary>
public void Dispose()
{
_excelFile.Dispose();
}
private List<Afiliado> ToListaAfiliado(string pathDelFicheroExcel)
{
//Filter:("*.xls;*.xlsx)|*.xls;*.xlsx"); //le indicamos el tipo de filtro en este caso que busque solo los archivos excel
try
{
var book = new ExcelQueryFactory(pathDelFicheroExcel);
var resultado = (from row in book.Worksheet("Hoja1")
let item = new Afiliado
{
docnumber = row["Cedula"].Cast<string>(),
Monto = int.Parse(row["Monto"].Cast<string>())
}
select item).ToList();
book.Dispose();
return resultado.ToList();
}
catch
{
return null;
}
}
public void LeerExcel()
{
//TipoTematica 1= Tematica activa de posipedia, 2= Tematica empresa, 3= Tematica inactiva posipedia
var fileName = "FormaciónEducaVirtualPresencial.xlsx";
var filePath = Path.Combine(HttpRuntime.AppDomainAppPath, "Content/ArchivosDocumentacion/"+fileName);
var serverPath = "ftp://[email protected]:990//FormaciónEducaVirtualPresencial.xlsx";
FtpWebRequest request = (FtpWebRequest)WebRequest.Create(serverPath);
request.KeepAlive = true;
request.UsePassive = true;
request.UseBinary = true;
request.Method = WebRequestMethods.Ftp.DownloadFile;
request.Credentials = new NetworkCredential("ftp_manuelaB", "f7hUwGQs");
request.EnableSsl = true;
// Read the file from the server & write to destination
ServicePointManager.ServerCertificateValidationCallback = new System.Net.Security.RemoteCertificateValidationCallback(AcceptAllCertifications);
using (FtpWebResponse response = (FtpWebResponse)request.GetResponse()) // Error here
using (Stream responseStream = response.GetResponseStream())
{
int bytesRead = 0;
byte[] buffer = new byte[2048];
FileStream fileStream = new FileStream(filePath, FileMode.Create);
while (true)
{
bytesRead = responseStream.Read(buffer, 0, buffer.Length);
if (bytesRead == 0)
break;
fileStream.Write(buffer, 0, bytesRead);
}
fileStream.Close();
}
var tematica =from a in db.Tbl_Tematica
where (a.TipoTematica!=2 && !db.Tbl_Rol_Por_Tematica.Select(b => b.Fk_Id_Tematica).Contains(a.Id_Tematica)) select a;
if (tematica != null)
{
List<Tematica> tema = tematica.ToList();
db.Tbl_Tematica.RemoveRange(tema);
}
db.SaveChanges();
var tematicaPosipedia = from a in db.Tbl_Tematica
where (a.TipoTematica != 2)
select a;
//creamos el libro a partir de la ruta
var Book = new ExcelQueryFactory(filePath);
//Consulta con Linq
var resp = (from row in Book.Worksheet("Hoja1")
let item = new Tematica
{
//Id_Tematica = row[0].Cast<string>();
Area = row[0].Cast<string>(),
Tematicas = row[1].Cast<string>(),
Diseno = row[2].Cast<string>(),
Objetivo = row[3].Cast<string>(),
DirigidoA = row[4].Cast<string>(),
TipoTematica = 1,
NombreDocumento = null,
SesionEmpresa = null,
}
select item).ToList();
Book.Dispose();
foreach (Tematica p in tematicaPosipedia)
{
bool activarTematica = false;
foreach (Tematica archivo in resp.ToList())
{
if (p.Tematicas.Equals(archivo.Tematicas) && !activarTematica)
{
resp.Remove(archivo);
activarTematica = true;
}
}
if(activarTematica)
p.TipoTematica = 1;
else
p.TipoTematica = 3;
}
db.Tbl_Tematica.AddRange(resp);
db.SaveChanges();
}