public virtual IActionResult ExportToExcel()
{
try
{
var data = _baseRepo.GetAll <T>(x => x.Where(y => y.Active == true));
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <T>(data, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (data != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new T().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}
public IActionResult AccountStateExport(long customerId)
{
try
{
var data = _repositoryFactory.GetCustomDataRepositories <ICustomerBalanceRepository>().CustomerState(customerId);
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <CustomerStateModel>(data.Data, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (data != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}
private void UploadButton_Click(object sender, RoutedEventArgs e)
{
// Create OpenFileDialog
Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
// Set filter for file extension and default file extension
dlg.DefaultExt = ".csv";
dlg.Filter = "Excel Files (*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv";
// Display OpenFileDialog by calling ShowDialog method
bool?result = dlg.ShowDialog();
if (result == true)
{
// Open document
string filename = dlg.FileName;
if (Path.GetExtension(filename) == ".csv" || Path.GetExtension(filename) == ".CSV")
{
ReportFormat bank = ReportFormats.Where(r => r.IsChecked == true).First();
string holder = HoldersItems.Where(r => r.IsChecked == true).Select(x => x.HolderName).First();
ExcelImport.ImportFromExcel(filename, bank, holder, date_statement.SelectedDate);
UpdateSalaries();
UpdateDataGrid();
}
}
}
public IActionResult GetOwedExpensesReport(long branchOfficeId = 0, long supplierId = 0, long currencyId = 0, string startDate = "0", string endDate = "0")
{
try
{
DateTime?start = null;
start = startDate != "0" ? Convert.ToDateTime(startDate) : start;
DateTime?end = null;
end = endDate != "0" ? Convert.ToDateTime(endDate) : DateTime.Now;
var data = _repo.GetDebsToPay(start, end, supplierId, currencyId, branchOfficeId);
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <Expense>(data, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (data != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM IMPORT_CMS_SPL_AVL WHERE PROJECT_ID IN (0, -1, " + Session["PROJECT_ID"].ToString() + ")");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
ExcelImport.ImporNpoi(stream, "IMPORT_CMS_SPL_AVL", "PK_IMPORT_CMS_SPL_AVL", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("BEGIN PKG_IMPORT_CMS_SPL_AVL.UPDATE_SPOOL(" + Session["PROJECT_ID"].ToString() + ");END;");
Master.ShowSuccess("CMS Material Available Status imported!");
} // method
public Task <IActionResult> ExportComissionsReport([FromBody] ComissionsSearch search)
{
var t_result = Task.Factory.StartNew <IActionResult>((arg) => {
try
{
var t_inputs = arg as Tuple <ComissionsSearch, ISellerRepository>;
var invoices = t_inputs.Item1.ReportType == 0 ?
t_inputs.Item2.SalesComissions(t_inputs.Item1) : t_inputs.Item2.PaymentsComissions(t_inputs.Item1);
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <ComissionDetail>(invoices.ComissionsByCyrrencies.SelectMany(x => x.Details), requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (invoices != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}, new Tuple <ComissionsSearch, ISellerRepository>(search, _repositoryFactory.GetCustomDataRepositories <ISellerRepository>()));
return(t_result);
}
protected void UploadETAFile()
{
try
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(matEtaUploader.PostedFile.FileName);
string Extension = Path.GetExtension(matEtaUploader.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
matEtaUploader.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM TBL_TEMP_SML_MAT_ETA WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
ExcelImport.ImportDataTable(dt, "TBL_TEMP_SML_MAT_ETA", "", "PROJECT_ID", proj_id);
}
catch (Exception ex)
{
Master.show_error("Error ETA File : " + ex.Message);
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM PIP_ISOMETRIC_IMPORT WHERE PROJECT_ID IN (0, -1, " + proj_id + ")");
ExcelImport.Import_Excel_File(FilePath, Extension, "PIP_ISOMETRIC_IMPORT", "PIP_ISOMETRIC_IMPORT_PK",
"PROJECT_ID", Session["PROJECT_ID"].ToString());
WebTools.ExecNonQuery("BEGIN PKG_IMPORT_ISO.PRC_IMPORT_ISO(" + proj_id + ");END;");
Master.ShowSuccess("Isometric list imported!");
} // method
protected void btnImport_Click(object sender, EventArgs e)
{
if (!FileUpload1.HasFile)
{
return;
}
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
FileUpload1.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM PIP_MSR_IMPORT");
WebTools.ExecNonQuery("DELETE FROM PIP_PO_DETAIL");
WebTools.ExecNonQuery("DELETE FROM PIP_PO");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
ExcelImport.ImporNpoi(stream, "PIP_MSR_IMPORT", "", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("DELETE PIP_MSR_IMPORT WHERE PO_NO='TEXT' OR AMD_NO='TEXT' OR MATERIAL_DESCRIPTION1='TEXT'");
WebTools.ExecNonQuery("UPDATE PIP_MSR_IMPORT SET MATERIAL_DESCRIPTION1=UPPER(MATERIAL_DESCRIPTION1)");
WebTools.ExecNonQuery("UPDATE PIP_MSR_IMPORT SET MATERIAL_DESCRIPTION2=UPPER(MATERIAL_DESCRIPTION2)");
WebTools.ExecNonQuery("UPDATE PIP_MSR_IMPORT SET IDENT_CD=MATERIAL_DESCRIPTION1 WHERE IDENT_CD IS NULL AND MATERIAL_DESCRIPTION1 IS NOT NULL");
WebTools.ExecNonQuery("DELETE PIP_MSR_IMPORT WHERE IDENT_CD IS NULL");
WebTools.ExecNonQuery("BEGIN PKG_IMPORT_PO.APPEND_PO(" + proj_id + ");END;");
Master.ShowSuccess("PO imported!");
} // method
public IActionResult GetTaxesReportExcel(string initialDate = "0", string endDate = "0")
{
try
{
DateTime?startDate = null;
startDate = initialDate != "0" ? Convert.ToDateTime(initialDate) : startDate;
DateTime?finalDate = null;
finalDate = endDate != "0" ? Convert.ToDateTime(endDate) : finalDate;
var data = businessStateRepository.GetTaxesReport(startDate, finalDate);
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <InvoiceTax>(data.SelectMany(x => x.Taxes), requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (data != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}
protected void UploadFile(FileUpload f, string DestinationTable)
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(f.PostedFile.FileName);
string Extension = Path.GetExtension(f.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
f.SaveAs(FilePath);
// delete old data
WebTools.ExecNonQuery("DELETE FROM " + DestinationTable + " WHERE PROJECT_ID = '" + Session["PROJECT_ID"].ToString() + "'");
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
dt.Columns.Add("IMPORT_BY");
foreach (DataRow r in dt.Rows)
{
r["IMPORT_BY"] = Session["USER_NAME"].ToString();
}
ExcelImport.ImportDataTable(dt, DestinationTable, "", "PROJECT_ID", proj_id);
}
public void Test()
{
// Arrange
var importer = new ExcelImport <Simple>
{
ColumnMapList = new List <ExcelImport <Simple> .ExcelColumnMap>
{
new ExcelImport <Simple> .ExcelColumnMap(1, (x, value) => x.ColumnA = value),
new ExcelImport <Simple> .ExcelColumnMap(2, (x, value) => x.ColumnB = value),
new ExcelImport <Simple> .ExcelColumnMap(3, (x, value) => x.ColumnC = value)
}
};
// Act
var result = importer.ImportFromFile(FileName, FirstSheetName, 2).ToArray();
// Assert
Assert.AreEqual(2, result.Length);
Assert.AreEqual("Data A", result[0].ColumnA);
Assert.AreEqual("Data B", result[0].ColumnB);
Assert.AreEqual("Data C", result[0].ColumnC);
Assert.AreEqual("Third row", result[1].ColumnA);
Assert.IsNull(result[1].ColumnB);
Assert.AreEqual("Last cell", result[1].ColumnC);
}
public Task <IActionResult> ExportState(string startDate = "0", string endDate = "0")
{
var t_result = Task.Factory.StartNew <IActionResult>((arg) => {
try
{
var t_inputs = arg as Tuple <string, string>;
var invoices = _customRepo.GetCompanyStatus(
t_inputs.Item1 == "0" ? DateTime.MinValue : Convert.ToDateTime(t_inputs.Item1),
t_inputs.Item2 == "0" ? DateTime.Now : Convert.ToDateTime(t_inputs.Item2));
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <CompanyStateModel>(invoices, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (invoices != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}, new Tuple <string, string>(startDate, endDate));
return(t_result);
}
private void ProcessExcel(AdminDataImport dataImport, string fileExtension)
{
var postedFile = Request.Files[0];
var filePath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, System.IO.Path.GetFileName(postedFile.FileName));
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
postedFile.SaveAs(filePath);
if (dataImport.Schema == FileUploadSchema.Grade.ToString())
{
ExcelImport.UploadGrades(filePath
, fileExtension
, dataImport.CourseId
, dataImport.Deliverable
, CurrentUser.Id);
}
else
{
ExcelImport.UploadSurveys(filePath
, fileExtension
, dataImport.CourseId
, CurrentUser.Id);
}
}
public IActionResult ExportToExcel(long branchOfficeId = 0, long warehouseId = 0, long productId = 0)
{
try
{
var data = _baseRepo.GetAll <Inventory>(x => x.Include(i => i.Product)
.Include(i => i.Warehouse)
.Include(i => i.BranchOffice)
.Include(i => i.Unit)
, y => y.Active == true &&
(branchOfficeId > 0 ? y.BranchOfficeId == branchOfficeId : y.BranchOfficeId > 0) &&
(warehouseId > 0 ? y.WarehouseId == warehouseId : y.WarehouseId > 0) &&
(productId > 0 ? y.ProductId == productId : y.ProductId > 0));
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "ES";
var excelData = ExportUtility.GetExcelData <Inventory>(data, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (data != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}
private static void ImportSPS(string Extension, string FilePath, string proj_id)
{
WebTools.ExecNonQuery("DELETE FROM PIP_SUPP_MTO_SPS WHERE PROJECT_ID IN (0, -1, " + proj_id + ")");
ExcelImport.Import_Excel_File(FilePath, Extension, "PIP_SUPP_MTO_SPS", "PK_SUPP_MTO_SPS", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("DELETE FROM PIP_SUPP_MTO_SPS WHERE ITEM_NAME='TEXT' OR ITEM_TYPE='TEXT'");
}
private void StopWatching(ExcelImport import)
{
var fileDropWatcher = _fileDropWatchers[import.Oid];
fileDropWatcher.Stop();
fileDropWatcher.Dispose();
_fileDropWatchers.Remove(import.Oid);
}
protected void btnSplUpload_Click(object sender, EventArgs e)
{
string sql, iso_id, spl_id, spl_id2;
if (splUploader.HasFile)
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(splUploader.PostedFile.FileName);
string Extension = Path.GetExtension(splUploader.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
splUploader.SaveAs(FilePath);
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
try
{
switch (rdoUploadOption.SelectedValue)
{
case "DELETE":
sql = "DELETE FROM PIP_SML_SPOOL";
WebTools.ExeSql(sql);
break;
}
foreach (DataRow dr in dt.Rows)
{
if (dr[0].ToString().Trim().Equals("ISO_TITLE1"))
{
continue;
}
iso_id = WebTools.GetExpr("ISO_ID", "PIP_ISOMETRIC", "ISO_TITLE1='" + dr[0].ToString() + "'");
spl_id = WebTools.GetExpr("SPL_ID", "PIP_SPOOL", "ISO_ID='" + iso_id + "'" + " AND SPOOL='" + dr[1].ToString() + "'");
spl_id2 = WebTools.GetExpr("SPL_ID", "PIP_SML_SPOOL", " SPL_ID='" + spl_id + "'");
if (spl_id2.Trim().Length == 0 && spl_id.Trim().Length > 0)
{
sql = "INSERT INTO PIP_SML_SPOOL (PROJECT_ID, LIST_NAME, SPL_ID) VALUES (1, '" + FileName + "', '" + spl_id + "')";
WebTools.ExeSql(sql);
}
}
Master.ShowSuccess("Selected Spools Imported.");
}
catch (Exception ex)
{
Master.ShowWarn(ex.Message + " Invalid ISO / Spool Provided");
}
}
else
{
Master.ShowWarn("Please Select a file to upload");
return;
}
}
public void Should_accept_file_stream_from_Excel97()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXls, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLS, true);
}
}
private void ImportCccSpool(string Extension, string FilePath, string proj_id)
{
WebTools.ExecNonQuery("DELETE FROM CCC_IMPORT_SPOOL WHERE PROJECT_ID IN (0, -1, " + proj_id + ")");
FileStream fs = new FileStream(FilePath, FileMode.Open);
ExcelImport.ImporNpoi(fs, "CCC_IMPORT_SPOOL", "CCC_IMPORT_SPOOL_UK1", "PROJECT_ID", proj_id);
WebTools.ExecNonQuery("DELETE FROM CCC_IMPORT_SPOOL WHERE ISO_TITLE1='TEXT' OR SPOOLNO='TEXT'");
WebTools.ExecNonQuery("BEGIN PKG_CCC_IMPORT_MTO.PRC_IMPORT_SPL(" + proj_id + ");END;");
}
public void Should_accept_file_stream_from_Excel97()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXls, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLS, true);
}
}
public virtual Task <IActionResult> ExportReceipts(long branchOfficeId = 0, long customerId = 0, long currencyId = 0, long paymentTypeId = 0, string startDate = "0", string endDate = "0")
{
DateTime?start = new DateTime();
if (startDate != "0")
{
start = Convert.ToDateTime(startDate);
}
else
{
start = null;
}
DateTime?end = new DateTime();
if (endDate != "0")
{
end = Convert.ToDateTime(endDate);
}
else
{
end = null;
}
var t_result = Task.Factory.StartNew <IActionResult>((arg) => {
try
{
var t_inputs = arg as Tuple <long, long, long, long, DateTime?, DateTime?>;
var invoices = base._repositoryFactory.GetCustomDataRepositories <ICustomerPaymentRepository>().IncomesReport(
t_inputs.Item3,
t_inputs.Item1,
t_inputs.Item2,
t_inputs.Item4,
t_inputs.Item5,
t_inputs.Item6
);
string requestLanguage = "EN";
var languageIdHeader = this.Request.Headers["languageid"];
requestLanguage = languageIdHeader.FirstOrDefault() ?? "es";
var excelData = ExportUtility.GetExcelData <CustomerPayment>(invoices, requestLanguage, this.languageKeys.ToList());
var excelStream = ExcelImport.CreateXlsStream(
excelData.Item1,
excelData.Item2
);
if (invoices != null && excelStream != null && excelStream.Length > 0)
{
return(File(excelStream.ToArray(), "application/octet-stream", $"{new Product().GetType().Name}-{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}.xls"));
}
return(BadRequest(new { status = -1, message = "Documento no existe" }));
}
catch (Exception ex)
{
return(Ok(new { status = -1, message = ex.Message }));
}
}, new Tuple <long, long, long, long, DateTime?, DateTime?>(customerId, currencyId, branchOfficeId, paymentTypeId, start, end));
return(t_result);
}
protected void btnIsoUpload_Click(object sender, EventArgs e)
{
string sql, iso_title1;
if (isoUploader.HasFile)
{
string proj_id = Session["PROJECT_ID"].ToString();
string FileName = Path.GetFileName(isoUploader.PostedFile.FileName);
string Extension = Path.GetExtension(isoUploader.PostedFile.FileName);
string FolderPath = WebTools.SessionDataPath();
string FilePath = FolderPath + FileName;
isoUploader.SaveAs(FilePath);
//lblSmlUser.Text = FilePath;
//return;
FileStream stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read);
DataTable dt = new DataTable();
dt = ExcelImport.xlsxToDT2(stream);
try
{
switch (rdoUploadOption.SelectedValue)
{
case "DELETE":
sql = "DELETE FROM PIP_SML_ISOME";
WebTools.ExeSql(sql);
break;
}
foreach (DataRow dr in dt.Rows)
{
//iso_title1 = WebTools.GetExpr("ISO_ID", "PIP_ISOMETRIC", "ISO_TITLE1='" + dr[0].ToString() + "'");
if (dr[0].ToString().Trim().Equals("ISO_TITLE1"))
{
continue;
}
iso_title1 = dr[0].ToString().Trim();
if (iso_title1.Trim().Length > 0)
{
sql = "INSERT INTO PIP_SML_ISOME (ISO_TITLE) VALUES ('" + iso_title1 + "')";
WebTools.ExeSql(sql);
}
}
Master.ShowSuccess("Selected Isometrics Imported.");
}
catch (Exception ex)
{
Master.ShowWarn(ex.Message);
}
}
else
{
Master.ShowWarn("Please Select a file to upload");
return;
}
}
public async void ImporterFichier()
{
ExcelImport Import = new ExcelImport();
//await Import.ImporterCSV(cheminFichier);
Import.ImporterXLS(cheminFichier);
OnPropertyChanged("ListeNouveauxCompetiteur");
}
public void Should_create_Xls_Importer_for_Xls_file()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXls, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLS, true);
Assert.IsInstanceOf(typeof(ExcelXlsImporter), import.ExcelImporter);
}
}
public void Should_throw_InvalidOperationException_if_hasHeaderRow_is_false_for_XLSX()
{
import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, false);
Assert.Throws <InvalidOperationException>(() => import.GetRowsAsKeyValuePairs(1));
}
}
public void Should_create_Xlsx_Importer_for_Xlsx_file()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
Assert.IsInstanceOf(typeof(ExcelXlsxImporter), import.ExcelImporter);
}
}
public void ImportData()
{
try
{
var dialog = new OpenFileDialog();
var file = dialog.ShowDialog();
if (file == true)
{
var result = dialog.FileName;
var ovimport = new ExcelImport(new DynamicPath(result));
var data = ovimport.ImportDataFromType(result, type);
if (data != null)
{
var count = data.Count;
var confirmation = MessageBox.Show($"{count} documents trouvés, voulez-vous continuer!", "Confirmation", MessageBoxButton.YesNo);
if (confirmation == MessageBoxResult.Yes)
{
foreach (var item in data)
{
item.AddedAtUtc = DateTime.Now;
// item.isLocal = false;
try
{
// item.Series = item.MyModule()?.Id;
}
catch
{}
item.Save();
item.Submit();
// item.Save();
}
//var mi = typeof(BaseMongoRepository).GetMethod("AddMany");
//var gen = mi.MakeGenericMethod(type);
//gen.Invoke(DS.db, new object[] { data });
// DS.db.AddMany<t>(data);
MessageBox.Show("Terminé");
Actualiser();
}
}
else
{
MessageBox.Show("Aucun document trouvé");
return;
}
}
}
catch (Exception s)
{
MessageBox.Show(s.Message);
return;
}
}
public void Should_throw_ArgumentExcetion_if_asking_for_header_row_when_file_does_not_have_header_row()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, false);
Assert.Throws <ArgumentException>(() => import.GetRows(1, true));
}
}
public AdminController(GameStateService gameStateService, OverridableSettings appSettings, TilgangsKontroll tilgangsKontroll, PosisjonsService posisjonsService, ExcelImport excelImport, KmlToExcelPoster kmlToExcelPoster, ExcelWriter excelWriter, ExcelExport excelExport, DataContextFactory dataContextFactory)
{
_gameStateService = gameStateService;
_appSettings = appSettings;
_tilgangsKontroll = tilgangsKontroll;
_posisjonsService = posisjonsService;
_excelImport = excelImport;
_kmlToExcelPoster = kmlToExcelPoster;
_excelWriter = excelWriter;
_excelExport = excelExport;
_dataContextFactory = dataContextFactory;
}
public void Should_correctly_convert_date_type_to_string()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testDataTypesXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var row = import.GetRows(1, false).ToList()[0].ToList();
Assert.AreEqual("22/10/2011", row[1]);
}
}
public void Should_return_keyvaluepairs_for_xlsx()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var values = import.GetRowsAsKeyValuePairs(0);
Assert.That(values, Is.Not.Empty);
}
}
public void Should_return_correct_number_of_rows_ignoring_header()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var rows = import.GetRows(1, true);
Assert.AreEqual(5, rows.Count());
}
}
public void Should_correctly_convert_date_type_to_string()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testDataTypesXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var row = import.GetRows(1, false).ToList()[0].ToList();
Assert.AreEqual("22/10/2011", row[1]);
}
}
public void SetUp()
{
import = new ExcelImport();
_firstRowDictionary = new Dictionary<string, object>
{
{"ID", "1"},
{"Title","Mr"},
{"FirstName", "Bob"},
{"LastName","Smith"},
{"Salutation","Bob"},
{"EmailAddress","*****@*****.**"},
{"AddressLine1", "1 Short Street"},
{"AddressLine2", ""},
{"City","London"},
{"Region","SouthEast"},
{"PostalCode","W1 1QP"},
{"Country","United Kingdom"},
{"HomePhone","01123 1234356"},
{"WorkPhone","01456123456"},
{"Mobile","07757123456"},
{"ContactByPhone","Y"},
{"ContactByEmail","Y"},
{"ContactBySMS","Y"},
{"ContactByPost","N"}
};
_lastRowDictionary = new Dictionary<string, object>
{
{"ID", "5"},
{"Title","Mr"},
{"FirstName", "Brian"},
{"LastName","Monroe"},
{"Salutation","Brian"},
{"EmailAddress","*****@*****.**"},
{"AddressLine1", "5 Short Street"},
{"AddressLine2", ""},
{"City","London"},
{"Region","SouthEast"},
{"PostalCode","W1 5QP"},
{"Country","United Kingdom"},
{"HomePhone","01123 444356"},
{"WorkPhone","01456123321"},
{"Mobile","07757123987"},
{"ContactByPhone","Y"},
{"ContactByEmail","N"},
{"ContactBySMS","N"},
{"ContactByPost","N"}
};
}
public void Should_return_correct_data()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var firstRow = import.GetRows(1, true).ToList()[0].ToList();
Assert.AreEqual("Bob", firstRow[2]);
Assert.AreEqual("01123 1234356", firstRow[12]);
var lastRow = import.GetRows(1, true).ToList()[4].ToList();
Assert.AreEqual("Monroe", lastRow[3]);
Assert.AreEqual("N", lastRow[18]);
}
}
public void Should_correctly_convert_data_type_to_string()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testDataTypesXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var row = import.GetRows(1, false).ToList()[0].ToList();
Assert.AreEqual("abcdef", row[0]);
Assert.AreEqual("12345", row[2]);
Assert.AreEqual("123.45", row[3]);
Assert.AreEqual("0778788990", row[4]);
Assert.AreEqual("1.99", row[5]);
Assert.AreEqual("0.5025", row[6]);
}
}
public void Should_dispose_instance()
{
using (var import = new ExcelImport())
{
}
}
public void Should_be_disposable()
{
var import = new ExcelImport();
Assert.IsTrue(import is IDisposable);
}
private void DoImportData()
{
var importArgs = new ImportArgs<Mouse>
{
Filename = filename,
WorkSheetName = sheet
};
importArgs.Columns.Clear();
foreach (var columnViewModel in Columns)
{
var reader = columnViewModel.GetCellReader();
if (reader != null)
{
importArgs.Columns.Add((ICellReader<Mouse>) reader);
}
}
var excelImport = new ExcelImport<Mouse>(context,
mice => mice.Include(m => m.Records).Include(m => m.Locality),
c => c.Localities.Include(l => l.Mice).Load());
excelImport.ProgressChanged += (o, e) => { Progress = excelImport.Progress*100; };
excelImport.Finished += (o, e) =>
{
excelImport.Save();
MessageBox.Show("Import complete");
};
excelImport.Cancelled += (o, e) => { MessageBox.Show("Import cancelled."); };
excelImport.Error += (o, e) => { MessageBox.Show("ERROR!\n\n" + e.Error); };
excelImport.Start(importArgs);
}
public void Should_throw_exception_if_file_stream_for_xls_null()
{
var import = new ExcelImport();
Assert.Throws<ArgumentNullException>(() => import.Open(null, ExcelFileType.XLS, true));
}
public void Should_throw_ArgumentExcetion_if_asking_for_header_row_when_file_does_not_have_header_row()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, false);
Assert.Throws<ArgumentException>(() => import.GetRows(1, true));
}
}
private void DoImportLocalities()
{
var importArgs = new ImportArgs<Locality>
{
Filename = filename,
WorkSheetName = sheet
};
importArgs.Columns.Clear();
foreach (var columnViewModel in Columns)
{
var reader = columnViewModel.GetCellReader();
if (reader != null)
{
importArgs.Columns.Add((ICellReader<Locality>)reader);
}
}
var excelImport = new ExcelImport<Locality>(context, locality => locality, _ => { });
excelImport.ProgressChanged += (o, e) => { Progress = excelImport.Progress*100; };
excelImport.Cancelled += (o, e) => { MessageBox.Show("Import cancelled."); };
excelImport.Error += (o, e) => { MessageBox.Show("ERROR!\n\n" + e.Error); };
excelImport.Finished += (o, e) =>
{
excelImport.Save();
MessageBox.Show("Import complete");
};
excelImport.Start(importArgs);
}
public void Should_throw_InvalidOperationException_if_hasHeaderRow_is_false_for_XLSX()
{
import = new ExcelImport();
using (FileStream stream = new FileStream(_testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, false);
Assert.Throws<InvalidOperationException>(() => import.GetRowsAsKeyValuePairs(1));
}
}
public void Should_return_keyvaluepairs_for_xlsx()
{
var import = new ExcelImport();
using (FileStream stream = new FileStream(_testFileXlsx, FileMode.Open, FileAccess.Read))
{
import.Open(stream, ExcelFileType.XLSX, true);
var values = import.GetRowsAsKeyValuePairs(0);
Assert.That(values, Is.Not.Empty);
}
}
public ActionResult Index(HttpPostedFileBase file)
{
// Verify that the user selected a file
if (file != null && file.ContentLength > 0)
{
//if (verficarExcel(file.FileName))
//{
// extract only the fielname
var fileName = Path.GetFileName(file.FileName);
ExcelImport excelImport = new ExcelImport();
// store the file inside ~/App_Data/uploads folder
string strExtension = Path.GetExtension(fileName);
string strUploadFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;
var path = Path.Combine(Server.MapPath("~/UploadFiles"), strUploadFileName);
file.SaveAs(path);
String resul = Convert.ToString(excelImport.importToSQL(strExcelConn(path, strUploadFileName)));
//}
}
// redirect back to the index action to show the form once again
return RedirectToAction("Index", "Establecimientos");
}
