private void Btn_ExportPlin_Click(object sender, EventArgs e)
{
try
{
WorkBook ExcelPodatak = WorkBook.Create(ExcelFileFormat.XLS);
ExcelPodatak.Metadata.Author = "IronXL";
WorkSheet SpremiPodatak = ExcelPodatak.CreateWorkSheet("new_sheet");
SpremiPodatak["A1"].Value = "Mjesec";
SpremiPodatak["B1"].Value = cb_PMjesec.SelectedItem.ToString();
SpremiPodatak["A2"].Value = "Godina";
SpremiPodatak["B2"].Value = cb_Godina.SelectedItem.ToString();
SpremiPodatak["A3"].Value = "Staro Stanje";
SpremiPodatak["B3"].Value = tb_Plin1.Text;
SpremiPodatak["A4"].Value = "Novo Stanje";
SpremiPodatak["B4"].Value = tb_Plin2.Text;
SpremiPodatak["A5"].Value = "Razlika";
SpremiPodatak["B5"].Value = tb_Prazlika.Text;
SpremiPodatak["A6"].Value = lbl_Pizracunaj.Text;
ExcelPodatak.SaveAs(@"C:\Users\Dado\Desktop\Plin.xlsx");
Form DatotekaSpremljena = new DatotekaSpremljena();
DatotekaSpremljena.Show();
}
catch
{
MessageBox.Show("Zatvorite Postojecu datoteku prvo");
}
}
private void Btn_ExportStruja_Click(object sender, EventArgs e)
{
try
{
if (tb_Cijena.Text == "")
{
Form PopunitePolja = new PopunitePolja();
PopunitePolja.Show();
}
else
{
WorkBook ExcelPodatak = WorkBook.Create(ExcelFileFormat.XLS);
ExcelPodatak.Metadata.Author = "IronXL";
WorkSheet SpremiPodatak = ExcelPodatak.CreateWorkSheet("new_sheet");
SpremiPodatak["A1"].Value = "Mjesec";
SpremiPodatak["B1"].Value = cb_MMjesec.SelectedItem.ToString();
SpremiPodatak["A2"].Value = "Godina";
SpremiPodatak["B2"].Value = cb_Godina.SelectedItem.ToString();
SpremiPodatak["A3"].Value = "Provider";
SpremiPodatak["B3"].Value = cb_Internet.SelectedItem.ToString();
SpremiPodatak["A4"].Value = "Cijena Računa";
SpremiPodatak["B4"].Value = tb_Cijena.Text;
ExcelPodatak.SaveAs(@"C:\Users\Dado\Desktop\Mobitel.xls");
Form DatotekaSpremljena = new DatotekaSpremljena();
DatotekaSpremljena.Show();
}
}
catch
{
MessageBox.Show("Zatvorite postojeću datoteku prvo");
}
}
public FileXlsxUI(string fileToRead, string fileToSave, uint row, char column)
{
if (!string.IsNullOrEmpty(fileToRead) && !fileToRead.Equals(fileToSave))
{
FileToRead = $"{fileToRead}.xlsx";
FileToSave = $"{fileToSave}.xlsx";
if (File.Exists(FileToRead))
{
workBook = WorkBook.Load(FileToRead);
workSheet = workBook.DefaultWorkSheet;
}
else
{
workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workSheet = workBook.CreateWorkSheet("Main");
}
Row = row == 0 ? row + 1 : row;
Column = column;
RangeOfColumns = Column;
}
else
{
throw new ArgumentException("Name of a file can not be null or empty and have the same name as another file!");
}
}
public void WriteUsersToFile()
{
var saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel|*.xls|Excel 2010|*.xlsx";
if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
{
return;
}
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS);
xlsWorkbook.Metadata.Author = "KLB";
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet");
var users = _userRepository.GetAllUsers();
xlsSheet["A1"].Value = "Last Name";
xlsSheet["B1"].Value = "Year of Connection";
xlsSheet["C1"].Value = "Phone Number";
xlsSheet["D1"].Value = "Address";
for (int i = 2; i <= users.Count + 1; i++)
{
xlsSheet[$"A{i}"].Value = users[i - 2].SecondName;
xlsSheet[$"B{i}"].Value = users[i - 2].YearOfConnection;
xlsSheet[$"C{i}"].Value = users[i - 2].PhoneNumber;
xlsSheet[$"D{i}"].Value = users[i - 2].Address.ToString();
}
xlsSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
xlsSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
xlsWorkbook.SaveAs(saveFileDialog.FileName);
}
private void Form1_Load(object sender, EventArgs e)
{
workbook = WorkBook.Create(ExcelFileFormat.XLSX);
workbook.Metadata.Author = "User";
sheet = workbook.CreateWorkSheet("new_sheet");
UpdateTable();
}
private void Btn_ExportStruja_Click(object sender, EventArgs e)
{
try
{
WorkBook ExcelPodatak = WorkBook.Create(ExcelFileFormat.XLS);
ExcelPodatak.Metadata.Author = "IronXL";
WorkSheet SpremiPodatak = ExcelPodatak.CreateWorkSheet("new_sheet");
SpremiPodatak["A1"].Value = "Mjesec";
SpremiPodatak["B1"].Value = cb_SMjesec.SelectedItem.ToString();
SpremiPodatak["A2"].Value = "Godina";
SpremiPodatak["B2"].Value = cb_Godina.SelectedItem.ToString();
SpremiPodatak["A3"].Value = "Dnevno Staro Stanje";
SpremiPodatak["B3"].Value = tb_StaroStanjeVT.Text;
SpremiPodatak["A4"].Value = "Dnevno Novo Stanje";
SpremiPodatak["B4"].Value = tb_NovoStanjeVT.Text;
SpremiPodatak["A5"].Value = "Dnevna Razlika";
SpremiPodatak["B5"].Value = tb_RazlikaVT.Text;
SpremiPodatak["A6"].Value = "Nocno Staro Stanje";
SpremiPodatak["B6"].Value = tb_StaroStanjeNT.Text;
SpremiPodatak["A7"].Value = "Nocno Novo Stanje";
SpremiPodatak["B7"].Value = tb_NovoStanjeNT.Text;
SpremiPodatak["A8"].Value = "Nocna Razlika";
SpremiPodatak["B8"].Value = tb_RazlikaNT.Text;
SpremiPodatak["A9"].Value = lbl_Izracun.Text;
ExcelPodatak.SaveAs(@"C:\Users\Dado\Desktop\Struja.xls");
Form DatotekaSpremljena = new DatotekaSpremljena();
DatotekaSpremljena.Show();
}
catch
{
MessageBox.Show("Zatvorite postojecu datoteku prvo");
}
}
public static async Task Main(string[] args)
{
var scenarios = new[] { "Scenario 1", "Scenario 3", "Inner City" };
var path = Path.Combine(KnownFolders.Desktop.Path, "Caelicus", "final-final-results", "single orders");
var excel = WorkBook.Create(ExcelFileFormat.XLSX);
excel.Metadata.Title = "Caelicus Data Analysis";
excel.Metadata.Author = "Caelicus";
foreach (var scenario in scenarios)
{
var fullPath = Path.Combine(path, scenario);
var allFiles = Directory.GetFiles(fullPath, "*.json", SearchOption.AllDirectories);
var simHistories = new List <SimulationHistory>();
foreach (var file in allFiles)
{
simHistories.Add(JsonConvert.DeserializeObject <SimulationHistory>(await File.ReadAllTextAsync(file)));
}
await GenerateExcelSheet(simHistories, excel.CreateWorkSheet(scenario));
}
excel.SaveAs(Path.Combine(path, "analysis.xlsx"));
}
/// <summary>
/// The ExtractSheet.
/// </summary>
/// <param name="file">The file<see cref="string"/>.</param>
/// <param name="destination">The destination<see cref="string"/>.</param>
/// <param name="sheet">The sheet<see cref="string"/>.</param>
public static void ExtractSheet(string file, string destination, string sheet)
{
WorkBook workbook = WorkBook.Load(file);
WorkBook new_wb = WorkBook.Create();
var ws = workbook.GetWorkSheet(sheet);
try
{
ws.CopyTo(new_wb, "data");
}
catch (ArgumentException ae)
{
Console.Error.WriteLine("FAILED:\t" + destination);
return;
}
new_wb.SaveAs(destination + ".xls");
new_wb.Close();
workbook.Close();
Console.WriteLine("Finished:\t" + destination);
}
public void CreateExcelReport <T>(List <T> toReport, string path)
{
WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("main_sheet");
var props = typeof(T).GetProperties().Where((x) => FieldType.Split(' ').Contains(x.Name)).ToList();
int propsLength = props.Capacity;
string[] cells = { "A", "B", "C", "D", "E", "F", "G" };
int j = 0;
string cell;
foreach (var p in toReport)
{
for (int i = 1; i < propsLength; i++)
{
cell = cells[i - 1];
cell += j + 1;
xlsSheet[cell].Value = props[i - 1].GetValue(p).ToString();
}
j++;
}
xlsxWorkbook.SaveAs(path + ".xlsx");
}
public void ConvertToExcel(List <IntervalData> intervalDataList, string folderPath)
{
var intervalDataWorkBook = WorkBook.Create(ExcelFileFormat.XLSX);
var xlsSheet = intervalDataWorkBook.CreateWorkSheet("Sheet 1");
xlsSheet["A1"].Value = "Date";
xlsSheet["B1"].Value = "TimeSlot";
xlsSheet["C1"].Value = "SlotVal";
var counter = 2;
string timeSpanRepresentation;
string dateValue;
foreach (var intervalItem in intervalDataList)
{
timeSpanRepresentation = $"{intervalItem.TimeSlot.ToString(@"hh")} - {intervalItem.TimeSlot.ToString(@"mm")}";
dateValue = intervalItem.Date.ToString("dd MMM yyyy");
xlsSheet[$"A{counter}"].Value = dateValue;
xlsSheet[$"B{counter}"].StringValue = timeSpanRepresentation;
xlsSheet[$"C{counter}"].Value = intervalItem.SlotVal.ToString("0.00");
counter++;
}
Directory.CreateDirectory(folderPath);
var fileName = $@"NewExcelFile{DateTime.Now.ToString(@"hh\:mm\:ss").Replace(":", "-")}.xlsx";
intervalDataWorkBook.SaveAs(Path.Combine(folderPath, fileName));
}
static void CreateExcelFile()
{
var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
newXLFile.Metadata.Title = "IronXL New File";
var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
newWorkSheet["A1"].Value = "Hello World";
newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}
static void SaveXL()
{
var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
newXLFile.Metadata.Title = "IronXL New File";
var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
newWorkSheet["A1"].Value = "Hello World";
newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
newXLFile.ExportToHtml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldHTML.HTML");
//newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");
//newXLFile.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");
//newXLFile.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldCSV.csv",delimiter:"|");
//newXLFile.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
}
/**SaveDataToCSV - zapisuje dane do pliku Raport.xlsx */
public static void SaveDataToXLSX(List <string> outputClientList)
{
WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
xlsxWorkbook.Metadata.Author = "Kasper";
WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("Raport");
// pentla wpisujaca dane do xlsSheet
for (int i = 0; i < outputClientList.Count; i++)
{
// ustalenie adresu komórki
string field = "A" + (i + 1).ToString();
xlsSheet[field].Value = outputClientList[i];
}
xlsxWorkbook.SaveAs("Raport.xlsx");
}
public void Create() //Create new excel file
{ //Create new workbook
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS);
//Add a blank WorkSheet
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("Sheet1");
if (!File.Exists(@"..\..\App-data\Book1.xlsx"))
{
//Save the excel file
xlsWorkbook.SaveAs(@"..\..\App-data\Book1.xlsx");
}
else
{
Console.WriteLine("The File exists already");
}
}
private void Report(List <double[]> distance, string[] names)
{
string filePath = "..\\..\\..\\distance.xlsx";
if (File.Exists(filePath))
{
File.Delete(filePath);
}
WorkBook workbook = WorkBook.Create();
WorkSheet sheet = workbook.CreateWorkSheet("test.xl");
var range = sheet["A1:OO" + (distance.Count + 5)];
foreach (var cell in range)
{
int r = cell.RowIndex;
int c = cell.ColumnIndex;
if (r > distance.Count || c > distance.Count)
{
continue;
}
if (r == 0) // first row for names
{
if (c == 0)
{
continue;
}
cell.Value = names[c - 1];
}
else if (c == 0) // first column for names
{
if (r == 0)
{
continue;
}
cell.Value = names[r - 1];
}
else
{
cell.Value = decimal.Round((decimal)distance[r - 1][c - 1], 3, MidpointRounding.AwayFromZero);
}
}
workbook.SaveAs(filePath);
}
private void button3_Click(object sender, EventArgs e)
{
WorkBook archivoExcel = WorkBook.Create(ExcelFileFormat.XLSX);
archivoExcel.Metadata.Author = "Arcadia";
WorkSheet hojaExcel = archivoExcel.CreateWorkSheet("Productos");
int i = 1;
foreach (DataGridViewRow fila in dgvProductos.Rows)
{
Console.WriteLine("Fila: " + i);
hojaExcel["A" + i].Value = fila.Cells["idProducto"].ToString();
Console.WriteLine("IdProducto: " + fila.Cells["idProducto"].ToString());
hojaExcel["B" + i].Value = fila.Cells["nombreProducto"].ToString();
Console.WriteLine("nombre: " + fila.Cells["nombreProducto"].ToString());
hojaExcel["C" + i].Value = fila.Cells["cantidadProducto"].ToString();
Console.WriteLine("Cantidad: " + fila.Cells["cantidadProducto"].ToString());
i++;
}
archivoExcel.SaveAs("C:\\Users\\Arcadia\\Desktop\\Prueba1.xlsx");
}
private void CreateExcelFile(List <string> green, List <string> red)
{
try
{
WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
xlsxWorkbook.Metadata.Author = "HS";
//Add a blank WorkSheet
WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("sheet1");
xlsSheet["A1"].Value = "NOT WORKING";
xlsSheet["A1"].Style.Font.SetColor("#ff0000");
xlsSheet["A1"].Style.BottomBorder.SetColor("#ff0000");
xlsSheet["A1"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
//xlsSheet["A1"].Style.Font.SetColor("#ff0000");
for (int i = 2; i < red.Count + 2; i++)
{
xlsSheet["A" + i].Value = red[i - 2];
}
xlsSheet["A" + (red.Count + 2)].Value = "WORKING";
xlsSheet["A" + (red.Count + 2)].Style.Font.SetColor("#ff0000");
xlsSheet["A" + (red.Count + 2)].Style.BottomBorder.SetColor("#ff0000");
xlsSheet["A" + (red.Count + 2)].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double;
for (int i = 0; i < green.Count; i++)
{
xlsSheet["A" + (red.Count + 2 + i + 1)].Value = green[i];
}
string filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
xlsxWorkbook.SaveAs(filePath + "\\Feedback_Redirects.xlsx");
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Экспортв главной таблицы в excel.
/// </summary>
/// <param name="data">Коллекция с данными для экспорта.</param>
/// <returns>Имя файла .xlsx</returns>
public string Export(ObservableCollection <DataPoint> data)
{
var workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet sheet = workbook.CreateWorkSheet("Таблица");
sheet["A1"].Value = "Машина";
sheet["B1"].Value = "Партия";
sheet["C1"].Value = "Номенклатура";
sheet["D1"].Value = "Время обработки";
for (int i = 0; i < data.Count; i++)
{
sheet[$"A{i + 2}"].Value = data[i].Machine;
sheet[$"B{i + 2}"].Value = data[i].Party;
sheet[$"C{i + 2}"].Value = data[i].Type;
sheet[$"D{i + 2}"].Value = data[i].Time;
}
string file = $"Отчет_{DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss")}.xlsx";
workbook.SaveAs(file);
return(file);
}
public WorkBook CreateWorkBook(List <Row> rows)
{
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = new DefaultWorkSheetBuilder().BuildDefaultWorkSheet(workbook);
for (int i = 0, j = 2; i < rows.Count; i++, j++)
{
worksheet[$"A{j}"].Value = rows[i].Hie;
worksheet[$"B{j}"].Value = rows[i].IDX;
worksheet[$"C{j}"].Value = rows[i].Level;
worksheet[$"D{j}"].Value = rows[i].Parent;
worksheet[$"E{j}"].Value = rows[i].Node;
worksheet[$"F{j}"].Value = rows[i].Description;
worksheet[$"G{j}"].Value = rows[i].Method;
worksheet[$"H{j}"].Value = rows[i].Contains_Att;
worksheet[$"I{j}"].Value = rows[i].Contains_Val;
worksheet[$"J{j}"].Value = rows[i].Between_Att;
worksheet[$"K{j}"].Value = rows[i].Between_Lo;
worksheet[$"L{j}"].Value = rows[i].Between_Hi;
}
return(workbook);
}
internal static void ExportToExcel(IEnumerable <Client> clients)
{
try
{
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet workSheet = workBook.CreateWorkSheet("discounts");
workSheet["A1"].Value = "Id";
workSheet["B1"].Value = "Name";
workSheet["C1"].Value = "Discount";
int row = 2;
foreach (var client in clients)
{
workSheet[$"A{row}"].Value = client.Id;
workSheet[$"B{row}"].Value = client.Name;
workSheet[$"C{row}"].Value = $"{client.Discount}%";
row++;
}
try
{
workBook.SaveAs("ClientsDiscount.xlsx");
}
catch (System.IO.IOException)
{
throw;
}
}
catch
{
throw;
}
}
private void btnJsonToExcel_Click(object sender, RoutedEventArgs e)
{
Save();
var folder = txtFolder.Text;
var extension = txtExtension.Text;
var excelPath = txtExcel.Text;
if (Path.GetFullPath(excelPath) != excelPath)
{
excelPath = Path.Combine(folder, excelPath);
}
var jsonPaths = Directory.EnumerateFiles(folder, $"*{extension}");
// languages[language[key]] = value
var languages = new Dictionary <string, Dictionary <string, string> >();
var keySet = new HashSet <string>();
foreach (var jsonPath in jsonPaths)
{
var fileName = Path.GetFileName(jsonPath);
var languageKey = fileName.Substring(0, fileName.Length - extension.Length);
var json = File.ReadAllText(jsonPath);
var language = JsonConvert.DeserializeObject <Dictionary <string, string> >(json);
languages[languageKey] = language;
foreach (var item in language)
{
keySet.Add(item.Key);
}
}
var keys = keySet.ToArray();
var workbook = WorkBook.Create();
var workSheet = workbook.DefaultWorkSheet;
if (workSheet == null)
{
workSheet = workbook.CreateWorkSheet("Translations");
}
{ // Write Language Keys
char col = 'B';
foreach (var language in languages)
{
workSheet[$"{col}1"].StringValue = language.Key;
int row = 2;
foreach (var key in keys)
{
if (languages[language.Key].TryGetValue(key, out var val))
{
workSheet[$"{col}{row}"].StringValue = val;
}
else
{
workSheet[$"{col}{row}"].StringValue = null;
}
workSheet[$"A{row}"].StringValue = key;
row++;
}
col++;
}
}
workbook.SaveAs(excelPath);
}
private void CreateWorkBook()
{
Random r = new Random();
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("2020 Budget");
sheet["A1"].Value = "January";
sheet["B1"].Value = "February";
sheet["C1"].Value = "March";
sheet["D1"].Value = "April";
sheet["E1"].Value = "May";
sheet["F1"].Value = "June";
sheet["G1"].Value = "July";
sheet["H1"].Value = "August";
sheet["I1"].Value = "September";
sheet["J1"].Value = "October";
sheet["K1"].Value = "November";
sheet["L1"].Value = "December";
for (int i = 2; i <= 11; i++)
{
sheet["A" + i].Value = r.Next(1, 1000);
sheet["B" + i].Value = r.Next(1000, 2000);
sheet["C" + i].Value = r.Next(2000, 3000);
sheet["D" + i].Value = r.Next(3000, 4000);
sheet["E" + i].Value = r.Next(4000, 5000);
sheet["F" + i].Value = r.Next(5000, 6000);
sheet["G" + i].Value = r.Next(6000, 7000);
sheet["H" + i].Value = r.Next(7000, 8000);
sheet["I" + i].Value = r.Next(8000, 9000);
sheet["J" + i].Value = r.Next(9000, 10000);
sheet["K" + i].Value = r.Next(10000, 11000);
sheet["L" + i].Value = r.Next(11000, 12000);
}
////DB Stuff
////Create database objects to populate data from database
//string contring;
//string sql;
//DataSet ds = new DataSet("DataSetName");
//SqlConnection con;
//SqlDataAdapter da;
////Set Database Connection string
//contring = @"Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password";
////SQL Query to obtain data
//sql = "SELECT Field_Names FROM Table_Name";
////Open Connection & Fill DataSet
//con = new SqlConnection(contring);
//da = new SqlDataAdapter(sql, con);
//con.Open();
//da.Fill(ds);
////Loop through contents of dataset
//foreach (DataTable table in ds.Tables)
//{
// int Count = table.Rows.Count - 1;
// for (int j = 12; j <= 21; j++)
// {
// sheet["A" + j].Value = table.Rows[Count]["Field_Name_1"].ToString();
// sheet["B" + j].Value = table.Rows[Count]["Field_Name_2"].ToString();
// sheet["C" + j].Value = table.Rows[Count]["Field_Name_3"].ToString();
// sheet["D" + j].Value = table.Rows[Count]["Field_Name_4"].ToString();
// sheet["E" + j].Value = table.Rows[Count]["Field_Name_5"].ToString();
// sheet["F" + j].Value = table.Rows[Count]["Field_Name_6"].ToString();
// sheet["G" + j].Value = table.Rows[Count]["Field_Name_7"].ToString();
// sheet["H" + j].Value = table.Rows[Count]["Field_Name_8"].ToString();
// sheet["I" + j].Value = table.Rows[Count]["Field_Name_9"].ToString();
// sheet["J" + j].Value = table.Rows[Count]["Field_Name_10"].ToString();
// sheet["K" + j].Value = table.Rows[Count]["Field_Name_11"].ToString();
// sheet["L" + j].Value = table.Rows[Count]["Field_Name_12"].ToString();
// }
// Count++;
//}
sheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");
sheet["A1:L1"].Style.TopBorder.SetColor("#000000");
sheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.Type = IronXL.Styles.BorderType.Medium;
sheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
sheet["A11:L11"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium;
decimal sum = sheet["A2:A11"].Sum();
decimal avg = sheet["B2:B11"].Avg();
decimal max = sheet["C2:C11"].Max();
decimal min = sheet["D2:D11"].Min();
sheet["A12"].Value = sum;
sheet["B12"].Value = avg;
sheet["C12"].Value = max;
sheet["D12"].Value = min;
//sheet["A6"].Value = "=SUM(A2:A4)";
//if (sheet["A6"].IntValue == sheet["A2:A4"].IntValue)
//{
// Console.WriteLine("Basic test passed");
//}
//var range = sheet["A2:A8"];
////set style to multiple cells
////sheet["A5:A6"].Style.Font.Bold = true;
//range.Style.Rotation = 45;
sheet.SetPrintArea("A1:L12");
sheet.ProtectSheet("Password");
sheet.CreateFreezePane(0, 1);
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape;
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4;
workbook.SaveAs("Budget.xlsx");
}
public IronExcelDataOutput(string filePath)
{
wb = WorkBook.Create();
workSheet = wb.CreateWorkSheet("Решение задачи");
this.filePath = filePath;
}
private void CreateExcel()
{
try
{
string filename = "Data";
WorkBook wb = WorkBook.Create(ExcelFileFormat.XLSX);
wb.Metadata.Author = "GDG Filtering Software";
WorkSheet ws = wb.CreateWorkSheet("Main Sheet");
int counter = 2;
string[] headers = new[] { "Type", "TimeStamp", "Email Address", "Full Name", "Phone Number", "Gender",
"Number of Women", "Educational Background", "Thoughts", "Fully Parsed ? ", "Errors while Parsing" };
char[] alphas = new[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
for (int i = 0; i < 11; i++)
{
ws[$"{alphas[i]}{1}"].Value = headers[i];
}
foreach (StudentModel student in mouldedStudents)
{
Console.WriteLine("MOULDED STUDENT: " + student.Name + ", " + student.Type + " :: " + counter);
ws[$"A{counter}"].Value = student.Type;
ws[$"B{counter}"].Value = student.Timestamp;
ws[$"C{counter}"].Value = student.Email;
ws[$"D{counter}"].Value = student.Name;
if (student.Phone == 0)
{
ws[$"E{counter}"].Value = "";
}
else
{
ws[$"E{counter}"].Value = student.Phone;
}
ws[$"F{counter}"].Value = student.Gender.ToString();
ws[$"G{counter}"].Value = student.Women;
ws[$"H{counter}"].Value = student.EducationalBG;
ws[$"I{counter}"].Value = student.ShortThoughts;
ws[$"J{counter}"].Value = student.Params;
ws[$"K{counter}"].Value = student.Errors;
if (student.Errors.Length > 1)
{
ws[$"K{counter}"].Style.SetBackgroundColor("#e1ff00");
if (student.Errors.Contains("THIS APPLICANT HAS COMPETED BEFORE."))
{
ws.Rows[counter - 1].Style.SetBackgroundColor("#ff0000");
}
else if (student.Errors.Contains("Invalid Phone Number."))
{
ws[$"E{counter}"].Style.SetBackgroundColor("#e1ff00");
}
else if (student.Errors.Contains("Name Parsing Error."))
{
ws[$"D{counter}"].Style.SetBackgroundColor("#e1ff00");
}
else if (student.Errors.Contains("Email Parsing Error."))
{
ws[$"C{counter}"].Style.SetBackgroundColor("#e1ff00");
}
}
if (student.Type.Equals("Team Leader"))
{
Console.WriteLine($"{student.Name}, membs = {student.Team.Count}");
foreach (StudentModel member in student.Team)
{
++counter;
Console.WriteLine("MOULDED STUDENT: " + student.Name + ", " + student.Type + " :: " + counter);
ws[$"A{counter}"].Value = member.Type + " (" + member.TeamLeader + ")";
ws[$"C{counter}"].Value = member.Email;
ws[$"D{counter}"].Value = member.Name;
if (member.Phone == 0)
{
ws[$"E{counter}"].Value = "";
}
else
{
ws[$"E{counter}"].Value = member.Phone;
}
ws[$"J{counter}"].Value = member.Params;
ws[$"K{counter}"].Value = member.Errors;
if (member.Errors.Length > 1)
{
ws[$"K{counter}"].Style.SetBackgroundColor("#e1ff00");
if (member.Errors.Contains("THIS APPLICANT HAS COMPETED BEFORE."))
{
ws.Rows[counter - 1].Style.SetBackgroundColor("#ff0000");
}
else if (member.Errors.Contains("Invalid Phone Number."))
{
ws[$"E{counter}"].Style.SetBackgroundColor("#e1ff00");
}
else if (member.Errors.Contains("Name Parsing Error."))
{
ws[$"D{counter}"].Style.SetBackgroundColor("#e1ff00");
}
else if (member.Errors.Contains("Email Parsing Error."))
{
ws[$"C{counter}"].Style.SetBackgroundColor("#e1ff00");
}
}
}
//counter++;
}
++counter;
}
ws["A1:L1"].Style.SetBackgroundColor("#c4c4c4");
if (FileText.Equals(""))
{
if (File.Exists("HackthonData.xlsx"))
{
ShowMessage($"File Name is Empty and there already exits a Document with the default name 'HacktonData'\n" +
$"Delete the old existing Doc, or give the new one another FileName.", 'w');
}
else
{
Console.WriteLine($"{FileText}");
wb.SaveAs("HackthonData.xlsx");
ShowMessage($"File Name is Empty\n" +
$"The Analyzed Data has been saved as 'HackthonData.xlsx' in this Program Folder.", 'r');
}
}
else
{
if (Regex.IsMatch(FileText.Trim(), @"^[a-zA-z0-9 ]+$"))
{
if (File.Exists($"{FileText.Trim()}.xlsx"))
{
ShowMessage($"File Name is Empty and there already exits a Document with the default name 'HacktonData'\n" +
$"Delete the old existing Doc, or give the new one another FileName.", 'w');
}
else
{
Console.WriteLine($"{FileText}");
//Utils.BackToFront(DestinationText +
wb.SaveAs(FileText.Trim() + ".xlsx");
ShowMessage($"The Analyzed Data has been saved as '{FileText}.xlsx' in the Specified Destination.", 'r');
}
}
else
{
if (File.Exists("HackthonData.xlsx"))
{
ShowMessage($"File Name is Empty and there already exits a Document with the default name 'HacktonData'\n" +
$"Delete the old existing Doc, or give the new one another FileName.", 'w');
}
else
{
Console.WriteLine($"{FileText}");
//wb.SaveAs("Dsaf")
//Utils.BackToFront(DestinationText +
wb.SaveAs("HackthonData.xlsx");
ShowMessage($"File Name was entered in a Wrong Format\n" +
$"The Analyzed Data has been saved as 'Data.xlsx' in the Specified Destination.", 'r');
}
}
}
}
catch (Exception e)
{
ShowMessage($"Error Happened. {e.Message}.", 'w');
}
}
public void Submit()
{
var index = _view.GetIndexVehicles();
if (index == -1)
{
return;
}
var vehicle = ApplicationContext.Vehicles[index];
string path = ".";
if (_view.GetExportTypes().Contains("txt") ||
_view.GetExportTypes().Contains("word") ||
_view.GetExportTypes().Contains("excel"))
{
FolderBrowserDialog DirDialog = new FolderBrowserDialog();
DirDialog.Description = "Selecting a directory";
DirDialog.SelectedPath = @"C:\";
if (DirDialog.ShowDialog() == DialogResult.OK)
{
path = DirDialog.SelectedPath;
Console.WriteLine(path);
}
}
if (_view.GetExportTypes().Contains("txt"))
{
System.IO.File.WriteAllLines($@"{path}\Log_{vehicle.VehicleBrand}_{vehicle.Model}.txt",
vehicle.LogString.ToArray());
}
if (_view.GetExportTypes().Contains("word"))
{
System.IO.File.WriteAllLines($@"{path}\Log_{vehicle.VehicleBrand}_{vehicle.Model}.doc",
vehicle.LogString.ToArray());
}
if (_view.GetExportTypes().Contains("window"))
{
_view.OpenLog(vehicle.LogString);
}
if (_view.GetExportTypes().Contains("excel"))
{
var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
newXLFile.Metadata.Title = "Log_{vehicle.VehicleBrand}_{vehicle.Model}";
var newWorkSheet = newXLFile.CreateWorkSheet($"{vehicle.VehicleBrand}_{vehicle.Model}");
for (int i = 1; i <= vehicle.LogString.Count; i++)
{
//write the Dynamic value in one row
List <string> row = vehicle.LogString[i - 1].Split('#').ToList();
newWorkSheet["A" + i].Value = row[0];
//write the Dynamic value in another row
newWorkSheet["B" + i].Value = row[1];
}
newXLFile.SaveAs($@"{path}\Log_{vehicle.VehicleBrand}_{vehicle.Model}.xlsx");
}
}
public static void Crear(string filesalida, List <VisualStudioModel> lstvalues)
{
InitializeWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Dependencias");
var fila = sheet.CreateRow(0);
CrearCeldaHeaderExcel.Crear(fila, 1, "PROYECTO", ref sheet, hssfworkbook);
CrearCeldaHeaderExcel.Crear(fila, 2, "DEPENDECIA", ref sheet, hssfworkbook);
CrearCeldaHeaderExcel.Crear(fila, 3, "NUGET", ref sheet, hssfworkbook);
CrearCeldaHeaderExcel.Crear(fila, 4, "VERSIÓN", ref sheet, hssfworkbook);
CrearCeldaHeaderExcel.Crear(fila, 5, "FRAMEWORK", ref sheet, hssfworkbook);
int index = 1;
foreach (VisualStudioModel item in lstvalues)
{
var row = sheet.CreateRow(index++);
CrearCeldaExcel.Crear(row, 1, item.VisualStudio.AssemblyName, ref sheet);
foreach (string project in item.VisualStudio.Proyectos)
{
CrearCeldaExcel.Crear(row, 2, project, ref sheet);
var nuget = item.Packages.FirstOrDefault();
if (!string.IsNullOrWhiteSpace(nuget.id))
{
CrearCeldaExcel.Crear(row, 3, nuget.id, ref sheet);
CrearCeldaExcel.Crear(row, 4, nuget.version, ref sheet);
CrearCeldaExcel.Crear(row, 5, nuget.targetFramework, ref sheet);
item.Packages.Remove(nuget);
}
row = sheet.CreateRow(index++);
}
for (int i = 0; i < item.Packages.Count; i++)
{
CrearCeldaExcel.Crear(row, 3, item.Packages[i].id, ref sheet);
CrearCeldaExcel.Crear(row, 4, item.Packages[i].version, ref sheet);
CrearCeldaExcel.Crear(row, 5, item.Packages[i].targetFramework, ref sheet);
row = sheet.CreateRow(index++);
}
}
WriteToFile(filesalida);
return;
WorkBook xlsxWorkbook = WorkBook.Create(ExcelFileFormat.XLSX);
xlsxWorkbook.Metadata.Author = "David Hernandez";
//Add a blank WorkSheet
WorkSheet xlsSheet = xlsxWorkbook.CreateWorkSheet("Dependencias");
CrearFilaExcel.Crear(xlsSheet);
//int index = 1;
//int fila = 2;
foreach (VisualStudioModel item in lstvalues)
{
++index;
xlsSheet[$"A{index }"].Value = item.VisualStudio.AssemblyName;
//fila = index;
foreach (string project in item.VisualStudio.Proyectos)
{
xlsSheet[$"B{index}"].Value = project;
++index;
}
foreach (var pack in item.Packages)
{
xlsSheet[$"C{fila }"].Value = pack.id;
xlsSheet[$"D{fila }"].Value = pack.version + ".0"; // Se agrego el cero porque tiene un bug el nuget excel y convierte el valor a fecha
xlsSheet[$"E{fila }"].Value = pack.targetFramework;
//++fila;
//index = fila;
}
}
//Save the excel file
xlsxWorkbook.SaveAs(filesalida);
}
public void 航空里程兌換精進_欄位檢核(string browser)
{
string timesavepath = System.DateTime.Now.ToString("yyyyMMdd'-'HHmm");
string snapshotpath = System.AppDomain.CurrentDomain.BaseDirectory + "SnapshotFolder\\AirlineMilesRedemption";
string excel_savepath = snapshotpath + "\\TestReport.xlsx";
WorkBook xlsWorkbook;
WorkSheet xlsSheet;
if (File.Exists(excel_savepath) == true && WorkBook.Load(excel_savepath).GetWorkSheet(testcase_name) == null)
{ // 判斷當excel檔存在 但 沒有"xxx"工作表 >>> 讀取該excel檔, new create "xxx" 工作表
xlsWorkbook = WorkBook.Load(excel_savepath);
xlsSheet = xlsWorkbook.CreateWorkSheet(testcase_name);
}
else if (File.Exists(excel_savepath) == true && WorkBook.Load(excel_savepath).GetWorkSheet(testcase_name) != null)
{ // 判斷當excel檔存在 且 有"xxx"工作表 >>> 讀取該excel檔, 讀取"xxx" 工作表
xlsWorkbook = WorkBook.Load(excel_savepath);
xlsSheet = xlsWorkbook.GetWorkSheet(testcase_name);
}
else
{ // 判斷當excel檔不存在 >>> new create excel檔 & new create "xxx" 工作表
xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLSX); //定義 excel格式為 XLSX
xlsSheet = xlsWorkbook.CreateWorkSheet(testcase_name);
}
IWebElement go_to_redeem = driver.FindElement(By.XPath("//*[@id='layout_m_0_content_m_3_tab_0_HlkMeleageForm']"));
go_to_redeem.Click(); // 點"前往兌換"
System.Threading.Thread.Sleep(100);
WebDriverWait redeem_window = new WebDriverWait(driver, TimeSpan.FromSeconds(3));
redeem_window.Until(SeleniumExtras.WaitHelpers.ExpectedConditions.ElementExists(By.XPath("//*[@id='login']"))); //等待直到看到 會員登入 dialog
IWebElement creditcard_friend_login = driver.FindElement(By.XPath("//*[@id='layout_m_0_content_m_2_HlkCardLogin']/img"));
creditcard_friend_login.Click();// 點"信用卡友登入"
System.Threading.Thread.Sleep(100);
string csvpath = $@"{UserDataList.Upperfolderpath}\testdata\UserInfo.csv";
using (var reader = new StreamReader(csvpath)) //讀CSV檔
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
var records = csv.GetRecords <UserDataList>().ToList();
int k = 1;
foreach (var user in records)
{
if (k == 6)
{
IWebElement ID = driver.FindElement(By.XPath("//*[@id='CHID_T']"));
IWebElement CreditCard = driver.FindElement(By.XPath("//*[@id='cardNO_T']"));
IWebElement Expire_Mon = driver.FindElement(By.XPath("//*[@id='endM']"));
IWebElement Expire_Year = driver.FindElement(By.XPath("//*[@id='endY']"));
IWebElement CheckCode = driver.FindElement(By.XPath("//*[@id='cardCVV_T']"));
IWebElement Submit_button = driver.FindElement(By.XPath("//*[@id='confirm_btn']"));
ID.Clear();
ID.SendKeys(user.ID); // 輸入身分證字號
System.Threading.Thread.Sleep(100);
CreditCard.Clear();
CreditCard.SendKeys(user.CARDID_FULL); // 輸入卡號
System.Threading.Thread.Sleep(100);
Expire_Mon.Clear();
Expire_Mon.SendKeys(user.MON); // 輸入有效月份
System.Threading.Thread.Sleep(100);
Expire_Year.Clear();
Expire_Year.SendKeys(user.YEAR); // 輸入有效年分
System.Threading.Thread.Sleep(100);
CheckCode.Clear();
CheckCode.SendKeys(user.CHECKCODE); //輸入驗證碼
System.Threading.Thread.Sleep(100);
Submit_button.Click(); // 送出
System.Threading.Thread.Sleep(100);
break;
}
k++;
}
}
IWebElement redeem_again = driver.FindElement(By.XPath(""));
redeem_again.Click(); // 前往兌換
System.Threading.Thread.Sleep(100);
IWebElement submit_redeem_setting = driver.FindElement(By.XPath(""));
submit_redeem_setting.Click(); //點送出
Tools.PageSnapshot($@"{snapshotpath}\{testcase_name}_{browser}_{Version}_{timesavepath}.png", driver);
System.Threading.Thread.Sleep(500);
//檢查點數輸入欄位
IWebElement airline_input_point = driver.FindElement(By.XPath(""));
string[] array_asia_point = new string[] { "１２３", "５６", "Ｂ", "@", "G", "0", "6666", "1", "20", "50", "999", "中文字", "@#$*", "！＠", "1a4", "" };
//定義excel欄位
xlsSheet["A1"].Value = "檢查 " + testcase_name + " '點數 & 換算' 欄位";
xlsSheet["B1"].Value = "顯示訊息";
xlsSheet["C1"].Value = "結果";
xlsSheet["A1:C1"].Style.BottomBorder.SetColor("#ff6600"); // 底線"紅色"
xlsSheet["A1:C1"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double; //加雙底線
int i = 3;
foreach (var keyin_point in array_asia_point) // 跑迴圈以逐筆輸入欄位
{
string check_position = "A" + i;
string show_msg = "B" + i;
string test_result = "C" + i;
airline_input_point.Clear();
airline_input_point.SendKeys(keyin_point);
string total_point = driver.FindElement(By.Id("total")).GetAttribute("value");
string to_mile = driver.FindElement(By.Id("mile")).GetAttribute("value");
string display_keyin_point = airline_input_point.GetAttribute("value");
string unit_point = driver.FindElement(By.Id("")).GetAttribute("value");
submit_redeem_setting.Click();
string count_error = driver.FindElement(By.Id("counter-error")).Text;
xlsSheet[check_position].Value = "輸入 " + keyin_point;
xlsSheet[show_msg].Value = count_error;
xlsSheet[test_result].Value = " ==> 我要用 " + unit_point + " 點 x " + display_keyin_point + " = " + total_point + " 兌換" + testcase_name + " " + to_mile + " 哩";
System.Threading.Thread.Sleep(100);
i++;
}
if (File.Exists(excel_savepath) != true)
{
xlsWorkbook.SaveAs(excel_savepath);
}
else
{
xlsWorkbook.Save();
}
CloseBrowser();
}
public void CreateExcelReport <T>(List <T> list, string path, bool isReverted)
{
WorkBook xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLS);
//Add a blank WorkSheet
WorkSheet xlsSheet = xlsWorkbook.CreateWorkSheet("new_sheet");
//Add data and styles to the new worksheet
PropertyInfo[] properties = null;
int row = 1;
if (list != null)
{
properties = list[0].GetType().GetProperties();
}
if (properties.Length > 10)
{
MessageBox.Show("Не больше 10 свойств");
return;
}
int j = 0;
for (int i = 0; i < properties.Length; i++)
{
if (isReverted)
{
xlsSheet[columns[i] + "" + row].Value = properties[i].Name;
}
else
{
xlsSheet[columns[j] + "" + row].Value = properties[i].Name;
}
row++;
}
string cell = "";
if (isReverted)
{
foreach (var value in list)
{
j = 0;
for (int i = 0; i < properties.Length; i++, j++)
{
cell += columns[j];
cell += "" + row;
xlsSheet[cell].Value = properties[i].GetValue(value).ToString();
cell = "";
}
row++;
}
}
else
{
foreach (var value in list)
{
row = 1;
j++;
for (int i = 0; i < properties.Length; i++)
{
cell += columns[j];
cell += "" + row;
xlsSheet[cell].Value = properties[i].GetValue(value).ToString();
row++;
cell = "";
}
}
}
//Save the excel file
xlsWorkbook.SaveAs(path + "\\NewExcelFile.xls");
}
public void 欄位檢核測試(string browser)
{
StartTestCase(browser, "預售屋信託查詢精進_欄位檢核測試", "York");
INFO("");
string time = System.DateTime.Now.ToString("yyyy-MM-dd_HHmm");
string snapshotfolderpath = $@"{System.AppDomain.CurrentDomain.BaseDirectory}\SnapshotFolder\PreSaleHouseTrustInquiry";
string excel_path = $@"{snapshotfolderpath}\TestReport.xlsx";
Tools.CreateSnapshotFolder(snapshotfolderpath);
System.Threading.Thread.Sleep(100);
WorkBook xlsWorkbook;
WorkSheet xlsSheet;
if (File.Exists(excel_path) == true && WorkBook.Load(excel_path).GetWorkSheet(testcase_name) == null)
{ // 判斷當excel檔存在 但 沒有"預售屋查詢"工作表 >>> 讀取該excel檔, new create "預售屋查詢" 工作表
xlsWorkbook = WorkBook.Load(excel_path);
xlsSheet = xlsWorkbook.CreateWorkSheet(testcase_name);
}
else if (File.Exists(excel_path) == true && WorkBook.Load(excel_path).GetWorkSheet(testcase_name) != null)
{ // 判斷當excel檔存在 且 有"預售屋查詢"工作表 >>> 讀取該excel檔, 讀取"預售屋查詢" 工作表
xlsWorkbook = WorkBook.Load(excel_path);
xlsSheet = xlsWorkbook.GetWorkSheet(testcase_name);
}
else
{ // 判斷當excel檔不存在 >>> new create excel檔 & new create "預售屋查詢" 工作表
xlsWorkbook = WorkBook.Create(ExcelFileFormat.XLSX); //定義 excel格式為 XLSX
xlsSheet = xlsWorkbook.CreateWorkSheet(testcase_name);
}
IWebElement ProjectNameDropDownList = driver.FindElement(By.XPath("//*[@id='layout_0_rightcontent_1_PnlInput']/div[2]/table/tbody/tr[1]/td[2]/ul"));
ProjectNameDropDownList.Click(); // 點建案下拉選單
System.Threading.Thread.Sleep(100);
IWebElement SelectProject = driver.FindElement(By.XPath("//*[@id='layout_0_rightcontent_1_PnlInput']/div[2]/table/tbody/tr[1]/td[2]/ul/li/ul/li[6]/span"));
SelectProject.Click(); // 選建案
System.Threading.Thread.Sleep(100);
//Array存取預測試的字串
string[] array_payment_account = new string[] { "", "1234567890123456789", "12345", "１２３４５６", "中文字", "ＡＢＣＤＥＦ", "ａｂｃｄｅｆ", "abcdef", "ABCDEF", "@#$%^&*", "！＠＃＄％︿", "123abc456DEF", "987654321", "" };
//定義excel欄位
xlsSheet["A1"].Value = "檢查 繳款帳號欄位";
xlsSheet["A2"].Value = "建案名稱";
xlsSheet["B2"].Value = "手動輸入";
xlsSheet["C2"].Value = "欄位實際值";
xlsSheet["D2"].Value = "顯示訊息";
xlsSheet["E2"].Value = "預期結果(顯示訊息)";
xlsSheet["F2"].Value = "測試結果";
xlsSheet["A2:F2"].Style.BottomBorder.SetColor("#ff6600"); // 底線"紅色"
xlsSheet["A2:F2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Double; //加雙底線
xlsSheet["A3"].Value = ProjectNameDropDownList.Text; // 欄位=建案名稱
int i = 3;
foreach (string keyin in array_payment_account) // 跑迴圈以逐筆輸入欄位
{
string check_position = "B" + i;
string actual_value = "C" + i;
string show_msg = "D" + i;
string expect_result = "E" + i;
string test_result = "F" + i;
IWebElement PaymentAccount = driver.FindElement(By.XPath("//*[@id='layout_0_rightcontent_1_TxtVacno']"));
PaymentAccount.Clear(); // 清除繳款欄位
PaymentAccount.SendKeys(keyin); // 輸入值
IWebElement submit_button = driver.FindElement(By.XPath("//*[@id='layout_0_rightcontent_1_LbtnQuery']"));
string actualPaymentAccount = PaymentAccount.GetAttribute("value"); // 實際欄位讀取到的值
bool expect_check_result = Regex.IsMatch(actualPaymentAccount, @"^\d{6,16}$"); // 判斷欄位裡的字串是否為"6-16位全數字"
bool digital_check_result = Regex.IsMatch(actualPaymentAccount, @"^[0-9]*$"); // 判斷欄位裡的字串是否為"全數字"
if (expect_check_result == true) // 判斷當輸入字元符合預期(全數字, 6~16位數), 送出data後延遲等待5秒, 等網頁load完
{
submit_button.Click(); // 點 送出
System.Threading.Thread.Sleep(5000);
}
else
{
submit_button.Click(); // 點 送出
System.Threading.Thread.Sleep(100);
}
string payment_account_error = driver.FindElement(By.Id("layout_0_rightcontent_1_TxtVacno-error")).Text;
if (actualPaymentAccount == "") // 判斷當輸入欄位為"空值" >>> 預期顯示 "必須填寫" warning
{
xlsSheet[check_position].Value = keyin;
xlsSheet[actual_value].Value = actualPaymentAccount;
xlsSheet[show_msg].Value = payment_account_error;
xlsSheet[expect_result].Value = "顯示 '必須填寫'";
xlsSheet[test_result].Value = "Case_1";
}
else if (actualPaymentAccount.Length < 6 && digital_check_result == true) // 判斷當"輸入欄位數字小於六位數" >>> 預期顯示 "最少 6 個字" warning
{
xlsSheet[check_position].Value = keyin;
xlsSheet[actual_value].Value = actualPaymentAccount;
xlsSheet[show_msg].Value = payment_account_error;
xlsSheet[expect_result].Value = "顯示 '最少 6 個字'";
xlsSheet[test_result].Value = "Case_2";
}
else if (digital_check_result != true) // 判斷當輸入欄位"不全為數字" >>> 預期顯示 "只可輸入數字" warning
{
xlsSheet[check_position].Value = keyin;
xlsSheet[actual_value].Value = actualPaymentAccount;
xlsSheet[show_msg].Value = payment_account_error;
xlsSheet[expect_result].Value = "顯示 '只可輸入數字'";
xlsSheet[test_result].Value = "Case_3";
}
// 判斷當輸入 "全部都數字" 且 "字數介於 6~16 位數間 >>> 預期查詢結果顯示 "企業代碼不存在"
else if (expect_check_result == true)
{
string searchresult = driver.FindElement(By.Id("layout_0_rightcontent_1_LblMessage")).Text;
xlsSheet[check_position].Value = keyin;
xlsSheet[actual_value].Value = actualPaymentAccount;
xlsSheet[show_msg].Value = searchresult;
xlsSheet[expect_result].Value = "顯示 '企業代碼不存在'";
xlsSheet[test_result].Value = "Case_4";
}
//else if (keyin.Length > 16 && result == true) // 判斷當輸入位元 "大於16位數" 且 "全部都數字" >> 預期查詢結果顯示 "企業代碼不存在 (need to confirm)"
//{
// string searchresult = driver.FindElement(By.Id("layout_0_rightcontent_1_LblMessage")).Text; // 擷取"查詢結果" 欄位字串
// xlsSheet[check_position].Value = keyin;
// xlsSheet[actual_value].Value = actualPaymentAccount;
// xlsSheet[show_msg].Value = searchresult;
// xlsSheet[expect_result].Value = "顯示 '[TBD]企業代碼不存在'";
// xlsSheet[test_result].Value = "Need Manaul Check";
//}
else //非以上情況測試結果fail
{
string searchresult = driver.FindElement(By.Id("layout_0_rightcontent_1_LblMessage")).Text; // 擷取"查詢結果" 欄位字串
xlsSheet[check_position].Value = keyin;
xlsSheet[actual_value].Value = actualPaymentAccount;
xlsSheet[show_msg].Value = $"Message 1: {payment_account_error} / Message 2: {searchresult}";
xlsSheet[test_result].Value = "FAIL";
}
i++;
}
if (File.Exists(excel_path) != true)
{
xlsWorkbook.SaveAs(excel_path);
}
else
{
xlsWorkbook.Save();
}
driver.Quit();
}
public void readXLS(string FilePath)
{
try
{
WorkBook workbook = WorkBook.Load(FilePath);
WorkSheet sheet = workbook.WorkSheets.First();
for (int i = 0; i < 5; i++)
{
if (sheet[columns[i] + "1"].StringValue != columnNames[i])
{
label1.Text = "Неверный формат файла!";
return;
}
}
//prepare excel for bad data
WorkBook mistakesWorkbook = WorkBook.Create(ExcelFileFormat.XLS);
mistakesWorkbook.Metadata.Author = "IronXL";
WorkSheet xlsSheet = mistakesWorkbook.CreateWorkSheet("Sheet1");
for (int j = 0; j < 5; j++)
{
xlsSheet[columns[j] + "1"].Value = columnNames[j];
}
int rowCounter = 2;
//run throw rows
for (int i = 2; i < 1000000; i++)
{
if (sheet['A' + i.ToString()].StringValue == "")
{
break;
}
bool checkFormat = true;
//run throw columns
string name = sheet[columns[0] + i.ToString()].StringValue;
string type = sheet[columns[1] + i.ToString()].StringValue;
string weight = sheet[columns[2] + i.ToString()].StringValue;
string article = sheet[columns[3] + i.ToString()].StringValue;
string yearOfIssue = sheet[columns[4] + i.ToString()].StringValue;
//check data format
if (name.Length > 100)
{
checkFormat = false;
}
try {
Convert.ToDouble(weight);
}
catch {
checkFormat = false;
}
if (article.Length > 16)
{
checkFormat = false;
}
for (int j = 0; j < article.Length; j++)
{
if (article[j] == ' ')
{
checkFormat = false;
break;
}
}
try {
Convert.ToDateTime(yearOfIssue);
}
catch {
checkFormat = false;
}
if (!checkFormat)
{
xlsSheet["A" + rowCounter.ToString()].Value = name;
xlsSheet["B" + rowCounter.ToString()].Value = type;
xlsSheet["C" + rowCounter.ToString()].Value = weight;
xlsSheet["D" + rowCounter.ToString()].Value = article;
xlsSheet["E" + rowCounter.ToString()].Value = yearOfIssue;
rowCounter++;
}
else
{
AddInfoToComponentsTable(name, type, Convert.ToDouble(weight), article, Convert.ToDateTime(yearOfIssue));
}
}
label1.Text = "Кол-во строк в плохом формате: " + (rowCounter - 2).ToString();
mistakes = mistakesWorkbook;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
label1.Text = "Вероятно, выбранный вами файл используется другой программой!";
}
}
