protected override Stream RenderToStream(Document document)
{
using (SLDocument slDocument = new SLDocument())
{
var sheets = document.Sheets.ToList();
if (!string.IsNullOrWhiteSpace(sheets[0].Name))
{
slDocument.RenameWorksheet(slDocument.GetCurrentWorksheetName(), sheets[0].Name);
}
int rowCounter = 0;
foreach (var row in sheets[0].Rows)
{
rowCounter++;
if (row == null)
{
continue;
}
this.RenderRow(slDocument, row, rowCounter);
}
var sheetIndex = 1;
foreach (var sheet in sheets.Skip(1))
{
var res = slDocument.AddWorksheet($"Sheet{sheetIndex++}");
if (!string.IsNullOrWhiteSpace(sheet.Name))
{
slDocument.RenameWorksheet(slDocument.GetCurrentWorksheetName(), sheet.Name);
}
rowCounter = 0;
foreach (var row in sheet.Rows)
{
rowCounter++;
if (row == null)
{
continue;
}
this.RenderRow(slDocument, row, rowCounter);
}
}
var stream = new MemoryStream();
slDocument.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
return(stream);
}
}
public static string upisiUFajl(ArrayList rib, String fileName, String clientName)
{
SLDocument sl;
sl = new SLDocument(Params.TEMPLATE_PATH);
int i = 6;
Console.WriteLine(DateTime.Now + ": Pocinje upisivanje u fajl");
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Bankovni računi");
sl.SetCellValue("G2", clientName);
sl.SetCellValue("G3", DateTime.Now.ToString("dd. MMMM yyyy", CultureInfo.GetCultureInfo("sr-Latn-CS")));
foreach (RacunIzBaze onerib in rib)
{
sl.SetCellValue(i, 1, onerib.maticniBroj);
sl.SetCellValue(i, 2, onerib.carlCustomID);
sl.SetCellValue(i, 3, onerib.PIB);
sl.SetCellValue(i, 4, onerib.naziv);
sl.SetCellValue(i, 5, onerib.banka);
sl.SetCellValue(i, 6, onerib.brojRacuna);
sl.SetCellValue(i, 7, onerib.statusRacuna);
if (i % 1000 == 0)
{
Console.WriteLine(DateTime.Now + ": upisano " + i + "/" + rib.Count);
}
i++;
}
string filePath = Params.PUTANJA_ZA_REZULTAT + "\\Qbing računi - " + fileName + " - " + DateTime.Now.ToString("yyyyMMdd") + ".xlsx";
sl.SaveAs(filePath);
Console.WriteLine(DateTime.Now + ": Zavrseno upisivanje u fajl");
return(filePath);
}
public void CreateSpreadsheet()
{
int dataColNo = 0;
int colNo = 1;
int rowNo = 1;
try
{
SetColHeaders();
foreach (DataRow dRow in dSet.Tables[0].Rows)
{
dataColNo = 0;
colNo = 1;
rowNo++;
foreach (object colData in dRow.ItemArray)
{
sldWaspSourceFile.SetCellValue(rowNo, colNo++, colData.ToString().Trim());
dataColNo++;
}
}
sldWaspSourceFile.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);
sldWaspSourceFile.SaveAs(outFilePath);
}catch (Exception ex)
{
lm.Write(entity + " OutputManager.CreateSpreadsheet() " + ex.Message);
}
}
/// <summary>
/// Create a new Excel file, rename the default sheet from
/// Sheet1 to the value in pSheetName
/// </summary>
/// <param name="pFileName"></param>
/// <param name="pSheetName"></param>
/// <returns></returns>
public bool CreateNewFile(string pFileName, string pSheetName)
{
using (var doc = new SLDocument())
{
doc.RenameWorksheet("Sheet1", pSheetName);
doc.SaveAs(pFileName);
return(true);
}
}
/// <summary>
/// Writes to excel.
/// </summary>
/// <param name="dt">The data table.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="orgName">Name of the org.</param>
/// <param name="sheetHeader">The sheet header.</param>
/// <returns>
/// the excel file.
/// </returns>
public static byte[] WriteToExcel(DataTable dt, string sheetName, string orgName, string sheetHeader)
{
MemoryStream stream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheet = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheet, sheetName);
// Set current Org name as main header for the sheet
//document.SetCellValue(1, 1, orgName);
//document.MergeWorksheetCells(1, 1, 1, dt.Columns.Count);
//SLStyle orgNameStyle = document.CreateStyle();
//orgNameStyle.Font.FontName = "Calibri";
//orgNameStyle.Font.FontSize = 14;
//orgNameStyle.Font.FontColor = System.Drawing.Color.Black;
//orgNameStyle.Font.Bold = true;
//orgNameStyle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
//orgNameStyle.Alignment.Vertical = VerticalAlignmentValues.Center;
//orgNameStyle.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGray, SLThemeColorIndexValues.Light1Color);
//orgNameStyle.Fill.SetPatternBackgroundColor(SLThemeColorIndexValues.Light1Color);
//document.SetCellStyle(1, 1, orgNameStyle);
// Set sheet title and merge the sheet name
document.SetCellValue(1, 1, sheetHeader);
document.MergeWorksheetCells(1, 1, 1, dt.Columns.Count);
SLStyle header = document.CreateStyle();
header.Font.FontName = "Calibri";
header.Font.FontSize = 12;
header.Font.FontColor = System.Drawing.Color.Black;
header.Font.Bold = true;
header.Alignment.Horizontal = HorizontalAlignmentValues.Center;
header.Alignment.Vertical = VerticalAlignmentValues.Center;
document.SetCellStyle(1, 1, header);
SLStyle columnHeader = document.CreateStyle();
columnHeader.Font.Bold = true;
document.ImportDataTable(2, 1, dt, true);
document.AutoFitColumn(1, dt.Columns.Count);
document.AutoFitRow(2, dt.Rows.Count);
document.SetCellStyle(2, 1, 2, dt.Columns.Count, columnHeader);
var footerIndex = dt.Rows.Count + 5;
document.SetCellValue(footerIndex, 1, string.Format("Generated on {0}", DateTime.Now.ToString("dd/MM/yyyy")));
document.MergeWorksheetCells(footerIndex, 1, footerIndex, 2);
document.SetCellValue(footerIndex, 3, "This report is generated by Krishna Infotech.");
document.MergeWorksheetCells(footerIndex, 3, footerIndex, dt.Columns.Count);
document.SaveAs(stream);
}
return(stream.ToArray());
}
private void OpenImageToXLSReporteGrafico(List <EstadoCitaCortoDTO> EstadosCitas, byte[] Contenido, string Excel)
{
using (var sl = new SLDocument())
{
var pic = new SLPicture(Contenido, ImagePartType.Png);
pic.SetPosition(3, 2);
sl.InsertPicture(pic);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Grafica UADyCS");
sl.AddWorksheet("Tabla Reporte");
sl.SelectWorksheet("Tabla Reporte");
int filaInicio = 6;
int numeroColumnaInicio = 4;
int numeroColumnaCuerpo = numeroColumnaInicio;
int numeroFila = filaInicio;
sl.SetCellValue(numeroFila - 2, numeroColumnaInicio, "Reporte UADyCS");
sl.SetCellValue(numeroFila, numeroColumnaInicio, "UADyCS");
EstadosCitas.ForEach(fila =>
{
numeroColumnaCuerpo = numeroColumnaInicio + 1;
if (numeroFila == filaInicio)
{
fila.Valores.ForEach(estado => {//Descripcion del estado=> solicitado,atendido,..
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item1); numeroColumnaCuerpo++;
});
}
numeroFila++;
//Nombre de la UADyCS
sl.SetCellValue(numeroFila, numeroColumnaInicio, fila.Descripcion);
numeroColumnaCuerpo = numeroColumnaInicio;
fila.Valores.ForEach(estado => {
numeroColumnaCuerpo++;
//Cantidad del estado
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item2);
});
});
SLTable tbl = sl.CreateTable(filaInicio, numeroColumnaInicio, numeroFila, numeroColumnaCuerpo);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium9);
sl.InsertTable(tbl);
sl.AutoFitColumn(numeroColumnaInicio, numeroColumnaCuerpo);
sl.SaveAs(Excel);
sl.Dispose();
}
}
public async override Task <string> Generate(ViewModel model, Manager manager)
{
var loadTasks = new[] { 1, 2 }
.Select(i => manager.Badge.ListForSectionAsync(model.SelectedSection, i, model.SelectedTerm))
.ToArray();
await Task.WhenAll(loadTasks);
var badges = loadTasks.SelectMany(t => t.Result);
var progressTasks = badges
.Select(b => manager.Badge.ListProgressForBadgeAsync(model.SelectedSection, b, model.SelectedTerm))
.ToArray();
await Task.WhenAll(progressTasks);
var progressReports = progressTasks.ToDictionary(r => r.Result.Badge.Id, r => r.Result);
var document = new SLDocument();
var dateFormat = document.CreateStyle();
dateFormat.FormatCode = "d mmm, yyyy";
var isFirst = true;
foreach (var badge in badges)
{
if (isFirst)
{
isFirst = false;
document.RenameWorksheet(SLDocument.DefaultFirstSheetName, badge.Name);
}
else
{
document.AddWorksheet(badge.Name);
}
document.SelectWorksheet(badge.Name);
WriteHeader(document, badge);
Api.BadgeProgressReport report;
if (!progressReports.TryGetValue(badge.Id, out report))
{
continue;
}
var row = 1;
foreach (var progress in report)
{
document.SetCellStyle(row, 4, dateFormat);
WriteProgress(document, badge, progress, ++row);
}
}
var fileName = GetSafeFileName(model);
document.SaveAs(fileName);
return(fileName);
}
/// <summary>
/// Converts the HTML to EXCEL.
/// </summary>
/// <returns>Converted document as byte array.</returns>
public byte[] ConvertHTMLToXL(string htmlStr)
{
//Load html document
var htmlDocument = this.HtmlConverterHelper.GetNewHtmlDocument(htmlStr);
int rowIndex = 1, colIndex = 1;
MemoryStream MemoryStream = new MemoryStream();
var tables = htmlDocument.DocumentNode.Descendants("table").ToList();
tables.ForEach(table =>
{
this.SetSLStyles(table);
});
using (var document = new SLDocument())
{
var rows = tables[0].Descendants("tr");
document.RenameWorksheet(document.GetCurrentWorksheetName(), tables[0].Id);
foreach (var row in rows)
{
var cells = row.Descendants("td");
var rowStyles = this.tableRowStyle[tables[0].Id];
var cellStyles = this.tableCellStyle[tables[0].Id];
colIndex = 1;
foreach (var cell in cells)
{
HtslCellStyle style;
var styleExists = cellStyles.TryGetValue(string.Format(HtslConstants.RowColPlaceHolder, rowIndex, colIndex), out style);
document.SetCellValue(rowIndex, colIndex, cell.InnerText);
if (styleExists)
{
document.SetCellStyle(rowIndex, colIndex, style as SLStyle);
}
++colIndex;
}
++rowIndex;
}
document.SaveAs(MemoryStream);
}
return(MemoryStream.ToArray());
}
/// <summary>
/// Creates a worksheet for the Spreadsheet Document of the
/// </summary>
/// <typeparam name="T">Type of object to represent in the worksheet</typeparam>
/// <param name="records">List of the records to add to the worksheet</param>
/// <param name="tabName">Name for the worksheet tab. Pasing nothing will result in {TypeName}List. </param>
/// <param name="makeTable">Will create a table for the records.</param>
public void CreateAndAppendWorksheet <T>(IEnumerable <T> records, string tabName = "", bool makeTable = true)
{
Type t = typeof(T);
if (tabName == "")
{
tabName = String.Format("{0}List", t.Name);
}
if (doc.SelectWorksheet("Sheet1"))
{
doc.RenameWorksheet("Sheet1", tabName);
}
else
{
doc.AddWorksheet(tabName);
}
if (FirstSheet == "")
{
FirstSheet = tabName;
}
List <PropertyInfo> properties = OrderProperties(t);
List <int> hideColumns = new List <int>();
int propertyCount = properties.Count;
Type[] propertyTypes = new Type[propertyCount];
DisplayFormatAttribute[] formatAttributes = new DisplayFormatAttribute[propertyCount];
DisplayNoWrap[] wrapAttributes = new DisplayNoWrap[propertyCount];
DisplayNameAttribute[] nameAttributes = new DisplayNameAttribute[propertyCount];
DisplayWidth[] widthAttributes = new DisplayWidth[propertyCount];
for (int k = 0; k < propertyCount; k++)
{
PropertyInfo p = properties[k];
if (p.GetCustomAttributes(typeof(DisplayHide), true).Cast <DisplayHide>().FirstOrDefault() != null)
{
hideColumns.Add(k);
}
formatAttributes[k] = p.GetCustomAttributes(typeof(DisplayFormatAttribute), true).Cast <DisplayFormatAttribute>().FirstOrDefault();
wrapAttributes[k] = p.GetCustomAttributes(typeof(DisplayNoWrap), true).Cast <DisplayNoWrap>().FirstOrDefault();
nameAttributes[k] = p.GetCustomAttributes(typeof(DisplayNameAttribute), true).Cast <DisplayNameAttribute>().FirstOrDefault();
widthAttributes[k] = p.GetCustomAttributes(typeof(DisplayWidth), true).Cast <DisplayWidth>().FirstOrDefault();
propertyTypes[k] = p.PropertyType;
}
CreateHeader(properties, hideColumns, nameAttributes);
CreateRows <T>(records, properties, hideColumns, formatAttributes, propertyTypes);
FormatColumns(properties, records, makeTable, hideColumns, wrapAttributes, widthAttributes);
}
public SLDocument LoadTextFile(SLDocument sldocWorksheet, string strTextFileFullPath)
{
sldocWorksheet.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Delimited");
SLTextImportOptions sltioWorksheetOptions = new SLTextImportOptions();
sltioWorksheetOptions.Culture = new CultureInfo("en-US");
SLWorksheetStatistics sldocstatsWorksheetStats = sldocWorksheet.GetWorksheetStatistics();
int iLastColumnWithData = sldocstatsWorksheetStats.EndColumnIndex;
for (int iCounter = 0; iCounter < iLastColumnWithData; iCounter++)
{
sltioWorksheetOptions.SetColumnFormat(iCounter, SLTextImportColumnFormatValues.Text);
}
sldocWorksheet.ImportText(strTextFileFullPath, "A1", sltioWorksheetOptions);
return(sldocWorksheet);
}
public void CreateSpreadsheet()
{
int dataColNo = 0;
int colNo = 1;
int rowNo = 1;
if (sldStockStatus == null)
{
sldStockStatus = new SLDocument();
}
try
{
if (worksheetCount++ > 0)
{
sldStockStatus.AddWorksheet(location);
}
else
{
sldStockStatus.RenameWorksheet(SLDocument.DefaultFirstSheetName, location);
}
SetColHeaders();
foreach (DataRow dRow in dSet.Tables[0].Rows)
{
dataColNo = 0;
colNo = 1;
rowNo++;
foreach (object colData in dRow.ItemArray)
{
if (!skipCols.Contains(dataColNo++))
{
sldStockStatus.SetCellValue(rowNo, colNo++, colData.ToString());
}
}
}
// sldStockStatus.SaveAs(outFilePath);
}
catch (Exception ex)
{
lm.Write(location + " OutputManager.CreateSpreadsheet() " + ex.Message);
}
}
/// <summary>
/// Writes to excel.
/// </summary>
/// <param name="dt">The data table.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns>
/// the excel file.
/// </returns>
public static byte[] WriteToExcel(DataTable dt, string sheetName)
{
MemoryStream stream = new MemoryStream();
using (SLDocument document = new SLDocument())
{
var currentSheet = document.GetCurrentWorksheetName();
document.RenameWorksheet(currentSheet, sheetName);
SLStyle columnHeader = document.CreateStyle();
columnHeader.Font.Bold = true;
document.ImportDataTable(1, 1, dt, true);
document.SetCellStyle(1, 1, 1, dt.Columns.Count, columnHeader);
document.AutoFitColumn(1, dt.Columns.Count);
document.AutoFitRow(1, dt.Rows.Count);
document.SaveAs(stream);
}
return(stream.ToArray());
}
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 async override Task <string> Generate(ViewModel model, Manager manager)
{
var loadTasks = new[] { 1, 2 }
.Select(i => manager.Badge.ListForSectionAsync(model.SelectedSection, i, model.SelectedTerm))
.ToArray();
await Task.WhenAll(loadTasks);
var badges = loadTasks.SelectMany(t => t.Result);
var members = await manager.Member.ListForSectionAsync(model.SelectedSection, model.SelectedTerm);
var progressTasks = badges
.Select(b => manager.Badge.ListProgressForBadgeAsync(model.SelectedSection, b, model.SelectedTerm))
.ToArray();
var progressReports = await Task.WhenAll(progressTasks);
var document = new SLDocument();
document.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Badge Achievement");
var column = 0;
document.SetCellValue(1, ++column, "Family Name");
document.SetCellValue(1, ++column, "First Name");
document.SetCellValue(1, ++column, "Date of Birth");
var orderedBadges = badges.OrderBy(b => b.Category).ThenBy(b => b.Group).ThenBy(b => b.Name).ToArray();
var lastGroup = string.Empty;
foreach (var badge in orderedBadges)
{
document.SetCellValue(2, ++column, badge.Name);
if (lastGroup != badge.Group)
{
lastGroup = badge.Group;
document.SetCellValue(1, column, lastGroup);
}
}
var dateFormat = document.CreateStyle();
dateFormat.FormatCode = "d mmm, yyyy";
var row = 2;
var achievements = progressReports
.SelectMany(r => r)
.GroupBy(p => p.Member.Id)
.ToDictionary(g => g.Key, g => g.ToDictionary(b => b.Badge.Id, b => b.IsCompleted));
foreach (var member in members)
{
column = 0;
++row;
document.SetCellValue(row, ++column, member.FamilyName);
document.SetCellValue(row, ++column, member.FirstName);
++column;
if (member.WhenBorn.HasValue)
{
document.SetCellValue(row, column, member.WhenBorn.Value);
}
document.SetCellStyle(row, column, dateFormat);
Dictionary <string, bool> badgesCompleted;
if (!achievements.TryGetValue(member.Id, out badgesCompleted))
{
continue;
}
foreach (var badge in orderedBadges)
{
bool isCompleted;
document.SetCellValue(row, ++column, badgesCompleted.TryGetValue(badge.Id, out isCompleted) && isCompleted ? "Yes" : null);
}
}
var fileName = GetSafeFileName(model);
document.SaveAs(fileName);
return(fileName);
}
private void BotonExportar_Click(object sender, RoutedEventArgs e)
{
if (RadioExcel.IsChecked == true)
{
var excelD = new SLDocument();
SLStyle StyleHeaders = excelD.CreateStyle();
StyleHeaders.Fill.SetPattern(PatternValues.Solid,
System.Drawing.Color.FromArgb(184, 204, 228),
System.Drawing.Color.FromArgb(184, 204, 228)
);
StyleHeaders.SetFontBold(true);
InsertDataTableIntoExcelDocument(ref excelD, TablaParaExportar, 1, 1, true);
for (int i = 1; i < TablaParaExportar.Columns.Count + 1; i++)
{
excelD.SetCellStyle(1, i, StyleHeaders);
if (i != 1)
{
excelD.AutoFitColumn(i);
}
}
var TablaCMD = excelD.CreateTable(1, 1, TablaParaExportar.Rows.Count, TablaParaExportar.Columns.Count);
TablaCMD.HasTotalRow = false;
TablaCMD.HasAutoFilter = true;
TablaCMD.DisplayName = "TablaDeComandos";
excelD.InsertTable(TablaCMD);
excelD.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Comandos");
excelD.AddWorksheet("Datos");
//Busqueda de columnas
var ListaDeColumnas = new List <string>();
var OnlyDataT = new DataTable();
Data2Show.ForEach(delegate(SortedList <String, String> _Registro)
{
var ColumnasNoAgregadas = (from V in _Registro.Keys.ToList <string>() where !ListaDeColumnas.Contains(V) select V).ToList();
ColumnasNoAgregadas.ForEach(delegate(string Cname)
{
ListaDeColumnas.Add(Cname);
OnlyDataT.Columns.Add(Cname);
});
});
//Procesando la información para generalizar los casos mas comunes
Data2Show.ForEach(delegate(SortedList <String, String> _Registro)
{
var NuevaFila = OnlyDataT.NewRow();
_Registro.Keys.ToList <string>().ForEach(
delegate(string Cname)
{
NuevaFila[Cname] = _Registro[Cname];
});
OnlyDataT.Rows.Add(NuevaFila);
});
InsertDataTableIntoExcelDocument(ref excelD, OnlyDataT, 1, 1, true);
for (int i = 1; i < OnlyDataT.Columns.Count + 1; i++)
{
excelD.SetCellStyle(1, i, StyleHeaders);
excelD.AutoFitColumn(i);
}
var Tabla = excelD.CreateTable(1, 1, OnlyDataT.Rows.Count, OnlyDataT.Columns.Count);
Tabla.HasTotalRow = false;
Tabla.HasAutoFilter = true;
Tabla.DisplayName = "TablaDeDatos";
excelD.InsertTable(Tabla);
try
{
excelD.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\ReporteDeCargaArduino2DB.xlsx");
MessageBox.Show("Se ha exportado correctamente el reporte al escritorio con el nombre de ReporteDeCargaArduino2DB");
}
catch (Exception MyE)
{
MessageBox.Show("Se ha detectado un error al crear el documento de excel, " + MyE.Message.ToString());
}
}
if (RadioCSV.IsChecked == true)
{
ToCSV(TablaParaExportar, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\CsvReporteDeCarga.csv");
//Busqueda de columnas
var ListaDeColumnas = new List <string>();
var OnlyDataT = new DataTable();
Data2Show.ForEach(delegate(SortedList <String, String> _Registro)
{
var ColumnasNoAgregadas = (from V in _Registro.Keys.ToList <string>() where !ListaDeColumnas.Contains(V) select V).ToList();
ColumnasNoAgregadas.ForEach(delegate(string Cname)
{
ListaDeColumnas.Add(Cname);
OnlyDataT.Columns.Add(Cname);
});
});
//Procesando la información para generalizar los casos mas comunes
Data2Show.ForEach(delegate(SortedList <String, String> _Registro)
{
var NuevaFila = OnlyDataT.NewRow();
_Registro.Keys.ToList <string>().ForEach(
delegate(string Cname)
{
NuevaFila[Cname] = _Registro[Cname];
});
OnlyDataT.Rows.Add(NuevaFila);
});
ToCSV(OnlyDataT, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\CsvData.csv");
}
}
public void General_Reesters(string v1, string v2)
{
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Реестр за арендованных и собственных вагон-цистерн компании.xlsx";
DataTable dt, dataTable, Itog_Rep;
string Name;
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("ТОО Казыкурт");
sl.RenameWorksheet("ТОО Казыкурт", "Реестр");
sl.AddWorksheet("Temp");
string RefreshAllCount = "exec dbo.GetReportAllRenderedService_v1 '" + v1 + "','" + v2 + "'," + "@Type = " + 2;
dt = DbConnection.DBConnect(RefreshAllCount);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 1)
{
Name = dt.Rows[i][j].ToString();
sl.CopyWorksheet("Реестр", Name);
}
}
}
sl.SelectWorksheet("Реестр");
sl.DeleteWorksheet("Temp");
int k = 0;
foreach (var name in sl.GetWorksheetNames())
{
if (name == "Реестр")
{
string RefreshAll = "exec dbo.GetReportAllRenderedService_v1 '" + v1 + "','" + v2 + "'," + "@Type = " + 1;
dataTable = DbConnection.DBConnect(RefreshAll);
string GetCountServiceCost = "exec dbo.Itog_All_Report '" + v1 + "','" + v2 + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
sl.SelectWorksheet(name);
sl.SetCellValue("C6", "в ТОО Казыгурт-Юг c " + v1 + " по " + v2);
var val = dataTable.Rows.Count + 16 + Itog_Rep.Rows.Count * 2;
sl.CopyCell("B13", "K20", "B" + val, true);
sl.ImportDataTable(10, 1, dataTable, false);
sl.CopyCell("E10", "S" + Convert.ToString(dataTable.Rows.Count + 10), "G10", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j < dataTable.Columns.Count + 3; j++)
{
sl.SetCellStyle(i + 10, j, FormattingExcelCells(sl, true));
}
}
sl.SetCellValue(dataTable.Rows.Count + 12, 2, "=C6");
sl.SetCellStyle(dataTable.Rows.Count + 12, 2, FormattingExcelCells(sl, false));
sl.SetCellValue(dataTable.Rows.Count + 14, 2, "Всего обработано вагонов - цистерн всех собственников по видам операций:");
sl.SetCellStyle(dataTable.Rows.Count + 14, 2, FormattingExcelCells(sl, false));
//Итоговая сводка
int rowcount = 0;
//int total = 0;
double EndSum = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 2, FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 12, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 12, FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
sl.SetCellValue(dataTable.Rows.Count + 14, 13, dataTable.Rows.Count);
sl.SetCellStyle(dataTable.Rows.Count + 14, 13, FormattingExcelCells(sl, false));
//Итоговая сумма
sl.SetCellValue(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, EndSum);
sl.SetCellStyle(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, FormattingExcelCells(sl, false));
GetCountServiceCost = string.Empty;
dataTable.Clear();
Itog_Rep.Clear();
}
else
{
int cs = Convert.ToInt32(dt.Rows[k][0].ToString());
string Refresh = "dbo.GetReportRenderedServices_v1 '" + v1 + "','" + v2 + "','" + cs + "'";
dataTable = DbConnection.DBConnect(Refresh);
string GetCountServiceCost = "exec dbo.Itog_Report '" + v1 + "','" + v2 + "','" + cs + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
Name = dt.Rows[k][1].ToString();
sl.SelectWorksheet(Name);
sl.SetCellValue("C4", dt.Rows[k][1].ToString());
sl.SetCellValue("C6", "в ТОО Казыгурт-Юг c " + v1 + " по " + v2);
var val = dataTable.Rows.Count + 16 + Itog_Rep.Rows.Count * 2;
sl.CopyCell("B13", "K20", "B" + val, true);
sl.ImportDataTable(10, 1, dataTable, false);
sl.CopyCell("E10", "S" + Convert.ToString(dataTable.Rows.Count + 10), "G10", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j < dataTable.Columns.Count + 3; j++)
{
sl.SetCellStyle(i + 10, j, FormattingExcelCells(sl, true));
}
}
sl.SetCellValue(dataTable.Rows.Count + 12, 2, "=C6");
sl.SetCellStyle(dataTable.Rows.Count + 12, 2, FormattingExcelCells(sl, false));
sl.SetCellValue(dataTable.Rows.Count + 14, 2, "Всего обработано вагонов - цистерн всех собственников по видам операций:");
sl.SetCellStyle(dataTable.Rows.Count + 14, 2, FormattingExcelCells(sl, false));
//Итоговая сводка
int rowcount = 0;
//int total = 0;
double EndSum = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 2, FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 12, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 12, FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
sl.SetCellValue(dataTable.Rows.Count + 14, 13, dataTable.Rows.Count);
sl.SetCellStyle(dataTable.Rows.Count + 14, 13, FormattingExcelCells(sl, false));
//Итоговая сумма
sl.SetCellValue(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, EndSum);
sl.SetCellStyle(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, FormattingExcelCells(sl, false));
GetCountServiceCost = string.Empty;
dataTable.Clear();
Itog_Rep.Clear();
k++;
}
}
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\Общий Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\Общий Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
public SLDocument MakeEXCEL(List <DocType> dts)
{
int i = 1;
SLDocument sl = new SLDocument();
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "工作表1");
SLConditionalFormatting cf = new SLConditionalFormatting("C2", "C" + (dts.Count + 1).ToString());
cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill);
cf.HighlightCellsEqual(true, "-1", SLHighlightCellsStyleValues.LightRedFill);
sl.AddConditionalFormatting(cf);
cf = new SLConditionalFormatting("B2", "B" + (dts.Count + 1).ToString());
cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill);
cf.HighlightCellsEqual(true, "0", SLHighlightCellsStyleValues.LightRedFill);
sl.AddConditionalFormatting(cf);
cf = new SLConditionalFormatting("A2", "A" + (dts.Count + 1).ToString());
cf.HighlightCellsWithDuplicates(SLHighlightCellsStyleValues.LightRedFill);
cf.HighlightCellsEqual(true, "0", SLHighlightCellsStyleValues.LightRedFill);
sl.AddConditionalFormatting(cf);
cf = new SLConditionalFormatting("J2", "J" + (dts.Count + 1).ToString());
cf.HighlightCellsWithFormula("=DATE(YEAR($J2),MONTH($J2)-1,DAY($J2)) <= TODAY()", SLHighlightCellsStyleValues.LightRedFill);
sl.AddConditionalFormatting(cf);
SLStyle style = sl.CreateStyle();
style.Alignment.WrapText = true;
style.Alignment.Horizontal = HorizontalAlignmentValues.Center;
style.Alignment.Vertical = VerticalAlignmentValues.Center;
sl.SetCellStyle(1, 1, style);
sl.SetCellStyle(1, 2, style);
sl.SetCellStyle(1, 3, style);
sl.SetCellStyle(1, 4, style);
sl.SetCellStyle(1, 5, style);
sl.SetCellStyle(1, 6, style);
sl.SetCellStyle(1, 7, style);
sl.SetCellStyle(1, 8, style);
sl.SetCellStyle(1, 9, style);
sl.SetCellStyle(1, 10, style);
sl.SetCellStyle(1, 11, style);
sl.SetCellStyle(1, 12, style);
sl.SetCellStyle(1, 13, style);
sl.SetColumnWidth(1, 10);
sl.SetColumnWidth(2, 15);
sl.SetColumnWidth(3, 10);
sl.SetColumnWidth(4, 60);
sl.SetColumnWidth(5, 10);
sl.SetColumnWidth(6, 20);
sl.SetColumnWidth(7, 15);
sl.SetColumnWidth(8, 20);
sl.SetColumnWidth(9, 20);
sl.SetColumnWidth(10, 20);
sl.SetColumnWidth(11, 10);
sl.SetColumnWidth(12, 10);
sl.SetColumnWidth(13, 50);
sl.SetCellValue(1, 1, "表單序號");
sl.SetCellValue(1, 2, "表單代號");
sl.SetCellValue(1, 3, "網頁代碼");
sl.SetCellValue(1, 4, "表單名稱");
sl.SetCellValue(1, 5, "版本");
sl.SetCellValue(1, 6, "制訂單位");
sl.SetCellValue(1, 7, "文件類別");
sl.SetCellValue(1, 8, "首次公佈時間");
sl.SetCellValue(1, 9, "最近檢視時間");
sl.SetCellValue(1, 10, "預計檢視時間");
sl.SetCellValue(1, 11, "負責同仁");
sl.SetCellValue(1, 12, "備註");
sl.SetCellValue(1, 13, "備註(2)");
style.Font.FontColor = System.Drawing.Color.Red;
foreach (var y in dts)
{
//sl.SetCellValue(i + 1, 1, Convert.ToInt64(y.Index));
sl.SetCellValue(i + 1, 2, y.ID);
if (y.Eng)
{
sl.SetCellStyle(i + 1, 2, style);
}
sl.SetCellValue(i + 1, 3, Convert.ToInt64(y.webID));
sl.SetCellValue(i + 1, 4, y.Name);
sl.SetCellValue(i + 1, 5, Convert.ToDouble(y.Version));
sl.SetCellValue(i + 1, 6, y.Depart);
sl.SetCellValue(i + 1, 7, y.doctp);
sl.SetCellValue(i + 1, 8, y.Stime);
sl.SetCellValue(i + 1, 9, y.Rtime);
//sl.SetCellValue(i + 1, 10, y.Ntime.ToString("yyy-MM-dd"));
sl.SetCellValue(i + 1, 10, string.Format("=IF(I{0}=\"\",\"\",DATE(YEAR(I{0})+{1},MONTH(I{0}),DAY(I{0})))", i + 1, y.Ntime.Year - y.Rtime.Year));
if (y.Ntime.AddMonths(-1) < DateTime.Now)
{
sl.SetCellStyle(i + 1, 5, style);
sl.SetCellStyle(i + 1, 9, style);
sl.SetCellStyle(i + 1, 10, style);
}
sl.SetCellValue(i + 1, 11, y.Own);
//sl.SetCellValue(i + 1, 13, y.Color);
SLStyle st2 = sl.CreateStyle();
st2.FormatCode = "#,##0.0";
sl.SetCellStyle(i + 1, 5, st2);
st2.FormatCode = "yyyy/mm/dd";
sl.SetCellStyle(i + 1, 8, st2);
sl.SetCellStyle(i + 1, 9, st2);
sl.SetCellStyle(i + 1, 10, st2);
SLStyle stp = sl.CreateStyle();
stp.Protection.Locked = false;
stp.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.FromArgb(255, 204, 255, 255), System.Drawing.Color.DarkSalmon);
stp.SetRightBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon);
stp.SetLeftBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon);
stp.SetTopBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon);
stp.SetBottomBorder(BorderStyleValues.Thin, System.Drawing.Color.DarkSalmon);
sl.SetCellStyle(i + 1, 5, stp);
sl.SetCellStyle(i + 1, 9, stp);
i++;
}
SLSheetProtection sp = new SLSheetProtection();
sp.AllowInsertRows = false;
sp.AllowInsertColumns = false;
sp.AllowFormatCells = true;
sp.AllowDeleteColumns = false;
sp.AllowDeleteRows = false;
sp.AllowSelectUnlockedCells = true;
sp.AllowSelectLockedCells = false;
sl.ProtectWorksheet(sp);
return(sl);
}
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" }));
}
}
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 void RenameWorksheet(string oldWorksheetName, string newWorksheetName)
{
_excelDocument.RenameWorksheet(oldWorksheetName, newWorksheetName);
}
private void ExportToExcel()
{
#region Variables
DBConnection dbCon = null;
SLDocument slDoc = new SLDocument();
SLStyle slStyle = null;
DataSet ds = null;
DataTable dt = null;
int intCriteria = 0;
int intLineStart = 3;
int intLine = intLineStart;
string[,] arrCriteria = new string[16, 2];
string strAppPath = Server.MapPath("~");
string strFileName = string.Empty;
string strFolder = ConfigurationManager.AppSettings[utilSOCYWeb.cWCKReportFolder].ToString();
#endregion Variables
//lblError.Text = "";
#region Export to Excel
slDoc.SetCellValue("A1", lblReportTitle.Text);
slStyle = slDoc.CreateStyle();
slStyle.SetFontBold(true);
slStyle.Font.FontSize = 14;
slDoc.SetCellStyle("A1", slStyle);
slDoc.SetCellValue("F1", "Report Date: " + DateTime.Now.ToString("dd MMM yyyy HH:mm:ss"));
#region Criteria
slDoc.SetCellValue("A2", "Report Criteria"); //set report heading
slStyle = slDoc.CreateStyle();
slStyle.SetFontBold(true);
slDoc.SetCellStyle("A2", slStyle);
arrCriteria[intCriteria, 0] = "prt_id";
if (cboPartner.Enabled && cboPartner.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblPartner.Text);
slDoc.SetCellValue("B" + intLine, cboPartner.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
arrCriteria[intCriteria, 0] = "cso_id";
if (cboCSO.Enabled && cboCSO.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblCSO.Text);
slDoc.SetCellValue("B" + intLine, cboCSO.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
arrCriteria[intCriteria, 0] = "rgn_id";
if (cboRegion.Enabled && cboRegion.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblRegion.Text);
slDoc.SetCellValue("B" + intLine, cboRegion.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
arrCriteria[intCriteria, 0] = "dst_id";
if (cboDistrict.Enabled && cboDistrict.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblDistrict.Text);
slDoc.SetCellValue("B" + intLine, cboDistrict.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
arrCriteria[intCriteria, 0] = "sct_id";
if (cboSubCounty.Enabled && cboSubCounty.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblSubcounty.Text);
slDoc.SetCellValue("B" + intLine, cboSubCounty.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
arrCriteria[intCriteria, 0] = "wrd_id";
if (cboParish.Enabled && cboParish.SelectedIndex != 0)
{
slDoc.SetCellValue("A" + intLine, lblParish.Text);
slDoc.SetCellValue("B" + intLine, cboParish.SelectedItem.Text);
intLine++;
}
else
{
arrCriteria[intCriteria, 1] = "";
}
intCriteria++;
#endregion Criteria
#region Report
dbCon = new DBConnection(utilSOCYWeb.cWCKConnection);
try
{
switch (Session["reportid"].ToString())
{
case utilSOCYWeb.CRHomeVisitAggregate:
ReportsCapturedDataDB.reportType = utilSOCYWeb.CRHomeVisitAggregate;
ReportsCapturedDataDB.prt_id = cboPartner.SelectedValue.ToString() != string.Empty ? cboPartner.SelectedValue.ToString() : null;
ReportsCapturedDataDB.cso = cboCSO.SelectedValue != string.Empty ? cboCSO.SelectedValue.ToString() : null;
ReportsCapturedDataDB.region = cboRegion.SelectedValue != string.Empty ? cboRegion.SelectedValue.ToString() : null;
ReportsCapturedDataDB.district = cboDistrict.SelectedValue != string.Empty ? cboDistrict.SelectedValue.ToString() : null;
ReportsCapturedDataDB.subcounty = cboSubCounty.SelectedValue != string.Empty ? cboSubCounty.SelectedValue.ToString() : null;
ReportsCapturedDataDB.parish = cboParish.SelectedValue != string.Empty ? cboParish.SelectedValue.ToString() : null;
#region Dates
if (txtCreateDateFrom.Text != string.Empty)
{
ReportsCapturedDataDB.datecreateFrom = Convert.ToDateTime(txtCreateDateFrom.Text);
}
else
{
ReportsCapturedDataDB.datecreateFrom = null;
}
if (txtCreateDateTo.Text != string.Empty)
{
ReportsCapturedDataDB.datecreateTo = Convert.ToDateTime(txtCreateDateTo.Text);
}
else
{
ReportsCapturedDataDB.datecreateTo = null;
}
#endregion Dates
ds = ReportsCapturedDataDB.GetReportData();
break;
}
if (ds != null)
{
for (int intCount = 0; intCount < ds.Tables.Count; intCount++)
{
#region Sheet Setup
dt = ds.Tables[intCount];
if (intCount == 0)
{
slDoc.RenameWorksheet(SLDocument.DefaultFirstSheetName, dt.TableName);
}
else
{
slDoc.AddWorksheet(dt.TableName);
intLine = 1;
}
#endregion Sheet Setup
#region Headers
for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
{
slDoc.SetCellValue(Number2ExcelColumn(intCol) + intLine, dt.Columns[intCol].ColumnName);
slStyle = slDoc.CreateStyle();
slStyle.SetFontBold(true);
slDoc.SetCellStyle(Number2ExcelColumn(intCol) + intLine, slStyle);
}
intLine++;
#endregion Headers
#region Data
for (int intRow = 0; intRow < dt.Rows.Count; intRow++)
{
for (int intCol = 0; intCol < dt.Columns.Count; intCol++)
{
slDoc.SetCellValue(Number2ExcelColumn(intCol) + (intLine + intRow), dt.Rows[intRow][intCol].ToString());
}
}
intLine = intLine + dt.Rows.Count;
#endregion Data
}
}
}
finally
{
dbCon.Dispose();
}
#endregion Report
#region Save File
#region File Name
strFileName = "Captured_Data_" + Session["reportid"].ToString() + "_" + DateTime.Now.ToString("yyyyMMMddHHmmss");
#endregion File Name
try
{
slDoc.SaveAs(strAppPath + "\\" + strFolder + "\\" + strFileName + ".xlsx");
Response.Redirect(strFolder + "\\" + strFileName + ".xlsx");
//reset the report type
Session["reportid"] = null;
}
catch (OutOfMemoryException exc)
{
string script = "alert(\"Not enough Server Memory to process request. Please add additional Report Criteria to reduce the number of records returned\");";
ScriptManager.RegisterStartupScript(this, GetType(),
"ServerControlScript", script, true);
}
slDoc.Dispose();
#endregion Save File
#endregion Export to Excel
}
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"));
}
}
private static void ExtractAirplaneCalendar(string filePath, string saveFilePath, IEnumerable <SheetInstructions> sheetInstructions)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
{
var xlsxDocument = new SLDocument();
var workSheetNumber = 0;
foreach (var sheetInstruction in sheetInstructions)
{
if (workSheetNumber++ == 0)
{
xlsxDocument.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetInstruction.SheetName);
}
else
{
xlsxDocument.AddWorksheet(sheetInstruction.SheetName);
}
int currentRow = 1, currentColumn = 1;
xlsxDocument.SetColumnWidth(1, 15);
xlsxDocument.SetRowHeight(1, 50);
// Retrieve a reference to the workbook part.
var wbPart = document.WorkbookPart;
var theSheet = wbPart.Workbook.Descendants <Sheet>().FirstOrDefault(s => s.Name.ToString().Equals(sheetInstruction.SheetName, StringComparison.OrdinalIgnoreCase));
if (theSheet == null)
{
return;
}
var lstRawComments = new Dictionary <string, Comment>();
foreach (var sheet in wbPart.WorksheetParts)
{
var s = GetSheetFromWorkSheet(wbPart, sheet);
if (s.Name.HasValue && s.Name.Value.Equals(sheetInstruction.SheetName, StringComparison.OrdinalIgnoreCase))
{
foreach (var commentsPart in sheet.GetPartsOfType <WorksheetCommentsPart>())
{
foreach (Comment comment in commentsPart.Comments.CommentList)
{
lstRawComments.Add(comment.Reference, comment);
}
}
}
}
var wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
var theRows = wsPart.Worksheet.Descendants <Row>();
var options = RegexOptions.None;
var regex = new Regex("[ ]{2,}", options);
var titleRow = theRows.Skip(sheetInstruction.AirplaneNames.Item1.RowIndex - 1).FirstOrDefault();
var airplaneNamesColumnIndex = sheetInstruction.AirplaneNames.Item1.ColumnIndex - 1;
var airplaneCount = sheetInstruction.AirplaneNames.Item2.ColumnIndex -
sheetInstruction.AirplaneNames.Item1.ColumnIndex + 1;
var titleNames = titleRow.Descendants <Cell>()
.Skip(airplaneNamesColumnIndex)
.Take(airplaneCount)
.Select(t => (regex.Replace(GetCellValue(t, wbPart, out var b).ToString().Trim().Replace("\n", " "), " ")).Replace("|", ""));
var titleCellValues = titleRow
.Descendants <Cell>()
.Skip(airplaneNamesColumnIndex)
.Take(airplaneCount)
.Select(t => Regex.Replace(t.CellReference, @"[^A-Z]+", String.Empty)).ToList();
//clean the airplane names
titleNames = titleNames.Select(t => Regex.Replace(t, @"[^0-9a-zA-Z\s\-]+", String.Empty)).Select(t => regex.Replace(t, " "));
titleNames = (new string[] { "Date" }).Union(titleNames);
titleNames.ToList().ForEach(title =>
{
xlsxDocument.SetColumnWidth(currentColumn, 20);
(new TitleCell()
{
Text = title
}).SetOutputCell(currentRow, currentColumn++, xlsxDocument);
});
currentRow++;
currentColumn = 1;
var meaninfulRows = theRows.Skip(sheetInstruction.CalendarStartPosition.RowIndex - 1)
.Where(r =>
{
if (!TryGetDateFromCell(r.Descendants <Cell>().ElementAt(sheetInstruction.CalendarStartPosition.ColumnIndex - 1), wbPart, out var dateTime))
{
return(false);
}
return(dateTime >= sheetInstruction.MinimDate && dateTime < sheetInstruction.MaximDate);
}).ToList();
string line = null;
foreach (var row in meaninfulRows)
{
var cells = row.Descendants <Cell>().ToList();
var values = row.Descendants <Cell>()
// this list in not linear - it skips columns with no values therefore it needs to be used by reference
//.Skip(airplaneNamesColumnIndex)
//.Take(airplaneCount)
.Select(t =>
{
var ocell = new OutputCell()
{
Text = GetCellValue(t, wbPart, out var isStrikeout).ToString().Trim().Replace("\n", " "),
IsStrikeout = isStrikeout
};
if (lstRawComments.ContainsKey(t.CellReference.ToString()))
{
// ocell.TextComment = lstComments[t.CellReference];
ocell.Comment = lstRawComments[t.CellReference];
}
return(System.Tuple.Create(Regex.Replace(t.CellReference, @"[^A-Z]+", String.Empty), ocell /*v*/));
}).ToDictionary(t => t.Item1, t => t.Item2);
var vals = new List <OutputCell>();
foreach (var letter in titleCellValues)
{
if (values.ContainsKey(letter))
{
vals.Add(values[letter]);
}
else
{
vals.Add(new OutputCell()
{
Text = null
});
}
}
TryGetDateFromCell(cells.ElementAt(sheetInstruction.CalendarStartPosition.ColumnIndex - 1), wbPart, out var dateTime);
var cellValues = (new OutputCell[] { new DateCell()
{
Text = dateTime.ToString("yyyy-MM-dd")
} }).Union(vals);
cellValues.ToList().ForEach(a => a.SetOutputCell(currentRow, currentColumn++, xlsxDocument));
currentRow++;
currentColumn = 1;
}
}
xlsxDocument.SelectWorksheet(sheetInstructions.First().SheetName);
xlsxDocument.SaveAs(saveFilePath);
}
}
/// -----------------------------------------------------------------------------------------------
/// <summary>
/// Get Workbook, setup and insert worksheet, add data, add styles
/// </summary>
/// <param name="dataSet">DataSet</param>
/// <param name="sheetNames">string[]: Names of the individual Sheets in your output</param>
/// <param name="showColumnHeadersOverall">bool</param>
/// <param name="rowIndex">int</param>
/// <param name="columnIndex">int</param>
/// <returns>SLDocument</returns>
/// -----------------------------------------------------------------------------------------------
private SLDocument GenerateWorkbook(
DataSet dataSet,
string[] sheetNames,
bool showColumnHeadersOverall,
int rowIndex = 1,
int columnIndex = 1)
{
try
{
// Declarations
var tableCounter = 0;
var outlineLevel = 0;
// Initializtions
_tableCounter = tableCounter;
_sheetCounter = 0;
// Get first table from Dataset
var dataTable = dataSet.Tables[tableCounter];
// If not then get first child
var baseChild = _settings.ChildSettings[tableCounter];
// Get a name for this sheet
var workingSheetName = GetSheetName(
_sheetCounter,
sheetNames,
baseChild.SheetName,
dataTable.TableName);
// Set the sheet name and select it.
_document.RenameWorksheet(SLDocument.DefaultFirstSheetName, workingSheetName);
_document.SelectWorksheet(workingSheetName);
// Set the row grouping +/- graphic to appear at the top of the grouping
_document.slws.PageSettings.SheetProperties.SummaryBelow = false;
// Set the column grouping +/- graphic to appear at the right of the grouping
_document.slws.PageSettings.SheetProperties.SummaryRight = false;
// Get rows and relation count
var dataRowCollection = dataSet.Tables[tableCounter].Rows;
// Get the bound columns
var columns = dataTable.Columns;
// Get the UDC sorted by column order
var sortedUdc = baseChild.UserDefinedColumns.OrderBy(item => item.FieldOrder);
// Are the column headers going to be shown?
// First check "showColumnHeadersOverall" to see if they have all been turned off (set to false),
// if not then get individual child setting.
var showColumnHeaders = showColumnHeadersOverall && baseChild.ShowColumnHeader;
// Initialize rowCounter counter to starting rowCounter index;
var rowCounter = rowIndex;
// If no records append "No Data" to sheet and return document
// otherwise proceed with data export
if (dataRowCollection.Count == 0)
{
_document.SetCellValue(rowCounter, columnIndex + 1, "No Data");
}
else
{
int childRelationCount;
/****** Adding Column Headers *******/
// Add column headers from either the Data-table or from the UDCs
SetupColumnHeaders(columnIndex, ref rowCounter, showColumnHeaders, outlineLevel, sortedUdc,
columns,
baseChild);
/****** Adding Parent Content Rows *******/
//Looping through Data Table content
var rowOdd = true; // First rowCounter is odd
foreach (DataRow parentRow in dataRowCollection)
{
//Alternate Rows Style
SetupRowsAndCells(rowCounter, columnIndex, rowOdd, sortedUdc, columns, parentRow,
tableCounter);
rowOdd = !rowOdd; // odd/even switch
// Increment the rowCounter count
++rowCounter;
// Is there a Child Relation
childRelationCount = dataSet.Tables[tableCounter].ChildRelations.Count;
// If so, begin the recursive call for the children
if (childRelationCount != 0)
{
// Get the child relation name and call for its children, increment tableCounter & outlineLevel
// for the next family of children
var relationName = dataSet.Tables[tableCounter].ChildRelations[0].ToString();
GetChildren(dataSet, parentRow, workingSheetName, tableCounter + 1, outlineLevel + 1,
relationName, showColumnHeadersOverall, ref rowCounter, columnIndex);
}
}
_document.SelectWorksheet(workingSheetName);
_document.AutoFitColumn(1, 30);
_document.AutoFitRow(1, rowCounter);
// Is there there another table and does it have a Child Relation?
childRelationCount = dataSet.Tables[_tableCounter].ChildRelations.Count;
if (_tableCounter + 1 < dataSet.Tables.Count && childRelationCount == 0)
{
GetSubsequentSheets(dataSet, sheetNames, _tableCounter + 1, showColumnHeadersOverall);
}
}
return(_document);
}
catch (Exception ex)
{
Log.Error("SpreadsheetLightWrapper.Export.Exporter.GenerateWorkbook -> " + ex.Message + ": " + ex);
}
return(null);
}
private async void bStart_Click(object sender, EventArgs e)
{
encoder = new GifBitmapEncoder();
FileStream stream = null;
try
{
stream = new FileStream(tbFileName.Text + ".gif", FileMode.Create);
}
catch (Exception eb)
{
System.Console.WriteLine(eb.Message);
}
imc2 = 0;
InitializeSimulationProperties();
InitializeEnvironment();
//alert - ball too close
if (Convert.ToDouble(nudZs.Value) <= ballStruct.radius)
{
MessageBox.Show("The ball is too close to the pinhole surface (z<=R)!" +
" Change one of the values to start the simulation.", "Attention!",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
}
else
{
progressBar1.Value = 0;
PHOTONSqueue.Clear();
ERRORqueue.Clear();
chartError.ChartAreas[0].AxisX.IsLogarithmic = false;
chartError.Series[0].Points.Clear();
chartLOC.Series[0].Points.Clear();
gbLEDs.Enabled = false;
gbSE.Enabled = false;
gbSP.Enabled = false;
sw = new Stopwatch();
sw.Start();
bStart.Enabled = false;
//----------------------------------------------------------------------
SLDocument sl = new SLDocument();
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Settings");
sl.AddWorksheet("Res1");
Random r0 = new Random(Form1.GLOBAL_RANDOM_VAR.Next() & DateTime.Now.Millisecond);
double xstart = Convert.ToDouble(nudXs.Value);
double xstep = Convert.ToDouble(nudXst.Value);
double xfinni = Convert.ToDouble(nudXsp.Value);
double ystart = Convert.ToDouble(nudYs.Value);
double ystep = Convert.ToDouble(nudYst.Value);
double yfinni = Convert.ToDouble(nudYsp.Value);
double zstart = Convert.ToDouble(nudZs.Value);
double zstep = Convert.ToDouble(nudZst.Value);
double zfinni = Convert.ToDouble(nudZsp.Value);
int imc = 0;
for (int phRad = 1; phRad <= 35; phRad++)
{
for (int zc = 0; zc <= (zfinni - zstart) / zstep; zc++)
{
for (int yc = 0; yc <= (yfinni - ystart) / ystep; yc++)
{
for (int xc = 0; xc <= (xfinni - xstart) / xstep; xc++)
{
CartesianCoordinates pinholeCoordinates = new CartesianCoordinates(x: 0, y: 0, z: 0);
pinholeStuct = new GeometricalObject(pinholeCoordinates, Elementary.Cart2Sphere(pinholeCoordinates), radius: phRad * 0.01, side: 0);
CartesianCoordinates ballCh = new CartesianCoordinates(x: 0, y: 0, z: 0);
ballStructh = new GeometricalObject(ballCh, Elementary.Cart2Sphere(ballCh), radius: pinholeStuct.radius, side: 0);
labelCP.Invoke(new Action(delegate()
{
labelCP.Text = String.Format("({0:0.00}, {1:0.00}, {2:0.00})", xstart + (xstep * xc), ystart + (ystep * yc), zstart + (zstep * zc));
}));
imc++;
// update the position of the ball
var _inputParameters = new { Ax = -xstart - (xstep * xc), Ay = -ystart - (ystep * yc), Az = zstart + (zstep * zc), Ar = ballStruct.radius };
await Task.Run(() =>
{
Thread[] threadsArray = new Thread[NUM_THREADS];
for (int threadIterator = 0; threadIterator < NUM_THREADS; threadIterator++)
{
threadsArray[threadIterator] = new Thread(GreatLoop);
threadsArray[threadIterator].Priority = ThreadPriority.Highest;
threadsArray[threadIterator].Start(_inputParameters);
}
foreach (Thread t in threadsArray)
{
t.Join();
}
});
refrcnt = 0;
double qpA = pixelHIT[NUM_PIXELS_SIDE / 2 - 1, NUM_PIXELS_SIDE / 2];
double qpB = pixelHIT[NUM_PIXELS_SIDE / 2, NUM_PIXELS_SIDE / 2];
double qpC = pixelHIT[NUM_PIXELS_SIDE / 2, NUM_PIXELS_SIDE / 2 - 1];
double qpD = pixelHIT[NUM_PIXELS_SIDE / 2 - 1, NUM_PIXELS_SIDE / 2 - 1];
double tmpX = (qpA + qpD - qpB - qpC) / (qpA + qpB + qpC + qpD);
double tmpY = (qpA + qpB - qpC - qpD) / (qpA + qpB + qpC + qpD);
chartLOC.Series[0].Points.AddXY(tmpX, tmpY);
sl.AddWorksheet(String.Format("Res{0}", imc));
for (int w = 0; w < NUM_PIXELS_SIDE; w++)
{
for (int k = 0; k < NUM_PIXELS_SIDE; k++)
{
try
{
double tmp1 = pixelHIT[w, k];
sl.SetCellValue(w + 1, k + 1, tmp1);
}
catch (Exception erorWrite) { System.Console.WriteLine(erorWrite.Message); }
}
}
Array.Clear(pixelHIT, 0, NUM_PIXELS_SIDE * NUM_PIXELS_SIDE);
}
sw.Stop();
System.Console.WriteLine("Time: " + sw.Elapsed);
}
}
}
sl.SaveAs((tbFileName.Text.Length > 0 ? tbFileName.Text : "test") + ".xlsx");
try
{
InitializeGifParams(imc2 - 1);
encoder.Save(stream);
}
catch (Exception e3)
{
System.Console.WriteLine(e3.Message);
}
bStart.Enabled = true;
gbLEDs.Enabled = true;
gbSE.Enabled = true;
gbSP.Enabled = true;
}
}
public void General_Reesters(string v1, string v2)
{
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Реестр за арендованных и собственных вагон-цистерн компании.xlsx";
DataTable dt, dataTable, Itog_Rep;
string Name;
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("Batys");
sl.RenameWorksheet("Batys", "Реестр");
sl.AddWorksheet("Temp");
string RefreshAllCount = "exec dbo.GetReportAllRenderedService_v1 '" + v1 + "','" + v2 + "'," + "@Type = " + 2;
dt = DbConnection.DBConnect(RefreshAllCount);
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
if (j == 1)
{
Name = dt.Rows[i][j].ToString();
sl.CopyWorksheet("Реестр", Name);
}
}
}
sl.SelectWorksheet("Реестр");
sl.DeleteWorksheet("Temp");
int k = 0;
foreach (var name in sl.GetWorksheetNames())
{
if (name == "Реестр")
{
string RefreshAll = "exec dbo.GetReportAllRenderedService_v1 '" + v1 + "','" + v2 + "'," + "@Type = " + 1;
dataTable = DbConnection.DBConnect(RefreshAll);
string GetCountServiceCost = "exec dbo.Itog_All_Report '" + v1 + "','" + v2 + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
sl.SelectWorksheet(name);
sl.SetCellValue("F12", "в ТОО \"Ертыс сервис\" " + v1 + " по " + v2);
var val = dataTable.Rows.Count + 18;
sl.CopyCell("B18", "G24", "B" + val, true);
sl.ImportDataTable(16, 1, dataTable, false);
sl.CopyCell("Z16", "Z" + Convert.ToString(dataTable.Rows.Count + 16), "AA16", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j <= dataTable.Columns.Count + 1; j++)
{
sl.SetCellStyle(i + 16, j, FormattingExcelCells(sl, true));
if (j == 13 | j == 14 | j == 17 | j == 23 | j == 24)
{
SLStyle style = sl.CreateStyle();
style.FormatCode = "yyyy/mm/dd hh:mm:ss";
sl.SetColumnStyle(j, style);
}
}
}
double EndSum = 0;
int rowcount = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 2, FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 10, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 10, FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
//Итоговая сумма
sl.SetCellValue(val, 4, EndSum);
sl.SetCellStyle(val, 4, FormattingExcelCells(sl, false));
dataTable.Clear();
EndSum = 0;
}
else
{
int cs = Convert.ToInt32(dt.Rows[k][0].ToString());
string Refresh = "dbo.GetReportRenderedServices_v1 '" + v1 + "','" + v2 + "','" + cs + "'";
dataTable = DbConnection.DBConnect(Refresh);
string GetCountServiceCost = "exec dbo.Itog_Report '" + v1 + "','" + v2 + "','" + cs + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
Name = dt.Rows[k][1].ToString();
sl.SelectWorksheet(Name);
sl.SetCellValue("F10", dt.Rows[k][1].ToString());
sl.SetCellValue("F12", "в ТОО \"Ертыс Сервис\"" + v1 + " по " + v2);
var val = dataTable.Rows.Count + 18;
sl.CopyCell("B18", "G24", "B" + val, true);
sl.ImportDataTable(16, 1, dataTable, false);
sl.CopyCell("Z16", "Z" + Convert.ToString(dataTable.Rows.Count + 16), "AA16", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j <= dataTable.Columns.Count + 1; j++)
{
sl.SetCellStyle(i + 16, j, FormattingExcelCells(sl, true));
if (j == 13 | j == 14 | j == 17 | j == 23 | j == 24)
{
SLStyle style = sl.CreateStyle();
style.FormatCode = "yyyy/mm/dd hh:mm:ss";
sl.SetColumnStyle(j, style);
}
}
}
double EndSum = 0;
int rowcount = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 2, FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 10, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 10, FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
//Итоговая сумма
sl.SetCellValue(val, 4, EndSum);
sl.SetCellStyle(val, 4, FormattingExcelCells(sl, false));
k++;
}
}
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\Общий Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\Общий Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
private void BtnExlGenRepPerAdm_Click(object sender, EventArgs e)
{
if (CmbBxTipRepGenRepPerAdm.Text == "Consulta Usuarios del Sistema")
{
int CellHeader = 2, cellstart = 2;
SLDocument sl = new SLDocument();
sl.SetCellValue("B" + CellHeader, "DESCTIPOPER");
sl.SetCellValue("C" + CellHeader, "NUMIDUSU");
sl.SetCellValue("D" + CellHeader, "NOMUSU");
sl.SetCellValue("E" + CellHeader, "FECHNACUSU");
sl.SetCellValue("F" + CellHeader, "TELUSU");
sl.SetCellValue("G" + CellHeader, "CORREOUSU");
sl.SetCellValue("H" + CellHeader, "DIRDOMUSU");
sl.SetCellValue("I" + CellHeader, "NOMTIPOID");
SqlConnection Connect = DB_Connection.DBConnection();
SqlCommand command = new SqlCommand(string.Format("SELECT DESCTIPOPER,NUMIDUSU,NOMUSU,FECHNACUSU,TELUSU,CORREOUSU,DIRDOMUSU,NOMTIPOID FROM TABUSUARIOS,TABTIPOPER, TABTIPOID WHERE TABUSUARIOS.IDTIPOPER = TABTIPOPER.IDTIPOPER AND TABUSUARIOS.IDTIPOID = TABTIPOID.IDTIPOID ORDER BY DESCTIPOPER"), Connect);
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
CellHeader++;
sl.SetCellValue("B" + CellHeader, Reader["DESCTIPOPER"].ToString());
sl.SetCellValue("C" + CellHeader, Reader["NUMIDUSU"].ToString());
sl.SetCellValue("D" + CellHeader, Reader["NOMUSU"].ToString());
sl.SetCellValue("E" + CellHeader, Reader["FECHNACUSU"].ToString());
sl.SetCellValue("F" + CellHeader, Reader["TELUSU"].ToString());
sl.SetCellValue("G" + CellHeader, Reader["CORREOUSU"].ToString());
sl.SetCellValue("H" + CellHeader, Reader["DIRDOMUSU"].ToString());
sl.SetCellValue("I" + CellHeader, Reader["NOMTIPOID"].ToString());
}
SLStyle CellStyle = sl.CreateStyle();
CellStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
sl.AutoFitColumn("B", "I");
sl.SetCellStyle("B" + cellstart, "I" + CellHeader, CellStyle);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "DF_REPORT_SystemUsers");
sl.SaveAs("Report" + CmbBxTipRepGenRepPerAdm.Text + ".xlsx");
MessageBox.Show("Reporte Generado a Excel", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reader.Close();
Connect.Close();
}
else if (CmbBxTipRepGenRepPerAdm.Text == "Consulta Asignacion de Usuarios - Sucursales")
{
int CellHeader = 2, cellstart = 2;
SLDocument sl = new SLDocument();
sl.SetCellValue("B" + CellHeader, "NOMCIUDAD");
sl.SetCellValue("C" + CellHeader, "NOMSUCURSAL");
sl.SetCellValue("D" + CellHeader, "NOMUSU");
sl.SetCellValue("E" + CellHeader, "NUMIDUSU");
sl.SetCellValue("F" + CellHeader, "DESCTIPOPER");
SqlConnection Connect = DB_Connection.DBConnection();
SqlCommand command = new SqlCommand(string.Format("SELECT TABCIUDADES.NOMCIUDAD,TABSUCURSALES.NOMSUCURSAL,TABUSUARIOS.NOMUSU,TABUSUARIOS.NUMIDUSU,TABTIPOPER.DESCTIPOPER FROM TABCIUDADES, TABSUCURSALES, TABUSUARIOS, TABTIPOPER WHERE TABCIUDADES.IDCIUDAD = TABSUCURSALES.IDCIUDAD AND TABTIPOPER.IDTIPOPER = TABUSUARIOS.IDTIPOPER AND TABSUCURSALES.IDSUCURSAL = TABUSUARIOS.SUCOPERARIOS ORDER BY NOMCIUDAD"), Connect);
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
CellHeader++;
sl.SetCellValue("B" + CellHeader, Reader["NOMCIUDAD"].ToString());
sl.SetCellValue("C" + CellHeader, Reader["NOMSUCURSAL"].ToString());
sl.SetCellValue("D" + CellHeader, Reader["NOMUSU"].ToString());
sl.SetCellValue("E" + CellHeader, Reader["NUMIDUSU"].ToString());
sl.SetCellValue("F" + CellHeader, Reader["DESCTIPOPER"].ToString());
}
SLStyle CellStyle = sl.CreateStyle();
CellStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
sl.AutoFitColumn("B", "F");
sl.SetCellStyle("B" + cellstart, "F" + CellHeader, CellStyle);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "DF_REPORT_SystemUsersperSuc");
sl.SaveAs("Report" + CmbBxTipRepGenRepPerAdm.Text + ".xlsx");
MessageBox.Show("Reporte Generado a Excel", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reader.Close();
Connect.Close();
}
else if (CmbBxTipRepGenRepPerAdm.Text == "Consulta de Envios por sucursales")
{
int CellHeader = 2, cellstart = 2;
SLDocument sl = new SLDocument();
sl.SetCellValue("B" + CellHeader, "NOMCIUDAD");
sl.SetCellValue("C" + CellHeader, "NOMSUCURSAL");
sl.SetCellValue("D" + CellHeader, "IDENVIOGUIA");
sl.SetCellValue("E" + CellHeader, "FECHAENVIO");
sl.SetCellValue("F" + CellHeader, "VALORENVIO");
sl.SetCellValue("G" + CellHeader, "DESCESTADO");
SqlConnection Connect = DB_Connection.DBConnection();
SqlCommand command = new SqlCommand(string.Format("SELECT TABCIUDADES.NOMCIUDAD,TABSUCURSALES.NOMSUCURSAL,TABENVIOS.IDENVIOGUIA,TABENVIOS.FECHAENVIO,TABENVIOS.VALORENVIO,TABESTADOS.DESCESTADO FROM TABENVIOS, TABSUCURSALES, TABCIUDADES,TABESTADOS WHERE TABCIUDADES.IDCIUDAD = TABSUCURSALES.IDSUCURSAL AND TABENVIOS.IDSUCORI = TABSUCURSALES.IDSUCURSAL AND TABESTADOS.IDESTADO = TABENVIOS.IDESTADO ORDER BY NOMCIUDAD"), Connect);
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
CellHeader++;
sl.SetCellValue("B" + CellHeader, Reader["NOMCIUDAD"].ToString());
sl.SetCellValue("C" + CellHeader, Reader["NOMSUCURSAL"].ToString());
sl.SetCellValue("D" + CellHeader, Reader["IDENVIOGUIA"].ToString());
sl.SetCellValue("E" + CellHeader, Reader["FECHAENVIO"].ToString());
sl.SetCellValue("F" + CellHeader, Reader["VALORENVIO"].ToString());
sl.SetCellValue("G" + CellHeader, Reader["DESCESTADO"].ToString());
}
SLStyle CellStyle = sl.CreateStyle();
CellStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
sl.AutoFitColumn("B", "G");
sl.SetCellStyle("B" + cellstart, "G" + CellHeader, CellStyle);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "DF_REPORT_EnviosperSuc");
sl.SaveAs("Report" + CmbBxTipRepGenRepPerAdm.Text + ".xlsx");
MessageBox.Show("Reporte Generado a Excel", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reader.Close();
Connect.Close();
}
else if (CmbBxTipRepGenRepPerAdm.Text == "Consulta de Envios Cancelados")
{
int CellHeader = 2, cellstart = 2;
SLDocument sl = new SLDocument();
sl.SetCellValue("B" + CellHeader, "N° GUIA");
sl.SetCellValue("C" + CellHeader, "ID USUARIO");
sl.SetCellValue("D" + CellHeader, "VALOR TOTAL");
sl.SetCellValue("E" + CellHeader, "ESTADO");
sl.SetCellValue("F" + CellHeader, "FECHA ENVIO");
sl.SetCellValue("G" + CellHeader, "PERFIL");
sl.SetCellValue("H" + CellHeader, "SUC. ORIGEN");
sl.SetCellValue("I" + CellHeader, "SUC. DESTINO");
sl.SetCellValue("J" + CellHeader, "ID DESTINATARIO");
sl.SetCellValue("K" + CellHeader, "NOMBRE DESTINATARIO");
sl.SetCellValue("L" + CellHeader, "TELEFONO DESTINATARIO");
sl.SetCellValue("M" + CellHeader, "DIRECCION DESTINO");
sl.SetCellValue("N" + CellHeader, "DETALLES DEL ENVIO");
sl.SetCellValue("O" + CellHeader, "DETALLES DE CANCELACIÓN");
SqlConnection Connect = DB_Connection.DBConnection();
SqlCommand command = new SqlCommand(string.Format("SELECT LTRIM(RTRIM(REPLACE(TEN.IDENVIOGUIA, '', ''))) AS 'N° GUIA',LTRIM(RTRIM(REPLACE(TEN.NUMIDUSU, '', ''))) AS 'ID USUARIO',LTRIM(RTRIM(REPLACE(TEN.VALORENVIO, '', ''))) AS 'VALOR TOTAL',LTRIM(RTRIM(REPLACE(TES.DESCESTADO, '', ''))) AS ESTADO,LTRIM(RTRIM(REPLACE(TEN.FECHAENVIO, '', ''))) AS 'FECHA ENVIO',LTRIM(RTRIM(REPLACE(TTP.DESCTIPOPER, '', ''))) AS PERFIL,LTRIM(RTRIM(REPLACE(TSU.NOMSUCURSAL, '', ''))) AS 'SUC. ORIGEN',LTRIM(RTRIM(REPLACE(TSU2.NOMSUCURSAL, '', ''))) AS 'SUC. DESTINO',LTRIM(RTRIM(REPLACE(TEN.IDDESTINATARIO, '', ''))) AS 'ID DESTINATARIO',LTRIM(RTRIM(REPLACE(TEN.NOMDESTINATARIO, '', ''))) AS 'NOMBRE DESTINATARIO',LTRIM(RTRIM(REPLACE(TEN.TELDESTINATARIO, '', ''))) AS 'TELEFONO DESTINATARIO',LTRIM(RTRIM(REPLACE(TEN.DIRDESTINO, '', ''))) AS 'DIRECCION DESTINO',LTRIM(RTRIM(REPLACE(TEN.DETENVIO, '', ''))) AS 'DETALLES DEL ENVIO', LTRIM(RTRIM(REPLACE(TEN.DETCANCELACION, '', ''))) AS 'DETALLES DE CANCELACIÓN' FROM TABENVIOS TEN INNER JOIN TABESTADOS TES ON TES.IDESTADO = TEN.IDESTADO INNER JOIN TABTIPOPER TTP ON TEN.IDTIPOPER = TTP.IDTIPOPER INNER JOIN TABSUCURSALES TSU ON TEN.IDSUCORI = TSU.IDSUCURSAL INNER JOIN TABSUCURSALES TSU2 ON TEN.IDSUCDES = TSU2.IDSUCURSAL WHERE TEN.IDESTADO = '6' ORDER BY FECHAENVIO DESC"), Connect);
SqlDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
CellHeader++;
sl.SetCellValue("B" + CellHeader, Reader["N° GUIA"].ToString());
sl.SetCellValue("C" + CellHeader, Reader["ID USUARIO"].ToString());
sl.SetCellValue("D" + CellHeader, Reader["VALOR TOTAL"].ToString());
sl.SetCellValue("E" + CellHeader, Reader["ESTADO"].ToString());
sl.SetCellValue("F" + CellHeader, Reader["FECHA ENVIO"].ToString());
sl.SetCellValue("G" + CellHeader, Reader["PERFIL"].ToString());
sl.SetCellValue("H" + CellHeader, Reader["SUC. ORIGEN"].ToString());
sl.SetCellValue("I" + CellHeader, Reader["SUC. DESTINO"].ToString());
sl.SetCellValue("J" + CellHeader, Reader["ID DESTINATARIO"].ToString());
sl.SetCellValue("K" + CellHeader, Reader["NOMBRE DESTINATARIO"].ToString());
sl.SetCellValue("L" + CellHeader, Reader["TELEFONO DESTINATARIO"].ToString());
sl.SetCellValue("M" + CellHeader, Reader["DIRECCION DESTINO"].ToString());
sl.SetCellValue("N" + CellHeader, Reader["DETALLES DEL ENVIO"].ToString());
sl.SetCellValue("O" + CellHeader, Reader["DETALLES DE CANCELACIÓN"].ToString());
}
SLStyle CellStyle = sl.CreateStyle();
CellStyle.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.TopBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.BottomBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.RightBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
CellStyle.Border.LeftBorder.BorderStyle = BorderStyleValues.Thin;
CellStyle.Border.LeftBorder.Color = System.Drawing.Color.Black;
sl.AutoFitColumn("B", "O");
sl.SetCellStyle("B" + cellstart, "O" + CellHeader, CellStyle);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "DF_REPORT_EnviosCancelados");
sl.SaveAs("Report" + CmbBxTipRepGenRepPerAdm.Text + ".xlsx");
MessageBox.Show("Reporte Generado a Excel", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reader.Close();
Connect.Close();
}
}
public void TotalByCompany(string date_1, string date_2, object comapany, string company_name, string dates_1, string dates_2)
{
var GR = new General_Reestr();
DataTable dataTable, Itog_Rep;
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Реестр за арендованных и собственных вагон-цистерн компании.xlsx";
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("ТОО Казыкурт");
sl.RenameWorksheet("ТОО Казыкурт", "Реестр");
string Refresh = "dbo.GetReportRenderedServices_v1 '" + date_1 + "','" + date_2 + "','" + comapany + "'";
dataTable = DbConnection.DBConnect(Refresh);
string GetCountServiceCost = "exec dbo.Itog_Report '" + date_1 + "','" + date_2 + "','" + comapany + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
sl.SetCellValue("C4", company_name);
sl.SetCellValue("C6", "в ТОО Казыгурт-Юг c " + dates_1 + " по " + dates_2);
var val = dataTable.Rows.Count + 16 + Itog_Rep.Rows.Count * 2;
sl.CopyCell("B13", "K20", "B" + val, true);
sl.ImportDataTable(10, 1, dataTable, false);
sl.CopyCell("E10", "R" + Convert.ToString(dataTable.Rows.Count + 10), "G10", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j < dataTable.Columns.Count + 3; j++)
{
sl.SetCellStyle(i + 10, j, GR.FormattingExcelCells(sl, true));
}
}
sl.SetCellValue(dataTable.Rows.Count + 12, 2, "=C6");
sl.SetCellStyle(dataTable.Rows.Count + 12, 2, GR.FormattingExcelCells(sl, false));
sl.SetCellValue(dataTable.Rows.Count + 14, 2, "Всего обработано вагонов - цистерн " + company_name + " по видам операций:");
sl.SetCellStyle(dataTable.Rows.Count + 14, 2, GR.FormattingExcelCells(sl, false));
//Итоговая сводка
int rowcount = 0;
int total = 0;
double EndSum = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 2, GR.FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 15 + rowcount, j + 12, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 15 + rowcount, j + 12, GR.FormattingExcelCells(sl, false));
}
}
EndSum += val1;
if (i < Itog_Rep.Rows.Count && Itog_Rep.Rows[i][0].ToString() != "Текущий отцепочный ремонт горячей обработкой" && Itog_Rep.Rows[i][0].ToString() != "Текущий отцепочный ремонт" && Itog_Rep.Rows[i][0].ToString() != "Очистка от знака опастности")
{
total += int.Parse(Itog_Rep.Rows[i][1].ToString());
}
else
{
continue;
}
}
//sl.SetCellValue(dataTable.Rows.Count + 14, 13, total);
sl.SetCellValue(dataTable.Rows.Count + 14, 13, dataTable.Rows.Count);
sl.SetCellStyle(dataTable.Rows.Count + 14, 13, GR.FormattingExcelCells(sl, false));
//Итоговая сумма
sl.SetCellValue(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, EndSum);
sl.SetCellStyle(dataTable.Rows.Count + Itog_Rep.Rows.Count * 2 + 16, 14, GR.FormattingExcelCells(sl, false));
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
dataTable.Clear();
Itog_Rep.Clear();
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
public void TotalByCompany(string date_1, string date_2, object comapany, string company_name, string dates_1, string dates_2)
{
var GR = new General_Reestr();
DataTable dataTable, Itog_Rep;
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Реестр за арендованных и собственных вагон-цистерн компании.xlsx";
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("ТОО Казыкурт");
sl.RenameWorksheet("ТОО Казыкурт", "Реестр");
string Refresh = "dbo.GetReportRenderedServices_v1 '" + date_1 + "','" + date_2 + "','" + comapany + "'";
dataTable = DbConnection.DBConnect(Refresh);
string GetCountServiceCost = "exec dbo.Itog_Report '" + date_1 + "','" + date_2 + "','" + comapany + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
sl.SetCellValue("F10", company_name);
sl.SetCellValue("F12", "в ТОО \"Batys Petroleum\"" + dates_1 + " по " + dates_2);
var val = dataTable.Rows.Count + 24 + Itog_Rep.Rows.Count;
sl.CopyCell("B18", "G24", "B" + val, true);
sl.ImportDataTable(16, 1, dataTable, false);
sl.CopyCell("W16", "W" + Convert.ToString(dataTable.Rows.Count + 16), "X16", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j <= dataTable.Columns.Count + 1; j++)
{
sl.SetCellStyle(i + 16, j, GR.FormattingExcelCells(sl, true));
}
}
sl.SetCellValue(dataTable.Rows.Count + 19, 2, "=F12");
sl.SetCellStyle(dataTable.Rows.Count + 19, 2, GR.FormattingExcelCells(sl, false));
sl.SetCellValue(dataTable.Rows.Count + 20, 2, "Всего обработано вагонов - цистерн " + company_name + " по видам операций:");
sl.SetCellStyle(dataTable.Rows.Count + 20, 2, GR.FormattingExcelCells(sl, false));
//Итоговая сводка
int rowcount = 0;
int total = 0;
double EndSum = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 21 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 21 + rowcount, j + 2, GR.FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 21 + rowcount, j + 12, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 21 + rowcount, j + 12, GR.FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
sl.SetCellValue(dataTable.Rows.Count + 20, 12, dataTable.Rows.Count);
sl.SetCellStyle(dataTable.Rows.Count + 20, 12, GR.FormattingExcelCells(sl, false));
//Итоговая сумма
sl.SetCellValue(val, 4, EndSum);
sl.SetCellStyle(val, 4, GR.FormattingExcelCells(sl, false));
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
dataTable.Clear();
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
public void TotalByCompany(string date_1, string date_2, object comapany, string company_name, string dates_1, string dates_2)
{
var GR = new General_Reestr();
DataTable dataTable, Itog_Rep;
string path = AppDomain.CurrentDomain.BaseDirectory + @"ReportTemplates\Реестр за арендованных и собственных вагон-цистерн компании.xlsx";
using (SLDocument sl = new SLDocument(path))
{
sl.SelectWorksheet("ТОО Казыкурт");
sl.RenameWorksheet("ТОО Казыкурт", "Реестр");
string Refresh = "dbo.GetReportRenderedServices_v1 '" + date_1 + "','" + date_2 + "','" + comapany + "'";
dataTable = DbConnection.DBConnect(Refresh);
string GetCountServiceCost = "exec dbo.Itog_Report '" + date_1 + "','" + date_2 + "','" + comapany + "'";
Itog_Rep = DbConnection.DBConnect(GetCountServiceCost);
sl.SetCellValue("F10", company_name);
sl.SetCellValue("F12", "в ТОО \"Ертыс Сервис\"" + dates_1 + " по " + dates_2);
var val = dataTable.Rows.Count + 18;
sl.CopyCell("B18", "G24", "B" + val, true);
sl.ImportDataTable(16, 1, dataTable, false);
sl.CopyCell("Z16", "Z" + Convert.ToString(dataTable.Rows.Count + 16), "AA16", true);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 1; j <= dataTable.Columns.Count + 1; j++)
{
sl.SetCellStyle(i + 16, j, GR.FormattingExcelCells(sl, true));
if (j == 13 | j == 14 | j == 17 | j == 23 | j == 24)
{
SLStyle style = sl.CreateStyle();
style.FormatCode = "yyyy/mm/dd hh:mm:ss";
sl.SetColumnStyle(j, style);
}
}
}
double EndSum = 0;
int rowcount = 0;
for (int i = 0; i < Itog_Rep.Rows.Count; i++)
{
rowcount++;
double val1 = 1;
for (int j = 0; j < Itog_Rep.Columns.Count; j++)
{
if (j == 0)
{
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 2, Itog_Rep.Rows[i][j].ToString());
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 2, GR.FormattingExcelCells(sl, false));
}
else
{
val1 = val1 * double.Parse(Itog_Rep.Rows[i][j].ToString());
sl.SetCellValue(i + dataTable.Rows.Count + 19 + rowcount, j + 10, Convert.ToDecimal(Itog_Rep.Rows[i][j].ToString()));
sl.SetCellStyle(i + dataTable.Rows.Count + 19 + rowcount, j + 10, GR.FormattingExcelCells(sl, false));
}
}
EndSum += val1;
}
//Итоговая сумма
sl.SetCellValue(val, 4, EndSum);
sl.SetCellStyle(val, 4, GR.FormattingExcelCells(sl, false));
sl.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
dataTable.Clear();
Process.Start(AppDomain.CurrentDomain.BaseDirectory + @"Report\" + company_name + " Реестр за арендованных и собственных вагон-цистерн компании.xlsx");
}
