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");
}
}
private void Form1_Load(object sender, EventArgs e)
{
workbook = WorkBook.Create(ExcelFileFormat.XLSX);
workbook.Metadata.Author = "User";
sheet = workbook.CreateWorkSheet("new_sheet");
UpdateTable();
}
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");
}
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 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 static void ExportToExcel(string Path, string Timestamp)
{
Console.WriteLine(Path);
Console.WriteLine(Timestamp);
//Load Work Book
WorkBook custom_workbook = WorkBook.Load(workbook_full_path);
string date_today = DateTime.Today.ToShortDateString();
string custom_worksheet = date_today.Replace("/", "_");
//Check If Today's Worksheet Exists
WorkSheet todays_sheet;
if (custom_workbook.GetWorkSheet(custom_worksheet) == null)
{
todays_sheet = custom_workbook.CreateWorkSheet(custom_worksheet);
todays_sheet["A1"].Value = "Path";
todays_sheet["B1"].Value = "Date Create";
custom_workbook.Save();
}
else
{
todays_sheet = custom_workbook.GetWorkSheet(custom_worksheet);
}
//Get Last Row info
int row_count = todays_sheet.Rows.Count();
int row_number = row_count + 1;
//Sheet Data
todays_sheet["A" + row_number].Value = Path;
todays_sheet["B" + row_number].Value = Timestamp;
custom_workbook.Save();
}
public static void WriteExcel(DataTable table, string targetFile)
{
WorkBook workBook = new WorkBook(ExcelFileFormat.XLSX);
var sheet = workBook.CreateWorkSheet("CompatilityIssues");
var columns = table.Columns;
for (int i = 1; i <= columns.Count; i++)
{
sheet.SetCellValue(1, i, columns[i - 1].ColumnName);
}
var ind = 2;
for (int i = 0; i < table.Rows.Count; i++)
{
var cols = table.Rows[i].ItemArray;
for (int j = 0; j < cols.Count(); j++)
{
sheet.SetCellValue(ind, j + 1, cols[j]);
}
ind++;
}
var stream = workBook.ToStream();
stream.WriteTo(File.OpenWrite(targetFile));
}
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!");
}
}
/// <summary>
/// Download a list of 250 countries from restcountries.eu and save the data to a spreadsheet
/// Data from: https://restcountries.eu/rest/v2/
/// </summary>
/// <returns></returns>
public async Task ProcessAsync()
{
//Load the data
var client = new Client(new Uri("https://restcountries.eu/rest/v2/"));
List <RestCountry> countries = await client.GetAsync <List <RestCountry> >();
//Create a new spreadsheet
var workbook = new WorkBook(ExcelFileFormat.XLSX);
var worksheet = workbook.CreateWorkSheet("Countries");
//Set the header cells
worksheet["A1"].Value = "Name";
worksheet["B1"].Value = "Population";
worksheet["C1"].Value = "Capital";
worksheet["D1"].Value = "Language1";
worksheet["E1"].Value = "Language2";
worksheet["F1"].Value = "Language3";
worksheet["G1"].Value = "Region";
worksheet["H1"].Value = "NumericCode";
//Iterate through countries
for (var i = 2; i < countries.Count; i++)
{
var country = countries[i];
//Set the basic values
worksheet[$"A{i}"].Value = country.name;
worksheet[$"B{i}"].Value = country.population;
worksheet[$"G{i}"].Value = country.region;
worksheet[$"H{i}"].Value = country.numericCode;
//Iterate through languages
for (var x = 0; x < 3; x++)
{
if (x > (country.languages.Count - 1))
{
break;
}
var language = country.languages[x];
//Get the letter for the column
var columnLetter = GetColumnLetter(4 + x);
//Set the language name
worksheet[$"{columnLetter}{i}"].Value = language.name;
}
}
//Save the document
workbook.SaveAs("Countries.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");
}
static void FillSheetFromDb()
{
testDBEntities dbContext = new testDBEntities();
WorkBook workbook = WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.CreateWorkSheet("FromDb");
List <Country> countryList = dbContext.Countries.ToList();
sheet.SetCellValue(0, 0, "Id");
sheet.SetCellValue(0, 1, "Country Name");
int row = 1;
foreach (var item in countryList)
{
sheet.SetCellValue(row, 0, item.id);
sheet.SetCellValue(row, 1, item.CountryName);
row++;
}
workbook.SaveAs("FilledFile.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;
}
}
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;
}
}
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 IronExcelDataOutput(string filePath)
{
wb = WorkBook.Create();
workSheet = wb.CreateWorkSheet("Решение задачи");
this.filePath = filePath;
}
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");
}
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 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 = "Вероятно, выбранный вами файл используется другой программой!";
}
}
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');
}
}
