protected void IbnExcel_Click(object sender, ImageClickEventArgs e)
{
Idioma = (DataTable)ViewState["TablaIdioma"];
try
{
string VbTxtSql = "", VbNomArchivo = "";
DataRow[] Result;
Idioma = (DataTable)ViewState["TablaIdioma"];
Result = Idioma.Select("Objeto= 'CurExptrAlerProxSvc'");
foreach (DataRow row in Result)
{
VbNomArchivo = row["Texto"].ToString().Trim();
}
CsTypExportarIdioma CursorIdioma = new CsTypExportarIdioma();
CursorIdioma.Alimentar("CurExptrAlerProxSvc", Session["77IDM"].ToString().Trim());
if (DdlAeronave.Text.Trim().Equals("0"))
{
VbTxtSql = "EXEC Consultas_General 3, '', '', 'CurExptrAlerProxSvc',@Todo,@Di, @ICC, '06-01-2012', '06-01-2012'";
}
else
{
VbTxtSql = "EXEC Consultas_General 3, @A, '', 'CurExptrAlerProxSvc',@Todo,@Di, @ICC, '06-01-2012', '06-01-2012'";
}
Cnx.SelecBD();
using (SqlConnection con = new SqlConnection(Cnx.GetConex()))
{
using (SqlCommand SC = new SqlCommand(VbTxtSql, con))
{
SC.CommandTimeout = 90000000;
SC.Parameters.AddWithValue("@Todo", CkbVisualTodo.Checked == true ? 1 : 0);
SC.Parameters.AddWithValue("@Di", TxtDiaVisual.Text);
SC.Parameters.AddWithValue("@A", DdlAeronave.SelectedItem.Text.Trim());
SC.Parameters.AddWithValue("@ICC", Session["!dC!@"]);
using (SqlDataAdapter sda = new SqlDataAdapter())
{
SC.Connection = con;
sda.SelectCommand = SC;
using (DataSet ds = new DataSet())
{
sda.Fill(ds);
int VbLog = VbNomArchivo.Length > 30 ? 30 : VbNomArchivo.Length;
ds.Tables[0].TableName = VbNomArchivo.Trim().Substring(0, VbLog);
using (XLWorkbook wb = new XLWorkbook())
{
foreach (DataTable dt in ds.Tables)
{
wb.Worksheets.Add(dt);
}
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/ms-excel";
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xlsx", VbNomArchivo));
Response.Charset = "";
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
}
}
}
Page.Title = ViewState["PageTit"].ToString();
}
catch (Exception Ex)
{
string VbcatUs = Session["C77U"].ToString(), VbcatNArc = ViewState["PFileName"].ToString(), VbcatVer = Session["77Version"].ToString(), VbcatAct = Session["77Act"].ToString();
Cnx.UpdateErrorV2(VbcatUs, VbcatNArc, "Exportar Informe Ingeniería Próximos Cumplimientos", Ex.StackTrace.Substring(Ex.StackTrace.Length - 300, 300), Ex.Message, VbcatVer, VbcatAct);
}
}
/**************************************************************************/
private void BuildWorksheetPageObservations(
MacroscopeJobMaster JobMaster,
XLWorkbook wb,
string WorksheetLabel
)
{
var ws = wb.Worksheets.Add(WorksheetLabel);
int iRow = 1;
int iCol = 1;
int iColMax = 1;
MacroscopeDocumentCollection DocCollection = JobMaster.GetDocCollection();
MacroscopeAllowedHosts AllowedHosts = JobMaster.GetAllowedHosts();
{
ws.Cell(iRow, iCol).Value = "URL";
iCol++;
ws.Cell(iRow, iCol).Value = "Status Code";
iCol++;
ws.Cell(iRow, iCol).Value = "Status";
iCol++;
ws.Cell(iRow, iCol).Value = "Observation";
}
iColMax = iCol;
iRow++;
foreach (MacroscopeDocument msDoc in DocCollection.IterateDocuments())
{
string Url = msDoc.GetUrl();
string StatusCode = (( int )msDoc.GetStatusCode()).ToString();
string Status = msDoc.GetStatusCode().ToString();
foreach (string Observation in msDoc.IterateRemarks())
{
iCol = 1;
this.InsertAndFormatUrlCell(ws, iRow, iCol, msDoc);
if (AllowedHosts.IsInternalUrl(Url: Url))
{
ws.Cell(iRow, iCol).Style.Font.SetFontColor(XLColor.Green);
}
else
{
ws.Cell(iRow, iCol).Style.Font.SetFontColor(XLColor.Gray);
}
iCol++;
this.InsertAndFormatContentCell(ws, iRow, iCol, StatusCode);
iCol++;
this.InsertAndFormatContentCell(ws, iRow, iCol, Status);
iCol++;
this.InsertAndFormatContentCell(ws, iRow, iCol, Observation);
iRow++;
}
}
{
var rangeData = ws.Range(1, 1, iRow - 1, iColMax);
var excelTable = rangeData.CreateTable();
}
}
private static void AddPage <T>(XLWorkbook workbook, string pageName, IEnumerable <T> data, ExcelFormattingSettings formattingSettings)
{
var type = typeof(T);
var props = type.GetProperties();
//Create a sheet
var ws = workbook.Worksheets.Add(pageName);
// Default style
ws.Style.Font.FontSize = formattingSettings.DefaultFont.Size;
ws.Style.Font.FontName = formattingSettings.DefaultFont.Family;
ws.Style.Font.Bold = formattingSettings.DefaultFont.IsBold;
ws.Style.Font.Italic = formattingSettings.DefaultFont.IsItalic;
int iColumn = 1;
int iRow = 1;
// Add headers
if (formattingSettings.HeaderSettings.AddHeaders)
{
//Header font settings
ws.Row(iRow).Style.Font.FontName = formattingSettings.HeaderSettings.Font.Family;
ws.Row(iRow).Style.Font.FontSize = formattingSettings.HeaderSettings.Font.Size;
ws.Row(iRow).Style.Font.Bold = formattingSettings.HeaderSettings.Font.IsBold;
ws.Row(iRow).Style.Font.Italic = formattingSettings.HeaderSettings.Font.IsItalic;
foreach (var prop in props)
{
var columnSettings = prop.GetCustomAttribute <ColumnSettingsAttribute>();
ws.Cell(iRow, iColumn++).Value = columnSettings == null ? prop.Name : columnSettings.Header;
}
iRow++;
}
// Add data
foreach (var obj in data)
{
iColumn = 1;
for (int i = 0; i < props.Length; i++)
{
ws.Cell(iRow, iColumn).Value = props[i].GetValue(obj);
if (props[i].PropertyType == typeof(DateTime))
{
ws.Cell(iRow, iColumn).Style.DateFormat.Format = formattingSettings.DateFormat;
}
iColumn++;
}
iRow++;
}
// Column widths
if (formattingSettings.AutoFit)
{
ws.Columns().AdjustToContents();
}
// Freeze headers
if (formattingSettings.HeaderSettings.AddHeaders)
{
ws.RangeUsed().SetAutoFilter(true);
ws.SheetView.Freeze(1, 0);
}
}
public ExcelHelper(string excelPath)
{
_workbook = new XLWorkbook(excelPath);
}
public void Create(string filePath)
{
var wb = new XLWorkbook();
IXLWorksheet ws;
#region Single Column Numbers
String singleColumnNumbers = "Single Column Numbers";
ws = wb.Worksheets.Add(singleColumnNumbers);
// Add a bunch of numbers to filter
ws.Cell("A1").SetValue("Numbers")
.CellBelow().SetValue(2)
.CellBelow().SetValue(3)
.CellBelow().SetValue(3)
.CellBelow().SetValue(5)
.CellBelow().SetValue(1)
.CellBelow().SetValue(4);
// Add filters
ws.RangeUsed().SetAutoFilter().Column(1).EqualTo(3).Or.GreaterThan(4);
// Sort the filtered list
ws.AutoFilter.Sort(1);
#endregion
#region Single Column Strings
String singleColumnStrings = "Single Column Strings";
ws = wb.Worksheets.Add(singleColumnStrings);
// Add a bunch of strings to filter
ws.Cell("A1").SetValue("Strings")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C")
.CellBelow().SetValue("C")
.CellBelow().SetValue("E")
.CellBelow().SetValue("A")
.CellBelow().SetValue("D");
// Add filters
ws.RangeUsed().SetAutoFilter().Column(1).Between("B", "D");
// Sort the filtered list
ws.AutoFilter.Sort(1);
#endregion
#region Single Column Mixed
String singleColumnMixed = "Single Column Mixed";
ws = wb.Worksheets.Add(singleColumnMixed);
// Add a bunch of items to filter
ws.Cell("A1").SetValue("Mixed")
.CellBelow().SetValue("B")
.CellBelow().SetValue(3)
.CellBelow().SetValue("C")
.CellBelow().SetValue("E")
.CellBelow().SetValue(1)
.CellBelow().SetValue(4);
// Add filters
ws.RangeUsed().SetAutoFilter().Column(1).EqualTo(3).Or.EqualTo("C");
// Sort the filtered list
ws.AutoFilter.Sort(1);
#endregion
#region Multi Column
String multiColumn = "Multi Column";
ws = wb.Worksheets.Add(multiColumn);
ws.Cell("A1").SetValue("First")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C")
.CellBelow().SetValue("C")
.CellBelow().SetValue("E")
.CellBelow().SetValue("A")
.CellBelow().SetValue("D");
ws.Cell("B1").SetValue("Numbers")
.CellBelow().SetValue(2)
.CellBelow().SetValue(3)
.CellBelow().SetValue(3)
.CellBelow().SetValue(5)
.CellBelow().SetValue(1)
.CellBelow().SetValue(4);
ws.Cell("C1").SetValue("Strings")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C")
.CellBelow().SetValue("C")
.CellBelow().SetValue("E")
.CellBelow().SetValue("A")
.CellBelow().SetValue("D");
// Add filters
ws.RangeUsed().SetAutoFilter().Column(2).EqualTo(3).Or.GreaterThan(4);
ws.RangeUsed().SetAutoFilter().Column(3).Between("B", "D");
// Sort the filtered list
ws.AutoFilter.Sort(3);
#endregion
using (var ms = new MemoryStream())
{
wb.SaveAs(ms);
var workbook = new XLWorkbook(ms);
#region Single Column Numbers
workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending);
#endregion
#region Single Column Strings
workbook.Worksheet(singleColumnStrings).AutoFilter.Sort(1, XLSortOrder.Descending);
#endregion
#region Single Column Mixed
workbook.Worksheet(singleColumnMixed).AutoFilter.Column(1).EqualOrGreaterThan("D");
workbook.Worksheet(singleColumnMixed).AutoFilter.Sort(1, XLSortOrder.Descending);
#endregion
#region Multi Column
workbook.Worksheet(multiColumn).AutoFilter.Column(3).EqualTo("E");
workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending);
#endregion
workbook.SaveAs(filePath);
ms.Close();
}
}
public ActionResult WriteExcel()
{
string filetodownload = string.Empty;
string response = "NoData";
try
{
string startPath = Server.MapPath("~/Downloads/SiteDetails/");
if (Directory.Exists(startPath))
{
foreach (string _file in Directory.GetFiles(startPath))
{
System.IO.File.Delete(_file);
}
}
filetodownload = "Site Report " + DateTime.Now.Date.ToString("dd-MM-yyyy") + ".xlsx";
DataTable dt = new DataTable();
SqlConnection SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["MSSQL_ConnectionString"].ConnectionString);
SqlCommand Sqlcomrep = new SqlCommand();
Sqlcomrep.Connection = SqlCon;
Sqlcomrep.CommandType = CommandType.StoredProcedure;
Sqlcomrep.CommandTimeout = 500;
Sqlcomrep.CommandText = "SiteMList_get";
Sqlcomrep.Parameters.AddWithValue("@ProfileId", Convert.ToInt32(Session["ProfileId"]));
Sqlcomrep.Parameters.AddWithValue("@UserId", Convert.ToInt32(Session["UserId"]));
SqlDataAdapter sqlda = new SqlDataAdapter(Sqlcomrep);
SqlCon.Open();
sqlda.Fill(dt);
SqlCon.Close();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Site Details");
wb.SaveAs(System.Web.HttpContext.Current.Server.MapPath("~/Downloads/SiteDetails/" + filetodownload));
}
response = "Reports Successfully Created";
}
catch (Exception ex)
{
response = "Error in Creating Reports";
string logfilename = "ErrorLog" + DateTime.Now.Date.ToString("ddMMyyyy") + ".txt";
string message = string.Format("Time: {0}", DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss tt"));
message += Environment.NewLine;
message += string.Format("TargetSite: {0}", ex.ToString());
message += Environment.NewLine;
message += "-----------------------------------------------------------";
message += Environment.NewLine;
string path = System.Web.HttpContext.Current.Server.MapPath("~/Log/" + logfilename);
//string path = System.IO.Directory.GetParent(Environment.CurrentDirectory).ToString();
using (StreamWriter writer = new StreamWriter(path, true))
{
writer.WriteLine(message);
writer.Close();
}
}
return(Json(filetodownload));
}
public void Upper_Value()
{
Object actual = XLWorkbook.EvaluateExpr(@"Upper(""AbCdEfG"")");
Assert.AreEqual("ABCDEFG", actual);
}
internal static void AddMiscCostsSheet(XLWorkbook workbook, TECBid bid, string sheetName = "Misc Costs")
{
throw new NotImplementedException();
}
public void Trim_Value()
{
Object actual = XLWorkbook.EvaluateExpr(@"Trim("" some text with padding "")");
Assert.AreEqual("some text with padding", actual);
}
public void Upper_Empty_Input_String()
{
Object actual = XLWorkbook.EvaluateExpr(@"Upper("""")");
Assert.AreEqual("", actual);
}
public void Trim_EmptyInput_Striong()
{
Object actual = XLWorkbook.EvaluateExpr(@"Trim("""")");
Assert.AreEqual("", actual);
}
public void Text_String_Input()
{
Object actual = XLWorkbook.EvaluateExpr(@"TEXT(""211x"", ""#00"")");
Assert.AreEqual("211x", actual);
}
public void Text_Empty_Input_String()
{
Object actual = XLWorkbook.EvaluateExpr(@"Text(1913415.93, """")");
Assert.AreEqual("", actual);
}
public void Search_Start_Position_Too_Large2()
{
Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""text"", ""This is some text"", 15)"), Throws.TypeOf <ArgumentException>());
}
public void Value_Input_String_Is_Not_A_Number()
{
Assert.That(() => XLWorkbook.EvaluateExpr(@"Value(""asdf"")"), Throws.TypeOf <FormatException>());
}
internal static void AddElectricalMaterialSheet(XLWorkbook workbook, TECBid bid, string sheetName = "Wire and Conduit")
{
throw new NotImplementedException();
}
public void Code_Empty_Input_String()
{
// Todo: more specific exception - ValueException?
Assert.That(() => XLWorkbook.EvaluateExpr(@"Code("""")"), Throws.TypeOf <IndexOutOfRangeException>());
}
public bool ImportExcelData(string filePath, ref ValidationErrors errors)
{
bool rtn = true;
var targetFile = new FileInfo(filePath);
if (!targetFile.Exists)
{
errors.Add("导入的数据文件不存在");
return(false);
}
var excelFile = new ExcelQueryFactory(filePath);
using (XLWorkbook wb = new XLWorkbook(filePath))
{
//第一个Sheet
using (IXLWorksheet wws = wb.Worksheets.First())
{
//对应列头
excelFile.AddMapping <WMS_InvInfoModel>(x => x.InvCode, "库房编码");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.InvName, "库房名称");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.Remark, "说明");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.Status, "状态");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.CreatePerson, "创建人");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.CreateTime, "创建时间");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.ModifyPerson, "修改人");
excelFile.AddMapping <WMS_InvInfoModel>(x => x.ModifyTime, "修改时间");
//SheetName，第一个Sheet
var excelContent = excelFile.Worksheet <WMS_InvInfoModel>(0);
//开启事务
using (DBContainer db = new DBContainer())
{
var tran = db.Database.BeginTransaction(); //开启事务
int rowIndex = 0;
//检查数据正确性
foreach (var row in excelContent)
{
rowIndex += 1;
string errorMessage = String.Empty;
var model = new WMS_InvInfoModel();
model.Id = row.Id;
model.InvCode = row.InvCode;
model.InvName = row.InvName;
model.Remark = row.Remark;
model.Status = row.Status;
model.CreatePerson = row.CreatePerson;
model.CreateTime = row.CreateTime;
model.ModifyPerson = row.ModifyPerson;
model.ModifyTime = row.ModifyTime;
if (!String.IsNullOrEmpty(errorMessage))
{
rtn = false;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, 10).Value = errorMessage;
continue;
}
//执行额外的数据校验
try
{
AdditionalCheckExcelData(model);
}
catch (Exception ex)
{
rtn = false;
errorMessage = ex.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, 10).Value = errorMessage;
continue;
}
//写入数据库
WMS_InvInfo entity = new WMS_InvInfo();
entity.Id = model.Id;
entity.InvCode = model.InvCode;
entity.InvName = model.InvName;
entity.Remark = model.Remark;
entity.Status = model.Status;
entity.CreatePerson = model.CreatePerson;
entity.CreateTime = model.CreateTime;
entity.ModifyPerson = model.ModifyPerson;
entity.ModifyTime = model.ModifyTime;
db.WMS_InvInfo.Add(entity);
try
{
db.SaveChanges();
}
catch (Exception ex)
{
rtn = false;
//将当前报错的entity状态改为分离，类似EF的回滚（忽略之前的Add操作）
db.Entry(entity).State = System.Data.Entity.EntityState.Detached;
errorMessage = ex.InnerException.InnerException.Message;
errors.Add(string.Format("第 {0} 列发现错误：{1}{2}", rowIndex, errorMessage, "<br/>"));
wws.Cell(rowIndex + 1, 10).Value = errorMessage;
}
}
if (rtn)
{
tran.Commit(); //必须调用Commit()，不然数据不会保存
}
else
{
tran.Rollback(); //出错就回滚
}
}
}
wb.Save();
}
return(rtn);
}
public void Dollar_Empty_Input_String()
{
Assert.That(() => XLWorkbook.EvaluateExpr(@"Dollar("", 3)"), Throws.TypeOf <ExpressionParseException>());
}
public void Open(string path)
{
_workbook = new XLWorkbook(path);
}
public static void CreateExcel(this IEnumerable <object> list, Stream stream, string sheetName, bool includeHeaders = true)
{
var workBook = new XLWorkbook();
var sheet = workBook.AddWorksheet(sheetName);
int rowIndex = includeHeaders ? 2 : 1;
int headerRowIndex = 1;
var columnPositions = new Dictionary <string, int>();
foreach (var line in list.ToDictionaryList <object>())
{
foreach (var column in line.Keys)
{
// If the columns doesn't already exist in the dataset, then add it.
if (!columnPositions.ContainsKey(column))
{
columnPositions.Add(column, columnPositions.Count + 1);
if (includeHeaders)
{
sheet.Cell(headerRowIndex, columnPositions[column]).Value = column;
}
}
var cellValue = line[column];
var strValue = cellValue?.ToString();
// Attempt to get the type.
if (cellValue is DateTime || cellValue is bool || cellValue.IsNumber())
{
sheet.Cell(rowIndex, columnPositions[column]).Value = cellValue;
}
else if (DateTime.TryParse(strValue, out DateTime dateValue))
{
sheet.Cell(rowIndex, columnPositions[column]).Value = dateValue;
}
else if (bool.TryParse(strValue, out bool boolValue))
{
sheet.Cell(rowIndex, columnPositions[column]).Value = boolValue;
}
else if (decimal.TryParse(strValue, out decimal decValue))
{
// Try to convert from string.
if (strValue.StartsWith("0"))
{
// Add an apostrophe so excel doesn't do away with any leading zeros...
sheet.Cell(rowIndex, columnPositions[column]).Value = $"'{strValue}";
}
else
{
sheet.Cell(rowIndex, columnPositions[column]).Value = decValue;
}
}
else
{
sheet.Cell(rowIndex, columnPositions[column]).Value = strValue;
}
}
rowIndex++;
}
if (includeHeaders)
{
var rngTable = sheet.Range($"A1:{GetColumnId(columnPositions.Count)}{rowIndex}");
rngTable.FirstRow().Style
.Font.SetBold()
.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
workBook.SaveAs(stream, new SaveOptions {
ValidatePackage = true
});
}
private static void AddQueryResultPage(XLWorkbook wb, QueryResult result, ExcelFormattingSettings formattingSettings)
{
//Create a sheet
var ws = wb.Worksheets.Add(result.Name);
// Columns
int length = result.Columns.Count();
if (length == 0)
{
return;
}
// Default style
ws.Style.Font.FontSize = formattingSettings.DefaultFont.Size;
ws.Style.Font.FontName = formattingSettings.DefaultFont.Family;
ws.Style.Font.Bold = formattingSettings.DefaultFont.IsBold;
ws.Style.Font.Italic = formattingSettings.DefaultFont.IsItalic;
int iColumn = 1;
int iRow = 1;
var columns = result.Columns.ToList();
// Add headers
if (formattingSettings.HeaderSettings.AddHeaders)
{
//Header font settings
ws.Row(iRow).Style.Font.FontName = formattingSettings.HeaderSettings.Font.Family;
ws.Row(iRow).Style.Font.FontSize = formattingSettings.HeaderSettings.Font.Size;
ws.Row(iRow).Style.Font.Bold = formattingSettings.HeaderSettings.Font.IsBold;
ws.Row(iRow).Style.Font.Italic = formattingSettings.HeaderSettings.Font.IsItalic;
foreach (var column in result.Columns)
{
var md = result.Metadata.Columns.FirstOrDefault(c => c.Name == column.Name);
ws.Cell(iRow, iColumn++).Value = md?.Label ?? column.Name;
}
iRow++;
}
// Add data
foreach (IDictionary <string, object> obj in result.Data)
{
iColumn = 1;
for (int i = 0; i < length; i++)
{
ws.Cell(iRow, iColumn).Value = obj[columns[i].Name];
if (columns[i].ColumnType?.Equals(typeof(DateTime)) == true)
{
ws.Cell(iRow, iColumn).Style.DateFormat.Format = formattingSettings.DateFormat;
}
iColumn++;
}
iRow++;
}
// Check for totals
if (result.Metadata.Columns.Any(c => c.HasTotals))
{
iRow++;
ws.Cell(iRow, 1).Value = result.Metadata.TotalsLabel;
var totals = result.Metadata.Columns.Where(c => c.HasTotals);
foreach (var total in totals)
{
var columnIndex = columns
.FindIndex(c => c.Name.Equals(total.Name, StringComparison.OrdinalIgnoreCase)) + 1;
var columnName = (char)('A' - 1 + columnIndex);
var startingRow = formattingSettings.HeaderSettings.AddHeaders ? 2 : 1;
ws.Cell(iRow, columnIndex).FormulaA1 = $"=SUM({columnName}{startingRow}:{columnName}{iRow -2})";
}
ws.Row(iRow).Style.Font.Bold = true;
}
// Check for fixed rows
if (result.Metadata.FixedColumns > 0)
{
ws.SheetView.FreezeColumns(result.Metadata.FixedColumns);
}
// Column widths
if (formattingSettings.AutoFit)
{
ws.Columns().AdjustToContents();
}
// Freeze headers
if (formattingSettings.HeaderSettings.AddHeaders)
{
ws.RangeUsed().SetAutoFilter(true);
ws.SheetView.FreezeRows(1);
}
}
protected void btnImportarExcel_Click(object sender, EventArgs e)
{
if (fuSelecionarExcel.HasFile)
{
if (".xlsx" == Path.GetExtension(fuSelecionarExcel.FileName))
{
byte[] buffer = new byte[fuSelecionarExcel.FileBytes.Length];
fuSelecionarExcel.FileContent.Seek(0, SeekOrigin.Begin);
fuSelecionarExcel.FileContent.Read(buffer, 0, Convert.ToInt32(fuSelecionarExcel.FileContent.Length));
Stream stream2 = new MemoryStream(buffer);
DataTable dt = new DataTable();
using (XLWorkbook workbook = new XLWorkbook(stream2))
{
IXLWorksheet sheet = workbook.Worksheet(1);
bool FirstRow = true;
string readRange = "1:1";
foreach (IXLRow row in sheet.RowsUsed())
{
//If Reading the First Row (used) then add them as column name
if (FirstRow)
{
//Checking the Last cellused for column generation in datatable
readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Columns.Add(cell.Value.ToString());
}
FirstRow = false;
}
else
{
//Adding a Row in datatable
dt.Rows.Add();
int cellIndex = 0;
//Updating the values of datatable
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
cellIndex++;
}
}
}
}
if (dt.Columns.Contains("NOMBRE(S)".Trim()) && dt.Columns.Contains("APEPATERNO".Trim()) && dt.Columns.Contains("APEMATERNO".Trim()) &&
dt.Columns.Contains("CORREO".Trim()) && dt.Columns.Contains("CELULAR".Trim()))
{
usuariosCompletosServices.ValidarExcelToListFranquicias(dt);
if (usuariosCompletosServices.lsLigasInsertar.Count >= 1)
{
usuariosCompletosServices.ExcelToListFranquicias(usuariosCompletosServices.lsgvUsuariosSeleccionados, usuariosCompletosServices.lsLigasInsertar, Guid.Parse(ViewState["UidFranquiciaLocal"].ToString()));
gvUsuariosSeleccionados.DataSource = usuariosCompletosServices.lsgvUsuariosSeleccionados;
gvUsuariosSeleccionados.DataBind();
}
if (usuariosCompletosServices.lsLigasErrores.Count >= 1)
{
btnDescargarError.Visible = true;
btnMasDetalle.Visible = true;
pnlAlertImportarError.Visible = true;
lblMnsjAlertImportarError.Text = "<strong>!Lo sentimos¡</strong> algunos usuarios no se han importado. **Recuerde que todos los campos son obligatorios**";
divAlertImportarError.Attributes.Add("class", "alert alert-danger alert-dismissible fade show");
}
}
else
{
btnDescargarError.Visible = false;
btnMasDetalle.Visible = true;
pnlAlertImportarError.Visible = true;
lblMnsjAlertImportarError.Text = "<strong>!Lo sentimos¡</strong> el archivo no tiene las columnas correctas.";
divAlertImportarError.Attributes.Add("class", "alert alert-danger alert-dismissible fade show");
}
}
else
{
// byte[] buffer = new byte[fuSelecionarExcel.FileBytes.Length];
// fuSelecionarExcel.FileContent.Seek(0, SeekOrigin.Begin);
// fuSelecionarExcel.FileContent.Read(buffer, 0, Convert.ToInt32(fuSelecionarExcel.FileContent.Length));
// Stream stream2 = new MemoryStream(buffer);
// int f = 0;
// using (var reader = new StreamReader(stream2))
// f = reader.ReadAllLines();
// int f2 = '"' + ',' + '"';
// //txtAsunto.Text = f.Replace("\r\n", txtAsunto.Text);
// string ll = txtAsunto.Text.Replace(@"\\", "-");
// string fio = "";
// string lol = txtAsunto.Text.Replace("\\", "-");
// string fi00o = "";
// gridview.DataSource = (DataTable)ReadToEnd(txtAsunto.Text.Replace(@"\", ""));
// gridview.DataBind();
pnlAlert.Visible = true;
lblMensajeAlert.Text = "Solo se admite los formatos xlsx";
divAlert.Attributes.Add("class", "alert alert-danger alert-dismissible fade show");
}
}
}
public void GetOpportunity()
{
OpportunityService.OpportunityService service = new OpportunityService.OpportunityService();
var result1 = service.GetAllOpportunity().Where(x => x.Name.Contains("Shapoorji")).FirstOrDefault();
try
{
StringBuilder builder = new StringBuilder();
var workbook = new XLWorkbook();
var infoWorksheet = workbook.Worksheets.Add("Project Information");
infoWorksheet.Cell("A1").Value = "Project Description";
infoWorksheet.Cell("B1").Value = result1.ProjectDescription;
infoWorksheet.Cell("A2").Value = "Project Rational";
infoWorksheet.Cell("B2").Value = result1.ProjectRationale;
infoWorksheet.Cell("A3").Value = "Category Type";
infoWorksheet.Cell("B3").Value = result1.CategoryType;
infoWorksheet.Cell("A4").Value = "Project No";
infoWorksheet.Cell("B4").Value = result1.ProjectNo;
infoWorksheet.Cell("A5").Value = "Project Name";
infoWorksheet.Cell("B5").Value = result1.Name;
infoWorksheet.Cell("A6").Value = "Region";
infoWorksheet.Cell("B6").Value = result1.Region;
infoWorksheet.Cell("A7").Value = "Sector";
infoWorksheet.Cell("B7").Value = result1.Sector;
infoWorksheet.Cell("A7").Value = "Sub Sector";
infoWorksheet.Cell("B7").Value = result1.SubSector;
infoWorksheet.Cell("A8").Value = "Country";
infoWorksheet.Cell("B8").Value = result1.Country;
infoWorksheet.Cell("A9").Value = "Currency";
infoWorksheet.Cell("B9").Value = result1.Currency;
infoWorksheet.Cell("A10").Value = "Financing Cost";
infoWorksheet.Cell("B10").Value = result1.BudgetAmount;
infoWorksheet.Cell("A11").Value = "Approval Level";
infoWorksheet.Cell("B11").Value = result1.ApprovalLevel;
infoWorksheet.Cell("A12").Value = "NS Project Type";
infoWorksheet.Cell("B12").Value = result1.NSProjectType;
infoWorksheet.Cell("A13").Value = "NS Core Sector";
infoWorksheet.Cell("B13").Value = result1.NSCoreSector;
infoWorksheet.Cell("A14").Value = "NSO Processing Category";
infoWorksheet.Cell("B14").Value = result1.NSOProcessingCategory;
infoWorksheet.Cell("A15").Value = "Project Stage";
infoWorksheet.Cell("B15").Value = result1.ProjectStage;
infoWorksheet.Cell("A16").Value = "Project Status";
infoWorksheet.Cell("B16").Value = result1.ProjectStatus;
infoWorksheet.Cell("A17").Value = "Task Status";
infoWorksheet.Cell("B17").Value = result1.TaskStatus;
infoWorksheet.Cell("A18").Value = "Division";
infoWorksheet.Cell("B18").Value = result1.Division;
infoWorksheet.Cell("A19").Value = "Division Role";
infoWorksheet.Cell("B19").Value = result1.DivisionRole;
infoWorksheet.Cell("A21").Value = "Milestones";
infoWorksheet.Cell("A22").Value = "PRF Approval";
infoWorksheet.Cell("B22").Value = result1.PRFApproval;
infoWorksheet.Cell("A23").Value = "CRP ICM";
infoWorksheet.Cell("B23").Value = result1.CRPICM;
infoWorksheet.Cell("A24").Value = "Letter of No-Objection";
infoWorksheet.Cell("B24").Value = result1.LetterOfNoObjection;
infoWorksheet.Cell("A25").Value = "Final Review ICM";
infoWorksheet.Cell("B25").Value = result1.FinalReviewICM;
infoWorksheet.Cell("A26").Value = "RRP Approval";
infoWorksheet.Cell("B26").Value = result1.RRPApproval;
infoWorksheet.Cell("A27").Value = "Signing Date";
infoWorksheet.Cell("B27").Value = result1.SigningDate;
infoWorksheet.Cell("A28").Value = "Effectiveness Date";
infoWorksheet.Cell("B28").Value = result1.EffectivenessDate;
infoWorksheet.Cell("A29").Value = "XARR Date";
infoWorksheet.Cell("B29").Value = result1.XARRDate;
infoWorksheet.Cell("A30").Value = "Project End Date";
infoWorksheet.Cell("B30").Value = result1.ProjectEndDate;
var teamWorksheet = workbook.Worksheets.Add("Project Team");
teamWorksheet.Cell("A1").Value = "Project Team";
teamWorksheet.Cell("A2").Value = "Name";
teamWorksheet.Cell("B2").Value = "Role";
ProjectTeamExecutor pte = new ProjectTeamExecutor();
var team = pte.GetAllTeamMembers(result1.OpportunityId);
int ctr = 2;
for (int i = 0; i < team.Count; i++)
{
teamWorksheet.Cell(string.Format("A{0}", ctr + i + 1)).Value = team[i].Name;
teamWorksheet.Cell(string.Format("B{0}", ctr + i + 1)).Value = team[i].Role;
}
var obWorksheet = workbook.Worksheets.Add("Obligors");
int obCtr = 1;
obWorksheet.Cell(string.Format("A{0}", obCtr)).Value = "Obligors";
obCtr++;
obWorksheet.Cell("A" + obCtr).Value = "Entity Role";
obWorksheet.Cell("B" + obCtr).Value = "Account Name";
obWorksheet.Cell("C" + obCtr).Value = "Involvement in Project";
AccountExecutor acc = new AccountExecutor();
var obligors = acc.GetAccountsByParentIDAndRole(result1.OpportunityId, "Obligors");
for (int i = 0; i < obligors.Count; i++)
{
obWorksheet.Cell(string.Format("A{0}", obCtr + i + 1)).Value = obligors[i].EntityRole;
obWorksheet.Cell(string.Format("B{0}", obCtr + i + 1)).Value = obligors[i].AccountName;
obWorksheet.Cell(string.Format("C{0}", obCtr + i + 1)).Value = obligors[i].InvolvementInProject;
obCtr++;
}
var spWorksheet = workbook.Worksheets.Add("Sponsors");
var spCtr = 1;
spWorksheet.Cell(string.Format("A{0}", spCtr)).Value = "Sponsors";
spCtr++;
spWorksheet.Cell("A" + spCtr).Value = "Entity Role";
spWorksheet.Cell("B" + spCtr).Value = "Account Name";
spWorksheet.Cell("C" + spCtr).Value = "Country";
var sponsors = acc.GetAccountsByParentIDAndRole(result1.OpportunityId, "Sponsors");
if (sponsors.Count > 0)
{
for (int i = 0; i < obligors.Count; i++)
{
spWorksheet.Cell(string.Format("A{0}", spCtr + i + 1)).Value = sponsors[i].EntityRole;
spWorksheet.Cell(string.Format("B{0}", spCtr + i + 1)).Value = sponsors[i].AccountName;
spWorksheet.Cell(string.Format("C{0}", spCtr + i + 1)).Value = sponsors[i].Country;
}
}
var dmfImpWorksheet = workbook.Worksheets.Add("DMF - Impact");
int dmfImpactCtr = 1;
dmfImpWorksheet.Cell(string.Format("A{0}", dmfImpactCtr)).Value = "DMF - Impact";
dmfImpactCtr++;
dmfImpWorksheet.Cell("A" + dmfImpactCtr).Value = "Impact Statement";
dmfImpWorksheet.Cell("B" + dmfImpactCtr).Value = "Indicators";
dmfImpWorksheet.Cell("C" + dmfImpactCtr).Value = "Unit of Measurement";
dmfImpWorksheet.Cell("D" + dmfImpactCtr).Value = "Baseline Value";
dmfImpWorksheet.Cell("E" + dmfImpactCtr).Value = "Baseline Year";
dmfImpWorksheet.Cell("F" + dmfImpactCtr).Value = "Achievement Value";
dmfImpWorksheet.Cell("G" + dmfImpactCtr).Value = "Achieved by (Year)";
DMFExecutor dmf = new DMFExecutor();
var impact = dmf.GetNSOImpact(result1.OpportunityId);
if (impact.Count > 0)
{
for (int i = 0; i < impact.Count; i++)
{
dmfImpWorksheet.Cell(string.Format("A{0}", dmfImpactCtr + i + 1)).Value = impact[i].ImpactStatement;
dmfImpWorksheet.Cell(string.Format("B{0}", dmfImpactCtr + i + 1)).Value = impact[i].Indicators;
dmfImpWorksheet.Cell(string.Format("C{0}", dmfImpactCtr + i + 1)).Value = impact[i].UnitOfMeasurement;
dmfImpWorksheet.Cell(string.Format("D{0}", dmfImpactCtr + i + 1)).Value = impact[i].BaselineValue;
dmfImpWorksheet.Cell(string.Format("E{0}", dmfImpactCtr + i + 1)).Value = impact[i].BaselineYear;
dmfImpWorksheet.Cell(string.Format("F{0}", dmfImpactCtr + i + 1)).Value = impact[i].AchievementValue;
dmfImpWorksheet.Cell(string.Format("G{0}", dmfImpactCtr + i + 1)).Value = impact[i].AchievedByYear;
}
}
var dmfOutcomeWorksheet = workbook.Worksheets.Add("DMF - Outcome");
int dmfOutcomeCtr = 1;
dmfOutcomeWorksheet.Cell(string.Format("A{0}", dmfOutcomeCtr)).Value = "DMF - Impact";
dmfOutcomeCtr++;
dmfOutcomeWorksheet.Cell("A" + dmfOutcomeCtr).Value = "Outcome Statement";
dmfOutcomeWorksheet.Cell("B" + dmfOutcomeCtr).Value = "Indicators";
dmfOutcomeWorksheet.Cell("C" + dmfOutcomeCtr).Value = "Unit of Measurement";
dmfOutcomeWorksheet.Cell("D" + dmfOutcomeCtr).Value = "Baseline Value";
dmfOutcomeWorksheet.Cell("E" + dmfOutcomeCtr).Value = "Baseline Year";
dmfOutcomeWorksheet.Cell("F" + dmfOutcomeCtr).Value = "Achievement Value";
dmfOutcomeWorksheet.Cell("G" + dmfOutcomeCtr).Value = "Achieved by (Year)";
var outcome = dmf.GetNSOOutcome(result1.OpportunityId);
if (outcome.Count > 0)
{
for (int i = 0; i < outcome.Count; i++)
{
dmfOutcomeWorksheet.Cell(string.Format("A{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].OutcomeStatement;
dmfOutcomeWorksheet.Cell(string.Format("B{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].Indicators;
dmfOutcomeWorksheet.Cell(string.Format("C{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].UnitOfMeasurement;
dmfOutcomeWorksheet.Cell(string.Format("D{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].BaselineValue;
dmfOutcomeWorksheet.Cell(string.Format("E{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].BaselineYear;
dmfOutcomeWorksheet.Cell(string.Format("F{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].AchievementValue;
dmfOutcomeWorksheet.Cell(string.Format("G{0}", dmfOutcomeCtr + i + 1)).Value = outcome[i].AchievedByYear;
}
}
var dmfOutputWorksheet = workbook.Worksheets.Add("DMF - Output");
int dmfOutputCtr = 1;
dmfOutputWorksheet.Cell(string.Format("A{0}", dmfOutputCtr)).Value = "DMF - Impact";
dmfOutputCtr++;
dmfOutputWorksheet.Cell("A" + dmfOutputCtr).Value = "Output Statement";
dmfOutputWorksheet.Cell("B" + dmfOutputCtr).Value = "Indicators";
dmfOutputWorksheet.Cell("C" + dmfOutputCtr).Value = "Unit of Measurement";
dmfOutputWorksheet.Cell("D" + dmfOutputCtr).Value = "Baseline Value";
dmfOutputWorksheet.Cell("E" + dmfOutputCtr).Value = "Baseline Year";
dmfOutputWorksheet.Cell("F" + dmfOutputCtr).Value = "Achievement Value";
dmfOutputWorksheet.Cell("G" + dmfOutputCtr).Value = "Achieved by (Year)";
var output = dmf.GetNSOOutput(result1.OpportunityId);
if (output.Count > 0)
{
for (int i = 0; i < outcome.Count; i++)
{
dmfOutputWorksheet.Cell(string.Format("A{0}", dmfOutputCtr + i + 1)).Value = output[i].OutputStatement;
dmfOutputWorksheet.Cell(string.Format("B{0}", dmfOutputCtr + i + 1)).Value = output[i].Indicators;
dmfOutputWorksheet.Cell(string.Format("C{0}", dmfOutputCtr + i + 1)).Value = output[i].UnitOfMeasurement;
dmfOutputWorksheet.Cell(string.Format("D{0}", dmfOutputCtr + i + 1)).Value = output[i].BaselineValue;
dmfOutputWorksheet.Cell(string.Format("E{0}", dmfOutputCtr + i + 1)).Value = output[i].BaselineYear;
dmfOutputWorksheet.Cell(string.Format("F{0}", dmfOutputCtr + i + 1)).Value = output[i].AchievementValue;
dmfOutputWorksheet.Cell(string.Format("G{0}", dmfOutputCtr + i + 1)).Value = output[i].AchievedByYear;
}
}
var covenantWorksheet = workbook.Worksheets.Add("Project Covenant");
int covCtr = 1;
covenantWorksheet.Cell(string.Format("A{0}", covCtr)).Value = "Project Covenant";
covCtr++;
covenantWorksheet.Cell("A" + covCtr).Value = "Related Project";
covenantWorksheet.Cell("B" + covCtr).Value = "Covenant Specific ID";
covenantWorksheet.Cell("C" + covCtr).Value = "Covenant Type";
covenantWorksheet.Cell("D" + covCtr).Value = "Covenant Description";
covenantWorksheet.Cell("E" + covCtr).Value = "Reference";
covenantWorksheet.Cell("F" + covCtr).Value = "Frequency of Review";
covenantWorksheet.Cell("G" + covCtr).Value = "Remarks/Issues";
covenantWorksheet.Cell("H" + covCtr).Value = "Due Date";
covenantWorksheet.Cell("I" + covCtr).Value = "Complied With?";
covenantWorksheet.Cell("J" + covCtr).Value = "Submission Date";
covenantWorksheet.Cell("K" + covCtr).Value = "Status";
CovenantExecutor covExec = new CovenantExecutor();
var cov = covExec.GetNSOCovenants(result1.OpportunityId);
if (cov.Count > 0)
{
for (int i = 0; i < cov.Count; i++)
{
covenantWorksheet.Cell(string.Format("A{0}", covCtr + i + 1)).Value = cov[i].ParentIDString;
covenantWorksheet.Cell(string.Format("B{0}", covCtr + i + 1)).Value = cov[i].Name;
covenantWorksheet.Cell(string.Format("C{0}", covCtr + i + 1)).Value = cov[i].CovenantType;
covenantWorksheet.Cell(string.Format("D{0}", covCtr + i + 1)).Value = cov[i].CovenantDescription;
covenantWorksheet.Cell(string.Format("E{0}", covCtr + i + 1)).Value = cov[i].Reference;
covenantWorksheet.Cell(string.Format("F{0}", covCtr + i + 1)).Value = cov[i].FrequencyOfReview;
covenantWorksheet.Cell(string.Format("G{0}", covCtr + i + 1)).Value = cov[i].RemarksIssues;
covenantWorksheet.Cell(string.Format("H{0}", covCtr + i + 1)).Value = cov[i].DueDate;
covenantWorksheet.Cell(string.Format("I{0}", covCtr + i + 1)).Value = cov[i].CompliedWith;
covenantWorksheet.Cell(string.Format("J{0}", covCtr + i + 1)).Value = cov[i].SubmissionDate;
covenantWorksheet.Cell(string.Format("K{0}", covCtr + i + 1)).Value = cov[i].Status;
}
}
workbook.SaveAs("Shapoorji.xlsx");
//builder.AppendLine(string.Format("Project Description : {0}", result1.ProjectDescription));
//builder.AppendLine(string.Format("Project Rationale : {0}", result1.ProjectRationale));
//builder.AppendLine(string.Format("Category Type : {0}", result1.CategoryType));
//builder.AppendLine(string.Format("Project No : {0}", result1.ProjectNo));
//builder.AppendLine(string.Format("Project Name : {0}", result1.Name));
//builder.AppendLine(string.Format("Region : {0}", result1.Region));
//builder.AppendLine(string.Format("Sector : {0}", result1.Sector));
//builder.AppendLine(string.Format("Sub Sector : {0}", result1.SubSector));
//builder.AppendLine(string.Format("Country : {0}", result1.Country));
//builder.AppendLine(string.Format("Currency : {0}", result1.Currency));
//builder.AppendLine(string.Format("Financing Cost : {0}", result1.BudgetAmount));
//builder.AppendLine(string.Format("Approval Level : {0}", result1.ApprovalLevel));
//builder.AppendLine(string.Format("NS Project Type : {0}", result1.NSProjectType));
//builder.AppendLine(string.Format("NS Core Sector : {0}", result1.NSCoreSector));
//builder.AppendLine(string.Format("NSO Processing Category : {0}", result1.NSOProcessingCategory));
//builder.AppendLine(string.Format("Project Stage : {0}", result1.ProjectStage));
//builder.AppendLine(string.Format("Project Status : {0}", result1.ProjectStatus));
//builder.AppendLine(string.Format("Task Status : {0}", result1.TaskStatus));
//builder.AppendLine("==============================");
//builder.AppendLine(string.Format("Division : {0}", result1.Division));
//builder.AppendLine(string.Format("Division Role : {0}", result1.DivisionRole));
builder.AppendLine(string.Format("Guarantee : {0}", result1.Guarantee));
builder.AppendLine(string.Format("Borrower : {0}", result1.Borrower));
builder.AppendLine(string.Format("Model of Financial Ass : {0}", result1.ModeOfFinancialAssistance));
builder.AppendLine(string.Format("Processing Category : {0}", result1.ProcessingCategory));
builder.AppendLine(string.Format("Processing Scenario : {0}", result1.processingScenario));
builder.AppendLine(string.Format("Expected Approval Year : {0}", result1.ExpectedApprovalYear));
builder.AppendLine(string.Format("Additional Financing : {0}", result1.AdditionalFinancing));
/*
* builder.AppendLine(string.Format("Project Description : {0}", result1.ProjectDescription));
* builder.AppendLine(string.Format("Project Rationale : {0}", result1.ProjectRationale));
*
* builder.AppendLine(string.Format("Category Type : {0}", result1.CategoryType));
* builder.AppendLine(string.Format("Project No : {0}", result1.ProjectNo));
* builder.AppendLine(string.Format("Project Name : {0}", result1.Name));
* builder.AppendLine(string.Format("Region : {0}", result1.Region));
* builder.AppendLine(string.Format("Sector : {0}", result1.Sector));
* builder.AppendLine(string.Format("Sub Sector : {0}", result1.SubSector));
* builder.AppendLine(string.Format("Country : {0}", result1.Country));
* builder.AppendLine(string.Format("Currency : {0}", result1.Currency));
* builder.AppendLine(string.Format("Financing Cost : {0}", result1.BudgetAmount));
* builder.AppendLine(string.Format("Approval Level : {0}", result1.ApprovalLevel));
* builder.AppendLine(string.Format("NS Project Type : {0}", result1.NSProjectType));
* builder.AppendLine(string.Format("NS Core Sector : {0}", result1.NSCoreSector));
* builder.AppendLine(string.Format("NSO Processing Category : {0}", result1.NSOProcessingCategory));
* builder.AppendLine(string.Format("Project Stage : {0}", result1.ProjectStage));
* builder.AppendLine(string.Format("Project Status : {0}", result1.ProjectStatus));
* builder.AppendLine(string.Format("Task Status : {0}", result1.TaskStatus));
* builder.AppendLine("==============================");
* builder.AppendLine(string.Format("Division : {0}", result1.Division));
* builder.AppendLine(string.Format("Division Role : {0}", result1.DivisionRole));
*
*
* builder.AppendLine(string.Format("Guarantee : {0}", result1.Guarantee));
* builder.AppendLine(string.Format("Borrower : {0}", result1.Borrower));
*
* builder.AppendLine(string.Format("Model of Financial Ass : {0}", result1.ModeOfFinancialAssistance));
* builder.AppendLine(string.Format("Processing Category : {0}", result1.ProcessingCategory));
* builder.AppendLine(string.Format("Processing Scenario : {0}", result1.processingScenario));
* builder.AppendLine(string.Format("Expected Approval Year : {0}", result1.ExpectedApprovalYear));
* builder.AppendLine(string.Format("Additional Financing : {0}", result1.AdditionalFinancing));
*
*/
/*
* var result = service.GetOneOpportunity("573b99ed-bf50-e511-80ee-3863bb2eb8d8");
*
* Console.WriteLine(string.Format("Project Name : {0}", result.Name));
* Console.WriteLine(string.Format("Region : {0}", result.Region));
* Console.WriteLine(string.Format("Sector : {0}", result.Sector));
* Console.WriteLine(string.Format("Sub Sector : {0}", result.SubSector));
* Console.WriteLine(string.Format("Country : {0}", result.Country));
* Console.WriteLine(string.Format("Currency : {0}", result.Currency));
* Console.WriteLine(string.Format("Financing Cost : {0}", result.BudgetAmount));
* Console.WriteLine(string.Format("Approval Level : {0}", result.ApprovalLevel));
* Console.WriteLine(string.Format("Guarantee : {0}", result.Guarantee));
* Console.WriteLine(string.Format("Borrower : {0}", result.Borrower));
* Console.WriteLine(string.Format("Category Type : {0}", result.CategoryType));
* Console.WriteLine(string.Format("Model of Financial Ass : {0}", result.ModeOfFinancialAssistance));
* Console.WriteLine(string.Format("Processing Category : {0}", result.ProcessingCategory));
* Console.WriteLine(string.Format("Processing Scenario : {0}", result.processingScenario));
* Console.WriteLine(string.Format("Expected Approval Year : {0}", result.ExpectedApprovalYear));
* Console.WriteLine(string.Format("Additional Financing : {0}", result.AdditionalFinancing));
* Console.WriteLine(string.Format("Project Stage : {0}", result.ProjectStage));
* Console.WriteLine(string.Format("Project Status : {0}", result.ProjectStatus));
* Console.WriteLine("==============================");
* Console.WriteLine(string.Format("Division : {0}", result.Division));
* Console.WriteLine(string.Format("Division Role : {0}", result.DivisionRole));
*/
/*
* var result = service.GetAllOpportunity();
*
* foreach (var r in result)
* {
* Console.WriteLine(string.Format("Project Name : {0}", r.Name));
* Console.WriteLine(string.Format("Region : {0}", r.Region));
* Console.WriteLine(string.Format("Sector : {0}", r.Sector));
* Console.WriteLine(string.Format("Sub Sector : {0}", r.SubSector));
* Console.WriteLine(string.Format("Country : {0}", r.Country));
* Console.WriteLine(string.Format("Currency : {0}", r.Currency));
* Console.WriteLine(string.Format("Financing Cost : {0}", r.BudgetAmount));
* Console.WriteLine(string.Format("Approval Level : {0}", r.ApprovalLevel));
* Console.WriteLine(string.Format("Guarantee : {0}", r.Guarantee));
* Console.WriteLine(string.Format("Borrower : {0}", r.Borrower));
* Console.WriteLine(string.Format("Category Type : {0}", r.CategoryType));
* Console.WriteLine(string.Format("Model of Financial Ass : {0}", r.ModeOfFinancialAssistance));
* Console.WriteLine(string.Format("Processing Category : {0}", r.ProcessingCategory));
* Console.WriteLine(string.Format("Processing Scenario : {0}", r.processingScenario));
* Console.WriteLine(string.Format("Expected Approval Year : {0}", r.ExpectedApprovalYear));
* Console.WriteLine(string.Format("Additional Financing : {0}", r.AdditionalFinancing));
* Console.WriteLine(string.Format("Project Stage : {0}", r.ProjectStage));
* Console.WriteLine(string.Format("Project Status : {0}", r.ProjectStatus));
* Console.WriteLine("==============================");
* Console.WriteLine(string.Format("Division : {0}", r.Division));
* Console.WriteLine(string.Format("Division Role : {0}", r.DivisionRole));
* }
*/
Console.WriteLine("Complete");
Console.ReadLine();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.ReadLine();
}
}
public XLWorkbook ExportExcelSummary(BaseReportModel model, List <StoreModels> lstStore)
{
string sheetName = "Time_Clock_Summary";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
var lstEmpChecked = model.ListEmployees.Where(m => m.Checked).ToList();
if (lstEmpChecked == null || !lstEmpChecked.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
List <string> lstUserID = lstEmpChecked.Select(m => m.Name).ToList();
model.ListStores = lstEmpChecked.Select(m => m.StoreId).ToList();
DateTimeHelper.GetDateTime(ref model);
var _lstBusDayAllStore = _baseFactory.GetBusinessDays(model.FromDate, model.ToDate, model.ListStores, model.Mode);
// When data null
if (_lstBusDayAllStore == null || !_lstBusDayAllStore.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
var _lstBusDayIdAllStore = _lstBusDayAllStore.Select(ss => ss.Id).ToList();
var data = GetTimeClockSummary(model.ListStores, lstUserID, _lstBusDayIdAllStore, model.Mode);
// When data null
if (data == null || !data.Any())
{
// Set header report
CreateReportHeader(ws, 12, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
// Format header report
ws.Range(1, 1, 3, 12).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range(1, 1, 3, 12).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
return(wb);
}
//Table
//row 1: Table Header
CreateReportHeader(ws, 8, model.FromDate, model.ToDate, _AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Clock Summary Report"));
DateTime fromDate = model.FromDate;
DateTime toDate = model.ToDate;
int row = 4;
int startRow = row;
int startRow_Store = row;
int endRow_Store = row;
int startRow_User = row;
int endRow_User = row;
string storeId = string.Empty, storeName = string.Empty;
lstStore = lstStore.Where(ww => model.ListStores.Contains(ww.Id)).OrderBy(oo => oo.Name).ToList();
for (int i = 0; i < lstStore.Count; i++)
{
storeId = lstStore[i].Id;
storeName = lstStore[i].Name;
var _lstBusDayIdStore = _lstBusDayAllStore.Where(x => x.StoreId == storeId).Select(ss => ss.Id).ToList();
if (_lstBusDayIdStore != null && _lstBusDayIdStore.Any())
{
var dataStore = data.Where(x => x.StoreId == storeId && _lstBusDayIdStore.Contains(x.BusinessId)).OrderBy(oo => oo.DateTimeIn).ToList();
if (dataStore != null && dataStore.Any())
{
startRow_Store = row;
endRow_Store = row;
row++;
//Timer Header
ws.Cell("A" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Employee"));
ws.Cell("B" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date In"));
ws.Cell("C" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time In"));
ws.Cell("D" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Date Out"));
ws.Cell("E" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Time Out"));
ws.Cell("F" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Hours Worked"));
ws.Cell("G" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Late(Minutes)"));
ws.Cell("H" + row).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Early Leave(Minutes)"));
ws.Range("A" + row + ":H" + row).Style.Font.SetBold(true);
ws.Range("A" + row + ":H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
row++;
#region ======Get Employees
List <RPEmployeeItemModels> lstUser = lstEmpChecked.Where(ww => ww.StoreId == storeId).Select(m => new RPEmployeeItemModels
{
ID = m.ID,
Name = m.Name,
StoreId = m.StoreId
}).OrderBy(oo => oo.Name).ToList();
int frmDateOutMerge = 0, toDateOutMerge = 0;
List <DateOutInfo> lstDateOuts = new List <DateOutInfo>();
foreach (var employee in lstUser)
{
var dataEmp = dataStore.Where(x => x.UserId == employee.ID).GroupBy(gg => gg.CreatedDate.Date).OrderBy(oo => oo.Key).ToList();
if (dataEmp != null && dataEmp.Any())
{
startRow_User = row;
endRow_User = row;
#region ====Date
double _sumHw = 0, _sumLate = 0, _sumEarly = 0;
var _checkObj = new TimeClockReportModels();
foreach (var itmData in dataEmp)
{
int startRow_Date = row;
int endRow_Date = row;
DateTime _date = itmData.Key;
var lstdateDateGroup = itmData.GroupBy(gg => gg.DateTimeIn).OrderBy(oo => oo.Key).ToList();
lstDateOuts = new List <DateOutInfo>();
frmDateOutMerge = row;
toDateOutMerge = row;
//loop data in date
foreach (var itemGroup in lstdateDateGroup)
{
_checkObj = itemGroup.Where(ww => ww.DateTimeOut.Date != Commons.MinDate.Date).FirstOrDefault();
if (_checkObj == null)
{
_checkObj = itemGroup.FirstOrDefault();
}
//Time In
ws.Cell("C" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeIn.TimeOfDay));
ws.Cell("C" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
//check merge time out
if (_checkObj.DateTimeOut.Date != Commons.MinDate.Date)
{
var checkExist = lstDateOuts.Where(ww => ww.DateOut.Date == _checkObj.DateTimeOut.Date).FirstOrDefault();
if (checkExist == null)
{
var info = new DateOutInfo();
info.DateOut = _checkObj.DateTimeOut;
info.FromRow = frmDateOutMerge;
info.ToRow = toDateOutMerge;
lstDateOuts.Add(info);
frmDateOutMerge++;
toDateOutMerge++;
}
else
{
checkExist.ToRow++;
}
//E time out
ws.Cell("E" + row).SetValue(DateTimeHelper.GetAMPM(_checkObj.DateTimeOut.TimeOfDay));
ws.Cell("E" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
}
else
{
frmDateOutMerge++;
toDateOutMerge++;
}
var _hoursWorked = Math.Round(_checkObj.HoursWork, 2);
// F hours work
ws.Cell("F" + row).SetValue(_hoursWorked);
ws.Cell("F" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("F" + row).Style.NumberFormat.Format = "#,##0.00";
_sumHw += _hoursWorked;
ws.Cell("G" + row).SetValue(Math.Round(_checkObj.Late, 2));
ws.Cell("G" + row).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("G" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
_sumLate += Math.Round(_checkObj.Late, 2);
ws.Cell("H" + row).SetValue(Math.Round(_checkObj.Early, 2));
ws.Cell("H" + row).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("H" + row).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
_sumEarly += Math.Round(_checkObj.Early, 2);
//========================================================
row++;
endRow_User++;
endRow_Date++;
//Date Out
foreach (var item in lstDateOuts)
{
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Merge().SetValue(item.DateOut.ToString("MM/dd/yyyy"));
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
ws.Range("D" + item.FromRow + ":D" + item.ToRow).Style.Font.SetBold(true);
}
//==============================================================================
//row++;
//endRow_User++;
//endRow_Date++;
}//end dateDate.Count > 0
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Merge().SetValue(_date.ToString("MM/dd/yyyy"));
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Font.SetBold(true);
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("B" + startRow_Date + ":B" + (endRow_Date - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
}
#endregion
//===> Total
row++;
endRow_User++;
ws.Cell("B" + (row - 1)).SetValue(_AttributeForLanguage.CurrentUser.GetLanguageTextFromKey("Total"));
ws.Cell("B" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left);
ws.Range("B" + (row - 1) + ":E" + (row - 1)).Merge();
ws.Cell("F" + (row - 1)).SetValue(_sumHw);
ws.Cell("F" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("F" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("G" + (row - 1)).SetValue(_sumLate);
ws.Cell("G" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("G" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Cell("H" + (row - 1)).SetValue(_sumEarly);
ws.Cell("H" + (row - 1)).Style.NumberFormat.Format = "#,##0.00";
ws.Cell("H" + (row - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Font.SetBold(true);
ws.Range("B" + (row - 1) + ":H" + (row - 1)).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#d9d9d9"));
//========================
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Merge().SetValue(employee.Name);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Font.SetBold(true);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
ws.Range("A" + startRow_User + ":A" + (endRow_User - 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
}
}
#endregion
//Get StoreName
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Merge().SetValue(storeName);
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Font.SetBold(true);
ws.Range("A" + startRow_Store + ":H" + endRow_Store).Style.Fill.SetBackgroundColor(XLColor.FromHtml(Commons.BgColorStore));
}
}
}
//Set Border
ws.Range("A1:H" + (row - 1)).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws.Range("A1:H" + (row - 1)).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Columns().AdjustToContents();
ws.Column(1).Width = 30;
ws.Column(2).Width = 15;
ws.Column(4).Width = 15;
return(wb);
}
public void Init()
{
// Make sure tests run on a deterministic culture
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
workbook = SetupWorkbook();
}
public static void GenerateAndExport(string path, TECBid bid)
{
XLWorkbook workbook = new XLWorkbook();
throw new NotImplementedException();
}
//public static List<CatorceTerViewModel> GetCatorceTerDesdeVoucher(FacturaPoliContext db, ClientesContablesModel ObjCliente)
//{
// IQueryable<VoucherModel> QueryIandE = db.DBVoucher.Where(x => x.)
// return null;
//}
public static byte[] GetExcelCatorceTer(List <CatorceTerViewModel> lstCatorceTer, ClientesContablesModel objCliente, bool InformarMembrete, SessionParaExcel Fechas)
{
string RutaEsteExcel = ParseExtensions.Get_AppData_Path("14Ter.xlsx");
byte[] ExcelByteArray = null;
using (XLWorkbook excelFile = new XLWorkbook(RutaEsteExcel))
{
var workSheet = excelFile.Worksheet(1);
if (InformarMembrete == true)
{
workSheet.Cell("A1").Value = objCliente.RazonSocial;
workSheet.Cell("A2").Value = objCliente.RUTEmpresa;
workSheet.Cell("A3").Value = objCliente.Giro;
workSheet.Cell("A4").Value = objCliente.Direccion;
workSheet.Cell("A5").Value = objCliente.Ciudad;
workSheet.Cell("A6").Value = objCliente.Representante;
workSheet.Cell("A7").Value = objCliente.RUTRepresentante;
}
else
{
workSheet.Cell("A1").Value = string.Empty;
workSheet.Cell("A2").Value = string.Empty;
workSheet.Cell("A3").Value = string.Empty;
workSheet.Cell("A4").Value = string.Empty;
workSheet.Cell("A5").Value = string.Empty;
workSheet.Cell("A6").Value = string.Empty;
workSheet.Cell("A7").Value = string.Empty;
}
if (string.IsNullOrWhiteSpace(Fechas.Mes) && string.IsNullOrWhiteSpace(Fechas.Anio))
{
workSheet.Cell("F8").Value = "Informe 14 Ter";
}
else if (!string.IsNullOrWhiteSpace(Fechas.Mes) && !string.IsNullOrWhiteSpace(Fechas.Anio))
{
workSheet.Cell("F8").Value = "Informe 14 Ter" + " " + ParseExtensions.obtenerNombreMes(Convert.ToInt32(Fechas.Mes)) + " " + Fechas.Anio;
}
else if (!string.IsNullOrWhiteSpace(Fechas.Anio) && string.IsNullOrWhiteSpace(Fechas.Mes))
{
workSheet.Cell("F8").Value = "Informe 14 Ter" + " " + Fechas.Anio;
}
workSheet.Column("B").Style.DateFormat.Format = "dd-MM-yyyy";
workSheet.Columns("G:H").Style.NumberFormat.Format = "#,##0 ;-#,##0";
workSheet.Columns("A", "K").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
int NumeroFilaExcel = 13;
int Correlativo = 1;
foreach (var itemTer in lstCatorceTer)
{
if (itemTer.TotalIngreso > 0 || itemTer.TotalEgreso > 0)
{
workSheet.Cell(NumeroFilaExcel, "F").Value = "TOTAL:";
workSheet.Cell(NumeroFilaExcel, "G").Value = itemTer.TotalIngreso;
workSheet.Cell(NumeroFilaExcel, "H").Value = itemTer.TotalEgreso;
}
else
{
workSheet.Cell(NumeroFilaExcel, "A").Value = Correlativo;
workSheet.Cell(NumeroFilaExcel, "B").Value = itemTer.Fecha;
if (itemTer.TipoDocumento != 0)
{
workSheet.Cell(NumeroFilaExcel, "C").Value = itemTer.TipoDocumento.ToString();
}
else
{
workSheet.Cell(NumeroFilaExcel, "C").Value = "Otros";
}
workSheet.Cell(NumeroFilaExcel, "D").Value = itemTer.Folio;
workSheet.Cell(NumeroFilaExcel, "E").Value = itemTer.NombreReceptor;
workSheet.Cell(NumeroFilaExcel, "F").Value = itemTer.RutReceptor;
workSheet.Cell(NumeroFilaExcel, "G").Value = itemTer.Ingreso;
workSheet.Cell(NumeroFilaExcel, "H").Value = itemTer.Egreso;
workSheet.Cell(NumeroFilaExcel, "I").Value = itemTer.CuentaContable;
}
workSheet.Range("A" + NumeroFilaExcel + ":K" + NumeroFilaExcel).Rows
().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
workSheet.Range("A" + NumeroFilaExcel + ":K" + NumeroFilaExcel).Rows
().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
Correlativo++;
NumeroFilaExcel++;
}
ExcelByteArray = ParseExtensions.GetExcelStream(excelFile);
}
if (ExcelByteArray == null)
{
return(null);
}
else
{
return(ExcelByteArray);
}
}
internal static void AddDevicesSheet(XLWorkbook workbook, TECBid bid, string sheetName = "Devices")
{
IXLWorksheet worksheet = workbook.Worksheets.Add(sheetName);
throw new NotImplementedException();
}
public XLCalcEngine(XLWorkbook wb)
{
_wb = wb;
IdentifierChars = "$:!";
}
public void Search_Wildcard_String_Not_Found()
{
Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""soft?2010"", ""Microsoft Excel 2010"")"), Throws.TypeOf <ArgumentException>());
}
