/// <summary>
/// Formata as células do excel
/// </summary>
/// <param name="workbook">Representa um MS Excel workbook</param>
/// <param name="worksheet">Representa uma Planilha no workbook</param>
private static void FormatCells(IWorkbook workbook, IWorksheet worksheet)
{
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Color.FromArgb(255, 174, 33);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
//Add custom colors to the palette
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
//Defining body style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
//Apply Header style
worksheet.Rows[0].CellStyle = headerStyle;
//Apply Body Style
worksheet.Range["A2:D101"].CellStyle = bodyStyle;
//Auto-fit the columns
worksheet.UsedRange.AutofitColumns();
}
private IStyle AddHeaderStyle(IWorkbook workbook)
{
// Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Color.FromArgb(234, 112, 33);
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
return(headerStyle);
}
private void ConvertToXlsx()
{
try
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Preserve data types as per the value
application.PreserveCSVDataTypes = true;
//Read my CSV file
Stream csvStream = File.OpenRead(Path.GetFullPath(saveFile.FileName));
//Reads CSV stream as a workbook
IWorkbook workbook = application.Workbooks.Open(csvStream);
IWorksheet sheet = workbook.Worksheets[0];
// Formatting CSV data as a table
IListObject table = sheet.ListObjects.Create("PortsTable", sheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleDark10;
IRange location = table.Location;
location.AutofitColumns();
// Define/apply header style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Syncfusion.Drawing.Color.LightGreen;
headerStyle.Font.FontName = "Consolas";
headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.Dark_blue;
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 16;
headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
headerStyle.EndUpdate();
// Define and apply a body style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
bodyStyle.Font.FontName = "Cambria";
bodyStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.BlackCustom;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium;
bodyStyle.EndUpdate();
// Define a few additonal tweaks
IStyle increaseStyle = workbook.Styles.Add("IncreaseStyle");
increaseStyle.BeginUpdate();
increaseStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue;
increaseStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
increaseStyle.Font.Size = 14;
increaseStyle.Font.Bold = true;
// Modify this range seperately so it doesn't get messed up
sheet.Range["A1:A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A2:A2"].DateTime.ToShortDateString();
sheet.Range["A2:A2"].CellStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue;
sheet.Range["A2:A2"].CellStyle.Font.Bold = true;
sheet.Range["A2:A2"].CellStyle.Font.Size = 14;
sheet.Range["A2:A4"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A2:A2"].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A3:A4"].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
sheet.Range["A3:A3"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["B1:B100"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["B2:B3"].CellStyle.Color = Syncfusion.Drawing.Color.Black;
// Custom widths
sheet.Columns[0].ColumnWidth = 20.00;
// Then Apply header style
sheet.Range["A1:A1"].CellStyle = headerStyle;
sheet.Range["A3:A3"].CellStyle = headerStyle;
sheet.Range["B1:B1"].CellStyle = headerStyle;
// And the body style
sheet.Range["A4:A4"].CellStyle = bodyStyle;
sheet.Range["B4:B100"].CellStyle = bodyStyle;
// Additions
sheet.Range["A2:A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A4:A4"].CellStyle = increaseStyle;
sheet.Range["A4:A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
// Apply a conditional format for cells with the text 'Open'
IConditionalFormats condition = sheet.Range["B4:B100"].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.SpecificText;
condition1.Text = "Open";
condition1.Operator = ExcelComparisonOperator.ContainsText;
condition1.BackColor = ExcelKnownColors.Red;
// Save file in the same directory as the initial raw CSV from StreamWriter
Stream excelStream;
string makeoverPath = Environment.ExpandEnvironmentVariables(@"C:\Users\%USERNAME%\Desktop\makeover.xlsx");
excelStream = File.Create(Path.GetFullPath(makeoverPath));
workbook.SaveAs(excelStream);
// Release all resources => IMPORTANT!
excelStream.Dispose();
MessageBox.Show("SUCCESS! Your original file " + saveFile.FileName + " has been converted to .XLSX. You can view it at: " + makeoverPath);
// Flip this on so that below I can safely delete the ugly .CSV original file
prettified = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void generarreporte(int selected)
{
dtConsulta = getventas(selected);
if (dtConsulta.Rows.Count > 0)
{
int sucursales = radios();
if (sucursales == 1)
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
//headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
//pStyle.Color = colorss(m_select);
pStyle.Color = System.Drawing.Color.DodgerBlue;
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 4);
worksheet.SetColumnWidth(19, 4);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 40);
worksheet.SetColumnWidth(22, 20);
worksheet.SetColumnWidth(23, 5);
worksheet.SetColumnWidth(24, 20);
worksheet.SetColumnWidth(26, 12);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string qty = "N" + rusia + "";
string fpl = "AA" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["AA2:" + fpl].NumberFormat = "#,##0.00";
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
else //base VPN
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
pStyle.Font.Color = ExcelKnownColors.White;
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
if (m_select == 2)
{
pStyle.Color = System.Drawing.Color.Orange;
}
else if (m_select == 3 || m_select == 4)
{
pStyle.Color = System.Drawing.Color.Red;
}
else
{
pStyle.Color = System.Drawing.Color.Gray;
}
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 40);
worksheet.SetColumnWidth(19, 20);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 17);
worksheet.SetColumnWidth(22, 7);
worksheet.SetColumnWidth(23, 15);
worksheet.SetColumnWidth(24, 15);
worksheet.SetColumnWidth(25, 15);
worksheet.SetColumnWidth(26, 7);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[7].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[21].HorizontalAlignment = ExcelHAlign.HAlignRight;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string ffecha = "B" + rusia + "";
string qty = "N" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
}
else
{
RadWindow radWindow = new RadWindow();
RadWindow.Alert(new DialogParameters()
{
Content = "No hay registros para mostrar.",
Header = "BIG",
DialogStartupLocation = WindowStartupLocation.CenterOwner
// IconContent = "";
});
}
}
//
// GET: /Default/
public ActionResult Default(string SaveOption)
{
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//A new workbook is created.[Equivalent to creating a new workbook in Microsoft Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(DefaultResolveApplicationDataPath(@"BudgetPlanner.xls"));
IWorksheet sheet = workbook.Worksheets[1];
sheet.FirstVisibleRow = 3;
IFont font = workbook.CreateFont();
font.Bold = true;
#region TextBox
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = sheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = sheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
sheet.Range["O6"].Text = "Weekly";
sheet.Range["E7"].Text = "Frequency";
sheet.Range["F7"].Text = "Amount";
sheet.Range["G7"].Text = "Monthly";
sheet.Range["H7"].Text = "Yearly";
sheet.Range["B8"].Text = "Total Income";
sheet.Range["C9"].Text = "Salary/Wages";
sheet.Range["C10"].Text = "Salary/Wages(Spouse)";
sheet.Range["C11"].Text = "Other";
sheet.Range["C12"].Text = "Other";
sheet.Range["C13"].Text = "Other";
sheet.Range["B17"].Text = "Transportation";
sheet.Range["F25"].Number = 3000;
sheet.Range["F9"].Number = 55000;
sheet.Range["F10"].Number = 35000;
sheet.Range["C18"].Text = "Auto Loan/Lease";
sheet.Range["C19"].Text = "Insurance";
sheet.Range["C20"].Text = "Gas ";
sheet.Range["C21"].Text = "Maintenance ";
sheet.Range["C22"].Text = "Registration/Inspection";
sheet.Range["C23"].Text = "Bill's train pass";
sheet.Range["C24"].Text = "Jane's bus pass";
sheet.Range["C25"].Text = "Other";
sheet.Range["E16"].Text = "Total";
sheet.Range["N6"].Text = "Chart";
sheet.Range["B27"].Text = "Home";
sheet.Range["F28"].Number = 20000;
sheet.Range["F29"].Number = 5000;
sheet.Range["F33"].Number = 5000;
sheet.Range["C28"].Text = "EMI";
sheet.Range["C29"].Text = "Rent";
sheet.Range["C30"].Text = "Maintanence";
sheet.Range["C31"].Text = "Insurance";
sheet.Range["C32"].Text = "Furniture";
sheet.Range["C33"].Text = "Household Supplies";
sheet.Range["C34"].Text = "Groceries";
sheet.Range["C35"].Text = "Real Estate Tax";
sheet.Range["C36"].Text = "Other";
sheet.Range["B39"].Text = "Utilities";
sheet.Range["F41"].Number = 1000;
sheet.Range["F42"].Number = 250;
sheet.Range["F43"].Number = 150;
sheet.Range["F45"].Number = 175;
sheet.Range["C40"].Text = "Phone - Home";
sheet.Range["C41"].Text = "Phone - Cell";
sheet.Range["C42"].Text = "Cable";
sheet.Range["C43"].Text = "Gas";
sheet.Range["C44"].Text = "Water";
sheet.Range["C45"].Text = "Electricity";
sheet.Range["C46"].Text = "Internet";
sheet.Range["C47"].Text = "Other";
sheet.Range["B50"].Text = "Health";
sheet.Range["F53"].Number = 500;
sheet.Range["C51"].Text = "Dental";
sheet.Range["C52"].Text = "Medical";
sheet.Range["C53"].Text = "Medication";
sheet.Range["C54"].Text = "Vision/contacts";
sheet.Range["C55"].Text = "Life Insurance";
sheet.Range["C56"].Text = "Electricity";
sheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
sheet.Range["E7:H7"].CellStyle.Font.Bold = true;
sheet.Range["B17"].CellStyle.Font.Bold = true;
sheet.Range["B27"].CellStyle.Font.Bold = true;
sheet.Range["B39"].CellStyle.Font.Bold = true;
sheet.Range["B50"].CellStyle.Font.Bold = true;
sheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
sheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C9:C13"].CellStyle = tableStyle;
sheet.Range["E9:F13"].CellStyle = tableStyle;
sheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["B17:C17"].CellStyle.Color = Color.White;
sheet.Range["C18:C25"].CellStyle = tableStyle;
sheet.Range["O6"].CellStyle = tableStyle;
sheet.Range["E18:F25"].CellStyle = tableStyle;
sheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C28:C36"].CellStyle = tableStyle;
sheet.Range["B27:C27"].CellStyle.Color = Color.White;
sheet.Range["E28:F36"].CellStyle = tableStyle;
sheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C40:C47"].CellStyle = tableStyle;
sheet.Range["B39:C39"].CellStyle.Color = Color.White;
sheet.Range["E40:F47"].CellStyle = tableStyle;
sheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
sheet.Range["C51:C57"].CellStyle = tableStyle;
sheet.Range["B50:C50"].CellStyle.Color = Color.White;
sheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = sheet.Range["E9:E13"].DataValidation;
sheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = sheet.Range["E18:E25"].DataValidation;
sheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = sheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = sheet.Range["E28:E37"].DataValidation;
sheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = sheet.Range["E40:E47"].DataValidation;
sheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = sheet.Range["E51:E57"].DataValidation;
sheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
sheet.Range["G8"].Formula = "SUM(G9:G13)";
sheet.Range["H8"].Formula = "SUM(H9:H13)";
sheet.Range["G17"].Formula = "SUM(G18:G25)";
sheet.Range["H17"].Formula = "SUM(H18:H25)";
sheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
sheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G27"].Formula = "SUM(G28:G36)";
sheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G39"].Formula = "SUM(G40:G47)";
sheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
sheet.Range["G50"].Formula = "SUM(G51:G57)";
sheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
sheet.Range["G" + i].Formula = "F" + i + "*A" + i;
sheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = sheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = sheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
sheet.InsertColumn(9);
#endregion
try
{
//Saving the workbook to disk.
if (SaveOption == "Xls")
{
//Save as .xls format
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
//Save as .xlsx format
else
{
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, "SpreadSheet.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
private ActionResult ExportExcelDailyCOD(DateTime dateFrom, DateTime dateTo, List <DailyCOD> Items)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(new[] { "Daily COD" });
IWorksheet worksheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel2013;
worksheet.Range["A1:A2"].Merge();
worksheet.Range["B1:B2"].Merge();
worksheet.Range["C1:F1"].Merge();
worksheet.Range["G1:J1"].Merge();
// Header First
worksheet.Range["A1"].Text = "Branch";
worksheet.Range["B1"].Text = "ERP_ID";
worksheet.Range["C1"].Text = $"{dateTo.ToString("dd/MM/yyyy", enUS)} to {dateTo.ToString("dd/MM/yyyy", enUS)}";
worksheet.Range["G1"].Text = $"As of {dateTo.ToString("dd/MM/yyyy", enUS)}";
worksheet.Range["C2"].Text = "Consignment";
worksheet.Range["D2"].Text = "Amount";
worksheet.Range["E2"].Text = "Surcharge";
worksheet.Range["F2"].Text = "Amount/Con";
worksheet.Range["G2"].Text = "Consignment";
worksheet.Range["H2"].Text = "amount";
worksheet.Range["I2"].Text = "Surcharge";
worksheet.Range["J2"].Text = "Amount/Con";
// Header Style
IStyle headerStyleBranch = workbook.Styles.Add("HeaderStyleBranch");
headerStyleBranch.BeginUpdate();
headerStyleBranch.Color = Color.FromArgb(224, 224, 224);
headerStyleBranch.Font.Color = ExcelKnownColors.Black;
headerStyleBranch.Font.Bold = true;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleBranch.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleBranch.EndUpdate();
worksheet.Range["A1:B2"].CellStyle = headerStyleBranch;
IStyle headerStyleMtd = workbook.Styles.Add("HeaderStyleMtd");
headerStyleMtd.BeginUpdate();
headerStyleMtd.Color = Color.FromArgb(187, 222, 251);
headerStyleMtd.Font.Color = ExcelKnownColors.Black;
headerStyleMtd.Font.Bold = true;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleMtd.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyleMtd.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleMtd.EndUpdate();
worksheet.Range["C1:F1"].CellStyle = headerStyleMtd;
IStyle headerStyleDaily = workbook.Styles.Add("HeaderStyleDaily");
headerStyleDaily.BeginUpdate();
headerStyleDaily.Color = Color.FromArgb(225, 248, 225);
headerStyleDaily.Font.Color = ExcelKnownColors.Black;
headerStyleDaily.Font.Bold = true;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleDaily.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyleDaily.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleDaily.EndUpdate();
worksheet.Range["G1:J1"].CellStyle = headerStyleDaily;
IStyle headerStyle = AddHeaderStyle(workbook);
worksheet.Range["C2:J2"].CellStyle = headerStyle;
// Header Style
worksheet.ImportData(Items.Select(d => new {
d.BranchID,
d.ERPID,
d.MonthlyCODConsignment,
d.MonthlyCODAmount,
d.MonthlyCODSurcharge,
d.MonthlyAmountPerConsignment,
d.DailyCODConsignment,
d.DailyCODAmount,
d.DailyCODSurcharge,
d.DailyAmountPerConsignment,
}), 3, 1, false);
worksheet.Columns[1].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[5].CellStyle.NumberFormat = "#,##0";
for (int i = 2; i < 5; i++)
{
worksheet.Columns[i].CellStyle.NumberFormat = "#,##0.00";
}
for (int i = 6; i < 9; i++)
{
worksheet.Columns[i].CellStyle.NumberFormat = "#,##0.00";
}
worksheet.Range["C3"].FreezePanes();
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/msexcel", "KE_PDC_DailyCOD_Report_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xlsx"));
}
private async void ExportCommandExecute(SessionModel session)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
Loading = true;
await Task.Delay(200);
List <SessionInit> lSessionInit = App.Database.GetSessionInit(session.ID);
if (lSessionInit == null || lSessionInit.Count == 0)
{
Loading = false;
await Application.Current.MainPage.DisplayAlert("No hay datos", "La sesión no se puede exportar, aún no contiene datos", "Aceptar");
}
else
{
//Set the default application version as Excel 2013.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook with a worksheet
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(lSessionInit.Count);
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(29, 161, 242);
headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White;
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
bodyStyle.Color = Syncfusion.Drawing.Color.White;
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
//Access first worksheet from the workbook instance.
int cont = 0;
foreach (SessionInit sessionInit in lSessionInit)
{
IWorksheet worksheet = workbook.Worksheets[cont];
//Adding text to a cell
worksheet[1, 1].Value = "Sesión";
worksheet[1, 2].Value = "Actividad";
worksheet[1, 3].Value = "Profesional";
worksheet[1, 4].Value = "Alumno";
UserModel user = App.Database.GetUser(App.Database.GetSession(sessionInit.SessionId).UserID);
worksheet[2, 1].Value = session.Name;
worksheet[2, 2].Value = session.ActivityName;
worksheet[2, 3].Value = user.UserName;
worksheet[2, 4].Value = sessionInit.StudentCode;
worksheet.Name = cont.ToString() + " - " + sessionInit.StudentCode;
worksheet.UsedRange.AutofitColumns();
worksheet.Rows[0].CellStyle = headerStyle;
List <SessionData> sessionsData = App.Database.GetSessionData(sessionInit.ID);
int i = 4;
foreach (SessionData sd in sessionsData)
{
worksheet[i, 1].Value = sd.Data;
worksheet.Range["$A$" + i + ":$F$" + i].Merge();
i++;
}
worksheet.Range["A4:F" + i].CellStyle = bodyStyle;
cont++;
}
//Save the workbook to stream in xlsx format.
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
//Save the stream as a file in the device and invoke it for viewing
string filepath = Xamarin.Forms.DependencyService.Get <ISave>().SaveAndView(session.Name.Replace(" ", "") + ".xlsx", "application/msexcel", stream);
Mail mail = new Mail(filepath, user);
Loading = false;
}
}
}
private FileStreamResult ExportExcelShopDailyRevenue(DateTime dateFrom, DateTime dateTo, List <ShopDailyRevenue> Items)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(new[] { "ShopDailyRevenue" });
IWorksheet worksheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel2013;
worksheet.Range["A1:A2"].Merge();
worksheet.Range["B1:B2"].Merge();
worksheet.Range["C1:H1"].Merge();
worksheet.Range["I1:N1"].Merge();
// Header First
worksheet.Range["A1"].Text = "Branch";
worksheet.Range["B1"].Text = "ERP_ID";
worksheet.Range["C1"].Text = $"{dateTo.ToString("dd/MM/yyyy", enUS)} to {dateTo.ToString("dd/MM/yyyy", enUS)}";
worksheet.Range["I1"].Text = $"As of {dateTo.ToString("dd/MM/yyyy", enUS)}";
//worksheet.Range["A2"].Text = "BranchID";
worksheet.Range["C2"].Text = "Sender";
worksheet.Range["D2"].Text = "Con";
worksheet.Range["E2"].Text = "Box";
worksheet.Range["F2"].Text = "Revenue";
worksheet.Range["G2"].Text = "YPC";
worksheet.Range["H2"].Text = "YPB";
worksheet.Range["I2"].Text = "Sender";
worksheet.Range["J2"].Text = "Con";
worksheet.Range["K2"].Text = "Box";
worksheet.Range["L2"].Text = "Revenue";
worksheet.Range["M2"].Text = "YPC";
worksheet.Range["N2"].Text = "YPB";
IStyle headerStyleBranch = workbook.Styles.Add("HeaderStyleBranch");
headerStyleBranch.BeginUpdate();
headerStyleBranch.Color = Color.FromArgb(224, 224, 224);
headerStyleBranch.Font.Color = ExcelKnownColors.Black;
headerStyleBranch.Font.Bold = true;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleBranch.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleBranch.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleBranch.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleBranch.EndUpdate();
worksheet.Range["A1:B2"].CellStyle = headerStyleBranch;
IStyle headerStyleMtd = workbook.Styles.Add("HeaderStyleMtd");
headerStyleMtd.BeginUpdate();
headerStyleMtd.Color = Color.FromArgb(187, 222, 251);
headerStyleMtd.Font.Color = ExcelKnownColors.Black;
headerStyleMtd.Font.Bold = true;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleMtd.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleMtd.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleMtd.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyleMtd.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleMtd.EndUpdate();
worksheet.Range["C1:H2"].CellStyle = headerStyleMtd;
IStyle headerStyleDaily = workbook.Styles.Add("HeaderStyleDaily");
headerStyleDaily.BeginUpdate();
headerStyleDaily.Color = Color.FromArgb(225, 248, 225);
headerStyleDaily.Font.Color = ExcelKnownColors.Black;
headerStyleDaily.Font.Bold = true;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyleDaily.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleDaily.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(191, 191, 191);
headerStyleDaily.HorizontalAlignment = ExcelHAlign.HAlignCenter;
headerStyleDaily.VerticalAlignment = ExcelVAlign.VAlignCenter;
headerStyleDaily.EndUpdate();
worksheet.Range["I1:N2"].CellStyle = headerStyleDaily;
worksheet.ImportData(Items, 3, 1, false);
worksheet.Columns[1].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[2].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[3].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[4].CellStyle.NumberFormat = "#,##0.00";
worksheet.Columns[5].CellStyle.NumberFormat = "#,##0.00";
worksheet.Columns[6].CellStyle.NumberFormat = "#,##0.00";
worksheet.Columns[7].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[8].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[9].CellStyle.NumberFormat = "#,##0";
worksheet.Columns[10].CellStyle.NumberFormat = "#,##0.00";
worksheet.Columns[11].CellStyle.NumberFormat = "#,##0.00";
worksheet.Columns[12].CellStyle.NumberFormat = "#,##0.00";
worksheet.Range["C3"].FreezePanes();
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/msexcel", "KE_PDC_ShopDailyRevenue_Report_" + DateTime.Now.ToString("yyyMMdd_HHmmss") + ".xlsx"));
}
static void Main(string[] args)
{
intEMPRESAID = Convert.ToByte(GetSetting(appName, section, "EmpresaID", String.Empty));
intSUCURSALID = Convert.ToByte(GetSetting(appName, section, "SucursalID", String.Empty));
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = GetExistenciaDataTable();
int osos = tabla.Rows.Count;
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:H2"];
string namesuc = GetName(intSUCURSALID);
worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al " + dateday + namesuc;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 13);
worksheet.SetColumnWidth(2, 50);
worksheet.SetColumnWidth(3, 17);
worksheet.SetColumnWidth(4, 28);
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUM(H2:H" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = "LRG-Existencias al " + namer + namesuc + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
}
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the path of the input file
string inputPath = GetFullTemplatePath("BudgetPlanner.xls");
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 12 worksheets
IWorkbook workbook = application.Workbooks.Open(inputPath);
IWorksheet worksheet = workbook.Worksheets[1];
worksheet.FirstVisibleRow = 3;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "BudgetPlannerOutput.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcek2007.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "BudgetPlannerOutput.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "BudgetPlannerOutput.xlsx";
}
IFont font = workbook.CreateFont();
font.Bold = true;
#endregion
#region TextBox
ITextBoxShape textbox = worksheet.TextBoxes.AddTextBox(5, 2, 40, 140);
textbox.Text = "Quick Budget";
textbox.RichText.SetFont(0, 11, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(204, 204, 255);
textbox = worksheet.TextBoxes.AddTextBox(7, 2, 25, 140);
textbox.Text = "Income";
textbox.RichText.SetFont(0, 5, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
textbox = worksheet.TextBoxes.AddTextBox(16, 2, 25, 140);
textbox.Text = "Spending";
textbox.RichText.SetFont(0, 7, font);
textbox.VAlignment = ExcelCommentVAlign.Center;
textbox.HAlignment = ExcelCommentHAlign.Center;
textbox.Fill.FillType = ExcelFillType.Gradient;
textbox.Fill.GradientColorType = ExcelGradientColor.TwoColor;
textbox.Fill.TwoColorGradient(ExcelGradientStyle.Vertical, ExcelGradientVariants.ShadingVariants_2);
textbox.Fill.BackColor = Color.FromArgb(0, 0, 128);
#endregion
#region Write Text and Numbers
worksheet.Range["O6"].Text = "Weekly";
worksheet.Range["E7"].Text = "Frequency";
worksheet.Range["F7"].Text = "Amount";
worksheet.Range["G7"].Text = "Monthly";
worksheet.Range["H7"].Text = "Yearly";
worksheet.Range["B8"].Text = "Total Income";
worksheet.Range["C9"].Text = "Salary/Wages";
worksheet.Range["C10"].Text = "Salary/Wages(Spouse)";
worksheet.Range["C11"].Text = "Other";
worksheet.Range["C12"].Text = "Other";
worksheet.Range["C13"].Text = "Other";
worksheet.Range["B17"].Text = "Transportation";
worksheet.Range["F25"].Number = 3000;
worksheet.Range["F9"].Number = 55000;
worksheet.Range["F10"].Number = 35000;
worksheet.Range["C18"].Text = "Auto Loan/Lease";
worksheet.Range["C19"].Text = "Insurance";
worksheet.Range["C20"].Text = "Gas ";
worksheet.Range["C21"].Text = "Maintenance ";
worksheet.Range["C22"].Text = "Registration/Inspection";
worksheet.Range["C23"].Text = "Bill's train pass";
worksheet.Range["C24"].Text = "Jane's bus pass";
worksheet.Range["C25"].Text = "Other";
worksheet.Range["E16"].Text = "Total";
worksheet.Range["N6"].Text = "Chart";
worksheet.Range["B27"].Text = "Home";
worksheet.Range["F28"].Number = 20000;
worksheet.Range["F29"].Number = 5000;
worksheet.Range["F33"].Number = 5000;
worksheet.Range["C28"].Text = "EMI";
worksheet.Range["C29"].Text = "Rent";
worksheet.Range["C30"].Text = "Maintanence";
worksheet.Range["C31"].Text = "Insurance";
worksheet.Range["C32"].Text = "Furniture";
worksheet.Range["C33"].Text = "Household Supplies";
worksheet.Range["C34"].Text = "Groceries";
worksheet.Range["C35"].Text = "Real Estate Tax";
worksheet.Range["C36"].Text = "Other";
worksheet.Range["B39"].Text = "Utilities";
worksheet.Range["F41"].Number = 1000;
worksheet.Range["F42"].Number = 250;
worksheet.Range["F43"].Number = 150;
worksheet.Range["F45"].Number = 175;
worksheet.Range["C40"].Text = "Phone - Home";
worksheet.Range["C41"].Text = "Phone - Cell";
worksheet.Range["C42"].Text = "Cable";
worksheet.Range["C43"].Text = "Gas";
worksheet.Range["C44"].Text = "Water";
worksheet.Range["C45"].Text = "Electricity";
worksheet.Range["C46"].Text = "Internet";
worksheet.Range["C47"].Text = "Other";
worksheet.Range["B50"].Text = "Health";
worksheet.Range["F53"].Number = 500;
worksheet.Range["C51"].Text = "Dental";
worksheet.Range["C52"].Text = "Medical";
worksheet.Range["C53"].Text = "Medication";
worksheet.Range["C54"].Text = "Vision/contacts";
worksheet.Range["C55"].Text = "Life Insurance";
worksheet.Range["C56"].Text = "Electricity";
worksheet.Range["C57"].Text = "Other";
#endregion
#region Cell styles
IStyle tableStyle = workbook.Styles.Add("TableStyle");
tableStyle.BeginUpdate();
tableStyle.Color = Color.White;
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeRight].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableStyle.Borders[ExcelBordersIndex.EdgeTop].ColorRGB = Color.FromArgb(150, 150, 150);
tableStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableStyle.EndUpdate();
worksheet.Range["E7:H7"].CellStyle.Font.Bold = true;
worksheet.Range["B17"].CellStyle.Font.Bold = true;
worksheet.Range["B27"].CellStyle.Font.Bold = true;
worksheet.Range["B39"].CellStyle.Font.Bold = true;
worksheet.Range["B50"].CellStyle.Font.Bold = true;
worksheet.Range["E7:H7"].CellStyle.Font.Underline = ExcelUnderline.Single;
worksheet.Range["B7:H14"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C9:C13"].CellStyle = tableStyle;
worksheet.Range["E9:F13"].CellStyle = tableStyle;
worksheet.Range["B16:H26"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["B17:C17"].CellStyle.Color = Color.White;
worksheet.Range["C18:C25"].CellStyle = tableStyle;
worksheet.Range["O6"].CellStyle = tableStyle;
worksheet.Range["E18:F25"].CellStyle = tableStyle;
worksheet.Range["B27:H38"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C28:C36"].CellStyle = tableStyle;
worksheet.Range["B27:C27"].CellStyle.Color = Color.White;
worksheet.Range["E28:F36"].CellStyle = tableStyle;
worksheet.Range["B39:H49"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C40:C47"].CellStyle = tableStyle;
worksheet.Range["B39:C39"].CellStyle.Color = Color.White;
worksheet.Range["E40:F47"].CellStyle = tableStyle;
worksheet.Range["B50:H58"].CellStyle.Color = Color.FromArgb(223, 223, 223);
worksheet.Range["C51:C57"].CellStyle = tableStyle;
worksheet.Range["B50:C50"].CellStyle.Color = Color.White;
worksheet.Range["E51:F57"].CellStyle = tableStyle;
#endregion
#region Data Validation
IDataValidation validation = worksheet.Range["E9:E13"].DataValidation;
worksheet.Range["E9:E13"].Text = "Monthly";
validation.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation1 = worksheet.Range["E18:E25"].DataValidation;
worksheet.Range["E18:E25"].Text = "Monthly";
validation1.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation2 = worksheet.Range["O6"].DataValidation;
validation2.ListOfValues = new string[] { "Weekly", "Monthly", "Yearly" };
IDataValidation validation3 = worksheet.Range["E28:E37"].DataValidation;
worksheet.Range["E28:E36"].Text = "Monthly";
validation3.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation4 = worksheet.Range["E40:E47"].DataValidation;
worksheet.Range["E40:E47"].Text = "Monthly";
validation4.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
IDataValidation validation5 = worksheet.Range["E51:E57"].DataValidation;
worksheet.Range["E51:E57"].Text = "Monthly";
validation5.ListOfValues = new string[] { "Daily", "Weekly", "Monthly", "Semi-Annually", "Quarterly", "Yearly" };
#endregion
#region Formulas
worksheet.Range["G8"].Formula = "SUM(G9:G13)";
worksheet.Range["H8"].Formula = "SUM(H9:H13)";
worksheet.Range["G17"].Formula = "SUM(G18:G25)";
worksheet.Range["H17"].Formula = "SUM(H18:H25)";
worksheet.Range["G16"].Formula = "G17+G27+G39+G50+G59+G71";
worksheet.Range["h16"].Formula = "H17+H27+H39+H50+H59+H71";
for (int i = 9; i <= 13; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
for (int i = 18; i <= 25; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G27"].Formula = "SUM(G28:G36)";
worksheet.Range["H27"].Formula = "SUM(H28:H37)";
for (int i = 28; i <= 36; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G39"].Formula = "SUM(G40:G47)";
worksheet.Range["H39"].Formula = "SUM(H40:H47)";
for (int i = 40; i <= 47; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
worksheet.Range["G50"].Formula = "SUM(G51:G57)";
worksheet.Range["H50"].Formula = "SUM(H51:H57)";
for (int i = 51; i <= 57; i++)
{
worksheet.Range["G" + i].Formula = "F" + i + "*A" + i;
worksheet.Range["H" + i].Formula = "G" + i + "*12";
}
#endregion
#region SummaryChart
//Clustered Column Chart
IChartShape chart = worksheet.Charts.Add();
//Set Chart Type
chart.ChartType = ExcelChartType.Bar_Clustered;
//Set DataRange.
chart.Series.Add("Expense");
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["N10"];
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Income");
chart.Series[1].Values = workbook.Worksheets["Sheet1"].Range["N9"];
chart.Series[1].DataPoints[0].DataLabels.IsValue = true;
chart.Series[1].DataPoints[0].DataLabels.Size = 7f;
chart.Series.Add("Balance");
chart.Series[2].Values = workbook.Worksheets["Sheet1"].Range["N8"];
chart.Series[2].DataPoints[0].DataLabels.IsValue = true;
chart.Series[2].DataPoints[0].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
chart.PrimaryCategoryAxis.Visible = false;
//Format Chart Area
IChartFrameFormat chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
IChartFrameFormat chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 7;
chart.BottomRow = 22;
chart.LeftColumn = 9;
chart.RightColumn = 16;
chart.ChartTitle = "Budget Summary";
chart.ChartTitleArea.Bold = true;
#endregion
#region SpendingChart
chart = worksheet.Charts.Add();
chart.ChartTitle = "Spending Summary";
chart.ChartTitleArea.Bold = true;
//Set Chart Type
chart.ChartType = ExcelChartType.Pie_3D;
//Set DataRange.
chart.DataRange = workbook.Worksheets["Sheet1"].Range["J9:K12"];
chart.IsSeriesInRows = false;
chart.Series[0].Values = workbook.Worksheets["Sheet1"].Range["K9:K12"];
chart.Series[0].CategoryLabels = workbook.Worksheets["Sheet1"].Range["J9:J12"];
chart.Series[0].Name = "Spending summary";
chart.Series[0].DataPoints[0].DataLabels.IsValue = true;
chart.Series[0].DataPoints[0].DataLabels.Size = 7f;
chart.Series[0].DataPoints[1].DataLabels.IsValue = true;
chart.Series[0].DataPoints[1].DataLabels.Size = 7f;
chart.Series[0].DataPoints[2].DataLabels.IsValue = true;
chart.Series[0].DataPoints[2].DataLabels.Size = 7f;
chart.Series[0].DataPoints[3].DataLabels.IsValue = true;
chart.Series[0].DataPoints[3].DataLabels.Size = 7f;
chart.PrimaryValueAxis.NumberFormat = "$#,##0";
//Format Chart Area
chartArea = chart.ChartArea;
//Style
chartArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chartArea.Border.LineColor = Color.Gray;
chartArea.Border.LineWeight = ExcelChartLineWeight.Medium;
//Plot Area
chartPlotArea = chart.PlotArea;
chartPlotArea.Border.LinePattern = ExcelChartLinePattern.Solid;
chart.PlotArea.Border.LineColor = Color.Gray;
chartPlotArea.Fill.ForeColor = Color.FromArgb(223, 223, 223);
chart.Legend.Position = ExcelLegendPosition.Bottom;
//Embedded chart position.
chart.TopRow = 25;
chart.BottomRow = 42;
chart.LeftColumn = 9;
chart.RightColumn = 16;
#endregion
#region Sheet View
workbook.Worksheets["Sheet1"].Visibility = WorksheetVisibility.Hidden;
workbook.Worksheets[0].Activate();
workbook.TabSheets[0].TabColor = ExcelKnownColors.Blue;
workbook.TabSheets[1].TabColor = ExcelKnownColors.Blue;
workbook.Worksheets[1].IsRowColumnHeadersVisible = false;
worksheet.InsertColumn(9);
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir)
{
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version)));
DateTime begin = DateTime.Now;
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = version;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Header
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
headerStyle.Color = Color.FromArgb(239, 243, 247);
//headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 12;
headerStyle.Color = Color.FromArgb(192, 192, 192);
headerStyle.Locked = true;
headerStyle.Font.FontName = "Arial";
headerStyle.Font.Color = ExcelKnownColors.Black;
headerStyle.Borders.LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
headerStyle.EndUpdate();
worksheet.SetDefaultRowStyle(1, headerStyle);
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.RowHeight = 36.75;
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Body
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204));
bodyStyle.Color = Color.FromArgb(255, 255, 204);
bodyStyle.Font.Size = 10;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Font.FontName = "Arial";
bodyStyle.Font.Bold = false;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
//bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
data.RowHeight = 31.50;
//IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle;
//DateTime beginFillPattern = DateTime.Now;
//data.CellStyle.FillPattern = ExcelPattern.Solid;
//DateTime endFillPattern = DateTime.Now;
//messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern)));
//worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm";
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i);
}
worksheet.SetColumnWidth(5, 18.00);
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb));
}
/// <summary>
/// Convert the Excel document to JSON
/// </summary>
/// <returns>Return the JSON document as stream</returns>
public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable)
{
if (button == "Input Document")
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Opening the encrypted Workbook
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Exports the DataTable to a spreadsheet.
string fileName = string.Empty;
#region Workbook Initialize
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (option == "XLS")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (dataTable != null)
{
worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1);
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247));
bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218));
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
#endregion
}
}
}
public void WriteDataToWorksheet(IWorksheet worksheet, IStyle headerStyle)
{
try
{
//Assign some text in a cell
worksheet.Range["A2"].Text = "NHI";
worksheet.Range["B2"].Text = "Referral Source";
worksheet.Range["C2"].Text = "Last Review Date";
worksheet.Range["D2"].Text = "Entry Date to Support Hours";
worksheet.Range["E2"].Text = "Weekly Allocated Hours";
worksheet.Range["F2"].Text = "Weekly Allocated Travel Time";
worksheet.Range["G2"].Text = "Exit Date";
worksheet.Range["H2"].Text = "Child & Youth Client";
worksheet.Range["I2"].Text = "Support hours narrative";
headerStyle.BeginUpdate();
headerStyle.Color = Color.Aqua;
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[0].CellStyle = headerStyle;
var count = 3;
foreach (var service in Vm.Services)
{
count++;
foreach (var cl in service.Clients)
{
worksheet.Range[$"A{count}"].Text =
string.IsNullOrEmpty(cl.NHINumber) ? cl.ClientName : cl.NHINumber;
//Referral Source
worksheet.Range[$"B{count}"].Text = cl.ReferralData.ReferrerOrganisation;
//Last Review Date
worksheet.Range[$"C{count}"].Text = cl.ReferralData.ReferralUpdated;
//Entry Date to Support Hours
worksheet.Range[$"D{count}"].Text = cl.ReferralData.ReferralDate;
//Weekly Allocated Hours
worksheet.Range[$"E{count}"].Text = cl.ServiceData.EngagementStatus;
//Weekly Allocated Time
//TODO Find out what this is??
//Exit Date
worksheet.Range[$"G{count}"].Text = string.IsNullOrEmpty(cl.ServiceData.ServiceEndDate) ? "Still Active" : cl.ServiceData.ServiceEndDate;
//Child & Youth
if (cl.ReferralData.ReferrerOrganisation == Youth)
{
worksheet.Range[$"H{count}"].Text = "Yes";
}
worksheet.Range[$"I{count}"].Text = service.ServiceName;
count++;
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount)))
{
MessageBox.Show("Enter Numerical Value");
return;
}
if (rowCount <= 0)
{
MessageBox.Show("Invalid row count");
return;
}
if (colCount <= 0)
{
MessageBox.Show("Invalid column count");
return;
}
if (rdbExcel97.Checked)
{
if (colCount > 256)
{
MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format.");
return;
}
if (rowCount > 65536)
{
MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format.");
return;
}
}
if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked)
{
if (rowCount > 100001)
{
MessageBox.Show("Row count must be less than or equal to 100,000.");
return;
}
if (colCount > 151)
{
MessageBox.Show("Column count must be less than or equal to 151.");
return;
}
}
#region Starttime
//Start Time
#endregion
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "PerformanceChecking.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "PerformanceChecking.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
startTime = DateTime.Now;
workbook.DetectDateTimeInValue = false;
#endregion
if (chbColumnStyle.Checked)
{
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle);
}
if (this.chkImportOnSave.Checked)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
startTime = DateTime.Now;
worksheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
#region Apply Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
headerStyle.Color = Color.FromArgb(255, 174, 33);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
#endregion
IMigrantRange migrantRange = worksheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
migrantRange.CellStyle = headerStyle;
}
#region Insert Data
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
#region Workook Save
workbook.SaveAs(fileName);
#endregion
#region Workbook Save and Dispose
//Close the workbook
workbook.Close();
//Dispose the Excel Engine
excelEngine.Dispose();
#endregion
#region Set EndTime and get LogDetails
//End Time
endTime = DateTime.Now - startTime;
LogDetails(endTime);
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
}
#endregion
}
private void generarreporte()
{
DataTable tabla = dtblcatalogo;
//Refresh(CashierBusyIndicator);
//generar();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ").ToUpper();
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
int osos = tabla.Rows.Count;
worksheet.Name = "Servicios";
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:K2"];
worksheet.Range["A1"].Text = "CATÁLOGO DE SERVICIOS AL " + dateday;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 30);
worksheet.SetColumnWidth(3, 15);
worksheet.SetColumnWidth(4, 20);
worksheet.SetColumnWidth(5, 7);
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 15);
worksheet.SetColumnWidth(11, 15);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\CATÁLOGO DE SERVICIOS AL " + dateday + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
private void btnExport_Click(object sender, System.EventArgs e)
{
//Exports the DataTable to a spreadsheet.
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "ExportToExcel.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (this.dataGrid1.DataSource != null)
{
worksheet.ImportDataTable((DataTable)this.dataGrid1.DataSource, true, 3, 1, -1, -1);
}
else
{
MessageBox.Show("There is no datatable to export, Please import a datatable first", "Error");
//Close the workbook.
workbook.Close();
return;
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 242, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(182, 189, 218));
headerStyle.Color = Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
workbook.SaveAs(fileName);
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(fileName);
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private async void ExportCommandExecute(SessionModel session)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
Loading = true;
//await Task.Delay(200);
await Task.Run(() =>
{
List <SessionInit> lSessionInit = App.Database.GetSessionInit(session.ID);
if (lSessionInit == null || lSessionInit.Count == 0)
{
Loading = false;
Device.BeginInvokeOnMainThread(async() =>
{
Application.Current.MainPage.DisplayAlert("No hay datos", "La sesión no se puede exportar, aún no contiene datos", "Aceptar");
});
}
else
{
//Set the default application version as Excel 2013.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013;
//Create a workbook with a worksheet
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(lSessionInit.Count);
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(29, 161, 242);
headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.White;
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
bodyStyle.Color = Syncfusion.Drawing.Color.White;
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
//Access first worksheet from the workbook instance.
ActivityModel activity = Json.GetActivityByName(session.ActivityName);
int cont = 0;
foreach (SessionInit sessionInit in lSessionInit)
{
IWorksheet worksheet = workbook.Worksheets[cont];
//Adding text to a cell
worksheet[1, 1].Value = "Sesión";
worksheet[2, 1].Value = "Actividad";
worksheet[3, 1].Value = "Fecha";
worksheet[4, 1].Value = "Duración";
worksheet[5, 1].Value = "Profesional";
worksheet[6, 1].Value = "Alumno";
UserModel user = App.Database.GetUser(App.Database.GetSession(sessionInit.SessionId).UserID);
worksheet[1, 2].Value = session.Name;
worksheet[2, 2].Value = session.ActivityName;
worksheet[3, 2].Text = sessionInit.Date.ToString("dd/MM/yyyy HH:mm:ss");
worksheet[4, 2].Text = sessionInit.Time;
worksheet[5, 2].Value = user.UserName;
worksheet[6, 2].Value = sessionInit.StudentCode;
worksheet.Name = cont.ToString() + " - " + sessionInit.StudentCode;
worksheet[1, 1].CellStyle = headerStyle;
worksheet[2, 1].CellStyle = headerStyle;
worksheet[3, 1].CellStyle = headerStyle;
worksheet[4, 1].CellStyle = headerStyle;
worksheet[5, 1].CellStyle = headerStyle;
worksheet[6, 1].CellStyle = headerStyle;
int column = 1;
foreach (MessageDevice message in activity.Messages)
{
foreach (FieldMessage field in message.Fields)
{
worksheet[9, column].Value = field.Description;
worksheet[9, column].CellStyle = headerStyle;
column++;
}
}
Dictionary <MessageDevice, int> messagesColumns = new Dictionary <MessageDevice, int>();
int calculateColumn = 1;
foreach (MessageDevice message in activity.Messages)
{
messagesColumns.Add(message, calculateColumn);
calculateColumn += message.Fields.Count();
}
List <SessionData> sessionsData = App.Database.GetSessionData(sessionInit.ID);
int row = 10;
column = 1;
int numByte = 0;
for (int j = 0; j < sessionsData.Count; j++)
{
MessageDevice messageType1 = activity.Messages.Find(m => m.Fields.Sum(f => f.Bytes) == sessionsData[j].Data.Length);
if (messageType1 == null)
{
int size = Convert.ToInt32(string.Concat(sessionsData[j].Data[3].ToString("X2")), 16) + 4;
SessionData sessionDataComplementary = sessionsData.Find(s => s.Data.Length + sessionsData[j].Data.Length == size && s.DeviceName.Equals(sessionsData[j].DeviceName));
byte[] messageTemp = null;
if (sessionDataComplementary != null)
{
messageTemp = new byte[sessionsData[j].Data.Length + sessionDataComplementary.Data.Length];
Array.Copy(sessionsData[j].Data, 0, messageTemp, 0, sessionsData[j].Data.Length);
Array.Copy(sessionDataComplementary.Data, 0, messageTemp, sessionsData[j].Data.Length, sessionDataComplementary.Data.Length);
messageType1 = activity.Messages.Find(m => m.Fields.Sum(f => f.Bytes) == messageTemp.Length);
}
if (messageType1 != null)
{
column = messagesColumns[messageType1];
numByte = 0;
foreach (FieldMessage field in messageType1.Fields)
{
string m = Convert.ToInt32(string.Concat(messageTemp.Skip(numByte).Take(field.Bytes).ToArray().Select(b => b.ToString("X2"))), 16).ToString();
if (field.Format != null && field.Format.Equals("ms"))
{
double millis = Convert.ToDouble(m);
worksheet[row, column].NumberFormat = "###,###,##0.0#########";
worksheet[row, column].Number = millis / 1000.0;;
}
else
{
worksheet[row, column].Text = m;
}
worksheet[row, column].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
column++;
numByte += field.Bytes;
}
row++;
}
}
else
{
column = messagesColumns[messageType1];
numByte = 0;
foreach (FieldMessage field in messageType1.Fields)
{
string m = Convert.ToInt32(string.Concat(sessionsData[j].Data.Skip(numByte).Take(field.Bytes).ToArray().Select(b => b.ToString("X2"))), 16).ToString();
worksheet[row, column].Value = m;
column++;
numByte += field.Bytes;
}
row++;
}
}
worksheet.UsedRange.AutofitColumns();
cont++;
}
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
string filepath = DependencyService.Get <ISave>().Save("SmartGames_" + session.Name.Replace(" ", "") + ".xlsx", "application/msexcel", stream);
Mail mail = new Mail(filepath, User);
}
});
Loading = false;
}
}
