protected override void RenderTitle()
{
DataTable dataSource = DataSource.Tables["TitleDataTable"];
if (dataSource != null)
{
/* NOTE: do coding here. */
rowIndex += 2;
columnIndex = documentStat.StartColumnIndex + 2;
document.SetRowHeight(rowIndex, 120);
document.SetCellValue(rowIndex, columnIndex, dataSource.Rows[0][0].ToString());
Int32 endColumnIndex = columnIndex + dataSource.Columns.Count;
SLStyle style = document.CreateStyle();
style.Font = new SLFont {
FontName = "Arial",
Bold = true,
FontSize = 20,
FontColor = System.Drawing.Color.Black
};
style.SetWrapText(false);
document.AutoFitRow(rowIndex);
style.RemoveFill();
style.RemoveBorder();
document.SetCellStyle(rowIndex, columnIndex, style);
document.MergeWorksheetCells(rowIndex, columnIndex, rowIndex, endColumnIndex);
rowIndex++;
}
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 3;
slDocument.SetCellValue(iRow, 1, "ID");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "ID Anggota");
slDocument.SetColumnWidth(2, 15);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Nama Anggota");
slDocument.SetColumnWidth(3, 25);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Status");
slDocument.SetColumnWidth(5, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(3, 1, iRow, 5, headerStyle);
return(slDocument);
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <MaterialDetailViewModel> listData)
{
int iRow = 4; //starting row data
foreach (var data in listData)
{
string materialUom = "";
foreach (var materialUomDetailse in data.MaterialUom)
{
materialUom += materialUomDetailse.Meinh + " - " + materialUomDetailse.UmrenStr + Environment.NewLine;
}
slDocument.SetCellValue(iRow, 1, data.PlantName);
slDocument.SetCellValue(iRow, 2, data.MaterialNumber);
slDocument.SetCellValue(iRow, 3, data.MaterialGroup);
slDocument.SetCellValue(iRow, 4, data.MaterialDesc);
slDocument.SetCellValue(iRow, 5, data.PurchasingGroup);
slDocument.SetCellValue(iRow, 6, data.UomName);
slDocument.SetCellValue(iRow, 7, data.GoodTypeName);
slDocument.SetCellValue(iRow, 8, data.IssueStorageLoc);
if (data.Tariff == null)
{
slDocument.SetCellValue(iRow, 9, string.Empty);
}
else
{
slDocument.SetCellValue(iRow, 9, data.Tariff.Value.ToString("N2"));
}
slDocument.SetCellValue(iRow, 10, data.Tariff_Curr);
if (data.Hje == null)
{
slDocument.SetCellValue(iRow, 11, string.Empty);
}
else
{
slDocument.SetCellValue(iRow, 11, data.Hje.Value.ToString("N2"));
}
slDocument.SetCellValue(iRow, 12, data.Hje_Curr);
slDocument.SetCellValue(iRow, 13, materialUom);
slDocument.SetCellValue(iRow, 14, data.IsPlantDelete ? "Yes" : "No");
slDocument.SetCellValue(iRow, 15, data.IsClientDelete ? "Yes" : "No");
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.SetWrapText(true);
slDocument.AutoFitColumn(1, 15);
slDocument.SetCellStyle(4, 1, iRow - 1, 15, valueStyle);
return(slDocument);
}
public static void WriteReportHeader(string fileName, string testMode, Dictionary <string, string> dictionary)
{
try
{
if (File.Exists(fileName) == false)
{
using (SLDocument s = new SLDocument())
{
s.SaveAs(fileName);
}
}
using (SLDocument sl = new SLDocument(fileName))
{
SLStyle headerstyle = sl.CreateStyle();
headerstyle.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
headerstyle.Font.FontSize = 18;
headerstyle.Font.FontName = "宋体";
headerstyle.Font.Bold = true;
SLStyle secondrow_header = sl.CreateStyle();
secondrow_header.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
secondrow_header.SetWrapText(true);
secondrow_header.SetVerticalAlignment(VerticalAlignmentValues.Center);
secondrow_header.Font.FontSize = 12;
secondrow_header.Font.FontName = "宋体";
string rowindex = "1";
string lastCol = "A";
char endRow = Convert.ToChar('A' + dictionary.Keys.Count);
sl.SetCellValue("A1", testMode);
sl.MergeWorksheetCells("A1", endRow.ToString() + '1');
sl.SetCellStyle("A1", headerstyle);
rowindex = "2";
lastCol = "A";
sl.SetCellStyle("A2", endRow.ToString() + '2', secondrow_header);
foreach (var item in dictionary)
{
sl.SetCellValue(lastCol + rowindex, item.Key);
lastCol = getNextColumnCellName(lastCol);
}
sl.Save();
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 10;
int col = 1;
slDocument.SetCellValue(iRow, col++, "Id");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, col++, "Id Anggota");
slDocument.SetColumnWidth(2, 20);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, col, "Nama Anggota");
slDocument.SetColumnWidth(3, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, col, "Tanggal Gabung");
slDocument.SetColumnWidth(4, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, col, "Pokok");
slDocument.SetColumnWidth(5, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 5, iRow + 1, 5);
slDocument.SetCellValue(iRow, col, "Wajib");
slDocument.SetColumnWidth(6, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Sukarela");
slDocument.SetColumnWidth(7, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Belanja");
slDocument.SetColumnWidth(8, 15);
col++;
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, col, "Bunga Pinjaman");
slDocument.SetColumnWidth(9, 15);
col++;
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, headerStyle);
return(slDocument);
}
public static void WriteReportHeader(string fileName, string testMode, string[] header)
{
try
{
if (File.Exists(fileName) == false)
{
using (SLDocument s = new SLDocument())
{
s.SaveAs(fileName);
}
}
using (SLDocument sl = new SLDocument(fileName))
{
SLStyle headerstyle = sl.CreateStyle();
headerstyle.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
headerstyle.Font.FontSize = 18;
headerstyle.Font.FontName = "宋体";
headerstyle.Font.Bold = true;
SLStyle secondrow_header = sl.CreateStyle();
secondrow_header.Alignment.Horizontal = DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center;
secondrow_header.SetWrapText(true);
secondrow_header.SetVerticalAlignment(VerticalAlignmentValues.Center);
secondrow_header.Font.FontSize = 12;
secondrow_header.Font.FontName = "宋体";
string rowindex = "1";
string lastCol = "A";
sl.SetCellValue("A1", testMode);
sl.MergeWorksheetCells("A1", "M1");
sl.SetCellStyle("A1", headerstyle);
rowindex = "2";
lastCol = "A";
sl.SetCellStyle("A2", "M2", secondrow_header);
for (int i = 0; i < header.Length; i++)
{
sl.SetCellValue(lastCol + rowindex, header[i]);
lastCol = getNextColumnCellName(lastCol);
}
sl.Save();
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
internal void FormatColumns <T>(List <PropertyInfo> properties, IEnumerable <T> records, bool makeTable, IEnumerable <int> hideColumns, DisplayNoWrap[] wrapAttributes, DisplayWidth[] widthAttributes)
{
int skippedColumns = 0;
int columnIndex = 0;
for (int k = 0; k < properties.Count; k++)
{
if (!hideColumns.Contains(k))
{
SLStyle style = new SLStyle();
if (wrapAttributes[k] != null)
{
style.SetWrapText(false);
}
else
{
style.SetWrapText(true);
}
var widthAttribute = widthAttributes[k];
if (widthAttribute != null)
{
doc.SetColumnWidth(columnIndex + 1, widthAttribute.Width);
}
doc.SetColumnStyle(columnIndex + 1, style);
columnIndex++;
}
else
{
skippedColumns++;
}
}
if (makeTable)
{
SLTable tbl = doc.CreateTable(1, 1, records.Count() + 1, properties.Count() - skippedColumns);
tbl.SetTableStyle(SLTableStyleTypeValues.Light1);
doc.InsertTable(tbl);
}
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <VirtualNppbckDetails> listData)
{
int iRow = 3; //starting row data
var listPlants = _plantBll.GetAll();
foreach (var data in listData)
{
string plantDesc = "";
var plant = listPlants.Where(c => c.NPPBKC_ID == data.VirtualNppbckId);
foreach (var plant1 in plant)
{
plantDesc += plant1.WERKS + "-" + plant1.ORT01 + Environment.NewLine;
}
slDocument.SetCellValue(iRow, 1, data.VirtualNppbckId);
slDocument.SetCellValue(iRow, 2, data.Address1);
slDocument.SetCellValue(iRow, 3, data.Address2);
slDocument.SetCellValue(iRow, 4, data.City);
slDocument.SetCellValue(iRow, 5, data.CityAlias);
slDocument.SetCellValue(iRow, 6, data.RegionOfficeOfDGCE);
slDocument.SetCellValue(iRow, 7, data.TextTo);
slDocument.SetCellValue(iRow, 8, data.KppbcId);
slDocument.SetCellValue(iRow, 9, data.Region);
slDocument.SetCellValue(iRow, 10, data.AcountNumber);
slDocument.SetCellValue(iRow, 11, Utils.ConvertHelper.ConvertDateToStringddMMMyyyy(data.StartDate));
slDocument.SetCellValue(iRow, 12, Utils.ConvertHelper.ConvertDateToStringddMMMyyyy(data.EndDate));
slDocument.SetCellValue(iRow, 13, data.FlagForLack1 ? "Yes" : "No");
slDocument.SetCellValue(iRow, 14, plantDesc);
slDocument.SetCellValue(iRow, 15, data.Is_Deleted);
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.SetWrapText(true);
slDocument.AutoFitColumn(1, 15);
slDocument.SetCellStyle(3, 1, iRow - 1, 15, valueStyle);
return(slDocument);
}
private SLDocument CreateDataExcel(SLDocument slDocument, List <DetailPlantT1001W> listData)
{
int iRow = 3; //starting row data
foreach (var data in listData)
{
data.ReceiveMaterials = GetPlantReceiveMaterial(data);
var receiveMaterial = "";
foreach (var plantReceiveMaterialItemModel in data.ReceiveMaterials)
{
if (plantReceiveMaterialItemModel.IsChecked)
{
receiveMaterial += plantReceiveMaterialItemModel.EXT_TYP_DESC + Environment.NewLine;
}
}
slDocument.SetCellValue(iRow, 1, data.Werks);
slDocument.SetCellValue(iRow, 2, data.NPPBKC_ID);
slDocument.SetCellValue(iRow, 3, data.PlantDescription);
slDocument.SetCellValue(iRow, 4, data.Address);
slDocument.SetCellValue(iRow, 5, data.Ort01);
slDocument.SetCellValue(iRow, 6, data.Skeptis);
slDocument.SetCellValue(iRow, 7, data.IsMainPlant ? "Yes" : "No");
slDocument.SetCellValue(iRow, 8, receiveMaterial);
slDocument.SetCellValue(iRow, 9, data.Phone);
slDocument.SetCellValue(iRow, 10, data.NPPBKC_IMPORT_ID);
slDocument.SetCellValue(iRow, 11, data.IsDeletedString);
iRow++;
}
//create style
SLStyle valueStyle = slDocument.CreateStyle();
valueStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
valueStyle.SetWrapText(true);
slDocument.AutoFitColumn(1, 11);
slDocument.SetCellStyle(3, 1, iRow - 1, 11, valueStyle);
return(slDocument);
}
private SLDocument CreateHeaderExcel(SLDocument slDocument)
{
int iRow = 10;
slDocument.SetCellValue(iRow, 1, "ID Anggota");
slDocument.SetColumnWidth(1, 10);
//slDocument.MergeWorksheetCells(iRow, 1, iRow + 1, 1);
slDocument.SetCellValue(iRow, 2, "Nama Anggota");
slDocument.SetColumnWidth(2, 20);
//slDocument.MergeWorksheetCells(iRow, 2, iRow + 1, 2);
slDocument.SetCellValue(iRow, 3, "Total Simpanan");
slDocument.SetColumnWidth(3, 15);
//slDocument.MergeWorksheetCells(iRow, 3, iRow + 1, 3);
slDocument.SetCellValue(iRow, 4, "Total Belanja");
slDocument.SetColumnWidth(4, 15);
//slDocument.MergeWorksheetCells(iRow, 4, iRow + 1, 4);
slDocument.SetCellValue(iRow, 5, "Total Pinjaman");
slDocument.SetColumnWidth(5, 15);
//slDocument.MergeWorksheetCells(iRow, 5, iRow + 1, 5);
slDocument.SetCellValue(iRow, 6, "JMA");
slDocument.SetColumnWidth(6, 15);
//slDocument.MergeWorksheetCells(iRow, 6, iRow + 1, 6);
slDocument.SetCellValue(iRow, 7, "JUA");
slDocument.SetColumnWidth(7, 15);
slDocument.SetCellValue(iRow, 8, "JPA");
slDocument.SetColumnWidth(8, 15);
slDocument.SetCellValue(iRow, 9, "Total");
slDocument.SetColumnWidth(9, 15);
SLStyle headerStyle = slDocument.CreateStyle();
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
//headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.LightGreen);
headerStyle.SetWrapText(true);
headerStyle.SetVerticalAlignment(VerticalAlignmentValues.Center);
headerStyle.Font.FontSize = 10;
slDocument.SetCellStyle(10, 1, iRow, 9, headerStyle);
return(slDocument);
}
private void GenerateXlsFromTestPlan(String path, List <GeneralUseStructure> listPlanStructure)
{
#region Styles and SLDocument
SLDocument xlsx = new SLDocument();
SLStyle style = xlsx.CreateStyle();
style.SetWrapText(true);
SLStyle indexStyle = xlsx.CreateStyle();
indexStyle.Alignment.Horizontal = HorizontalAlignmentValues.Right;
indexStyle.SetWrapText(true);
SLStyle headerStyle = xlsx.CreateStyle();
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Yellow, System.Drawing.Color.Black);
headerStyle.Font.Bold = true;
headerStyle.Font.FontName = "Calibri";
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
#endregion
#region Populate Excel
foreach (GeneralUseStructure planStructure in listPlanStructure)
{
TestPlan testPlan = (TestPlan)planStructure;
for (int k = 0; k < testPlan.TestCases.Count; k++)
{
TestCase testCase = testPlan.TestCases[k];
xlsx.SetColumnStyle(1, 1, style);
//xlsx.SetColumnWidth(1, 1, 50);
String title = testCase.Title;
title = title.Replace(" ", "");
if (title.Length > 31)
{
title = title.Substring(0, 25) + title.Substring(title.Length - 5, 5);
}
xlsx.AddWorksheet(HttpUtility.UrlDecode(title));
xlsx.SelectWorksheet(title);
xlsx.SetCellValue(1, 1, "Test Case #");
xlsx.SetCellValue(1, 2, "Work Item ID");
xlsx.SetCellValue(1, 3, "Test Title");
xlsx.SetCellValue(1, 4, "Summary");
xlsx.SetCellValue(1, 5, "Test Step");
xlsx.SetCellValue(1, 6, "Action/Description");
xlsx.SetCellValue(1, 7, "Expected Results");
xlsx.SetCellValue(1, 8, "Assigned To");
xlsx.SetCellValue(1, 9, "State");
xlsx.SetCellValue(1, 10, "Reason");
xlsx.SetCellValue(1, 11, "Iteration Path");
xlsx.SetCellValue(1, 12, "Area Path");
xlsx.SetCellValue(1, 13, "Application");
xlsx.SetCellValue(1, 14, "Complexity");
xlsx.SetCellValue(1, 15, "Risks");
xlsx.SetCellValue(1, 16, "TC Lifecycle");
xlsx.SetCellValue(1, 17, "Lifecycle Type");
xlsx.SetCellValue(1, 18, "TC Team Usage");
xlsx.SetCellStyle("A1", "R1", headerStyle);
xlsx.SetCellValue(2, 1, "TC" + testCase.WorkItemId);
xlsx.SetCellStyle(2, 1, style);
xlsx.SetCellValue(2, 2, "Test Case " + HttpUtility.UrlDecode(testCase.WorkItemId.ToString()));
xlsx.SetCellStyle(2, 2, style);
if (k != (testPlan.TestCases.Count - 1))
{
xlsx.SetCellValue(2, 3, HttpUtility.UrlDecode(testCase.Title));
xlsx.SetCellStyle(2, 3, style);
}
xlsx.SetCellValue(2, 4, HttpUtility.UrlDecode(testCase.Summary));
xlsx.SetCellStyle(2, 4, style);
xlsx.SetCellValue(2, 5, "1");
xlsx.SetCellStyle(2, 5, indexStyle);
xlsx.SetCellValue(2, 6, HttpUtility.UrlDecode(testCase.TDpreConditions));
xlsx.SetCellStyle(2, 6, style);
xlsx.SetCellValue(2, 7, HttpUtility.UrlDecode(testCase.TDpostConditions));
xlsx.SetCellStyle(2, 7, style);
xlsx.SetCellValue(2, 8, HttpUtility.UrlDecode(testCase.TDassigned));
xlsx.SetCellStyle(2, 8, style);
xlsx.SetCellValue(2, 9, HttpUtility.UrlDecode(testCase.TDstate));
xlsx.SetCellStyle(2, 9, style);
xlsx.SetCellValue(2, 10, HttpUtility.UrlDecode(testCase.TDreason));
xlsx.SetCellStyle(2, 10, style);
xlsx.SetCellValue(2, 11, HttpUtility.UrlDecode(testCase.TDiterationPath));
xlsx.SetCellStyle(2, 11, style);
xlsx.SetCellValue(2, 12, HttpUtility.UrlDecode(testCase.TDareaPath));
xlsx.SetCellStyle(2, 12, style);
xlsx.SetCellValue(2, 13, HttpUtility.UrlDecode(testCase.TDapplication));
xlsx.SetCellStyle(2, 13, style);
xlsx.SetCellValue(2, 14, HttpUtility.UrlDecode(testCase.TDcomplexity));
xlsx.SetCellStyle(2, 14, style);
xlsx.SetCellValue(2, 15, HttpUtility.UrlDecode(testCase.TDrisks));
xlsx.SetCellStyle(2, 15, style);
xlsx.SetCellValue(2, 16, HttpUtility.UrlDecode(testCase.TDtcLifecycle));
xlsx.SetCellStyle(2, 16, style);
xlsx.SetCellValue(2, 17, HttpUtility.UrlDecode(testCase.TDlifecycleType));
xlsx.SetCellStyle(2, 17, style);
xlsx.SetCellValue(2, 18, HttpUtility.UrlDecode(testCase.TDtcTeamUsage));
xlsx.SetCellStyle(2, 18, style);
#region Step
for (int i = 0; i < testCase.TestSteps.Count; i++)
{
TestStep step = testCase.TestSteps[i];
if (k == (testPlan.TestCases.Count - 1))
{
xlsx.SetCellValue(i + 2, 3, HttpUtility.UrlDecode(step.Title));
xlsx.SetCellStyle(i + 2, 3, style);
}
if (!testCase.WriteFirstLine)
{
xlsx.SetCellValue(i + 2, 5, HttpUtility.UrlDecode(step.Index));
xlsx.SetCellStyle(i + 2, 5, indexStyle);
if (step.Description.Contains("$@#ITERATION@#"))
{
String[] aux = step.Description.Split('$');
xlsx.SetCellValue(i + 2, 6, HttpUtility.UrlDecode(aux[0]));
}
else
{
xlsx.SetCellValue(i + 2, 6, HttpUtility.UrlDecode(step.Description));
}
xlsx.SetCellStyle(i + 2, 6, style);
if (step.ExpectedResult.Contains("$@#ITERATION@#"))
{
String[] aux = step.ExpectedResult.Split('$');
xlsx.SetCellValue(i + 2, 7, HttpUtility.UrlDecode(aux[0]));
}
else
{
xlsx.SetCellValue(i + 2, 7, HttpUtility.UrlDecode(step.ExpectedResult));
}
xlsx.SetCellStyle(i + 2, 7, style);
}
else
{
xlsx.SetCellValue(i + 3, 5, HttpUtility.UrlDecode((Int32.Parse(step.Index) + 1).ToString()));
xlsx.SetCellStyle(i + 3, 5, indexStyle);
if (step.Description.Contains("$@#ITERATION@#"))
{
String[] aux = step.Description.Split('$');
xlsx.SetCellValue(i + 3, 6, HttpUtility.UrlDecode(aux[0]));
}
else
{
xlsx.SetCellValue(i + 3, 6, HttpUtility.UrlDecode(step.Description));
}
xlsx.SetCellStyle(i + 3, 6, style);
if (step.ExpectedResult.Contains("$@#ITERATION@#"))
{
String[] aux = step.ExpectedResult.Split('$');
xlsx.SetCellValue(i + 3, 7, HttpUtility.UrlDecode(aux[0]));
}
else
{
xlsx.SetCellValue(i + 3, 7, HttpUtility.UrlDecode(step.ExpectedResult));
}
xlsx.SetCellStyle(i + 3, 7, style);
}
if (!testCase.Title.Equals("GeneralTestCase") && step.Index.Equals(1))
{
xlsx.SetCellValue(i + 2, 3, HttpUtility.UrlDecode(step.Title));
}
}
xlsx.AutoFitRow(1, 10000);
xlsx.AutoFitColumn(1, 18);
#endregion
}
}
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
xlsx.SaveAs(path + @"\Plan.xlsx");
#endregion
}
public ActionResult CrearExcelFinanzas(int idTanda)
{
/*string handle = Guid.NewGuid().ToString();
*
* StringBuilder csv = new StringBuilder();
* string Columnas = string.Format(";{0};{1};{2};{3};{4}", "Cantidad", "Producto", "Costo", "Precio", "Diferencia");
* csv.AppendLine(Columnas);
*
*
* TanoNEEntities ctx = new TanoNEEntities();
* Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
*
*
* var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().ToList();
* foreach (var local in locales)
* {
* csv.AppendLine(local.direccion);
* decimal costoLocal = 0;
* decimal precioLocal = 0;
* var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
*
* Costos ultimoc = prod.Costos.Count > 1 ? prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Costos.FirstOrDefault();
* decimal costo = ultimoc.costo * compra.Cantidad;
* //decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
*
* Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
* decimal precio = ultimop.precio * compra.Cantidad;
* //decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
* costoLocal += costo;
* precioLocal += precio;
* string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
* csv.AppendLine(string.Format(";;;${0};${1};${2}", costoLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precioLocal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precioLocal - costoLocal).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture)));
* }
* //string cierre = string.Format("Total;;;${0}", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* //csv.AppendLine(cierre);
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* TempData[handle] = memoryStream.ToArray();
* }
*
* return Json(new { FileGuid = handle, FileName = "Reporte.csv" });*/
string handle = Guid.NewGuid().ToString();
TanoNEEntities ctx = new TanoNEEntities();
Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
EstadosCompra entre = ctx.EstadosCompra.FirstOrDefault(a => a.codigo == 3);
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Finanzas");
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
SLStyle negrita = sl.CreateStyle();
negrita.Font.Bold = true;
int row = 3;
sl.SetColumnWidth(1, 16);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 15);
sl.SetColumnWidth(5, 15);
sl.SetColumnWidth(6, 15);
sl.SetCellValue(1, 1, "Local");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Costo");
sl.SetCellValue(1, 5, "Precio");
sl.SetCellValue(1, 6, "Diferencia");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
var locales = ctx.Compras.Where(a => a.tandaId == idTanda && a.estadoId != entre.idEstadoCompra).Select(a => a.Locales).Distinct().OrderBy(a => new { a.comuna, a.nombre }).ToList();
foreach (var local in locales)
{
var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad), CompraProducto = a.FirstOrDefault() }).ToArray();
int totalVecinx = (row + listado.Count() - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 6, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 6, bordeAb);
for (int x = 1; x < 7; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, local.direccion + (local.nombre != null ? "\n" + local.nombre : ""));
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
decimal costoLocal = 0;
decimal precioLocal = 0;
for (int x = 0; x < listado.Count(); x++)
{
var compra = listado[x];
Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
Costos ultimoc = compra.CompraProducto.Costos;
decimal costo = ultimoc.costo * compra.Cantidad;
//decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
//Precios ultimop = prod.Precios.Count > 1 ? prod.Precios.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Precios.FirstOrDefault();
decimal precio = compra.CompraProducto.Precios.precio * compra.Cantidad;
//decimal precio = prod.Precios.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).precio * compra.Cantidad;
costoLocal += costo;
precioLocal += precio;
//string filas = string.Format(";{0};{1};${2};${3};${4}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), precio.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture), (precio - costo).ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
sl.SetCellValue(row, 2, compra.Cantidad);
sl.SetCellValue(row, 3, prod.producto + " - " + prod.marca + " - " + prod.presentacion);
sl.SetCellValue(row, 4, costo);
sl.SetCellValue(row, 5, precio);
sl.SetCellValue(row, 6, precio - costo);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
}
sl.SetCellValue(row, 3, "Totales por local: ");
sl.SetCellValue(row, 4, costoLocal);
sl.SetCellValue(row, 5, precioLocal);
sl.SetCellValue(row, 6, precioLocal - costoLocal);
sl.SetCellStyle(row, 3, negrita);
sl.SetCellStyle(row, 4, negrita);
sl.SetCellStyle(row, 5, negrita);
sl.SetCellStyle(row, 6, negrita);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 6, centrado);
row++;
row++;
}
sl.SaveAs(mem);
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(Json(new { FileGuid = handle, FileName = "Finanzas.xlsx" }));
}
}
public static SLDocument PrintDepartmentReport(List <PublicationOnDepartment> model)
{
var folder = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
var path = Path.Combine(folder, "DepartmentReport.xlsx");
SLDocument sl = new SLDocument(path, "Report");
SLStyle styleWhite = sl.GetCellStyle("A6");
styleWhite.SetWrapText(true);
SLStyle styleGreen = sl.GetCellStyle("H6");
styleGreen.SetWrapText(true);
for (int i = 0; i < model.Count; i++)
{
sl.SetCellValue($"A{i + 6}", i + 1);
sl.SetCellStyle($"A{i + 6}", styleWhite);
var authors = model[i].Collaborators[0].Name;
foreach (var aut in model[i].Collaborators.Skip(1))
{
authors += ", " + aut.Name;
}
sl.SetCellValue($"B{i + 6}", authors);
sl.SetCellStyle($"B{i + 6}", styleWhite);
sl.SetCellValue($"C{i + 6}", model[i].Name);
sl.SetCellStyle($"C{i + 6}", styleWhite);
sl.SetCellValue($"D{i + 6}", model[i].PublicationType);
sl.SetCellStyle($"D{i + 6}", styleWhite);
sl.SetCellValue($"E{i + 6}", model[i].Output);
sl.SetCellStyle($"E{i + 6}", styleWhite);
sl.SetCellValue($"F{i + 6}", model[i].Pages);
sl.SetCellStyle($"F{i + 6}", styleWhite);
sl.SetCellValue($"G{i + 6}", model[i].Pages);
sl.SetCellStyle($"G{i + 6}", styleWhite);
sl.SetCellValue($"H{i + 6}", model[i].IsOverseas ? "Так" : "Нi");
sl.SetCellStyle($"H{i + 6}", styleGreen);
sl.SetCellValue($"I{i + 6}", model[i].NMBD);
sl.SetCellStyle($"I{i + 6}", styleGreen);
sl.SetCellValue($"J{i + 6}", model[i].CitationNumberNMBD);
sl.SetCellStyle($"J{i + 6}", styleGreen);
sl.SetCellValue($"K{i + 6}", model[i].ImpactFactorNMBD);
sl.SetCellStyle($"K{i + 6}", styleGreen);
sl.SetCellValue($"L{i + 6}", model[i].ResearchDoneType);
sl.SetCellStyle($"L{i + 6}", styleWhite);
sl.SetCellValue($"M{i + 6}", model[i].DepartmentName);
sl.SetCellStyle($"M{i + 6}", styleGreen);
}
var title = sl.GetCellValueAsString("C5");
title += " кафедри " + model[0].DepartmentName;
if (model[0].Start != null && model[0].End != null)
{
var period = model[0].Start.Value.ToShortDateString().Replace('/', '.')
+ " - "
+ model[0].End.Value.ToShortDateString().Replace('/', '.');
title += " за перiод " + period;
}
sl.SetCellValue("C2", title);
return(sl);
}
//TestScript excel generation
private void GenerateXlsTestScript(UmlModel model)
{
int currentLine = 0;
String modelName = model.Name;
String directory = CreateTestScriptDirectory();
#region Styles and SLDocument
SLDocument xlsx = new SLDocument();
SLStyle style = xlsx.CreateStyle();
style.SetWrapText(true);
SLStyle style_aux = xlsx.CreateStyle();
style_aux.SetWrapText(true);
style_aux.Alignment.Horizontal = HorizontalAlignmentValues.Center;
SLStyle headerStyle = xlsx.CreateStyle();
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(0x8EB4E3), System.Drawing.Color.Black);
headerStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
headerStyle.Font.Bold = true;
headerStyle.Font.FontName = "Calibri";
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
#endregion
if (model.Diagrams.Count(X => X is UmlActivityDiagram) > 1)
{
throw new Exception("Only one Activity diagram allowed! (For now)");
}
UmlActivityDiagram act = model.Diagrams.OfType <UmlActivityDiagram>().First();
if (act.Name.Length > 31)
{
String aux = act.Name;
aux = aux.Substring(0, 25) + aux.Substring(aux.Length - 5, 5);
aux = HttpUtility.UrlDecode(aux);
xlsx.AddWorksheet(aux);
xlsx.SelectWorksheet(aux);
}
else
{
xlsx.AddWorksheet(HttpUtility.UrlDecode(act.Name));
xlsx.SelectWorksheet(HttpUtility.UrlDecode(act.Name));
}
ReadXLsx read = new ReadXLsx();
//Must be in the output folder
KeywordDictionary dic = read.Leitor(@"KeyWords_2_2.xlsx");
#region Header
xlsx.SetCellValue(1, 1, "StepRunMode");
xlsx.SetCellValue(1, 2, "Keyword");
xlsx.SetCellValue(1, 3, "Object");
xlsx.SetCellValue(1, 4, "ActionValue1");
xlsx.SetCellValue(1, 5, "ManualStepDescription");
xlsx.SetCellValue(1, 6, "StopOnFail");
xlsx.SetCellStyle("A1", "F1", headerStyle);
#endregion
#region Dados Fixos
xlsx.SetCellValue(2, 1, "r");
xlsx.SetCellStyle(2, 1, style_aux);
xlsx.SetCellValue(2, 2, "calludf");
xlsx.SetCellStyle(2, 2, style);
xlsx.SetCellValue(2, 3, "setbasestate");
xlsx.SetCellStyle(2, 3, style);
xlsx.SetCellValue(3, 1, "w");
xlsx.SetCellStyle(3, 1, style_aux);
xlsx.SetCellValue(3, 2, "perform");
xlsx.SetCellStyle(3, 2, style);
xlsx.SetCellValue(3, 3, "browser;*about:blank*");
xlsx.SetCellStyle(3, 3, style);
xlsx.SetCellValue(3, 4, "navigate;dt_URL");
xlsx.SetCellStyle(3, 4, style);
xlsx.SetCellValue(4, 1, "w");
xlsx.SetCellStyle(4, 1, style_aux);
xlsx.SetCellValue(4, 2, "perform");
xlsx.SetCellStyle(4, 2, style);
xlsx.SetCellValue(4, 3, "browser;*about:blank*");
xlsx.SetCellStyle(4, 3, style);
xlsx.SetCellValue(4, 4, "waitforpage;dt_MaxWebPageSyncTime");
xlsx.SetCellStyle(4, 4, style);
currentLine = 4;
#endregion
for (int i = 0; i < act.UmlObjects.OfType <UmlTransition>().Count(); i++)
{
UmlTransition tran = act.UmlObjects.OfType <UmlTransition>().ElementAt(i);
String[] tdAction = { "" };
String tdObject = "";
if (tran.Target is UmlFinalState)
{
continue;
}
foreach (KeyValuePair <String, String> pair in tran.TaggedValues)
{
if (pair.Key.Equals("TDACTION"))
{
tdAction = HttpUtility.UrlDecode(pair.Value).Split(',');
}
if (pair.Key.Equals("TDOBJECT"))
{
tdObject = HttpUtility.UrlDecode(pair.Value);
tdObject = tdObject.Substring(1);
tdObject = tdObject.Substring(0, tdObject.Length - 1);
}
}
for (int j = 0; j < tdAction.Count(); j++)
{
//if (!tdObject.Split(';')[0].Equals("window"))
//{
String tdActionSingle = tdAction[j];
tdActionSingle = tdActionSingle.Substring(1);
tdActionSingle = tdActionSingle.Substring(0, tdActionSingle.Length - 1);
String[] tdActionAux = tdActionSingle.Split(';');
if (tdActionAux[0].Equals("wait"))
{
xlsx.SetCellValue(currentLine + 1, 1, "w");
xlsx.SetCellStyle(currentLine + 1, 1, style_aux);
xlsx.SetCellValue(currentLine + 1, 2, tdActionAux[0]);
xlsx.SetCellStyle(currentLine + 1, 2, style);
xlsx.SetCellValue(currentLine + 1, 3, tdActionAux[1]);
xlsx.SetCellStyle(currentLine + 1, 3, style);
xlsx.SetCellValue(currentLine + 1, 4, "");
xlsx.SetCellStyle(currentLine + 1, 4, style);
}
else
{
String tdObjectToCompare = tdObject.Split(';')[0];
String tdActionToCompare = tdActionSingle.Split(';')[0];
tdObjectToCompare = tdObjectToCompare.ToLower();
tdActionToCompare = tdActionToCompare.ToLower();
String keyword = dic.GetKeyword(tdObjectToCompare, tdActionToCompare);
xlsx.SetCellValue(currentLine + 1, 1, "w");
xlsx.SetCellStyle(currentLine + 1, 1, style_aux);
xlsx.SetCellValue(currentLine + 1, 2, keyword);
xlsx.SetCellStyle(currentLine + 1, 2, style);
xlsx.SetCellValue(currentLine + 1, 3, tdObject);
xlsx.SetCellStyle(currentLine + 1, 3, style);
xlsx.SetCellValue(currentLine + 1, 4, tdActionSingle);
xlsx.SetCellStyle(currentLine + 1, 4, style);
}
currentLine++;
//}
}
}
xlsx.AutoFitColumn(1, 6);
xlsx.AutoFitRow(1, currentLine);
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
if (!File.Exists(directory + @"\" + modelName + "_TestScript.xlsx"))
{
xlsx.SaveAs(directory + @"\" + modelName + "_TestScript.xlsx");
}
else
{
int i = 1;
string name;
string[] sub;
string[] files = System.IO.Directory.GetFiles(directory);
foreach (String file in files)
{
name = System.IO.Path.GetFileNameWithoutExtension(file);
if (name.Contains("("))
{
char[] splitTokens = { '(', ')' };
sub = name.Split(splitTokens);
if (sub[0] == modelName)
{
//descubro o maior index
if (Convert.ToInt16(sub[1]) > i)
{
i = Convert.ToInt16(sub[1]);
}
}
}
}
xlsx.SaveAs(directory + @"\" + modelName + "(" + (i + 1) + ")" + "_TestScript.xlsx");
}
}
public static SLDocument PrintReportForm11(List <PublicationForm11> model, ApplicationUser user)
{
var folder = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
var path = Path.Combine(folder, "Form11.xlsx");
SLDocument sl = new SLDocument(path, "Report");
SLStyle styleWhite = sl.GetCellStyle("A4");
styleWhite.SetWrapText(true);
for (int i = 0; i < model.Count; i++)
{
sl.SetCellValue($"A{i + 4}", i + 1);
sl.SetCellStyle($"A{i + 4}", styleWhite);
sl.SetCellValue($"B{i + 4}", model[i].Name);
sl.SetCellStyle($"B{i + 4}", styleWhite);
sl.SetCellValue($"C{i + 4}", model[i].PublicationType);
sl.SetCellStyle($"C{i + 4}", styleWhite);
sl.SetCellValue($"D{i + 4}", model[i].Output);
sl.SetCellStyle($"D{i + 4}", styleWhite);
sl.SetCellValue($"E{i + 4}", model[i].PublishedAt.ToShortDateString());
sl.SetCellStyle($"E{i + 4}", styleWhite);
sl.SetCellValue($"F{i + 4}", model[i].Pages);
sl.SetCellStyle($"F{i + 4}", styleWhite);
sl.SetCellValue($"G{i + 4}", model[i].Collaborators.Count + 1);
sl.SetCellStyle($"G{i + 4}", styleWhite);
var value = "";
if (model[i].Collaborators.Count > 0)
{
value = model[i].Collaborators[0].Name;
foreach (var lab in model[i].Collaborators.Skip(1))
{
value += ", " + lab.Name;
}
}
else
{
value = "Нет соавторов";
}
sl.SetCellValue($"H{i + 4}", value);
sl.SetCellStyle($"H{i + 4}", styleWhite);
}
var title = $"Наукові публікації {user.LastName} {user.FirstName.FirstOrDefault()}. {user.ThirdName.FirstOrDefault()}.";
sl.SetCellValue("C2", title);
return(sl);
using (ExcelPackage pck = new ExcelPackage())
{
var datasource = CreateForm11(user);
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Публикации " +
$"{user.LastName} {user.FirstName.Substring(0, 1)}. {user.ThirdName.Substring(0, 1)}.");
var frmt = ws.Cells;
frmt.Style.ShrinkToFit = false;
frmt.Style.Indent = 5;
frmt.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
ws.DefaultColWidth = 200;
ws.Column(1).AutoFit(35, 50);
ws.Column(2).AutoFit(35, 50);
ws.Column(3).AutoFit(35, 50);
ws.Column(4).AutoFit(35, 50);
ws.Column(5).AutoFit(35, 50);
ws.Cells[1, 1].Value = "Назва";
ws.Cells[1, 2].Value = "Характер роботи";
ws.Cells[1, 3].Value = "Вихідні дані";
ws.Cells[1, 4].Value = "Обсяг (стор.)";
ws.Cells[1, 5].Value = "Співавтори";
for (int i = 0; i < datasource.Count(); i++)
{
ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).Name;
ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).StoringType;
ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).Output;
ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).Pages;
if (datasource.ElementAt(i).Collaborators.Count > 0)
{
ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).Collaborators[0].Name;
foreach (var lab in datasource.ElementAt(i).Collaborators.Skip(1))
{
ws.Cells[i + 2, 5].Value += ", " + lab.Name;
}
}
else
{
ws.Cells[i + 2, 5].Value = "Нет соавторов";
}
}
using (ExcelRange rng = ws.Cells[1, 1, 1, 5])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(0, 200, 218, 230)); //Set color to DarkGray
rng.Style.Font.Color.SetColor(Color.Black);
}
//return pck.GetAsByteArray();
//pck.SaveAs(new FileInfo(filepath));
}
}
public bool generateExcel(List <List <List <Factura> > > IngresoSemanas, List <List <List <Egreso> > > EgresoSemanas, string folderpath, string fileName, int pYear, int pMonth)
{
bool result = true;
try
{
//decimal TotalIngresos = 0;
//decimal TotalEgresos = 0;
//decimal Total = 0;
SLDocument sl = new SLDocument();
List <Producto> productosTable = new List <Producto>();
int dayMonthIngrsos = 1;
int dayMonthEgresos = 1;
decimal lastTotalIngresos = 0;
decimal lastTotalEgresos = 0;
for (int s = 0; s < IngresoSemanas.Count; s++)
{
int dayWeek = 1;
decimal totalSemana = 0;
List <List <Factura> > SemanaIngresos = IngresoSemanas[s];
List <Detfactura> DetallesSemana = new List <Detfactura>();
List <List <Egreso> > SemanaEgresos = EgresoSemanas[s];
List <Egreso> ConceptosEgresos = new List <Egreso>();
productosTable.Clear();
//procesando ingresos
sl.AddWorksheet("Week " + (s + 1));
sl.MergeWorksheetCells(2, 2, 2, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells("C3", "C4");
//set height for each column
sl.SetRowHeight(1, 25);
sl.SetRowHeight(2, 25);
sl.SetRowHeight(3, 25);
sl.SetRowHeight(4, 25);
//set width for each header cell
sl.SetColumnWidth(2, 20);
sl.SetColumnWidth(3, 17);
sl.SetCellValue("B2", "INGRESOS");
SLStyle styleTitle = sl.CreateStyle();
styleTitle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.Black);
styleTitle.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTitle.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTitle.Font.Bold = true;
styleTitle.Font.FontSize = 11;
sl.SetCellStyle(2, 2, styleTitle);
SLStyle styleProducto = sl.CreateStyle();
styleProducto.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleProducto.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleProducto.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
styleProducto.Font.Bold = false;
styleProducto.Font.FontSize = 10;
SLStyle styleDiaIngreso = sl.CreateStyle();
styleDiaIngreso.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDiaIngreso.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleDiaIngreso.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleDiaIngreso.Font.Bold = false;
styleDiaIngreso.Font.FontSize = 10;
SLStyle styleDates = sl.CreateStyle();
styleDates.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleDates.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleDates.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleDates.Font.Bold = true;
styleDates.Font.FontSize = 10;
styleDates.SetWrapText(true);
SLStyle styleIndexes = sl.CreateStyle();
styleIndexes.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleIndexes.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleIndexes.SetHorizontalAlignment(HorizontalAlignmentValues.Left);
styleIndexes.Font.Bold = true;
styleIndexes.Font.FontSize = 10;
SLStyle styleTotalIngresos = sl.CreateStyle();
styleTotalIngresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTotalIngresos.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTotalIngresos.Font.FontSize = 10;
styleTotalIngresos.Font.FontColor = System.Drawing.Color.Green;
SLStyle styleSubTotalIngresos = sl.CreateStyle();
styleSubTotalIngresos.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalIngresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleSubTotalIngresos.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleSubTotalIngresos.Font.FontSize = 10;
styleSubTotalIngresos.Font.FontColor = System.Drawing.Color.Green;
SLStyle styleTotalEgresos = sl.CreateStyle();
styleTotalEgresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleTotalEgresos.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
styleTotalEgresos.Font.FontSize = 10;
styleTotalEgresos.Font.FontColor = System.Drawing.Color.Red;
SLStyle styleSubTotalEgresos = sl.CreateStyle();
styleSubTotalEgresos.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
styleSubTotalEgresos.SetVerticalAlignment(VerticalAlignmentValues.Center);
styleSubTotalEgresos.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
styleSubTotalEgresos.Font.FontSize = 10;
styleSubTotalEgresos.Font.FontColor = System.Drawing.Color.Red;
sl.SetCellValue("B4", "DETALLES");
sl.SetCellStyle(3, 2, styleIndexes);
sl.SetCellStyle(4, 2, styleIndexes);
sl.SetCellValue("C3", "GANANCIAS DE LA SEMANA PASADA");
sl.SetCellStyle(3, 3, styleDates);
foreach (List <Factura> Dia in SemanaIngresos)
{
foreach (Factura ingreso in Dia)
{
foreach (Detfactura det in ingreso.DetsFactura)
{
if (productosTable.Where(a => a.Id == det.IdProducto).FirstOrDefault() == null)
{
productosTable.Add(det.Producto);
sl.SetCellValue(4 + productosTable.Count, dayWeek + 3, "DETALLES");
}
}
}
}
for (int d = 0; d < SemanaIngresos.Count; d++)
{
sl.SetCellValue("B3", new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
List <Factura> Dia = SemanaIngresos[d];
sl.SetColumnWidth(dayWeek + 3, 12);
sl.SetCellValue(3, dayWeek + 3, new DateTime(pYear, pMonth, dayMonthIngrsos).ToShortDateString());
sl.SetCellStyle(3, dayWeek + 3, styleDates);
sl.SetCellValue(4, dayWeek + 3, new DateTime(pYear, pMonth, dayMonthIngrsos).DayOfWeek.ToString());
sl.SetCellStyle(4, dayWeek + 3, styleDates);
foreach (Factura ingreso in Dia)
{
foreach (Detfactura det in ingreso.DetsFactura)
{
DetallesSemana.Add(det);
}
}
for (int prod = 0; prod < productosTable.Count; prod++)
{
Producto producto = productosTable[prod];
decimal ingresoDiaProducto = 0;
List <Factura> ingresoInDia = new List <Factura>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos && a.DetsFactura.Where(dt => dt.IdProducto == producto.Id).Count() > 0).Count() > 0)
{
ingresoInDia = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos && a.DetsFactura.Where(dt => dt.IdProducto == producto.Id).Count() > 0).ToList();
}
foreach (Factura fact in ingresoInDia)
{
foreach (Detfactura det in fact.DetsFactura.Where(a => a.IdProducto == productosTable[prod].Id))
{
ingresoDiaProducto += (det.Total - det.Descuento);
totalSemana += (det.Total - det.Descuento);
}
}
sl.SetCellValue(5 + prod, dayWeek + 3, ingresoDiaProducto == 0 ? "" : "$" + Decimal.Round(ingresoDiaProducto, 2).ToString());
sl.SetCellStyle(5 + prod, dayWeek + 3, styleDiaIngreso);
sl.SetCellStyle(5 + prod, 3, styleProducto);
}
sl.SetCellValue("C" + 5, "$" + Decimal.Round(lastTotalIngresos, 2));
//MessageBox.Show(new DateTime(2019,3,1).DayOfWeek.ToString());
decimal ingresoDia = 0;
List <Factura> ingresoInDiaSub = new List <Factura>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos).Count() > 0)
{
ingresoInDiaSub = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthIngrsos).ToList();
}
foreach (Factura fact in ingresoInDiaSub)
{
foreach (Detfactura det in fact.DetsFactura)
{
ingresoDia += (det.Total - det.Descuento);
}
}
sl.SetCellValue("B" + (productosTable.Count + 5), "SUBTOTAL DE INGRESOS");
sl.SetCellStyle((productosTable.Count + 5), 2, styleIndexes);
sl.SetRowHeight(productosTable.Count + 5, 30);
sl.SetCellStyle((productosTable.Count + 5), dayWeek + 3, styleSubTotalIngresos);
sl.SetCellValue((productosTable.Count + 5), dayWeek + 3, "$" + Decimal.Round(ingresoDia, 2).ToString());
dayWeek++;
dayMonthIngrsos++;
}
for (int p = 0; p < productosTable.Count; p++)
{
sl.SetCellValue("B" + (p + 5), productosTable[p].Nombre);
sl.SetCellStyle(p + 5, 2, styleProducto);
sl.SetRowHeight(p + 5, 30);
}
sl.MergeWorksheetCells(5, 3, productosTable.Count + 5, 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells(productosTable.Count + 6, 3, productosTable.Count + 6, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.SetCellValue("B" + (productosTable.Count + 6), "TOTAL SEMANA");
sl.SetCellStyle((productosTable.Count + 6), 2, styleIndexes);
sl.SetRowHeight(productosTable.Count + 6, 30);
sl.SetCellStyle((productosTable.Count + 6), 3, styleTotalIngresos);
sl.SetCellStyle(5, 3, styleDates);
sl.SetCellStyle((productosTable.Count + 5), 3, styleDates);
sl.SetCellValue("C" + (productosTable.Count + 6), "$" + Decimal.Round(totalSemana, 2));
//procesando egresos
sl.MergeWorksheetCells(3 + productosTable.Count + 5, 2, 3 + productosTable.Count + 5, SemanaIngresos.Count + 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells("C" + (3 + productosTable.Count + 6), "C" + (3 + productosTable.Count + 7));
sl.SetCellValue("B" + (3 + productosTable.Count + 5), "EGRESOS");
sl.SetCellStyle((3 + productosTable.Count + 5), 2, styleTitle);
sl.SetRowHeight(3 + productosTable.Count + 2, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 1, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 2, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 3, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 4, 25);
sl.SetRowHeight(3 + productosTable.Count + 2 + 5, 25);
sl.SetCellValue("B" + (3 + productosTable.Count + 6), new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
sl.SetCellValue("B" + (3 + productosTable.Count + 7), "DETALLES");
sl.SetCellStyle((3 + productosTable.Count + 6), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + 7), 2, styleIndexes);
sl.SetCellValue("C" + (3 + productosTable.Count + 6), "EGRESOS DE LA SEMANA PASADA");
sl.SetCellStyle((3 + productosTable.Count + 6), 3, styleDates);
dayWeek = 1;
decimal totalSemanaEgr = 0;
ConceptosEgresos.Clear();
for (int d = 0; d < SemanaEgresos.Count; d++)
{
foreach (Egreso egreso in SemanaEgresos[d])
{
if (ConceptosEgresos.Where(a => a.Nombre.ToUpper().Trim() == egreso.Nombre.ToUpper().Trim()).FirstOrDefault() == null)
{
ConceptosEgresos.Add(egreso);
}
}
}
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 8), 2, styleIndexes);
for (int d = 0; d < SemanaEgresos.Count; d++)
{
List <Egreso> Dia = SemanaEgresos[d];
decimal TotalEgresoDia = 0;
sl.SetCellValue("B" + (3 + productosTable.Count + 6), new DateTime(pYear, pMonth, 1).ToString("MMMM", new CultureInfo("es-ES")).ToUpper());
sl.SetCellValue((3 + productosTable.Count + 6), dayWeek + 3, new DateTime(pYear, pMonth, dayMonthEgresos).ToShortDateString());
sl.SetCellStyle((3 + productosTable.Count + 6), dayWeek + 3, styleDates);
sl.SetCellValue((3 + productosTable.Count + 7), dayWeek + 3, new DateTime(pYear, pMonth, dayMonthEgresos).DayOfWeek.ToString());
sl.SetCellStyle((3 + productosTable.Count + 7), dayWeek + 3, styleDates);
for (int egr = 0; egr < ConceptosEgresos.Count; egr++)
{
decimal EgresoDia = 0;
Egreso egreso = ConceptosEgresos[egr];
List <Egreso> egresoInDia = new List <Egreso>();
if (Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthEgresos).Count() > 0)
{
egresoInDia = Dia.Where(a => Convert.ToDateTime(a.FhRegistro).Day == dayMonthEgresos && a.Nombre.ToUpper().Trim() == egreso.Nombre.ToUpper().Trim()).ToList();
}
foreach (Egreso fact in egresoInDia)
{
EgresoDia += (fact.Total);
TotalEgresoDia += fact.Total;
totalSemanaEgr += (fact.Total);
}
sl.SetCellValue((3 + productosTable.Count + 8) + egr, dayWeek + 3, EgresoDia == 0 ? "" : "$" + Decimal.Round(EgresoDia, 2).ToString());
sl.SetCellStyle((3 + productosTable.Count + 8) + egr, 3, styleDiaIngreso);
sl.SetCellStyle((3 + productosTable.Count + 8) + egr, dayWeek + 3, styleDiaIngreso);
}
for (int p = 0; p < ConceptosEgresos.Count; p++)
{
sl.SetCellValue("B" + ((3 + productosTable.Count + 8) + p), ConceptosEgresos[p].Nombre);
sl.SetCellStyle(((3 + productosTable.Count + 8) + p), 2, styleProducto);
sl.SetRowHeight(((3 + productosTable.Count + 8) + p), 30);
}
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 8), dayWeek + 3, styleSubTotalEgresos);
sl.SetCellValue((3 + productosTable.Count + ConceptosEgresos.Count + 8), dayWeek + 3, "$" + Decimal.Round(TotalEgresoDia, 2).ToString());
dayMonthEgresos++;
dayWeek++;
}
sl.MergeWorksheetCells((3 + productosTable.Count + 8), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 8), 3, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 9), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 9), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 8), "SUBTOTAL EGRESOS SEMANA");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 9), "TOTAL SEMANA");
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 8), 30);
sl.SetCellValue("C" + (3 + productosTable.Count + 8), "$" + Decimal.Round(lastTotalEgresos, 2));
sl.SetCellStyle((3 + productosTable.Count + 8), 3, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 9), 2, styleIndexes);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 9), 3, styleTotalEgresos);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 9), 30);
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 9), "$" + Decimal.Round(totalSemanaEgr, 2));
//lastTotalIngresos -= lastTotalEgresos;
//resumen
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 11), 2, (3 + productosTable.Count + ConceptosEgresos.Count + 11), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 12), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 12), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 13), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 13), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.MergeWorksheetCells((3 + productosTable.Count + ConceptosEgresos.Count + 14), 3, (3 + productosTable.Count + ConceptosEgresos.Count + 14), 3 + SemanaEgresos.Count, BorderStyleValues.Thin);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 10), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 11), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 12), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 13), 25);
sl.SetRowHeight((3 + productosTable.Count + ConceptosEgresos.Count + 14), 25);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 11), 2, styleTitle);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 12), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 13), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 14), 2, styleDates);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 12), 3, styleTotalIngresos);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 13), 3, styleTotalEgresos);
sl.SetCellStyle((3 + productosTable.Count + ConceptosEgresos.Count + 14), 3, (Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2)) == 0 ? styleDates : (Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2)) < 0 ? styleTotalEgresos : styleTotalIngresos);
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 11), "RESUMEN FINAL DE SEMANA ENTREGADO AL ADMINISTRADOR");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 12), "INGRESOS");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 13), "GASTOS");
sl.SetCellValue("B" + (3 + productosTable.Count + ConceptosEgresos.Count + 14), "TOTAL");
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 12), "$" + Decimal.Round(totalSemana, 2));
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 13), "$" + Decimal.Round(totalSemanaEgr, 2));
sl.SetCellValue("C" + (3 + productosTable.Count + ConceptosEgresos.Count + 14), "$" + Decimal.Round((totalSemana - totalSemanaEgr) + (lastTotalIngresos), 2));
lastTotalEgresos = totalSemanaEgr;
lastTotalIngresos = lastTotalIngresos + (totalSemana - totalSemanaEgr);
//lastTotalIngresos = totalSemana==0? lastTotalIngresos: (totalSemana - totalSemanaEgr) + (lastTotalIngresos);
// lastTotalIngresos = totalSemana == 0? lastTotalIngresos:(lastTotalIngresos -= lastTotalEgresos);
// MessageBox.Show(lastTotalIngresos.ToString());
}
sl.DeleteWorksheet("Sheet1");
if (!Directory.Exists(folderpath))
{
DirectoryInfo di = Directory.CreateDirectory(folderpath);
sl.SaveAs(folderpath + "\\" + fileName);
}
else
{
sl.SaveAs(folderpath + "\\" + fileName);
}
}
catch (Exception Ex)
{
throw Ex;
}
return(result);
}
private void GeraArquivoGeral(DataSet ds, string mes, string ano)
{
string dtInicio = Convert.ToDateTime(txtDtInicioRelEstouro.Text).ToString("dd-MM-yyyy");
string dtFinal = Convert.ToDateTime(txtDtFinalRelEstouro.Text).ToString("dd-MM-yyyy");
int qtdestouro = 0, resperiodo = 0, linha = 6;
int previsao = objBLL.GeraTotalEstouros(Convert.ToDateTime(dtInicio), Convert.ToDateTime(dtFinal));
SLDocument sl = new SLDocument();
//Set das configurações do arquivos e variaveis de style
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.PaperSize = SLPaperSizeValues.A4Paper;
sl.SetPageSettings(ps);
SLStyle bordaAcima = sl.CreateStyle();
bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultBold = sl.CreateStyle();
resultBold.SetFontBold(true);
resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultRed = sl.CreateStyle();
resultRed.SetFontBold(true);
resultRed.SetFontColor(System.Drawing.Color.Red);
resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bordaContorno = sl.CreateStyle();
bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle alinhadoCentro = sl.CreateStyle();
alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center;
alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center;
alinhadoCentro.SetWrapText(true);
//Loops de preenchimento do arquivo
foreach (DataRow LAreaRel in ds.Tables["AREAS_REL"].Rows)
{
foreach (DataRow LQtdEstouro in ds.Tables["QTD_ESTOURO"].Rows)
{
if (LAreaRel[0].ToString() == LQtdEstouro[0].ToString())
{
qtdestouro = Convert.ToInt32(LQtdEstouro[1]);
break;
}
qtdestouro = 0;
}
foreach (DataRow LRespPeriodo in ds.Tables["RESP_PERIODO"].Rows)
{
if (LAreaRel[0].ToString() == LRespPeriodo[0].ToString())
{
resperiodo = Convert.ToInt32(LRespPeriodo[1]);
break;
}
resperiodo = 0;
}
sl.SetCellValue(linha, 1, LAreaRel[0].ToString());
sl.SetCellValue(linha, 2, qtdestouro);
sl.SetCellValue(linha, 3, resperiodo);
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
if (qtdestouro != 0 && resperiodo != 0)
{
sl.SetCellValue(linha, 4, "=ROUND(((B" + linha + "/C" + linha + ")*100),2)");
}
else
{
sl.SetCellValue(linha, 4, 0);
}
linha++;
}
//Atribuindo valores fixos e stylo das celulas
int auxlinha = linha - 1;
sl.SetCellValue(1, 1, "Manifestações com previsão de encerramento entre " + dtInicio + " e " + dtFinal);
sl.SetCellValue(1, 2, previsao.ToString());
sl.SetCellStyle(1, 1, alinhadoCentro);
sl.SetCellStyle(1, 2, alinhadoCentro);
sl.SetCellValue(2, 1, "Respostas das Áreas Gerenciadoras:");
sl.SetCellValue(2, 2, "=C" + linha + @"&"" ou ""&ROUND(B1/C" + linha + @",4)*100&""%""");
sl.SetCellValue(3, 1, "Manifestações encerradas após a previsão:");
sl.SetCellValue(3, 2, "=B" + linha + @"&"" ou ""&ROUND(B" + linha + @"/B1,4)*100&""%""");
sl.SetCellValue(5, 1, "Áreas Gerenciadoras");
sl.SetCellValue(5, 2, "Quantidade de estouros dos prazos");
sl.SetCellValue(5, 3, "Respostas no período");
sl.SetCellValue(5, 4, "%");
sl.SetCellStyle(5, 1, resultBold);
sl.SetCellStyle(5, 2, resultBold);
sl.SetCellStyle(5, 3, resultBold);
sl.SetCellStyle(5, 4, resultBold);
sl.SetCellValue(linha, 1, "Total");
sl.SetCellValue(linha, 2, "=SUM(B6:B" + auxlinha + ")");
sl.SetCellValue(linha, 3, "=SUM(C6:C" + auxlinha + ")");
sl.SetCellStyle(linha, 1, resultBold);
sl.SetCellStyle(linha, 2, resultRed);
sl.SetCellStyle(linha, 3, resultRed);
sl.SetRowHeight(5, 38);
sl.SetColumnWidth(1, 39);
sl.SetRowHeight(1, 40);
sl.AutoFitColumn(2);
sl.AutoFitColumn(3);
sl.SetColumnWidth(4, 7);
sl.SetColumnStyle(1, alinhadoCentro);
sl.SetColumnStyle(2, alinhadoCentro);
sl.SetColumnStyle(3, alinhadoCentro);
sl.SetColumnStyle(4, alinhadoCentro);
string caminho = Server.MapPath(@"UploadFile\\" + "Relatorio_Estouro "
+ mes +
" " + ano + ".xlsx");
sl.SaveAs(caminho);
}
public static void GenerateXlsFromTestPlan(String path, List <TestPlan> ListPlans)
{
List <TestCase> listCase = new List <TestCase>();
#region Styles and SLDocument
SLDocument xlsx = new SLDocument();
SLStyle style = xlsx.CreateStyle();
style.SetWrapText(true);
SLStyle indexStyle = xlsx.CreateStyle();
indexStyle.Alignment.Horizontal = HorizontalAlignmentValues.Right;
indexStyle.SetWrapText(true);
SLStyle headerStyle = xlsx.CreateStyle();
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Yellow, System.Drawing.Color.Black);
headerStyle.Font.Bold = true;
headerStyle.Font.FontName = "Calibri";
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
#endregion
#region Populate Excel
foreach (TestPlan testPlan in ListPlans)
{
for (int k = 0; k < testPlan.TestCases.Count; k++)
{
TestCase testCase = testPlan.TestCases[k];
listCase.Add(testCase);
xlsx.SetColumnStyle(1, 1, style);
xlsx.SetColumnWidth(1, 1, 50);
String title = testCase.Title;
title = title.Replace(" ", "");
if (title.Length > 31)
{
title = title.Substring(0, 25) + title.Substring(title.Length - 5, 5);
}
bool dssd = xlsx.AddWorksheet(HttpUtility.UrlDecode(title));
bool re = xlsx.SelectWorksheet(title);
xlsx.SetCellValue(1, 1, "Test Case #");
xlsx.SetColumnWidth(1, 13);
xlsx.SetCellValue(1, 2, "Work Item ID");
xlsx.SetColumnWidth(2, 22);
xlsx.SetCellValue(1, 3, "Test Title");
xlsx.SetColumnWidth(3, 30);
xlsx.SetCellValue(1, 4, "Summary");
xlsx.SetColumnWidth(4, 30);
xlsx.SetCellValue(1, 5, "Test Step");
xlsx.SetColumnWidth(5, 10);
xlsx.SetCellValue(1, 6, "Action/Description");
xlsx.SetColumnWidth(6, 60);
xlsx.SetCellValue(1, 7, "Expected Results");
xlsx.SetColumnWidth(7, 50);
xlsx.SetCellValue(1, 8, "Assigned To");
xlsx.SetColumnWidth(8, 20);
xlsx.SetCellValue(1, 9, "State");
xlsx.SetColumnWidth(9, 10);
xlsx.SetCellValue(1, 10, "Reason");
xlsx.SetColumnWidth(10, 10);
xlsx.SetCellValue(1, 11, "Iteration Path");
xlsx.SetColumnWidth(11, 30);
xlsx.SetCellValue(1, 12, "Area Path");
xlsx.SetColumnWidth(12, 10);
xlsx.SetCellValue(1, 13, "Application");
xlsx.SetColumnWidth(13, 13);
xlsx.SetCellValue(1, 14, "Complexity");
xlsx.SetColumnWidth(14, 13);
xlsx.SetCellValue(1, 15, "Risks");
xlsx.SetColumnWidth(15, 15);
xlsx.SetCellValue(1, 16, "TC Lifecycle");
xlsx.SetColumnWidth(16, 12);
xlsx.SetCellValue(1, 17, "Lifecycle Type");
xlsx.SetColumnWidth(17, 15);
xlsx.SetCellValue(1, 18, "TC Team Usage");
xlsx.SetColumnWidth(18, 15);
xlsx.SetCellStyle("A1", "R1", headerStyle);
xlsx.SetRowHeight(1, 1, 18);
xlsx.SetCellValue(2, 1, "TC" + testCase.TestCaseId.ToString("000"));
xlsx.SetCellStyle(2, 1, style);
xlsx.SetCellValue(2, 2, "Test Case " + HttpUtility.UrlDecode(testCase.WorkItemId.ToString()));
xlsx.SetCellStyle(2, 2, style);
xlsx.SetCellValue(2, 3, HttpUtility.UrlDecode(testCase.Title));
xlsx.SetCellStyle(2, 3, style);
xlsx.SetCellValue(2, 4, HttpUtility.UrlDecode(testCase.Summary));
xlsx.SetCellStyle(2, 4, style);
xlsx.SetCellValue(2, 5, "1");
xlsx.SetCellStyle(2, 5, indexStyle);
xlsx.SetCellValue(2, 6, HttpUtility.UrlDecode(testCase.TDpreConditions));
xlsx.SetCellStyle(2, 6, style);
xlsx.SetCellValue(2, 7, HttpUtility.UrlDecode(testCase.TDpostConditions));
xlsx.SetCellStyle(2, 7, style);
xlsx.SetCellValue(2, 8, HttpUtility.UrlDecode(testCase.TDassigned));
xlsx.SetCellStyle(2, 8, style);
xlsx.SetCellValue(2, 9, HttpUtility.UrlDecode(testCase.TDstate));
xlsx.SetCellStyle(2, 9, style);
xlsx.SetCellValue(2, 10, HttpUtility.UrlDecode(testCase.TDreason));
xlsx.SetCellStyle(2, 10, style);
xlsx.SetCellValue(2, 11, HttpUtility.UrlDecode(testCase.TDiterationPath));
xlsx.SetCellStyle(2, 11, style);
xlsx.SetCellValue(2, 12, HttpUtility.UrlDecode(testCase.TDareaPath));
xlsx.SetCellStyle(2, 12, style);
xlsx.SetCellValue(2, 13, HttpUtility.UrlDecode(testCase.TDapplication));
xlsx.SetCellStyle(2, 13, style);
xlsx.SetCellValue(2, 14, HttpUtility.UrlDecode(testCase.TDcomplexity));
xlsx.SetCellStyle(2, 14, style);
xlsx.SetCellValue(2, 15, HttpUtility.UrlDecode(testCase.TDrisks));
xlsx.SetCellStyle(2, 15, style);
xlsx.SetCellValue(2, 16, HttpUtility.UrlDecode(testCase.TDtcLifecycle));
xlsx.SetCellStyle(2, 16, style);
xlsx.SetCellValue(2, 17, HttpUtility.UrlDecode(testCase.TDlifecycleType));
xlsx.SetCellStyle(2, 17, style);
xlsx.SetCellValue(2, 18, HttpUtility.UrlDecode(testCase.TDtcTeamUsage));
xlsx.SetCellStyle(2, 18, style);
#region Step
for (int i = 0; i < testCase.TestSteps.Count; i++)
{
TestStep step = testCase.TestSteps[i];
if (!testCase.WriteFirstLine)
{
xlsx.SetCellValue(i + 2, 5, HttpUtility.UrlDecode(step.Index));
xlsx.SetCellStyle(i + 2, 5, indexStyle);
xlsx.SetCellValue(i + 2, 6, HttpUtility.UrlDecode(step.Description));
xlsx.SetCellStyle(i + 2, 6, style);
xlsx.SetCellValue(i + 2, 7, HttpUtility.UrlDecode(step.ExpectedResult));
xlsx.SetCellStyle(i + 2, 7, style);
}
else
{
xlsx.SetCellValue(i + 3, 5, HttpUtility.UrlDecode(step.Index));
xlsx.SetCellStyle(i + 3, 5, indexStyle);
xlsx.SetCellValue(i + 3, 6, HttpUtility.UrlDecode(step.Description));
xlsx.SetCellStyle(i + 3, 6, style);
xlsx.SetCellValue(i + 3, 7, HttpUtility.UrlDecode(step.ExpectedResult));
xlsx.SetCellStyle(i + 3, 7, style);
}
}
#endregion
}
}
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
//xlsx.DeleteRow(2,1);
xlsx.SaveAs(path + @"\Plan.xlsx");
#endregion
}
public FileResult ExportarEncargado()
{
/*StringBuilder csv = new StringBuilder();
* string Columnas = string.Format("{0};{1};{2};{3};{4}", "N", "Nombre", "Productos", "Precio", "Local");
* csv.AppendLine(Columnas);
*
* decimal costoTotal = 0;
* TanoNEEntities ctx = new TanoNEEntities();
* Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
*
* Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
* var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres }).ToList().Select(a => new
* {
* idCompra = a.idCompra,
* nombre = a.Vecinos.nombres,
* productos = string.Join(" - ", a.CompraProducto.ToList().Select(b => "(" + b.cantidad + ") " + b.Productos.producto + " - " + b.Productos.marca + " - " + b.Productos.presentacion + " - " + b.Productos.Precios.LastOrDefault(precio => a.fecha > precio.fecha).precio + "\015")),
* precio = a.CompraProducto.ToList().Sum(b => b.cantidad * b.Productos.Precios.LastOrDefault(precio => a.fecha >= precio.fecha).precio),
* retiro = a.EstadosCompra.codigo,
* local = a.Locales.direccion
* }).ToArray();
*
* for (int x = 0; x < lista.Count(); x++)
* {
* var compra = lista[x];
* string filas = string.Format("{0};{1};{2};${3};{4}", compra.idCompra, compra.nombre, compra.productos, compra.precio, compra.local);
* csv.AppendLine(filas);
* }
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* return File(memoryStream.ToArray() as byte[], "application/vnd.ms-excel", "Reporte.csv");
* }*/
TanoNEEntities ctx = new TanoNEEntities();
Vecinos vecino = ctx.Vecinos.FirstOrDefault(a => a.correo == User.Identity.Name);
Tandas ultima = ctx.Tandas.ToList().LastOrDefault(a => a.fechaCerrado != null);
DateTime ProximaEntrea = ultima.fechaVenta.HasValue ? ultima.fechaVenta.Value : DateTime.Now;
string nombreLibro = ProximaEntrea.ToString("dd-MM-yyyy") + " Entrega";
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, nombreLibro);
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
sl.SetColumnWidth(1, 30);
sl.SetColumnWidth(2, 65);
sl.SetColumnWidth(3, 10);
sl.SetColumnWidth(4, 10);
sl.SetColumnWidth(5, 35);
sl.SetCellValue(1, 1, "Nombre");
sl.SetCellValue(1, 2, "Producto");
sl.SetCellValue(1, 3, "Precio");
sl.SetCellValue(1, 4, "Total");
sl.SetCellValue(1, 5, "Observaciones");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
string urla = ConfigurationManager.AppSettings["UrlSitio"];
int row = 3;
bordeNegrita.Font.Bold = false;
var lista = ctx.Compras.Where(a => a.tandaId == ultima.idTanda && (vecino.localId == null ? vecino.comuna == a.Locales.comuna : vecino.localId == a.localId)).OrderBy(a => new { a.Locales.idLocal, a.Vecinos.nombres });
foreach (var compra in lista)
{
int totalVecinx = (row + compra.CompraProducto.Count - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 5, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 5, bordeAb);
for (int x = 1; x < 6; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, new System.Globalization.CultureInfo("en-US", false).TextInfo.ToTitleCase(compra.Vecinos.nombres.ToLower()) + "\n" + compra.Vecinos.telefono + "\n" + compra.Vecinos.correo);
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
var ordenado = compra.CompraProducto.OrderBy(a => a.Productos.producto);
decimal totaltotal = 0;
foreach (var compraProducto in ordenado)
{
decimal total = compraProducto.Precios.precio * compraProducto.cantidad;
totaltotal += total;
centrado.Font.Bold = false;
sl.SetCellValue(row, 2, compraProducto.cantidad + ": " + compraProducto.Productos.producto + " - " + compraProducto.Productos.marca + " - " + compraProducto.Productos.presentacion);
sl.SetCellValue(row, 3, total);
sl.SetCellStyle(row, 3, centrado);
if (compraProducto.cantidad > 6)
{
sl.SetCellStyle(row, 2, row, 5, rojo);
sl.SetCellValue(row, 6, "Alerta! Mucha cantidad");
}
row++;
}
centrado.Font.Bold = true;
sl.SetCellValue(row - 1, 4, totaltotal);
sl.SetCellStyle(row - 1, 4, centrado);
}
sl.SaveAs(mem);
mem.Position = 0;
return(File(mem.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Reporte.xlsx"));
}
}
//TestData excel generation
private void GenerateXlsTestData(UmlModel model)
{
int currentColumn = 0;
String modelName = model.Name;
//Verifica se diretorio existe, se não existe cria um novo.
String directory = CreateTestDataDirectory();
#region Styles and SLDocument
SLDocument xlsx = new SLDocument();
SLStyle style = xlsx.CreateStyle();
SLStyle headerStyle = xlsx.CreateStyle();
style.SetWrapText(true);
//style.Border.RightBorder.Color = System.Drawing.Color.Black;
//style.Border.LeftBorder.Color = System.Drawing.Color.Black;
//style.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
//style.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
//style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
//style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(0x8EB4E3), System.Drawing.Color.Black);
headerStyle.Font.Bold = true;
headerStyle.Font.FontName = "Calibri";
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
#endregion
if (model.Diagrams.Count(X => X is UmlActivityDiagram) > 1)
{
throw new Exception("Only one Activity diagram allowed! (For now)");
}
UmlActivityDiagram act = model.Diagrams.OfType <UmlActivityDiagram>().First();
if (act.Name.Length > 31)
{
String aux = act.Name;
aux = aux.Substring(0, 25) + aux.Substring(aux.Length - 5, 5);
aux = HttpUtility.UrlDecode(aux);
xlsx.AddWorksheet(aux);
xlsx.SelectWorksheet(aux);
}
else
{
xlsx.AddWorksheet(HttpUtility.UrlDecode(act.Name));
xlsx.SelectWorksheet(HttpUtility.UrlDecode(act.Name));
}
for (int i = 0; i < act.UmlObjects.OfType <UmlTransition>().Count(); i++)
{
UmlTransition tran = act.UmlObjects.OfType <UmlTransition>().ElementAt(i);
foreach (KeyValuePair <String, String> pair in tran.TaggedValues)
{
String valueTDObject = "";
if (pair.Key.Equals("TDOBJECT"))
{
valueTDObject = HttpUtility.UrlDecode(pair.Value);
valueTDObject = valueTDObject.Substring(1);
valueTDObject = valueTDObject.Substring(0, valueTDObject.Length - 1);
if (valueTDObject.Contains("textbox") || valueTDObject.Contains("selectbox"))
{
char[] delimiterChars = { '_' };
string[] words = valueTDObject.Split(delimiterChars);
String aux = words[words.Count() - 1];
xlsx.SetCellValue(1, currentColumn + 1, aux);
//xlsx.SetColumnWidth(1, 30);
xlsx.SetCellStyle(1, currentColumn + 1, headerStyle);
//xlsx.SetRowHeight(1, 1, 18);
currentColumn++;
}
}
}
}
xlsx.AutoFitColumn(1, currentColumn);
xlsx.AutoFitRow(1);
xlsx.DeleteWorksheet(SLDocument.DefaultFirstSheetName);
if (!File.Exists(directory + @"\" + modelName + "_TestData.xlsx"))
{
xlsx.SaveAs(directory + @"\" + modelName + "_TestData.xlsx");
}
else
{
int i = 1;
string name;
string[] sub;
string[] files = System.IO.Directory.GetFiles(directory);
foreach (String file in files)
{
name = System.IO.Path.GetFileNameWithoutExtension(file);
if (name.Contains("("))
{
char[] splitTokens = { '(', ')' };
sub = name.Split(splitTokens);
if (sub[0] == modelName)
{
//descubro o maior index
if (Convert.ToInt16(sub[1]) > i)
{
i = Convert.ToInt16(sub[1]);
}
}
}
}
xlsx.SaveAs(directory + @"\" + modelName + "(" + (i + 1) + ")" + "_TestData.xlsx");
}
}
internal static void GenerateXlsFromTestPlan(TestPlan testPlan, String savePath)
{
FunctionalTool.MainWindow.ProgressBar.Maximum = testPlan.TestCases.Count;
for (int k = 0; k < testPlan.TestCases.Count; k++)
{
SLDocument xlsx = new SLDocument();
#region Styles
SLStyle style = xlsx.CreateStyle();
style.SetWrapText(true);
SLStyle indexStyle = xlsx.CreateStyle();
indexStyle.Alignment.Horizontal = HorizontalAlignmentValues.Right;
indexStyle.SetWrapText(true);
SLStyle headerStyle = xlsx.CreateStyle();
headerStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Yellow, System.Drawing.Color.Black);
headerStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
headerStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
headerStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
#endregion
xlsx.AddWorksheet("List");
xlsx.SetCellValue(1, 1,
"'- Complexity"
+ "\n Complex"
+ "\n Extremely Complex"
+ "\n Moderate"
+ "\n Simple"
+ "\n"
+ "\n - Risk"
+ "\n 1-Critical"
+ "\n 2-High"
+ "\n 3-Medium"
+ "\n 4-Low"
+ "\n"
+ "\n - TC Lifecycle"
+ "\n Current"
+ "\n Future"
+ "\n Obsolete"
+ "\n"
+ "\n - Lifecycle Type"
+ "\n Core App"
+ "\n E2E"
+ "\n Smoke"
+ "\n"
+ "\n - TC Team Usage"
+ "\n DIT"
+ "\n DUT"
+ "\n PERF"
+ "\n SIT"
+ "\n UAT"
+ "\n VIT"
+ "\n"
+ "\n - State"
+ "\n Design"
+ "\n "
+ "\n - Reason"
+ "\n New"
);
xlsx.SetColumnStyle(1, 1, style);
xlsx.SetColumnWidth(1, 1, 50);
xlsx.SelectWorksheet("Sheet1");
xlsx.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Test Cases");
FunctionalTool.MainWindow.ProgressBar.Value = k + 1;
FunctionalTool.MainWindow.ProgressBar.UpdateLayout();
TestCase testCase = testPlan.TestCases[k];
xlsx.SetCellValue(1, 1, "Test Case #");
xlsx.SetColumnWidth(1, 13);
xlsx.SetCellValue(1, 2, "Work Item ID");
xlsx.SetColumnWidth(2, 22);
xlsx.SetCellValue(1, 3, "Test Title");
xlsx.SetColumnWidth(3, 30);
xlsx.SetCellValue(1, 4, "Summary");
xlsx.SetColumnWidth(4, 30);
xlsx.SetCellValue(1, 5, "Test Step");
xlsx.SetColumnWidth(5, 10);
xlsx.SetCellValue(1, 6, "Action/Description");
xlsx.SetColumnWidth(6, 60);
xlsx.SetCellValue(1, 7, "Expected Results");
xlsx.SetColumnWidth(7, 50);
xlsx.SetCellValue(1, 8, "Assigned To");
xlsx.SetColumnWidth(8, 20);
xlsx.SetCellValue(1, 9, "State");
xlsx.SetColumnWidth(9, 10);
xlsx.SetCellValue(1, 10, "Reason");
xlsx.SetColumnWidth(10, 10);
xlsx.SetCellValue(1, 11, "Iteration Path");
xlsx.SetColumnWidth(11, 30);
xlsx.SetCellValue(1, 12, "Area Path");
xlsx.SetColumnWidth(12, 10);
xlsx.SetCellValue(1, 13, "Application");
xlsx.SetColumnWidth(13, 13);
xlsx.SetCellValue(1, 14, "Complexity");
xlsx.SetColumnWidth(14, 13);
xlsx.SetCellValue(1, 15, "Risks");
xlsx.SetColumnWidth(15, 15);
xlsx.SetCellValue(1, 16, "TC Lifecycle");
xlsx.SetColumnWidth(16, 12);
xlsx.SetCellValue(1, 17, "Lifecycle Type");
xlsx.SetColumnWidth(17, 15);
xlsx.SetCellValue(1, 18, "TC Team Usage");
xlsx.SetColumnWidth(18, 15);
xlsx.SetCellStyle("A1", "R1", headerStyle);
xlsx.SetRowHeight(1, 1, 18);
xlsx.SetCellValue(2, 1, "TC" + testCase.TestCaseId.ToString("000"));
xlsx.SetCellStyle(2, 1, style);
xlsx.SetCellValue(2, 2, testCase.TestSteps[0].workItemIdString);
xlsx.SetCellStyle(2, 2, style);
xlsx.SetCellValue(2, 3, testCase.Title);
xlsx.SetCellStyle(2, 3, style);
xlsx.SetCellValue(2, 4, testCase.Summary);
xlsx.SetCellStyle(2, 4, style);
xlsx.SetCellValue(2, 5, "1");
xlsx.SetCellStyle(2, 5, indexStyle);
xlsx.SetCellValue(2, 6, testCase.TestSteps[0].Description.Trim());
xlsx.SetCellStyle(2, 6, style);
xlsx.SetCellValue(2, 7, testCase.TestSteps[0].ExpectedResult);
xlsx.SetCellStyle(2, 7, style);
xlsx.SetCellValue(2, 8, testCase.TestSteps[0].FTassigned);
xlsx.SetCellStyle(2, 8, style);
xlsx.SetCellValue(2, 9, testCase.TestSteps[0].FTstate);
xlsx.SetCellStyle(2, 9, style);
xlsx.SetCellValue(2, 10, testCase.TestSteps[0].FTreason);
xlsx.SetCellStyle(2, 10, style);
xlsx.SetCellValue(2, 11, testCase.TestSteps[0].FTiterationPath);
xlsx.SetCellStyle(2, 11, style);
xlsx.SetCellValue(2, 12, testCase.TestSteps[0].FTareaPath);
xlsx.SetCellStyle(2, 12, style);
xlsx.SetCellValue(2, 13, testCase.TestSteps[0].FTapplication);
xlsx.SetCellStyle(2, 13, style);
xlsx.SetCellValue(2, 14, testCase.TestSteps[0].FTcomplexity);
xlsx.SetCellStyle(2, 14, style);
xlsx.SetCellValue(2, 15, testCase.TestSteps[0].FTrisks);
xlsx.SetCellStyle(2, 15, style);
xlsx.SetCellValue(2, 16, testCase.TestSteps[0].FTtcLifecycle);
xlsx.SetCellStyle(2, 16, style);
xlsx.SetCellValue(2, 17, testCase.TestSteps[0].FTlifecycleType);
xlsx.SetCellStyle(2, 17, style);
xlsx.SetCellValue(2, 18, testCase.TestSteps[0].FTtcTeamUsage);
xlsx.SetCellStyle(2, 18, style);
for (int i = 1; i < testCase.TestSteps.Count; i++)
{
TestStep step = testCase.TestSteps[i];
xlsx.SetCellValue(i + 2, 2, step.workItemIdString);
xlsx.SetCellStyle(i + 2, 2, style);
xlsx.SetCellValue(i + 2, 3, step.Title);
xlsx.SetCellStyle(i + 2, 3, style);
xlsx.SetCellValue(i + 2, 5, step.Index);
xlsx.SetCellStyle(i + 2, 5, indexStyle);
xlsx.SetCellValue(i + 2, 6, step.Description.Trim().Replace("'", "\""));
xlsx.SetCellStyle(i + 2, 6, style);
xlsx.SetCellValue(i + 2, 7, step.ExpectedResult.Replace("'", "\""));
xlsx.SetCellStyle(i + 2, 7, style);
}
string str = "@,!#$%\"^&:*<>?|//\\";
char[] ch = str.ToCharArray();
string name = HttpUtility.UrlDecode(testCase.Title);
foreach (char c in ch)
{
if (name.Contains(c.ToString()))
{
name.Replace(c.ToString(), "");
}
}
xlsx.SaveAs(savePath + "\\" + testCase.Title + ".xlsx");
}
}
public ActionResult CrearExcel(int idTanda, bool porLocal = false)
{
/*string handle = Guid.NewGuid().ToString();
*
* StringBuilder csv = new StringBuilder();
* string Columnas = null;
* if (porLocal)
* Columnas = ""; // string.Format("{0};{1};{2};{3}", "N", "Local", "Productos");
* else
* Columnas = string.Format("{0};{1};{2};{3}", "N", "Producto", "Presentacion", "Cantidad", "Costo(Aprox)");
* csv.AppendLine(Columnas);
*
*
* TanoNEEntities ctx = new TanoNEEntities();
* Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
*
* if (porLocal)
* {
* decimal costoTotal = 0;
* var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().ToList();
* foreach (var local in locales)
* {
* csv.AppendLine(local.direccion);
* var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
* Costos ultimo = prod.Costos.Count > 1 ? prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) : prod.Costos.FirstOrDefault();
* decimal costo = ultimo.costo * compra.Cantidad;
* costoTotal += costo;
* string filas = string.Format(";{0};{1};${2}", compra.Cantidad, string.Format("{0} - {1} - {2}", prod.producto, prod.marca, prod.presentacion), costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
* }
* string cierre = string.Format("Total;;;${0}", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(cierre);
* }
* else
* {
* var listado = ctx.CompraProducto.Where(a => a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad) }).ToArray();
* decimal costoTotal = 0;
* for (int x = 0; x < listado.Count(); x++)
* {
* var compra = listado[x];
* Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == compra.idProducto);
* Costos ultimo = prod.Costos.Count > 1 ? (prod.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) == null ? prod.Costos.LastOrDefault() : prod.Costos.FirstOrDefault()) : prod.Costos.FirstOrDefault();
* decimal costo = ultimo.costo * compra.Cantidad;
* //decimal costo = prod.Costos.FirstOrDefault(a => a.fecha <= actual.fechaAbierto).costo * compra.Cantidad;
* costoTotal += costo;
* string filas = string.Format("{0};{1};{2};{3};${4}", x + 1, prod.producto, prod.presentacion, compra.Cantidad, costo.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(filas);
* }
*
* string cierre = string.Format("{0};{1};{2};{3};${4}", "", "", "", "", costoTotal.ToString("0.00", System.Globalization.CultureInfo.InvariantCulture));
* csv.AppendLine(cierre);
* }
*
*
*
* using (MemoryStream memoryStream = new MemoryStream(Encoding.Default.GetBytes(csv.ToString())))
* {
* memoryStream.Position = 0;
* TempData[handle] = memoryStream.ToArray();
* }
*
* return Json(new { FileGuid = handle, FileName = "Reporte.csv" });*/
string handle = Guid.NewGuid().ToString();
TanoNEEntities ctx = new TanoNEEntities();
Tandas actual = ctx.Tandas.FirstOrDefault(a => a.idTanda == idTanda);
using (MemoryStream mem = new MemoryStream())
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, porLocal ? "Local" : "Total");
SLStyle bordeNegrita = sl.CreateStyle();
bordeNegrita.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeNegrita.Font.Bold = true;
bordeNegrita.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
bordeNegrita.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeIz = sl.CreateStyle();
bordeIz.Border.LeftBorder.Color = System.Drawing.Color.Black;
bordeIz.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeDe = sl.CreateStyle();
bordeDe.Border.RightBorder.Color = System.Drawing.Color.Black;
bordeDe.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAr = sl.CreateStyle();
bordeAr.Border.TopBorder.Color = System.Drawing.Color.Black;
bordeAr.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle bordeAb = sl.CreateStyle();
bordeAb.Border.BottomBorder.Color = System.Drawing.Color.Black;
bordeAb.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
SLStyle saltoLinea = sl.CreateStyle();
saltoLinea.SetVerticalAlignment(VerticalAlignmentValues.Center);
saltoLinea.SetWrapText(true);
SLStyle rojo = sl.CreateStyle();
rojo.Fill.SetPatternType(PatternValues.Solid);//.BottomBorder.Color = System.Drawing.Color.Red;
rojo.Font.FontColor = System.Drawing.Color.White;
rojo.Fill.SetPatternForegroundColor(System.Drawing.Color.Red);
SLStyle centrado = sl.CreateStyle();
centrado.FormatCode = "$ * #,##0.00";
centrado.Font.FontSize = 10;
centrado.SetHorizontalAlignment(HorizontalAlignmentValues.Right);
SLStyle negrita = sl.CreateStyle();
negrita.Font.Bold = true;
int row = 3;
if (porLocal)
{
sl.SetColumnWidth(1, 16);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 15);
sl.SetColumnWidth(5, 15);
sl.SetCellValue(1, 1, "Local");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Costo");
sl.SetCellValue(1, 5, "Costo Total");
sl.SetCellStyle(1, 1, 1, 5, bordeNegrita);
decimal total = 0;
var locales = ctx.Compras.Where(a => a.tandaId == idTanda).Select(a => a.Locales).Distinct().OrderBy(a => new { a.comuna, a.nombre }).ToList();
foreach (var local in locales)
{
var listado = ctx.CompraProducto.Where(a => a.Compras.localId == local.idLocal && a.Compras.tandaId == idTanda).OrderBy(a => a.Productos.producto).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, cp = a.FirstOrDefault(), Cantidad = a.Sum(b => b.cantidad) });
int totalVecinx = (row + listado.Count() - 1);
sl.SetCellStyle(row, 1, totalVecinx, 1, bordeIz);
sl.SetCellStyle(row, 1, row, 5, bordeAr);
sl.SetCellStyle(totalVecinx, 1, totalVecinx, 5, bordeAb);
for (int x = 1; x < 6; x++)
{
sl.SetCellStyle(row, x, totalVecinx, x, bordeDe);
}
sl.SetCellValue(row, 1, local.direccion + (local.nombre != null ? "\n" + local.nombre : ""));
sl.SetCellStyle(row, 1, saltoLinea);
sl.MergeWorksheetCells(row, 1, totalVecinx, 1);
decimal subTotalLocal = 0;
foreach (var productos in listado)
{
Productos prod = ctx.Productos.FirstOrDefault(a => a.idProducto == productos.idProducto);
Costos ultimo = productos.cp.Costos;
decimal costoTotal = ultimo.costo * productos.Cantidad;
subTotalLocal += costoTotal;
sl.SetCellValue(row, 2, productos.Cantidad);
sl.SetCellValue(row, 3, prod.producto + " - " + prod.marca + " - " + prod.presentacion);
sl.SetCellValue(row, 4, ultimo.costo);
sl.SetCellValue(row, 5, costoTotal);
sl.SetCellStyle(row, 4, centrado);
sl.SetCellStyle(row, 5, centrado);
row++;
}
total += subTotalLocal;
sl.SetCellValue(row, 4, "Subtotal local: ");
sl.SetCellStyle(row, 4, negrita);
sl.SetCellValue(row, 5, subTotalLocal);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 5, negrita);
row++;
row++;
}
row++;
sl.SetCellValue(row, 4, "Total: ");
sl.SetCellStyle(row, 4, negrita);
sl.SetCellValue(row, 5, total);
sl.SetCellStyle(row, 5, centrado);
sl.SetCellStyle(row, 5, negrita);
}
else
{
sl.SetColumnWidth(1, 5);
sl.SetColumnWidth(2, 10);
sl.SetColumnWidth(3, 65);
sl.SetColumnWidth(4, 20);
sl.SetColumnWidth(5, 20);
sl.SetColumnWidth(6, 15);
sl.SetColumnWidth(7, 15);
sl.SetCellValue(1, 1, "N°");
sl.SetCellValue(1, 2, "Cantidad");
sl.SetCellValue(1, 3, "Producto");
sl.SetCellValue(1, 4, "Marca");
sl.SetCellValue(1, 5, "Presentacion");
sl.SetCellValue(1, 6, "Costo");
sl.SetCellValue(1, 7, "Costo Total");
sl.SetCellStyle(1, 1, 1, 7, bordeNegrita);
var listado = ctx.CompraProducto.Where(a => a.Compras.tandaId == idTanda).GroupBy(a => a.productoId).Select(a => new { idProducto = a.Key, Cantidad = a.Sum(b => b.cantidad), Compra = a.FirstOrDefault() }).OrderBy(a => new { a.Compra.Productos.Categorias.nombre, a.Compra.Productos.producto }).ToList();
decimal subTotal = 0;
for (int x = 0; x < listado.Count(); x++)
{
var compra = listado.ElementAt(x);
Costos ultimo = compra.Compra.Costos; //.Costos.Count > 1 ? (compra.Compra.Productos.Costos.LastOrDefault(a => a.fecha.Date <= actual.fechaAbierto.Date) == null ? compra.Compra.Productos.Costos.LastOrDefault() : compra.Compra.Productos.Costos.FirstOrDefault()) : compra.Compra.Productos.Costos.FirstOrDefault();
decimal costoTotal = ultimo.costo * compra.Cantidad;
subTotal += costoTotal;
sl.SetCellValue(row, 1, x + 1);
sl.SetCellValue(row, 2, compra.Cantidad);
sl.SetCellValue(row, 3, compra.Compra.Productos.producto + " - " + compra.Compra.Productos.marca + " - " + compra.Compra.Productos.presentacion);
sl.SetCellValue(row, 4, compra.Compra.Productos.marca);
sl.SetCellValue(row, 5, compra.Compra.Productos.presentacion);
sl.SetCellValue(row, 6, ultimo.costo);
sl.SetCellValue(row, 7, costoTotal);
sl.SetCellStyle(row, 6, centrado);
sl.SetCellStyle(row, 7, centrado);
row++;
}
centrado.Font.Bold = true;
sl.SetCellValue(row, 5, "Total: ");
sl.SetCellValue(row, 7, subTotal);
sl.SetCellStyle(row, 7, centrado);
}
sl.SaveAs(mem);
mem.Position = 0;
TempData[handle] = mem.ToArray();
return(Json(new { FileGuid = handle, FileName = porLocal ? "Local.xlsx" : "Total.xlsx" }));
}
}
private void GeraArquivo(System.Data.DataTable dt, string dataInicial, string dataFinal)
{
SLDocument sl = new SLDocument();
int linha = 1;
//Set das configurações do arquivos e variaveis de style
SLPageSettings ps = new SLPageSettings();
ps.Orientation = OrientationValues.Landscape;
ps.PaperSize = SLPaperSizeValues.A4Paper;
sl.SetPageSettings(ps);
SLStyle bordaAcima = sl.CreateStyle();
bordaAcima.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultBold = sl.CreateStyle();
resultBold.SetFontBold(true);
resultBold.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultBold.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle resultRed = sl.CreateStyle();
resultRed.SetFontBold(true);
resultRed.SetFontColor(System.Drawing.Color.Red);
resultRed.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
resultRed.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bordaContorno = sl.CreateStyle();
bordaContorno.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
bordaContorno.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle alinhadoCentro = sl.CreateStyle();
alinhadoCentro.Alignment.Horizontal = HorizontalAlignmentValues.Center;
alinhadoCentro.Alignment.Vertical = VerticalAlignmentValues.Center;
alinhadoCentro.SetWrapText(true);
sl.SetCellValue(linha, 1, "Código Empresa");
sl.SetCellValue(linha, 2, "Número do registro do empregado");
sl.SetCellValue(linha, 3, "Nome");
sl.SetCellValue(linha, 4, "E-mail");
sl.SetCellValue(linha, 5, "Data da compra");
sl.SetCellValue(linha, 6, "Valor da compra");
sl.SetCellValue(linha, 7, "Quantidade de ingressos");
sl.SetCellValue(linha, 8, "Parcelas");
sl.SetCellValue(linha, 9, "Forma de pagamento");
sl.SetCellValue(linha, 10, "Data de vencimento");
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
sl.SetCellStyle(linha, 5, bordaContorno);
sl.SetCellStyle(linha, 6, bordaContorno);
sl.SetCellStyle(linha, 7, bordaContorno);
sl.SetCellStyle(linha, 8, bordaContorno);
sl.SetCellStyle(linha, 9, bordaContorno);
sl.SetCellStyle(linha, 10, bordaContorno);
sl.SetCellStyle(linha, 1, resultBold);
sl.SetCellStyle(linha, 2, resultBold);
sl.SetCellStyle(linha, 3, resultBold);
sl.SetCellStyle(linha, 4, resultBold);
sl.SetCellStyle(linha, 5, resultBold);
sl.SetCellStyle(linha, 6, resultBold);
sl.SetCellStyle(linha, 7, resultBold);
sl.SetCellStyle(linha, 8, resultBold);
sl.SetCellStyle(linha, 9, resultBold);
sl.SetCellStyle(linha, 10, resultBold);
linha++;
foreach (DataRow dr in dt.Rows)
{
sl.SetCellValue(linha, 1, dr[0].ToString());
sl.SetCellValue(linha, 2, dr[1].ToString());
sl.SetCellValue(linha, 3, dr[2].ToString());
sl.SetCellValue(linha, 4, dr[3].ToString());
sl.SetCellValue(linha, 5, dr[4].ToString());
sl.SetCellValue(linha, 6, dr[5].ToString());
sl.SetCellValue(linha, 7, dr[6].ToString());
sl.SetCellValue(linha, 8, dr[7].ToString());
sl.SetCellValue(linha, 9, dr[8].ToString());
sl.SetCellValue(linha, 10, dr[9].ToString());
sl.SetCellStyle(linha, 1, bordaContorno);
sl.SetCellStyle(linha, 2, bordaContorno);
sl.SetCellStyle(linha, 3, bordaContorno);
sl.SetCellStyle(linha, 4, bordaContorno);
sl.SetCellStyle(linha, 5, bordaContorno);
sl.SetCellStyle(linha, 6, bordaContorno);
sl.SetCellStyle(linha, 7, bordaContorno);
sl.SetCellStyle(linha, 8, bordaContorno);
sl.SetCellStyle(linha, 9, bordaContorno);
sl.SetCellStyle(linha, 10, bordaContorno);
linha++;
}
sl.AutoFitColumn(1);
sl.AutoFitColumn(2);
sl.AutoFitColumn(3);
sl.AutoFitColumn(4);
sl.AutoFitColumn(5);
sl.AutoFitColumn(6);
sl.AutoFitColumn(7);
sl.AutoFitColumn(8);
sl.AutoFitColumn(9);
sl.AutoFitColumn(10);
string arquivo;
if (!String.IsNullOrEmpty(dataInicial) && !String.IsNullOrEmpty(dataFinal))
{
arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados "
+ dataInicial +
" á " + dataFinal + ".xlsx");
}
else
{
arquivo = Server.MapPath(@"UploadFile\\" + "Relatorio_Festa_Aposentados_Geral.xlsx");
}
sl.SaveAs(arquivo);
}
/// <summary>
/// Exports the poll result report.
/// </summary>
/// <param name="poll">The poll.</param>
/// <param name="pollResponse">The poll response.</param>
/// <param name="workSheetName">Name of the work sheet.</param>
/// <returns>
/// excel byte array
/// </returns>
public byte[] GetFailureExportReport(IEnumerable <AssetManager.Models.FailureView> failures, string workSheetName)
{
var textCenterBold = new SLStyle()
{
Alignment = new SLAlignment()
{
Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center
}, Font = { Bold = true }
};
var textCenter = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
};
var textLeft = new SLStyle()
{
Alignment = { Horizontal = HorizontalAlignmentValues.Left }
};
var headColoumnStyle = new SLStyle()
{
Font = { FontSize = 12 }
};
SLStyle highlightStyle = new SLStyle()
{
Font = { FontSize = 12, Bold = true }
};
highlightStyle.SetWrapText(true);
highlightStyle.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
highlightStyle.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.Gray);
textCenter.SetWrapText(true);
highlightStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
highlightStyle.Alignment.Vertical = VerticalAlignmentValues.Center;
MemoryStream memoryStream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheetName = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheetName, workSheetName);
document.SetCellValue(1, 1, string.Format("SFR Report {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.SetCellStyle(1, 1, textCenterBold);
document.MergeWorksheetCells(1, 1, 1, 16);
document.SetCellValue(2, 1, "SL");
document.SetColumnWidth(1, 4);
document.SetColumnWidth(2, 4);
document.SetColumnWidth(3, 3.84);
document.SetColumnWidth(4, 8);
document.SetColumnWidth(5, 8);
document.SetColumnWidth(6, 8);
document.SetColumnWidth(7, 8);
document.SetColumnWidth(8, 19.84);
document.SetColumnWidth(9, 10);
document.SetColumnWidth(10, 10);
document.SetColumnWidth(11, 9.75);
document.SetColumnWidth(12, 11);
document.SetColumnWidth(13, 9);
document.SetColumnWidth(14, 11);
document.SetColumnWidth(15, 9);
document.SetColumnWidth(16, 8.75);
//document.SetColumnWidth(17, 8.75);
document.SetCellValue(2, 2, "SFRNo");
document.SetCellValue(2, 3, "Reported");
document.SetCellValue(2, 4, "Chargeable / Non Chargeable");
document.SetCellValue(2, 5, "Station");
document.SetCellValue(2, 6, "Gear at Fault");
document.SetCellValue(2, 7, "Sub Gear at Fault");
document.SetCellValue(2, 8, "Brief Description");
document.SetCellValue(2, 9, "Cause of Failure");
document.SetCellValue(2, 10, "Sub Cause of Failure");
//document.SetCellValue(1, 7, "Train Loss Punctuality");
document.SetCellValue(2, 11, "Train Detained");
//document.SetCellValue(1, 9, "Failure Chargeable");
//document.SetCellValue(1, 8, "Department");
document.SetCellValue(2, 12, "Time of Occ urrence");
document.SetCellValue(2, 13, "Time Signal Main In formed");
document.SetCellValue(2, 14, "Time Signal Main Reached");
document.SetCellValue(2, 15, "Time Rectified");
document.SetCellValue(2, 16, "Dur ation");
//document.SetCellValue(2, 17, "Last Visit");
var pageSettings = document.GetPageSettings();
pageSettings.ScalePage(95);
pageSettings.PaperSize = SLPaperSizeValues.A4Paper;
pageSettings.Orientation = OrientationValues.Landscape;
pageSettings.HeaderMargin = 0.3;
pageSettings.TopMargin = 0.2;
pageSettings.BottomMargin = 0.2;
pageSettings.LeftMargin = 0.2;
pageSettings.RightMargin = 0.2;
pageSettings.FooterMargin = 0.3;
pageSettings.OddFooterText = "Designed and Developded by Krishna Infotech";
pageSettings.AppendOddFooter(SLHeaderFooterFormatCodeValues.Right);
document.SetPageSettings(pageSettings);
document.SetCellStyle(2, 1, 2, 16, highlightStyle);
int lastActiveRow = 3;
failures.ForEach(failure =>
{
document.SetCellValue(lastActiveRow, 1, lastActiveRow - 2);
document.SetCellValue(lastActiveRow, 2, failure.SFRNo);
document.SetCellValue(lastActiveRow, 3, failure.Reported);
document.SetCellValue(lastActiveRow, 4, failure.FailureChargeable ? "Yes" : "No");
document.SetCellValue(lastActiveRow, 5, failure.Station);
document.SetCellValue(lastActiveRow, 6, failure.GearFault);
document.SetCellValue(lastActiveRow, 7, failure.SubGearFault);
document.SetCellValue(lastActiveRow, 8, $"{failure.Description} ---- SSE:{failure.SSELastVisitOn.ToString()} JE:{failure.JELastVisitOn.ToString()} ESM: {failure.ESMLastVisitOn.ToString()}");
document.SetCellValue(lastActiveRow, 9, failure.CauseOfFailureValue);
document.SetCellValue(lastActiveRow, 10, failure.SubCauseOfFailureValue);
document.SetCellValue(lastActiveRow, 11, failure.TrainDetained);
document.SetCellValue(lastActiveRow, 12, failure.TimeOfOccurance.ToString());
document.SetCellValue(lastActiveRow, 13, failure.TimeSignalMainInformed.ToString());
document.SetCellValue(lastActiveRow, 14, failure.TimeSignalMainReached.ToString());
document.SetCellValue(lastActiveRow, 15, failure.TimeRectified.ToString());
document.SetCellValue(lastActiveRow, 16, failure.TotalDuration.HasValue ? Math.Round(TimeSpan.FromHours(failure.TotalDuration.Value).TotalMinutes).ToString() : string.Empty);
lastActiveRow++;
});
document.SetCellStyle(3, 1, lastActiveRow, 16, textCenter);
var footerIndex = failures.Count() + 6;
document.SetCellValue(footerIndex, 1, string.Format("Generated on {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.MergeWorksheetCells(footerIndex, 1, footerIndex, 3);
document.SetCellValue(footerIndex, 4, "This report is generated by SFR Software");
document.MergeWorksheetCells(footerIndex, 4, footerIndex, 9);
document.SaveAs(memoryStream);
return(memoryStream.ToArray());
}
}
