private void RunAnalysis(object sender, RoutedEventArgs e)
{
if (this.FileSelection.MultipleFilesChecked)
{
var filePaths = this.FileSelection.SelectedFiles;
if (filePaths == null || filePaths.Length == 0)
{
AlertManager.NoFileSelected();
return;
}
int count = filePaths.Select(excelFile => new ExcelWrapper(excelFile)).Select(excelWrapper => excelWrapper.GetCount()).Sum();
this.Logger.Log(count + " entries!");
}
else
{
string filePath = this.FileSelection.SelectedFile;
if (!File.Exists(filePath))
{
AlertManager.NoFileSelected();
return;
}
var excelWrapper = new ExcelWrapper(filePath);
this.Logger.Log(excelWrapper.GetCount().ToString());
}
}
private string[] CheckColumns(string[] columns, ExcelWrapper excelWrapper, string value, bool caseSensitive)
{
var output = new List <string>();
foreach (string columnNumber in columns)
{
int column = ExcelWrapper.ConvertStringColumnToNumber(columnNumber);
string[] rows = excelWrapper.GetStringRows(column);
if (rows != null)
{
for (int j = 0; j < rows.Length; j++)
{
string rowValue = rows[j];
if (rowValue != null && rowValue.Equals(value))
{
output.Add($"\"{rowValue}\" was found at {columnNumber}{j}");
}
else if (rowValue != null && !caseSensitive && string.Equals(rowValue, value, StringComparison.CurrentCultureIgnoreCase))
{
output.Add($"\"{rowValue}\" was found at {columnNumber}{j}");
}
}
}
else
{
AlertManager.Custom($"There isn't {columnNumber} column in {excelWrapper.FileName}");
}
}
return(output.ToArray());
}
private static object ColumnConverted(ExcelWrapper excelWrapper, string column)
{
// ReSharper disable once CoVariantArrayConversion
var jArray = new JArray(excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column)));
return(jArray);
}
private void Convert(string filePath)
{
var excelWrapper = new ExcelWrapper(filePath);
string typeOfJsonConvert = this.TypeOfJsonArrayConversionContainer.Children.OfType <RadioButton>()
.First(r => r.IsChecked == true).DataContext.ToString();
object json = null;
switch (typeOfJsonConvert)
{
case "Rows":
{
json = RowConverter(excelWrapper, this.InputRow.Number - 1);
break;
}
case "Columns":
{
json = ColumnConverted(excelWrapper, this.InputColumn.Text);
break;
}
case "Excel":
{
json = ExcelConverter(excelWrapper);
break;
}
}
this.Output.OutputTextBox.Text = JsonConvert.SerializeObject(json, Formatting.Indented);
this.Output.FileName = excelWrapper.FileName.Split('.')[0] + ".json";
}
private void RunMultipleFilesAnalysis(object sender, RoutedEventArgs e)
{
if (this.FileSelection.SelectedFiles == null)
{
AlertManager.NoFileSelected();
return;
}
string[] filePaths = this.FileSelection.SelectedFiles;
double sum = 0;
foreach (string filePath in filePaths)
{
var excelWrapper = new ExcelWrapper(filePath);
string firstColumn = this.FirstColumn.Text;
int firstColumnNumber = ExcelWrapper.ConvertStringColumnToNumber(firstColumn);
double[] firstColumnValues = excelWrapper.GetDoubleRows(firstColumnNumber);
string fileName = Path.GetFileName(filePath);
if (firstColumnValues == null)
{
MessageBox.Show($"There is no column '{firstColumn}' in {fileName}");
return;
}
sum += firstColumnValues.Sum();
}
this.MultipleFilesOutput.Text = $"Summation of all excel files\n{sum}";
}
private void RunAnalysis(object sender, RoutedEventArgs e)
{
var firstExcelWrapper = new ExcelWrapper(this.FirstFileSelection.SelectedFile);
var secondExcelWrapper = new ExcelWrapper(this.SecondFileSelection.SelectedFile);
if (firstExcelWrapper.FileName == secondExcelWrapper.FileName)
{
AlertManager.Custom("The excel files must be different!");
return;
}
string[] columns = this.ColumnTextBox.GetColumns();
var selectedComparisonType = (ComparisonType)Enum.Parse(typeof(ComparisonType), this.ComparisonTypePanel.Children.OfType <RadioButton>()
.First(r => r.IsChecked == true).DataContext.ToString());
var logs = new List <string>();
string emptyOutput = string.Empty;
if (selectedComparisonType == ComparisonType.FindSimilarities)
{
emptyOutput = "No similarities were found between both excels!";
foreach (string column in columns)
{
int columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column);
string[] firstExcelRows = firstExcelWrapper.GetStringRows(columnNumber)
.Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
string[] secondExcelRows = secondExcelWrapper.GetStringRows(columnNumber)
.Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
logs.AddRange(firstExcelRows.Where(x => secondExcelRows.Contains(x))
.Select(x => $"'{x}' was found in the both excels"));
}
}
else if (selectedComparisonType == ComparisonType.FindDifferences)
{
emptyOutput = "No differences were found between both excels!";
foreach (string column in columns)
{
int columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column);
string[] firstExcelRows = firstExcelWrapper.GetStringRows(columnNumber)
.Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
string[] secondExcelRows = secondExcelWrapper.GetStringRows(columnNumber)
.Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
logs.AddRange(firstExcelRows.Where(x => !secondExcelRows.Contains(x))
.Select(x =>
$"'{x}' was found in {firstExcelWrapper.FileName} but not in {secondExcelWrapper.FileName}"));
}
}
this.Logger.Log(logs.Count > 0
? string.Join(Environment.NewLine, logs)
: emptyOutput);
}
private void SumOperation()
{
var excelWrappers = this.FileSelection.SelectedFiles.Select(path => new ExcelWrapper(path)).ToArray();
string column = this.ColumnInput.Text;
int columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column);
double sum = excelWrappers.Sum(x => x.GetDoubleRows(columnNumber).Sum());
this.Output.OutputTextBox.Text = sum.ToString("F2");
}
private static object RowConverter(ExcelWrapper excelWrapper, int row)
{
string[] columns = excelWrapper.GetColumns();
var jArray = new JArray();
foreach (string column in columns)
{
jArray.Add(excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))[row]);
}
return(jArray);
}
private void FindDuplicatesAnalysis(ExcelWrapper[] excelWrappers, bool specificColumns)
{
foreach (var excelWrapper in excelWrappers)
{
var duplicates = new List <string>();
string[] columns = specificColumns ? this.ColumnTextBox.GetColumns() : excelWrapper.GetColumns();
foreach (string column in columns)
{
int columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column);
string[] cells = excelWrapper.GetStringRows(columnNumber)?.Where(cell => !string.IsNullOrEmpty(cell)).ToArray();
if (cells != null)
{
var cellEntries = new Dictionary <string, int>();
foreach (string cell in cells)
{
if (!cellEntries.ContainsKey(cell))
{
cellEntries.Add(cell, 1);
}
else
{
cellEntries[cell]++;
}
}
string checkingColumn = $"Checking column '{column}'";
duplicates.Add(checkingColumn);
duplicates.AddRange(from pair in cellEntries where pair.Value > 1 select $"'{pair.Key}' is entered {pair.Value} times");
if (duplicates.Last() == checkingColumn)
{
duplicates.Add("Nothing found!");
}
}
else
{
duplicates.Add($"There is no '{column}' column in {excelWrapper.FileName}");
}
}
string log = string.Join("\n", duplicates);
this.Logger.Log($"{excelWrapper.FileName}\r\n" + log);
}
}
private static object ExcelConverter(ExcelWrapper excelWrapper)
{
string[] columns = excelWrapper.GetColumns();
var jArray = new JArray();
foreach (string column in columns)
{
// ReSharper disable once CoVariantArrayConversion
jArray.Add(new JArray(excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))));
}
return(jArray);
}
static void Main(string[] args)
{
ExcelWrapper wrapper = new ExcelWrapper();
if (wrapper.OpenExcelFile(@"c:\1.xlsx", true))
{
string val = wrapper.GetCellValue("Лист1", 1, 1);
if (wrapper.SetCellValue("Лист1", val + "X", 1, 1))
{
string val2 = wrapper.GetCellValue("Лист1", 1, 1);
}
}
wrapper.Close();
}
static void Main(string[] args)
{
var watch = new Stopwatch();
var excelPath = "C:/Excel/test.xlsx";
watch.Start();
var bytes = ExcelWrapper.Export(new ExportExcel <Test>[] { new ExportExcel <Test>("DATA IMPORT", GenerateData()) });
File.WriteAllBytes(excelPath, bytes);
watch.Stop();
Console.WriteLine($"Tiempo: {watch.Elapsed}");
watch.Reset();
watch.Start();
var arrayBytes = File.ReadAllBytes(excelPath);
//var arrayBytes = File.ReadAllBytes("C:/Excel/test2.xlsx");
var resultExcel = ExcelWrapper.Import <Test>(arrayBytes);
watch.Stop();
try
{
var templatetPath = "C:/Excel/test_template.xlsx";
var fileTemplate = File.ReadAllBytes(templatetPath);
var paramst = new[] {
new TemplateData("nombre", "Michael Emir"),
new TemplateData("fecha", "12/08/1992"),
new TemplateData("correlativo", "CE-3450P"),
new TemplateData("ncuenta", "1209-9021-312"),
new TemplateData("valor", "1,900.75"),
new TemplateData("table", GenerateData())
};
var resultBytes = ExcelWrapper.ExportWithTemplate(new List <ExportTemplateExcel> {
new ExportTemplateExcel("TEST", paramst)
}, fileTemplate);
File.WriteAllBytes("C:/Excel/result_template.xlsx", resultBytes);
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
Console.WriteLine($"Tiempo: {watch.Elapsed}");
Console.Read();
}
private void ConcatOperation()
{
var excelWrappers = this.FileSelection.SelectedFiles.Select(path => new ExcelWrapper(path));
string column = this.ColumnInput.Text;
int columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column);
var stringBuilder = new StringBuilder();
foreach (var excelWrapper in excelWrappers)
{
string[] entries = excelWrapper.GetStringRows(columnNumber);
foreach (string entry in entries)
{
stringBuilder.Append(entry);
}
}
this.Output.OutputTextBox.Text = stringBuilder.ToString();
}
private void btnDocumentoRelatorio_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
Action <ExcelWrapper> fncGeraHeaderExcel = (objExcel) =>
{
using (var context = new CadastroMotoristaContext())
{
BindingSource bi = new BindingSource();
dt = context.Documentos.ToList().ToDataTable();
objExcel.GeraHeader(false, dt.Columns.Cast <DataColumn>()
.Select(x => x.ColumnName)
.ToArray());
}
};
using (ExcelWrapper ex = new ExcelWrapper("Controle de Motoristas", "Documentos", "documentos"))
{
fncGeraHeaderExcel(ex);
foreach (DataRow row in dt.Rows)
{
uint colcnt = 0;
foreach (DataColumn dc in dt.Columns)
{
ex.AdicionaConteudo(row[dc].ToString(), colcnt++);
}
ex.IncrementaLinha();
}
ex.AjustaHeader();
MemoryStream ms = new MemoryStream();
ex.WriteToMemory(ms);
string varq = MemoryStreamParaArquivo(ms, ".xlsx", true);
if (varq != string.Empty)
{
System.Diagnostics.Process.Start(varq);
}
}
}
private void Convert(string filePath)
{
var excelWrapper = new ExcelWrapper(filePath);
string[] columns = excelWrapper.GetColumns();
string typeOfJsonConvert = this.TypeOfHtmlConversionContainer.Children.OfType <RadioButton>()
.First(r => r.IsChecked == true).DataContext.ToString();
string html = string.Empty;
if (typeOfJsonConvert == "Table")
{
html = "<table>\r\n\t<tbody>\r\n\t\t<tr>" + columns
.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))[0])
.Aggregate(html, (current, propertyName) => current + $"\r\n\t\t\t<th>{propertyName}</th>");
html += "\r\n\t\t</tr>";
string[][] propertyValues = columns.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column)).Skip(1).ToArray())
.ToArray();
var list = new List <List <string> >();
foreach (string[] values in propertyValues)
{
for (int j = 0; j < values.Length; j++)
{
if (list.Count >= j + 1)
{
list[j].Add(values[j]);
}
else
{
list.Add(new List <string> {
values[j]
});
}
}
}
html = list.Aggregate(html,
(current, row) =>
current +
$"\r\n\t\t<tr>\r\n{string.Join("\r\n", row.Select(x => $"\t\t\t<td>{x}</td>"))}\r\n\t\t</tr>") +
"\r\n\t</tbody>\r\n</table>";
}
else if (typeOfJsonConvert == "List")
{
string[] propertyNames = columns.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))[0]).ToArray();
string[][] propertyValues = columns.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column)).ToArray())
.ToArray();
int rowsCount = propertyValues.Select(propertyValue => propertyValue.Length).Max();
var list = new List <string>();
for (int i = 0; i < rowsCount; i++)
{
string ul = "<ul>";
for (int j = 0; j < propertyNames.Length; j++)
{
ul += $"\r\n\t<li>{propertyValues[j][i]}</li>";
}
list.Add(ul + "</ul>");
}
html = string.Join("\r\n", list);
}
this.Output.OutputTextBox.Text = html;
this.Output.FileName = excelWrapper.FileName.Split('.')[0] + ".html";
}
private void RunColumnAnalysis(object sender, RoutedEventArgs e)
{
if (this.FileSelection.SelectedFiles == null)
{
AlertManager.NoFileSelected();
return;
}
var operation = (OperationType)this.OperationSelector.SelectedIndex;
string[] filePaths = this.FileSelection.SelectedFiles;
var output = new List <string>();
foreach (string filePath in filePaths)
{
var excelWrapper = new ExcelWrapper(filePath);
string firstColumn = this.FirstColumn.Text;
string secondColumn = this.SecondColumn.Text;
int firstColumnNumber = ExcelWrapper.ConvertStringColumnToNumber(firstColumn);
int secondColumnNumber = ExcelWrapper.ConvertStringColumnToNumber(secondColumn);
double[] firstColumnValues = excelWrapper.GetDoubleRows(firstColumnNumber);
double[] secondColumnValues = excelWrapper.GetDoubleRows(secondColumnNumber);
string fileName = Path.GetFileName(filePath);
if (firstColumnValues == null)
{
MessageBox.Show($"There is no column '{firstColumn}' in {fileName}");
return;
}
if (secondColumnValues == null)
{
MessageBox.Show($"There is no column '{secondColumn}' in {fileName}");
return;
}
double[] newValues = new double[firstColumnValues.Length > secondColumnValues.Length
? firstColumnValues.Length
: secondColumnValues.Length];
switch (operation)
{
case OperationType.Divide:
for (int i = 0; i < newValues.Length; i++)
{
newValues[i] = firstColumnValues[i] / secondColumnValues[i];
}
break;
case OperationType.Multiply:
for (int i = 0; i < newValues.Length; i++)
{
newValues[i] = firstColumnValues[i] * secondColumnValues[i];
}
break;
case OperationType.Add:
for (int i = 0; i < newValues.Length; i++)
{
newValues[i] = firstColumnValues[i] + secondColumnValues[i];
}
break;
case OperationType.Subtract:
for (int i = 0; i < newValues.Length; i++)
{
newValues[i] = firstColumnValues[i] - secondColumnValues[i];
}
break;
}
output.Add($"Results from '{fileName}':");
output.Add(string.Join("\n", newValues));
}
this.ColumnOutput.Text = string.Join("\n", output);
}
private void Convert(string filePath)
{
var excelWrapper = new ExcelWrapper(filePath);
string[] columns = excelWrapper.GetColumns();
string typeOfJsonConvert = this.TypeOfJsonConversionContainer.Children.OfType <RadioButton>()
.First(r => r.IsChecked == true).DataContext.ToString();
object json = null;
if (typeOfJsonConvert == "Rows")
{
string[] propertyNames = columns.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))[0]).ToArray();
string[][] propertyValues = columns.Select(column =>
excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column)).Skip(1).ToArray())
.ToArray();
int jObjectCount = propertyValues.Select(propertyValue => propertyValue.Length).Max();
var jsonObjects = new List <JObject>();
for (int i = 0; i < jObjectCount; i++)
{
var jsonObject = new JObject();
for (int j = 0; j < propertyNames.Length; j++)
{
jsonObject.Add(propertyNames[j], propertyValues[j][i]);
}
jsonObjects.Add(jsonObject);
}
json = jsonObjects;
this.Output.OutputTextBox.Text = JsonConvert.SerializeObject(jsonObjects, Formatting.Indented);
}
else if (typeOfJsonConvert == "Columns")
{
var jsonProperties = (
from column in columns
let dataRows = excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column))
select new JProperty(dataRows[0], dataRows.Skip(1))
).ToList();
json = new JObject(jsonProperties);
}
else if (typeOfJsonConvert == "Excel")
{
var jsonData = (from column in columns
let columnNumber = ExcelWrapper.ConvertStringColumnToNumber(column)
select new JObject(new JProperty("Column", column), new JProperty("ColumnNumber", columnNumber),
new JProperty("Data", excelWrapper.GetValueRows(columnNumber)))
).ToList();
json = jsonData;
}
else if (typeOfJsonConvert == "Array")
{
var jArray = new JArray();
foreach (string column in columns)
{
jArray.Add(excelWrapper.GetValueRows(ExcelWrapper.ConvertStringColumnToNumber(column)));
}
json = jArray;
}
this.Output.OutputTextBox.Text = JsonConvert.SerializeObject(json, Formatting.Indented);
this.Output.FileName = excelWrapper.FileName.Split('.')[0] + ".json";
}
public static Statistics CalcStatistics(ExcelWrapper excelWrapper, MaterialMatrix materialMat)
{
Statistics ret = new Statistics();
#if !DEBUG
try
#endif
{
if (materialMat == null || !materialMat.IsNotNegativ() || materialMat.CountNonNegativeEntrys() < 3)
{
return(null);
}
MaterialMatrix tmpMaterialMatrix = materialMat.Clone();
for (int colInd = 49; colInd < excelWrapper.GetRowCount(); colInd++)
{
string[] obj = excelWrapper.GetRow(colInd);
bool isAvai = false;
bool isResolvable = false;
if (string.IsNullOrWhiteSpace(obj[0]))
{
throw new Exception();
}
#if !DEBUG
try
#endif
{
isAvai = AvailabilityCheck.IsAvailable(obj, tmpMaterialMatrix);
isResolvable = AvailabilityCheck.CanResolve(obj);
}
#if !DEBUG
catch (Exception) { }
#endif
if (isAvai)
{
ret.countAllAvai++;
ret.allAvaiPumps.Add(obj);
}
ret.countAll++;
ret.allPumps.Add(obj);
if (obj[11] == "RKP" || obj[11] == "FRP")
{
ret.countRKP++;
ret.RKPPumps.Add(obj);
if (isAvai)
{
ret.countRKPAvai++;
ret.RKPAvaiPumps.Add(obj);
}
}
if (isResolvable)
{
ret.resolvable++;
ret.resolvablePumps.Add(obj);
if (isAvai)
{
ret.resolvableAvai++;
ret.resolvableAvaiPumps.Add(obj);
}
}
if (string.IsNullOrWhiteSpace(obj[0]))
{
throw new Exception();
}
}
}
#if !DEBUG
catch (Exception) { return(ret); }
#endif
return(ret);
}
