static void ChangeCellColors(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C3:H10"].Merge();
worksheet.Range["C3:H10"].Value = "Test";
worksheet.Range["C3:H10"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
worksheet.Range["C3:H10"].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
worksheet.Cells["A1"].Value = "Test";
#region #ColorCells
// Format an individual cell.
worksheet.Cells["A1"].Font.Color = Color.Red;
worksheet.Cells["A1"].FillColor = Color.Yellow;
// Format a range of cells.
CellRange range = worksheet.Range["C3:H10"];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Fill.BackgroundColor = Color.LightBlue;
rangeFormatting.Fill.PatternType = PatternType.LightHorizontal;
rangeFormatting.Fill.PatternColor = Color.Violet;
range.EndUpdateFormatting(rangeFormatting);
#endregion #ColorCells
}
finally {
workbook.EndUpdate();
}
}
static void Print(IWorkbook workbook)
{
Worksheet worksheet = workbook.Worksheets[0];
// Generate worksheet content - the simple multiplication table.
CellRange columnHeadings = worksheet.Range.FromLTRB(1, 0, 40, 0);
columnHeadings.Formula = "=COLUMN() - 1";
CellRange rowHeadings = worksheet.Range.FromLTRB(0, 1, 0, 40);
rowHeadings.Formula = "=ROW() - 1";
CellRange tableRange = worksheet.Range.FromLTRB(1, 1, 40, 40);
tableRange.Formula = "=(ROW()-1)*(COLUMN()-1)";
// Format headers of the multiplication table.
Formatting rangeFormatting = columnHeadings.BeginUpdateFormatting();
rangeFormatting.Borders.BottomBorder.LineStyle = BorderLineStyle.Thin;
rangeFormatting.Borders.BottomBorder.Color = Color.Black;
columnHeadings.EndUpdateFormatting(rangeFormatting);
rangeFormatting = rowHeadings.BeginUpdateFormatting();
rangeFormatting.Borders.RightBorder.LineStyle = BorderLineStyle.Thin;
rangeFormatting.Borders.RightBorder.Color = Color.Black;
rowHeadings.EndUpdateFormatting(rangeFormatting);
rangeFormatting = tableRange.BeginUpdateFormatting();
rangeFormatting.Fill.BackgroundColor = Color.LightBlue;
tableRange.EndUpdateFormatting(rangeFormatting);
#region #WorksheetPrintOptions
worksheet.ActiveView.Orientation = PageOrientation.Landscape;
// Display row and column headings.
worksheet.ActiveView.ShowHeadings = true;
worksheet.ActiveView.PaperKind = System.Drawing.Printing.PaperKind.A4;
// Access an object that contains print options.
WorksheetPrintOptions printOptions = worksheet.PrintOptions;
// Print in black and white.
printOptions.BlackAndWhite = true;
// Do not print gridlines.
printOptions.PrintGridlines = false;
// Scale the print area to fit to two pages wide.
printOptions.FitToPage = true;
printOptions.FitToWidth = 2;
// Print a dash instead of a cell error message.
printOptions.ErrorsPrintMode = ErrorsPrintMode.Dash;
#endregion #WorksheetPrintOptions
#region #PrintWorkbook
// Invoke the Print Preview dialog for the workbook.
using (LegacyPrintableComponentLink link = new LegacyPrintableComponentLink(workbook))
{
link.CreateDocument();
link.ShowPrintPreviewDialog(App.Current.MainWindow);
}
#endregion #PrintWorkbook
}
static void FormatCell(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["B2"].Value = "Test";
worksheet.Range["C3:E6"].Value = "Test";
#region #CellFormatting
// Access the cell to be formatted.
Cell cell = worksheet.Cells["B2"];
// Specify font settings (font name, color, size and style).
cell.Font.Name = "MV Boli";
cell.Font.Color = Color.Blue;
cell.Font.Size = 14;
cell.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
cell.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in the cell.
cell.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
cell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
#endregion #CellFormatting
#region #RangeFormatting
// Access the range of cells to be formatted.
CellRange range = worksheet.Range["C3:E6"];
// Begin updating of the range formatting.
Formatting rangeFormatting = range.BeginUpdateFormatting();
// Specify font settings (font name, color, size and style).
rangeFormatting.Font.Name = "MV Boli";
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Font.Size = 14;
rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
rangeFormatting.Fill.BackgroundColor = Color.LightSkyBlue;
// Specify text alignment in cells.
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
// End updating of the range formatting.
range.EndUpdateFormatting(rangeFormatting);
#endregion #RangeFormatting
}
finally {
workbook.EndUpdate();
}
}
static void SelectedCell(SpreadsheetControl control)
{
control.BeginUpdate();
control.SelectedCell.FillColor = Color.LightGray;
CellRange c = control.SelectedCell;
c.FillColor = Color.Blue;
CellRange currentSelection = control.Selection;
Formatting rangeFormatting = currentSelection.BeginUpdateFormatting();
rangeFormatting.Borders.SetOutsideBorders(DevExpress.Utils.DXColor.Green, BorderLineStyle.MediumDashDot);
currentSelection.EndUpdateFormatting(rangeFormatting);
control.EndUpdate();
}
public ActionResult CustomAction(string customCommand)
{
IWorkbook workbook = SpreadsheetExtension.GetCurrentDocument("Spreadsheet");
Worksheet worksheet = workbook.Worksheets[0];
switch (customCommand)
{
case "applyFormatting":
CellRange priceRange = worksheet.Range["C2:C15"];
Formatting rangeFormatting = priceRange.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.SandyBrown;
rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
rangeFormatting.Fill.BackgroundColor = Color.PaleGoldenrod;
rangeFormatting.NumberFormat = "$0.0#";
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
priceRange.EndUpdateFormatting(rangeFormatting);
break;
case "insertLink":
worksheet.Columns["G"].WidthInPixels = 180;
Cell cell1 = worksheet.Cells["G4"];
cell1.Fill.BackgroundColor = Color.WhiteSmoke;
worksheet.Hyperlinks.Add(cell1, "https://documentation.devexpress.com/OfficeFileAPI/14912/Spreadsheet-Document-API", true, "Spreadsheet Document API");
break;
case "drawBorders":
CellRange tableRange = worksheet.Range["A2:E16"];
tableRange.Borders.SetAllBorders(Color.RosyBrown, BorderLineStyle.Hair);
break;
case "showTotal":
Cell cell2 = worksheet.Cells["E16"];
cell2.Formula = "=SUBTOTAL(9,E2:E15)";
Cell cell3 = worksheet.Cells["A16"];
cell3.Formula = "SUBTOTAL(103,A2:A15)";
Cell cell4 = worksheet.Cells["D16"];
cell4.Value = "Total amount";
break;
}
return(SpreadsheetExtension.GetCustomActionResult("Spreadsheet"));
}
protected void ASPxSpreadsheet1_Callback(object sender, DevExpress.Web.CallbackEventArgsBase e)
{
ASPxSpreadsheet spreadSheet = sender as ASPxSpreadsheet;
IWorkbook workbook = spreadSheet.Document;
Worksheet worksheet = workbook.Worksheets[0];
switch (e.Parameter)
{
case "applyFormatting":
CellRange priceRange = worksheet.Range["C2:C15"];
Formatting rangeFormatting = priceRange.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.SandyBrown;
rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
rangeFormatting.Fill.BackgroundColor = Color.PaleGoldenrod;
rangeFormatting.NumberFormat = "$0.0#";
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
priceRange.EndUpdateFormatting(rangeFormatting);
break;
case "insertLink":
worksheet.Columns["G"].WidthInPixels = 180;
CellRange cell1 = worksheet.Cells["G4"];
cell1.Fill.BackgroundColor = Color.WhiteSmoke;
worksheet.Hyperlinks.Add(cell1, "https://documentation.devexpress.com/OfficeFileAPI/14912/Spreadsheet-Document-API", true, "Spreadsheet Document API");
break;
case "drawBorders":
CellRange tableRange = worksheet.Range["A2:E16"];
tableRange.Borders.SetAllBorders(Color.RosyBrown, BorderLineStyle.Hair);
break;
case "showTotal":
CellRange cell2 = worksheet.Cells["E16"];
cell2.Formula = "=SUBTOTAL(9,E2:E15)";
CellRange cell3 = worksheet.Cells["A16"];
cell3.Formula = "SUBTOTAL(103,A2:A15)";
CellRange cell4 = worksheet.Cells["D16"];
cell4.Value = "Total amount";
break;
}
}
static void ChangeCellColors(Workbook workbook)
{
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Range["C3:D4"].Merge();
worksheet.Range["C3:D4"].Value = "Test";
worksheet.Cells["A1"].Value = "Test";
#region #ColorCells
// Format an individual cell.
worksheet.Cells["A1"].Font.Color = Color.Red;
worksheet.Cells["A1"].FillColor = Color.Yellow;
// Format a cell range.
CellRange range = worksheet.Range["C3:D4"];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Fill.BackgroundColor = Color.LightBlue;
rangeFormatting.Fill.PatternType = PatternType.LightHorizontal;
rangeFormatting.Fill.PatternColor = Color.Violet;
range.EndUpdateFormatting(rangeFormatting);
#endregion #ColorCells
}
static void AddCellBorders(IWorkbook workbook)
{
workbook.BeginUpdate();
try {
#region #CellBorders
Worksheet worksheet = workbook.Worksheets[0];
// Set each particular border for the cell.
Cell cellB2 = worksheet.Cells["B2"];
Borders cellB2Borders = cellB2.Borders;
cellB2Borders.LeftBorder.LineStyle = BorderLineStyle.MediumDashDot;
cellB2Borders.LeftBorder.Color = Color.Pink;
cellB2Borders.TopBorder.LineStyle = BorderLineStyle.MediumDashDotDot;
cellB2Borders.TopBorder.Color = Color.HotPink;
cellB2Borders.RightBorder.LineStyle = BorderLineStyle.MediumDashed;
cellB2Borders.RightBorder.Color = Color.DeepPink;
cellB2Borders.BottomBorder.LineStyle = BorderLineStyle.Medium;
cellB2Borders.BottomBorder.Color = Color.Red;
// Set all borders for cells.
Cell cellC4 = worksheet.Cells["C4"];
cellC4.Borders.SetAllBorders(Color.Orange, BorderLineStyle.MediumDashed);
Cell cellD6 = worksheet.Cells["D6"];
cellD6.Borders.SetOutsideBorders(Color.Gold, BorderLineStyle.Double);
#endregion #CellBorders
#region #CellRangeBorders
// Set all borders for the range of cells in one step.
CellRange range1 = worksheet.Range["B8:F13"];
range1.Borders.SetAllBorders(Color.Green, BorderLineStyle.Double);
// Set all inside and outside borders separately for the range of cells.
CellRange range2 = worksheet.Range["C15:F18"];
range2.SetInsideBorders(Color.SkyBlue, BorderLineStyle.MediumDashed);
range2.Borders.SetOutsideBorders(Color.DeepSkyBlue, BorderLineStyle.Medium);
// Set all horizontal and vertical borders separately for the range of cells.
CellRange range3 = worksheet.Range["D21:F23"];
Formatting range3Formatting = range3.BeginUpdateFormatting();
Borders range3Borders = range3Formatting.Borders;
range3Borders.InsideHorizontalBorders.LineStyle = BorderLineStyle.MediumDashDot;
range3Borders.InsideHorizontalBorders.Color = Color.DarkBlue;
range3Borders.InsideVerticalBorders.LineStyle = BorderLineStyle.MediumDashDotDot;
range3Borders.InsideVerticalBorders.Color = Color.Blue;
range3.EndUpdateFormatting(range3Formatting);
// Set each particular border for the range of cell.
CellRange range4 = worksheet.Range["E25:F26"];
Formatting range4Formatting = range4.BeginUpdateFormatting();
Borders range4Borders = range4Formatting.Borders;
range4Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thick);
range4Borders.LeftBorder.Color = Color.Violet;
range4Borders.TopBorder.Color = Color.Violet;
range4Borders.RightBorder.Color = Color.DarkViolet;
range4Borders.BottomBorder.Color = Color.DarkViolet;
range4.EndUpdateFormatting(range4Formatting);
#endregion #CellRangeBorders
}
finally {
workbook.EndUpdate();
}
}
private void simpleActionRprtCntbl_Execute(object sender, DevExpress.ExpressApp.Actions.SimpleActionExecuteEventArgs e)
{
if (View != null)
{
int i = 5;
Empresa emp = View.ObjectSpace.FindObject <Empresa>(null);
// Servicios Profesionales
Workbook book = new Workbook();
var sheet = book.Worksheets.ActiveWorksheet;
sheet.Cells[0, 0].Value = emp.Compania.Nombre;
sheet.Cells[0, 3].Value = emp.Compania.Rfc;
sheet.Cells[1, 0].Value =
(emp.Regimenes != null && emp.Regimenes.Count > 0)
? (emp.Regimenes[0] as RegimenEmpresa).Rgmn.Dscrpcn
: string.Empty;
// emp.Regimen; TIT Sep 2018
int ano = DateTime.Today.Month == 1 ? DateTime.Today.Year - 1 : DateTime.Today.Year;
sheet.Cells["A4"].Value = string.Format("Ejercicio {0}.", ano);
sheet.Cells[i++, 0].Value = "ENERO";
sheet.Cells[i++, 0].Value = "FEBRERO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "MARZO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "ABRIL";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "MAYO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "JUNIO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "JULIO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "AGOSTO";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "SEPTIEMBRE";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "OCTUBRE";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "NOVIEMBRE";
sheet.Cells[i++, 0].Value = "Acumulado";
sheet.Cells[i++, 0].Value = "DICIEMBRE";
sheet.Cells[i++, 0].Value = "Tota Acumulado";
sheet.Cells["c3"].Value = "ISR";
sheet.Cells["c3"].Font.FontStyle = SpreadsheetFontStyle.Bold;
// Access the range of cells to be formatted.
CellRange range = sheet.Range["C4:G4"];
// Begin updating of the range formatting.
DevExpress.Spreadsheet.Formatting rangeFormatting = range.BeginUpdateFormatting();
// Specify font settings (font name, color, size and style).
rangeFormatting.Font.Name = "Arial";
// rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Font.Size = 8;
// rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
rangeFormatting.Fill.BackgroundColor = Color.LightGray;
// Specify text alignment in cells.
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
rangeFormatting.Alignment.WrapText = true;
// End updating of the range formatting.
range.EndUpdateFormatting(rangeFormatting);
range = sheet.Range["G3:K3"];
// Begin updating of the range formatting.
rangeFormatting = range.BeginUpdateFormatting();
// Specify font settings (font name, color, size and style).
rangeFormatting.Font.Name = "Arial";
// rangeFormatting.Font.Color = Color.Blue;
rangeFormatting.Font.Size = 8;
// rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
// Specify cell background color.
rangeFormatting.Fill.BackgroundColor = Color.LightGray;
// Specify text alignment in cells.
rangeFormatting.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
rangeFormatting.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
rangeFormatting.Alignment.WrapText = true;
// End updating of the range formatting.
range.EndUpdateFormatting(rangeFormatting);
sheet.Cells["c4"].Value = string.Format("INGRESOS{0}(Cobrados por sus{0}ventas o servicios){0}Sin incluir IVA",
Environment.NewLine);
/*
* sheet.Cells["c4"].Alignment.Vertical = SpreadsheetVerticalAlignment.Justify;
* sheet.Cells["c4"].Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
* sheet.Cells["c4"].Alignment.ShrinkToFit = true;
* sheet.Cells["c4"].Alignment.WrapText = true;*/
sheet.Columns[2].Width = 350;
sheet.Columns[3].Width = 340;
sheet.Columns[4].Width = 340;
sheet.Columns[6].Width = 330;
sheet.Columns[7].Width = 330;
sheet.Columns[8].Width = 330;
sheet.Columns[9].Width = 330;
sheet.Columns[10].Width = 330;
sheet.Cells["d4"].Value = string.Format("DEDUCCIONES{0}(Compras y/o{0}gastos, sin incluir{0}IVA",
Environment.NewLine);
sheet.Cells["e4"].Value = string.Format("INGRESOS ACUMULABLES -{0}DEDUCCIONES ACUMULABLES ={0}BASE",
Environment.NewLine);
sheet.Cells["f4"].Value = "ISR RETENIDO";
sheet.Cells["g3"].Value = "PAGOS (Provisionales)";
sheet.Cells["g4"].Value = "ISR";
sheet.Rows[3].Height = 260;
sheet.Cells["h3"].Value = string.Format("IVA{0}(Causado por{0}sus ventas o{0}servicios)",
Environment.NewLine);
sheet.Cells["i3"].Value = string.Format("IVA{0}(Acreditable por{0}sus compras y/o{0}gastos)",
Environment.NewLine);
sheet.Cells["j3"].Value = string.Format("IVA{0}RETENIDO", Environment.NewLine);
sheet.Cells["k3"].Value = string.Format("IVA PAGADO (+){0}O{0}A FAVOR (-)", Environment.NewLine);
sheet.Cells["l3"].Value = "DIOT";
sheet.Rows[2].Height = 240;
decimal[] total = new decimal[12];
decimal[] reten = new decimal[12];
decimal[] ivaTras = new decimal[12];
decimal[] ivaRet = new decimal[12];
decimal[] ivaAcr = new decimal[12];
decimal[] totAcm = new decimal[12];
decimal[] retAcm = new decimal[12];
decimal[] totalDdc = new decimal[12];
decimal[] totAcmDdc = new decimal[12];
CriteriaOperator[] operands = new CriteriaOperator[2];
i = 5;
operands[1] = new BinaryOperator("Status", DocumentoStatus.Sellada, BinaryOperatorType.Equal);
for (int mesini = 1; mesini < 13; mesini++)
{
DateTime mFechaIni = apl.Log.Fecha.FechaInicial(mesini, ano);
DateTime mFechaFin = apl.Log.Fecha.FechaFinal(mesini, ano);
operands[0] = GroupOperator.And(
new BinaryOperator("FechaDoc", mFechaIni, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("FechaDoc", mFechaFin, BinaryOperatorType.LessOrEqual));
operands[1] = new BinaryOperator("Status", DocumentoStatus.Sellada, BinaryOperatorType.Equal);
IList arr = ((XPObjectSpace)View.ObjectSpace).CreateCollection(typeof(DocumentoSalida), new GroupOperator(operands), null);
total[mesini - 1] = 0;
totAcm[mesini - 1] = 0;
reten[mesini - 1] = 0;
retAcm[mesini - 1] = 0;
ivaTras[mesini - 1] = 0;
ivaRet[mesini - 1] = 0;
ivaAcr[mesini - 1] = 0;
totalDdc[mesini - 1] = 0;
totAcmDdc[mesini - 1] = 0;
if (arr.Count > 0)
{
foreach (DocumentoSalida doc in arr)
{
total[mesini - 1] += doc.SubTotal;
reten[mesini - 1] += doc.RetenISR;
ivaTras[mesini - 1] += doc.Impuesto04;
ivaRet[mesini - 1] += doc.RetenIVA;
}
}
operands[0] = GroupOperator.And(
new BinaryOperator("FechaDoc", mFechaIni, BinaryOperatorType.GreaterOrEqual),
new BinaryOperator("FechaDoc", mFechaFin, BinaryOperatorType.LessOrEqual));
operands[1] = null;
arr = ((XPObjectSpace)View.ObjectSpace).CreateCollection(typeof(Recepcion), new GroupOperator(operands), null);
if (arr.Count > 0)
{
foreach (Recepcion doc in arr)
{
totalDdc[mesini - 1] += doc.SubTotal;
ivaAcr[mesini - 1] += doc.Impuesto04;
}
}
if ((mesini - 1) > 0)
{
// 1, 2, ...,11 0, 1, ...,10 1, 2, ...,11
totAcm[mesini - 1] += totAcm[mesini - 2] + total[mesini - 1];
retAcm[mesini - 1] += retAcm[mesini - 2] + reten[mesini - 1];
totAcmDdc[mesini - 1] += totAcmDdc[mesini - 2] + totalDdc[mesini - 1];
}
else
{
// 0 0
totAcm[mesini - 1] = total[mesini - 1];
retAcm[mesini - 1] = reten[mesini - 1];
totAcmDdc[mesini - 1] = totalDdc[mesini - 1];
}
if (total[mesini - 1] != 0)
{
sheet.Cells[i, 2].SetValue(total[mesini - 1]);
sheet.Cells[i, 2].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (totalDdc[mesini - 1] != 0)
{
sheet.Cells[i, 3].SetValue(totalDdc[mesini - 1]);
sheet.Cells[i, 3].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (reten[mesini - 1] != 0)
{
sheet.Cells[i, 5].SetValue(reten[mesini - 1]);
sheet.Cells[i, 5].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (ivaTras[mesini - 1] != 0)
{
sheet.Cells[i, 7].SetValue(ivaTras[mesini - 1]);
sheet.Cells[i, 7].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (ivaAcr[mesini - 1] != 0)
{
sheet.Cells[i, 8].SetValue(ivaAcr[mesini - 1]);
sheet.Cells[i, 8].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (ivaRet[mesini - 1] != 0)
{
sheet.Cells[i, 9].SetValue(ivaRet[mesini - 1]);
sheet.Cells[i, 9].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
sheet.Cells[i, 10].Formula = string.Format("=h{0}-i{0}-j{0}", i + 1);
sheet.Cells[i++, 10].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
if (mesini > 1)
{
sheet.Rows[i].FillColor = Color.FromName("BurlyWood");
//Beige"); // AntiqueWhite"); Bisque BlanchedAlmond
if (totAcm[mesini - 1] != 0)
{
sheet.Cells[i, 2].SetValue(totAcm[mesini - 1]);
sheet.Cells[i, 2].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
if (totAcmDdc[mesini - 1] != 0)
{
sheet.Cells[i, 3].SetValue(totAcmDdc[mesini - 1]);
sheet.Cells[i, 3].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
sheet.Cells[i, 4].Formula = string.Format("=c{0}-d{0}", i + 1);
sheet.Cells[i, 4].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
if (retAcm[mesini - 1] != 0)
{
sheet.Cells[i, 5].SetValue(retAcm[mesini - 1]);
sheet.Cells[i++, 5].NumberFormat = "$#,##0.00;[Red]$#,##0.00";
}
else
{
i++;
}
}
}
book.SaveDocument(string.Format("Contable.xls"));
}
/*
* Workbook book = new Workbook();
* var sheet = book.Worksheets.ActiveWorksheet;
* sheet.Cells[0, 0].Value = "Carlos Javier Lopez Cruz";
* sheet.Cells[1, 0].Value = "LOCC670416JI8";
* // sheet.Cells[1, 1].Value = "litros67";
* int ano = DateTime.Today.Month == 1 ? DateTime.Today.Year-1 : DateTime.Today.Year;
* // DateTime.Today.Year;
*
* sheet.Cells["A4"].Value = string.Format("Determinación del ISR provisional del Ejercicio {0}.",
* ano);
*
* sheet.Cells[5, 1].Value = "ENERO";
* sheet.Cells[5, 4].Value = "FEBRERO";
* sheet.Cells[5, 7].Value = "MARZO";
* sheet.Cells[5, 10].Value = "ABRIL";
* sheet.Cells[5, 13].Value = "MAYO";
* sheet.Cells[5, 16].Value = "JUNIO";
* sheet.Cells[5, 19].Value = "JULIO";
* sheet.Cells[5, 22].Value = "AGOSTO";
* sheet.Cells[5, 25].Value = "SEPTIEMBRE";
* sheet.Cells[5, 28].Value = "OCTUBRE";
* sheet.Cells[5, 31].Value = "NOVIEMBRE";
* sheet.Cells[5, 34].Value = "DICIEMBRE";
*
*
* XPObjectSpace objectSpace = (XPObjectSpace)View.ObjectSpace;
* Empresa emp = objectSpace.FindObject<Empresa>(null);
*
* CriteriaOperator[] operands = new CriteriaOperator[2];
* decimal[] total = new decimal[12];
* decimal[] totAcm = new decimal[12];
* decimal[] compr = new decimal[12];
* decimal[] reten = new decimal[12];
* decimal[] retAcm = new decimal[12];
* decimal[] ivaTras = new decimal[12];
* decimal[] ivaRet = new decimal[12];
* decimal[] ivaAcr = new decimal[12];
*
* operands[1] = new BinaryOperator("Status", DocumentoStatus.Cancelado, BinaryOperatorType.NotEqual);
*
* sheet.Cells["A1"].Value = emp.Compania.Nombre;
* sheet.Cells["B1"].Value = emp.Compania.Rfc;
*
* for (int mesini = 1; mesini < 13; mesini++)
* {
* DateTime mFechaIni = Fecha.FechaInicial(mesini, ano);
* DateTime mFechaFin = Fecha.FechaFinal(mesini, ano);
*
* operands[0] = GroupOperator.And(new BinaryOperator("FechaDoc", mFechaIni, BinaryOperatorType.GreaterOrEqual),
* new BinaryOperator("FechaDoc", mFechaFin, BinaryOperatorType.LessOrEqual));
*
* IList arr = objectSpace.CreateCollection(typeof(DocumentoSalida), new GroupOperator(operands), null);
*
* total[mesini - 1] = 0;
* totAcm[mesini - 1] = 0;
* reten[mesini - 1] = 0;
* retAcm[mesini - 1] = 0;
* ivaTras[mesini - 1] = 0;
* ivaRet[mesini - 1] = 0;
* ivaAcr[mesini - 1] = 0;
* if (arr.Count > 0)
* {
* foreach (DocumentoSalida doc in arr)
* {
* total[mesini - 1] += doc.SubTotal;
* reten[mesini - 1] += doc.RetenISR;
* ivaTras[mesini - 1] += doc.Impuesto04;
* ivaRet[mesini - 1] += doc.RetenIVA;
* }
*
* if ((mesini - 1) > 0)
* {
* totAcm[mesini - 1] += totAcm[mesini - 2] + total[mesini - 2];
* retAcm[mesini - 1] += retAcm[mesini - 2] + reten[mesini - 2];
* }
* }
* }
*
* sheet.Cells[6, 0].ColumnWidth = 470;
* sheet.Cells["A7"].Value = "Ingresos Acumulados:";
* sheet.Cells[6, 1].Value = 0;
* sheet.Cells[6, 4].Value = total[0].ToString("n2");
* sheet.Cells[6, 7].Value = (total[0] + total[1]).ToString("n2");
* sheet.Cells[6, 10].Value = (total[0] + total[1] + total[2]).ToString("n2");
* sheet.Cells[6, 13].Value = (total[0] + total[1] + total[2]
+ total[3]).ToString("n2");
+ sheet.Cells[6, 16].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4]).ToString("n2");
+ sheet.Cells[6, 19].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5]).ToString("n2");
+ sheet.Cells[6, 22].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5] + total[6]).ToString("n2");
+ sheet.Cells[6, 25].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5] + total[6] + total[7]).ToString("n2");
+ sheet.Cells[6, 28].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5] + total[6] + total[7]
+ total[8]).ToString("n2");
+ sheet.Cells[6, 31].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5] + total[6] + total[7]
+ total[8] + total[9]).ToString("n2");
+ sheet.Cells[6, 34].Value = (total[0] + total[1] + total[2]
+ total[3] + total[4] + total[5] + total[6] + total[7]
+ total[8] + total[9] + total[10]).ToString("n2");
+
+ sheet.Cells["A8"].Value = "Ingresos:";
+ sheet.Cells[7, 1].Value = total[0].ToString("n2");
+ sheet.Cells[7, 4].Value = total[1].ToString("n2");
+ sheet.Cells[7, 7].Value = total[2].ToString("n2");
+ sheet.Cells[7, 10].Value = total[3].ToString("n2");
+ sheet.Cells[7, 13].Value = total[4].ToString("n2");
+ sheet.Cells[7, 16].Value = total[5].ToString("n2");
+ sheet.Cells[7, 19].Value = total[6].ToString("n2");
+ sheet.Cells[7, 22].Value = total[7].ToString("n2");
+ sheet.Cells[7, 25].Value = total[8].ToString("n2");
+ sheet.Cells[7, 28].Value = total[9].ToString("n2");
+ sheet.Cells[7, 31].Value = total[10].ToString("n2");
+ sheet.Cells[7, 34].Value = total[11].ToString("n2");
+
+ sheet.Cells["A9"].Value = "Total de Ingresos:";
+ sheet.Cells[8, 2].Value = (total[0] + totAcm[0]).ToString("n2");
+ sheet.Cells[8, 5].Value = (total[1] + totAcm[1]).ToString("n2");
+ sheet.Cells[8, 8].Value = (total[2] + totAcm[2]).ToString("n2");
+ sheet.Cells[8, 11].Value = (total[3] + totAcm[3]).ToString("n2");
+ sheet.Cells[8, 14].Value = (total[4] + totAcm[4]).ToString("n2");
+ sheet.Cells[8, 17].Value = (total[5] + totAcm[5]).ToString("n2");
+ sheet.Cells[8, 20].Value = (total[6] + totAcm[6]).ToString("n2");
+ sheet.Cells[8, 23].Value = (total[7] + totAcm[7]).ToString("n2");
+ sheet.Cells[8, 26].Value = (total[8] + totAcm[8]).ToString("n2");
+ sheet.Cells[8, 29].Value = (total[9] + totAcm[9]).ToString("n2");
+ sheet.Cells[8, 32].Value = (total[10] + totAcm[10]).ToString("n2");
+ sheet.Cells[8, 35].Value = (total[11] + totAcm[11]).ToString("n2");
+
+
+
+ decimal[] totalDdc = new decimal[12];
+ decimal[] totAcmDdc = new decimal[12];
+ for (int mesini = 1; mesini < 13; mesini++)
+ {
+ DateTime mFechaIni = Fecha.FechaInicial(mesini, ano);
+ DateTime mFechaFin = Fecha.FechaFinal(mesini, ano);
+
+ operands[0] = GroupOperator.And(new BinaryOperator("FechaDoc", mFechaIni, BinaryOperatorType.GreaterOrEqual),
+ new BinaryOperator("FechaDoc", mFechaFin, BinaryOperatorType.LessOrEqual));
+
+ IList arr = objectSpace.CreateCollection(typeof(Recepcion), new GroupOperator(operands), null);
+
+ totalDdc[mesini - 1] = 0;
+ totAcmDdc[mesini - 1] = 0;
+ ivaAcr[mesini - 1] = 0;
+ if (arr.Count > 0)
+ {
+ foreach (Recepcion doc in arr)
+ {
+ totalDdc[mesini - 1] += doc.SubTotal;
+ ivaAcr[mesini - 1] += doc.Impuesto04;
+ }
+
+ if ((mesini - 1) > 0)
+ totAcmDdc[mesini - 1] += totAcmDdc[mesini - 2] + totalDdc[mesini - 2];
+ }
+ }
+
+ sheet.Cells[9, 0].Value = "Deducciones Acumuladas:";
+ sheet.Cells[9, 1].Value = totAcmDdc[0].ToString("n2");
+ sheet.Cells[9, 4].Value = totAcmDdc[1].ToString("n2");
+ sheet.Cells[9, 7].Value = totAcmDdc[2].ToString("n2");
+ sheet.Cells[9, 10].Value = totAcmDdc[3].ToString("n2");
+ sheet.Cells[9, 13].Value = totAcmDdc[4].ToString("n2");
+ sheet.Cells[9, 16].Value = totAcmDdc[5].ToString("n2");
+ sheet.Cells[9, 19].Value = totAcmDdc[6].ToString("n2");
+ sheet.Cells[9, 22].Value = totAcmDdc[7].ToString("n2");
+ sheet.Cells[9, 25].Value = totAcmDdc[8].ToString("n2");
+ sheet.Cells[9, 28].Value = totAcmDdc[9].ToString("n2");
+ sheet.Cells[9, 31].Value = totAcmDdc[10].ToString("n2");
+ sheet.Cells[9, 34].Value = totAcmDdc[11].ToString("n2");
+
+ int ren = 10;
+ sheet.Cells[ren, 0].Value = "Deducciones:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1+3*i].Value = totalDdc[i].ToString("n2");
+
+ ren = 11;
+ sheet.Cells[ren, 0].Value = "Total de Deducciones:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 2+3*i].Value = (totalDdc[i] + totAcmDdc[i]).ToString("n2");
+
+ ren++;
+ sheet.Cells[++ren, 0].Value = "Base ISR:";
+ for (int i = 0; i < 12; i++)
+ {
+ sheet[ren, 2+3*i].Value = (total[i] + totAcm[i]- (totalDdc[i] + totAcmDdc[i])) > 0
+ ? (total[i] + totAcm[i] - (totalDdc[i] + totAcmDdc[i])).ToString("n2") : "-";
+ }
+
+ sheet.Cells[++ren, 0].Value = "Límite Inferior:";
+ sheet.Cells[++ren, 0].Value = "Excedente L.I.:";
+ sheet.Cells[++ren, 0].Value = "% Marginal:";
+ sheet.Cells[++ren, 0].Value = "Impuesto Marginal:";
+ sheet.Cells[++ren, 0].Value = "Cuotra fija:";
+ sheet.Cells[++ren, 0].Value = "ISR Causado:";
+
+ ren++;
+ ren++;
+ sheet.Cells[ren, 0].Value = "Retenciones Acumuladas:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1+3*i].Value = retAcm[i].ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "Retención ISR:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = reten[i].ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "Total de Retenciones:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 2 + 3 * i].Value = (reten[i] + retAcm[i]).ToString("n2");
+
+ ren++;
+ sheet.Cells[++ren, 0].Value = "Determinacion del Impuesto al Valor Agregado Mensual del Ejercicio 2014";
+
+ ren++;
+ ren++;
+ sheet.Cells[ren, 0].Value = "IVA trasladado:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = ivaTras[i].ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "Retención IVA:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = ivaRet[i].ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "IVA Acreditable:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = ivaAcr[i].ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "Saldo a favor de IVA:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = ivaTras[i] - ivaRet[i]-ivaAcr[i] > 0 ? "0" :
+ Math.Round(ivaAcr[i] + ivaRet[i] - ivaTras[i]).ToString("n2");
+
+ ren++;
+ sheet.Cells[ren, 0].Value = "Saldo a pagar de IVA:";
+ for (int i = 0; i < 12; i++)
+ sheet.Cells[ren, 1 + 3 * i].Value = ivaTras[i] - ivaRet[i] - ivaAcr[i] > 0 ?
+ Math.Round(ivaTras[i] - ivaRet[i] - ivaAcr[i]).ToString("n2") : "0";
+
+ book.SaveDocument(string.Format("Contable.xls"));*/
}
static void AddCellBorders(Workbook workbook)
{
#region #CellBorders
Worksheet worksheet = workbook.Worksheets[0];
// Specify borders for the "B2" cell.
Cell cellB2 = worksheet.Cells["B2"];
Borders cellB2Borders = cellB2.Borders;
cellB2Borders.LeftBorder.LineStyle = BorderLineStyle.MediumDashDot;
cellB2Borders.LeftBorder.Color = Color.Pink;
cellB2Borders.TopBorder.LineStyle = BorderLineStyle.MediumDashDotDot;
cellB2Borders.TopBorder.Color = Color.HotPink;
cellB2Borders.RightBorder.LineStyle = BorderLineStyle.MediumDashed;
cellB2Borders.RightBorder.Color = Color.DeepPink;
cellB2Borders.BottomBorder.LineStyle = BorderLineStyle.Medium;
cellB2Borders.BottomBorder.Color = Color.Red;
cellB2Borders.DiagonalBorderType = DiagonalBorderType.Up;
cellB2Borders.DiagonalBorderLineStyle = BorderLineStyle.Thick;
cellB2Borders.DiagonalBorderColor = Color.Red;
// Specify diagonal borders for the "C4" cell.
Cell cellC4 = worksheet.Cells["C4"];
Borders cellC4Borders = cellC4.Borders;
cellC4Borders.SetDiagonalBorders(Color.Orange, BorderLineStyle.Double, DiagonalBorderType.UpAndDown);
// Specify outside borders for the "D6" cell.
Cell cellD6 = worksheet.Cells["D6"];
cellD6.Borders.SetOutsideBorders(Color.Gold, BorderLineStyle.Double);
#endregion #CellBorders
#region #CellRangeBorders
// Specify all borders for the "B8:F13" cell range.
CellRange range1 = worksheet.Range["B8:F13"];
range1.Borders.SetAllBorders(Color.Green, BorderLineStyle.Double);
// Specify inside and outside borders for the "C15:F18" cell range.
CellRange range2 = worksheet.Range["C15:F18"];
range2.SetInsideBorders(Color.SkyBlue, BorderLineStyle.MediumDashed);
range2.Borders.SetOutsideBorders(Color.DeepSkyBlue, BorderLineStyle.Medium);
// Specify horizontal and vertical borders for the "D21:F23" cell range.
CellRange range3 = worksheet.Range["D21:F23"];
Formatting range3Formatting = range3.BeginUpdateFormatting();
Borders range3Borders = range3Formatting.Borders;
range3Borders.InsideHorizontalBorders.LineStyle = BorderLineStyle.MediumDashDot;
range3Borders.InsideHorizontalBorders.Color = Color.DarkBlue;
range3Borders.InsideVerticalBorders.LineStyle = BorderLineStyle.MediumDashDotDot;
range3Borders.InsideVerticalBorders.Color = Color.Blue;
range3.EndUpdateFormatting(range3Formatting);
// Specify borders for the "E25:F26" cell range.
CellRange range4 = worksheet.Range["E25:F26"];
Formatting range4Formatting = range4.BeginUpdateFormatting();
Borders range4Borders = range4Formatting.Borders;
range4Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thick);
range4Borders.LeftBorder.Color = Color.Violet;
range4Borders.TopBorder.Color = Color.Violet;
range4Borders.RightBorder.Color = Color.DarkViolet;
range4Borders.BottomBorder.Color = Color.DarkViolet;
range4Borders.DiagonalBorderType = DiagonalBorderType.UpAndDown;
range4Borders.DiagonalBorderLineStyle = BorderLineStyle.MediumDashed;
range4Borders.DiagonalBorderColor = Color.BlueViolet;
range4.EndUpdateFormatting(range4Formatting);
#endregion #CellRangeBorders
}
public void showNgayToanTruong(IWorkbook wb, DateTime dt)
{
if (wb.Worksheets == null || wb.Worksheets.Count == 0)
{
return;
}
if (dt.DayOfWeek.ToString() == "Sunday")
{
DialogResult dialogResult = MessageBox.Show("Chủ nhật không có lịch học", "Có lỗi", MessageBoxButtons.OK);
return;
}
int day = dt.Day, month = dt.Month;
string dd = (day < 10) ? "0" + day : day.ToString(), mm = (month < 10) ? "0" + month : month.ToString();
string title = mm + "/" + dt.Year.ToString();
string ngay = $"Ngày {dd} tháng {mm} năm {dt.Year}";
fname = $"TKB_Tat_Ca_Lop_{dd}-{mm}-{dt.Year}.xlsx";
int col = 0, row = 0;
int lop_col = 0, lop_row = 0;
using (FileStream stream = new FileStream("TKB_Ngay_Tat_Ca_Lop.xlsx", FileMode.Open))
{
TKBLopViewer.LoadDocument(stream, DocumentFormat.Xlsx);
wb_Ngay = TKBLopViewer.Document;
Worksheet ws_ngay = wb_Ngay.Worksheets[0];
//Duyệt từng sheet TKB HK của mỗi lớp
int l = wb.Worksheets.Count;
for (int i = 0; i < l; i++)
{
//Kiếm ngày
//Tìm ô có dạng mm/yyyy trong TKB HK của lớp đó
Worksheet ws_HK = wb.Worksheets[i];
string chk = ws_HK.Name;
Console.WriteLine(chk);
string search = searchDateCell(ws_HK, title, day);
if (search == null || search == "")
{
continue;
}
Cell c = ws_HK.Cells[search];
//Dịch xuống 1 ô để lấy những tiết đầu của ngày hôm đó
col = c.ColumnIndex;
row = c.RowIndex + 1;
//Tìm ô tên lớp đó trong TKB ngày toàn trường
search = searchCellByValue(ws_ngay, $"Lớp: {ws_HK.Name}");
if (search == "")
{
break;
}
string searchQuanSo = searchCellByValue(ws_HK, "QUÂN SỐ:");
if (searchQuanSo != "")
{
int cqs = ws_HK.Cells[searchQuanSo].ColumnIndex + 1;
int rqs = ws_HK.Cells[searchQuanSo].RowIndex;
while (true)
{
if (ws_HK[rqs, cqs].Value.ToString() != null && ws_HK[rqs, cqs].Value.ToString() != "")
{
searchQuanSo = ws_HK[rqs, cqs].Value.ToString();
break;
}
cqs++;
if (cqs > 33)
{
searchQuanSo = "";
break;
}
}
}
c = ws_ngay.Cells[search];
ws_ngay[c.RowIndex + 1, c.ColumnIndex].Value = "Tổng số học viên: " + searchQuanSo; //Gán quân số
lop_col = c.ColumnIndex + 1; lop_row = c.RowIndex + 5;
//COPY TKB
//Phục vụ check merged
string caption_hk = ws_HK.Columns[col].GetReferenceA1().Split(new Char[] { '$', ':' })[1];
string caption_lop = ws_ngay.Columns[lop_col].GetReferenceA1().Split(new Char[] { '$', ':' })[1];
for (int j = 0; j < 5; j++)
{
ws_ngay.Cells[lop_row++, lop_col].SetRichText(ws_HK.Cells[row++, col].GetRichText());
//Kiểm tra merge
for (int z = 1; z < 5; z++)
{
string r = caption_hk + row + ':' + caption_hk + (row + z);
CellRange cell_range = ws_HK[r];
if (cell_range.IsMerged)
{
string r_t = caption_lop + lop_row + ":" + caption_lop + (lop_row + z);
ws_ngay.MergeCells(ws_ngay[r_t]);
}
}
}
string lbl = ws_ngay.Columns[lop_col].GetReferenceA1().Split(new Char[] { '$', ':' })[1];
string select = $"{lbl}{lop_row - 4}:{lbl}{lop_row}";
CellRange range = ws_ngay.Range[select];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Size = 12;
range.EndUpdateFormatting(rangeFormatting);
}
//Chỉnh ngày
DateTime dt2 = (dt.DayOfWeek.ToString() == "Monday") ? dt.AddDays(-4) : dt.AddDays(-1);
int day2 = dt2.Day, month2 = dt2.Month;
string dd2 = (day2 < 10) ? "0" + day2 : day2.ToString(), mm2 = (month2 < 10) ? "0" + month2 : month2.ToString();
string ngay2 = $"Ngày {dd2} tháng {mm2} năm {dt2.Year}";
IEnumerable <Cell> searchResult = ws_ngay.Search("ngay");
foreach (Cell c in searchResult)
{
if (c.RowIndex == 1 || c.RowIndex == 20)
{
ws_ngay[c.GetReferenceA1()].Value = ngay;
}
else
{
ws_ngay[c.GetReferenceA1()].Value = ngay2;
}
}
}
}
public void showNgayTheoLop(Worksheet ws_HK, DateTime dt)
{
if (dt.DayOfWeek.ToString() == "Sunday")
{
DialogResult dialogResult = MessageBox.Show("Chủ nhật không có lịch học", "Có lỗi", MessageBoxButtons.OK);
return;
}
int day = dt.Day, month = dt.Month;
string dd = (day < 10) ? "0" + day : day.ToString(), mm = (month < 10) ? "0" + month : month.ToString();
string title = mm + "/" + dt.Year.ToString();
string ngay = $"Ngày {dd} tháng {mm} năm {dt.Year}";
fname = $"TKB_Lop_{ws_HK.Name}_{dd}-{mm}-{dt.Year}.xlsx";
//d = day;
//Tìm ô có dạng mm/yyyy
string search = searchDateCell(ws_HK, title, day);
if (search == "")
{
return;
}
Worksheet ws_ngay = null;
using (FileStream stream = new FileStream("TKB_Ngay_Theo_Lop.xlsx", FileMode.Open))
{
//Nạp file excel Ngày
TKBLopViewer.LoadDocument(stream, DocumentFormat.Xlsx);
wb_Ngay = TKBLopViewer.Document;
ws_ngay = wb_Ngay.Worksheets[0];
ws_ngay["A2"].Value = $"Lớp {ws_HK.Name}";
string qs = ws_HK["AC5"].Value != null ? ws_HK["AC5"].Value.ToString() : "";
ws_ngay["A3"].Value = $"Tổng số học viên: {qs}";
ws_ngay["E3"].Value = $"Ngày {dd} tháng {mm} năm {dt.Year}";
DateTime dt2 = (dt.DayOfWeek.ToString() == "Monday") ? dt.AddDays(-4) : dt.AddDays(-1);
int day2 = dt2.Day, month2 = dt2.Month;
string dd2 = (day2 < 10) ? "0" + day2 : day2.ToString(), mm2 = (month2 < 10) ? "0" + month2 : month2.ToString();
string ngay2 = $"Ngày {dd2} tháng {mm2} năm {dt2.Year}";
ws_ngay["H13"].Value = $"Ngày {dd2} tháng {mm2} năm {dt2.Year}";
day++;
Cell c = ws_HK.Cells[search];
string col = ws_HK.Columns[c.ColumnIndex].GetReferenceA1();
string[] arr = col.Split(new Char[] { '$', ':' });
col = arr[1];
int row = c.RowIndex + 2;
for (int i = 7; i < 12; i++)
{
ws_ngay["B" + i].SetRichText(ws_HK[col + (row)].GetRichText());
//Phục vụ check merged
string caption_hk = ws_HK.Columns[col].GetReferenceA1().Split(new Char[] { '$', ':' })[1];
//string caption_lop = ws_ngay.Columns[col].GetReferenceA1().Split(new Char[] { '$', ':' })[1];
for (int j = 0; j < 5; j++)
{
//ws_ngay.Cells[lop_row++, lop_col].SetRichText(ws_HK.Cells[row++, col].GetRichText());
//Kiểm tra merge
for (int z = 1; z < 5; z++)
{
string r = caption_hk + row + ':' + caption_hk + (row + z);
CellRange cell_range = ws_HK[r];
if (cell_range.IsMerged)
{
string r_d = "B" + i + ":" + "B" + (i + z);
ws_ngay.MergeCells(ws_ngay[r_d]);
}
}
}
row++;
}
CellRange range = ws_ngay.Range["B7:B11"];
Formatting rangeFormatting = range.BeginUpdateFormatting();
rangeFormatting.Font.Size = 12;
range.EndUpdateFormatting(rangeFormatting);
}
}
