/// <summary>
/// BuildTeachersTable
/// </summary>
/// <param name="ws"></param>
/// <param name="Teachers"></param>
private static void BuildTeachersTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByTeacher> Teachers)
{
ws.Column(1).Width = 17.86;
ws.Column(2).Width = 12.43;
//Set Header titles
ws.Cells[4, 1].Value = "Teachers";
ws.Cells[4, 1].Style.Font.Bold = true;
ws.Cells[5, 1].Value = "Teacher Name";
ws.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
// ws.Cells[5, 1].AutoFilter = true;
ws.Cells[5, 2].Value = "Office Visits";
ws.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
// ws.Cells[5, 2].AutoFilter = true;
//Get Data for Teachers
for (int i = 0; i < Teachers.Count(); i++)
{
ws.Cells[i + 6, 1].Value = Teachers.ElementAt(i).sent_by_contact_name;
ws.Cells[i + 6, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[i + 6, 2].Value = Teachers.ElementAt(i).total_visits;
ws.Cells[i + 6, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
//Set Header style
using (ExcelRange rng = ws.Cells[4, 1, 5 + Teachers.Count(), 2])
{
rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);
}
}
private void SetColumns(ExcelWorksheet worksheet)
{
worksheet.Column(1).Width = 20;
worksheet.Column(2).Width = 20;
worksheet.Column(3).Width = 20;
worksheet.Column(4).Width = 20;
worksheet.Column(5).Width = 20;
}
public void SetColumnWidth(string ColumnIndex, int Width)
{
if (worksheet == null)
{
return;
}
int nColumnIndex = 1;
if (Int32.TryParse(ColumnIndex, out nColumnIndex))
{
worksheet.Column(nColumnIndex).Width = Width;
}
}
public void AutoFitAll()
{
for (int i = 1; i < workSheet.Dimension.End.Column + 1; i++)
{
workSheet.Column(i).AutoFit();
}
}
private static EpplusResult FormatResult(ExcelWorksheet ws, int count, PropertyInfo[] cols, ExcelPackage ep)
{
var range = ws.Cells[1, 1, count + 1, cols.Length];
var table = ws.Tables.Add(range, "Members");
table.ShowFilter = false;
table.TableStyle = TableStyles.Light9;
int userdatacol = 1;
int groupcol = 1;
int questionscol = 1;
for (var i = 0; i < cols.Length; i++)
{
var col = i + 1;
var name = cols[i].Name;
table.Columns[i].Name = name;
var colrange = ws.Cells[1, col, count + 2, col];
if (name.Contains("Date") || name == "LastAttend")
{
colrange.Style.Numberformat.Format = "mm-dd-yy";
colrange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
ws.Column(col).Width = 12;
}
switch (name)
{
case "UserData":
colrange.Style.WrapText = true;
userdatacol = col;
break;
case "Groups":
colrange.Style.WrapText = true;
groupcol = col;
break;
case "Questions":
colrange.Style.WrapText = true;
questionscol = col;
break;
}
}
ws.Cells[ws.Dimension.Address].AutoFitColumns();
if(userdatacol > 1)
ws.Column(userdatacol).Width = 40.0;
if(groupcol > 1)
ws.Column(groupcol).Width = 60.0;
if(questionscol > 1)
ws.Column(questionscol).Width = 40.0;
return new EpplusResult(ep, "OrgMember.xlsx");
}
private static void AdjustExcel(int count, ExcelWorksheet excel, int iRow)
{
for(var j = 1; j <= count; j++)
{
excel.Column(j).AutoFit();
for(var i = 1; i < iRow; i++)
{
excel.Cells[i, j].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Gray);
}
}
}
private void CreateHeaderClearWeights(ExcelWorksheet workSheet)
{
var cell = workSheet.Cells["A2:H2"];
cell.Merge = true;
cell.Value = "";
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);
FillHeaderSquare(workSheet.Cells["A3"], "№ п/п");
workSheet.Column(1).Width = 11;
FillHeaderSquare(workSheet.Cells["B3"], "Профиль");
workSheet.Column(2).Width = 11;
FillHeaderSquare(workSheet.Cells["C3"], "Типоразмер");
workSheet.Column(3).Width = 15;
FillHeaderSquare(workSheet.Cells["D3"], "Марка стали");
workSheet.Column(4).Width = 25;
FillHeaderSquare(workSheet.Cells["E3"], "ГОСТ на сортамент");
workSheet.Column(5).Width = 25;
FillHeaderSquare(workSheet.Cells["F3"], "Вес");
workSheet.Column(6).Width = 14;
FillHeaderSquare(workSheet.Cells["G3"], "Коэфф. использ.");
workSheet.Column(7).Width = 8;
FillHeaderSquare(workSheet.Cells["H3"], "Вес с отходом");
workSheet.Column(8).Width = 14;
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column=ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while(column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if(address.Start.Column==1 && address.End.Column==ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if(cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
private void CreateHeaderBillInnerShift(ExcelWorksheet workSheet)
{
workSheet.PrinterSettings.Orientation = eOrientation.Landscape;
workSheet.PrinterSettings.PaperSize = ePaperSize.A4;
workSheet.PrinterSettings.FitToPage = true;
workSheet.PrinterSettings.FitToWidth = 1;
workSheet.Cells["B2"].Value = "Накладная №";
workSheet.Cells["B3"].Value = "Дата";
workSheet.Cells["C3"].Style.Numberformat.Format = "dd.mm.yyyy";
workSheet.Cells["A5"].Value = "Отправитель";
workSheet.Cells["A6"].Value = "Получатель";
workSheet.Cells["B2"].Style.Font.Bold = true;
workSheet.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["B3"].Style.Font.Bold = true;
workSheet.Cells["B3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["A5"].Style.Font.Bold = true;
workSheet.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["A6"].Style.Font.Bold = true;
workSheet.Cells["A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
FillHeaderSquare(workSheet.Cells["A8"], "Наименование");
FillHeaderSquare(workSheet.Cells["B8"], "№ заказа");
FillHeaderSquare(workSheet.Cells["C8"], "№ чертежа");
FillHeaderSquare(workSheet.Cells["D8"], "Профиль");
FillHeaderSquare(workSheet.Cells["E8"], "Типоразмер");
FillHeaderSquare(workSheet.Cells["F8"], "Кол-во шт.");
FillHeaderSquare(workSheet.Cells["G8"], "Примечание");
FillHeaderSquare(workSheet.Cells["H8"], "Тех. маршрут");
workSheet.Column(1).Width = 23;
workSheet.Column(2).Width = 9;
workSheet.Column(3).Width = 25;
workSheet.Column(4).Width = 15;
workSheet.Column(5).Width = 10;
workSheet.Column(6).Width = 7;
workSheet.Column(7).Width = 13;
workSheet.Column(8).Width = 30;
workSheet.Cells["D2"].Style.WrapText = true;
}
public ActionResult DescargaHistorico(string __a)
{
Int32 _fila = 2;
String _servidor;
String _ruta;
if (__a.Length == 0)
{
return(View());
}
List <AsignaLecturaReLectura> _lista = MvcApplication._Deserialize <List <AsignaLecturaReLectura> >(__a);
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
_ruta = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Historico de Lecturas");
oWs.Cells[1, 1].Value = "MEDIDOR";
oWs.Cells[1, 2].Value = "FECHA DE LECTURA";
oWs.Cells[1, 3].Value = "LECTURA";
oWs.Cells[1, 4].Value = "OPERARIO";
oWs.Cells[1, 5].Value = "OBSERVACION";
oWs.Cells[1, 6].Value = "ESTADO";
foreach (AsignaLecturaReLectura oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.medidorLectura;
oWs.Cells[_fila, 2].Value = oBj.fecLectura;
oWs.Cells[_fila, 3].Value = (oBj.lectura);
oWs.Cells[_fila, 4].Value = (oBj.ope_nombre);
oWs.Cells[_fila, 5].Value = (oBj.obsLectura);
oWs.Cells[_fila, 6].Value = (oBj.Estado);
_fila++;
}
oWs.Row(1).Style.WrapText = true;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
oWs.Column(1).AutoFit();
oWs.Column(2).AutoFit();
oWs.Column(3).AutoFit();
oWs.Column(4).AutoFit();
oWs.Column(5).AutoFit();
oWs.Column(6).AutoFit();
oEx.Save();
}
return(new ContentResult
{
Content = "{ \"__a\": \"" + _servidor + "\" }",
ContentType = "application/json"
});
}
public string Reporte_DetalleLecturas(int id_local, int id_servicio, string fechaini, string fechaFin, int ipOperario, int categoriaLectura)
{
string Res = "";
string _servidor;
string _ruta;
string resultado = "";
int _fila = 2;
string FileRuta = "";
string FileExcel = "";
List <ResumenLecturas> loDatos = new List <ResumenLecturas>();
try
{
NLectura obj_negocio = new NLectura();
loDatos = obj_negocio.NListaLecturaDetalladoEstado(id_local, id_servicio, fechaini, fechaFin, ipOperario, categoriaLectura);
if (loDatos.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/Upload/Detalle_Resumen_Lecturas_" + _servidor);
string rutaServer = ConfigurationManager.AppSettings["servidor-archivos"];
FileExcel = rutaServer + "Detalle_Resumen_Lecturas_" + _servidor;
FileInfo _file = new FileInfo(FileRuta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(FileRuta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("ControlProcesos");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "Lecturista";
oWs.Cells[1, 2].Value = "Direccion";
oWs.Cells[1, 3].Value = "Suministro";
oWs.Cells[1, 4].Value = "Medidor";
oWs.Cells[1, 5].Value = "Zona";
oWs.Cells[1, 6].Value = "Lectura";
oWs.Cells[1, 7].Value = "Confirmacion Lectura";
oWs.Cells[1, 8].Value = "Observacion";
oWs.Cells[1, 9].Value = "Estado";
//marco detalle CABECERA
for (int i = 1; i <= 11; i++)
{
oWs.Cells[1, i].Style.Font.Size = 9;
oWs.Cells[1, i].Style.Font.Bold = true;
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
oWs.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
foreach (ResumenLecturas obj in loDatos)
{
//marco detalle
for (int i = 1; i <= 9; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
}
oWs.Cells[_fila, 1].Value = obj.id_Operario_Lectura;
oWs.Cells[_fila, 2].Value = obj.direccion_lectura;
oWs.Cells[_fila, 3].Value = obj.suministro_lectura;
oWs.Cells[_fila, 4].Value = obj.medidor_lectura;
oWs.Cells[_fila, 5].Value = obj.Zona_lectura;
oWs.Cells[_fila, 6].Value = obj.LecturaMovil_Lectura;
oWs.Cells[_fila, 7].Value = obj.confirmacion_Lectura;
oWs.Cells[_fila, 8].Value = obj.abreviatura_observacion;
oWs.Cells[_fila, 9].Value = obj.descripcion_estado;
_fila++;
}
oWs.Cells.Style.Font.Size = 8; //letra tamaño
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 9; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
//Get the startcolumn
//ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._values.Exists(0, address.Start.Column))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._values.GetValue(0, address.Start.Column) as ExcelColumn;
}
//var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
//column.StyleID = styleCashe[s];
ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
//column.StyleID = newId;
ws.SetStyle(0, column.ColumnMin, newId);
}
//index++;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
column._columnMax = address.End.Column;
break;
}
else
{
column = (ws._values.GetValue(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
//newCol.StyleID = styleCashe[s];
//ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
//newCol.StyleID = newId;
ws.SetStyle(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator <int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
//cse.Value = newId;
ws.SetStyle(cse.Row, cse.Column, newId);
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
//ExcelRow row = ws.Row(rowNum);
var s = ws._styles.GetValue(rowNum, 0);
if (s == 0)
{
//iteratte all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator <int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value;
var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws._styles.Exists(rowNum, col))
{
ws._styles.SetValue(rowNum, col, s);
}
}
}
}
ws.SetStyle(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(rowNum, 0, styleCashe[s]);
//row.StyleID = styleCashe[s];
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws._styles.SetValue(rowNum, 0, newId);
ws.SetStyle(rowNum, 0, newId);
}
}
//Get Start Cell
//ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
//int index = ws._cells.IndexOf(rowID);
//index = ~index;
var cse2 = new CellsStoreEnumerator <int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
//while (index < ws._cells.Count)
while (cse2.Next())
{
//var cell = ws._cells[index] as ExcelCell;
//if(cell.Row > address.End.Row)
//{
// break;
//}
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
cse2.Value = newId;
ws.SetStyle(cse2.Row, cse2.Column, newId);
}
}
}
else //Cellrange
{
//var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
//while(cse.Next())
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
//ExcelCell cell = ws.Cell(row, col);
//int s = ws._styles.GetValue(row, col);
var s = GetStyleId(ws, row, col);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(row, col, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(row, col, newId);
}
}
}
}
}
private ExcelWorksheet CreateListView_Table(ExcelWorksheet worksheet, DataRow dr,
int row, string fieldformat, System.Drawing.Color colFromHex, DataSet ds_columns)
{
worksheet.Column(1).Width = 15;
worksheet.Column(2).Width = 15;
worksheet.Cells["A1"].Value = "TableName";
worksheet.Cells["B1"].Value = dr["name"].ToString();
worksheet.Cells["A2"].Value = "ViewType";
worksheet.Cells["B2"].Value = "ListView";
worksheet.Cells["A3"].Value = "ViewName";
worksheet.Cells["B3"].Value = dr["name"].ToString() + "_ListView";
worksheet.Cells["A4"].Value = "Title";
worksheet.Cells["A5"].Value = "Root Service URL";
worksheet.Cells["B5"].Value = "output/service";
worksheet.Cells["A6"].Value = "Order By";
worksheet.Cells["A7"].Value = "Filter";
worksheet.Cells["B7"].Value = "TRUE";
worksheet.Cells["A8"].Value = "PageSize";
worksheet.Cells["B8"].Value = "20";
//
worksheet.Cells["A9"].Value = "EditView";
worksheet.Cells["A10"].Value = "FilterView";
worksheet.Cells["A11"].Value = "InputParameters";
worksheet.Cells["A12"].Value = "FilterParameters";
worksheet.Cells["A13"].Value = "MasterPage";
//
worksheet.Cells["A14"].Value = "ButtonNew";
worksheet.Cells["B14"].Value = "TRUE";
worksheet.Cells["C14"].Value = "Caption";
worksheet.Cells["D14"].Value = "เพิ่ม";
worksheet.Cells["E14"].Value = "AppearanceRules";
worksheet.Cells["F14"].Value = "TRUE";
worksheet.Cells["A15"].Value = "ButtonSave";
worksheet.Cells["B15"].Value = "FALSE";
worksheet.Cells["C15"].Value = "Caption";
worksheet.Cells["D15"].Value = "บันทึก";
worksheet.Cells["E15"].Value = "AppearanceRules";
worksheet.Cells["F15"].Value = "FALSE";
worksheet.Cells["A16"].Value = "ButtonDelete";
worksheet.Cells["B16"].Value = "TRUE";
worksheet.Cells["C16"].Value = "Caption";
worksheet.Cells["D16"].Value = "ลบ";
worksheet.Cells["E16"].Value = "AppearanceRules";
worksheet.Cells["F16"].Value = "TRUE";
//
worksheet.Cells["A17"].Value = "SqlSelect";
worksheet.Cells["A18"].Value = "SqlInsert";
worksheet.Cells["A19"].Value = "SqlUpdate";
worksheet.Cells["A20"].Value = "SqlDelete";
worksheet.Cells["F17"].Value = "SqlSelectParameter";
worksheet.Cells["F18"].Value = "SqlInsertParameter";
worksheet.Cells["F19"].Value = "SqlUpdateParameter";
worksheet.Cells["F20"].Value = "SqlDeleteParameter";
//
////Add the headers
worksheet.Cells["A21"].Value = "Caption";
worksheet.Cells["B21"].Value = "Column";
worksheet.Cells["C21"].Value = "Format";
worksheet.Cells["D21"].Value = "TextAlignment";
worksheet.Cells["E21"].Value = "IsHideResize";
worksheet.Cells["F21"].Value = "CustomStyle";
colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00");
worksheet.Cells["A21:F21"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A21:F21"].Style.Fill.BackgroundColor.SetColor(colFromHex);
row = 22;
foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
{
fieldformat = "";
if (row == 22)
{
if (dr_columns["CONSTRAINT_TYPE"].ToString().ToLower() == "pk")
worksheet.Cells["B11"].Value = dr_columns["name"].ToString();
}
string[] typename = dr_columns["type_name"].ToString().Split(' ');
if (typename[0].ToString().ToString() == "date" || typename[0].ToString().ToString() == "datetime")
{
fieldformat = "d MMM yyyy";
}
else if (typename[0].ToString().ToString() == "int" || typename[0].ToString().ToString() == "decimal"
|| typename[0].ToString().ToString() == "numeric" || typename[0].ToString().ToString() == "money")
{
fieldformat = "n2";
}
worksheet.Cells["A" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["B" + row.ToString()].Value = dr_columns["name"].ToString();
worksheet.Cells["C" + row.ToString()].Value = fieldformat;
row++;
}
row--;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Size = 10;
return worksheet;
}
public static void ExportGridToExcel(ExcelWorksheet ws, DataGridView grid)
{
for (int i = 0; i < grid.Columns.Count; i++)
{
// header
var hCell = ws.Cells[1, i + 1];
hCell.Value = grid.Columns[i].HeaderText;
hCell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
hCell.Style.Fill.BackgroundColor.SetColor(Color.Wheat);
hCell.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
for (int j = 0; j < grid.Rows.Count; j++)
{
ws.Cells[j + 2, i + 1].Value = (grid[i, j].Value ?? string.Empty);
}
ws.Column(i + 1).AutoFit();
}
}
/// <summary>
/// BuildViolationsTable
/// </summary>
/// <param name="ws"></param>
/// <param name="OffenseTypes"></param>
private static void BuildViolationsTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByOffenseType> OffenseTypes)
{
ws.Column(11).Width = 50;
ws.Column(11).Style.WrapText = true;
ws.Column(12).Width = 12.14;
ws.Cells[4, 11].Value = "Violations";
ws.Cells[4, 11].Style.Font.Bold = true;
ws.Cells[5, 11].Value = "Offense Type";
ws.Cells[5, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[5, 12].Value = "Office Visits";
ws.Cells[5, 12].Style.Border.BorderAround(ExcelBorderStyle.Thin);
for (int i = 0; i < OffenseTypes.Count(); i++)
{
ws.Cells[i + 6, 11].Value = OffenseTypes.ElementAt(i).offense_type;
ws.Cells[i + 6, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[i + 6, 12].Value = OffenseTypes.ElementAt(i).total_visits;
ws.Cells[i + 6, 12].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
//Set Header style
using (ExcelRange rng = ws.Cells[4, 11, 5 + OffenseTypes.Count(), 12])
{
rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);
}
}
private void saveButton_Click(object sender, EventArgs e)
{
if (dataTable.RowCount == 0)
{
return;
}
int rowIndex = dataTable.Rows.Add();
string filename;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Xlsx files|*.xlsx|Xls files|*.xls";
DialogResult res = sfd.ShowDialog();
filename = sfd.FileName;
var regExp = @"^(?:[\w]\:|\\)(\\[\p{L}_\-\.\s0-9]+)+\.((xlsx)|(xls))$";
Regex regex = new Regex(regExp);
if (res == DialogResult.OK && regex.IsMatch(filename))
{
filename = sfd.FileName;
}
else if (res == DialogResult.Cancel)
{
return;
}
else
{
MessageBoxIcon messageBoxIcon = MessageBoxIcon.Error;
MessageBox.Show("Could not save file\nTry again!", "Saving failed", MessageBoxButtons.OK, messageBoxIcon);
return;
}
using (ExcelPackage excel = new ExcelPackage())
{
//var xlApp = new OfficeOpenXml.FormulaParsing.Excel.Application();
excel.Workbook.Worksheets.Add("Worksheet1");
FileInfo excelFile = new FileInfo(filename);
OfficeOpenXml.ExcelWorksheet excelWorkSheet = excel.Workbook.Worksheets["Worksheet1"];
var style = new CellStyle();
style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
style.VerticalAlignment = VerticalAlignmentStyle.Center;
excelWorkSheet.Cells["A1"].Value = "Date:";
excelWorkSheet.Cells["B1"].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
excelWorkSheet.Column(2).Width = 20;
excelWorkSheet.Cells["A2"].Value = "Available sources:";
excelWorkSheet.Cells["B2"].Value = "Robots:";
excelWorkSheet.Cells["B2"].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Right;
excelWorkSheet.Cells["B3"].Value = "Operators:";
excelWorkSheet.Cells["B3"].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Right;
excelWorkSheet.Cells["C3"].Value = robotLimit;
excelWorkSheet.Cells["C2"].Value = operatorLimit;
excelWorkSheet.Cells["A5"].Value = "Product";
excelWorkSheet.Cells["B5"].Value = "Number of robots";
excelWorkSheet.Cells["C5"].Value = "Number of operators";
excelWorkSheet.Cells["D5"].Value = "Profit";
var mergedRange = excelWorkSheet.Cells["A4:D4"];
mergedRange.Merge = true;
mergedRange.Value = "Allocation by products";
mergedRange.Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
for (int i = 1; i < 10; i++)
{
excelWorkSheet.Cells["A" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["B" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["C" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Cells["D" + i].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
}
//writeText.WriteLine("Available sources:;" + "robots;" + 10);
//writeText.WriteLine(";operators;" + 4);
//writeText.Write("All products: " + ";" + prodCount + "\n");
//writeText.WriteLine("Allocation by products:");
//writeText.Write("Product" + ";" + "Number of robots" + ";" + "Number of operators" + ";" + "Profit\n");
string fromYellowLine;
string toYellowLine;
string all;
int prodcnt = 1;
int rowCnt = 6;
foreach (var i in indexList) //
{
Console.WriteLine("forban");
Table table = tables[i.Product];
double profit = form.getU(i.Product, i.Robot, i.Worker);
Console.WriteLine("pr: " + profit);
excelWorkSheet.Cells["A" + rowCnt].Value = i.Product + 1 + ".";
excelWorkSheet.Cells["B" + rowCnt].Value = i.Robot;
excelWorkSheet.Cells["C" + rowCnt].Value = i.Worker;
excelWorkSheet.Cells["D" + rowCnt].Value = profit;
//excelWorkSheet.Cells["D" + rowCnt].Style.Numberformat = (OfficeOpenXml.Style.ExcelNumberFormat)NumberFormatBuilder.Accounting(3, true);
//writeText.WriteLine(prodcnt + "." + ";" + i[j] + ";" + i[j + 1] + ";" + profit);
fromYellowLine = "A" + rowCnt.ToString();
toYellowLine = "D" + rowCnt.ToString();
all = fromYellowLine + ":" + toYellowLine;
excelWorkSheet.Cells[all].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
all = fromYellowLine + ":" + toYellowLine;
prodcnt++;
rowCnt++;
}
excelWorkSheet.Cells["A" + rowCnt].Value = "Total";
excelWorkSheet.Cells["B" + rowCnt].Value = allRobot;
excelWorkSheet.Cells["C" + rowCnt].Value = allWorker;
excelWorkSheet.Cells["D" + rowCnt].Value = profitSum;
fromYellowLine = "A" + rowCnt.ToString();
toYellowLine = "D" + rowCnt.ToString();
all = fromYellowLine + ":" + toYellowLine;
excelWorkSheet.Cells[all].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
excelWorkSheet.Cells[all].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
excelWorkSheet.Cells[all].Style.HorizontalAlignment = (OfficeOpenXml.Style.ExcelHorizontalAlignment)HorizontalAlignmentStyle.Center;
excelWorkSheet.Column(1).AutoFit();
excelWorkSheet.Column(2).AutoFit();
excelWorkSheet.Column(3).AutoFit();
excelWorkSheet.Column(4).AutoFit();
excelWorkSheet.Column(5).AutoFit();
excel.SaveAs(excelFile);
}
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = true;
requiredDataElements.NeedsOverstayData = true;
requiredDataElements.NeedsEnforcementActionData = true;
this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, this._ReportParams);
this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);
OfficeOpenXml.ExcelWorksheet ws = null;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the standard report title lines
rowIdx = 1; // Excel uses 1-based indexes
colIdx = 1;
RenderCommonReportTitle(ws, this._ReportName);
// Render common report header for enforcement activity restriction filter, but only if its not for all activity
if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
{
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
}
// Render common report header for regulated hour restriction filter
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
colIdx = 1;
int hyperlinkstartRowIdx = rowIdx;
if (_ReportParams.IncludeMeterSummary == true)
{
RenderWorksheetHyperlink(ws, "Meter Enforcement", "Meter Enforcement summary");
}
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderWorksheetHyperlink(ws, "Space Enforcement", "Space Enforcement summary");
}
if (_ReportParams.IncludeAreaSummary == true)
{
RenderWorksheetHyperlink(ws, "Area Enforcement", "Area Enforcement summary");
}
if (_ReportParams.IncludeDailySummary == true)
{
RenderWorksheetHyperlink(ws, "Daily Enforcement", "Daily Enforcement summary");
}
if (_ReportParams.IncludeMonthlySummary == true)
{
RenderWorksheetHyperlink(ws, "Monthly Enforcement", "Monthly Enforcement summary");
}
if (_ReportParams.IncludeDetailRecords == true)
{
RenderWorksheetHyperlink(ws, "Details", "Enforcement details");
}
rowIdx++;
rowIdx++;
colIdx = 1;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Now start the report data summary header
RenderOverallReportSummary(ws);
// --- END OF OVERALL SUMMARY WORKSHEET ---
// Should we include a worksheet with Meter aggregates?
if (_ReportParams.IncludeMeterSummary == true)
{
RenderMeterSummaryWorksheet(pck, "Meter Enforcement");
}
// Should we include a worksheet with Space aggregates?
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderSpaceSummaryWorksheet(pck, "Space Enforcement");
}
// Should we include a worksheet with Area aggregates?
if (_ReportParams.IncludeAreaSummary == true)
{
RenderAreaSummaryWorksheet(pck, "Area Enforcement");
}
// Should we include a worksheet with Daily aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderDailySummaryWorksheet(pck, "Daily Enforcement");
}
// Should we include a worksheet with Monthly aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderMonthlySummaryWorksheet(pck, "Monthly Enforcement");
}
// Should we include a Details worksheet?
if (_ReportParams.IncludeDetailRecords == true)
{
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Details");
int detailColumnCount = 18;
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.SetValue(rowIdx, 1, "Space #");
ws.SetValue(rowIdx, 2, "Meter #");
ws.SetValue(rowIdx, 3, "Area #");
ws.SetValue(rowIdx, 4, "Area");
ws.SetValue(rowIdx, 5, "Arrival");
ws.SetValue(rowIdx, 6, "Departure");
ws.SetValue(rowIdx, 7, "Start of" + Environment.NewLine + "Overstay Violation");
ApplyWrapTextStyleToCell(ws, rowIdx, 7);
ws.SetValue(rowIdx, 8, "Overstay Violation" + Environment.NewLine + "Duration");
ApplyWrapTextStyleToCell(ws, rowIdx, 8);
ws.SetValue(rowIdx, 9, "Overstay Violation" + Environment.NewLine + "Action Taken");
ApplyWrapTextStyleToCell(ws, rowIdx, 9);
ws.SetValue(rowIdx, 10, "Overstay Violation" + Environment.NewLine + "Action Taken Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 10);
ws.SetValue(rowIdx, 11, "Overstay Rule");
ws.SetValue(rowIdx, 12, "Payment Timestamp");
ws.SetValue(rowIdx, 13, "Payment Expiration");
ws.SetValue(rowIdx, 14, "Payment Zeroed-out" + Environment.NewLine + "Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 14);
ws.SetValue(rowIdx, 15, "Start of" + Environment.NewLine + "Payment Violation");
ApplyWrapTextStyleToCell(ws, rowIdx, 15);
ws.SetValue(rowIdx, 16, "Payment Violation" + Environment.NewLine + "Duration");
ApplyWrapTextStyleToCell(ws, rowIdx, 16);
ws.SetValue(rowIdx, 17, "Payment Violation" + Environment.NewLine + "Action Taken");
ApplyWrapTextStyleToCell(ws, rowIdx, 17);
ws.SetValue(rowIdx, 18, "Payment Violation" + Environment.NewLine + "Action Taken Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 18);
Dictionary <int, List <string> > ColumnLinesForRow = new Dictionary <int, List <string> >();
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
#region Populate data for each record
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvent in this._ReportEngine.ReportDataModel.ReportableEvents)
{
// Ignore unoccupied sections or dummy sensor events
if (repEvent.SensorEvent_IsOccupied == false)
{
continue;
}
if (repEvent.IsDummySensorEvent == true)
{
continue;
}
// The details only need to list records that are involved in either payment or overstay violations (unenforceable sensor and payment events can be ignored)
if ((repEvent.PaymentVios.Count == 0) && (repEvent.Overstays.Count == 0))
{
continue;
}
// Start with fresh collections for each column's text lines for current row
for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++)
{
ColumnLinesForRow[nextCol] = new List <string>();
}
AreaAsset areaAsset = _ReportEngine.GetAreaAsset(repEvent.BayInfo.AreaID_PreferLibertyBeforeInternal);
// Output row values for data
ColumnLinesForRow[1].Add(repEvent.BayInfo.SpaceID.ToString());
ColumnLinesForRow[2].Add(repEvent.BayInfo.MeterID.ToString());
if (areaAsset != null)
{
ColumnLinesForRow[3].Add(areaAsset.AreaID.ToString());
ColumnLinesForRow[4].Add(areaAsset.AreaName);
}
ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
// Add sensor ins/outs for each "repeat" sensor event
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in repEvent.RepeatSensorEvents)
{
ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in repEvent.Overstays)
{
ColumnLinesForRow[7].Add(overstay.StartOfOverstayViolation.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[8].Add(FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits));
if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken))
{
ColumnLinesForRow[9].Add(overstay.EnforcementActionTaken);
}
else
{
ColumnLinesForRow[9].Add("");
}
if (overstay.EnforcementActionTakenTimeStamp > DateTime.MinValue)
{
ColumnLinesForRow[10].Add(overstay.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[10].Add("");
}
if (overstay.OverstayBasedOnRuleDetail != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(Enum.ToObject(typeof(DayOfWeek), overstay.OverstayBasedOnRuleDetail.DayOfWeek).ToString() + " ");
sb.Append(overstay.OverstayBasedOnRuleDetail.StartTime.ToString("hh:mm:ss tt") + " - " +
overstay.OverstayBasedOnRuleDetail.EndTime.ToString("hh:mm:ss tt") + ", ");
sb.Append(overstay.OverstayBasedOnRuleDetail.Type + ", Max Stay: " + overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString());
ColumnLinesForRow[11].Add(sb.ToString());
}
else
{
ColumnLinesForRow[11].Add("");
}
}
foreach (SensorAndPaymentReportEngine.PaymentEvent payEvent in repEvent.PaymentEvents)
{
if (payEvent.PaymentEvent_IsPaid == false)
{
continue;
}
ColumnLinesForRow[12].Add(payEvent.PaymentEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
if (payEvent.WasStoppedShortViaZeroOutTrans == true)
{
ColumnLinesForRow[13].Add(payEvent.OriginalPaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[14].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[13].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[14].Add("");
}
}
foreach (SensorAndPaymentReportEngine.PaymentVioEvent payVio in repEvent.PaymentVios)
{
ColumnLinesForRow[15].Add(payVio.StartOfPayViolation.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[16].Add(FormatTimeSpanAsHoursMinutesAndSeconds(payVio.DurationOfTimeInViolation));
if (!string.IsNullOrEmpty(payVio.EnforcementActionTaken))
{
ColumnLinesForRow[17].Add(payVio.EnforcementActionTaken);
}
else
{
ColumnLinesForRow[17].Add("");
}
if (payVio.EnforcementActionTakenTimeStamp > DateTime.MinValue)
{
ColumnLinesForRow[18].Add(payVio.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[18].Add("");
}
}
int linesForRow = 1;
for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++)
{
int columnRowLines = 0;
StringBuilder sb = new StringBuilder();
bool firstLine = true;
foreach (string nextLine in ColumnLinesForRow[nextCol])
{
columnRowLines++;
if (firstLine == false)
{
sb.AppendLine();
}
sb.Append(nextLine);
firstLine = false;
}
ws.SetValue(rowIdx, nextCol, sb.ToString());
if (columnRowLines > linesForRow)
{
linesForRow = columnRowLines;
}
if (columnRowLines > 1)
{
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, nextCol])
{
ws.Cells[rowIdx, nextCol].Style.WrapText = true;
}
}
}
// Do we need to resize the row?
if (linesForRow > 1)
{
ws.Row(rowIdx).Height = (ws.DefaultRowHeight * linesForRow);
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, detailColumnCount])
{
rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
#endregion
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount])
{
rng.AutoFilter = true;
}
// Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value)
// Column 1 & 2 are numeric integer
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 4, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 9, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 10, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 11, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 12, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 13, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 14, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 15, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 16, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 17, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 18, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
// And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text)
ws.Column(1 + 6).Width = 24;
ws.Column(1 + 7).Width = 24;
ws.Column(1 + 8).Width = 24;
ws.Column(1 + 9).Width = 27;
ws.Column(1 + 13).Width = 24;
ws.Column(1 + 14).Width = 24;
ws.Column(1 + 15).Width = 24;
ws.Column(1 + 16).Width = 24;
ws.Column(1 + 17).Width = 27;
}
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
if (!ws._values.Exists(0, address.Start.Column))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumMax + 1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws._styles.GetValue(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
break;
}
else
{
column = (ws._values.GetValue(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator <int>(ws._styles, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
cse.Value = newId;
//ws.SetStyle(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellsStoreEnumerator <int>(ws._styles, 1, 0, address._toRow, 0);
while (cse.Next())
{
for (int c = address._fromRow; c <= address._toCol; c++)
{
if (!ws._styles.Exists(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
ws.SetStyle(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws._styles.GetValue(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator <int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value;
var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws._styles.Exists(rowNum, col))
{
ws._styles.SetValue(rowNum, col, s);
}
}
}
}
ws.SetStyle(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws._styles.SetValue(rowNum, 0, newId);
ws.SetStyle(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellsStoreEnumerator <int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
cse2.Value = newId;
}
}
//Update cells with styled rows
cse2 = new CellsStoreEnumerator <int>(ws._styles, 0, 1, 0, address._toCol);
while (cse2.Next())
{
for (int r = address._fromRow; r <= address._toRow; r++)
{
if (!ws._styles.Exists(r, cse2.Column))
{
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(r, cse2.Column, newId);
}
}
}
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
var s = GetStyleId(ws, row, col);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(row, col, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(row, col, newId);
}
}
}
}
}
public string Descarga_repartoExcel(int servicio, string tipoRecibo, string cicloFacturacion, int Estado, string fecha_ini, string fecha_fin, string suministro, string medidor, int operario)
{
int _fila = 2;
string _ruta;
string nombreServicio = "";
string FileExcel = "";
int usuario = 0;
DataTable _lista = new DataTable();
try
{
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.N_ListandoReparto_Tomadas(servicio, tipoRecibo, cicloFacturacion, Estado, fecha_ini, fecha_fin, suministro, medidor, operario);
if (_lista.Rows.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
nombreServicio = usuario + "LISTADO_REPARTO.xlsx";
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("listado_reparto");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "Suministro";
oWs.Cells[1, 2].Value = "Ciclo";
oWs.Cells[1, 3].Value = "Fecha Recojo";
oWs.Cells[1, 4].Value = "Fecha Entrega";
oWs.Cells[1, 5].Value = "Unidad Lectura";
oWs.Cells[1, 6].Value = "Operario";
oWs.Cells[1, 7].Value = "Estado";
foreach (DataRow oBj in _lista.Rows)
{
oWs.Cells[_fila, 1].Value = oBj["suministro"].ToString();
oWs.Cells[_fila, 2].Value = oBj["ciclo"].ToString();
oWs.Cells[_fila, 3].Value = oBj["fecha_recojo"].ToString();
oWs.Cells[_fila, 4].Value = oBj["fecha_entrega"].ToString();
oWs.Cells[_fila, 5].Value = oBj["unidad_lectura"].ToString();
oWs.Cells[_fila, 6].Value = oBj["operario"].ToString();
oWs.Cells[_fila, 7].Value = oBj["estado"].ToString();
_fila++;
}
//------definir el tamaño de todo el documento
oWs.Cells.Style.Font.Size = 8;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 5; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public string Descarga_ResultadosReclamosExcel(string fechaini, string fechafin, int servicio)
{
int _fila = 2;
string _ruta;
string nombreServicio = "";
string FileExcel = "";
int usuario = 0;
try
{
List <Lecturas_Tomadas> _lista = new List <Lecturas_Tomadas>();
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.N_Descarga_ResultadosReclamosExcel(fechaini, fechafin, servicio);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
nombreServicio = usuario + "RECLAMOS_REPORTE.xlsx";
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Reclamos");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
oWs.Cells[1, 1].Value = "MEDIDOR";
oWs.Cells[1, 2].Value = "CUENTA CONTRATO";
oWs.Cells[1, 3].Value = "FECHA PLAN LECTURA";
oWs.Cells[1, 4].Value = "MES";
oWs.Cells[1, 5].Value = "LECTURA";
foreach (Lecturas_Tomadas oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.MEDIDOR;
oWs.Cells[_fila, 2].Value = oBj.CTA_CTO;
oWs.Cells[_fila, 3].Value = oBj.FECHA_PLAN_LECTURA;
oWs.Cells[_fila, 4].Value = oBj.MES;
oWs.Cells[_fila, 5].Value = oBj.LECTURA;
_fila++;
}
//------definir el tamaño de todo el documento
oWs.Cells.Style.Font.Size = 8;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 5; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
private void CreateHeaderPassport(ExcelWorksheet ws)
{
ws.Cells["A2:Q2"].Merge = true;
ws.Cells["A2:Q2"].Value = "Заголовок";
ws.Cells["A2:Q2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
SetStyleToAllLinesSquare(ws.Cells["A2:Q2"], ExcelBorderStyle.Thick);
FillHeaderSquare(ws.Cells["A3"], "№ п/п");
ws.Column(1).Width = 11;
FillHeaderSquare(ws.Cells["B3"], "№ поз. по спец-ии");
ws.Column(2).Width = 6;
FillHeaderSquare(ws.Cells["C3"], "Обозначение");
ws.Column(3).Width = 27;
FillHeaderSquare(ws.Cells["D3"], "Наименование");
ws.Column(4).Width = 50;
FillHeaderSquare(ws.Cells["E3"], "Профиль");
ws.Column(5).Width = 12;
FillHeaderSquare(ws.Cells["F3"], "Типоразмер");
ws.Column(6).Width = 14;
FillHeaderSquare(ws.Cells["G3"], "Мерность");
ws.Column(7).Width = 5;
FillHeaderSquare(ws.Cells["H3"], "ГОСТ на сортамент");
ws.Column(8).Width = 16;
FillHeaderSquare(ws.Cells["I3"], "Марка стали");
ws.Column(9).Width = 15;
FillHeaderSquare(ws.Cells["J3"], "ГОСТ на материал");
ws.Column(10).Width = 5;
FillHeaderSquare(ws.Cells["K3"], "Длина");
ws.Column(11).Width = 8;
FillHeaderSquare(ws.Cells["L3"], "Ширина");
ws.Column(12).Width = 8;
FillHeaderSquare(ws.Cells["M3"], "Кол-во на 1 сб. ед.");
ws.Column(13).Width = 8;
FillHeaderSquare(ws.Cells["N3"], "Кол-во всего");
ws.Column(14).Width = 8;
FillHeaderSquare(ws.Cells["O3"], "Масса на 1 ед., кг.");
ws.Column(15).Width = 14;
FillHeaderSquare(ws.Cells["P3"], "Масса всего, кг.");
ws.Column(16).Width = 14;
FillHeaderSquare(ws.Cells["Q3"], "ОТПРАВОЧНЫЕ ПОЗИЦИИ (ОП)");
ws.Column(17).Width = 9;
}
private void InsertFullTable(ExcelWorksheet ws, int headerRow, int column, DataTable table, string format)
{
ws.Cells[headerRow, column, headerRow + table.Rows.Count - 1, column + table.Columns.Count - 1].LoadFromDataTable(table, true);
if (string.IsNullOrEmpty(format))
{
//This is a bit hacky, but what can you do...
//we have to guess the format of the columns because we can't provide them directly in the template
GuessColumnFormats(ws, headerRow, column, table);
}
else
{
ws.Cells[headerRow + 1, column, headerRow + 1 + table.Rows.Count, column + table.Columns.Count].Style.Numberformat.Format = format;
}
for (int i = 0; i < table.Columns.Count; i++)
{
if (column + i == 1) continue; //We don't want to autosize the first column
ws.Column(column + i).AutoFit();
}
}
private void SetStyleFullColumn(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, Dictionary <int, int> styleCashe)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
column = (ExcelColumn)o;
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumMax + 1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
column = newColumn;
}
break;
}
else
{
column = (ws.GetValueInner(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellStoreEnumerator <ExcelValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column &&
cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellStoreEnumerator <ExcelValue>(ws._values, 1, 0, address._toRow, 0);
while (cse.Next())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (int c = address._fromCol; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
/// <summary>
/// BuildHomeroomsTable
/// </summary>
/// <param name="ws"></param>
/// <param name="Homerooms"></param>
private static void BuildHomeroomsTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByHomeroom> Homerooms)
{
ws.Column(5).Width = 12.86;
ws.Column(6).Width = 16;
ws.Column(7).Width = 6.71;
ws.Column(8).Width = 11.71;
ws.Cells[4, 5].Value = "Homerooms";
ws.Cells[4, 5].Style.Font.Bold = true;
ws.Cells[5, 5].Value = "School";
ws.Cells[5, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[5, 6].Value = "Homeroom";
ws.Cells[5, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[5, 7].Value = "Grade";
ws.Cells[5, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[5, 8].Value = "Office Visits";
ws.Cells[5, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);
for (int i = 0; i < Homerooms.Count(); i++)
{
ws.Cells[i + 6, 5].Value = Homerooms.ElementAt(i).school_name;
ws.Cells[i + 6, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[i + 6, 6].Value = Homerooms.ElementAt(i).homeroom_name;
ws.Cells[i + 6, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[i + 6, 7].Value = Homerooms.ElementAt(i).grade.ToString();
ws.Cells[i + 6, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[i + 6, 8].Value = Homerooms.ElementAt(i).total_visits;
ws.Cells[i + 6, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
//Set Header style
using (ExcelRange rng = ws.Cells[4, 5, 5 + Homerooms.Count(), 8])
{
rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);
}
}
public string GenerarArchivoExcel_data_detallado(DataTable dt_detalles, string nombreFile, string nombreExcel)
{
string _ruta = "";
string Res = "";
int _fila = 2;
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
try
{
_ruta = HttpContext.Current.Server.MapPath("~/Temp/" + nombreFile);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add(nombreExcel);
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
//for (int i = 1; i <= 8; i++)
//{
// oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
//}
oWs.Cells[1, 1].Value = "Año";
oWs.Cells[1, 2].Value = "Mes";
oWs.Cells[1, 3].Value = "Dia";
oWs.Cells[1, 4].Value = "Codigo";
oWs.Cells[1, 5].Value = "Operario";
oWs.Cells[1, 6].Value = "Total";
int ac = 0;
foreach (DataRow oBj in dt_detalles.Rows)
{
ac += 1;
//for (int j = 1; j <= 6; j++)
//{
// oWs.Cells[_fila, j].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
//}
oWs.Cells[_fila, 1].Value = oBj["anio"].ToString();
oWs.Cells[_fila, 2].Value = oBj["mes"].ToString();
oWs.Cells[_fila, 3].Value = oBj["dia"].ToString();
oWs.Cells[_fila, 4].Value = oBj["id_Operario_Lectura"].ToString();
oWs.Cells[_fila, 5].Value = oBj["operario"].ToString();
oWs.Cells[_fila, 6].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 6].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear texto
oWs.Cells[_fila, 6].Style.Numberformat.Format = "#,##0.00";
oWs.Cells[_fila, 6].Value = Convert.ToDecimal(oBj["cant_reg"]);
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int k = 1; k <= 6; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = "1|" + ruta_descarga + nombreFile;
}
catch (Exception ex)
{
Res = "0|" + ex.Message;
}
return(Res);
}
/// <summary>
/// Sets the column widths to a preset size.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
private void SetColumnWidths(ExcelWorksheet worksheet)
{
worksheet.Column(1).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 87);
worksheet.Column(2).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 93);
worksheet.Column(3).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
worksheet.Column(4).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
worksheet.Column(5).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
worksheet.Column(6).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 40);
worksheet.Column(7).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 55);
worksheet.Column(8).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 54);
worksheet.Column(9).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 61);
worksheet.Column(10).Width = ExcelHelper.Pixel2ColumnWidth(worksheet, 72);
}
public string Descarga_Excel(string __a, string __g, string __c, string __d, string __e, string __f, int __o, string sector, string zona)
{
Int32 _fila = 2;
string _servidor;
String _ruta;
string resultado = "";
string nombreServicio = "";
string FileExcel = "";
try
{
List <string> listaVar = new List <string>();
listaVar = new List <string>();
listaVar.Add(__a);
listaVar.Add(__g);
listaVar.Add(__c);
listaVar.Add(__d);
listaVar.Add(__e);
listaVar.Add(__f);
listaVar.Add(Convert.ToString(__o));
Session["listaVal"] = listaVar;
List <Lecturas_Tomadas> _lista = new List <Lecturas_Tomadas>();
NLectura obj_negocio = new DSIGE.Negocio.NLectura();
_lista = obj_negocio.NListaLecturasTomadas(
new Request_Lectura_Tomadas()
{
local = Convert.ToInt32(__a),
lista = Convert.ToString(__g),
f_ini = Convert.ToString(__c),
f_fin = Convert.ToString(__d),
suministro = Convert.ToString(__e),
medidor = Convert.ToString(__f),
operario = Convert.ToInt32(__o)
}
);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
nombreServicio = "";
if (Convert.ToInt32(__g) == 1)
{
nombreServicio = "LECTURAS_";
}
else if (Convert.ToInt32(__g) == 2)
{
nombreServicio = "RELECTURA_";
}
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
_ruta = Path.Combine(Server.MapPath("~/Temp/") + "\\" + nombreServicio + _servidor);
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + nombreServicio + _servidor;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Lecturas_tomadas");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
//marco detalle CABECERA
for (int i = 1; i <= 10; i++)
{
oWs.Cells[1, i].Style.Font.Bold = true;
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
oWs.Cells[1, i].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
}
oWs.Cells[1, 1].Value = "SUMINISTRO";
oWs.Cells[1, 2].Value = "MEDIDOR";
oWs.Cells[1, 3].Value = "LECTURA ACTUAL";
oWs.Cells[1, 4].Value = "LECTURA ANTERIOR";
oWs.Cells[1, 5].Value = "PROMEDIO DE CONSUMO";
oWs.Cells[1, 6].Value = "LECTURISTA";
oWs.Cells[1, 7].Value = "COD. OBSERVACION";
oWs.Cells[1, 8].Value = "DESC. OBSERVACION";
oWs.Cells[1, 9].Value = "FECHA LECTURA";
oWs.Cells[1, 10].Value = "TIENE FOTO";
int acu = 0;
foreach (Lecturas_Tomadas oBj in _lista)
{
//marco detalle
for (int i = 1; i <= 10; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin); //marco
}
oWs.Cells[_fila, 1].Value = oBj.Suministro_lectura;
oWs.Cells[_fila, 2].Value = oBj.Medidor_lectura;
oWs.Cells[_fila, 3].Value = oBj.Confirmacion_lectura;
oWs.Cells[_fila, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 3].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 4].Value = oBj.lectura_Anterior;
oWs.Cells[_fila, 4].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 4].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 5].Value = oBj.promedioConsumo_Lectura;
oWs.Cells[_fila, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
oWs.Cells[_fila, 5].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Right; // alinear tex
oWs.Cells[_fila, 6].Value = oBj.Operario;
oWs.Cells[_fila, 7].Value = oBj.observacion_lectura;
oWs.Cells[_fila, 8].Value = oBj.descripcion_observacion;
oWs.Cells[_fila, 9].Value = oBj.fechaLecturaMovil_Lectura;
oWs.Cells[_fila, 10].Value = oBj.tieneFoto_lectura;
_fila++;
}
oWs.Cells.Style.Font.Size = 8; //letra tamaño
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int i = 1; i <= 10; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + FileExcel, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
public string GenerarArchivoExcel_data(DataTable dt_detalles, string nombreFile, string nombreExcel)
{
string _ruta = "";
string Res = "";
int _fila = 2;
try
{
_ruta = HttpContext.Current.Server.MapPath("~/Temp/" + nombreFile);
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
ruta_descarga = ruta_descarga + nombreFile;
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add(nombreExcel);
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 9));
for (int i = 1; i <= 12; i++)
{
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
oWs.Cells[1, 1].Value = "Unidad Lectura";
oWs.Cells[1, 2].Value = "Cuenta Contrato";
oWs.Cells[1, 3].Value = "Cliente";
oWs.Cells[1, 4].Value = "Direccion";
oWs.Cells[1, 5].Value = "Urbanización";
oWs.Cells[1, 6].Value = "Distrito";
oWs.Cells[1, 7].Value = "Latitud";
oWs.Cells[1, 8].Value = "Longitud";
oWs.Cells[1, 9].Value = "Fecha";
oWs.Cells[1, 10].Value = "Hora";
oWs.Cells[1, 11].Value = "Porcion";
oWs.Cells[1, 12].Value = "Mes";
foreach (DataRow oBj in dt_detalles.Rows)
{
oWs.Cells[_fila, 1].Value = oBj["Cod_UnidadLectura"].ToString();
oWs.Cells[_fila, 2].Value = oBj["CuentaContrato_Reparto"].ToString();
oWs.Cells[_fila, 3].Value = oBj["nombreCliente_Reparto"].ToString();
oWs.Cells[_fila, 4].Value = oBj["Direccion_Reparto"].ToString();
oWs.Cells[_fila, 5].Value = oBj["urbanizacion_Reparto"].ToString();
oWs.Cells[_fila, 6].Value = oBj["distrito_Reparto"].ToString();
oWs.Cells[_fila, 7].Value = oBj["latitud_lectura"].ToString();
oWs.Cells[_fila, 8].Value = oBj["longitud_lectura"].ToString();
oWs.Cells[_fila, 9].Value = oBj["fechaRecepcion_Reparto"].ToString();
oWs.Cells[_fila, 10].Value = oBj["hora"].ToString();
oWs.Cells[_fila, 11].Value = oBj["porcion"].ToString();
oWs.Cells[_fila, 12].Value = oBj["mes"].ToString();
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int k = 1; k <= 12; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = "1|" + ruta_descarga;
}
catch (Exception ex)
{
Res = "0|" + ex.Message;
}
return(Res);
}
public ActionResult DescargaExcel(string __a)
{
Int32 _fila = 2;
String _servidor;
String _ruta;
if (__a.Length == 0)
{
return(View());
}
List <LecturaHistorico> _lista = MvcApplication._Deserialize <List <LecturaHistorico> >(__a);
_servidor = String.Format("{0:ddMMyyyy_hhmmss}.xlsx", DateTime.Now);
//_ruta = Path.Combine(Server.MapPath("/Lecturas/Temp"), _servidor);
_ruta = Path.Combine(Server.MapPath("/Temp"), _servidor);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Resumen de Lecturas");
oWs.Cells[1, 1].Value = "OPERADOR";
oWs.Cells[1, 2].Value = "TOTAL";
oWs.Cells[1, 3].Value = "REALIZADOS";
oWs.Cells[1, 4].Value = "CON FOTO";
oWs.Cells[1, 5].Value = "PENDIENTES";
oWs.Cells[1, 6].Value = "% AVANCE";
oWs.Cells[1, 7].Value = "HORA INICIO DE TRABAJO";
oWs.Cells[1, 8].Value = "HORA TERMINO DE TRABAJO";
oWs.Cells[1, 9].Value = "HORAS TRABAJADAS";
foreach (LecturaHistorico oBj in _lista)
{
oWs.Cells[_fila, 1].Value = oBj.des_ope;
oWs.Cells[_fila, 2].Value = oBj.total;
oWs.Cells[_fila, 3].Value = (oBj.realizado);
oWs.Cells[_fila, 4].Value = (oBj.conFoto);
oWs.Cells[_fila, 5].Value = (oBj.pendiente);
oWs.Cells[_fila, 6].Value = (oBj.avance);
oWs.Cells[_fila, 7].Value = (oBj.f_ini);
oWs.Cells[_fila, 8].Value = (oBj.f_fin);
oWs.Cells[_fila, 9].Value = (oBj.horas);
_fila++;
}
oWs.Row(1).Style.WrapText = true;
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
oWs.Column(1).AutoFit();
oWs.Column(2).AutoFit();
oWs.Column(3).AutoFit();
oWs.Column(4).AutoFit();
oWs.Column(5).AutoFit();
oWs.Column(6).AutoFit();
oWs.Column(7).AutoFit();
oWs.Column(8).AutoFit();
oWs.Column(9).AutoFit();
oEx.Save();
}
return(new ContentResult
{
Content = "{ \"__a\": \"" + _servidor + "\" }",
ContentType = "application/json"
});
}
private void CreateHeaderPassport(ExcelWorksheet ws, List<TechOperation> techOperations)
{
CreateHeaderPassport(ws);
for (var i = 0; i < techOperations.Count; i++)
{
var colNumInt = CountFixedField + i;
var colNumStr = GetColumnExcelName(AsciiTableStart + colNumInt);
FillHeaderSquare(ws.Cells[colNumStr + "3"], techOperations[i].ShortName);
ws.Column(colNumInt).Width = 3;
}
var rangeStr = string.Format("A2:{0}2",
GetColumnExcelName(AsciiTableStart + CountFixedField + techOperations.Count - 1));
var cell = ws.Cells[rangeStr];
cell.Merge = false;
cell.Merge = true;
cell.Value = "Заголовок";
SetCenterOrientation(cell);
SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
if (!ws._values.Exists(0, address.Start.Column))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
column = (ExcelColumn)ws._values.GetValue(0, address.Start.Column);
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if(column.ColumnMin > prevColumMax+1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin-1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws._styles.GetValue(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if(column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
break;
}
else
{
column = (ws._values.GetValue(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator<int>(ws._styles, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
cse.Value = newId;
//ws.SetStyle(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellsStoreEnumerator<int>(ws._styles, 1, 0, address._toRow, 0);
while (cse.Next())
{
for (int c = address._fromRow; c <= address._toCol; c++)
{
if (!ws._styles.Exists(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, c, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
ws.SetStyle(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws._styles.GetValue(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value;
var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws._styles.Exists(rowNum, col))
{
ws._styles.SetValue(rowNum, col, s);
}
}
}
}
ws.SetStyle(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws._styles.SetValue(rowNum, 0, newId);
ws.SetStyle(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
cse2.Value = newId;
}
}
//Update cells with styled rows
cse2 = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, address._toCol);
while (cse2.Next())
{
for (int r = address._fromRow; r <= address._toRow; r++)
{
if (!ws._styles.Exists(r, cse2.Column))
{
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(r, cse2.Column, newId);
}
}
}
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
var s = GetStyleId(ws, row, col);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(row, col, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(row, col, newId);
}
}
}
}
}
public string DescargaExcel(string fechaAsignacion, int TipoServicio)
{
int _fila = 2;
string _ruta;
string nombreArchivo = "";
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
var usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
try
{
List <Cls_Entidad_Export_trabajos_lectura> _lista = new List <Cls_Entidad_Export_trabajos_lectura>();
Cls_Negocio_Export_trabajos_lectura obj_negocio = new DSIGE.Negocio.Cls_Negocio_Export_trabajos_lectura();
_lista = obj_negocio.Capa_Negocio_Get_ListaLecturas_Excel(fechaAsignacion, TipoServicio);
if (_lista.Count == 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
if (TipoServicio == 1)
{
nombreArchivo = "LECTURAS_EXPORTADO_" + usuario + ".xls";
}
else if (TipoServicio == 2)
{
nombreArchivo = "RELECTURAS_EXPORTADO_" + usuario + ".xls";
}
else if (TipoServicio == 9)
{
nombreArchivo = "RECLAMOS_EXPORTADO_" + usuario + ".xls";
}
_ruta = Path.Combine(Server.MapPath("~/Temp") + "\\" + nombreArchivo);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Importar");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
for (int i = 1; i <= 21; i++)
{
oWs.Cells[1, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
oWs.Cells[1, i].Style.Font.Size = 9; //letra tamaño
oWs.Cells[1, i].Style.Font.Bold = true; //Letra negrita
}
oWs.Cells[1, 1].Value = "ITEM";
oWs.Cells[1, 2].Value = "NOBORAR";
oWs.Cells[1, 3].Value = "INSTALACIÓN";
oWs.Cells[1, 4].Value = "APARATO";
oWs.Cells[1, 5].Value = "TIPO CALLE";
oWs.Cells[1, 6].Value = "NOMBRE DE CALLE";
oWs.Cells[1, 7].Value = "ALTURA DE CALLE";
oWs.Cells[1, 8].Value = "NÚMERO DE EDIFICIO";
oWs.Cells[1, 9].Value = "NÚMERO DE DEPARTAMENTO";
oWs.Cells[1, 10].Value = "DETALLE CONSTRUCCIÓN (OBJETO DE CONEXIÓN)";
oWs.Cells[1, 11].Value = "CONJUNTO DE VIVIENDA (OBJETO DE CONEXIÓN)";
oWs.Cells[1, 12].Value = "MANZANA/LOTE";
oWs.Cells[1, 13].Value = "DISTRITO";
oWs.Cells[1, 14].Value = "CUENTA CONTRATO";
oWs.Cells[1, 15].Value = "SECUENCIA DE LECTURA";
oWs.Cells[1, 16].Value = "UNIDAD DE LECTURA";
oWs.Cells[1, 17].Value = "NÚMERO DE LECTURAS ESTIMADAS CONSECUTIVAS";
oWs.Cells[1, 18].Value = "EMPRESA LECTORA";
oWs.Cells[1, 19].Value = "NOTA 2 DE LA UBICACIÓN DEL APARATO";
oWs.Cells[1, 20].Value = "TECNICO";
oWs.Cells[1, 21].Value = "SECUENCIA";
int acu = 0;
foreach (Cls_Entidad_Export_trabajos_lectura oBj in _lista)
{
acu = acu + 1;
for (int i = 1; i <= 21; i++)
{
oWs.Cells[_fila, i].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
}
oWs.Cells[_fila, 1].Value = acu;
oWs.Cells[_fila, 2].Value = oBj.id_Lectura;
oWs.Cells[_fila, 3].Value = oBj.Instalacion;
//oWs.Cells[_fila, 4].Style.Numberformat.Format = "#,##0";
//oWs.Cells[_fila, 4].Value = Convert.ToDouble(oBj.Aparato);
oWs.Cells[_fila, 4].Value = oBj.Aparato;
oWs.Cells[_fila, 5].Value = oBj.Tipo_calle;
oWs.Cells[_fila, 6].Value = oBj.Nombre_Calle;
oWs.Cells[_fila, 7].Value = oBj.Altura_Calle;
oWs.Cells[_fila, 8].Value = oBj.Numero_Edificio;
oWs.Cells[_fila, 9].Value = oBj.Numero_Departamento;
oWs.Cells[_fila, 10].Value = oBj.Detalle_Construccion;
oWs.Cells[_fila, 11].Value = oBj.Conjunto_Vivienda;
oWs.Cells[_fila, 12].Value = oBj.Manzana_Lote;
oWs.Cells[_fila, 13].Value = oBj.Distrito;
oWs.Cells[_fila, 14].Value = oBj.Cuenta_contrato;
oWs.Cells[_fila, 15].Value = oBj.Secuencia_lectura;
oWs.Cells[_fila, 16].Value = oBj.Unidad_lectura;
oWs.Cells[_fila, 17].Value = oBj.Numero_lecturas_estimadas_consecutivas;
oWs.Cells[_fila, 18].Value = oBj.Empresa_Lectora;
oWs.Cells[_fila, 19].Value = oBj.Nota_2_ubicacion_aparato;
oWs.Cells[_fila, 20].Value = oBj.Tecnico;
oWs.Cells[_fila, 21].Value = oBj.Secuencia;
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Column(1).Style.Font.Bold = true;
for (int i = 1; i <= 21; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + ruta_descarga + nombreArchivo, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
private void CreateHeaderBillShift(ExcelWorksheet workSheet)
{
workSheet.PrinterSettings.Orientation = eOrientation.Portrait;
workSheet.PrinterSettings.PaperSize = ePaperSize.A4;
workSheet.Cells["B2"].Value = "Накладная №";
workSheet.Cells["B3"].Value = "Дата";
workSheet.Cells["C3"].Style.Numberformat.Format = "dd.mm.yyyy";
workSheet.Cells["A5"].Value = "Отправитель";
workSheet.Cells["A6"].Value = "Получатель";
workSheet.Cells["B2"].Style.Font.Bold = true;
workSheet.Cells["B2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["B3"].Style.Font.Bold = true;
workSheet.Cells["B3"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["A5"].Style.Font.Bold = true;
workSheet.Cells["A5"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
workSheet.Cells["A6"].Style.Font.Bold = true;
workSheet.Cells["A6"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
FillHeaderSquare(workSheet.Cells["A8"], "Наименование");
FillHeaderSquare(workSheet.Cells["B8"], "№ заказа");
FillHeaderSquare(workSheet.Cells["C8"], "№ чертежа");
FillHeaderSquare(workSheet.Cells["D8"], "Кол-во шт.");
FillHeaderSquare(workSheet.Cells["E8"], "Примечание");
workSheet.Column(1).Width = 23;
workSheet.Column(2).Width = 11;
workSheet.Column(3).Width = 25;
workSheet.Column(4).Width = 7;
workSheet.Column(5).Width = 13;
workSheet.Cells["D2"].Style.WrapText = true;
}
public string GenerarArchivoExcel_solicitud_cab(List <Solicitud_E> listDetalle, int id_usuario)
{
string Res = "";
int _fila = 4;
string FileRuta = "";
string FileExcel = "";
try
{
FileRuta = System.Web.Hosting.HostingEnvironment.MapPath("~/Archivos/Excel/" + id_usuario + "_SolicitudResguardo.xlsx");
string rutaServer = ConfigurationManager.AppSettings["Archivos"];
FileExcel = rutaServer + "Excel/" + id_usuario + "_SolicitudResguardo.xlsx";
FileInfo _file = new FileInfo(FileRuta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(FileRuta);
}
Thread.Sleep(1);
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("solicitudResguardo");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
oWs.Cells[1, 1].Style.Font.Size = 24; //letra tamaño 2
oWs.Cells[1, 1].Value = "SOLICITUDES DE RESGUARDO";
oWs.Cells[1, 1, 1, 7].Merge = true; // combinar celdaS
oWs.Cells[3, 1].Value = "NRO SOLICITUD";
oWs.Cells[3, 2].Value = "AREA ";
oWs.Cells[3, 3].Value = "SOLICITANTE";
oWs.Cells[3, 4].Value = "JEFE CUADRILLA";
oWs.Cells[3, 5].Value = "FECHA ASIGNACION";
oWs.Cells[3, 6].Value = "CANT. EFECTIVOS";
oWs.Cells[3, 7].Value = "ESTADOS";
foreach (var item in listDetalle)
{
oWs.Cells[_fila, 1].Value = item.nroSolicitud.ToString();
oWs.Cells[_fila, 2].Value = item.descripcionArea.ToString();
oWs.Cells[_fila, 3].Value = item.descripcionSolicitante.ToString();
oWs.Cells[_fila, 4].Value = item.descripcionJefeCuadrilla.ToString();
oWs.Cells[_fila, 5].Value = item.fechaAtencion.ToString();
oWs.Cells[_fila, 6].Value = item.cantidadEfectivos.ToString();
oWs.Cells[_fila, 7].Value = item.descripcionEstado.ToString();
_fila++;
}
oWs.Row(1).Style.Font.Bold = true;
oWs.Row(1).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(1).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Row(3).Style.Font.Bold = true;
oWs.Row(3).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(3).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int k = 1; k <= 7; k++)
{
oWs.Column(k).AutoFit();
}
oEx.Save();
}
Res = FileExcel;
}
catch (Exception)
{
throw;
}
return(Res);
}
private void CreateHeaderLZK(ExcelWorksheet workSheet)
{
workSheet.PrinterSettings.Orientation = eOrientation.Landscape;
workSheet.PrinterSettings.PaperSize = ePaperSize.A4;
workSheet.Cells["B2"].Value = "Заказ ";
workSheet.Cells["B2"].Style.Font.Size = 12;
workSheet.Cells["B2"].Style.Font.Bold = true;
workSheet.Cells["B3"].Value = "ЛЗК ";
FillHeaderSquare(workSheet.Cells["A7"], "№ п/п");
workSheet.Column(1).Width = 4;
FillHeaderSquare(workSheet.Cells["B7"], "Профиль");
workSheet.Column(2).Width = 18;
FillHeaderSquare(workSheet.Cells["C7"], "Типоразмер");
workSheet.Column(3).Width = 11;
FillHeaderSquare(workSheet.Cells["D7"], "Марка стали");
workSheet.Column(4).Width = 18;
FillHeaderSquare(workSheet.Cells["E7"], "ГОСТ на сортамент");
workSheet.Column(5).Width = 15;
FillHeaderSquare(workSheet.Cells["F7"], "Ед. изм.");
workSheet.Column(6).Width = 29;
FillHeaderSquare(workSheet.Cells["G7"], "Номер плавки");
FillHeaderSquare(workSheet.Cells["H7"], "Подп. клад.");
FillHeaderSquare(workSheet.Cells["I7"], "Подп. маст.");
}
public static void PopulateSheet(ExcelWorksheet worksheet, List<ReportColumn> columns, IList rows,
string tableName = "Table1", TableStyles tableStyle = TableStyles.Medium2)
{
if (columns == null)
throw new ArgumentNullException("columns");
if (rows == null)
throw new ArgumentNullException("rows");
Field[] fields = null;
TypeAccessor accessor = null;
var colCount = columns.Count;
int endCol = colCount;
int endRow = rows.Count + 1;
var header = worksheet.Cells[1, 1, 1, columns.Count];
header.LoadFromArrays(new List<object[]>
{
columns.ConvertAll(x => (x.Title ?? x.Name)).ToArray()
});
var dataList = new List<object[]>();
foreach (var obj in rows)
{
var data = new object[colCount];
var row = obj as Row;
if (row != null)
{
if (fields == null)
{
fields = new Field[colCount];
for (var i = 0; i < columns.Count; i++)
{
var n = columns[i].Name;
fields[i] = row.FindFieldByPropertyName(n) ?? row.FindField(n);
}
}
}
else if (obj != null)
{
if (accessor == null)
accessor = TypeAccessor.Create(obj.GetType());
}
for (var c = 0; c < colCount; c++)
{
if (row != null)
{
data[c] = fields[c].AsObject(row);
}
else if (obj != null)
{
data[c] = accessor[obj, columns[c].Name];
}
}
dataList.Add(data);
}
if (rows.Count > 0)
{
var dataRange = worksheet.Cells[2, 1, endRow, endCol];
dataRange.LoadFromArrays(dataList);
}
var tableRange = worksheet.Cells[1, 1, endRow, endCol];
var table = worksheet.Tables.Add(tableRange, tableName);
table.TableStyle = tableStyle;
for (var i = 1; i <= endCol; i++)
{
var column = columns[i - 1];
if (!column.Format.IsEmptyOrNull())
worksheet.Column(i).Style.Numberformat.Format = column.Format;
}
worksheet.Cells[1, 1, Math.Min(endRow, 250), endCol].AutoFitColumns(1, 100);
for (var colNum = 1; colNum <= endCol; colNum++)
{
var col = columns[colNum - 1];
var decorator = col.Decorator;
if (decorator != null)
{
for (var rowNum = 2; rowNum <= endRow; rowNum++)
{
var obj = rows[rowNum - 2];
var row = obj as Row;
decorator.Item = obj;
decorator.Name = col.Name;
decorator.Format = null;
decorator.Background = Color.Empty;
decorator.Foreground = Color.Empty;
object value;
if (row != null)
{
value = fields[colNum - 1].AsObject(row);
}
else if (obj != null)
{
value = accessor[obj, col.Name];
}
else
continue;
decorator.Value = value;
decorator.Decorate();
if (decorator.Background != Color.Empty ||
decorator.Foreground != Color.Empty ||
!Object.Equals(decorator.Value, value) ||
decorator.Format != null)
{
var cell = worksheet.Cells[rowNum, colNum];
if (decorator.Background != Color.Empty)
{
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(decorator.Background);
}
if (decorator.Foreground != Color.Empty)
cell.Style.Font.Color.SetColor(decorator.Foreground);
if (decorator.Format != null)
cell.Style.Numberformat.Format = decorator.Format;
if (!Object.Equals(decorator.Value, value))
cell.Value = decorator.Value;
}
}
}
}
}
private void CreateHeaderBigNorms(ExcelWorksheet workSheet)
{
var cell = workSheet.Cells["A6:N6"];
cell.Merge = true;
cell.Value = "";
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);
cell = workSheet.Cells["L1:N4"];
cell.Merge = true;
cell.Value = "<<Утверждаю>>\nИсполнительный директор\nООО \"ДетальКомплект\"\n______________ Воловиков В. А.";
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.WrapText = true;
FillHeaderSquare(workSheet.Cells["A7"], "№ п/п");
workSheet.Column(1).Width = 8;
FillHeaderSquare(workSheet.Cells["B7"], "Профиль");
workSheet.Column(2).Width = 11;
FillHeaderSquare(workSheet.Cells["C7"], "Типоразмер");
workSheet.Column(3).Width = 15;
FillHeaderSquare(workSheet.Cells["D7"], "Марка стали");
workSheet.Column(4).Width = 25;
FillHeaderSquare(workSheet.Cells["E7"], "ГОСТ на сортамент");
workSheet.Column(5).Width = 25;
FillHeaderSquare(workSheet.Cells["F7"], "Коэфф. использ.");
workSheet.Column(6).Width = 8;
FillHeaderSquare(workSheet.Cells["G7"], "Вес");
workSheet.Column(7).Width = 16;
FillHeaderSquare(workSheet.Cells["H7"], "Вес с отходом");
workSheet.Column(8).Width = 16;
FillHeaderSquare(workSheet.Cells["I7"], "Доп. ед. изм.");
workSheet.Column(9).Width = 14;
FillHeaderSquare(workSheet.Cells["J7"], "Доп. ед. изм. с отходом");
workSheet.Column(10).Width = 14;
FillHeaderSquare(workSheet.Cells["K7"], "Тип доп. ед. изм.");
workSheet.Column(11).Width = 14;
FillHeaderSquare(workSheet.Cells["L7"], "Примечание");
workSheet.Column(12).Width = 20;
FillHeaderSquare(workSheet.Cells["M7"], "Менеджер (сдал)");
workSheet.Column(13).Width = 10;
FillHeaderSquare(workSheet.Cells["N7"], "Мастер (принял)");
workSheet.Column(14).Width = 10;
}
public string DescargaExcel_New(int Local, string fechaAsignacion, int TipoServicio)
{
int _fila = 5;
string _ruta;
string nombreArchivo = "";
string ruta_descarga = ConfigurationManager.AppSettings["Archivos"];
var usuario = ((Sesion)Session["Session_Usuario_Acceso"]).usuario.usu_id;
try
{
DataTable dt_detalles = new DataTable();
Cls_Negocio_Export_trabajos_lectura obj_negocio = new DSIGE.Negocio.Cls_Negocio_Export_trabajos_lectura();
dt_detalles = obj_negocio.Capa_Negocio_Get_ListaCortesReconexion_Excel(Local, fechaAsignacion, TipoServicio);
if (dt_detalles.Rows.Count <= 0)
{
return(_Serialize("0|No hay informacion para mostrar.", true));
}
if (TipoServicio == 3)
{
nombreArchivo = "CORTES_EXPORTADO" + usuario + ".xls";
}
else if (TipoServicio == 4)
{
nombreArchivo = "RECONEXION_EXPORTADO" + usuario + ".xls";
}
_ruta = Path.Combine(Server.MapPath("~/Temp") + "\\" + nombreArchivo);
FileInfo _file = new FileInfo(_ruta);
if (_file.Exists)
{
_file.Delete();
_file = new FileInfo(_ruta);
}
using (Excel.ExcelPackage oEx = new Excel.ExcelPackage(_file))
{
Excel.ExcelWorksheet oWs = oEx.Workbook.Worksheets.Add("Estructura");
oWs.Cells.Style.Font.SetFromFont(new Font("Tahoma", 8));
oWs.Cells[1, 1].Value = "ESTRUCTURA LEGALIZACIÓN MASIVA DE ORDENES DE CORTES";
oWs.Cells[1, 1].Style.Font.Size = 18; //letra tamaño
oWs.Cells[1, 1, 1, 31].Merge = true; // combinar celdaS dt
oWs.Cells[1, 1].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[1, 1].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[1, 1].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 5].Value = "Actividades";
oWs.Cells[3, 5].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 5, 3, 19].Merge = true; // combinar celdaS dt
oWs.Cells[3, 5].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 5].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 5].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 21].Value = "Lecturas_Elementos";
oWs.Cells[3, 21].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 21, 3, 27].Merge = true; // combinar celdaS dt
oWs.Cells[3, 21].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 21].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 21].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 28].Value = "Tipo Comentario";
oWs.Cells[3, 28].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 28, 3, 29].Merge = true; // combinar celdaS dt
oWs.Cells[3, 28].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 28].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 28].Style.Font.Bold = true; //Letra negrita
oWs.Cells[3, 30].Value = "Fechas Legalización";
oWs.Cells[3, 30].Style.Font.Size = 10; //letra tamaño
oWs.Cells[3, 30, 3, 31].Merge = true; // combinar celdaS dt
oWs.Cells[3, 30].Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Cells[3, 30].Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
oWs.Cells[3, 30].Style.Font.Bold = true; //Letra negrita
oWs.Cells[4, 1].Value = "Orden";
oWs.Cells[4, 2].Value = "Causal";
oWs.Cells[4, 3].Value = "Personal";
oWs.Cells[4, 4].Value = "Datos_Adicionales";
oWs.Cells[4, 5].Value = "Or_Activity_id>";
oWs.Cells[4, 6].Value = "Cant Legal;";
oWs.Cells[4, 7].Value = "Nombre Atributo 1>";
oWs.Cells[4, 8].Value = "Medidor>";
oWs.Cells[4, 9].Value = "Id Componente>";
oWs.Cells[4, 10].Value = "Sello1=";
oWs.Cells[4, 11].Value = "Codigo Ubicación Sello=";
oWs.Cells[4, 12].Value = "Acción=";
oWs.Cells[4, 13].Value = "Manipulado S/N=";
oWs.Cells[4, 14].Value = "Medidor!";
oWs.Cells[4, 15].Value = "Sello2=";
oWs.Cells[4, 16].Value = "Ubicación=";
oWs.Cells[4, 17].Value = "Acción=";
oWs.Cells[4, 18].Value = "Manipulado S/N=";
oWs.Cells[4, 19].Value = "Medidor;;;";
oWs.Cells[4, 20].Value = "Items_Elementos";
oWs.Cells[4, 21].Value = "Medidor;";
oWs.Cells[4, 22].Value = "Consumo=";
oWs.Cells[4, 23].Value = "Lectura=";
oWs.Cells[4, 24].Value = "Causa=";
oWs.Cells[4, 25].Value = "Observación1=";
oWs.Cells[4, 26].Value = "Observación2=";
oWs.Cells[4, 27].Value = "Observación3";
oWs.Cells[4, 28].Value = "Código Comentario;";
oWs.Cells[4, 29].Value = "Comentario";
oWs.Cells[4, 30].Value = "Fecha Ini Ejec;";
oWs.Cells[4, 31].Value = "Fecha Fin Ejec";
int acu = 0;
foreach (DataRow oBj in dt_detalles.Rows)
{
acu = acu + 1;
oWs.Cells[_fila, 1].Value = oBj["Orden"].ToString();
oWs.Cells[_fila, 2].Value = oBj["Causal"].ToString();
oWs.Cells[_fila, 3].Value = oBj["Personal"].ToString();
oWs.Cells[_fila, 4].Value = oBj["Datos_Adicionales"].ToString();
oWs.Cells[_fila, 5].Value = oBj["Or_Activity_id"].ToString();
oWs.Cells[_fila, 6].Value = oBj["CantLegal"].ToString();
oWs.Cells[_fila, 7].Value = oBj["NombreAtributo1"].ToString();
oWs.Cells[_fila, 8].Value = oBj["Medidor1"].ToString();
oWs.Cells[_fila, 9].Value = oBj["IdComponente"].ToString();
oWs.Cells[_fila, 10].Value = oBj["Sello1"].ToString();
oWs.Cells[_fila, 11].Value = oBj["CodigoUbicacionSello"].ToString();
oWs.Cells[_fila, 12].Value = oBj["Accion1"].ToString();
oWs.Cells[_fila, 13].Value = oBj["ManipuladoS_N1"].ToString();
oWs.Cells[_fila, 14].Value = oBj["Medidor2"].ToString();
oWs.Cells[_fila, 15].Value = oBj["Sello2"].ToString();
oWs.Cells[_fila, 16].Value = oBj["Ubicacion"].ToString();
oWs.Cells[_fila, 17].Value = oBj["Accion2"].ToString();
oWs.Cells[_fila, 18].Value = oBj["ManipuladoS_N2"].ToString();
oWs.Cells[_fila, 19].Value = oBj["Medidor3"].ToString();
oWs.Cells[_fila, 20].Value = oBj["Items_Elementos"].ToString();
oWs.Cells[_fila, 21].Value = oBj["Medidor4"].ToString();
oWs.Cells[_fila, 22].Value = oBj["Consumo"].ToString();
oWs.Cells[_fila, 23].Value = oBj["Lectura"].ToString();
oWs.Cells[_fila, 24].Value = oBj["Causa"].ToString();
oWs.Cells[_fila, 25].Value = oBj["Observacion1"].ToString();
oWs.Cells[_fila, 26].Value = oBj["Observacion2"].ToString();
oWs.Cells[_fila, 27].Value = oBj["Observacion3"].ToString();
oWs.Cells[_fila, 28].Value = oBj["CodigoComentario"].ToString();
oWs.Cells[_fila, 29].Value = oBj["Comentario"].ToString();
oWs.Cells[_fila, 30].Value = oBj["FechaIniEjec"].ToString();
oWs.Cells[_fila, 31].Value = oBj["FechaFinEjec"].ToString();
_fila++;
}
oWs.Row(4).Style.Font.Bold = true;
oWs.Row(4).Style.HorizontalAlignment = Style.ExcelHorizontalAlignment.Center;
oWs.Row(4).Style.VerticalAlignment = Style.ExcelVerticalAlignment.Center;
for (int i = 1; i <= 31; i++)
{
oWs.Column(i).AutoFit();
}
oEx.Save();
}
return(_Serialize("1|" + ruta_descarga + nombreArchivo, true));
}
catch (Exception ex)
{
return(_Serialize("0|" + ex.Message, true));
}
}
private void CreateHeaderPassport(ExcelWorksheet ws, List<TechOperation> techOperations)
{
CreateHeaderPassport(ws);
for (var i = 0; i < techOperations.Count; i++)
{
var colNumInt = CountFixedField + i;
FillHeaderSquare(ws.Cells[3, colNumInt], techOperations[i].ShortName);
ws.Column(colNumInt).Width = 3;
}
ws.Cells["A2:Q2"].Merge = false;
var cell = ws.Cells[2, 1, 2, CountFixedField + techOperations.Count - 1];
cell.Merge = true;
cell.Value = "";
SetCenterOrientation(cell);
SetStyleToAllLinesSquare(cell, ExcelBorderStyle.Thick);
}
internal ExcelColumn Clone(ExcelWorksheet added, int col)
{
ExcelColumn newCol = added.Column(col);
newCol.ColumnMax = ColumnMax;
newCol.BestFit = BestFit;
newCol.Collapsed = Collapsed;
newCol.OutlineLevel = OutlineLevel;
newCol.PageBreak = PageBreak;
newCol.Phonetic = Phonetic;
newCol._styleName = _styleName;
newCol.StyleID = StyleID;
newCol.Width = Width;
newCol.Hidden = Hidden;
return newCol;
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
bool isNew;
//Get the startcolumn
object o = null;
if (!ws.ExistsValueInner(0, address.Start.Column, ref o))
{
column = ws.Column(address.Start.Column);
isNew = true;
}
else
{
//column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column);
column = (ExcelColumn)o;
isNew = false;
}
var prevColumMax = column.ColumnMax;
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMin > prevColumMax + 1)
{
var newColumn = ws.Column(prevColumMax + 1);
newColumn.ColumnMax = column.ColumnMin - 1;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws.GetStyleInner(0, column.ColumnMin);
AddNewStyleColumn(sender, e, ws, styleCashe, column, s);
//index++;
prevColumMax = column.ColumnMax;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
if (column._columnMax == address.End.Column)
{
break;
}
if (isNew)
{
column._columnMax = address.End.Column;
}
else
{
var newColumn = ws.Column(column._columnMax + 1);
newColumn.ColumnMax = address.End.Column;
AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID);
}
break;
}
else
{
column = (ws.GetValueInner(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws.GetStyleInner(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column &&
cse.Value._styleId != 0)
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, cse.Column, newId);
}
}
}
if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns))
{
//Update cells with styled columns
cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0);
while (cse.Next())
{
if (cse.Value._styleId == 0)
{
continue;
}
for (int c = address._fromRow; c <= address._toCol; c++)
{
if (!ws.ExistsStyleInner(cse.Row, c))
{
if (styleCashe.ContainsKey(cse.Value._styleId))
{
ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]);
}
else
{
ExcelXfs st = CellXfs[cse.Value._styleId];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value._styleId, newId);
ws.SetStyleInner(cse.Row, c, newId);
}
}
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
var s = ws.GetStyleInner(rowNum, 0);
if (s == 0)
{
//iterate all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value._styleId;
if (s == 0)
{
continue;
}
var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws.ExistsStyleInner(rowNum, col))
{
ws.SetStyleInner(rowNum, col, s);
}
}
}
}
ws.SetStyleInner(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(rowNum, 0, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(rowNum, 0, newId);
}
}
//Update individual cells
var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse2.Next())
{
var s = cse2.Value._styleId;
if (s == 0)
{
continue;
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(cse2.Row, cse2.Column, newId);
}
}
//Update cells with styled rows
cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol);
while (cse2.Next())
{
if (cse2.Value._styleId == 0)
{
continue;
}
for (int r = address._fromRow; r <= address._toRow; r++)
{
if (!ws.ExistsStyleInner(r, cse2.Column))
{
var s = cse2.Value._styleId;
if (styleCashe.ContainsKey(s))
{
ws.SetStyleInner(r, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyleInner(r, cse2.Column, newId);
}
}
}
}
}
else //Cellrange
{
var tmpCache = styleCashe;
var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1);
var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1);
ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column,
(List <ExcelCoreValue> list, int index, int row, int column, object args) =>
{
// Optimized GetStyleID
var s = list[index]._styleId;
if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s))
{
// get row styleId with cache
if (!rowCache.ContainsKey(row))
{
rowCache.Add(row, ws._values.GetValue(row, 0)._styleId);
}
s = rowCache[row];
if (s == 0)
{
// get column styleId with cache
if (!colCache.ContainsKey(column))
{
colCache.Add(column, ws._values.GetValue(0, column));
}
s = colCache[column]._styleId;
if (s == 0)
{
int r = 0, c = column;
if (ws._values.PrevCell(ref r, ref c))
{
//var val = ws._values.GetValue(0, c);
if (!colCache.ContainsKey(c))
{
colCache.Add(c, ws._values.GetValue(0, c));
}
var val = colCache[c];
var colObj = (ExcelColumn)(val._value);
if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174
{
s = val._styleId;
}
}
}
}
}
if (tmpCache.ContainsKey(s))
{
//ws.SetStyleInner(row, column, tmpCache[s]);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = tmpCache[s]
};
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
tmpCache.Add(s, newId);
//ws.SetStyleInner(row, column, newId);
list[index] = new ExcelCoreValue {
_value = list[index]._value, _styleId = newId
};
}
},
e);
}
}
private int SetColumnWidth(ExcelWorksheet reportSheet, int columnId, double width)
{
reportSheet.Column(columnId).Width = ExcelExt.GetTrueColumnWidth(width);
return columnId + 1;
}
private ExcelWorksheet PerformFinalFormatting(ExcelWorksheet sheet)
{
//Header
sheet.HeaderFooter.differentOddEven = false;
sheet.HeaderFooter.OddHeader.LeftAlignedText = "Virginia Tech Meeting Report: " + UnitName +
" " + TimeZoneInfo.ConvertTimeBySystemTimeZoneId(
System.DateTime.Now, TimeZoneInfo.Local.Id, "Eastern Standard Time").ToString("MMMM dd, yyyy");
//Footer
sheet.HeaderFooter.OddFooter.CenteredText =
string.Format("Page {0} of {1}",
ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
//Printing
sheet.PrinterSettings.Orientation = eOrientation.Landscape;
sheet.PrinterSettings.FitToPage = true;
sheet.PrinterSettings.FitToWidth = 1;
sheet.PrinterSettings.FitToHeight = 0;
sheet.PrinterSettings.TopMargin = 0.75M;
sheet.PrinterSettings.BottomMargin = 0.75M;
sheet.PrinterSettings.RightMargin = 0.25M;
sheet.PrinterSettings.LeftMargin = 0.25M;
sheet.PrinterSettings.RepeatRows = sheet.Cells["1:1"];
ExcelRange range_numberFormatting =
sheet.Cells[1, 5, Row, 6];
ExcelRange range_specialNumberFormatting =
sheet.Cells[1, 8, Row, 8];
ExcelRange range_newTotalNumberFormatting =
sheet.Cells[1, 9, Row, 9];
ExcelRange range_percentFormatting =
sheet.Cells[1, 10, Row, 10];
ExcelRange range_meritPercentFormatting =
sheet.Cells[1, 7, Row, 7];
ExcelRange range_comments =
sheet.Cells[1, 11, Row, 11];
//Cell styling
range_comments.Style.WrapText = true;
sheet.Cells[1, 1, Row, 1].Style.WrapText = true;
sheet.Cells[1, 2, Row, 2].Style.WrapText = true;
var numberFormat = "_($#,##0_);_($(#,##0);_($\"0\"_);_(@_)";
range_numberFormatting.Style.Numberformat.Format = numberFormat;
range_specialNumberFormatting.Style.Numberformat.Format = numberFormat;
range_newTotalNumberFormatting.Style.Numberformat.Format = numberFormat;
var percentFormat = "0.00%";
range_percentFormatting.Style.Numberformat.Format = percentFormat;
range_meritPercentFormatting.Style.Numberformat.Format = percentFormat;
sheet.Cells.AutoFitColumns();
// Widths.
sheet.Column(1).Width = 19.5;
sheet.Column(2).Width = 12.5;
sheet.Column(3).Width = 5.5;
sheet.Column(4).Width = 3.5;
sheet.Column(5).Width = 14;
sheet.Column(6).Width = 9;
sheet.Column(7).Width = 7.75;
sheet.Column(8).Width = 9;
sheet.Column(9).Width = 14;
sheet.Column(10).Width = 7.75;
sheet.Column(11).Width = 60;
// Freeze top row.
sheet.View.FreezePanes(2, 1);
if (IsExecutive)
{
sheet.Cells[1, 15, Row, 15].Style.WrapText = true;
sheet.Cells[1, 12, Row, 12].Style.Numberformat.Format = numberFormat;
sheet.Cells[1, 14, Row, 14].Style.Numberformat.Format = numberFormat;
sheet.Cells[1, 13, Row, 13].Style.Numberformat.Format = percentFormat;
sheet.Cells[1, 12, Row, 15].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
sheet.Cells[1, 12, Row, 15].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(169, 208, 142));
sheet.Column(11).Width = 40;
sheet.Column(12).Width = 9;
sheet.Column(13).Width = 7.75;
sheet.Column(14).Width = 9;
sheet.Column(15).Width = 40;
// Protection from editing data rows.
sheet.Protection.IsProtected = true;
sheet.Cells[1, 12, Row, 15].Style.Locked = false;
}
return sheet;
}
public void GetLatencyDataXLS(int PaymentCustomerId, List <int> listOfMeterIDs, DateTime StartTime, DateTime EndTime, MemoryStream ms)
{
SensingDatabaseSource Sensing = new SensingDatabaseSource(_CustomerConfig);
List <HistoricalSensingRecord> sensingData = Sensing.GetHistoricalVehicleSensingDataForMeters_StronglyTyped(_CustomerConfig.CustomerId, listOfMeterIDs, StartTime, EndTime, true);
DataSet dsSensingData = new DataSet();
DataTable dtVSData = new DataTable();
dtVSData.TableName = "All";
dtVSData.Columns.Add("MeterID", typeof(System.Int32));
dtVSData.Columns.Add("BayID", typeof(System.Int32));
dtVSData.Columns.Add("EventDateTime", typeof(System.DateTime));
dtVSData.Columns.Add("RecCreationDate", typeof(System.DateTime));
dtVSData.Columns.Add("LatencyAsSeconds", typeof(System.Int32));
dtVSData.Columns.Add("LatencyAsHMS", typeof(System.String));
dtVSData.Columns.Add("Occupied", typeof(System.Int32));
dsSensingData.Tables.Add(dtVSData);
Dictionary <string, VSLatency> sheetLatencies = new Dictionary <string, VSLatency>();
foreach (HistoricalSensingRecord nextVSData in sensingData)
{
DateTime RecCreationDate_ClientTimeZone = nextVSData.RecCreationDateTime;
// DEBUG: Not really sure if the RecCreationDateTime is stored in client or server timezone...
/*RecCreationDate_ClientTimeZone = UtilityClasses.TimeZoneInfo.ConvertTimeZoneToTimeZone(RecCreationDate_ClientTimeZone, _CustomerConfig.ServerTimeZone, _CustomerConfig.CustomerTimeZone);*/
TimeSpan latency = RecCreationDate_ClientTimeZone - nextVSData.DateTime;
VSLatency currSheetLatency = null;
// Get or create latency container for All spaces
if (sheetLatencies.ContainsKey("All"))
{
currSheetLatency = sheetLatencies["All"];
}
else
{
currSheetLatency = new VSLatency();
sheetLatencies.Add("All", currSheetLatency);
}
// Add the latency to the list
currSheetLatency.LatenciesInSeconds.Add(Convert.ToInt32(Math.Abs(latency.TotalSeconds)));
// Add info to the "All" table
DataRow dr = dtVSData.NewRow();
dtVSData.Rows.Add(dr);
dr["MeterID"] = nextVSData.MeterMappingId;
dr["BayID"] = nextVSData.BayId;
dr["EventDateTime"] = nextVSData.DateTime;
dr["RecCreationDate"] = RecCreationDate_ClientTimeZone;
dr["LatencyAsSeconds"] = Convert.ToInt32(Math.Abs(latency.TotalSeconds));
dr["LatencyAsHMS"] = Math.Abs(latency.Hours).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Minutes).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Seconds).ToString().PadLeft(2, '0') +
"." + Math.Abs(latency.Milliseconds).ToString();
dr["Occupied"] = Convert.ToInt32(nextVSData.IsOccupied);
// Then add info to a space-specific table
DataTable dtSpaceTable = null;
if (dsSensingData.Tables.IndexOf("Space" + nextVSData.BayId.ToString()) != -1)
{
dtSpaceTable = dsSensingData.Tables["Space" + nextVSData.BayId.ToString()];
}
else
{
dtSpaceTable = new DataTable();
dtSpaceTable.TableName = "Space" + nextVSData.BayId.ToString();
dtSpaceTable.Columns.Add("MeterID", typeof(System.Int32));
dtSpaceTable.Columns.Add("BayID", typeof(System.Int32));
dtSpaceTable.Columns.Add("EventDateTime", typeof(System.DateTime));
dtSpaceTable.Columns.Add("RecCreationDate", typeof(System.DateTime));
dtSpaceTable.Columns.Add("LatencyAsSeconds", typeof(System.Int32));
dtSpaceTable.Columns.Add("LatencyAsHMS", typeof(System.String));
dtSpaceTable.Columns.Add("Occupied", typeof(System.Int32));
dsSensingData.Tables.Add(dtSpaceTable);
}
// Get or create latency container for current space
if (sheetLatencies.ContainsKey("Space" + nextVSData.BayId.ToString()))
{
currSheetLatency = sheetLatencies["Space" + nextVSData.BayId.ToString()];
}
else
{
currSheetLatency = new VSLatency();
sheetLatencies.Add("Space" + nextVSData.BayId.ToString(), currSheetLatency);
}
// Add the latency to the list
currSheetLatency.LatenciesInSeconds.Add(Convert.ToInt32(Math.Abs(latency.TotalSeconds)));
dr = dtSpaceTable.NewRow();
dtSpaceTable.Rows.Add(dr);
dr["MeterID"] = nextVSData.MeterMappingId;
dr["BayID"] = nextVSData.BayId;
dr["EventDateTime"] = nextVSData.DateTime;
dr["RecCreationDate"] = RecCreationDate_ClientTimeZone;
dr["LatencyAsSeconds"] = Convert.ToInt32(Math.Abs(latency.TotalSeconds));
dr["LatencyAsHMS"] = Math.Abs(latency.Hours).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Minutes).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Seconds).ToString().PadLeft(2, '0') +
"." + Math.Abs(latency.Milliseconds).ToString();
dr["Occupied"] = Convert.ToInt32(nextVSData.IsOccupied);
}
dsSensingData.AcceptChanges();
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
foreach (DataTable nextTable in dsSensingData.Tables)
{
//Create the worksheet
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(nextTable.TableName);
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(nextTable, true);
//Format the header for column 1-3
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, nextTable.Columns.Count /*"A1:C1"*/])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
//Example how to Format Column 1 as numeric
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 1, 2 + nextTable.Rows.Count, 1])
{
col.Style.Numberformat.Format = "########0";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 2, 2 + nextTable.Rows.Count, 2])
{
col.Style.Numberformat.Format = "########0";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
ws.Column(3).Width = 20;
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 3, 2 + nextTable.Rows.Count, 3])
{
col.Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss tt";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
ws.Column(4).Width = 20;
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 4, 2 + nextTable.Rows.Count, 4])
{
col.Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss tt";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
ws.Column(5).Width = 20;
ws.Column(6).Width = 28;
// Now lets add aggregate data at the end of the sheet
if (sheetLatencies.ContainsKey(nextTable.TableName))
{
VSLatency currSheetLatenies = sheetLatencies[nextTable.TableName];
if (currSheetLatenies != null)
{
int colCount = nextTable.Columns.Count;
int rowCount = nextTable.Rows.Count;
// Average Latency
// Set cell value, then merge cells on same row
string cellValue = "Average Latency: " + currSheetLatenies.GetAverageLatency().ToString() + " seconds";
ws.Cells[rowCount + 3, 1].Value = cellValue;
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
// Minimum Latency
// Set cell value, then merge cells on same row
cellValue = "Minimum Latency: " + currSheetLatenies.GetMinLatency().ToString() + " seconds";
ws.Cells[rowCount + 4, 1].Value = cellValue;
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
// Maximum Latency
// Set cell value, then merge cells on same row
cellValue = "Maximum Latency: " + currSheetLatenies.GetMaxLatency().ToString() + " seconds";
ws.Cells[rowCount + 5, 1].Value = cellValue;
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
}
}
}
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
dsSensingData.Dispose();
}
private ExcelWorksheet CreateListView_XML(ExcelWorksheet worksheet,
int row, string fieldformat, System.Drawing.Color colFromHex, int i, ExcelConfiguration conf)
{
worksheet.Column(1).Width = 15;
worksheet.Column(2).Width = 15;
worksheet.Cells["A1"].Value = "TableName";
worksheet.Cells["B1"].Value = conf.ViewList[i].TableName;
worksheet.Cells["A2"].Value = "ViewType";
worksheet.Cells["B2"].Value = "ListView";
worksheet.Cells["A3"].Value = "ViewName";
worksheet.Cells["B3"].Value = conf.ViewList[i].ViewName;
worksheet.Cells["A4"].Value = "Title";
worksheet.Cells["B4"].Value = conf.ViewList[i].ViewTitle;
worksheet.Cells["A5"].Value = "Root Service URL";
worksheet.Cells["B5"].Value = conf.ViewList[i].RootServiceUrl;
worksheet.Cells["A6"].Value = "Order By";
worksheet.Cells["B6"].Value = conf.ViewList[i].OrderBy;
worksheet.Cells["A7"].Value = "Filter";
worksheet.Cells["B7"].Value = conf.ViewList[i].IsHasFilter;
worksheet.Cells["A8"].Value = "PageSize";
worksheet.Cells["B8"].Value = conf.ViewList[i].PageSize;
//
worksheet.Cells["A9"].Value = "EditView";
worksheet.Cells["A10"].Value = "FilterView";
worksheet.Cells["A11"].Value = "InputParameters";
worksheet.Cells["A12"].Value = "FilterParameters";
worksheet.Cells["A13"].Value = "MasterPage";
worksheet.Cells["B9"].Value = conf.ViewList[i].EditView;
worksheet.Cells["B10"].Value = conf.ViewList[i].FilterView;
worksheet.Cells["B11"].Value = conf.ViewList[i].InputParameters;
worksheet.Cells["B12"].Value = conf.ViewList[i].FilterParameters;
worksheet.Cells["B13"].Value = conf.ViewList[i].MasterPage;
//
worksheet.Cells["A14"].Value = "ButtonNew";
worksheet.Cells["B14"].Value = conf.ViewList[i].IsHasNewButton;
worksheet.Cells["C14"].Value = "Caption";
worksheet.Cells["D14"].Value = "เพิ่ม";
worksheet.Cells["E14"].Value = "AppearanceRules";
worksheet.Cells["F14"].Value = "TRUE";
worksheet.Cells["A15"].Value = "ButtonSave";
worksheet.Cells["B15"].Value = conf.ViewList[i].IsHasSaveButton;
worksheet.Cells["C15"].Value = "Caption";
worksheet.Cells["D15"].Value = "บันทึก";
worksheet.Cells["E15"].Value = "AppearanceRules";
worksheet.Cells["F15"].Value = "FALSE";
worksheet.Cells["A16"].Value = "ButtonDelete";
worksheet.Cells["B16"].Value = conf.ViewList[i].IsHasDeleteButton;
worksheet.Cells["C16"].Value = "Caption";
worksheet.Cells["D16"].Value = "ลบ";
worksheet.Cells["E16"].Value = "AppearanceRules";
worksheet.Cells["F16"].Value = "TRUE";
//
worksheet.Cells["A17"].Value = "SqlSelect";
worksheet.Cells["A18"].Value = conf.ViewList[i].SQLSelect;
worksheet.Cells["A19"].Value = "SqlUpdate";
worksheet.Cells["A20"].Value = conf.ViewList[i].SQLUpdate;
worksheet.Cells["F17"].Value = "SqlSelectParameter";
worksheet.Cells["F18"].Value = conf.ViewList[i].SQLInsertParams;
worksheet.Cells["F19"].Value = "SqlUpdateParameter";
worksheet.Cells["F20"].Value = conf.ViewList[i].SQLUpdateParams;
//
////Add the headers
worksheet.Cells["A21"].Value = "Caption";
worksheet.Cells["B21"].Value = "Column";
worksheet.Cells["C21"].Value = "Format";
worksheet.Cells["D21"].Value = "TextAlignment";
worksheet.Cells["E21"].Value = "IsHideResize";
worksheet.Cells["F21"].Value = "CustomStyle";
colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00");
worksheet.Cells["A21:F21"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells["A21:F21"].Style.Fill.BackgroundColor.SetColor(colFromHex);
row = 22;
for(int j=0;j<conf.ViewList[i].Items.Count;j++)
{
//foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows)
//{
worksheet.Cells["A" + row.ToString()].Value = conf.ViewList[i].Items[j].Caption;
worksheet.Cells["B" + row.ToString()].Value = conf.ViewList[i].Items[j].DataBinding;
worksheet.Cells["C" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
//worksheet.Cells["D" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
//worksheet.Cells["E" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
//worksheet.Cells["F" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats;
row++;
}
row--;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Name = "Tahoma";
worksheet.Cells["A1:F" + row.ToString()].Style.Font.Size = 10;
return worksheet;
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
//Get the startcolumn
ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._columns.ContainsKey(colID))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._columns[colID] as ExcelColumn;
}
var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
if (styleCashe.ContainsKey(column.StyleID))
{
column.StyleID = styleCashe[column.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(column.StyleID, newId);
column.StyleID = newId;
}
index++;
if (index >= ws._columns.Count)
{
break;
}
else
{
column = (ws._columns[index] as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
if (styleCashe.ContainsKey(newCol.StyleID))
{
newCol.StyleID = styleCashe[newCol.StyleID];
}
else
{
ExcelXfs st = CellXfs[column.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(newCol.StyleID, newId);
newCol.StyleID = newId;
}
//column._columnMax = address.End.Column;
}
//Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
foreach (ExcelCell cell in ws._cells)
{
if (cell.Column >= address.Start.Column &&
cell.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
ExcelRow row = ws.Row(rowNum);
if (row.StyleID == 0 && ws._columns.Count > 0)
{
//TODO: We should loop all columns here and change each cell. But for now we take style of column A.
foreach (ExcelColumn column in ws._columns)
{
row.StyleID = column.StyleID;
break; //Get the first one and break.
}
}
if (styleCashe.ContainsKey(row.StyleID))
{
row.StyleID = styleCashe[row.StyleID];
}
else
{
ExcelXfs st = CellXfs[row.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(row.StyleID, newId);
row.StyleID = newId;
}
}
//Get Start Cell
ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
int index = ws._cells.IndexOf(rowID);
index = ~index;
while (index < ws._cells.Count)
{
var cell = ws._cells[index] as ExcelCell;
if (cell.Row > address.End.Row)
{
break;
}
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
index++;
}
}
else //Cellrange
{
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
ExcelCell cell = ws.Cell(row, col);
if (styleCashe.ContainsKey(cell.StyleID))
{
cell.StyleID = styleCashe[cell.StyleID];
}
else
{
ExcelXfs st = CellXfs[cell.StyleID];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cell.StyleID, newId);
cell.StyleID = newId;
}
}
}
}
}
/// <summary>
/// Add an attribute metadata
/// </summary>
/// <param name="amd">Attribute metadata</param>
/// <param name="sheet">Worksheet where to write</param>
public void AddAttribute(AttributeMetadata amd, ExcelWorksheet sheet)
{
var y = 1;
if (!attributesHeaderAdded)
{
InsertAttributeHeader(sheet, lineNumber, y);
attributesHeaderAdded = true;
}
lineNumber++;
sheet.Cells[lineNumber, y].Value = (amd.LogicalName);
y++;
sheet.Cells[lineNumber, y].Value = (amd.SchemaName);
y++;
var amdDisplayName = amd.DisplayName.LocalizedLabels.FirstOrDefault(l => l.LanguageCode == settings.DisplayNamesLangugageCode);
sheet.Cells[lineNumber, y].Value = (amd.DisplayName.LocalizedLabels.Count == 0 ? "N/A" : amdDisplayName != null ? amdDisplayName.Label : "");
y++;
if (amd.AttributeType != null) sheet.Cells[lineNumber, y].Value = (amd.AttributeType.Value.ToString());
y++;
var amdDescription = amd.Description.LocalizedLabels.FirstOrDefault(l => l.LanguageCode == settings.DisplayNamesLangugageCode);
sheet.Cells[lineNumber, y].Value = (amd.Description.LocalizedLabels.Count == 0 ? "N/A" : amdDescription != null ? amdDescription.Label : "");
y++;
sheet.Cells[lineNumber, y].Value = ((amd.IsCustomAttribute != null && amd.IsCustomAttribute.Value).ToString(CultureInfo.InvariantCulture));
y++;
if (settings.AddRequiredLevelInformation)
{
sheet.Cells[lineNumber, y].Value = (amd.RequiredLevel.Value.ToString());
y++;
}
if (settings.AddValidForAdvancedFind)
{
sheet.Cells[lineNumber, y].Value = (amd.IsValidForAdvancedFind.Value.ToString(CultureInfo.InvariantCulture));
y++;
}
if (settings.AddAuditInformation)
{
sheet.Cells[lineNumber, y].Value = (amd.IsAuditEnabled.Value.ToString(CultureInfo.InvariantCulture));
y++;
}
if (settings.AddFieldSecureInformation)
{
sheet.Cells[lineNumber, y].Value = ((amd.IsSecured != null && amd.IsSecured.Value).ToString(CultureInfo.InvariantCulture));
y++;
}
if (settings.AddFormLocation)
{
var entity = settings.EntitiesToProceed.FirstOrDefault(e => e.Name == amd.EntityLogicalName);
if (entity != null)
{
foreach (var form in entity.FormsDefinitions.Where(fd => entity.Forms.Contains(fd.Id) || entity.Forms.Count == 0))
{
var formName = form.GetAttributeValue<string>("name");
var xmlDocument = new XmlDocument();
xmlDocument.LoadXml(form["formxml"].ToString());
var controlNode = xmlDocument.SelectSingleNode("//control[@datafieldname='" + amd.LogicalName + "']");
if (controlNode != null)
{
XmlNodeList sectionNodes = controlNode.SelectNodes("ancestor::section");
XmlNodeList headerNodes = controlNode.SelectNodes("ancestor::header");
XmlNodeList footerNodes = controlNode.SelectNodes("ancestor::footer");
if (sectionNodes.Count > 0)
{
if (sectionNodes[0].SelectSingleNode("labels/label[@languagecode='" + settings.DisplayNamesLangugageCode + "']") != null)
{
var sectionName = sectionNodes[0].SelectSingleNode("labels/label[@languagecode='" + settings.DisplayNamesLangugageCode + "']").Attributes["description"].Value;
XmlNode tabNode = sectionNodes[0].SelectNodes("ancestor::tab")[0];
if (tabNode != null && tabNode.SelectSingleNode("labels/label[@languagecode='" + settings.DisplayNamesLangugageCode + "']") != null)
{
var tabName = tabNode.SelectSingleNode("labels/label[@languagecode='" + settings.DisplayNamesLangugageCode + "']").Attributes["description"].Value;
if (sheet.Cells[lineNumber, y].Value != null)
{
sheet.Cells[lineNumber, y].Value = sheet.Cells[lineNumber, y].Value + "\r\n" + string.Format("{0}/{1}/{2}", formName, tabName, sectionName);
}
else
{
sheet.Cells[lineNumber, y].Value = string.Format("{0}/{1}/{2}", formName, tabName, sectionName);
}
}
}
}
else if (headerNodes.Count > 0)
{
if (sheet.Cells[lineNumber, y].Value != null)
{
sheet.Cells[lineNumber, y].Value = sheet.Cells[lineNumber, y].Value + "\r\n" + string.Format("{0}/Header", formName);
}
else
{
sheet.Cells[lineNumber, y].Value = string.Format("{0}/Header", formName);
}
}
else if (footerNodes.Count > 0)
{
if (sheet.Cells[lineNumber, y].Value != null)
{
sheet.Cells[lineNumber, y].Value = sheet.Cells[lineNumber, y].Value + "\r\n" + string.Format("{0}/Footer", formName);
}
else
{
sheet.Cells[lineNumber, y].Value = string.Format("{0}/Footer", formName);
}
}
}
}
}
sheet.Column(y).PageBreak = true;
y++;
}
sheet.Column(y).PageBreak = true;
AddAdditionalData(lineNumber, y, amd, sheet);
}
void SetColumnWidth(ExcelWorksheet ws, int columnLength)
{
ws.Column(1).Width = 20.0;
for (int i = 2; i <= columnLength; i++) {
ws.Column(i).Width = 11.0;
}
}
protected void btnDownloadSummary_Click(object sender, EventArgs e)
{
String ConnString = ConfigurationManager.ConnectionStrings["ConcordConnectionString_Report"].ConnectionString;
using (SqlConnection conn = new SqlConnection(ConnString))
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
using (SqlCommand command = new SqlCommand("DownloadSummaryReport", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@region", ddlRegion.SelectedValue);
if (!string.IsNullOrEmpty(ddlMarket.SelectedValue) && ddlMarket.SelectedValue != "-- Select --")
{
command.Parameters.AddWithValue("@market", ddlMarket.SelectedValue);
}
adapter.SelectCommand = command;
conn.Open();
adapter.Fill(dt);
}
}
conn.Close();
using (MemoryStream xlFileStream = new MemoryStream())
{
using (OfficeOpenXml.ExcelPackage xlPkg = new OfficeOpenXml.ExcelPackage())
{
int xlRowCnt = 1;
int xlColCnt = 1;
string sheetName = "Download Intake Summary";
OfficeOpenXml.ExcelWorksheet xlWrkSht1 = xlPkg.Workbook.Worksheets.Add(sheetName);
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Region";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Market";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Requestor ";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Site_ID ";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Status";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Desktop_Analysis_Request_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Los_Upload_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Prelim_Design_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Requested_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "Final_Design_Completed_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Filed_date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "PCN_Cleared_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "BOM_Sent_to_Market_Date";
xlWrkSht1.Cells[xlRowCnt, xlColCnt++].Value = "FCC_601_Filed_Date";
xlWrkSht1.Select("A1:N1");
xlWrkSht1.SelectedRange.Style.Font.Bold = true;
xlWrkSht1.SelectedRange.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
xlWrkSht1.SelectedRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#B4C6E7"));
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
xlRowCnt++;
xlWrkSht1.Cells[xlRowCnt, 1].Value = Convert.ToString(dt.Rows[i]["Region"]);
xlWrkSht1.Cells[xlRowCnt, 2].Value = Convert.ToString(dt.Rows[i]["Market"]);
xlWrkSht1.Cells[xlRowCnt, 3].Value = Convert.ToString(dt.Rows[i]["Requester"]);
xlWrkSht1.Cells[xlRowCnt, 4].Value = Convert.ToString(dt.Rows[i]["Site_ID"]);
xlWrkSht1.Cells[xlRowCnt, 5].Value = Convert.ToString(dt.Rows[i]["Status"]);
xlWrkSht1.Cells[xlRowCnt, 6].Value = ((Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != null && Convert.ToString(dt.Rows[i]["Desktop_Analysis_Request_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Desktop_Analysis_Request_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 7].Value = ((Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != null && Convert.ToString(dt.Rows[i]["Los_Upload_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Los_Upload_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 8].Value = ((Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != null && Convert.ToString(dt.Rows[i]["Prelim_Design_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Prelim_Design_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 9].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Requested_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Requested_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 10].Value = ((Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != null && Convert.ToString(dt.Rows[i]["Final_Design_Completed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["Final_Design_Completed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 11].Value = ((Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Filed_date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Filed_date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 12].Value = ((Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != null && Convert.ToString(dt.Rows[i]["PCN_Cleared_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["PCN_Cleared_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 13].Value = ((Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != null && Convert.ToString(dt.Rows[i]["BOM_Sent_to_Market_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["BOM_Sent_to_Market_Date"]).ToString("MM/dd/yyyy") : string.Empty);
xlWrkSht1.Cells[xlRowCnt, 14].Value = ((Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != null && Convert.ToString(dt.Rows[i]["FCC_601_Filed_Date"]) != "") ? Convert.ToDateTime(dt.Rows[i]["FCC_601_Filed_Date"]).ToString("MM/dd/yyyy") : string.Empty);
}
}
for (int colCnt = 1; colCnt <= xlColCnt; colCnt++)
{
xlWrkSht1.Column(colCnt).AutoFit();
}
xlWrkSht1.Column(xlColCnt).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
xlWrkSht1.Select("A1:N" + xlRowCnt);
xlWrkSht1.SelectedRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
xlWrkSht1.SelectedRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
xlPkg.SaveAs(xlFileStream);
xlWrkSht1.Dispose();
}
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=SqlExport.xlsx");
xlFileStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
private int appendnetworkCompanyToSheet(ExcelWorksheet worksheet, NetworkCompany networkCompany, int row)
{
string companyName;
if (networkCompany.Company.Name.Length > 30)
{
companyName = networkCompany.Company.Name.Remove(27) + "...";
}
else
{
companyName = networkCompany.Company.Name;
}
var headlinesColor = Color.MediumSeaGreen;
var inprogress = 0;
var finished = 0;
var total = 0;
var saved = 0;
var denied = 0;
worksheet.Cells[++row,
2].Value = "Kontaktinformationen";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row++,
2].Style.Fill.BackgroundColor.SetColor(headlinesColor);
//worksheet.Row(row++).Height = 20;
//worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
//worksheet.Cells[row, 6].Style.Numberformat.Format ="#0\\.00%";
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.Street;
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.PostalCode + " " + networkCompany.Company.Address.City;
worksheet.Cells[row++,
2].Value = networkCompany.Company.Address.Website;
worksheet.Cells[++row,
2].Value = "Ansprechpartner";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row,
2].Style.Fill.BackgroundColor.SetColor(headlinesColor);
foreach (var employee in networkCompany.Company.Employees)
{
row++;
worksheet.Cells[row++,
2].Value = employee.FirstName + " " + employee.LastName;
worksheet.Cells[row++,
2].Value = employee.Email;
worksheet.Cells[row++,
2].Value = employee.PhoneNumber;
}
if (networkCompany.Measures.Any())
{
foreach (var measure in networkCompany.Measures)
{
total = measure.EnergySavings.Aggregate(total,
(current, saving) => (int) (current + saving.Value));
if (measure.State.Title == "abgeschlossen")
{
finished++;
saved = measure.EnergySavings.Aggregate(saved,
(current, saving) => (int) (current + saving.Value));
}
if (measure.State.Title == "abgelehnt")
{
denied = measure.EnergySavings.Aggregate(denied,
(current, saving) => (int) (current + saving.Value));
}
if (measure.State.Title == "in Bearbeitung")
{
inprogress++;
}
}
total -= denied;
worksheet.Cells[++row,
2].Value = "Maßnahmen";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
2,
row,
4].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row,
2,
row,
4].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[++row,
2].Value = "Maßnahmen gesamt";
worksheet.Cells[row++,
4].Value = networkCompany.Measures.Count;
worksheet.Cells[row,
2].Value = "in Bearbeitung";
worksheet.Cells[row++,
4].Value = inprogress;
worksheet.Cells[row,
2].Value = "abgeschlossene Maßnahmen";
worksheet.Cells[row++,
4].Value = finished;
worksheet.Cells[row,
2].Value = "eingesparte Energie";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = saved;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row,
2].Value = "Potiential abgelehnter Maßnahmen";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = denied;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row,
2].Value = "gesamt Einsparungspotiential";
worksheet.Cells[row,
3].Value = "kWh";
worksheet.Cells[row,
4].Value = total;
worksheet.Cells[row++,
4].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row - 1,
6].Value = "verbleibendes Potential";
worksheet.Cells[row - 1,
8].Value = "kWh";
worksheet.Cells[row - 1,
9].Formula = new ExcelCellAddress(row - 1,
4).Address + "-" + new ExcelCellAddress(row - 3,
4).Address;
int chartrowStart;
if ((row - 15) <= 0)
{
chartrowStart = 1;
}
else
{
chartrowStart = row - 15;
}
ExcelChart chartMeasures = worksheet.Drawings.AddChart("Maßnahmen",
eChartType.Pie);
chartMeasures.Title.Text = "Maßnahmenpotential";
chartMeasures.SetPosition(chartrowStart,
0,
4,
20);
chartMeasures.SetSize(350,
280);
var data1Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 1,
9).Address;
var data2Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 2,
4).Address;
var data3Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 3,
4).Address;
var xAddress = "'" + companyName + "'!" + new ExcelCellAddress(row - 1,
6).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 2,
2).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 3,
2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 1,
3).Address);
var seriesMeasures = chartMeasures.Series.Add(data1Address + "," + data2Address + "," + data3Address,
xAddress);
seriesMeasures.HeaderAddress = legendAddress;
chartMeasures.Style = eChartStyle.Style2;
}
{
var effizienzstrom = new double[3000];
var effizienzerdgas = new double[3000];
var effizienzheizöl = new double[3000];
var effizienzfernwärme = new double[3000];
var effizienz = new double[3000];
var effiemissstrom = new double[3000];
var effiemisserdgas = new double[3000];
var effiemissheizöl = new double[3000];
var effiemissfernwärme = new double[3000];
var effiemiss = new double[3000];
var minYear = 3000;
var maxYear = 0;
var isEmpty = networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0;
if (networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0)
{
return row;
}
foreach (var survey in networkCompany.Surveys)
{
var relevantYear = true;
foreach (var reading in survey.Readings)
{
var value = reading.Value / 1000;
effizienz[survey.Date.Year] += value;
effiemiss[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
if (reading.EnergySource.Name == "electricity")
{
effizienzstrom[survey.Date.Year] += value;
effiemissstrom[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "naturalGas")
{
effizienzerdgas[survey.Date.Year] += value;
effiemisserdgas[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "fuelOil")
{
effizienzheizöl[survey.Date.Year] += value;
effiemissheizöl[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (reading.EnergySource.Name == "districtHeating")
{
effizienzfernwärme[survey.Date.Year] += value;
effiemissfernwärme[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000;
}
if (minYear > survey.Date.Year)
{
minYear = survey.Date.Year;
}
if (maxYear < survey.Date.Year)
{
maxYear = survey.Date.Year;
}
relevantYear = effizienz[survey.Date.Year] != 0;
}
if ((!relevantYear) && (minYear == survey.Date.Year))
{
minYear++;
}
if ((!relevantYear) && (maxYear == survey.Date.Year))
{
maxYear--;
}
}
row++;
var co2Offset = maxYear - minYear + 6;
var rowOffset = 0;
for (int index = minYear; index <= maxYear; index++)
{
worksheet.Cells[row + 0,
2].Value = "Energieverbrauch";
worksheet.Cells[row + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + 0,
4 + (index - minYear)].Value = index;
worksheet.Cells[row + 0,
4 + (index - minYear)].Style.Font.Bold = true;
worksheet.Cells[row + 1,
2].Value = "Strom";
worksheet.Cells[row + 1,
3].Value = "MWh";
worksheet.Cells[row + 1,
4 + (index - minYear)].Value = effizienzstrom[index];
worksheet.Cells[row + 1,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 2,
2].Value = "Erdgas";
worksheet.Cells[row + 2,
3].Value = "MWh";
worksheet.Cells[row + 2,
4 + (index - minYear)].Value = effizienzerdgas[index];
worksheet.Cells[row + 2,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 3,
2].Value = "Heizöl";
worksheet.Cells[row + 3,
3].Value = "MWh";
worksheet.Cells[row + 3,
4 + (index - minYear)].Value = effizienzheizöl[index];
worksheet.Cells[row + 3,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 4,
2].Value = "Fernwärme";
worksheet.Cells[row + 4,
3].Value = "MWh";
worksheet.Cells[row + 4,
4 + (index - minYear)].Value = effizienzfernwärme[index];
worksheet.Cells[row + 4,
4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 0,
(co2Offset + 2)].Value = "CO2-Emissionen";
worksheet.Cells[row + 0,
(co2Offset + 2)].Style.Font.Bold = true;
worksheet.Cells[row + 0,
(co2Offset + 4) + (index - minYear)].Value = index;
worksheet.Cells[row + 0,
(co2Offset + 4) + (index - minYear)].Style.Font.Bold = true;
worksheet.Cells[row + 1,
(co2Offset + 2)].Value = "CO2 Strom";
worksheet.Cells[row + 1,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 1,
(co2Offset + 4) + (index - minYear)].Value = effiemissstrom[index];
worksheet.Cells[row + 1,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 2,
(co2Offset + 2)].Value = "CO2 Erdgas";
worksheet.Cells[row + 2,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 2,
(co2Offset + 4) + (index - minYear)].Value = effiemisserdgas[index];
worksheet.Cells[row + 2,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 3,
(co2Offset + 2)].Value = "CO2 Heizöl";
worksheet.Cells[row + 3,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 3,
(co2Offset + 4) + (index - minYear)].Value = effiemissheizöl[index];
worksheet.Cells[row + 3,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 4,
(co2Offset + 2)].Value = "CO2 Fernwärme";
worksheet.Cells[row + 4,
(co2Offset + 3)].Value = "t";
worksheet.Cells[row + 4,
(co2Offset + 4) + (index - minYear)].Value = effiemissfernwärme[index];
worksheet.Cells[row + 4,
(co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
}
worksheet.Cells[row + 0,
6 + maxYear - minYear].Value = "Summe";
worksheet.Cells[row + 0,
6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[row + 0,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 0,
2,
row,
6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 0,
2,
row,
6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + 1,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 1,
4,
row + 1,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 1,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 1,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 2,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 2,
4,
row + 2,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 2,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 2,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 3,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 3,
4,
row + 3,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 3,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 3,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 4,
6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 4,
4,
row + 4,
4 + maxYear - minYear).Address);
worksheet.Cells[row + 4,
6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 4,
6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Value = "Summe";
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[row + 0,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 0,
co2Offset + 2,
row,
co2Offset + 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + 0,
co2Offset + 2,
row,
co2Offset + 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 1,
co2Offset + 4,
row + 1,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 1,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 2,
co2Offset + 4,
row + 2,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 2,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 3,
co2Offset + 4,
row + 3,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 3,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row + 4,
co2Offset + 4,
row + 4,
co2Offset + 4 + maxYear - minYear).Address);
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true;
worksheet.Cells[row + 4,
co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
row += 5;
worksheet.Cells[row,
2].Value = "Summe";
worksheet.Cells[row,
2].Style.Font.Bold = true;
worksheet.Cells[row,
3].Value = "MWh";
worksheet.Cells[row,
3].Style.Font.Bold = true;
worksheet.Cells[row,
co2Offset + 2].Value = "Summe";
worksheet.Cells[row,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row,
co2Offset + 3].Value = "t";
worksheet.Cells[row,
co2Offset + 3].Style.Font.Bold = true;
rowOffset = 18;
worksheet.Cells[++row + rowOffset + 0,
2].Value = "Entwicklung des Energieverbrauches";
worksheet.Cells[row + rowOffset + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
2,
row + 18,
4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
2,
row + 18,
4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
2].Value = "Strom";
worksheet.Cells[row + rowOffset + 2,
2].Value = "Erdgas";
worksheet.Cells[row + rowOffset + 3,
2].Value = "Heizöl";
worksheet.Cells[row + rowOffset + 4,
2].Value = "Fernwärme";
worksheet.Cells[row + rowOffset + 5,
2].Value = "Gesamt";
worksheet.Cells[row + rowOffset + 5,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Value = "Entwicklung der CO2-Emissionen";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 2].Value = "CO2 Strom";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 2].Value = "CO2 Erdgas";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 2].Value = "CO2 Heizöl";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 2].Value = "CO2 Fernwärme";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 2].Value = "CO2 Gesamt";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 2].Style.Font.Bold = true;
rowOffset = 42;
worksheet.Cells[row + rowOffset + 0,
2].Value = "Anteile am Gesamtenergieverbrauch";
worksheet.Cells[row + rowOffset + 0,
2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
2,
row + rowOffset + 0,
4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
2,
row + rowOffset + 0,
4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
2].Value = "Stromanteil";
worksheet.Cells[row + rowOffset + 2,
2].Value = "Erdgasanteil";
worksheet.Cells[row + rowOffset + 3,
2].Value = "Heizölanteil";
worksheet.Cells[row + rowOffset + 4,
2].Value = "Fernwärmeanteil";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Value = "Anteile an den Gesamtemissionen";
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row + rowOffset + 0,
co2Offset + 2,
row + rowOffset + 0,
co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 2].Value = "CO2 Stromanteil";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 2].Value = "CO2 Erdgasanteil";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 2].Value = "CO2 Heizölanteil";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 2].Value = "CO2 Fernwärmeanteil";
for (int index = minYear; index <= maxYear; index++)
{
worksheet.Cells[row - 1,
4 + index - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row - 5,
4 + index - minYear,
row - 2,
4 + index - minYear).Address);
worksheet.Cells[row - 1,
4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row - 1,
4 + index - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0";
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Formula = string.Format("Sum({0})",
new ExcelAddress(row - 5,
co2Offset + 4 + index - minYear,
row - 2,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row - 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00";
rowOffset = 18;
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 5,
4).Address,
new ExcelCellAddress(row - 5,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 4,
4).Address,
new ExcelCellAddress(row - 4,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 3,
4).Address,
new ExcelCellAddress(row - 3,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 2,
4).Address,
new ExcelCellAddress(row - 2,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 1,
4).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
4 + index - minYear].Style.Font.Bold = true;
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 5,
co2Offset + 4).Address,
new ExcelCellAddress(row - 5,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 4,
co2Offset + 4).Address,
new ExcelCellAddress(row - 4,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 3,
co2Offset + 4).Address,
new ExcelCellAddress(row - 3,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 2,
co2Offset + 4).Address,
new ExcelCellAddress(row - 2,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 5,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)",
new ExcelCellAddress(row - 1,
co2Offset + 4).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 5,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
rowOffset = 42;
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 5,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 4,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 3,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 2,
4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 5,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 1,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 4,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 2,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 3,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 3,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)",
new ExcelCellAddress(row - 2,
co2Offset + 4 + index - minYear).Address,
new ExcelCellAddress(row - 1,
co2Offset + 4 + index - minYear).Address);
worksheet.Cells[row + rowOffset + 4,
co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%";
}
ExcelChart chartOverview = worksheet.Drawings.AddChart("Entwicklung der Energieeffizienz",
eChartType.Line);
chartOverview.Title.Text = "Entwicklung der Energieeffizienz";
int chartrowStart;
int chartrowOffset;
int chartpixelOffset;
int chartsizeDelta;
if (co2Offset < 9)
{
chartrowOffset = co2Offset + 1;
chartpixelOffset = 80;
chartsizeDelta = 355 + (40 * (maxYear - minYear));
}
else
{
chartrowOffset = co2Offset + 1;
chartpixelOffset = 10;
chartsizeDelta = 315 + (40 * (maxYear - minYear));
}
if ((row - 27) <= 0)
{
chartrowStart = 1;
}
else
{
chartrowStart = row - 27;
}
chartOverview.SetPosition(chartrowStart,
0,
chartrowOffset,
chartpixelOffset);
chartOverview.SetSize(chartsizeDelta,
380);
{
var data1Address = new ExcelAddress((row + 23),
4,
(row + 23),
(4 + maxYear - minYear)).Address;
var data2Address = new ExcelAddress((row + 23),
4 + co2Offset,
(row + 23),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var series1Overview = chartOverview.Series.Add(data1Address,
xAddress);
series1Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18),
2).Address);
var series2Overview = chartOverview.Series.Add(data2Address,
xAddress);
series2Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18),
co2Offset + 2).Address);
}
chartOverview.Style = eChartStyle.Style2;
ExcelChart chartEnergieVerbrauch = worksheet.Drawings.AddChart("Energieverbrauch",
eChartType.ColumnClustered);
chartEnergieVerbrauch.Title.Text = "Energieverbrauch";
chartEnergieVerbrauch.SetPosition(row,
0,
1,
0);
chartEnergieVerbrauch.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 5 + index),
4,
(row - 5 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index),
2).Address);
var seriesEnergieVerbrauch = chartEnergieVerbrauch.Series.Add(dataAddress,
xAddress);
seriesEnergieVerbrauch.HeaderAddress = legendAddress;
}
chartEnergieVerbrauch.Style = eChartStyle.Style2;
chartEnergieVerbrauch.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
ExcelChart chartEmission = worksheet.Drawings.AddChart("CO2-Emissionen",
eChartType.ColumnClustered);
chartEmission.Title.Text = "CO2-Emissionen";
chartEmission.SetPosition(row,
0,
1 + co2Offset,
0);
chartEmission.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 5 + index),
4 + co2Offset,
(row - 5 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4 + co2Offset,
row - 6,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index),
2 + co2Offset).Address);
var seriesEmission = chartEmission.Series.Add(dataAddress,
xAddress);
seriesEmission.HeaderAddress = legendAddress;
}
chartEmission.Style = eChartStyle.Style2;
chartEmission.YAxis.Format = "#,##0 [$t];[RED] -#,##0 [$t]";
ExcelChart chartEnergieEntwicklung = worksheet.Drawings.AddChart("Energieffizienzentwicklung",
eChartType.Line);
chartEnergieEntwicklung.Title.Text = "Energieentwicklung";
chartEnergieEntwicklung.SetPosition(row + 24,
0,
1,
0);
chartEnergieEntwicklung.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row + 24 - 5 + index),
4,
(row + 24 - 5 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4,
row - 6,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index),
2).Address);
var seriesEnergieEntwicklung = chartEnergieEntwicklung.Series.Add(dataAddress,
xAddress);
seriesEnergieEntwicklung.HeaderAddress = legendAddress;
}
chartEnergieEntwicklung.Style = eChartStyle.Style2;
ExcelChart chartCO2entwicklung = worksheet.Drawings.AddChart("CO2-Emissionenentwicklung",
eChartType.Line);
chartCO2entwicklung.Title.Text = "CO2-Entwicklung";
chartCO2entwicklung.SetPosition(row + 24,
0,
1 + co2Offset,
0);
chartCO2entwicklung.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row + 24 - 5 + index),
4 + co2Offset,
(row + 24 - 5 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 6,
4 + co2Offset,
row - 6,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index),
2 + co2Offset).Address);
var seriesCO2entwicklung = chartCO2entwicklung.Series.Add(dataAddress,
xAddress);
seriesCO2entwicklung.HeaderAddress = legendAddress;
}
chartCO2entwicklung.Style = eChartStyle.Style2;
row += 47;
ExcelChart chartAnteilGesamt = worksheet.Drawings.AddChart("Anteile am Gesamtenergieverbrauch",
eChartType.ColumnStacked100);
chartAnteilGesamt.Title.Text = "Anteile am Gesamtenergieverbrauch";
//chartAnteilGesamt.Legend.Position = eLegendPosition.Right;
//chartAnteilGesamt.Legend.Add();
chartAnteilGesamt.SetPosition(row,
0,
1,
0);
chartAnteilGesamt.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 4 + index),
4,
(row - 4 + index),
(4 + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 53,
4,
row - 53,
(4 + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index),
2).Address);
var seriesAnteilGesamt = chartAnteilGesamt.Series.Add(dataAddress,
xAddress);
seriesAnteilGesamt.HeaderAddress = legendAddress;
}
//chart.DataLabel.ShowCategory = true;
//chart.DataLabel.ShowPercent = true;
//chart.DataLabel.ShowLeaderLines = true;
//chartAnteilGesamt.Legend.Remove();
chartAnteilGesamt.Style = eChartStyle.Style2;
//chartAnteilGesamt.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]";
ExcelChart chartAnteilGesEmis = worksheet.Drawings.AddChart("Anteile an den CO2-Emissionen",
eChartType.ColumnStacked100);
chartAnteilGesEmis.Title.Text = "Anteile an den CO2-Emissionen";
chartAnteilGesEmis.SetPosition(row,
0,
1 + co2Offset,
0);
chartAnteilGesEmis.SetSize(305 + (40 * (maxYear - minYear)),
315);
for (int index = 0; index < 4; index++)
{
var dataAddress = new ExcelAddress((row - 4 + index),
4 + co2Offset,
(row - 4 + index),
(4 + co2Offset + maxYear - minYear)).Address;
var xAddress = new ExcelAddress(row - 53,
4 + co2Offset,
row - 53,
(4 + co2Offset + maxYear - minYear)).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index),
2 + co2Offset).Address);
var seriesAnteilGesEmis = chartAnteilGesEmis.Series.Add(dataAddress,
xAddress);
seriesAnteilGesEmis.HeaderAddress = legendAddress;
}
chartAnteilGesEmis.Style = eChartStyle.Style2;
var pieChartsEnergyperYear = new ExcelChart[maxYear - minYear + 1];
var pieChartsCO2perYear = new ExcelChart[maxYear - minYear + 1];
for (int index = 0; index <= (maxYear - minYear); index++)
{
{
pieChartsEnergyperYear[index] = worksheet.Drawings.AddChart(("Energieverbrauchsanteile in " + (minYear + index)),
eChartType.Pie);
pieChartsEnergyperYear[index].Title.Text = "Energieverbrauchsanteile in " + (minYear + index);
pieChartsEnergyperYear[index].SetPosition(row + 17 + (17 * index),
0,
1,
0);
pieChartsEnergyperYear[index].SetSize(305 + (40 * (maxYear - minYear)),
315);
var dataAddress = new ExcelAddress(row - 4,
4 + index,
row - 1,
4 + index).Address;
var xAddress = new ExcelAddress(row - 4,
2,
row - 1,
2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5,
2).Address);
var seriespieChartEnergy = pieChartsEnergyperYear[index].Series.Add(dataAddress,
xAddress);
seriespieChartEnergy.HeaderAddress = legendAddress;
}
{
pieChartsCO2perYear[index] = worksheet.Drawings.AddChart(("CO2-Emissionsanteile in " + (minYear + index)),
eChartType.Pie);
pieChartsCO2perYear[index].Title.Text = "CO2-Emissionsanteile in " + (minYear + index);
pieChartsCO2perYear[index].SetPosition(row + 17 + (17 * index),
0,
co2Offset + 1,
0);
pieChartsCO2perYear[index].SetSize(305 + (40 * (maxYear - minYear)),
315);
var dataAddress = new ExcelAddress(row - 4,
co2Offset + 4 + index,
row - 1,
co2Offset + 4 + index).Address;
var xAddress = new ExcelAddress(row - 4,
co2Offset + 2,
row - 1,
co2Offset + 2).Address;
var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5,
co2Offset + 2).Address);
var seriespieChartCO2 = pieChartsCO2perYear[index].Series.Add(dataAddress,
xAddress);
seriespieChartCO2.HeaderAddress = legendAddress;
}
}
worksheet.Column(1).
Width = 8.43;
worksheet.Column(2).
Width = 33.57;
worksheet.Column(3).
Width = 7.43;
for (int index = 0; index <= (maxYear - minYear); index++)
{
worksheet.Column(4 + index).
Width = 10;
}
worksheet.Column(5 + maxYear - minYear).
Width = 0.83;
worksheet.Column(6 + maxYear - minYear).
Width = 9.29;
worksheet.Column(co2Offset + 1).
Width = 8.43;
worksheet.Column(co2Offset + 2).
Width = 16.43;
worksheet.Column(co2Offset + 3).
Width = 6.43;
for (int index = 0; index <= (maxYear - minYear); index++)
{
worksheet.Column(co2Offset + 4 + index).
Width = 10;
}
worksheet.Column(co2Offset + 5 + maxYear - minYear).
Width = 0.83;
worksheet.Column(co2Offset + 6 + maxYear - minYear).
Width = 10;
row += 17;
return (row);
}
}
private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary<int, int> styleCashe)
{
if (address.Start.Column == 0 || address.Start.Row == 0)
{
throw (new Exception("error address"));
}
//Columns
else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
{
ExcelColumn column;
int col = address.Start.Column, row = 0;
//Get the startcolumn
//ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
if (!ws._values.Exists(0, address.Start.Column))
{
column = ws.Column(address.Start.Column);
}
else
{
column = ws._values.GetValue(0, address.Start.Column) as ExcelColumn;
}
//var index = ws._columns.IndexOf(colID);
while (column.ColumnMin <= address.End.Column)
{
if (column.ColumnMax > address.End.Column)
{
var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
column.ColumnMax = address.End.Column;
}
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
//column.StyleID = styleCashe[s];
ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
//column.StyleID = newId;
ws.SetStyle(0, column.ColumnMin, newId);
}
//index++;
if (!ws._values.NextCell(ref row, ref col) || row > 0)
{
column._columnMax = address.End.Column;
break;
}
else
{
column = (ws._values.GetValue(0, col) as ExcelColumn);
}
}
if (column._columnMax < address.End.Column)
{
var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
newCol._columnMax = address.End.Column;
var s = ws._styles.GetValue(0, column.ColumnMin);
if (styleCashe.ContainsKey(s))
{
//newCol.StyleID = styleCashe[s];
//ws._styles.SetValue(0, column.ColumnMin, styleCashe[s]);
ws.SetStyle(0, column.ColumnMin, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
//newCol.StyleID = newId;
ws.SetStyle(0, column.ColumnMin, newId);
}
column._columnMax = address.End.Column;
}
//Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first.
var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
while (cse.Next())
{
if (cse.Column >= address.Start.Column &&
cse.Column <= address.End.Column)
{
if (styleCashe.ContainsKey(cse.Value))
{
ws.SetStyle(cse.Row, cse.Column, styleCashe[cse.Value]);
}
else
{
ExcelXfs st = CellXfs[cse.Value];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(cse.Value, newId);
//cse.Value = newId;
ws.SetStyle(cse.Row, cse.Column, newId);
}
}
}
}
//Rows
else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
{
for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
{
//ExcelRow row = ws.Row(rowNum);
var s = ws._styles.GetValue(rowNum, 0);
if (s == 0)
{
//iteratte all columns and set the row to the style of the last column
var cse = new CellsStoreEnumerator<int>(ws._styles, 0, 1, 0, ExcelPackage.MaxColumns);
while (cse.Next())
{
s = cse.Value;
var c = ws._values.GetValue(cse.Row, cse.Column) as ExcelColumn;
if (c != null && c.ColumnMax < ExcelPackage.MaxColumns)
{
for (int col = c.ColumnMin; col < c.ColumnMax; col++)
{
if (!ws._styles.Exists(rowNum, col))
{
ws._styles.SetValue(rowNum, col, s);
}
}
}
}
ws.SetStyle(rowNum, 0, s);
cse.Dispose();
}
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(rowNum, 0, styleCashe[s]);
//row.StyleID = styleCashe[s];
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws._styles.SetValue(rowNum, 0, newId);
ws.SetStyle(rowNum, 0, newId);
}
}
//Get Start Cell
//ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
//int index = ws._cells.IndexOf(rowID);
//index = ~index;
var cse2 = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
//while (index < ws._cells.Count)
while (cse2.Next())
{
//var cell = ws._cells[index] as ExcelCell;
//if(cell.Row > address.End.Row)
//{
// break;
//}
var s = cse2.Value;
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(cse2.Row, cse2.Column, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
cse2.Value = newId;
ws.SetStyle(cse2.Row, cse2.Column, newId);
}
}
}
else //Cellrange
{
//var cse = new CellsStoreEnumerator<int>(ws._styles, address._fromRow, address._fromCol, address._toRow, address._toCol);
//while(cse.Next())
for (int col = address.Start.Column; col <= address.End.Column; col++)
{
for (int row = address.Start.Row; row <= address.End.Row; row++)
{
//ExcelCell cell = ws.Cell(row, col);
//int s = ws._styles.GetValue(row, col);
var s = GetStyleId(ws, row, col);
if (styleCashe.ContainsKey(s))
{
ws.SetStyle(row, col, styleCashe[s]);
}
else
{
ExcelXfs st = CellXfs[s];
int newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
styleCashe.Add(s, newId);
ws.SetStyle(row, col, newId);
}
}
}
}
}
/// <summary>
/// Copies the current column to a new worksheet
/// </summary>
/// <param name="added">The worksheet where the copy will be created</param>
internal void Clone(ExcelWorksheet added)
{
ExcelColumn newCol = added.Column(ColumnMin);
newCol.ColumnMax = ColumnMax;
newCol.BestFit = BestFit;
newCol.Collapsed = Collapsed;
newCol.Hidden = Hidden;
newCol.OutlineLevel = OutlineLevel;
newCol.PageBreak = PageBreak;
newCol.Phonetic = Phonetic;
newCol.StyleName = StyleName;
newCol.StyleID = StyleID;
newCol.Width = Width;
}