private bool LoadExcelFile(string FilePath)
{
try
{
int nRowHeader = 0;
XlsReader Reader = new XlsReader(FilePath);
dsFile.Tables.Add(Reader.Table);
if (Contains_Header == true)
{
nRowHeader = Row_Start_No - 1;
for (int i = 0; i < Row_Start_No - 1; i++)
{
if (nRowHeader > 0)
{
i = 0;
dsFile.Tables[0].Rows.RemoveAt(i);
nRowHeader = nRowHeader - 1;
}
else
{
break;
}
}
}
Reader.Close();
return(true);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(false);
}
}
public override void OnInspectorGUI()
{
EditorGUILayout.LabelField("");
XlsReader script = target as XlsReader;
#region Idioma
/////////////w/////////////////////////////////////////////////////////////////////
/*EditorGUILayout.LabelField("Idioma");
* script.idioma = EditorGUILayout.Popup(script.idioma, idioma);*/
///////////////////////////////////////////////////////////////////////////////
#endregion
#region Contexto
EditorGUILayout.LabelField("");
EditorGUILayout.LabelField("Contexto");
script.contexto = EditorGUILayout.Popup(script.contexto, contexto);
#endregion
#region busqueda
EditorGUILayout.LabelField("");
EditorGUILayout.LabelField("ID de Texto");
script.idKey = EditorGUILayout.TextField(script.idKey);
if (GUILayout.Button("Buscar ID"))
{
Debug.Log(script.Search(Application.dataPath + "/Textos.xlsx"));
}
#endregion
}
public IFormatReader <IFileOptionsSet> CreateReader(ReaderType type, string path, IOptionsSet baseOptions = null)
{
IFormatReader <IFileOptionsSet> result = null;
FileOptionsSet options = new FileOptionsSet(baseOptions ?? OptionsSet.Empty)
{
FilePath = path
};
switch (type)
{
case ReaderType.Ascii:
result = new AsciiReader(options);
break;
case ReaderType.Excel:
case ReaderType.Excel2007:
result = new XlsReader(options);
break;
case ReaderType.Dbf:
result = new DbfReader(options);
break;
case ReaderType.Db:
throw new InvalidOperationException("use CreateDbReader()");
default:
throw new UnimplementedReaderTypeException(type);
}
return(result);
}
private ImportResult ReadXls(string filePath, int sheetIndex = 0, int maxRows = 0)
{
var result = new ImportResult();
IList <string[]> lstValues = new List <string[]>();
try
{
var reader = new XlsReader(filePath)
{
CurrentSheet = sheetIndex
};
reader.ReadRecord();
for (int i = 0; i < reader.RecordCount && (maxRows <= 0 || i < maxRows); i++)
{
bool record = reader.ReadRecord();
lstValues.Add(reader.Values);
}
result.StatusMessage = string.Format(
"Operation completed on {0} record(s). {1} Columns Detected",
reader.RecordCount,
reader.ColumnCount);
}
catch (Exception ex)
{
result.StatusMessage = string.Format("Exception thrown: {0}", ex.Message);
}
result.Values = lstValues;
return(result);
}
public void Presionado()
{
PlayerPrefs.SetInt("Idioma", Idioma);
XlsReader reader = FindObjectOfType(typeof(XlsReader)) as XlsReader;
texto.text = reader.Search(Application.dataPath + "/Textos.xlsx");
print("Click");
print(PlayerPrefs.GetInt("Idioma"));
}
/// <summary>
/// xls文档
/// </summary>
/// <param name="xlsStream">xls流</param>
/// <exception cref="ArgumentNullException"></exception>
public XlsDoc(Stream xlsStream)
{
if (xlsStream == null)
{
throw new ArgumentNullException(nameof(xlsStream));
}
using var dataset = XlsReader.ReadAsDataSet(xlsStream);
this.sheets = this.Parse(dataset).ToList();
}
public void processCitiFiles(string fileDate)
{
string fileXLS = "";
int filecount = 0;
XlsReader xlsReader = new XlsReader();
XlsReader.FileTypes fileType = XlsReader.FileTypes.XLSType;
string path = ConfigurationManager.AppSettings.Get("CitiSourceFolder");
//Step 1 : Read all available file in the directory
string fileToProcess = "*" + fileDate + "*.*";
string[] files = Directory.GetFiles(path, fileToProcess, SearchOption.AllDirectories);
Console.WriteLine();
if (files.Length != 0)
{
for (int i = 0; i < files.Length; i++)
{
filecount++;
fileXLS = "";
string fileXLSToProcess = "";
// Get the file from path
fileXLS = @files[i];
string fileExt = Path.GetExtension(fileXLS);
//Rename the file to XLS format
if (fileExt.ToLower() != ".xls")
{
File.Move(fileXLS, fileXLS + ".xls");
fileXLSToProcess = fileXLS + ".xls";
}
else
{
fileXLSToProcess = fileXLS;
}
// ReadFile(sampleCSV);
DataTable xlsDt = xlsReader.ReadXLSToDT(fileType, fileXLSToProcess);
if (xlsDt.Columns.Count == 7)
{
processCitiData(xlsDt, fileXLSToProcess);
}
else
{
Console.WriteLine(string.Format("skippiing {0} file as it seems empty", fileXLSToProcess));
}
}
}
}
/// <summary>
/// xls文档
/// </summary>
/// <param name="xls">xls文件路径</param>
/// <exception cref="ArgumentNullException"></exception>
/// <exception cref="FileNotFoundException"></exception>
public XlsDoc(string xls)
{
if (xls.IsNullOrEmpty())
{
throw new ArgumentNullException(nameof(xls));
}
if (File.Exists(xls) == false)
{
throw new FileNotFoundException($"找不到文件：{xls}");
}
using var xlsStream = new FileStream(xls, FileMode.Open, FileAccess.Read);
using var dataset = XlsReader.ReadAsDataSet(xlsStream);
this.sheets = this.Parse(dataset).ToList();
}
public async Task <ActionResult <AdsImportResultViewModel> > ImportReferential(ImportOptionsViewModel options)
{
var extension = System.IO.Path.GetExtension(options.FileName);
if (string.IsNullOrEmpty(extension))
{
return(BadRequest("extension"));
}
IList <ExchangeDataRow> models = new
List <ExchangeDataRow>();
var result = new AdsImportResultViewModel();
switch (extension)
{
case ".xlsx":
case ".xls":
{
using var xlsReader = new XlsReader <ExchangeDataRow>(options);
models = xlsReader.Read();
break;
}
case ".xml":
{
var xmlReader = new XmlReader <ExchangeDataRow>(options);
models = xmlReader.Read();
break;
}
case ".csv":
case ".txt":
{
var csvReader = new CsvReader <ExchangeDataRow>(options);
models = csvReader.Read();
break;
}
}
await StartProcessImportProduct(options, models, result);
return(Ok(result));
}
public async Task Import(ImportOptionsViewModel importOptions)
{
var extension = System.IO.Path.GetExtension(importOptions.FileName);
IList <AdsCategoryViewModel> models = new
List <AdsCategoryViewModel>();
if (extension.Equals(".xlsx") || extension.Equals(".xls"))
{
using var xlsReader = new XlsReader <AdsCategoryViewModel>(importOptions);
models = xlsReader.Read();
}
else if (extension.Equals(".xml"))
{
var xmlReader = new XmlReader <AdsCategoryViewModel>(importOptions);
models = xmlReader.Read();
ImportSubCategories(models, xmlReader);
}
// await _adsUnitOfWork.CategoriesRepository.Clear();
// await _adsUnitOfWork.CategoriesRepository.BulkInsertAsync(models);
}
static void Main(string[] args)
{
XlsReader reader = new XlsReader("file");
Console.Write(reader.getName());
}
private void Form1_Load(object sender, System.EventArgs e)
{
reader = new XlsReader("../../../../../sample data/products.xls");
reader.Settings.HasHeaders = true;
comboBox1.DataSource = reader.SheetNames;
}
private void LoadFromFile()
{
reader = new XlsReader(fileToLoad);
reader.Settings.HasHeaders = true;
sheetNames.DataSource = reader.SheetNames;
}
static void Main(string[] args)
{
var configuration = GetConfiguration();
var telemetryClient = new TelemetryClient()
{
InstrumentationKey = configuration["APPINSIGHTS_INSTRUMENTATIONKEY"]
};
var logger = new LoggerConfiguration()
.ReadFrom.Configuration(configuration)
.WriteTo
.ApplicationInsightsTraces(configuration["APPINSIGHTS_INSTRUMENTATIONKEY"])
.Enrich.WithProperty("WebJob", "UcasCourseImporter")
.Enrich.WithProperty("WebJob_Identifer", Guid.NewGuid())
.Enrich.WithProperty("WebJob_Triggered_Date", DateTime.UtcNow)
.CreateLogger();
var folder = Path.Combine(Path.GetTempPath(), "ucasfiles", Guid.NewGuid().ToString());
try
{
logger.Information("UcasCourseImporter started.");
var configOptions = new UcasCourseImporterConfigurationOptions(configuration);
configOptions.Validate();
var mcConfig = new McConfig(configuration);
Directory.CreateDirectory(folder);
var downloadAndExtractor = new DownloaderAndExtractor(logger, folder, configOptions.AzureUrl,
configOptions.AzureSignature);
var unzipFolder = downloadAndExtractor.DownloadAndExtractLatest("NetupdateExtract");
var unzipFolderProfiles = downloadAndExtractor.DownloadAndExtractLatest("EntryProfilesExtract_test");
var xlsReader = new XlsReader(logger);
// only used to avoid importing orphaned data
// i.e. we do not import institutions but need them to determine which campuses to import
var subjects = xlsReader.ReadSubjects("data");
// entry profile data - used to correct institution data
var institutionProfiles = ReadInstitutionProfiles(unzipFolderProfiles);
// data to import
var institutions = xlsReader.ReadInstitutions(unzipFolder);
UpdateContactDetails(institutions, institutionProfiles);
var campuses = xlsReader.ReadCampuses(unzipFolder, institutions);
var courses = xlsReader.ReadCourses(unzipFolder, campuses);
var courseSubjects = xlsReader.ReadCourseSubjects(unzipFolder, courses, subjects);
var courseNotes = xlsReader.ReadCourseNotes(unzipFolder);
var noteTexts = xlsReader.ReadNoteText(unzipFolder);
var payload = new UcasPayload
{
Institutions = new List <Xls.Domain.UcasInstitution>(institutions),
Courses = new List <UcasCourse>(courses),
CourseSubjects = new List <UcasCourseSubject>(courseSubjects),
Campuses = new List <UcasCampus>(campuses),
CourseNotes = new List <UcasCourseNote>(courseNotes),
NoteTexts = new List <UcasNoteText>(noteTexts),
Subjects = new List <UcasSubject>(subjects)
};
var context = GetDbContext(mcConfig);
var ucasDataMigrator = new UcasDataMigrator(context, logger, payload);
ucasDataMigrator.UpdateUcasData();
}
catch (Exception e)
{
logger.Error(e, "UcasCourseImporter error.");
}
finally
{
CleanupTempData(folder, logger);
logger.Information("UcasCourseImporter finished.");
// flush logs and wait for them to be written. https://github.com/serilog/serilog-sinks-applicationinsights#how-when-and-why-to-flush-messages-manually
telemetryClient.Flush();
Thread.Sleep(5000);
}
}
static void Main(string[] args)
{
var configuration = GetConfiguration();
var logger = new LoggerConfiguration()
.ReadFrom.Configuration(configuration)
.WriteTo
.ApplicationInsightsTraces(configuration["APPINSIGHTS_INSTRUMENTATIONKEY"])
.CreateLogger();
logger.Information("UcasCourseImporter started.");
var configOptions = new UcasCourseImporterConfigurationOptions(configuration);
configOptions.Validate();
var folder = Path.Combine(Path.GetTempPath(), "ucasfiles", Guid.NewGuid().ToString());
try
{
Directory.CreateDirectory(folder);
var downloadAndExtractor = new DownloaderAndExtractor(logger, folder, configOptions.AzureUrl,
configOptions.AzureSignature);
var unzipFolder = downloadAndExtractor.DownloadAndExtractLatest("NetupdateExtract");
var unzipFolderProfiles = downloadAndExtractor.DownloadAndExtractLatest("EntryProfilesExtract_test");
var xlsReader = new XlsReader(logger);
// only used to avoid importing orphaned data
// i.e. we do not import institutions but need them to determine which campuses to import
var subjects = xlsReader.ReadSubjects("data");
// entry profile data - used to correct institution data
var institutionProfiles = ReadInstitutionProfiles(unzipFolderProfiles);
// data to import
var institutions = xlsReader.ReadInstitutions(unzipFolder);
UpdateContactDetails(institutions, institutionProfiles);
var campuses = xlsReader.ReadCampuses(unzipFolder, institutions);
var courses = xlsReader.ReadCourses(unzipFolder, campuses);
var courseSubjects = xlsReader.ReadCourseSubjects(unzipFolder, courses, subjects);
var courseNotes = xlsReader.ReadCourseNotes(unzipFolder);
var noteTexts = xlsReader.ReadNoteText(unzipFolder);
var payload = new UcasPayload
{
Institutions = new ObservableCollection <UcasInstitution>(institutions),
Courses = new ObservableCollection <UcasCourse>(courses),
CourseSubjects = new ObservableCollection <UcasCourseSubject>(courseSubjects),
Campuses = new ObservableCollection <UcasCampus>(campuses),
CourseNotes = new ObservableCollection <UcasCourseNote>(courseNotes),
NoteTexts = new ObservableCollection <UcasNoteText>(noteTexts)
};
var manageApi = new ManageApi(logger, configOptions.ManageApiUrl, configOptions.ManageApiKey);
manageApi.PostPayload(payload);
}
catch (Exception e)
{
logger.Error(e, "UcasCourseImporter error.");
}
finally
{
CleanupTempData(folder, logger);
logger.Information("UcasCourseImporter finished.");
}
}
