public void FormatTable()
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
int lastRowID = xlSheet.UsedRange.Rows.Count;
int lastColumnID = xlSheet.UsedRange.Columns.Count;
Excel.Range tableRange = xlSheet.get_Range(GetCell(2, 1), GetCell(lastRowID, lastColumnID));
tableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range tableRange2 = xlSheet.get_Range(GetCell(2, 1), GetCell(lastRowID, 1));
tableRange2.Interior.Color = Color.LightYellow;
tableRange2.Font.Bold = true;
Excel.Range tableRange3 = xlSheet.get_Range(GetCell(2, lastColumnID), GetCell(lastRowID, lastColumnID));
tableRange3.Interior.Color = Color.LightGreen;
tableRange3.Cells.NumberFormat = "0.00";
}
private void FormatTable()
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightSalmon;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
int lastRowID = xlSheet.UsedRange.Rows.Count;
Excel.Range tableRange = xlSheet.get_Range(GetCell(1, 1), GetCell(lastRowID, headers.Length));
tableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range firstRange = xlSheet.get_Range(GetCell(2, 1), GetCell(2, headers.Length));
firstRange.Interior.Color = Color.LightYellow;
Excel.Range secondRange = xlSheet.get_Range(GetCell(3, 1), GetCell(3, headers.Length));
secondRange.Interior.Color = Color.LightGray;
Excel.Range thirdRange = xlSheet.get_Range(GetCell(4, 1), GetCell(4, headers.Length));
thirdRange.Interior.Color = Color.FromArgb(171, 128, 97);
Excel.Range noPodiumColumn = xlSheet.get_Range(GetCell(5, 1), GetCell(lastRowID, 1));
noPodiumColumn.Interior.Color = Color.LightBlue;
Excel.Range firstColumn = xlSheet.get_Range(GetCell(2, 1), GetCell(lastRowID, 1));
firstColumn.Font.Bold = true;
}
private void CreateTable()
{
string[] headers = new string[] {
"Kód",
"Eladó",
"Oldal",
"Kerület",
"Lift",
"Szobák száma",
"Alapterület (m2)",
"Ár (mFt)",
"Négyzetméter ár (Ft/m2)"
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[Flats.Count, headers.Length];
int counter = 0;
foreach (Flat f in Flats)
{
values[counter, 0] = f.Code;
values[counter, 1] = f.Vendor;
values[counter, 2] = f.Side;
values[counter, 3] = f.District;
if (f.Elevator == true)
{
values[counter, 4] = "Van";
}
else
{
values[counter, 4] = "Nincs";
}
values[counter, 5] = f.NumberOfRooms;
values[counter, 6] = f.FloorArea;
values[counter, 7] = f.Price;
values[counter, 8] = f.FloorArea * f.Price;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
int lastRowID = xlSheet.UsedRange.Rows.Count;
}
private void CreateTable()
{
//Fejléc
string[] headers = new string[] {
"Rendelésazonosító",
"Ár",
"Rögzítés ideje",
"Elfogadva",
"Felvétel ideje",
"Leadás várható ideje",
"Késés (perc)",
"Szállító futár azonosítója",
"Étterem azonosítója"
};
for (int i = 1; i - 1 < headers.Length; i++)
{
xlSheet.Cells[1, i] = headers[i - 1];
}
//Adatok betöltése
object[,] values = new object[Rendelések.Count, headers.Length];
int counter = 0;
foreach (Rendelés r in Rendelések)
{
values[counter, 0] = r.RendelésID;
values[counter, 1] = r.Ár;
values[counter, 2] = r.Rögzítés;
values[counter, 3] = r.Elfogadva;
values[counter, 4] = r.Felvéve;
values[counter, 5] = r.Leadva;
values[counter, 6] = r.Késés;
values[counter, 7] = r.FutárFK;
values[counter, 8] = r.ÉtteremFK;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
//Formázás
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range tableRange = xlSheet.get_Range(GetCell(1, 1), GetCell(Rendelések.Count + 1, headers.Length));
tableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range elsooszlopRange = xlSheet.get_Range(GetCell(2, 1), GetCell(Rendelések.Count + 1, 1));
elsooszlopRange.Font.Bold = true;
elsooszlopRange.Interior.Color = Color.LightYellow;
}
private void CreateTable()
{
string[] headers = new string[] {
"Year",
"Bread Price (USD)",
"Gold Price (USD)",
"Exchange Rate",
"Gold Price in Selected Currency"
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[foreCast.Count, headers.Length];
int counter = 0;
foreach (ForeCast f in foreCast)
{
values[counter, 0] = f.year;
values[counter, 1] = f.gold;
values[counter, 2] = f.bread;
var currentrate = (from x in exchangeRates
where x.Country == (CountyEnum)countrycombo.SelectedIndex + 1 select x.countrycode).FirstOrDefault();
values[counter, 3] = -1364 + 0.67 * f.year + 6.33 * currentrate;
values[counter, 4] = "=(" + GetCell(counter + 2, 2) + "*" + purchaseAmount.Value + "*" + GetCell(counter + 2, 4) + ")";
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range TableRange = xlSheet.get_Range(GetCell(2, 1), GetCell(counter + 1, headers.Length));
TableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range FirstColRange = xlSheet.get_Range(GetCell(2, 1), GetCell(counter + 1, 1));
FirstColRange.Interior.Color = Color.LightYellow;
FirstColRange.Font.Bold = true;
Excel.Range LastColRange = xlSheet.get_Range(GetCell(2, headers.Length), GetCell(counter + 1, headers.Length));
LastColRange.Interior.Color = Color.LightGreen;
LastColRange.NumberFormat = "0.00";
}
private void Formattable()
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
int lastRowID = xlSheet.UsedRange.Rows.Count;
Excel.Range tableRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, lastRowID));
}
private void button3_Click(object sender, EventArgs e)
{
//xlApp = new Excel.Application();
//xlWBook = xlApp.Workbooks.Open(FileName);
//xlWSheet = xlWBook.ActiveSheet;
try
{
Excel.Range rng = xlWSheet.Cells.get_Range("" + cmb1.Text + (int)No1.Value + "", "" + cmb2.Text + (int)No2.Value + "");
rng.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected override void InsertPersonalizzazioni(object siglaEntita)
{
//da classe base il filtro è corretto
DataView informazioni = Workbook.Repository[DataBase.TAB.ENTITA_INFORMAZIONE].DefaultView;
_ws.Columns[3].Font.Size = 9;
int col = _definedNames.GetFirstCol();
int row = _definedNames.GetRowByName(siglaEntita, "T");
//metto cella con scritta totale
//Excel.Range title = _ws.Range[Range.GetRange(row, col + 25)];
//title.Value = "TOTALE";
Excel.Range rngPersonalizzazioni = _ws.Range[Range.GetRange(row + 2, col + 25, _intervalloGiorniMax)];
rngPersonalizzazioni.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
//title.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
//Style.RangeStyle(title, bold: true, backColor: 8, align: Excel.XlHAlign.xlHAlignCenter);
rngPersonalizzazioni.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium);
rngPersonalizzazioni.Columns[1].ColumnWidth = Struct.cell.width.jolly1;
Style.RangeStyle(rngPersonalizzazioni, fontName: "Verdana", fontSize: 9, bold: true);
int i = 1;
//filtro giusto da classe base
DataView entitaInformazione = Workbook.Repository[DataBase.TAB.ENTITA_INFORMAZIONE].DefaultView;
foreach (DataRowView info in entitaInformazione)
{
CicloGiorni(_dataInizio, _dataInizio.AddDays(Struct.intervalloGiorni - 1), (oreGiorno, suffissoData, giorno) =>
{
//row = _definedNames.GetRowByName(siglaEntita, "T");
//int gasDayStart = TimeZone.CurrentTimeZone.IsDaylightSavingTime(giorno) ? 7 : 6;
//int remainingHours = 24 - Date.GetOreGiorno(giorno) + gasDayStart;
//Range rng1 = new Range(row + i + 1, _definedNames.GetColData1H1() + gasDayStart - 1, 1, 25 - gasDayStart + 1);
//Range rng2 = new Range(row + i + 2, _definedNames.GetColData1H1(), 1, remainingHours - 1);
Range[] giornoGas = GetRangeGiornoGas(giorno, info, _definedNames);
rngPersonalizzazioni.Cells[i, 1].Formula = "=SUM(" + giornoGas[0].ToString() + ") + SUM(" + giornoGas[1].ToString() + ")";
i++;
});
}
}
private void FormatTable()
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.Interior.Color = Color.Navy;
headerRange.Font.Color = Color.White;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range fullRange = xlSheet.get_Range(GetCell(1, 1), GetCell(xlSheet.UsedRange.Rows.Count, xlSheet.UsedRange.Columns.Count));
fullRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range contentRange = xlSheet.get_Range(GetCell(2, 1), GetCell(xlSheet.UsedRange.Rows.Count - 1, xlSheet.UsedRange.Columns.Count));
contentRange.Interior.Color = Color.LightGray;
}
public void Export(List <Student> selected)
{
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[selected.Count, headers.Length];
int counter = 0;
foreach (var x in selected)
{
values[counter, 0] = x.Name;
values[counter, 1] = x.BirthPlace;
values[counter, 2] = x.BirthDate.Date.ToString();
values[counter, 3] = x.MotherName;
values[counter, 4] = x.Country;
values[counter, 5] = x.PostalCode;
values[counter, 6] = x.City;
values[counter, 7] = x.Address;
values[counter, 8] = x.Phone;
values[counter, 9] = x.Email;
values[counter, 10] = x.IDNum;
values[counter, 11] = x.Category.ToString();
values[counter, 12] = x.Azonosito;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LimeGreen;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range namerange = xlSheet.get_Range(GetCell(2, 1), GetCell(selected.Count + 1, 1));
namerange.Font.Bold = true;
Excel.Range datarange = xlSheet.get_Range(GetCell(2, 1), GetCell(selected.Count + 1, headers.Length));
datarange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
}
/// <summary>
/// Inizializza la barra del titolo.
/// </summary>
protected void InitBarraNavigazione()
{
DataView categoriaEntita = Workbook.Repository[DataBase.TAB.CATEGORIA_ENTITA].DefaultView;
Excel.Range gotoBar = _ws.Range[_ws.Cells[2, 2], _ws.Cells[_struttura.rigaGoto + 1, categoriaEntita.Count + 3]];
gotoBar.Style = "Top menu GOTO";
gotoBar.BorderAround2(Weight: Excel.XlBorderWeight.xlMedium, Color: 1);
int i = 3;
foreach (DataRowView entita in categoriaEntita)
{
Excel.Range rng = _ws.Cells[_struttura.rigaGoto, i++];
rng.Value = entita["DesEntitaBreve"];
rng.Style = "Barra navigazione con nomi";
}
}
private void CreateTable()
{
string[] headers = new string[]
{
"Kérdés Id",
"Kérdés",
"Válasz 1",
"Válasz 2",
"Válasz 3",
"Válasz 4",
"Helyes válasz",
};
for (int i = 0; i < 7; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[Kerdes.Count, headers.Length];
int counter = 0;
foreach (Kerdes k in Kerdes)
{
values[counter, 0] = k.Kerdes_Id;
values[counter, 1] = k.Kerdes1;
values[counter, 2] = k.Valasz_1;
values[counter, 3] = k.Valasz_2;
values[counter, 4] = k.Valasz_3;
values[counter, 5] = k.Valasz_4;
values[counter, 6] = k.Helyes_valasz;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LawnGreen;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
}
private void Formazas(Excel.Worksheet xlWorkSheet)
{
Excel.Range headerRange = xlWorkSheet.get_Range("A1", "E1");
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.Fuchsia;
Excel.Range lastsor = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Excel.Range totalRange = xlWorkSheet.get_Range("A2", "E" + lastsor.Row);
totalRange.BorderAround2(Excel.XlLineStyle.xlDash, Excel.XlBorderWeight.xlMedium);
totalRange.Cells.Borders.LineStyle = Excel.XlLineStyle.xlDashDot;
totalRange.Interior.Color = Color.Yellow;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
}
private void CreateTable()
{
string[] headers = new string[] {
"Azonosító",
"Beteg neve",
"Neme",
"Életkora",
"Fertőzött-e",
"Kórterem",
"Életben van",
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, 1 + i] = headers[i];
}
object[,] values = new object[Patients.Count, headers.Length];
int counter = 0;
foreach (Patient p in Patients)
{
values[counter, 0] = p.Code;
values[counter, 1] = p.Name;
values[counter, 2] = p.Gender;
values[counter, 3] = p.Age;
values[counter, 4] = p.Covid;
values[counter, 5] = p.Ward;
values[counter, 6] = p.IsAlive;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 30;
headerRange.Interior.Color = Color.Fuchsia;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
}
private void CreateTableDiák()
{
string[] headers = new string[] {
"Név",
"Napi ár",
"Összesen",
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31"
};
xlSheet.Cells[1, 1] = (string)comboBox4.SelectedItem;
xlSheet.Cells[2, 1] = (string)comboBox5.SelectedItem;
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[3, 1 + i] = headers[i];
}
object[,] values = new object[szűrtdiák.Count, headers.Length - 31];
int counter = 0;
foreach (Diák diak in szűrtdiák)
{
values[counter, 0] = diak.Név;
values[counter, 1] = diak.Ár;
values[counter, 2] = "";
counter++;
}
xlSheet.get_Range(
GetCell(4, 1),
GetCell(3 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(3, 1), GetCell(3, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 30;
headerRange.Interior.Color = Color.Gray;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
Excel.Range calculated = xlSheet.get_Range(GetCell(4, 3),
GetCell(3 + values.GetLength(0), 3));
calculated.Formula = "=B4 * counta(D4:AH4)";
}
private void CreateTable()
{
string[] headers = new string[] {
"Eszköz neve",
"Model",
"Kategória",
"Beszerzési ár",
"Beszerzési idő",
"Gyári szám",
};
for (int i = 0; i < headers.Length; i++)
{
excelWorkSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[adatForras.Count, headers.Length];
int counter = 0;
foreach (var e in adatForras)
{
values[counter, 0] = e.assetname;
values[counter, 1] = e.model;
values[counter, 2] = e.category;
values[counter, 3] = e.price;
values[counter, 4] = e.purchasedate;
values[counter, 5] = e.serialnumber;
counter++;
}
excelWorkSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
int lastRowID = excelWorkSheet.UsedRange.Rows.Count;
int lastColID = excelWorkSheet.UsedRange.Columns.Count;
Excel.Range headerRange = excelWorkSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
Excel.Range tableRange = excelWorkSheet.get_Range(GetCell(1, 1), GetCell(1 + values.GetLength(0), values.GetLength(1)));
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.Font.Bold = true;
headerRange.Font.Italic = true;
tableRange.BorderAround2(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium);
}
private void CreateTable()
{
string[] headers = new string[]
{
"Eredmény id",
"Játékos neve",
"Összpontszám",
"Dátum",
};
for (int i = 0; i < 4; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
object[,] values = new object[Eredmeny.Count, headers.Length];
int counter = 0;
foreach (Eredmeny e in Eredmeny)
{
values[counter, 0] = e.Eredmeny_Id;
values[counter, 1] = e.Jatekos_nev;
values[counter, 2] = e.Eredmeny1;
values[counter, 3] = e.Datum;
//Convert.ToDateTime(values[counter, 3]) = e.Datum;
// Sajnos nem tudom dátummá formálni, az excel számként kezeli a dátum mezőt :(
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LawnGreen;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
}
private void FormatTable()
{
Excel.Range header = x1Sheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
header.Font.Bold = true;
header.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
header.EntireColumn.AutoFit();
header.Interior.Color = Color.LightYellow;
header.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range datumOszlop = x1Sheet.get_Range(GetCell(2, 1), GetCell(LastRow, 1));
datumOszlop.Font.Italic = true;
datumOszlop.NumberFormat = "mm/dd/yyyy hh:mm:ss";
datumOszlop.Interior.Color = Color.LightGray;
datumOszlop.EntireColumn.AutoFit();
datumOszlop.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
datumOszlop.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
datumOszlop.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
Excel.Range szamtartomany = x1Sheet.get_Range(GetCell(2, 4), GetCell(LastRow, 5));
szamtartomany.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
szamtartomany.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
szamtartomany.BorderAround2(Excel.XlLineStyle.xlDash, Excel.XlBorderWeight.xlThin);
szamtartomany.Interior.Color = Color.FloralWhite;
Excel.Range adattart = x1Sheet.get_Range(GetCell(2, 2), GetCell(LastRow, 3));
adattart.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
adattart.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
adattart.BorderAround2(Excel.XlLineStyle.xlDashDot, Excel.XlBorderWeight.xlThin);
adattart.Interior.Color = Color.AliceBlue;
Excel.Range ertektartomany = x1Sheet.get_Range(GetCell(2, 6), GetCell(LastRow, 6));
ertektartomany.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
ertektartomany.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
ertektartomany.Font.Bold = true;
ertektartomany.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin);
ertektartomany.Interior.Color = Color.LightGreen;
}
private void FormatTable(string[] headers)
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
int lastRowID = xlSheet.UsedRange.Rows.Count;
Excel.Range tableRange = xlSheet.get_Range(GetCell(1, 1), GetCell(headers.Length, lastRowID));
tableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range firstColumnRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, Flats.Count));
firstColumnRange.Font.Bold = true;
firstColumnRange.Interior.Color = Color.LightYellow;
Excel.Range lastColumnRange = xlSheet.get_Range(GetCell(2, headers.Length), GetCell(Flats.Count + 1, headers.Length));
lastColumnRange.Interior.Color = Color.LightGreen;
lastColumnRange.NumberFormat = "#,##0.00";
}
public void CreateTable()
{
string[] headers = new string[]
{
"Terméknév",
};
object[,] values = new object[NotAvailableProducts.Count, headers.Length];
int counter = 0;
foreach (var s in NotAvailableProducts)
{
values[counter, 0] = s.Név;
counter++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
xlSheet.Cells[1, 1] = headers[0];
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 30;
headerRange.Font.Color = Color.Black;
headerRange.Interior.Color = Color.DarkOrange;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range dataRange = xlSheet.get_Range(GetCell(2, 1), GetCell(1 + values.GetLength(0), values.GetLength(1)));
dataRange.Font.Color = Color.Blue;
dataRange.Font.Italic = true;
dataRange.EntireColumn.AutoFit();
dataRange.Interior.Color = Color.Orange;
}
private void FormatTable(int header)
{
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, header));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightBlue;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
headerRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
int lastRowID = xlSheet.UsedRange.Rows.Count;
Excel.Range tableRange = xlSheet.get_Range(GetCell(1, 1), GetCell(lastRowID, header));
tableRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
tableRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
Excel.Range firstColumn = xlSheet.get_Range(GetCell(1, 1), GetCell(lastRowID, 1));
firstColumn.Font.Bold = true;
firstColumn.Interior.Color = Color.Orange;
Excel.Range lastColumn = xlSheet.get_Range(GetCell(1, header), GetCell(lastRowID, header));
lastColumn.Interior.Color = Color.Red;
lastColumn.NumberFormat = "#,##0.00";
}
private void ExportToExcelToolStripMenuItem_Click(object sender, EventArgs e)
{
//Set region = US
System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Excel._Workbook wb = app.Workbooks.Add(Type.Missing);
Excel._Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];;
try
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Title = "Export to Excel";
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx;*.xls";
//saveDialog.FilterIndex = 2;
saveDialog.FileName = "Book" + number.ToString();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
ws.Name = "Ket Qua Noi Suy";
Excel.Range r = ws.Range["A1:L1"];
r.Merge();
r.Value = "Bảng tính nội suy";
r.BorderAround2();
r.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//r.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
// r.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
int i = 0, j = 0;
while (i < dgvKQ.ColumnCount)
{
//tieu de cot
ws.Cells[2, i + 1] = dgvKQ.Columns[i].HeaderText;
for (j = 0; j < dgvKQ.RowCount; j++)
{
ws.Cells[j + 3, i + 1] = dgvKQ[i, j].Value;
ws.Range[ws.Cells[j + 2, i + 1], ws.Cells[j + 2, i + 1]].BorderAround2();
}
i++;
}
wb.SaveAs(saveDialog.FileName);
app.Quit();
MessageBox.Show("Export Successful");
}
//Open Folder
Process folderProcess = new Process();
folderProcess.StartInfo.UseShellExecute = true;
folderProcess.StartInfo.FileName = Path.GetDirectoryName(saveDialog.FileName);
// folderProcess.StartInfo.Arguments = "/select";
folderProcess.Start();
// Process.Start("explorer", "/select," + saveDialog.FileName);
saveDialog = null;
folderProcess = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\n" + "Không thể mở được Folder do không thấy đường dẫn");
}
app = null;
wb = null;
ws = null;
}
private void AddWorksheet_WorkDiary(int daysInMonth)
{
for (int day = 1; day <= daysInMonth; day++)
{
_isSunday = false;
_excelWS = _excelWB.Worksheets.Add();
#region 標籤設定
_excelWS.Name = day.ToString();
string strWeek = _monthlyCalendar.GetDayOfWeek(day);
if (strWeek == "星期六")
{
_excelWS.Tab.Color = Color.OrangeRed;
}
if (strWeek == "星期天")
{
_isSunday = true;
_excelWS.Tab.Color = Color.Red;
}
#endregion
#region 設定標頭格線
Excel.Range range_Title = _excelWS.Range["A1", "K2"];
range_Title.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
#endregion
#region 設定第一行標頭
Excel.Range range_TitleRow1 = range_Title.Range["A1", "K1"];
range_TitleRow1.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range_TitleRow1.Font.Color = Color.White;
range_TitleRow1.Interior.Color = Color.Gray;
range_TitleRow1.RowHeight = 30;
range_TitleRow1.Range["B1", "C1"].Merge();
range_TitleRow1.Range["B1", "C1"].Item[1] = _department.Name + "-工作日報表(彙整)";
range_TitleRow1.Range["G1", "I1"].Merge();
range_TitleRow1.Range["G1", "I1"].Item[1] = _monthlyCalendar.GetDate(day);
range_TitleRow1.Range["J1", "K1"].Merge();
range_TitleRow1.Range["J1", "K1"].Item[1] = strWeek;
#endregion
#region 設定第二行標頭
Excel.Range range_TitleRow2 = range_Title.Range["A2", "K2"];
range_TitleRow2.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range_TitleRow2.Font.Name = "Microsoft JhengHei UI";
range_TitleRow2.Font.Size = 10;
range_TitleRow2.Font.Bold = true;
range_TitleRow2.WrapText = true;
range_TitleRow2.EntireColumn.AutoFit();
range_TitleRow2.Font.Color = Color.Black;
range_TitleRow2.Interior.Color = Color.LightGray;
range_TitleRow2.Range["A1"].Item[1] = "職員";
range_TitleRow2.Range["B1"].Item[1] = "專案、工地名稱";
range_TitleRow2.Range["C1", "E1"].Merge();
range_TitleRow2.Range["C1", "E1"].Item[1] = "今日工作內容詳述";
range_TitleRow2.Range["F1"].Item[1] = "出勤時數";
range_TitleRow2.Range["G1", "H1"].Merge();
range_TitleRow2.Range["G1", "H1"].Item[1] = "加班時數";
range_TitleRow2.Range["I1"].Item[1] = "報價收費";
range_TitleRow2.Range["J1", "K1"].Merge();
range_TitleRow2.Range["J1", "K1"].Item[1] = "備註";
#endregion
#region 設定欄位寬度
_excelWS.Range["A1"].ColumnWidth = 12;
_excelWS.Range["B1"].ColumnWidth = 20;
_excelWS.Range["C1"].ColumnWidth = 15;
_excelWS.Range["D1"].ColumnWidth = 15;
_excelWS.Range["E1"].ColumnWidth = 15;
_excelWS.Range["F1"].ColumnWidth = 8;
_excelWS.Range["G1"].ColumnWidth = 4;
_excelWS.Range["H1"].ColumnWidth = 4;
_excelWS.Range["I1"].ColumnWidth = 12;
_excelWS.Range["J1"].ColumnWidth = 10;
_excelWS.Range["K1"].ColumnWidth = 10;
#endregion
for (int i = 0; i < _department.Employees.Count; i++)
{
int startRow = (6 * i) + 3;
int endRow = (6 * i) + 7;
Excel.Range range_WorkRecord = _excelWS.Range["A" + startRow.ToString(), "K" + endRow.ToString()];
range_WorkRecord.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
range_WorkRecord.Font.Size = 10;
range_WorkRecord.RowHeight = 30;
Excel.Range range_EmployeeName = _excelWS.Range["A" + (startRow).ToString(), "A" + (startRow + 2).ToString()];
range_EmployeeName.Interior.Color = Color.FromArgb(0, 196, 189, 151);
range_EmployeeName.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range_EmployeeName.Font.Size = 12;
range_EmployeeName.Font.Bold = true;
range_EmployeeName.Merge();
range_EmployeeName.Item[1] = _department.Employees[i].Name;
Excel.Range range_TotalTime = _excelWS.Range["A" + (startRow + 3).ToString(), "A" + (startRow + 4).ToString()];
range_TotalTime.Interior.Color = Color.FromArgb(0, 217, 217, 217);
range_TotalTime.Font.Size = 12;
range_TotalTime.Font.Bold = true;
range_TotalTime.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range_TotalTime.Item[2].Font.Color = Color.Red;
range_TotalTime.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
range_TotalTime.Item[1] = "工時合計";
range_TotalTime.Item[2] = "=SUM(F" + startRow + " : G" + endRow + ")";
for (int j = 0; j < 5; j++)
{
Excel.Range range_WorkDescription = _excelWS.Range["C" + (startRow + j).ToString(), "E" + (startRow + j).ToString()];
range_WorkDescription.Merge();
Excel.Range range_OverHour = _excelWS.Range["G" + (startRow + j).ToString(), "H" + (startRow + j).ToString()];
range_OverHour.Merge();
Excel.Range range_Remark = _excelWS.Range["J" + (startRow + j).ToString(), "K" + (startRow + j).ToString()];
range_Remark.Merge();
}
if (_isSunday)
{
Excel.Range range_WorkDescription = _excelWS.Range["C" + (startRow).ToString(), "E" + (startRow).ToString()];
range_WorkDescription.Font.Color = Color.Red;
range_WorkDescription.Item[1] = "例假日";
}
range_WorkRecord.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range range_Group = _excelWS.Range["A" + (startRow + 1), "A" + (endRow)];
range_Group.Rows.Group();
Excel.Range range_Space = _excelWS.Range["A" + (endRow + 1).ToString(), "K" + (endRow + 1).ToString()];
range_Space.Merge();
}
}
}
/// <summary>
/// Uses the telemetry data to create an report in excel.
/// If the file already exists, it MUST be closed for this to work.
/// </summary>
static internal void Create(List <ScenarioRun> telemetryData, MainWindow.UserInput userInput)
{
Excel.Application excelApp = null;
try
{
excelApp = new Excel.Application();
Excel._Worksheet workSheet;
// Checks the provided name to see if it has a valid extension, and adds one if not.
string extension = Path.GetExtension(userInput.ResultFileName);
string excelExtension = ".xlsx";
string outputFilename;
if (Path.GetExtension(userInput.ResultFileName) != excelExtension)
{
outputFilename = Path.ChangeExtension(userInput.ResultFileName, excelExtension);
}
else
{
outputFilename = userInput.ResultFileName;
}
// Checks if that excel file already exists. Use the existing report if it does, otherwise create a new one.
int startColumnPosition = 1;
if (File.Exists(outputFilename))
{
// Makes sure the excel file isn't already open
while (IsFileLocked(new FileInfo(outputFilename)))
{
MessageBox.Show("Excel file is already locked and cannot be edited! Make sure that file is closed.");
}
// Load the existing excel sheet
workSheet = excelApp.Workbooks.Open(outputFilename).Sheets[1];
// Determine where the beginning of unused space is by checking the cells containing the scenario names.
for (int columnIndex = 1; columnIndex <= workSheet.Columns.Count; columnIndex += 5)
{
if (workSheet.Cells[2, columnIndex].Value == null)
{
startColumnPosition = columnIndex;
break;
}
}
}
else
{
excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}
// Set up column positions. These values are shifted over as more scenarios are added to a report.
int A = startColumnPosition;
int B = startColumnPosition + 1;
int C = startColumnPosition + 2;
int D = startColumnPosition + 3;
int eventCount = telemetryData.Count;
// Generic formatting and labels
workSheet.Cells[1, B] = userInput.BuildName;
workSheet.Cells[2, A] = userInput.ScenarioName;
workSheet.Cells[2, A].Font.Bold = true;
workSheet.Cells[3, B] = "Start";
workSheet.Cells[3, B].Font.Bold = true;
workSheet.Cells[3, C] = "End";
workSheet.Cells[3, C].Font.Bold = true;
workSheet.Cells[3, D] = "Result";
workSheet.Cells[3, D].Font.Bold = true;
Excel.Range formattingRange = workSheet.Range[workSheet.Cells[2, A], workSheet.Cells[eventCount + 8, D]];
formattingRange.NumberFormat = "0";
formattingRange.BorderAround2();
// Fill in the worksheet
for (int y = 0; y < eventCount; y++)
{
int eventRow = y + 4;
workSheet.Cells[eventRow, A] = y + 1;
workSheet.Cells[eventRow, B] = telemetryData[y].Start.Time;
workSheet.Cells[eventRow, C] = telemetryData[y].End.Time;
workSheet.Cells[eventRow, D] = $"=({ExcelColumnFromNumber(C)}{eventRow} - {ExcelColumnFromNumber(B)}{eventRow})";
}
int statsRow = telemetryData.Count + 5;
Excel.Range statsLabelsFormattingRange = workSheet.Range[workSheet.Cells[statsRow, A], workSheet.Cells[statsRow + 3, A]];
statsLabelsFormattingRange.Font.Bold = true;
string range = $"({ExcelColumnFromNumber(D)}{4}:{ExcelColumnFromNumber(D)}{eventCount + 3})";
workSheet.Cells[statsRow, A] = "High";
workSheet.Cells[statsRow, B] = $"=MAX{range}";
statsRow++;
workSheet.Cells[statsRow, A] = "Median";
workSheet.Cells[statsRow, B] = $"=MEDIAN{range}";
statsRow++;
workSheet.Cells[statsRow, A] = "Low";
workSheet.Cells[statsRow, B] = $"=MIN{range}";
statsRow++;
workSheet.Cells[statsRow, A] = $"{eventCount}th - 2nd Diff";
workSheet.Cells[statsRow, B] = $"={ExcelColumnFromNumber(D)}{eventCount + 3}-{ExcelColumnFromNumber(D)}{5}";
// Only merge cells for notes if there are notes to add.
Excel.Range notesCell = workSheet.Cells[statsRow + 2, A];
if (!string.IsNullOrEmpty(userInput.Notes))
{
notesCell.Value = userInput.Notes;
Excel.Range notes = workSheet.Range[workSheet.Cells[statsRow + 2, A], workSheet.Cells[statsRow + 2, D]].Merge();
}
// Adjust column widths to fit the content.
workSheet.Columns[A].ColumnWidth = 12;
for (int x = B; x <= D; x++)
{
workSheet.Columns[x].ColumnWidth = 8;
}
// Save the file.
workSheet.SaveAs(userInput.ResultFileName);
}
finally
{
excelApp.Workbooks.Close();
Marshal.ReleaseComObject(excelApp.Workbooks);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
public void crearExcel()
{
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.Visible = true;
//************************************************************//
//**************CONSTRUIMOS LA CABECERA DEL EXCEL*************//
//************************************************************//
ws.Range[ws.Cells[1, 1], ws.Cells[3, 7]].Merge();
ws.Cells[1, 1] = "RENDICIÓN CAJA OMEGA DISTRIBUIDORA";
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
ws.Range[ws.Cells[4, 1], ws.Cells[4, 7]].Merge();
ws.Cells[4, 1] = "Fecha Rendición: " + DateTime.Now.Date.ToString("dd/MM/yyyy");
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range cabecera = ws.get_Range("a1", "g4");
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
//************************************************************//
//*****************FIN LA CABECERA DEL EXCEL******************//
//************************************************************//
//************************************************************//
//*****************************EFECTIVO**********************//
//************************************************************//
int fila_efectivo = 7;
ws.Range[ws.Cells[fila_efectivo, 1], ws.Cells[fila_efectivo, 6]].Merge();
ws.Range["a7", "f7"].BorderAround2();
ws.Range["a7", "f7"].Font.Bold = true;
ws.Range["f7", "g7"].BorderAround2();
ws.Range["f7", "g7"].Font.Bold = true;
ws.Cells[fila_efectivo, 1] = dgv_tabla_efectivo.Rows[0].Cells[0].Value.ToString();
ws.Cells[fila_efectivo, 7] = "$" + dgv_tabla_efectivo.Rows[0].Cells[1].Value.ToString();
int fila_tarjeta = 8;
ws.Range[ws.Cells[fila_tarjeta, 1], ws.Cells[fila_tarjeta, 6]].Merge();
ws.Range["a8", "f8"].BorderAround2();
ws.Range["a8", "f8"].Font.Bold = true;
ws.Range["f8", "g8"].BorderAround2();
ws.Range["f8", "g8"].Font.Bold = true;
ws.Cells[fila_tarjeta, 1] = dgv_tabla_tarjeta.Rows[0].Cells[0].Value.ToString();
ws.Cells[fila_tarjeta, 7] = "$" + dgv_tabla_tarjeta.Rows[0].Cells[1].Value.ToString();
int fila_detalle_tarjeta = 9;
fila_detalle_tarjeta--;
for (int i = 0; i < dgv_tabla_detalle_tarjetas.Rows.Count; i++)
{
fila_detalle_tarjeta++;
ws.Range[ws.Cells[fila_detalle_tarjeta, 1], ws.Cells[fila_detalle_tarjeta, 5]].Merge();
ws.Range["a" + fila_detalle_tarjeta, "e" + fila_detalle_tarjeta].BorderAround2();
ws.Range["a" + fila_detalle_tarjeta, "e" + fila_detalle_tarjeta].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ws.Range["e" + fila_detalle_tarjeta, "f" + fila_detalle_tarjeta].BorderAround2();
for (int j = 0; j < 2; j++)
{
if (j == 0)
{
ws.Cells[fila_detalle_tarjeta, 1] = dgv_tabla_detalle_tarjetas.Rows[i].Cells[j].Value;
}
else
{
ws.Cells[fila_detalle_tarjeta, 6] = dgv_tabla_detalle_tarjetas.Rows[i].Cells[j].Value;
}
}
}
int fila_detalle_tarjeta_reparaciones = fila_detalle_tarjeta + 1;
fila_detalle_tarjeta_reparaciones--;
for (int i = 0; i < dgv_tabla_detalle_tarjetas.Rows.Count; i++)
{
fila_detalle_tarjeta_reparaciones++;
ws.Range[ws.Cells[fila_detalle_tarjeta_reparaciones, 1], ws.Cells[fila_detalle_tarjeta_reparaciones, 5]].Merge();
ws.Range["a" + fila_detalle_tarjeta_reparaciones, "e" + fila_detalle_tarjeta_reparaciones].BorderAround2();
ws.Range["a" + fila_detalle_tarjeta_reparaciones, "e" + fila_detalle_tarjeta_reparaciones].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ws.Range["e" + fila_detalle_tarjeta_reparaciones, "f" + fila_detalle_tarjeta_reparaciones].BorderAround2();
for (int j = 0; j < 2; j++)
{
if (j == 0)
{
ws.Cells[fila_detalle_tarjeta_reparaciones, 1] = dgv_tabla_detalle_tarjetas_reparaciones.Rows[i].Cells[j].Value;
}
else
{
ws.Cells[fila_detalle_tarjeta_reparaciones, 6] = dgv_tabla_detalle_tarjetas_reparaciones.Rows[i].Cells[j].Value;
}
}
}
//CABECEra CHEquE
int fila_cheque = fila_detalle_tarjeta_reparaciones + 1;
ws.Range[ws.Cells[fila_cheque, 1], ws.Cells[fila_cheque, 6]].Merge();
ws.Range["a" + fila_cheque, "f" + fila_cheque].BorderAround2();
ws.Range["f" + fila_cheque, "g" + fila_cheque].BorderAround2();
ws.Range["a" + fila_cheque, "f" + fila_cheque].Font.Bold = true;
ws.Range["f" + fila_cheque, "g" + fila_cheque].Font.Bold = true;
ws.Cells[fila_cheque, 1] = dgv_tabla_cheques.Rows[0].Cells[0].Value.ToString();
ws.Cells[fila_cheque, 7] = "$" + dgv_tabla_cheques.Rows[0].Cells[1].Value.ToString();
//DETALLE CHEQUE
int fila_detalle_cheque = fila_cheque + 1;
fila_detalle_cheque--;
for (int i = 0; i < dgv_tabla_detalle_cheque.Rows.Count; i++)
{
fila_detalle_cheque++;
ws.Range[ws.Cells[fila_detalle_cheque, 1], ws.Cells[fila_detalle_cheque, 5]].Merge();
ws.Range["a" + fila_detalle_cheque, "e" + fila_detalle_cheque].BorderAround2();
ws.Range["a" + fila_detalle_cheque, "e" + fila_detalle_cheque].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ws.Range["e" + fila_detalle_cheque, "f" + fila_detalle_cheque].BorderAround2();
for (int j = 0; j < 2; j++)
{
if (j == 0)
{
ws.Cells[fila_detalle_cheque, 1] = dgv_tabla_detalle_cheque.Rows[i].Cells[j].Value;
}
else
{
ws.Cells[fila_detalle_cheque, 6] = dgv_tabla_detalle_cheque.Rows[i].Cells[j].Value;
}
}
}
//CABECERA TRANSFERENCIA
int fila_transferencia = fila_detalle_cheque + 1;
ws.Range[ws.Cells[fila_transferencia, 1], ws.Cells[fila_transferencia, 6]].Merge();
ws.Range["a" + fila_transferencia, "f" + fila_transferencia].BorderAround2();
ws.Range["f" + fila_transferencia, "g" + fila_transferencia].BorderAround2();
ws.Range["a" + fila_transferencia, "f" + fila_transferencia].Font.Bold = true;
ws.Range["f" + fila_transferencia, "g" + fila_transferencia].Font.Bold = true;
ws.Cells[fila_transferencia, 1] = dgv_tabla_transferencia.Rows[0].Cells[0].Value.ToString();
ws.Cells[fila_transferencia, 7] = "$" + dgv_tabla_transferencia.Rows[0].Cells[1].Value.ToString();
//DETALLE TRANSFERENCIA
int fila_detalle_transferencia = fila_transferencia + 1;
fila_detalle_transferencia--;
for (int i = 0; i < dgv_tabla_transferencia_detalle.Rows.Count; i++)
{
fila_detalle_transferencia++;
ws.Range[ws.Cells[fila_detalle_transferencia, 1], ws.Cells[fila_detalle_transferencia, 5]].Merge();
ws.Range["a" + fila_detalle_transferencia, "e" + fila_detalle_transferencia].BorderAround2();
ws.Range["a" + fila_detalle_transferencia, "e" + fila_detalle_transferencia].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
ws.Range["e" + fila_detalle_transferencia, "f" + fila_detalle_transferencia].BorderAround2();
for (int j = 0; j < 2; j++)
{
if (j == 0)
{
ws.Cells[fila_detalle_transferencia, 1] = dgv_tabla_transferencia_detalle.Rows[i].Cells[j].Value;
}
else
{
ws.Cells[fila_detalle_transferencia, 6] = dgv_tabla_transferencia_detalle.Rows[i].Cells[j].Value;
}
}
}
int fila_total = fila_detalle_transferencia + 1;
ws.Range[ws.Cells[fila_total, 1], ws.Cells[fila_total, 6]].Merge();
ws.Range["a" + fila_total, "f" + fila_total].BorderAround2();
ws.Range["f" + fila_total, "g" + fila_total].BorderAround2();
ws.Range["a" + fila_total, "f" + fila_total].Font.Bold = true;
ws.Range["f" + fila_total, "g" + fila_total].Font.Bold = true;
ws.Cells[fila_total, 1] = dgv_tabla_total.Rows[0].Cells[0].Value.ToString();
ws.Cells[fila_total, 7] = "$" + dgv_tabla_total.Rows[0].Cells[1].Value.ToString();
}
public static void armarExcelRendiciones(DataGridView dgv_tabla_venta, DataGridView dgv_tabla_reparacion, DateTime desde, DateTime hasta, string usuario)
{
ControladorFuncVariadas control = new ControladorFuncVariadas();
Cursor.Current = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Interactive = false;
try
{
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.WindowState = XlWindowState.xlMaximized;
Microsoft.Office.Interop.Excel.Range cabecera = null;
ws.Cells[1, 1] = "RENDICIÓN " + usuario;
ws.Range[ws.Cells[1, 1], ws.Cells[3, 6]].Merge();
cabecera = ws.get_Range("a1", "f3");
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
ws.Cells[4, 1] = "Entre " + desde.ToString("dd/MM/yyyy") + " y " + hasta.ToString("dd/MM/yyyy");
ws.Range[ws.Cells[4, 1], ws.Cells[4, 6]].Merge();
Range desdehasta = null;
desdehasta = ws.get_Range("a4", "f4");
ws.Cells[4, 1].Font.Bold = true;
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Size = 11;
desdehasta.BorderAround2();
desdehasta.Interior.Color = Color.White;
ws.Cells[5, 1] = "ID VENTA";
ws.Cells[5, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range id_venta = ws.get_Range("a5", "a5");
id_venta.BorderAround2();
id_venta.Interior.Color = Color.White;
ws.Cells[5, 2] = "Fecha";
ws.Cells[5, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[5, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 2].Font.Bold = true;
Range venta_fecha = ws.get_Range("b5", "b5");
venta_fecha.BorderAround2();
venta_fecha.Interior.Color = Color.White;
ws.Cells[5, 3] = "Vendedor";
ws.Cells[5, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_vendedor = ws.get_Range("c5", "c5");
venta_vendedor.BorderAround2();
venta_vendedor.Interior.Color = Color.White;
ws.Cells[5, 4] = "Cliente";
ws.Cells[5, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_cliente = ws.get_Range("d5", "d5");
venta_cliente.BorderAround2();
venta_cliente.Interior.Color = Color.White;
ws.Cells[5, 5] = "Monto";
ws.Cells[5, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_monto = ws.get_Range("e5", "e5");
venta_monto.BorderAround2();
venta_monto.Interior.Color = Color.White;
ws.Cells[5, 6] = "Cobrado";
ws.Cells[5, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range venta_cobrado = ws.get_Range("f5", "f5");
venta_cobrado.BorderAround2();
venta_cobrado.Interior.Color = Color.White;
//********************
//Empiezo por cantidad
//********************
int fila = 6;
int columnas = 6;
for (int i = 0; i < dgv_tabla_venta.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla_venta.Rows[i].Cells["Id Venta"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
}
else if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla_venta.Rows[i].Cells["Fecha"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla_venta.Rows[i].Cells["Vendedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla_venta.Rows[i].Cells["Cliente"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 5] = dgv_tabla_venta.Rows[i].Cells["Monto"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla_venta.Rows[i].Cells["Cobrado"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
}
fila++;
}
ws.Cells[fila, 1] = "ID REPARACIÓN";
ws.Cells[fila, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range id_reparacion = ws.get_Range("a" + fila, "a" + fila);
id_reparacion.BorderAround2();
id_reparacion.Interior.Color = Color.White;
ws.Cells[fila, 2] = "Fecha";
ws.Cells[fila, 2].HorizontalAlignment = XlHAlign.xlHAlignCenter;
ws.Cells[fila, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 2].Font.Bold = true;
Range reparacion_fecha = ws.get_Range("b" + fila, "b" + fila);
reparacion_fecha.BorderAround2();
reparacion_fecha.Interior.Color = Color.White;
ws.Cells[fila, 3] = "Vendedor";
ws.Cells[fila, 3].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 3].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 3].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_vendedor = ws.get_Range("c" + fila, "c" + fila);
reparacion_vendedor.BorderAround2();
reparacion_vendedor.Interior.Color = Color.White;
ws.Cells[fila, 4] = "Cliente";
ws.Cells[fila, 4].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 4].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 4].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_cliente = ws.get_Range("d" + fila, "d" + fila);
reparacion_cliente.BorderAround2();
reparacion_cliente.Interior.Color = Color.White;
ws.Cells[fila, 5] = "Monto";
ws.Cells[fila, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_monto = ws.get_Range("e" + fila, "e" + fila);
reparacion_monto.BorderAround2();
reparacion_monto.Interior.Color = Color.White;
ws.Cells[fila, 6] = "Cobrado";
ws.Cells[fila, 6].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[fila, 6].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[fila, 6].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range reparacion_cobrado = ws.get_Range("f" + fila, "f" + fila);
reparacion_cobrado.BorderAround2();
reparacion_cobrado.Interior.Color = Color.White;
fila++;
for (int i = 0; i < dgv_tabla_reparacion.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla_reparacion.Rows[i].Cells["Id Venta"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 1].Borders);
}
else if (j == 1)
{
ws.Cells[fila, 2] = dgv_tabla_reparacion.Rows[i].Cells["Fecha"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 2].Borders);
}
else if (j == 2)
{
ws.Cells[fila, 3] = dgv_tabla_reparacion.Rows[i].Cells["Vendedor"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 3].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Cells[fila, 4] = dgv_tabla_reparacion.Rows[i].Cells["Cliente"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 4].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 5] = dgv_tabla_reparacion.Rows[i].Cells["Monto"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 5].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 5)
{
ws.Cells[fila, 6] = dgv_tabla_reparacion.Rows[i].Cells["Cobrado"].Value;
ControladorFuncVariadas.AllBorders(ws.Cells[fila, 6].Borders);
}
}
fila++;
}
//ws.Columns[2].NumberFormat = "######";
ws.Columns[5].NumberFormat = "$ #.###,00";
ws.Columns[6].NumberFormat = "$ #.###,00";
ws.Columns.AutoFit();
Excel.Interactive = true;
Excel.Visible = true;
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally { Cursor.Current = Cursors.Default; }
}
public void armarExcel()
{
Cursor.Current = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
Excel.Interactive = false;
try
{
Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
Worksheet ws = (Worksheet)Excel.ActiveSheet;
Excel.WindowState = XlWindowState.xlMaximized;
Microsoft.Office.Interop.Excel.Range cabecera = null;
if (listaprecio == 10)
{
ws.Cells[1, 1] = "STOCK";
ws.Range[ws.Cells[1, 1], ws.Cells[3, 9]].Merge();
ws.Range[ws.Cells[4, 1], ws.Cells[4, 9]].Merge();
cabecera = ws.get_Range("a1", "i4");
}
else
{
ws.Cells[1, 1] = "LISTA DE PRECIOS SIN IVA";
ws.Range[ws.Cells[1, 1], ws.Cells[3, 7]].Merge();
ws.Range[ws.Cells[4, 1], ws.Cells[4, 7]].Merge();
cabecera = ws.get_Range("a1", "g4");
}
ws.Cells[1, 1].Font.Bold = true;
ws.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[1, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[1, 1].Font.Size = 20;
cabecera.BorderAround2();
cabecera.Interior.Color = Color.White;
ws.Cells[4, 1] = "Fecha : " + DateTime.Now.Date.ToString("dd/MM/yyyy");
ws.Cells[4, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[4, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[4, 1].Font.Bold = true;
//ws.Range[ws.Cells[5, 1], ws.Cells[5, 2]].Merge();
ws.Cells[5, 1] = "COD";
ws.Cells[5, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 1].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 1].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range lista = ws.get_Range("a5", "a5");
lista.BorderAround2();
lista.Interior.Color = Color.White;
ws.Range[ws.Cells[5, 2], ws.Cells[5, 4]].Merge();
ws.Cells[5, 2] = "Producto";
ws.Cells[5, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 2].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 2].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range razon = ws.get_Range("b5", "d5");
razon.BorderAround2();
razon.Interior.Color = Color.White;
ws.Range[ws.Cells[5, 5], ws.Cells[5, 6]].Merge();
ws.Cells[5, 5] = "Categoría";
ws.Cells[5, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 5].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 5].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range email = ws.get_Range("e5", "f5");
email.BorderAround2();
email.Interior.Color = Color.White;
ws.Cells[5, 7] = "Precio";
ws.Cells[5, 7].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 7].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 7].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range precio = ws.get_Range("g5", "g5");
precio.BorderAround2();
precio.Interior.Color = Color.White;
if (listaprecio == 10)
{
ws.Cells[5, 8] = "Stock";
ws.Cells[5, 8].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 8].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 8].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range stock = ws.get_Range("h5", "h5");
stock.BorderAround2();
stock.Interior.Color = Color.White;
ws.Cells[5, 9] = "Total";
ws.Cells[5, 9].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ws.Cells[5, 9].VerticalAlignment = XlVAlign.xlVAlignCenter;
ws.Cells[5, 9].Font.Bold = true;
Microsoft.Office.Interop.Excel.Range total = ws.get_Range("i5", "i5");
total.BorderAround2();
total.Interior.Color = Color.White;
}
int fila = 6;
int columnas;
if (listaprecio == 10)
{
columnas = 6;
}
else
{
columnas = 4;
}
for (int i = 0; i < dgv_tabla.Rows.Count; i++)
{
for (int j = 0; j < columnas; j++)
{
if (j == 0)
{
ws.Cells[fila, 1] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Cells[fila, 1].Borders);
//ws.Cells[fila, 1].Color = Color.White;
}
else if (j == 1)
{
ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Merge();
ws.Cells[fila, 2] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Borders);
//ws.Range[ws.Cells[fila, 2], ws.Cells[fila, 4]].Color = Color.White;
}
else if (j == 3)
{
ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Merge();
ws.Cells[fila, 5] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Borders);
// ws.Range[ws.Cells[fila, 5], ws.Cells[fila, 6]].Color = Color.White;
}
else if (j == 2)
{
ws.Cells[fila, 7] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Cells[fila, 7].Borders);
//ws.Cells[fila, 7].Color = Color.White;
}
else if (j == 4)
{
ws.Cells[fila, 8] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Cells[fila, 8].Borders);
}
else if (j == 5)
{
ws.Cells[fila, 9] = dgv_tabla.Rows[i].Cells[j].Value;
AllBorders(ws.Cells[fila, 9].Borders);
}
}
fila++;
}
ws.Columns[1].NumberFormat = "######";
//ws.Range("A1:F20").Borders.LineStyle = xlContinuous
//AllBorders(ws.get_Range("a1", "zz9999").Borders);
ws.Columns[7].NumberFormat = "$ #.###,00";
if (listaprecio == 10)
{
ws.Columns[9].NumberFormat = "$ #.###,00";
}
Excel.Interactive = true;
Excel.Visible = true;
}
catch (Exception ex) { MessageBox.Show(ex.ToString()); }
finally { Cursor.Current = Cursors.Default; }
}
private void ExportReport(string ReportName, DataGridView dataGridView)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlBook = xlApp.Workbooks.Add();
Excel.Worksheet xlSheet = xlBook.Worksheets.Add();
xlSheet.Name = ReportName;
int xlRow = 1;
Recordset recordSet = new Recordset();
try
{
try
{
xlSheet.Columns.HorizontalAlignment = 2;
xlSheet.Columns.Font.Name = "Times New Roman";
xlSheet.Rows.Item[xlRow].Font.Bold = 1;
xlSheet.Rows.Item[xlRow].Interior.ColorIndex = 15;
recordSet = GetRecordSet(dataGridView);
if (recordSet.State == 0)
{
recordSet.Open();
}
}
catch (Exception ex)
{
MessageBox.Show("Message : " + ex.Message + "\nStacktrace : " + ex.StackTrace);
goto PROC_EXIT;
}
for (int xlColumn = 1; xlColumn < recordSet.Fields.Count; xlColumn++)
{
xlSheet.Cells.Item[xlRow, xlColumn] = recordSet.Fields[xlColumn].Name;
Excel.Range range = xlSheet.Cells[xlRow, xlColumn] as Excel.Range;
range.BorderAround2(1, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic);
}
for (int xlColumn = 1; xlColumn < recordSet.Fields.Count; xlColumn++)
{
xlSheet.Columns[xlColumn].NumberFormat = TranslateNumberFormat(recordSet.Fields[xlColumn].Type);
}
for (int recordCount = 0; recordCount < recordSet.RecordCount; recordCount++)
{
xlRow++;
if (recordSet.Fields[0].Value == true)
{
for (int fieldCount = 1; fieldCount < recordSet.Fields.Count; fieldCount++)
{
xlSheet.Cells.Item[xlRow, fieldCount] = recordSet.Fields[fieldCount].Value;
}
}
recordSet.MoveNext();
}
xlSheet.PageSetup.LeftHeader = "&[Page]" + " of " + "&[Pages]";
xlSheet.PageSetup.RightHeader = "&[Date]" + " &[Time]";
xlSheet.PageSetup.HeaderMargin = 5;
xlSheet.PageSetup.BottomMargin = 5;
xlSheet.PageSetup.LeftMargin = 5;
xlSheet.PageSetup.RightMargin = 5;
xlSheet.PageSetup.TopMargin = 2;
xlSheet.Columns.AutoFit();
xlSheet.Rows.AutoFit();
xlApp.UserControl = true;
xlApp.Visible = true;
}
catch (Exception ex) { MessageBox.Show("Message : " + ex.Message + "\nStacktrace : " + ex.StackTrace); }
PROC_EXIT:
xlSheet = null;
xlBook = null;
xlApp = null;
GC.Collect();
}
private void FillSheet()
{
var imageAnswerJoinQuery = from image in Images
join answer in Answers on image.ImageId equals answer.ImageId
select new
{
ImageId = image.ImageId,
PatientId = image.Patient,
Region = image.Region,
NameId = answer.NameId,
Answer = answer.Answer,
Comment = answer.Comment,
Timestamp = answer.Timestamp
};
string[] headers = new string[]
{
"Kép azonosító",
"Páciens azonosító",
"Régió",
"Kiöltő azonosító",
"Válasz",
"Megjegyzés",
"Válaszadás időpontja"
};
Excel.Range headerRange = excWS.get_Range(GetCell(1, 1), GetCell(headers.Length, 1));
headerRange.Value2 = headers;
object[,] imagesAndAnswers = new object[imageAnswerJoinQuery.Count(), headers.Length];
int iteration = 0;
foreach (var imgAns in imageAnswerJoinQuery)
{
imagesAndAnswers[iteration, 0] = imgAns.ImageId;
imagesAndAnswers[iteration, 1] = imgAns.PatientId;
imagesAndAnswers[iteration, 2] = imgAns.Region;
imagesAndAnswers[iteration, 3] = imgAns.NameId;
imagesAndAnswers[iteration, 4] = imgAns.Answer;
imagesAndAnswers[iteration, 5] = imgAns.Comment;
imagesAndAnswers[iteration, 6] = imgAns.Timestamp;
iteration++;
}
Excel.Range dataRange = excWS.get_Range(GetCell(1, 2), GetCell(headers.Length, 1 + imagesAndAnswers.GetLength(0)));
dataRange.Value2 = imagesAndAnswers;
Excel.Range dateRange = excWS.get_Range(GetCell(headers.Length, 2), GetCell(headers.Length, 1 + imagesAndAnswers.GetLength(0)));
dateRange.NumberFormat = "YYYY-MM-DD HH:mm:ss";
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Borders.Color = Color.Black;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
dataRange.Borders.Color = Color.Black;
dataRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range commentRange = excWS.get_Range(GetCell(6, 2), GetCell(6, 1 + imagesAndAnswers.GetLength(0)));
commentRange.Font.Italic = true;
Excel.Range answerRange = excWS.get_Range(GetCell(5, 2), GetCell(5, 1 + imagesAndAnswers.GetLength(0)));
Excel.Range regionRange = excWS.get_Range(GetCell(3, 2), GetCell(3, 1 + imagesAndAnswers.GetLength(0)));
Excel.Range imageRange = excWS.get_Range(GetCell(1, 2), GetCell(1, 1 + imagesAndAnswers.GetLength(0)));
imageRange.Interior.Color = Color.LightGreen;
regionRange.Interior.Color = Color.LightGreen;
answerRange.Interior.Color = Color.LightGreen;
dateRange.Interior.Color = Color.LightGreen;
}
private void CreateTable()
{
string[] headers = new string[]
{
"Location",
"Observation Time",
"Weather",
"Temperature (°C)",
"Wind (MpH)",
"Relative Humidity (%)"
};
for (int i = 0; i < headers.Length; i++)
{
xlSheet.Cells[1, i + 1] = headers[i];
}
XmlDocument xml = new XmlDocument();
object[,] values = new object[links.Count, headers.Length];
int cnt = 0;
foreach (weatherLinks link in links)
{
xml.Load(Application.StartupPath.ToString() + "\\xmlFiles\\" + link.fileName);
var location = xml.GetElementsByTagName("location")[0]?.InnerText.ToString();
var obsTime = xml.GetElementsByTagName("observation_time")[0]?.InnerText.ToString();
var weather = xml.GetElementsByTagName("weather")[0]?.InnerText.ToString();
var temp = xml.GetElementsByTagName("temp_c")[0]?.InnerText.ToString();
var wind = xml.GetElementsByTagName("wind_mph")[0]?.InnerText.ToString();
var hum = xml.GetElementsByTagName("relative_humidity")[0]?.InnerText.ToString();
values[cnt, 0] = location;
values[cnt, 1] = obsTime.Substring(obsTime.Length - 25, 25);
values[cnt, 2] = weather;
values[cnt, 3] = temp;
values[cnt, 4] = wind;
values[cnt, 5] = hum;
cnt++;
}
xlSheet.get_Range(
GetCell(2, 1),
GetCell(1 + values.GetLength(0), values.GetLength(1))).Value2 = values;
Excel.Range headerRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1, headers.Length));
headerRange.Font.Bold = true;
headerRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
headerRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
headerRange.EntireColumn.AutoFit();
headerRange.RowHeight = 40;
headerRange.Interior.Color = Color.LightPink;
headerRange.BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThick);
Excel.Range fullRange = xlSheet.get_Range(GetCell(1, 1), GetCell(1 + values.GetLength(0), headers.Length));
fullRange.RowHeight = 20;
fullRange.BorderAround2(Excel.XlLineStyle.xlContinuous);
fullRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
Excel.Range rightRange = xlSheet.get_Range(GetCell(1, 2), GetCell(1 + values.GetLength(0), headers.Length));
rightRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Excel.Range leftRange = xlSheet.get_Range(GetCell(2, 1), GetCell(1 + values.GetLength(0), 1));
leftRange.Font.Bold = true;
leftRange.Interior.Color = Color.Linen;
Excel.Range tempRange = xlSheet.get_Range(GetCell(1, 4), GetCell(1 + values.GetLength(0), 4));
tempRange.Font.Bold = true;
}
