/// <summary>
/// Appends the logo to specified worksheet.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <param name="model">Logo model.</param>
public static void AppendLogo(this ExcelWorksheet worksheet, LogoModel model)
{
SentinelHelper.ArgumentNull(model);
SentinelHelper.ArgumentNull(worksheet);
if (model.IsDefault)
{
return;
}
if (model.Show == YesNo.No)
{
return;
}
var logoPosition = model.Location;
var positionType = logoPosition.LocationType;
if (positionType == KnownElementLocation.ByAlignment)
{
return;
}
var found = model.Image.TryGetImage(out var image);
if (!found)
{
return;
}
var logoWidth =
model.LogoSize.Width == -1
? image.Width
: model.LogoSize.Width;
var logoHeight =
model.LogoSize.Height == -1
? image.Height
: model.LogoSize.Height;
var logoNameBuilder = new StringBuilder();
var coordenates = (CoordenatesModel)logoPosition.Mode;
logoNameBuilder.Append("logo");
logoNameBuilder.Append(coordenates.TableCoordenates.X);
logoNameBuilder.Append(coordenates.TableCoordenates.Y);
worksheet.Row(coordenates.TableCoordenates.Y).Height = (image.Height - 1) * 0.75d;
var logo = worksheet.Drawings.AddPicture(logoNameBuilder.ToString(), image);
logo.Locked = true;
logo.SetSize(logoWidth, logoHeight);
logo.SetPosition(
coordenates.TableCoordenates.Y - 1,
0,
coordenates.TableCoordenates.X - 1,
0);
}
public static double RowsUsedHeight(this ExcelWorksheet ws)
{
double h = 0;
for (var i = 1; i <= ws.RowCount(); i++)
{
h += ws.Row(i).Height;
}
return(h);
}
public FileResult Export(string franchiseStore, string orderNo, string cYBNo, string pickupDate, string recipient, string signatory, string signDate)
{
var list = _orderZWYSService.GetAllOrderZWYSs(franchiseStore, orderNo, cYBNo, pickupDate, recipient, signatory, signDate);
var path = _hostEnvironment.ContentRootPath + "/TempFiles/zwys_model.xlsx";
var file = new FileInfo(path);
using (var package = new ExcelPackage(file))
{
OfficeOpenXml.ExcelWorksheet sheet = package.Workbook.Worksheets[0];
#region write content
int pos = 2;
int index = 1;
foreach (var item in list)
{
sheet.Row(pos).Height = 20;//设置行高
sheet.Cells[pos, 1].Value = index;
sheet.Cells[pos, 2].Value = Convert.ToDateTime(item.PickupDate).ToString("yyyy-MM-dd");
sheet.Cells[pos, 3].Value = item.OrderNo;
sheet.Cells[pos, 4].Value = item.CYBNo;
sheet.Cells[pos, 5].Value = item.FranchiseStore;
sheet.Cells[pos, 6].Value = item.StoreStar;
sheet.Cells[pos, 7].Value = item.Recipient;
sheet.Cells[pos, 8].Value = item.ProvinceName;
sheet.Cells[pos, 9].Value = item.CityName;
sheet.Cells[pos, 10].Value = item.AreaName;
sheet.Cells[pos, 11].Value = item.CityLevel;
sheet.Cells[pos, 12].Value = item.Phone;
sheet.Cells[pos, 13].Value = item.Address;
sheet.Cells[pos, 14].Value = item.Pieces;
sheet.Cells[pos, 15].Value = item.Signatory;
sheet.Cells[pos, 16].Value = Convert.ToDateTime(item.SignDate).ToString("yyyy-MM-dd");
sheet.Cells[pos, 17].Value = item.Remark;
sheet.Cells[pos, 18].Value = Convert.ToBoolean(item.SettleState) ? "是" : "否";
using (ExcelRange range = sheet.Cells[pos, 1, pos, 18])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
pos++;
index++;
}
#endregion
var fileName = "植物医生" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
return(File(package.GetAsByteArray(), AppConfigurtaion.Configuration["FileContentType"], fileName));
}
}
/// <summary>
/// Copies the current row to a new worksheet
/// </summary>
/// <param name="added">The worksheet where the copy will be created</param>
internal void Clone(ExcelWorksheet added)
{
ExcelRow newRow = added.Row(Row);
newRow.Collapsed = Collapsed;
newRow.Height = Height;
newRow.Hidden = Hidden;
newRow.OutlineLevel = OutlineLevel;
newRow.PageBreak = PageBreak;
newRow.Phonetic = Phonetic;
newRow._styleName = _styleName;
newRow.StyleID = StyleID;
}
private static void DrawAwb(string currentAwb, ExcelWorksheet excel, int iRow, int count)
{
var row = excel.Row(iRow);
row.Height = ExcelConstants.AwbRowHeight;
row.Style.Font.Bold = true;
excel.Cells[iRow, 1].Value = currentAwb;
var range = excel.Cells[iRow, 1, iRow, count];
range.Merge = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
public static void AddDataRow(ExcelWorksheet worksheet, int row, IEnumerable<object> values)
{
int ca = 0;
foreach (object v in values)
{
ca++;
using (ExcelRange cell = worksheet.Cells[row, ca])
{
object value = v;
cell.Value = value;
}
}
worksheet.Row(row).Height = 10.2;
}
private XmlElement GetOrCreateCellElement(ExcelWorksheet xlWorksheet, int row, int col)
{
XmlElement cellNode = null;
// this will create the row if it does not already exist
XmlNode rowNode = xlWorksheet.Row(row).Node;
if (rowNode != null)
{
cellNode = (XmlElement)rowNode.SelectSingleNode(string.Format("./d:c[@" + ExcelWorksheet.tempColumnNumberTag + "='{0}']", col), _xlWorksheet.NameSpaceManager);
if (cellNode == null)
{
// Didn't find the cell so create the cell element
cellNode = xlWorksheet.WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
cellNode.SetAttribute(ExcelWorksheet.tempColumnNumberTag, col.ToString());
// You must insert the new cell at the correct location.
// Loop through the children, looking for the first cell that is
// beyond the cell you're trying to insert. Insert before that cell.
XmlNode biggerNode = null;
XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", _xlWorksheet.NameSpaceManager);
if (cellNodes != null)
{
foreach (XmlNode node in cellNodes)
{
XmlNode colNode = node.Attributes[ExcelWorksheet.tempColumnNumberTag];
if (colNode != null)
{
int colFound = Convert.ToInt32(colNode.Value);
if (colFound > col)
{
biggerNode = node;
break;
}
}
}
}
if (biggerNode == null)
{
rowNode.AppendChild(cellNode);
}
else
{
rowNode.InsertBefore(cellNode, biggerNode);
}
}
}
return(cellNode);
}
private static int DrawInfo(ExcelWorksheet ws, SenderCalculationAwbInfo info, int iRow, int count)
{
var range = ws.Cells[iRow, 1, iRow, count];
range.Merge = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "sender";
ws.Cells[iRow, 2].Value = info.CostPerKgOfSender;
ws.Cells[iRow, 10].Value = info.TotalSenderRate;
ws.Cells[iRow, 11].Value = info.TotalScotchCost;
ws.Cells[iRow, 12].Value = info.TotalFactureCost;
ws.Cells[iRow, 13].Value = info.TotalFactureCostEx;
ws.Cells[iRow, 14].Value = info.TotalPickupCost;
ws.Cells[iRow, 15].Value = info.TotalOfSender;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
ws.Cells[iRow, 1, iRow, 14].Style.Font.Bold = true;
iRow++;
ws.Cells[iRow, 1].Value = "flight";
ws.Cells[iRow, 2].Value = info.FlightCostPerKg;
ws.Cells[iRow, 15].Value = info.FlightCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
ws.Cells[iRow, 1, iRow, 14].Style.Font.Bold = true;
iRow++;
ws.Cells[iRow, 1].Value = "cost total";
ws.Cells[iRow, 15].Value = info.Total;
var rangeCost = ws.Cells[iRow, 1, iRow, count];
rangeCost.Style.Fill.PatternType = ExcelFillStyle.Solid;
rangeCost.Style.Fill.BackgroundColor.SetColor(Color.HotPink);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
ws.Cells[iRow, 1, iRow, 15].Style.Font.Bold = true;
iRow++;
return iRow;
}
void SetStyling(ExcelWorksheet ws)
{
ws.Cells.Style.Font.Size = 11.0f;
ws.Row(1).Style.Font.Bold = true;
ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
private static void LineupsGenerateSheetPerPeriodPerTeam(Color blue1, Color blue2, Color blueDark, Color offPink, ExcelWorksheet reportSheet, int period, int row)
{
try
{
reportSheet.Row(row).Height = 30;
int doubleColumn = 0;
reportSheet.Cells[row, 1 + doubleColumn, row + 1, 8 + doubleColumn].SetValue("Home Team").Merge().SetBackgroundColor(blue1).SetFontSize(14).SetCenterAlign();
//line up
reportSheet.Cells[row, 9 + doubleColumn, row, 14 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 15 + doubleColumn, row, 17 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin).SetBackgroundColor(blue1);
reportSheet.Cells[row, 18 + doubleColumn].SetBackgroundColor(blueDark).SetValue(period.ToString()).SetFontSize(24).SetCenterAlign().SetFontBold().SetFontColor(Color.White);
reportSheet.Cells[row, 21 + doubleColumn, row, 22 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 23 + doubleColumn, row + 1, 30 + doubleColumn].SetValue("Away Team").Merge().SetBackgroundColor(blue1).SetFontSize(14).SetCenterAlign();
//line up
reportSheet.Cells[row, 31 + doubleColumn, row, 36 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
//date
reportSheet.Cells[row, 37 + doubleColumn, row, 39 + doubleColumn].Merge().SetBackgroundColor(blue1).SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 40 + doubleColumn].SetBackgroundColor(blueDark).SetValue(period.ToString()).SetFontSize(24).SetFontBold().SetCenterAlign().SetFontColor(Color.White);
reportSheet.Cells[row, 43 + doubleColumn, row, 44 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
row += 1; //2
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 9 + doubleColumn, row, 14 + doubleColumn].Merge().SetValue("Lineup Tracker").SetCenterAlign();
reportSheet.Cells[row, 15 + doubleColumn, row, 17 + doubleColumn].SetBackgroundColor(blue1).SetValue("Date").SetCenterAlign().Merge();
reportSheet.Cells[row, 18 + doubleColumn].SetBackgroundColor(blueDark);
reportSheet.Cells[row, 21 + doubleColumn, row, 22 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
reportSheet.Cells[row, 31 + doubleColumn, row, 36 + doubleColumn].Merge().SetValue("Lineup Tracker").SetCenterAlign();
reportSheet.Cells[row, 37 + doubleColumn, row, 39 + doubleColumn].Merge().SetBackgroundColor(blue1).SetValue("Date").SetCenterAlign();
reportSheet.Cells[row, 40 + doubleColumn].SetBackgroundColor(blueDark);
reportSheet.Cells[row, 43 + doubleColumn, row, 44 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
int secondSheetStart = 22;
row += 1; //3
reportSheet.Row(row).Height = 15;
LineupsSetHeaderRow(reportSheet, row, doubleColumn);
LineupsSetHeaderRow(reportSheet, row, secondSheetStart);
row += 1;
int modulation = 1;
for (int i = row; i < row + 76; i += 2)
{
reportSheet.Row(i).Height = 14.25;
reportSheet.Row(i + 1).Height = 14.25;
LineupsDisplayRow(blue1, blue2, offPink, reportSheet, i, doubleColumn, modulation % 2 == 0);
LineupsDisplayRow(blue1, blue2, offPink, reportSheet, i, secondSheetStart, modulation % 2 == 0);
modulation += 1;
}
row += 75;
row += 1;//80
LineupsBottomMessages(reportSheet, row, doubleColumn);
LineupsBottomMessages(reportSheet, row, secondSheetStart);
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
private static int DrawHeader(ExcelWorksheet excel, decimal balance)
{
var iColumn = 1;
const int iRow = 2;
excel.Cells[iRow, iColumn++].Value = Entities.Client;
excel.Cells[iRow, iColumn++].Value = Entities.DisplayNumber;
excel.Cells[iRow, iColumn++].Value = Entities.FactoryName;
excel.Cells[iRow, iColumn++].Value = Entities.Mark;
excel.Cells[iRow, iColumn++].Value = Entities.Class;
excel.Cells[iRow, iColumn++].Value = Entities.Count;
excel.Cells[iRow, iColumn++].Value = Entities.Weight;
excel.Cells[iRow, iColumn++].Value = Entities.Invoice;
excel.Cells[iRow, iColumn++].Value = Entities.Value;
excel.Cells[iRow, iColumn++].Value = Entities.TariffPerKg;
excel.Cells[iRow, iColumn++].Value = Entities.TotalTariffCost;
excel.Cells[iRow, iColumn++].Value = Entities.ScotchCost;
excel.Cells[iRow, iColumn++].Value = Entities.Insurance;
excel.Cells[iRow, iColumn++].Value = Entities.FactureCost;
excel.Cells[iRow, iColumn++].Value = Entities.FactureCostEx;
excel.Cells[iRow, iColumn++].Value = Entities.PickupCost;
excel.Cells[iRow, iColumn++].Value = Entities.TransitCost;
excel.Cells[iRow, iColumn].Value = Entities.Total;
excel.Cells[iRow, iColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
excel.Cells[1, iColumn].Value = Entities.Balance;
excel.Cells[1, iColumn].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
var balanceCell = excel.Cells[1, iColumn + 1];
balanceCell.Value = balance.ToString("N2") + CurrencyName.Euro;
balanceCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
if(balance < 0)
{
balanceCell.Style.Font.Color.SetColor(Color.Red);
}
var headerCells = excel.Cells[1, 1, iRow, iColumn + 1];
headerCells.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
headerCells.Style.Font.Bold = true;
excel.View.FreezePanes(3, 1);
excel.Row(1).Height = ExcelConstants.DefaultRowHeight;
excel.Row(2).Height = ExcelConstants.DefaultRowHeight;
return iColumn;
}
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);
}
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;
}
}
}
}
}
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"
});
}
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());
}
public FileResult Export(string projectCode, string orderNo, string carNo, string sealNo, string departure, string terminal, string orderDate)
{
var list = _orderJDService.GetAllOrderJDs(projectCode, orderNo, carNo, sealNo, departure, terminal, orderDate);
var path = _hostEnvironment.ContentRootPath + "/TempFiles/jd_model.xlsx";
var file = new FileInfo(path);
using (var package = new ExcelPackage(file))
{
OfficeOpenXml.ExcelWorksheet sheet = package.Workbook.Worksheets[0];
#region write content
int pos = 2;
int index = 1;
foreach (var item in list)
{
sheet.Row(pos).Height = 20;//设置行高
sheet.Cells[pos, 1].Value = index;
sheet.Cells[pos, 2].Value = item.ProjectName;
sheet.Cells[pos, 3].Value = Convert.ToDateTime(item.OrderDate).ToString("yyyy-MM-dd");
sheet.Cells[pos, 4].Value = item.StartTime;
sheet.Cells[pos, 5].Value = item.EndTime;
sheet.Cells[pos, 6].Value = item.OrderNo;
sheet.Cells[pos, 7].Value = item.SealNo;
sheet.Cells[pos, 8].Value = item.CarNo;
sheet.Cells[pos, 9].Value = item.Driver;
sheet.Cells[pos, 10].Value = item.Departure;
sheet.Cells[pos, 11].Value = item.Terminal;
sheet.Cells[pos, 12].Value = item.CarTypeName;
sheet.Cells[pos, 13].Value = item.Beats;
sheet.Cells[pos, 14].Value = item.Mileage;
sheet.Cells[pos, 15].Value = item.HighspeedCharge;
sheet.Cells[pos, 16].Value = item.Cost;
sheet.Cells[pos, 17].Value = item.Rebate;
sheet.Cells[pos, 18].Value = item.Freight;
sheet.Cells[pos, 19].Value = item.OilCardBalance;
sheet.Cells[pos, 20].Value = item.Kilometers;
sheet.Cells[pos, 21].Value = item.OilCardTopup;
sheet.Cells[pos, 22].Value = item.OilCardUse;
sheet.Cells[pos, 23].Value = item.Repairfee;
sheet.Cells[pos, 24].Value = item.Fine;
sheet.Cells[pos, 25].Value = item.SuTongCard;
sheet.Cells[pos, 26].Value = item.Paycash;
sheet.Cells[pos, 27].Value = Convert.ToBoolean(item.SettleState) ? "是" : "否";
using (ExcelRange range = sheet.Cells[pos, 1, pos, 27])
{
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Color.SetColor(Color.Black);
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
pos++;
index++;
}
#endregion
var fileName = "燕翔外仓车辆明细" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
return(File(package.GetAsByteArray(), AppConfigurtaion.Configuration["FileContentType"], fileName));
}
}
/// <summary>
/// 添加表头
/// </summary>
/// <param name="worksheet">工作簿</param>
/// <param name="title">标题</param>
private static void AddTitle(ExcelWorksheet worksheet, string title)
{
worksheet.Cells["B2:F2"].Merge = true;
worksheet.Cells["B2"].Value = title;
worksheet.Cells["B2"].Style.Font.Bold = true;
worksheet.Cells["B2"].Style.Font.Name = "宋体";
worksheet.Cells["B2"].Style.Font.Size = 14;
worksheet.Row(2).Height = 19;
worksheet.Cells["B2"].Style.Indent = 8;
worksheet.Cells["B2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms,
ActivityRestrictions activityRestriction, string scopedAreaName, string scopedMeter)
{
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
this._ActivityRestriction = activityRestriction;
this.GatherReportData(listOfMeterIDs);
OfficeOpenXml.ExcelWorksheet ws = null;
int rowIdx = -1;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.Cells[rowIdx, 1].Value = "Asset Listings Report";
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 10])
{
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
rng.Style.Font.Italic = true;
rng.Style.Font.Size = 22;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
rowIdx++;
ws.Cells[rowIdx, 1].IsRichText = true;
OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Client: ", this._CustomerConfig.CustomerName);
AddRichTextNameAndValue(rtfCollection, ", Generated: ", NowAtDestination.ToShortDateString() + " " + NowAtDestination.ToShortTimeString());
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Included Activity: ", "Asset Listing");
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
if (!string.IsNullOrEmpty(scopedAreaName))
{
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Report limited to area: ", scopedAreaName);
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
}
if (!string.IsNullOrEmpty(scopedMeter))
{
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Report limited to meter: ", scopedMeter);
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
}
rowIdx++;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, 10])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
int hyperlinkstartRowIdx = rowIdx;
rowIdx++;
rowIdx++;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, 13])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Render the header row
rowIdx = 7; // Excel uses 1-based indexes
// have to start at column 2, does not work when start column is 1. Will come back when more time is avail
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 2, rowIdx, 6])
{
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
}
ws.Cells[rowIdx, 2].Value = "Site Details";
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 6])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 7, rowIdx, 13])
{
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237));
}
ws.Cells[rowIdx, 7].Value = "Regulations";
rowIdx++;
ws.Cells[rowIdx, 1].Value = "Meter ID";
ws.Cells[rowIdx, 2].Value = "Space ID";
ws.Cells[rowIdx, 3].Value = "Area #";
ws.Cells[rowIdx, 4].Value = "Site Details Area";
ws.Cells[rowIdx, 5].Value = "Co-Ordinates Lat";
ws.Cells[rowIdx, 6].Value = "Co-Ordinates Long";
ws.Cells[rowIdx, 7].Value = "Regulations - Sun";
ws.Cells[rowIdx, 8].Value = "Regulations - Mon";
ws.Cells[rowIdx, 9].Value = "Regulations - Tues";
ws.Cells[rowIdx, 10].Value = "Regulations - Wed";
ws.Cells[rowIdx, 11].Value = "Regulations - Thurs";
ws.Cells[rowIdx, 12].Value = "Regulations - Fri";
ws.Cells[rowIdx, 13].Value = "Regulations - Sat";
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, 6])
{
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++;
foreach (AssetListing_Space meterStat in this._ReportDataModel.SpaceDetailsList)
{
#region Unused code, but useful examples
// Example of how we could automatically render a dataset to worksheet
/*
* // Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
* ws.Cells["A1"].LoadFromDataTable(nextTable, true);
*/
// Example of how we could automatically render a strongly-typed collection of objects to worksheet
/*
* List<MeterStatisticObj> statObjects = new List<MeterStatisticObj>();
* statObjects.Add(meterStatCollection.MeterStats_Summary);
* ws.Cells["A1"].LoadFromCollection(statObjects, true);
*/
// Example of formatting a column for Date/Time
/*
* 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;
* }
*/
// Example of using RichText in a cell for advanced formatting possibilites
/*
* ws.Cells[rowIdx, 1].IsRichText = true;
* ws.Cells[rowIdx, 1].Style.WrapText = true; // Need this if we want multi-line
* OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText;
* OfficeOpenXml.Style.ExcelRichText ert = rtfCollection.Add(areaStat.AreaName + "\r\n");
*
* ert = rtfCollection.Add(" (ID=" + areaStat.AreaID.ToString() + ")");
* ert.Bold = false;
* ert.Italic = true;
* ert.Size = 8;
*/
#endregion
// Output row values for data
ws.Cells[rowIdx, 1].Value = meterStat.MeterID;
ws.Cells[rowIdx, 2].Value = meterStat.SpaceID;
ws.Cells[rowIdx, 3].Value = meterStat.AreaID;
ws.Cells[rowIdx, 4].Value = meterStat.Location;
ws.Cells[rowIdx, 5].Value = meterStat.Latitude;
ws.Cells[rowIdx, 6].Value = meterStat.Longitude;
RegulatedHoursGroupRepository.Repository = new RegulatedHoursGroupRepository();
RegulatedHoursGroup regulatedHours = RegulatedHoursGroupRepository.Repository.GetBestGroupForMeter(this._CustomerConfig.CustomerId,
meterStat.AreaID, meterStat.MeterID);
// If no regulated hour defintions came back, then we will default to assumption that regulated period is 24-hours a day
if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0))
{
rowIdx++;
continue;
}
// Loop through the daily rules and see if the timestamp falls within a Regulated or No Parking timeslot for the appropriate day
foreach (RegulatedHoursDetail detail in regulatedHours.Details)
{
string regulationTxt =
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt") + ", " +
detail.MaxStayMinutes.ToString() + " mins";
if (string.Compare(detail.Type, "Unregulated", true) == 0)
{
regulationTxt = "(Unregulated) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt") + ", " +
detail.MaxStayMinutes.ToString() + " mins";
}
else if (string.Compare(detail.Type, "No Parking", true) == 0)
{
regulationTxt = "(No Parking) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt");
}
else if (detail.MaxStayMinutes < 1)
{
regulationTxt = "(No Limit) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt");
}
// Determine which column of the spreadsheet is used for this day of the week
int columnIdxForDayOfWeek = 7 + detail.DayOfWeek;
// If the cell is empty, just add the regulation text. If something is already there, append the regulation text
// (There might be more than one regulated period for the same day)
if ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value == null) || ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) == null))
{
ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = regulationTxt;
}
else
{
ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = (ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) + Environment.NewLine + regulationTxt;
// And increment the row height also
ws.Row(rowIdx).Height = ws.Row(rowIdx).Height + ws.DefaultRowHeight;
ws.Cells[rowIdx, columnIdxForDayOfWeek].Style.WrapText = true;
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, 14])
{
rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
}
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[8, 1, 8, 13])
{
rng.AutoFilter = true;
}
// Column 1 is numeric integer (Meter ID)
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= 13; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
// 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("Occupancy Report Generation took: " + sw.Elapsed.ToString());
}
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));
}
}
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));
}
}
private static int DrawInfo(ExcelWorksheet ws, CalculationAwbInfo info, int iRow, int count)
{
var range = ws.Cells[iRow, 1, iRow, count];
range.Merge = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "sender";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 8].Value = info.CostPerKgOfSender;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.TotalSenderRate;
ws.Cells[iRow, 14].Value = info.TotalScotchCost;
ws.Cells[iRow, 16].Value = info.TotalFactureCost;
ws.Cells[iRow, 17].Value = info.TotalFactureCostEx;
ws.Cells[iRow, 18].Value = info.TotalPickupCost;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalOfSender;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "flight";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.FlightCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.FlightCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "custom";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.CustomCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.CustomCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "broker";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 9].Style.Font.Bold = true;
ws.Cells[iRow, 9].Value = info.BrokerCostPerKg;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.BrokerCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "insurance";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalInsuranceCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "forwarder";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalTransitCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "additional";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.AdditionalCost;
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 1].Value = "cost total";
ws.Cells[iRow, 1].Style.Font.Bold = true;
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.TotalExpenses;
var rangeCost = ws.Cells[iRow, 1, iRow, count];
rangeCost.Style.Fill.PatternType = ExcelFillStyle.Solid;
rangeCost.Style.Fill.BackgroundColor.SetColor(Color.HotPink);
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
ws.Cells[iRow, 19].Value = info.ProfitPerKg;
if (info.ProfitPerKg <= 0)
{
ws.Cells[iRow, 19].Style.Font.Color.SetColor(Color.Red);
}
ws.Cells[iRow, 20].Style.Font.Bold = true;
ws.Cells[iRow, 20].Value = info.Profit;
if (info.Profit <= 0)
{
ws.Cells[iRow, 20].Style.Font.Color.SetColor(Color.Red);
}
ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
iRow++;
return iRow;
}
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"
});
}
/// <summary>
/// 添加时间
/// </summary>
/// <param name="worksheet">工作簿</param>
/// <param name="info">信息</param>
private static void AddExtInfo(ExcelWorksheet worksheet, string info)
{
if (string.IsNullOrWhiteSpace(info))
{
info = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
}
//自适应高度
var result = info.Split(new[] {"\r\n"}, StringSplitOptions.None);
var count = result.Count();
worksheet.Cells["B3"].Value = info;
worksheet.Cells["B3"].Style.Font.Bold = true;
worksheet.Cells["B3"].Style.Font.Name = "宋体";
worksheet.Cells["B3"].Style.Font.Size = 10;
worksheet.Cells["B3"].Style.Indent = 8;
worksheet.Cells["B3"].Style.WrapText = true;
worksheet.Row(3).Height = worksheet.Row(3).Height*count;
worksheet.Cells["B3"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
worksheet.Cells["B3:F3"].Merge = true;
}
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));
}
}
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);
}
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);
newCol.ColumnMax = 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 static void PenaltiesGenerateSheetPerPeriod(ref Color pink1, ref Color pink2, ref Color pinkDark, ref Color lightBlue, ExcelWorksheet reportSheet, int period, int row, int doubleColumn)
{
try
{
reportSheet.Row(row).Height = 30;
reportSheet.Cells[row, 1 + doubleColumn, row + 1, 8 + doubleColumn].SetValue("Home Team").Merge().SetBackgroundColor(pink1).SetFontSize(14).SetCenterAlign();
reportSheet.Cells[row, 9 + doubleColumn, row, 10 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 11 + doubleColumn].SetBackgroundColor(pink1).SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 12 + doubleColumn, row, 13 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 14 + doubleColumn, row + 1, 21 + doubleColumn].SetValue("Away Team").Merge().SetBackgroundColor(pink1).SetFontSize(14).SetCenterAlign();
reportSheet.Cells[row, 22 + doubleColumn, row, 23 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 24 + doubleColumn].SetFontSize(24).SetCenterAlign().SetValue(period.ToString()).SetFontBold().SetBackgroundColor(pinkDark).SetFontColor(Color.White);
row += 1; //2
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 9 + doubleColumn, row, 10 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
reportSheet.Cells[row, 11 + doubleColumn].SetBackgroundColor(pink1).SetValue("Date").SetCenterAlign();
reportSheet.Cells[row, 12 + doubleColumn, row, 13 + doubleColumn].Merge().SetValue("Penalty Tracker").SetCenterAlign();
reportSheet.Cells[row, 22 + doubleColumn, row, 23 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
reportSheet.Cells[row, 24 + doubleColumn].SetBackgroundColor(pinkDark);
row += 1;//3
reportSheet.Row(row).Height = 15;
PenaltiesRowHeaderOfSheet(reportSheet, row, doubleColumn);
row += 1; //4
DisplayPenaltyCodes(reportSheet, row, doubleColumn);
int modulateRowColor = 1;
int notesRow = 1;
for (int i = row; i < row + 40; i += 2)
{
reportSheet.Row(i).Height = 16.50;
reportSheet.Row(i + 1).Height = 16.50;
//we need to set the row colors first because they are overwritten by other colors after
// the cells.
//full row of home or away secition
if (modulateRowColor % 2 == 0)
{
reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[i, 1 + doubleColumn, i + 1, 1 + doubleColumn].Merge().SetBackgroundColor(pink1).SetBorder(ExcelBorderStyle.Medium);
}
else
{
reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium).SetBackgroundColor(pink1);
reportSheet.Cells[i, 1 + doubleColumn, i + 1, 1 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
}
//full row of home or away secition
if (modulateRowColor % 2 == 0)
{
reportSheet.Cells[i, 14 + doubleColumn, i + 1, 23 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[i, 14 + doubleColumn, i + 1, 14 + doubleColumn].Merge().SetBackgroundColor(pink1).SetBorder(ExcelBorderStyle.Medium);
}
else
{
reportSheet.Cells[i, 14 + doubleColumn, i + 1, 23 + doubleColumn].SetBorder(ExcelBorderStyle.Medium).SetBackgroundColor(pink1);
reportSheet.Cells[i, 14 + doubleColumn, i + 1, 14 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
}
//displays the penalty Jam Row
for (int j = 2; j < 9; j++)
{
reportSheet.Cells[i, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i + 1, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
}
//Fo/EXP column
reportSheet.Cells[i, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
reportSheet.Cells[i + 1, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
reportSheet.Cells[i, 9 + doubleColumn, i + 1, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
//total column
reportSheet.Cells[i, 10 + doubleColumn, i + 1, 10 + doubleColumn].Merge().SetBorder(ExcelBorderStyle.Medium);
//row color sets background color
reportSheet.Cells[i, 11 + doubleColumn, i, 13 + doubleColumn].Merge().SetValue((notesRow).ToString()).SetCenterAlign();
reportSheet.Cells[i + 1, 11 + doubleColumn, i + 1, 13 + doubleColumn].Merge().SetValue((notesRow + 1).ToString()).SetBackgroundColor(lightBlue).SetCenterAlign();
//displays the penalty Jam Row
for (int j = 15; j < 22; j++)
{
reportSheet.Cells[i, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i + 1, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
}
reportSheet.Cells[i, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
reportSheet.Cells[i + 1, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
//fo/exp column away team
reportSheet.Cells[i, 22 + doubleColumn, i + 1, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[i, 23 + doubleColumn, i + 1, 23 + doubleColumn].Merge().SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
notesRow += 2;
modulateRowColor += 1;
}
row = 44;
reportSheet.Row(row).Height = 30;
reportSheet.Cells[row, 1 + doubleColumn, row, 9 + doubleColumn].Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White).SetValue("TOTAL PENALTIES FOR PERIOD:").SetRightAlignment().SetFontBold();
reportSheet.Cells[row, 10 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[row, 14 + doubleColumn, row, 22 + doubleColumn].Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White).SetValue("TOTAL PENALTIES FOR PERIOD:").SetRightAlignment().SetFontBold();
reportSheet.Cells[row, 23 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
row += 1;
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn, row + 3, 24 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[row, 1 + doubleColumn, row, 24 + doubleColumn].Merge().SetValue("PENALTY / Jam #: Enter codes for penalties in the upper row and jam # in the lower row for each skater.");
row += 1;
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn, row, 24 + doubleColumn].Merge().SetValue("FO/EXP: Foul Outs (FO) for penalty minutes should be marked as PM. Expulsions (EXP) should be listed by the appropriate penalty code. ");
row += 1;
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn, row, 24 + doubleColumn].Merge().SetValue("TOTAL: At the end of each period, add the number of penalties for each skater for that period and put it in the 'TOTAL' column.");
row += 1;
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn, row, 24 + doubleColumn].Merge().SetValue("CARRY OVER: Before period 2, transfer the penalties from period 1 by shading in the equivalent number of boxes. ");
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
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 static void ScoresFillTeamSheet(Color green1, Color green2, Color greenDark, ExcelWorksheet reportSheet, ref int row, int teamNumber)
{
try
{
reportSheet.Row(row).Height = 30;
reportSheet.Cells[row, 1, row + 1, 8].SetValue("Home Team").Merge().SetBackgroundColor(green1).SetFontSize(14).SetCenterAlign();
reportSheet.Cells[row, 9, row, 10].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 11].SetBackgroundColor(green1).SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 12, row, 14].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 15, row, 17].Merge().SetBorderBottom(ExcelBorderStyle.Thin).SetBackgroundColor(green1);
reportSheet.Cells[row, 18].SetFontSize(24).SetCenterAlign().SetValue(teamNumber.ToString()).SetFontBold().SetBackgroundColor(greenDark).SetFontColor(Color.White);
reportSheet.Cells[row, 20, row + 1, 27].SetValue("Away Team").Merge().SetBackgroundColor(green1).SetFontSize(14).SetCenterAlign();
reportSheet.Cells[row, 28, row, 29].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 30].SetBackgroundColor(green1).SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 31, row, 33].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 34, row, 36].Merge().SetBorderBottom(ExcelBorderStyle.Thin).SetBackgroundColor(green1);
reportSheet.Cells[row, 37].SetFontSize(24).SetCenterAlign().SetValue(teamNumber.ToString()).SetFontBold().SetBackgroundColor(greenDark).SetFontColor(Color.White);
row += 1; //2
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 9, row, 10].Merge().SetValue("Color").SetCenterAlign();
reportSheet.Cells[row, 11].SetBackgroundColor(green1).SetValue("Date").SetCenterAlign();
reportSheet.Cells[row, 12, row, 14].Merge().SetValue("Scorekeeper").SetCenterAlign();
reportSheet.Cells[row, 15, row, 17].Merge().SetValue("Jammer Ref").SetBackgroundColor(green1).SetCenterAlign();
reportSheet.Cells[row, 18].SetBackgroundColor(greenDark);
reportSheet.Cells[row, 28, row, 29].Merge().SetValue("Color").SetCenterAlign();
reportSheet.Cells[row, 30].SetBackgroundColor(green1).SetValue("Date").SetCenterAlign();
reportSheet.Cells[row, 31, row, 33].Merge().SetValue("Scorekeeper").SetCenterAlign();
reportSheet.Cells[row, 34, row, 36].Merge().SetValue("Jammer Ref").SetBackgroundColor(green1).SetCenterAlign();
reportSheet.Cells[row, 37].SetFontSize(24).SetBackgroundColor(greenDark);
row += 1;//3
reportSheet.Row(row).Height = 35.25;
int doubleColumn = 19;
ScoresRowHeaderOfSheet(reportSheet, row, 0);
ScoresRowHeaderOfSheet(reportSheet, row, doubleColumn);
row += 1; //4
int modulatorRow = 1;
for (int i = row; i < row + 76; i += 2)
{
reportSheet.Row(i).Height = 16.50;
reportSheet.Row(i + 1).Height = 16.50;
ScoresRowQuarterOfSheet(green1, green2, reportSheet, i, 0, modulatorRow);
ScoresRowQuarterOfSheet(green1, green2, reportSheet, i, doubleColumn, modulatorRow);
modulatorRow += 1;
}
row += 76;//80
reportSheet.Row(row).Height = 14.25;
reportSheet.Row(row + 1).Height = 14.25;
reportSheet.Cells[row, 1, row + 1, 1].Merge().SetBorder(ExcelBorderStyle.Thin).SetBackgroundColor(green2);
//sets the bottom row of the sheet.
for (int i = 1; i < 38; i++)
reportSheet.Cells[row, i, row + 1, i].Merge().SetBorder(ExcelBorderStyle.Thin).SetBackgroundColor(green2);
reportSheet.Cells[row, 2, row + 1, 2].Merge().SetBorder(ExcelBorderStyle.Thin).SetBackgroundColor(green2).SetValue("PERIOD TOTALS").SetFontSize(11).WrapText();
reportSheet.Cells[row, 20, row + 1, 20].Merge().SetBorder(ExcelBorderStyle.Thin).SetBackgroundColor(green2).SetValue("PERIOD TOTALS").SetFontSize(11).WrapText();
reportSheet.Cells[row, 37, row + 1, 37].SetBorder(ExcelBorderStyle.Medium);
row += 2;
reportSheet.Row(row).Height = 12;
string jamText = "JAM: Write in the jam number jam by jam, starting from 1. If there is a star pass, move to the next line, write 'SP' in the Jam # column, put in the new Jammer's number and pick up the scoring where the previous jammer left off.";
reportSheet.Cells[row, 1, row, 18].Merge().SetValue(jamText).SetCenterAlign();
reportSheet.Cells[row, 20, row, 37].Merge().SetValue(jamText).SetCenterAlign();
row += 1;
reportSheet.Row(row).Height = 12;
string trackingText = "Tracking: ALL of the Lead and Call categories should be marked with an X.";
reportSheet.Cells[row, 1, row, 18].Merge().SetValue(trackingText).SetCenterAlign();
reportSheet.Cells[row, 20, row, 37].Merge().SetValue(trackingText).SetCenterAlign();
row += 1;
reportSheet.Row(row).Height = 12;
trackingText = " Lost = When a jammer loses the ability to become lead jammer or loses lead jammer status itself. Do not check this box if the jammer is eligible but the opposing jammer is assigned lead jammer status first.";
reportSheet.Cells[row, 1, row, 18].Merge().SetValue(trackingText).SetCenterAlign();
reportSheet.Cells[row, 20, row, 37].Merge().SetValue(trackingText).SetCenterAlign();
row += 1;
reportSheet.Row(row).Height = 12;
trackingText = "Lead = Lead Jammer. Call = Called Jam, when the listed jammer successfully calls off the jam before jam time runs out. This is marked whether or not the jam was called legally.";
reportSheet.Cells[row, 1, row, 18].Merge().SetValue(trackingText).SetCenterAlign();
reportSheet.Cells[row, 20, row, 37].Merge().SetValue(trackingText).SetCenterAlign();
row += 1;
reportSheet.Row(row).Height = 12;
trackingText = "INJ = Called For Injury before the natural end of the jam. NP = First pass is not completed by the end of the jam (No Pass). ";
reportSheet.Cells[row, 1, row, 18].Merge().SetValue(trackingText).SetCenterAlign();
reportSheet.Cells[row, 20, row, 37].Merge().SetValue(trackingText).SetCenterAlign();
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
/// <summary>
/// Copies the current row to a new worksheet
/// </summary>
/// <param name="added">The worksheet where the copy will be created</param>
internal void Clone(ExcelWorksheet added)
{
ExcelRow newRow = added.Row(Row);
newRow.Collapsed = Collapsed;
newRow.Height = Height;
newRow.CustomHeight = newRow.CustomHeight;
newRow.Hidden = Hidden;
newRow.OutlineLevel = OutlineLevel;
newRow.PageBreak = PageBreak;
newRow.Phonetic = Phonetic;
newRow._styleName = _styleName;
newRow.StyleID = StyleID;
}
private static int IBRFCreateSectionThree(ExcelWorksheet reportSheet)
{
try
{
int row = 43;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1, row, 12].SetValue("Section 3. CERTIFICATION (Complete IMMEDIATELY AFTER Bout)").SetTitleFont().SetBorder(ExcelBorderStyle.Thin);
row += 1; //44
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1, row, 5].SetValue("Home Team Captain").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 12].SetValue("Visiting Team Captain").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1; //45
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1].SetValue("Skate Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Skate Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //46
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1].SetValue("Legal Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Legal Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //47
reportSheet.Row(row).Height = 18;
reportSheet.Cells[row, 1].SetValue("Signature:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Signature:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //48
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1, row, 5].SetValue("Head Referee").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 12].SetValue("Head NSO/Scorekeeper").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1; //49
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1].SetValue("Skate Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Skate Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //50
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1].SetValue("Legal Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Legal Name:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //51
reportSheet.Row(row).Height = 18;
reportSheet.Cells[row, 1].SetValue("Signature:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2, row, 5].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 6, row, 7].SetValue("Signature:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 12].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //52
reportSheet.Row(row).Height = 17.25;
reportSheet.Cells[row, 1, row, 12].SetValue(ScannedMessage).SetWhiteSpace().SetCenterAlign();
row += 1; //53
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 12].SetValue(RevMessage).SetWhiteSpace().SetCenterAlign();
return row;
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
return 0;
}
private static void LineupsBottomMessages(ExcelWorksheet reportSheet, int row, int doubleColumn)
{
try
{
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1 + doubleColumn, row + 4, 19 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("Write the jam number, starting from 1 each period, in the JAM column as each jam happens. Enter skater numbers by position. If no Pivot is fielded, enter an X in the noPivot box.");
row += 1;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("During the jam, circle the opposing jammer's current pass. When a skater sits in the penalty box, write that pass number in the left BOX column, or an S if the skater starts the jam in the box.");
row += 1;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("When the skater reenters the track from the box, write the current pass in the right column. If jam is called for injury, indicate it with an INJ in the BOX column of the injured skater.");
row += 1;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("When a star pass occurs, enter SP as in the JAM column on a new row, with jammer and pivot reversed (mark noPivot), same blockers, and maintain current pass count.");
row += 1;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("Note any box time carrying over from first period onto the period 2 sheet before turning in period 1 sheet.");
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
/// <summary>
/// creates section one of the WFTDA IBRF page.
/// </summary>
/// <param name="reportSheet"></param>
private static int IBRFCreateSectionOne(ExcelWorksheet reportSheet)
{
try
{
int row = 1;
reportSheet.Row(row).Height = 18;
reportSheet.Cells[row, 1, row, 12].Value = "WFTDA Interleague Bout Reporting Form (IBRF)";
reportSheet.Cells[row, 1, row, 12].SetPageTitle().SetBorder(ExcelBorderStyle.Thin);
row += 1;
reportSheet.Cells[row, 1, row, 12].Value = "Section 1. VENUE & ROSTERS";
reportSheet.Cells[row, 1, row, 12].SetTitleFont().SetBorder(ExcelBorderStyle.Thin);
row += 1;
reportSheet.Row(row).Height = 14.25;
//location
reportSheet.Cells[3, 1, 4, 1].Value = "Location:";
reportSheet.Cells[3, 1, 4, 1].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1;
reportSheet.Cells[4, 2, 4, 7].Value = "VENUE NAME";
reportSheet.Cells[4, 2, 4, 7].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[3, 2, 3, 7].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[4, 8, 4, 9].Value = "CITY";
reportSheet.Cells[4, 8, 4, 9].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[3, 8, 3, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[4, 10].Value = "ST/PRV";
reportSheet.Cells[4, 10].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[3, 10].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[4, 11, 4, 12].Value = "BOUT A/B";
reportSheet.Cells[4, 11, 4, 12].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[3, 11, 3, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;
reportSheet.Cells[5, 1].Value = "Date:";
reportSheet.Cells[5, 1].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[5, 2, 5, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[5, 6, 5, 7].Value = "Start Time:";
reportSheet.Cells[5, 6, 5, 7].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[5, 8, 5, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[5, 10].Value = "End Time:";
reportSheet.Cells[5, 10].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[5, 11, 5, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//6
reportSheet.Row(row).Height = 13.50;
reportSheet.Cells[6, 1, 6, 12].Value = "TEAM ROSTERS - List in order of skater number (numeric portion)";
reportSheet.Cells[6, 1, 6, 12].SetTitleBreak().SetBorder(ExcelBorderStyle.Thin);
row += 1;//7
reportSheet.Row(row).Height = 13.50;
reportSheet.Cells[7, 1, 7, 5].Value = "H O M E T E A M";
reportSheet.Cells[7, 1, 7, 5].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[7, 6, 7, 12].Value = "V I S I T I N G T E A M";
reportSheet.Cells[7, 6, 7, 12].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1;//8
reportSheet.Row(row).Height = 16.50;
reportSheet.Cells[8, 1].Value = "LEAGUE";
reportSheet.Cells[8, 1].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[8, 2, 8, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//9
reportSheet.Row(row).Height = 16.50;
reportSheet.Cells[9, 1].Value = "TEAM";
reportSheet.Cells[9, 1].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[9, 2, 9, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[8, 6, 8, 7].Value = "LEAGUE";
reportSheet.Cells[8, 6, 8, 7].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[8, 8, 8, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[9, 6, 9, 7].Value = "TEAM";
reportSheet.Cells[9, 6, 9, 7].SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[9, 8, 9, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//10
reportSheet.Row(row).Height = 16.50;
reportSheet.Cells[10, 1].Value = "# of players";
reportSheet.Cells[10, 1].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[10, 2].Value = "Skater #";
reportSheet.Cells[10, 2].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[10, 3, 10, 5].Value = "Skater Name";
reportSheet.Cells[10, 3, 10, 5].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[10, 6, 10, 7].Value = "# of players";
reportSheet.Cells[10, 6, 10, 7].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[10, 8].Value = "Skater #";
reportSheet.Cells[10, 8].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[10, 9, 10, 12].Value = "Skater Name";
reportSheet.Cells[10, 9, 10, 12].SetHeader1().SetBorder(ExcelBorderStyle.Thin);
for (int i = 1; i < 21; i++)
{
reportSheet.Row(i + 10).Height = 16.50;
reportSheet.Cells[i + 10, 1].SetHeader3().SetBorder(ExcelBorderStyle.Thin).SetValue(i).SetFontItalic();
reportSheet.Cells[i + 10, 2].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i + 10, 3, i + 10, 5].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i + 10, 6, i + 10, 7].Value = i;
reportSheet.Cells[i + 10, 6, i + 10, 7].SetHeader3().SetBorder(ExcelBorderStyle.Thin).SetFontItalic();
reportSheet.Cells[i + 10, 8].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i + 10, 9, i + 10, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
}
row = 31;
reportSheet.Row(row).Height = 13.50;
reportSheet.Cells[31, 1, 31, 2].SetValue("Referee Name").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 3].SetValue("Position").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 4].SetValue("League").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 5].SetValue("Cert.").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 6, 31, 8].SetValue("Referee Name").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 9].SetValue("Position").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 10, 31, 11].SetValue("League").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[31, 12].SetValue("Cert.").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 1, row, 12].SetBorder(ExcelBorderStyle.Thick);
row = 32;
for (int i = row; i < row + 4; i++)
{
reportSheet.Row(i).Height = 15;
reportSheet.Cells[i, 1, i, 2].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 6, i, 8].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 4].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 5].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 10, i, 11].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 12].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
}
row += 4;
return row;
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
return 0;
}
private static void BoutClockPage(Color lightGray, Color darkGray, Color aqua, Color purple, Color offPink, ExcelWorksheet reportSheet, ref int row, int doubleColumn, int teamNumber)
{
try
{
reportSheet.Row(row).Height = 30;
reportSheet.Cells[row, 1 + doubleColumn].SetValue("Home:").SetRightAlignment();
reportSheet.Cells[row, 2 + doubleColumn, row, 4 + doubleColumn].SetBackgroundColor(lightGray).Merge().SetFontSize(14).SetValue("Home Team").SetCenterAlign();
reportSheet.Cells[row, 5 + doubleColumn, 1 + row, 7 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8 + doubleColumn, 1 + row, 8 + doubleColumn].Merge().SetBackgroundColor(lightGray).SetBorderBottom(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 9 + doubleColumn, 1 + row, 9 + doubleColumn].Merge().SetBackgroundColor(darkGray).SetFontSize(24).SetCenterAlign().SetFontColor(Color.White).SetFontBold().SetValue(teamNumber.ToString());
row += 1;
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1 + doubleColumn, 1 + row, 1 + doubleColumn].SetValue("Away:").Merge().SetRightAlignment();
reportSheet.Cells[row, 2 + doubleColumn, 1 + row, 4 + doubleColumn].SetBackgroundColor(lightGray).Merge().SetFontSize(14).SetValue("Away Team").SetCenterAlign();
row += 1; //3
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 5 + doubleColumn, row, 7 + doubleColumn].Merge().SetValue("Period Timer").SetCenterAlign();
reportSheet.Cells[row, 8 + doubleColumn].SetBackgroundColor(lightGray).SetCenterAlign().SetValue("Date");
reportSheet.Cells[row, 9 + doubleColumn].SetBackgroundColor(darkGray);
row += 1;//4
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1 + doubleColumn].SetBackgroundColor(Color.Black);
reportSheet.Cells[row, 2 + doubleColumn, row, 3 + doubleColumn].SetValue("NAME").Merge().SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 4 + doubleColumn, row, 6 + doubleColumn].SetValue("TIMEOUTS TAKEN").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 7 + doubleColumn].SetValue("REVIEW").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 8 + doubleColumn, row, 9 + doubleColumn].SetValue("TEAM").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
row += 1;//5
reportSheet.Row(row).Height = 21;
reportSheet.Row(row + 1).Height = 21;
BoutClockCaptainsLine("Home", reportSheet, row, doubleColumn);
row += 2;
reportSheet.Row(row).Height = 21;
reportSheet.Row(row + 1).Height = 21;
BoutClockCaptainsLine("Away", reportSheet, row, doubleColumn);
row += 2;
reportSheet.Row(row + 1).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn, row, 9 + doubleColumn].SetValue("Identify captains and alternates before game. Indicate time on period clock when TO or OR used.").Merge().SetCenterAlign();
row += 1;
reportSheet.Row(row + 1).Height = 15;
reportSheet.Cells[row, 1 + doubleColumn].SetValue("JAM").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 2 + doubleColumn].SetValue("JAM TIME").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 3 + doubleColumn].SetValue("PACK LAPS").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 4 + doubleColumn].SetValue("EVENT").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 5 + doubleColumn, row, 7 + doubleColumn].SetValue("TEAM/SKATER").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 8 + doubleColumn].SetValue("Laps/Min").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
reportSheet.Cells[row, 9 + doubleColumn].SetValue("Pts/Min").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
row += 1;
for (int i = row; i < row + 38; i++)
{
reportSheet.Row(i).Height = 21;
BoutClockRow(aqua, purple, offPink, reportSheet, i, doubleColumn);
}
row += 38;
reportSheet.Row(row + 1).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn].SetValue("Team EVENTs: ");
reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record TO for team timeout or OR for official review, and indicate the team.");
row += 1;
reportSheet.Row(row + 1).Height = 12.75;
reportSheet.Cells[row, 1 + doubleColumn].SetValue("Skater EVENTs: ");
reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record INJ for jam-ending injury or EX for expulsion, indicate skater color & number.");
row += 1;
reportSheet.Row(row + 1).Height = 13.50;
reportSheet.Cells[row, 1 + doubleColumn].SetValue("Other EVENTs: ");
reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record OFF for any other official timeout. ");
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
}
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 static int IBRFCreateSectionFourNonSkatingOfficials(ExcelWorksheet reportSheet)
{
try
{
int row = 54;
reportSheet.Row(row).Height = 18;
reportSheet.Cells[row, 1, row, 12].SetValue("LIST OF NON-SKATING OFFICIALS/STAT TRACKERS").SetTitleFont().SetBorder(ExcelBorderStyle.Thin);
row += 1; //55
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1, row, 3].SetValue("Official/Tracker's Name").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 4, row, 7].SetValue("Non-Skating Official Position").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8, row, 9].SetValue("League").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 10, row, 12].SetValue("Certification").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1; //56
for (int i = row; i < row + 21; i++)
{
reportSheet.Row(i).Height = 12.75;
reportSheet.Cells[i, 1, i, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 4, i, 7].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 8, i, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[i, 10, i, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
}
row = 76;
row += 1; //77
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 12].SetValue(ScannedMessage).SetWhiteSpace().SetCenterAlign();
row += 1; //78
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 12].SetValue(RevMessage).SetWhiteSpace().SetCenterAlign();
return row;
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
return 0;
}
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 static int IBRFCreateSectionTwo(ExcelWorksheet reportSheet)
{
try
{
int row = 36;
reportSheet.Row(row).Height = 12;
reportSheet.Cells[row, 1, row, 12].SetValue("Section 2. SCORE (Complete DURING or IMMEDIATELY AFTER bout)").SetTitleFont().SetBorder(ExcelBorderStyle.Thin);
row += 1;
reportSheet.Row(row).Height = 12.75;
reportSheet.Cells[row, 1, row, 6].SetValue("HOME TEAM").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 7, row, 12].SetValue("VISITING TEAM").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
row += 1;//38
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1].SetValue("Period 1").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 4].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 5, row, 6].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 7].SetValue("Period 1").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 10].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 11, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//39
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1].SetValue("Period 2").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 2].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 4].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 5, row, 6].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 7].SetValue("Period 2").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 8].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 10].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 11, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1; //40
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 2].SetValue("TOTAL POINTS:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 3].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 4].SetValue("PENALTIES:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 5, row, 6].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 7, row, 8].SetValue("TOTAL POINTS:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 9].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 10].SetValue("PENALTIES:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 11, row, 12].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//41
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 3].SetValue("Expulsion/suspension notes:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
reportSheet.Cells[row, 4, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
row += 1;//42
reportSheet.Row(row).Height = 15;
reportSheet.Cells[row, 1, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
return row;
}
catch (Exception exception)
{
ErrorViewModel.Save(exception, exception.GetType());
}
return 0;
}
public static ExcelRow getRow(int iRowIndex, ExcelWorksheet oExcelWorksheet)
{
var oExcelRow = oExcelWorksheet.Row(iRowIndex);
return oExcelRow;
}
private static void HeaderStyles(ExcelWorksheet ws, ExcelRange cell)
{
cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
cell.Style.Font.Bold = true;
cell.Style.Locked = true;
ws.View.FreezePanes(2, 1);
ws.Row(1).Height = ExcelConstants.DefaultRowHeight;
}
private XmlElement GetOrCreateCellElement(ExcelWorksheet xlWorksheet, int row, int col)
{
XmlElement cellNode = null;
// this will create the row if it does not already exist
XmlNode rowNode = xlWorksheet.Row(row).Node;
if (rowNode != null)
{
cellNode = (XmlElement) rowNode.SelectSingleNode(string.Format("./d:c[@" + ExcelWorksheet.tempColumnNumberTag + "='{0}']", col), _xlWorksheet.NameSpaceManager);
if (cellNode == null)
{
// Didn't find the cell so create the cell element
cellNode = xlWorksheet.WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
cellNode.SetAttribute(ExcelWorksheet.tempColumnNumberTag, col.ToString());
// You must insert the new cell at the correct location.
// Loop through the children, looking for the first cell that is
// beyond the cell you're trying to insert. Insert before that cell.
XmlNode biggerNode = null;
XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", _xlWorksheet.NameSpaceManager);
if (cellNodes != null)
{
foreach (XmlNode node in cellNodes)
{
XmlNode colNode = node.Attributes[ExcelWorksheet.tempColumnNumberTag];
if (colNode != null)
{
int colFound = Convert.ToInt32(colNode.Value);
if (colFound > col)
{
biggerNode = node;
break;
}
}
}
}
if (biggerNode == null)
{
rowNode.AppendChild(cellNode);
}
else
{
rowNode.InsertBefore(cellNode, biggerNode);
}
}
}
return (cellNode);
}
///<summary>
/// Get the row
///</summary>
///<param name="sheet">Current worksheet</param>
///<param name="rowNumber">Row number to get</param>
///<returns>The excel row</returns>
public static ExcelRow GetRow(this ExcelWorksheet sheet, int rowNumber)
{
return(sheet.Row(rowNumber));
}
private static int DrawHeader(ExcelWorksheet ws)
{
var iColumn = 1;
ws.Cells[1, iColumn++].Value = Entities.Client;
ws.Cells[1, iColumn++].Value = Entities.DisplayNumber;
ws.Cells[1, iColumn++].Value = Entities.FactoryName;
ws.Cells[1, iColumn++].Value = Entities.Mark;
ws.Cells[1, iColumn++].Value = Entities.Class;
ws.Cells[1, iColumn++].Value = Entities.Count;
ws.Cells[1, iColumn++].Value = Entities.Weight;
ws.Cells[1, iColumn++].Value = Entities.SenderRate;
ws.Cells[1, iColumn++].Value = Entities.TotalSenderRate;
ws.Cells[1, iColumn++].Value = Entities.Invoice;
ws.Cells[1, iColumn++].Value = Entities.Value;
ws.Cells[1, iColumn++].Value = Entities.TariffPerKg;
ws.Cells[1, iColumn++].Value = Entities.TotalTariffCost;
ws.Cells[1, iColumn++].Value = Entities.ScotchCost;
ws.Cells[1, iColumn++].Value = Entities.Insurance;
ws.Cells[1, iColumn++].Value = Entities.FactureCost;
ws.Cells[1, iColumn++].Value = Entities.FactureCostEx;
ws.Cells[1, iColumn++].Value = Entities.PickupCost;
ws.Cells[1, iColumn++].Value = Entities.TransitCost;
ws.Cells[1, iColumn].Value = Entities.Profit;
HeaderStyles(ws.Cells[1, 1, 1, iColumn]);
ws.View.FreezePanes(2, 1);
ws.Row(1).Height = ExcelConstants.DefaultRowHeight;
return iColumn;
}