protected override void Execute(CodeActivityContext context)
{
var excelpath = ExcelPath.Get(context);
var pageindex = SheetIndex.Get(context);
var txtpath = TxtPath.Get(context);
var splitchar = SplitChar.Get(context);
var addheaders = AddHeaders.GetType();
Workbook wb = new Workbook();
wb.LoadFromFile(@excelpath);
Worksheet ws = wb.Worksheets[pageindex];
DataTable dt = ws.ExportDataTable();
//TXT VARSA İÇİNİ TEMİZLE
if (File.Exists(txtpath))
{
File.WriteAllText(txtpath, "");
}
//BAŞLIK
if (AddHeaders)
{
string[] columnNames = dt.Columns.Cast <DataColumn>().Select(x => x.ColumnName).ToArray();
File.AppendAllText(@txtpath, string.Join(splitchar, columnNames) + Environment.NewLine);
}
//SATIRLAR
foreach (DataRow dtR in dt.Rows)
{
File.AppendAllText(@txtpath, string.Join(splitchar, dtR.ItemArray) + Environment.NewLine);
}
}
public List <KeyValuePair <string, Type> > GetListOfExcelWorksheetColumns(string worksheetName)
{
List <KeyValuePair <string, Type> > columns = new List <KeyValuePair <string, Type> >();
try
{
_objCon.Open();
OleDbDataAdapter adp = new OleDbDataAdapter("Select * from [" + worksheetName + "$]", _objCon);
DataTable dt = new DataTable();
adp.Fill(dt);
_objCon.Close();
foreach (DataColumn column in dt.Columns)
{
string excelVersion = ExcelPath.Substring(ExcelPath.LastIndexOf('.'));
if (dt.Columns.Count == 1 && column.ToString().Equals("F1") && ((excelVersion.Equals(".xls") && dt.Rows.Count == 1) || (excelVersion.Equals(".xlsx") && dt.Rows.Count == 0)))
{
break;
}
columns.Add(new KeyValuePair <string, Type>(column.ColumnName, column.DataType));
}
return(columns);
}
catch (Exception)
{
return(columns);
}
finally
{
_objCon.Close();
}
}
public ExcelAsset(string excelName, string path = null)
{
mPath = new ExcelPath(excelName, path);
var ass = Assembly.Load("Assembly-CSharp");
var type = ass.GetType(mPath.ClassName);
if (type != null)
{
mValue = (BaseAsset)ScriptableObject.CreateInstance(type);
}
}
public bool SelectExcelPath()
{
try {
string initialFolder = null;
if (String.IsNullOrWhiteSpace(ExcelPath) || !File.Exists(ExcelPath))
{
var(is32bit, path) = DetectExcelVersion();
if (path != null)
{
ExcelArch = is32bit ? ArchType.x86 : ArchType.x64;
initialFolder = Path.GetDirectoryName(path);
}
else
{
System.Environment.GetFolderPath(System.Environment.SpecialFolder.ProgramFiles);
}
}
else
{
initialFolder = Path.GetDirectoryName(ExcelPath);
}
var ofd = new OpenFileDialog {
DereferenceLinks = true,
Filter = "MS Excel|excel.exe|All files (*.*)|*.*",
InitialDirectory = initialFolder,
Title = "Select Excel Path"
};
if (ofd.ShowDialog(Application.Current.MainWindow) == true)
{
ExcelPath = ofd.FileName;
if (ExcelPath.ToUpperInvariant().Contains("X86"))
{
ExcelArch = ArchType.x86;
}
else
{
ExcelArch = ArchType.x64;
}
}
return(true);
}
catch (Exception ex) {
logger.Error(ex, "Can't select Excel path");
return(false);
}
}
private void InitConext()
{
InitConnection();
var cmd = new OleDbCommand()
{
Connection = ExcelConnection,
CommandText = string.Format("SELECT * FROM `{0}`", DataSheet + '$')
};
table = new DataTable()
{
TableName = DataSheet
};
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(table);
ds.Tables.Add(table);
Console.WriteLine("Excel context {0} initiated.", ExcelPath.Split('\\').Last());
}
static List <ExcelPath> leerRutas(string path, DateTime fechaActual)
{
var paths = new List <ExcelPath>();
string[] lines = File.ReadAllLines(path);
foreach (string line in lines)
{
var dum = line.Split('|');
var pathVal = new ExcelPath(dum[0], dum[1]);
paths.Add(pathVal);
}
var pathDescuentosAgotados = paths.FirstOrDefault(x => x.Name == "AGOTADOS");
if (pathDescuentosAgotados != null)
{
var today = fechaActual;
while (true)
{
var todayNumero = Functions.ConvertToDateString(today);
var pathFinal = $"{pathDescuentosAgotados.Value}/descuentos_agotados_{todayNumero}.csv";
if (!File.Exists(pathFinal))
{
today = today.AddDays(-1);
continue;
}
pathDescuentosAgotados.Value = pathFinal;
break;
}
}
var pathMaestra = paths.FirstOrDefault(x => x.Name == "MAESTRA");
if (pathMaestra != null)
{
var today = fechaActual;
while (true)
{
var todayNumero = Functions.ConvertToDateString(today);
var pathFinal = $"{pathMaestra.Value}/Maestra_Descuentos_{todayNumero}.xlsx";
if (!File.Exists(pathFinal))
{
today = today.AddDays(-1);
continue;
}
pathMaestra.Value = pathFinal;
break;
}
}
var pathNuevos = paths.FirstOrDefault(x => x.Name == "NUEVOS");
if (pathNuevos != null)
{
var today = fechaActual;
while (true)
{
var todayNumero = Functions.ConvertToDateString(today);
var pathFinal = $"{pathNuevos.Value}/hive_output_{todayNumero}.tsv";
if (!File.Exists(pathFinal))
{
today = today.AddDays(-1);
continue;
}
pathNuevos.Value = pathFinal;
break;
}
}
return(paths);
}
