/// <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; }