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); } }