public static string getContabilidadEstadoResultadosSubGroups(string sGroup) { // if (currentMayorizadoDataset == null) // getContabilidadEstadoResultados(); DataTable table = currentMayorizadoDataset.Tables[0]; var groupedData = from b in table.AsEnumerable() where b.Field <string>("NombreFormatoBalance") == sGroup group b by b.Field <string>("NombreFormatoBalance") into g select new { NombreFormatoBalance = g.Key }; string json = null; List <ReporteEstadoResultado> fields = new List <ReporteEstadoResultado>(); try { Int32 id = 1; foreach (var row in groupedData) { ReporteEstadoResultado field = new ReporteEstadoResultado(); field.id = id.ToString(); //field.NombreTipoCuenta = row.NombreFormatoBalance; fields.Add(field); id++; } json = JsonConvert.SerializeObject(fields); } catch (Exception e) { Console.WriteLine(e.Message); } return(json); }
public string getMayorizadoExcelFile(ReporteEstadoResultado reporte) { string tmpFile = "Mayorizado_" + Utils.Global.GenerateName(10) + ".xlsx"; //@"d:\ShiolFramework\tmp" DirectoryInfo outputDir = new DirectoryInfo(ShiolConfiguration.Instance.Config.DirPlantillas); FileInfo newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile)); if (newFile.Exists) { newFile.Delete(); // ensures we create a new workbook newFile = new FileInfo(Path.Combine(outputDir.FullName, tmpFile)); } using (ExcelPackage package = new ExcelPackage(newFile)) { // add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Mayorizado"); //Add the headers int col = 1; int init_row = 4, row = 4; foreach (Column column in reporte.columns) { if (column.Header.ToUpper() != "TODOS") { worksheet.Cells[row, col++].Value = column.Header; } } worksheet.Cells[row, col++].Value = "Adjustments \n (+) &(-)"; worksheet.Cells[row, col++].Value = "Consolidado Total"; worksheet.Cells[row, col++].Value = "Bdg 2017 TTD"; worksheet.Cells[row, col++].Value = "Exe 2016 TTD"; worksheet.Cells[row, col++].Value = "Var % Exe vs Bgd 2017"; worksheet.Cells[row, col++].Value = "Var % Exe 2017 vs 2016"; worksheet.Cells[row, col++].Value = "Unit Analysis"; worksheet.Cells[row, col++].Value = "% of Sales"; worksheet.Cells[row, col++].Value = "Unit Analysis"; worksheet.Cells[row, col++].Value = "% of Sales"; /* * worksheet.Cells[1, col++].Value = ""; * worksheet.Cells[1, col++].Value = ""; * worksheet.Cells[1, col++].Value = ""; * worksheet.Cells[1, col++].Value = ""; */ //worksheet.Cells["A1:J1"].Style.Fill.PatternType = ExcelFillStyle.LightGray; worksheet.Cells["A" + row + ":" + "R" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); //ws.Cells["B24"].Style.Font.Bold = true; //ws.Cells["B24:X24"].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); //worksheet.Cells["A4:G4"].Style.Fill.PatternType = ExcelFillStyle.Solid; //worksheet.Cells["A4:G4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); //worksheet.Cells[startRow, 1, row - 1, 1].StyleName = "HyperLink"; //worksheet.Cells[startRow, 4, row - 1, 6].Style.Numberformat.Format = "[$$-409]#,##0"; //worksheet.Cells[startRow, 7, row - 1, 7].Style.Numberformat.Format = "0%"; //worksheet.Cells[startRow, 7, row - 1, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])"; int numberWidth = 15; worksheet.Column(1).Width = 45; worksheet.Column(2).Width = numberWidth; worksheet.Column(3).Width = numberWidth; worksheet.Column(4).Width = numberWidth; worksheet.Column(5).Width = numberWidth; worksheet.Column(6).Width = numberWidth; worksheet.Column(7).Width = numberWidth; worksheet.Column(8).Width = numberWidth; worksheet.Column(9).Width = numberWidth; row++; foreach (ReporteEstadoResultadoGroup group in reporte.groups) { worksheet.Cells["A" + row].Value = group.Descripcion; // worksheet.Cells[row, 2].Value = group.Todos; worksheet.Cells[row, 2].Value = group.H1; worksheet.Cells[row, 3].Value = group.H2; worksheet.Cells[row, 4].Value = group.H3; worksheet.Cells[row, 5].Value = group.H4; worksheet.Cells[row, 6].Value = group.H5; worksheet.Cells[row, 7].Value = group.H6; worksheet.Cells[row, 8].Value = group.H7; worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["A" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true; //Consolidado worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")"; row++; foreach (ReporteEstadoResultadoSubGroup sgroup in group.subGroups) { worksheet.Cells[row, 1].Value = " " + sgroup.Descripcion; //worksheet.Cells[row, 2].Value = sgroup.Todos; worksheet.Cells[row, 2].Value = sgroup.H1; worksheet.Cells[row, 3].Value = sgroup.H2; worksheet.Cells[row, 4].Value = sgroup.H3; worksheet.Cells[row, 5].Value = sgroup.H4; worksheet.Cells[row, 6].Value = sgroup.H5; worksheet.Cells[row, 7].Value = sgroup.H6; worksheet.Cells[row, 8].Value = sgroup.H7; worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells["B" + row + ":" + "J" + row].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); worksheet.Cells["B" + row + ":" + "J" + row].Style.Font.Bold = true; //Consolidado worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")"; row++; int rowsg = row; foreach (ReporteEstadoResultadoSubGroupChild sgroupc in sgroup.subGroupChilds) { worksheet.Cells[row, 1].Value = " " + " " + sgroupc.Descripcion; //worksheet.Cells[row, 2].Value = sgroupc.Todos; worksheet.Cells[row, 2].Value = sgroupc.H1; worksheet.Cells[row, 3].Value = sgroupc.H2; worksheet.Cells[row, 4].Value = sgroupc.H3; worksheet.Cells[row, 5].Value = sgroupc.H4; worksheet.Cells[row, 6].Value = sgroupc.H5; worksheet.Cells[row, 7].Value = sgroupc.H6; worksheet.Cells[row, 8].Value = sgroupc.H7; worksheet.Cells["B" + row + ":" + "J" + row].Style.Numberformat.Format = "#,##0.00"; //Consolidado worksheet.Cells["J" + row].Formula = "=SUM(B" + row + ":" + "H" + row + ")"; row++; } worksheet.Cells["B" + rowsg + ":" + "J" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); } } worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells worksheet.Cells["A" + init_row + ":" + "I" + (row - 1)].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black); //Add a formula for the value-column // worksheet.Cells["E2:E4"].Formula = "C2*D2"; /* * //Ok now format the values; * using (var range = worksheet.Cells[1, 1, 1, 5]) * { * range.Style.Font.Bold = true; * range.Style.Fill.PatternType = ExcelFillStyle.Solid; * range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); * range.Style.Font.Color.SetColor(Color.White); * } * * worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin; * worksheet.Cells["A5:E5"].Style.Font.Bold = true; * * worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 3, 4, 3).Address); * worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0"; * worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00"; * * //Create an autofilter for the range * worksheet.Cells["A1:E4"].AutoFilter = true; * * worksheet.Cells["A2:A4"].Style.Numberformat.Format = "@"; //Format as text * * //There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful. * //For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or * //you want to use the result of a formula in your program. * worksheet.Calculate(); * * worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells * * // lets set the header text * worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory"; * // add the page number to the footer plus the total number of pages * worksheet.HeaderFooter.OddFooter.RightAlignedText = * string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); * // add the sheet name to the footer * worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName; * // add the file path to the footer * worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName; * * worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"]; * worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"]; * * // Change the sheet view to show it in page layout mode * worksheet.View.PageLayoutView = true; */ // set some document properties package.Workbook.Properties.Title = "Mayorizado"; package.Workbook.Properties.Author = "Alvaro Medina"; package.Workbook.Properties.Comments = "Mayorización..."; // set some extended property values package.Workbook.Properties.Company = "Shiol Sistemas"; // set some custom property values package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Alvaro Medina"); package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "SHG Sistemas"); // save our new workbook and we are done! package.Save(); } return(newFile.FullName); }
public string getMayorizadoExcelFile1(DataTable dt, ReporteEstadoResultado reporte) { //@"d:\ShiolFramework\tmp" DirectoryInfo outputDir = new DirectoryInfo(ShiolConfiguration.Instance.Config.DirPlantillas); ExcelPackage pck = new ExcelPackage(); //Create a datatable with the directories and files from the root directory... // DataTable dt = GetDataTable(outputDir.Root); var wsDt = pck.Workbook.Worksheets.Add("FromDataTable"); //Load the datatable and set the number formats... wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium9); wsDt.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0"; wsDt.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy"; wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns(); /* * * //Select Name and Created-time... * var collection = (from row in dt.Select() select new { Name = row["Name"], Created_time = (DateTime)row["Created"] }); * * * * var wsEnum = pck.Workbook.Worksheets.Add("FromAnonymous"); * * //Load the collection starting from cell A1... * wsEnum.Cells["A1"].LoadFromCollection(collection, true, TableStyles.Medium9); * * //Add some formating... * wsEnum.Cells[2, 2, dt.Rows.Count - 1, 2].Style.Numberformat.Format = "mm-dd-yy"; * wsEnum.Cells[wsEnum.Dimension.Address].AutoFitColumns(); * * //Load a list of FileDTO objects from the datatable... * var wsList = pck.Workbook.Worksheets.Add("FromList"); * List<FileDTO> list = (from row in dt.Select() * select new FileDTO * { * Name = row["Name"].ToString(), * Size = row["Size"].GetType() == typeof(long) ? (long)row["Size"] : 0, * Created = (DateTime)row["Created"], * LastModified = (DateTime)row["Modified"], * IsDirectory = (row["Size"] == DBNull.Value) * }).ToList<FileDTO>(); * * //Load files ordered by size... * wsList.Cells["A1"].LoadFromCollection(from file in list * orderby file.Size descending * where file.IsDirectory == false * select file, true, TableStyles.Medium9); * * wsList.Cells[2, 2, dt.Rows.Count + 1, 2].Style.Numberformat.Format = "#,##0"; * wsList.Cells[2, 3, dt.Rows.Count + 1, 4].Style.Numberformat.Format = "mm-dd-yy"; * * * //Load directories ordered by Name... * wsList.Cells["F1"].LoadFromCollection(from file in list * orderby file.Name ascending * where file.IsDirectory == true * select new * { * Name = file.Name, * Created = file.Created, * Last_modified = file.LastModified * }, //Use an underscore in the property name to get a space in the title. * true, TableStyles.Medium11); * * wsList.Cells[2, 7, dt.Rows.Count + 1, 8].Style.Numberformat.Format = "mm-dd-yy"; * * //Load the list using a specified array of MemberInfo objects. Properties, fields and methods are supported. * var rng = wsList.Cells["J1"].LoadFromCollection(list, * true, * TableStyles.Medium10, * BindingFlags.Instance | BindingFlags.Public, * new MemberInfo[] { * typeof(FileDTO).GetProperty("Name"), * typeof(FileDTO).GetField("IsDirectory"), * typeof(FileDTO).GetMethod("ToString")} * ); * * wsList.Tables.GetFromRange(rng).Columns[2].Name = "Description"; * * wsList.Cells[wsList.Dimension.Address].AutoFitColumns(); */ //...and save var fi = new FileInfo(outputDir.FullName + @"\Mayorizado.xlsx"); if (fi.Exists) { fi.Delete(); } pck.SaveAs(fi); return(fi.FullName); }
public bool getContabilidadEstadoResultados(Request req, Response res, FileType type) { ReporteEstadoResultado reporte = new ReporteEstadoResultado(); DataTable dt = getContabilidadEstadoResultadosDataTable(req, res); reporte._columnsGroups = _columns; reporte._columnsSubGroups = _columns; reporte._columns = _columns; reporte.columns = columns; reporte.Descripcion = "Total General"; string Moneda = req.PathVariables["moneda"] != null ? req.PathVariables["moneda"] : "USD"; //Soles bool isSoles = (Moneda == "USD" ? false : true); string json = null; try { string NombreTipoCuenta = ""; string NombreFormatoBalance = ""; List <ReporteEstadoResultadoGroup> listGroups = new List <ReporteEstadoResultadoGroup>(); ReporteEstadoResultadoSubGroup subGroup = new ReporteEstadoResultadoSubGroup(); ReporteEstadoResultadoGroup group = new ReporteEstadoResultadoGroup(); Int32 idGroup = -1; Int32 idSubGroup = -1; Int32 idDetail = -1; foreach (DataRow row in dt.Rows) { if (NombreTipoCuenta != row["NombreTipoCuenta"].ToString()) { idGroup++; idSubGroup = -1; group = new ReporteEstadoResultadoGroup(); NombreFormatoBalance = ""; NombreTipoCuenta = row["NombreTipoCuenta"].ToString(); group.Descripcion = "Cuenta: " + row["NombreTipoCuenta"].ToString().Trim(); group.id = idGroup.ToString(); listGroups.Add(group); } if (NombreFormatoBalance != row["NombreFormatoBalance"].ToString()) { idSubGroup++; idDetail = -1; subGroup = new ReporteEstadoResultadoSubGroup(); NombreFormatoBalance = row["NombreFormatoBalance"].ToString(); subGroup.Descripcion = " Tipo: " + row["NombreFormatoBalance"].ToString().Trim(); subGroup.id = idSubGroup.ToString(); listGroups[idGroup].subGroups.Add(subGroup); } idDetail++; ReporteEstadoResultadoSubGroupChild field = new ReporteEstadoResultadoSubGroupChild(); field.Descripcion = row["NombreFormatoGanaciasPerdidas"].ToString().Trim(); field.H1.value = isSoles ? Convert.ToDouble(row["H1Soles"].ToString()) : Convert.ToDouble(row["H1Dolares"].ToString()); field.H2.value = isSoles ? Convert.ToDouble(row["H2Soles"].ToString()) : Convert.ToDouble(row["H2Dolares"].ToString()); field.H3.value = isSoles ? Convert.ToDouble(row["H3Soles"].ToString()) : Convert.ToDouble(row["H3Dolares"].ToString()); field.H4.value = isSoles ? Convert.ToDouble(row["H4Soles"].ToString()) : Convert.ToDouble(row["H4Dolares"].ToString()); field.H5.value = isSoles ? Convert.ToDouble(row["H5Soles"].ToString()) : Convert.ToDouble(row["H5Dolares"].ToString()); field.H6.value = isSoles ? Convert.ToDouble(row["H6Soles"].ToString()) : Convert.ToDouble(row["H6Dolares"].ToString()); field.H7.value = isSoles ? Convert.ToDouble(row["H7Soles"].ToString()) : Convert.ToDouble(row["H7Dolares"].ToString()); field.H8.value = isSoles ? Convert.ToDouble(row["H8Soles"].ToString()) : Convert.ToDouble(row["H8Dolares"].ToString()); field.Todos.value = field.H1.value + field.H2.value + field.H3.value + field.H4.value + field.H5.value + field.H6.value + field.H7.value; listGroups[idGroup].subGroups[idSubGroup].subGroupChilds.Add(field); listGroups[idGroup].subGroups[idSubGroup].H1 += field.H1.value; listGroups[idGroup].subGroups[idSubGroup].H2 += field.H2.value; listGroups[idGroup].subGroups[idSubGroup].H3 += field.H3.value; listGroups[idGroup].subGroups[idSubGroup].H4 += field.H4.value; listGroups[idGroup].subGroups[idSubGroup].H5 += field.H5.value; listGroups[idGroup].subGroups[idSubGroup].H6 += field.H6.value; listGroups[idGroup].subGroups[idSubGroup].H7 += field.H7.value; listGroups[idGroup].subGroups[idSubGroup].Todos += field.Todos.value; listGroups[idGroup].H1 += field.H1.value; listGroups[idGroup].H2 += field.H2.value; listGroups[idGroup].H3 += field.H3.value; listGroups[idGroup].H4 += field.H4.value; listGroups[idGroup].H5 += field.H5.value; listGroups[idGroup].H6 += field.H6.value; listGroups[idGroup].H7 += field.H7.value; listGroups[idGroup].Todos += field.Todos.value; reporte.H1.value += field.H1.value; reporte.H2.value += field.H2.value; reporte.H3.value += field.H3.value; reporte.H4.value += field.H4.value; reporte.H5.value += field.H5.value; reporte.H6.value += field.H6.value; reporte.H7.value += field.H7.value; reporte.Todos.value += field.Todos.value; } reporte.groups = listGroups; List <ReporteEstadoResultado> report = new List <ReporteEstadoResultado>(); report.Add(reporte); res.httpListenerResponse.Headers.Add("Access-Control-Expose-Headers", "X-Total-Count"); res.httpListenerResponse.Headers.Add("X-Total-Count", dt.Rows.Count.ToString()); if (type == FileType.Json) { json = JsonConvert.SerializeObject(report); res.Content = json; res.ContentType = "application/json"; res.httpListenerResponse.StatusCode = 200; //401 for error; } if (type == FileType.Xlsx | type == FileType.Pdf) { string excelPath = getMayorizadoExcelFile(reporte); if (type == FileType.Pdf) { string pdfFile = ExportWorkbookToPdf(excelPath, ShiolConfiguration.Instance.Config.DirPlantillas + @"\Mayorizado.pdf", false); WriteFile(res, pdfFile, "Mayorizado.pdf"); } else { WriteFile(res, excelPath, "Mayorizado.xlsx"); } //int converted = ExcelConverter.Convert(excelPath, ShiolConfiguration.Instance.Config.DirPlantillas + "\\Test.pdf", null); } return(true); } catch (Exception e) { Console.WriteLine(e.Message); return(false); } }