void SetTitleCellFormat(XlsFile xls) { TFlxFormat cellFormat = xls.GetCellVisibleFormatDef(1, 1); cellFormat.Font.Size20 = 240; cellFormat.Font.Color = TExcelColor.FromTheme(TThemeColor.Accent1); cellFormat.Font.Style = TFlxFontStyles.Bold; cellFormat.FillPattern.Pattern = TFlxPatternStyle.Solid; cellFormat.FillPattern.FgColor = TExcelColor.FromTheme(TThemeColor.Accent3, 0.6); cellFormat.VAlignment = TVFlxAlignment.center; cellFormat.HAlignment = THFlxAlignment.center; xls.SetCellFormat(1, 1, xls.AddFormat(cellFormat)); }
/// <summary> /// 给单元格添加标注 /// </summary> /// <param name="row">单元格所在行</param> /// <param name="col">单元格所在列</param> /// <param name="comment">标注</param> public void AddCommentToCell(int row, int col, string commentTitle, string commentDetail) { string comment = string.Format("{0}\n{1}\n", commentTitle, commentDetail); // 设置单元格背景色 TFlxFormat fmt = xls.GetCellVisibleFormatDef(row, col); fmt.FillPattern.Pattern = TFlxPatternStyle.Solid; fmt.FillPattern.FgColor = Color.FromArgb(0xFF9999); xls.SetCellFormat(row, col, xls.AddFormat(fmt)); // 设置批注 TRTFRun[] Runs = new TRTFRun[3]; Runs[0].FirstChar = 0; TFlxFont fnt = xls.GetDefaultFont; fnt.Size20 = 180; fnt.Color = TExcelColor.Automatic; fnt.Style = TFlxFontStyles.Bold; fnt.Family = 3; fnt.CharSet = 134; fnt.Scheme = TFontScheme.None; Runs[0].FontIndex = xls.AddFont(fnt); Runs[1].FirstChar = 7; fnt = xls.GetDefaultFont; fnt.Size20 = 180; fnt.Color = TExcelColor.Automatic; fnt.Family = 3; fnt.CharSet = 134; fnt.Scheme = TFontScheme.None; Runs[1].FontIndex = xls.AddFont(fnt); Runs[2].FirstChar = 13; fnt = xls.GetDefaultFont; Runs[2].FontIndex = xls.AddFont(fnt); xls.SetComment(row, col, new TRichString(comment, Runs, xls)); }
private int ColorOnly(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); TFlxFillPattern tfpattern3 = new TFlxFillPattern(); tfpattern3.BgColor = Colors.LightGray; tf4.FillPattern = tfpattern3; return xls.AddFormat(tf4); }
private int NoColorAndLeftBorder(XlsFile xls, int row, int col) { TFlxFormat tf3 = xls.GetCellVisibleFormatDef(row, col); tf3.HAlignment = THFlxAlignment.left; TFlxBorders tfBorderF = new TFlxBorders(); TFlxOneBorder oborder = new TFlxOneBorder(); oborder.Style = TFlxBorderStyle.Thin; tfBorderF.Left = oborder; tf3.Borders = tfBorderF; return xls.AddFormat(tf3); }
private int ColorAndRightBorder(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); TFlxFont tfont = new TFlxFont(); tfont.Style = TFlxFontStyles.Bold; tf4.Font = tfont; TFlxBorders tfBorderF1 = new TFlxBorders(); TFlxOneBorder oborder1 = new TFlxOneBorder(); oborder1.Style = TFlxBorderStyle.Thin; tfBorderF1.Right = oborder1; tf4.Borders = tfBorderF1; TFlxFillPattern tfpattern3 = new TFlxFillPattern(); tfpattern3.BgColor = Colors.LightGray; tf4.FillPattern = tfpattern3; return xls.AddFormat(tf4); }
private int ColorBold(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); TFlxFont tfont = new TFlxFont(); tfont.Style = TFlxFontStyles.Bold; tf4.Font = tfont; TFlxFillPattern tfpattern3 = new TFlxFillPattern(); tfpattern3.BgColor = Colors.LightGray; tf4.FillPattern = tfpattern3; return xls.AddFormat(tf4); }
public static XlsFile TaoTieuDe_A3(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotTrang1, int SoCotTrangLonHon1, Boolean NghiepVu = false) { xls.NewFile(1); //Create a new Excel file with 1 sheet. //Set the names of the sheets xls.ActiveSheet = 1; xls.SheetName = "Sheet1"; xls.ActiveSheet = 1; //Set the sheet we are working in. //Global Workbook Options xls.OptionsAutoCompressPictures = true; #region //Styles. TFlxFormat StyleFmt; StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2), StyleFmt); #endregion #region //Named Ranges TXlsNamedRange Range; string RangeName; RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles); Range = new TXlsNamedRange(RangeName, 1, 32, "='Sheet1'!$A:$G,'Sheet1'!$3:$5"); //You could also use: Range = new TXlsNamedRange(RangeName, 1, 0, 0, 0, 0, 0, 32); xls.SetNamedRange(Range); #endregion #region //Printer Settings THeaderAndFooter HeadersAndFooters = new THeaderAndFooter(); HeadersAndFooters.AlignMargins = true; HeadersAndFooters.ScaleWithDoc = true; HeadersAndFooters.DiffFirstPage = true; HeadersAndFooters.DiffEvenPages = false; HeadersAndFooters.DefaultFooter = "&RTrang: &P/&N"; if (NghiepVu == true) { HeadersAndFooters.FirstHeader = "&L&\"Times New Roman,Bold\"<#QuanKhu>\n<#Phong>\n&C&\"Times New Roman,Bold\"TỔNG HỢP QUYẾT TOÁN <#sLNS> - PHẦN <#TruongTien>\n <#LoaiThangQuy> <#Thang> năm <#Nam>"; } else { HeadersAndFooters.FirstHeader = "&L&\"Times New Roman,Bold\"<#QuanKhu>\n<#Phong>\n&C&\"Times New Roman,Bold\"TỔNG HỢP QUYẾT TOÁN LƯƠNG,PHỤ CẤP TIỀN ĂN\n Tháng <#Thang> năm <#Nam>"; } HeadersAndFooters.FirstFooter = "&RTrang: &P/&N"; HeadersAndFooters.EvenHeader = ""; HeadersAndFooters.EvenFooter = ""; xls.SetPageHeaderAndFooter(HeadersAndFooters); //You can set the margins in 2 ways, the one commented here or the one below: // TXlsMargins PrintMargins = xls.GetPrintMargins(); // PrintMargins.Left = 0.196850393700787; // PrintMargins.Top = 0.590551181102362; // PrintMargins.Right = 0.196850393700787; // PrintMargins.Bottom = 0.748031496062992; // PrintMargins.Header = 0.31496062992126; // PrintMargins.Footer = 0.31496062992126; // xls.SetPrintMargins(PrintMargins); xls.SetPrintMargins(new TXlsMargins(0.393700787401575, 0.590551181102362, 0.393700787401575, 0.748031496062992, 0.31496062992126, 0.31496062992126)); xls.PrintXResolution = 600; xls.PrintYResolution = 600; xls.PrintOptions = TPrintOptions.None; xls.PrintPaperSize = TPaperSize.A3; //Printer Driver Settings are a blob of data specific to a printer //This code is commented by default because normally you do not want to hard code the printer settings of an specific printer. // byte[] PrinterData = { // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x00, 0x06, 0xDC, 0x00, 0x00, 0x00, 0x03, 0xFF, 0x00, 0x00, 0x02, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x64, 0x00, 0x01, 0x00, 0x0F, 0x00, 0x58, 0x02, 0x02, 0x00, 0x01, 0x00, 0x58, 0x02, // 0x02, 0x00, 0x00, 0x00, 0x4C, 0x00, 0x65, 0x00, 0x74, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x01, 0x00, // 0x00, 0x00, 0xFF, 0xFF, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // }; // TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData); // xls.SetPrinterDriverSettings(PrinterDriverSettings); #endregion int TongSoHang = dt.Rows.Count; int _TuCot = TuCot; int TongSoCot = 0; int SoTrang = 1; if ((DenCotCua_DT - TuCotCua_DT) <= SoCotTrang1) { int SoCotDu = ((DenCotCua_DT - TuCotCua_DT)) % SoCotTrang1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrang1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; } else { int SoCotDu = (DenCotCua_DT - TuCotCua_DT - SoCotTrang1) % SoCotTrangLonHon1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrangLonHon1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; SoTrang = 1 + (TongSoCot - SoCotTrang1) / SoCotTrangLonHon1; } #region //Set up rows and columns xls.DefaultColWidth = 2340; xls.SetColWidth(1, 950); //(2.82 + 0.75) * 256 TFlxFormat ColFmt; ColFmt = xls.GetFormat(xls.GetColFormat(1)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(1, xls.AddFormat(ColFmt)); xls.SetColWidth(2, 950); //(2.82 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(2)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(2, xls.AddFormat(ColFmt)); xls.SetColWidth(3, 1000); //(2.96 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(3)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(3, xls.AddFormat(ColFmt)); xls.SetColWidth(4, 1000); //(2.82 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(4)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(4, xls.AddFormat(ColFmt)); xls.SetColWidth(5, 900); //(2.82 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(5)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(5, xls.AddFormat(ColFmt)); xls.SetColWidth(6, 800); //(2.39 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(6)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(6, xls.AddFormat(ColFmt)); xls.SetColWidth(7, 8000); //(27.68 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(7)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(7, xls.AddFormat(ColFmt)); xls.SetColWidth(8, 3400); //(12.39 + 0.75) * 256 for (int i = 0; i < TongSoCot; i++) { xls.SetColWidth(_TuCot + i, 3400); } xls.SetRowHeight(4, 600); xls.SetRowHeight(5, 600); xls.DefaultRowHeight = 300; #endregion #region//Merged Cells xls.MergeCells(4, 1, 5, 6); xls.MergeCells(4, 7, 5, 7); xls.MergeCells(4, 8, 5, 8); _TuCot = TuCot; if (SoTrang == 1) { xls.MergeCells(4, _TuCot, 4, _TuCot + SoCotTrang1 - 1); } else { xls.MergeCells(4, _TuCot, 4, _TuCot + SoCotTrang1 - 1); _TuCot = _TuCot + SoCotTrang1; for (int i = 1; i < SoTrang; i++) { xls.MergeCells(4, _TuCot, 4, _TuCot + SoCotTrangLonHon1 - 1); _TuCot = _TuCot + SoCotTrangLonHon1; } } #endregion #region //Set the cell values #region set tieu de cot tinh TFlxFormat fmt; fmt = xls.GetCellVisibleFormatDef(1, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 1, xls.AddFormat(fmt)); xls.SetCellValue(1, 1, "<#auto page breaks>"); fmt = xls.GetCellVisibleFormatDef(4, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 1, xls.AddFormat(fmt)); xls.SetCellValue(4, 1, "L-K-M-TM-TTM-NG"); fmt = xls.GetCellVisibleFormatDef(4, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 7, xls.AddFormat(fmt)); xls.SetCellValue(4, 7, "Nội dung"); fmt = xls.GetCellVisibleFormatDef(4, 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 8, xls.AddFormat(fmt)); xls.SetCellValue(4, 8, "Tổng cộng"); fmt = xls.GetCellVisibleFormatDef(5, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 1, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 7, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(5, 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(5, 8, xls.AddFormat(fmt)); #endregion #region set tieu de cot dong #region set hang trongdo va donvitinh _TuCot = TuCot; //set trang 1 fmt = xls.GetCellVisibleFormatDef(3, _TuCot + 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Italic; fmt.Font.Family = 1; xls.SetCellFormat(3, _TuCot + 8, xls.AddFormat(fmt)); xls.SetCellValue(3, _TuCot + 8, "Đơn vị tính: đồng Tờ số 1"); fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, _TuCot, xls.AddFormat(fmt)); xls.SetCellValue(4, _TuCot, "Trong đó"); for (int j = _TuCot + 1; j <= _TuCot + SoCotTrang1 - 1; j++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, j, xls.AddFormat(fmt)); } _TuCot = _TuCot + SoCotTrang1; //set cac trang con lai for (int i = 1; i < SoTrang; i++) { fmt = xls.GetCellVisibleFormatDef(3, _TuCot + 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Italic; fmt.Font.Family = 1; xls.SetCellFormat(3, _TuCot + 9, xls.AddFormat(fmt)); xls.SetCellValue(3, _TuCot + 9, "Đơn vị tính đồng Tờ số " + Convert.ToInt16(i + 1)); fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, _TuCot, xls.AddFormat(fmt)); xls.SetCellValue(4, _TuCot, "Trong đó"); for (int j = _TuCot + 1; j <= _TuCot + SoCotTrangLonHon1 - 1; j++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, j, xls.AddFormat(fmt)); } _TuCot = _TuCot + SoCotTrangLonHon1; } #endregion #region set cac cot don vi _TuCot = TuCot; String TenCot; int _TuCotCua_DT = TuCotCua_DT; for (int i = 0; i < SoCotTrang1; i++) { fmt = xls.GetCellVisibleFormatDef(5, _TuCot + i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; TenCot = ""; if (DenCotCua_DT > _TuCotCua_DT) { TenCot = "<#TenDV" + i + ">"; } xls.SetCellFormat(5, _TuCot + i, xls.AddFormat(fmt)); xls.SetCellValue(5, _TuCot + i, TenCot); } _TuCotCua_DT = _TuCotCua_DT + SoCotTrang1; _TuCot = _TuCot + SoCotTrang1; for (int i = 0; i < TongSoCot - SoCotTrang1; i++) { fmt = xls.GetCellVisibleFormatDef(5, _TuCot + i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; TenCot = ""; int a = Convert.ToInt16(SoCotTrang1) + i; if (DenCotCua_DT > _TuCotCua_DT) { TenCot = "<#TenDV" + a + ">"; } xls.SetCellFormat(5, _TuCot + i, xls.AddFormat(fmt)); xls.SetCellValue(5, _TuCot + i, TenCot); } #endregion #region ngày tháng năm, chữ ký //ngaythangnam xls.MergeCells(TongSoHang + TuHang + 2, 17, TongSoHang + TuHang + 2, 18); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 17); fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 2, 17, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 2, 17, "<#NgayThangNam>"); //ChuKy fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 7); fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 7, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 7, "<#row height(autofit)><#ThuaLenh1>\n\n<#ChucDanh1>\n\n\n\n\n\n\n\n<#Ten1>"); xls.MergeCells(TongSoHang + TuHang + 3, 8, TongSoHang + TuHang + 3, 9); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 8); fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 8, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 8, "<#row height(autofit)><#ThuaLenh2>\n\n<#ChucDanh2>\n\n\n\n\n\n\n\n<#Ten2>"); xls.MergeCells(TongSoHang + TuHang + 3, 11, TongSoHang + TuHang + 3, 12); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 11); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 11, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 11, "<#row height(autofit)><#ThuaLenh3>\n\n<#ChucDanh3>\n\n\n\n\n\n\n\n<#Ten3>"); xls.MergeCells(TongSoHang + TuHang + 3, 14, TongSoHang + TuHang + 4, 15); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 14); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 14, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 14, "<#row height(autofit)><#ThuaLenh4>\n\n<#ChucDanh4>\n\n\n\n\n\n\n\n<#Ten4>"); xls.MergeCells(TongSoHang + TuHang + 3, 17, TongSoHang + TuHang + 3, 18); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 17); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 17, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 17, "<#row height(autofit)><#ThuaLenh5>\n\n<#ChucDanh5>\n\n\n\n\n\n\n\n<#Ten5>"); for (int i = 1; i < SoTrang; i++) { xls.MergeCells(TongSoHang + TuHang + 2, 17 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 2, 18 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 17 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 2, 17 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 2, 17 + SoCotTrangLonHon1 * i, "<#NgayThangNam>"); xls.MergeCells(TongSoHang + TuHang + 3, 8 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 3, 9 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 8 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 8 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 8 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh2>\n\n<#ChucDanh2>\n\n\n\n\n\n\n\n<#Ten2>"); xls.MergeCells(TongSoHang + TuHang + 3, 8 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 2, 9 + SoCotTrangLonHon1 * i); xls.MergeCells(TongSoHang + TuHang + 3, 11 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 3, 12 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 11 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 11 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 11 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh3>\n\n<#ChucDanh3>\n\n\n\n\n\n\n\n<#Ten3>"); xls.MergeCells(TongSoHang + TuHang + 3, 14 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 3, 15 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 14 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 14 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 14 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh4>\n\n<#ChucDanh4>\n\n\n\n\n\n\n\n<#Ten4>"); xls.MergeCells(TongSoHang + TuHang + 3, 17 + SoCotTrangLonHon1 * i, TongSoHang + TuHang + 3, 18 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 3, 17 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 3, 17 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 3, 17 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh5>\n\n<#ChucDanh5>\n\n\n\n\n\n\n\n<#Ten5>"); } #endregion #endregion //Cell selection and scroll position. xls.SelectCell(11, 7, false); #endregion //Protection TSheetProtectionOptions SheetProtectionOptions; SheetProtectionOptions = new TSheetProtectionOptions(false); SheetProtectionOptions.SelectLockedCells = true; SheetProtectionOptions.SelectUnlockedCells = true; xls.Protection.SetSheetProtection(null, SheetProtectionOptions); return xls; }
private int ColorAndLeftBorder(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); tf4.HAlignment = THFlxAlignment.left; TFlxBorders tfBorderF1 = new TFlxBorders(); TFlxOneBorder oborder1 = new TFlxOneBorder(); oborder1.Style = TFlxBorderStyle.Thin; tfBorderF1.Left = oborder1; tf4.Borders = tfBorderF1; TFlxFillPattern tfpattern3 = new TFlxFillPattern(); tfpattern3.BgColor = Colors.LightGray; tf4.FillPattern = tfpattern3; return xls.AddFormat(tf4); }
private int NoColory(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); return xls.AddFormat(tf4); }
public static void saveAs(DataGridView dgv, string name) { if (dgv.RowCount < 1) { MessageBox.Show("没有可导出的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } SaveFileDialog dialog = new SaveFileDialog(); dialog.RestoreDirectory = true; dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*"; dialog.FileName = DateTime.Now.Date.ToString("yyyyMMdd") + ".xls"; if (dialog.ShowDialog() == DialogResult.OK) { XlsFile xls = new XlsFile(true); xls.NewFile(1); //Create a new Excel file with 1 sheet. xls.ActiveSheet = 1; //Set the sheet we are working in. xls.SheetName = "Sheet1"; //Sheet Options //Styles.默认单元格格式 TFlxFormat StyleFmt; StyleFmt = xls.GetStyle("Normal"); StyleFmt.Font.Name = "宋体"; StyleFmt.Font.Size20 = 240; StyleFmt.Font.CharSet = 134; xls.SetStyle("Normal", StyleFmt); //Printer Settings 默认打印设置 xls.SetPrintMargins(new TXlsMargins(0.7, 0.75, 0.7, 0.75, 0.3, 0.3)); xls.PrintXResolution = 600; xls.PrintYResolution = 600; xls.PrintOptions = TPrintOptions.Orientation; xls.PrintPaperSize = TPaperSize.A4; //Set up rows and columns 默认单元格大小 //11.88为100像素 xls.DefaultColWidth = 2340; xls.DefaultRowHeight = 285; //Set the cell values 设置单元格风格 TFlxFormat fmt; fmt = xls.GetCellVisibleFormatDef(1, 1); fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.ColorIndex = xls.NearestColorIndex(Color.Black); fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.ColorIndex = xls.NearestColorIndex(Color.Black); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.ColorIndex = xls.NearestColorIndex(Color.Black); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.ColorIndex = xls.NearestColorIndex(Color.Black); fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; //设置表格值 加载数据 //字体10号 fmt.Font.Size20 = 200; int k = -1; for (int i = 0; i < dgv.Columns.Count; i++) { k++; object cd = dgv.Columns[i].GetType(); if (dgv.Columns[i].Visible == false || dgv.Columns[i].GetType() == typeof(DataGridViewImageColumn)) { k--; continue; } xls.SetColWidth(k + 1, Convert.ToInt32(0.1188 * dgv.Columns[i].Width * 255)); xls.SetCellFormat(3, k + 1, xls.AddFormat(fmt)); xls.SetCellValue(3, k + 1, dgv.Columns[i].HeaderText); for (int j = 0; j < dgv.Rows.Count; j++) { xls.SetCellFormat(j + 4, k + 1, xls.AddFormat(fmt)); if (dgv.Rows[j].Cells[i].Value.GetType() == typeof(DateTime)) { xls.SetCellValue(j + 4, k + 1, Convert.ToDateTime(dgv.Rows[j].Cells[i].Value).ToString("yyyy/MM/dd HH:mm")); } else { if (dgv.Rows[j].Cells[i].Value.GetType() == typeof(Single)) { xls.SetCellValue(j + 4, k + 1, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells[i].Value), 2)); } else { xls.SetCellValue(j + 4, k + 1, dgv.Rows[j].Cells[i].Value); } } } } //设置表头 //字体12号 fmt.Font.Size20 = 240; //Merged Cells 合并单元格 xls.MergeCells(1, 1, 2, k + 1); xls.SetCellFormat(1, 1, xls.AddFormat(fmt)); xls.SetCellValue(1, 1, name); //Cell selection and scroll position.默认单元格 xls.SelectCell(1, 1, true); //Protection TSheetProtectionOptions SheetProtectionOptions; SheetProtectionOptions = new TSheetProtectionOptions(false); SheetProtectionOptions.SelectLockedCells = true; SheetProtectionOptions.SelectUnlockedCells = true; xls.Protection.SetSheetProtection(null, SheetProtectionOptions); xls.Save(dialog.FileName); try { System.Diagnostics.Process.Start(dialog.FileName); } catch { MessageBox.Show("文件已打开无法覆盖!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } }
/// <summary> /// tạo tiêu để cho báo cáo /// </summary> /// <param name="xls"></param> /// <param name="dt"></param> /// <param name="TuHang"></param> /// <param name="TuCot"></param> /// <param name="TuCotCua_DT"></param> /// <param name="DenCotCua_DT"></param> /// <param name="SoCotTrang1"></param> /// <param name="SoCotTrangLonHon1"></param> /// <returns></returns> public XlsFile TaoTieuDe(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotTrang1, int SoCotTrangLonHon1) { xls.NewFile(1); //Create a new Excel file with 1 sheet. //Set the names of the sheets xls.ActiveSheet = 1; xls.SheetName = "Sheet1"; xls.ActiveSheet = 1; //Set the sheet we are working in. //Global Workbook Options xls.OptionsAutoCompressPictures = true; #region //Styles. //Styles. TFlxFormat StyleFmt; StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "#,##0;-#,##0;;@"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "#,##0;-#,##0;;@"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "#,##0;-#,##0;;@"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "#,##0;-#,##0;;@"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2), StyleFmt); #endregion #region //Named Ranges //Named Ranges TXlsNamedRange Range; string RangeName; RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles); Range = new TXlsNamedRange(RangeName, 1, 32, "='Sheet1'!$A:$B,'Sheet1'!$1:$4"); //You could also use: Range = new TXlsNamedRange(RangeName, 1, 0, 0, 0, 0, 0, 32); xls.SetNamedRange(Range); #endregion #region //Printer Settings THeaderAndFooter HeadersAndFooters = new THeaderAndFooter(); HeadersAndFooters.AlignMargins = true; HeadersAndFooters.ScaleWithDoc = true; HeadersAndFooters.DiffFirstPage = false; HeadersAndFooters.DiffEvenPages = false; HeadersAndFooters.DefaultHeader = "&L&\"Times New Roman,Bold\" <#Cap1>\n <#Cap2>&C&\"Times New Roman,Bold\"TỔNG HỢP CẤP NGÂN SÁCH - <#TruongTien>\n<#Dot>&R&\"Times New Roman,Italic\"\n\n\nĐơn vị tính:1000 đồng Trang:&P/&N "; HeadersAndFooters.DefaultFooter = ""; HeadersAndFooters.FirstHeader = ""; HeadersAndFooters.FirstFooter = ""; HeadersAndFooters.EvenHeader = ""; HeadersAndFooters.EvenFooter = ""; xls.SetPageHeaderAndFooter(HeadersAndFooters); //You can set the margins in 2 ways, the one commented here or the one below: // TXlsMargins PrintMargins = xls.GetPrintMargins(); // PrintMargins.Left = 0.708661417322835; // PrintMargins.Top = 0.393700787401575; // PrintMargins.Right = 0.196850393700787; // PrintMargins.Bottom = 0.590551181102362; // PrintMargins.Header = 0.31496062992126; // PrintMargins.Footer = 0.31496062992126; // xls.SetPrintMargins(PrintMargins); xls.SetPrintMargins(new TXlsMargins(0.708661417322835, 0.393700787401575, 0.196850393700787, 0.590551181102362, 0.31496062992126, 0.31496062992126)); xls.PrintXResolution = 600; xls.PrintYResolution = 600; xls.PrintOptions = TPrintOptions.None; xls.PrintPaperSize = TPaperSize.A4; //Printer Driver Settings are a blob of data specific to a printer //This code is commented by default because normally you do not want to hard code the printer settings of an specific printer. // byte[] PrinterData = { // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x00, 0x06, 0xDC, 0x00, 0x00, 0x00, 0x03, 0x2F, 0x00, 0x00, 0x02, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x64, 0x00, 0x01, 0x00, 0x0F, 0x00, 0x58, 0x02, 0x02, 0x00, 0x01, 0x00, 0x58, 0x02, // 0x03, 0x00, 0x01, 0x00, 0x4C, 0x00, 0x65, 0x00, 0x74, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x01, 0x00, // 0x00, 0x00, 0xFF, 0xFF, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // }; // TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData); // xls.SetPrinterDriverSettings(PrinterDriverSettings); #endregion int TongSoHang = dt.Rows.Count; int _TuCot = TuCot; int TongSoCot = 0; int SoTrang = 1; if ((DenCotCua_DT - TuCotCua_DT) <= SoCotTrang1) { int SoCotDu = ((DenCotCua_DT - TuCotCua_DT)) % SoCotTrang1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrang1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; } else { int SoCotDu = (DenCotCua_DT - TuCotCua_DT - SoCotTrang1) % SoCotTrangLonHon1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrangLonHon1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; SoTrang = 1 + (TongSoCot - SoCotTrang1) / SoCotTrangLonHon1; } #region //Set up rows and columns //Set up rows and columns xls.DefaultColWidth = 2340; xls.SetColWidth(1, 1170); //(3.82 + 0.75) * 256 TFlxFormat ColFmt; ColFmt = xls.GetFormat(xls.GetColFormat(1)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(1, xls.AddFormat(ColFmt)); xls.SetColWidth(2, 4827); //(18.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(2)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(2, xls.AddFormat(ColFmt)); xls.SetColWidth(3, 3657); //(13.54 + 0.75) * 256 for (int i = 0; i < TongSoCot; i++) { xls.SetColWidth(_TuCot + i, 3600); } xls.SetRowHeight(4, 800); xls.DefaultRowHeight = 300; #endregion #region MagerCell #endregion #region //Set the cell values #region set tieu de cot tinh TFlxFormat fmt; fmt = xls.GetCellVisibleFormatDef(4, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 1, xls.AddFormat(fmt)); xls.SetCellValue(4, 1, "STT"); fmt = xls.GetCellVisibleFormatDef(4, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 2, xls.AddFormat(fmt)); xls.SetCellValue(4, 2, "Tên đơn vị"); fmt = xls.GetCellVisibleFormatDef(4, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 3, xls.AddFormat(fmt)); xls.SetCellValue(4, 3, "Tổng cộng"); fmt = xls.GetCellVisibleFormatDef(4, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 4, xls.AddFormat(fmt)); xls.SetCellValue(4, 4, 1); fmt = xls.GetCellVisibleFormatDef(4, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 5, xls.AddFormat(fmt)); xls.SetCellValue(4, 5, 2); fmt = xls.GetCellVisibleFormatDef(4, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 6, xls.AddFormat(fmt)); xls.SetCellValue(4, 6, 3); fmt = xls.GetCellVisibleFormatDef(4, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 7, xls.AddFormat(fmt)); xls.SetCellValue(4, 7, 4); fmt = xls.GetCellVisibleFormatDef(4, 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 8, xls.AddFormat(fmt)); xls.SetCellValue(4, 8, 5); fmt = xls.GetCellVisibleFormatDef(4, 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 9, xls.AddFormat(fmt)); xls.SetCellValue(4, 9, 6); fmt = xls.GetCellVisibleFormatDef(4, 10); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(4, 10, xls.AddFormat(fmt)); xls.SetCellValue(4, 10, 7); #endregion #region cau hinh chu ku xls.MergeCells(TongSoHang + TuHang + 4, 1, TongSoHang + TuHang + 4, 2); xls.MergeCells(TongSoHang + TuHang + 4, 3, TongSoHang + TuHang + 4, 4); xls.MergeCells(TongSoHang + TuHang + 4, 5, TongSoHang + TuHang + 4, 6); xls.MergeCells(TongSoHang + TuHang +4, 7, TongSoHang + TuHang + 4, 8); xls.MergeCells(TongSoHang + TuHang + 4, 9, TongSoHang + TuHang + 4, 10); // Thua lenh - chuc danh - ten fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 1, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 1, "<#row height(autofit)><#ThuaLenh1> \n<#ChucDanh1>\n\n\n\n\n\n\n<#Ten1>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang +4, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 3, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 3, "<#row height(autofit)><#ThuaLenh2> \n<#ChucDanh2>\n\n\n\n\n\n\n<#Ten2>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 5, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang +4, 5, "<#row height(autofit)><#ThuaLenh3> \n<#ChucDanh3>\n\n\n\n\n\n\n<#Ten3>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 5, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 5, 7, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 5, 7, "<#row height(autofit)><#ThuaLenh4>\n<#ChucDanh4>\n\n\n\n\n\n\n<#Ten4>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 9, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 9, "<#row height(autofit)><#ThuaLenh5>\n<#ChucDanh5>\n\n\n\n\n\n\n<#Ten5>"); #endregion #region set tieu de cot dong #region set hang LNS _TuCot = TuCot; //neu so trang =1 //fmt = xls.GetCellVisibleFormatDef(3, _TuCot +5); //fmt.Font.Name = "Times New Roman"; //fmt.Font.Style = TFlxFontStyles.Italic; //fmt.Font.Family = 1; //xls.SetCellFormat(3, _TuCot + 5, xls.AddFormat(fmt)); //xls.SetCellValue(3, _TuCot + 5, "Đơn vị tính: 1000 đ"); // ngay fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 9); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Italic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.bottom; xls.SetRowHeight(TongSoHang + TuHang + 2, 400); fmt.Font.Family = 1; xls.SetCellFormat(TongSoHang + TuHang + 2, 9, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 2, 9, "<#Ngay>"); for (int j = _TuCot + 1; j <= _TuCot + SoCotTrang1 - 1; j++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, j, xls.AddFormat(fmt)); } _TuCot = _TuCot + SoCotTrang1; //set cac trang con lai for (int i = 1; i < SoTrang; i++) { //fmt = xls.GetCellVisibleFormatDef(3, _TuCot + 5); //fmt.Font.Name = "Times New Roman"; //fmt.Font.Style = TFlxFontStyles.Italic; //fmt.Font.Family = 1; //xls.SetCellFormat(3, _TuCot + 5, xls.AddFormat(fmt)); //xls.SetCellValue(3, _TuCot + 5, "Đơn vị tính: 1000 đ"); //Ngay fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Italic; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i, "<#Ngay>"); //cau hinh chu ki cho trang lon hon 1 fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh2> \n<#ChucDanh2>\n\n\n\n\n\n\n<#Ten2>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 5 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang +4, 5 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 5 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh3> \n<#ChucDanh3>\n\n\n\n\n\n\n<#Ten3>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh4> \n<#ChucDanh4>\n\n\n\n\n\n\n<#Ten4>"); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang +4, 9 + SoCotTrangLonHon1 * i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 220; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(TongSoHang + TuHang + 4, 9 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 4, 9 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh5> \n<#ChucDanh5>\n\n\n\n\n\n\n<#Ten5>"); // for (int j = _TuCot + 1; j <= _TuCot + SoCotTrangLonHon1 - 1; j++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, j, xls.AddFormat(fmt)); } _TuCot = _TuCot + SoCotTrangLonHon1; } #endregion #endregion #region set cac cot loai ngan sach _TuCot = TuCot; String TenCot; int _TuCotCua_DT = TuCotCua_DT; if (SoTrang == 1) { for (int i = 0; i < TongSoCot; i++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.WrapText = true; fmt.VAlignment = TVFlxAlignment.center; TenCot = ""; if (DenCotCua_DT > _TuCotCua_DT) { TenCot = "<#LNS" + i + ">"; } xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt)); xls.SetCellValue(4, _TuCot + i, TenCot); } } else { for (int i = 0; i < SoCotTrang1; i++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.WrapText = true; fmt.VAlignment = TVFlxAlignment.center; TenCot = ""; if (DenCotCua_DT > _TuCotCua_DT) { TenCot = "<#LNS" + i + ">"; } xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt)); xls.SetCellValue(4, _TuCot + i, TenCot); } _TuCotCua_DT = _TuCotCua_DT + SoCotTrang1; _TuCot = _TuCot + SoCotTrang1; for (int i = 0; i < TongSoCot - SoCotTrang1; i++) { fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.WrapText = true; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; TenCot = ""; int a = Convert.ToInt16(SoCotTrang1) + i; if (DenCotCua_DT > _TuCotCua_DT) { TenCot = "<#LNS" + a + ">"; } xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt)); xls.SetCellValue(4, _TuCot + i, TenCot); } } #endregion #endregion //Cell selection and scroll position. //Protection TSheetProtectionOptions SheetProtectionOptions; SheetProtectionOptions = new TSheetProtectionOptions(false); SheetProtectionOptions.SelectLockedCells = true; SheetProtectionOptions.SelectUnlockedCells = true; xls.Protection.SetSheetProtection(null, SheetProtectionOptions); return xls; }
private int NoColorAndRightBorder(XlsFile xls, int row, int col) { TFlxFormat tf3 = xls.GetCellVisibleFormatDef(row, col); TFlxFont tfont = new TFlxFont(); tfont.Style = TFlxFontStyles.Bold; tf3.Font = tfont; TFlxBorders tfBorderF = new TFlxBorders(); TFlxOneBorder oborder = new TFlxOneBorder(); oborder.Style = TFlxBorderStyle.Thin; tfBorderF.Right = oborder; tf3.Borders = tfBorderF; return xls.AddFormat(tf3); }
/// <summary> /// Hiển thị dữ liệu ra báo cáo /// </summary> /// <param name="xls"></param> /// <param name="dt"></param> /// <param name="TuHang"></param> /// <param name="TuCot"></param> /// <param name="TuCotCua_DT"></param> /// <param name="DenCotCua_DT"></param> /// <param name="SoCotTrang1"></param> /// <param name="SoCotTrangLonHon1"></param> /// <param name="MapCotCoDinh"></param> public void Filldata(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotTrang1, int SoCotTrangLonHon1, String MapCotCoDinh) { TFlxFormat fmt; Object GiaTriO; int TongSoHang = dt.Rows.Count; int _TuCot = TuCot; int TongSoCot = 0; int SoTrang = 1; if ((DenCotCua_DT - TuCotCua_DT) <= SoCotTrang1) { int SoCotDu = ((DenCotCua_DT - TuCotCua_DT)) % SoCotTrang1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrang1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; } else { int SoCotDu = (DenCotCua_DT - TuCotCua_DT - SoCotTrang1) % SoCotTrangLonHon1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrangLonHon1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; SoTrang = 1 + (TongSoCot - SoCotTrang1) / SoCotTrangLonHon1; } //set border cho cot can them for (int c = DenCotCua_DT - TuCotCua_DT + TuCot; c < TongSoCot + TuCot; c++) { for (int h = 0; h < dt.Rows.Count; h++) { fmt = xls.GetCellVisibleFormatDef(h + TuHang, c); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(h + TuHang, c, xls.AddFormat(fmt)); } } String[] arrMapCot = MapCotCoDinh.Split('|'); String[] arrCot_Excel = arrMapCot[0].Split(','); String[] arrCot_DT = arrMapCot[1].Split(','); #region Fill dữ liệu những cột động _TuCot = TuCot; int d = 0; for (int c = 0; c < TongSoCot; c++) { Type _Type = typeof(String); if (c + TuCotCua_DT < DenCotCua_DT) _Type = dt.Columns[c + TuCotCua_DT].DataType; switch (_Type.ToString()) { case "System.Decimal": fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "_(* #,###_);_(* \\(#,###\\);_(* \"\"??_);_(@_)"; break; default: fmt = xls.GetCellVisibleFormatDef(TuHang, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; break; } for (int h = 0; h < dt.Rows.Count; h++) { GiaTriO = null; xls.SetCellFormat(h + TuHang, _TuCot, xls.AddFormat(fmt)); if (c + TuCotCua_DT < DenCotCua_DT) xls.SetCellValue(h + TuHang, _TuCot, dt.Rows[h][c + TuCotCua_DT]); } _TuCot++; } #endregion #region Fill dữ liệu những cột tĩnh _TuCot = TuCot; String KyTu1, KyTu2, strSum; for (int i = 0; i < dt.Rows.Count; i++) { for (int c = 0; c < arrCot_Excel.Length; c++) { fmt = xls.GetCellVisibleFormatDef(TuHang + i, Convert.ToInt32(arrCot_Excel[c])); if (c >= arrCot_Excel.Length - 3) { fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "_(* #,###_);_(* \\(#,###\\);_(* \"\"??_);_(@_)"; } else { fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 160; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; } fmt.WrapText = true; xls.AutofitRow(i + TuHang, true, 1); GiaTriO = null; if (c < arrCot_DT.Length) GiaTriO = dt.Rows[i][Convert.ToInt16(arrCot_DT[c])]; if (c < arrCot_Excel.Length) { xls.SetCellFormat(i + TuHang, Convert.ToInt16(arrCot_Excel[c]), xls.AddFormat(fmt)); xls.SetCellValue(i + TuHang, Convert.ToInt16(arrCot_Excel[c]), GiaTriO); } } } _TuCot = TuCot; //set tiêu đề cho hàng tổng số fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 1); fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 2, xls.AddFormat(fmt)); xls.SetRowHeight(TongSoHang + TuHang, 400); for (int i = 1; i < TongSoCot + TuCotCua_DT; i++) { xls.SetCellFormat(TongSoHang + TuHang, i, xls.AddFormat(fmt)); } for (int i = 0; i <= TongSoCot + 2; i++) { xls.SetCellValue(TongSoHang + TuHang, 2, "Tổng Cộng: "); fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 180; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang, _TuCot - 3 + i, xls.AddFormat(fmt)); KyTu1 = HamChung.ExportExcel_MaCot(_TuCot - 3 + i); if (TongSoHang > 1) { strSum = String.Format("=SUMIF(C{1}:C{3},\"<>\"&\"\",{0}{1}:{2}{3})", KyTu1, TuHang, KyTu1, TongSoHang + TuHang - 1); xls.SetCellFormat(TongSoHang + TuHang, _TuCot - 3 + i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang, _TuCot - 3 + i, new TFormula(strSum)); } } #endregion }
void SetTitleCellFormat(XlsFile xls) { TFlxFormat cellFormat = xls.GetCellVisibleFormatDef (1, 1); cellFormat.Font.Size20 = 240; cellFormat.Font.Color = TExcelColor.FromTheme (TThemeColor.Accent1); cellFormat.Font.Style = TFlxFontStyles.Bold; cellFormat.FillPattern.Pattern = TFlxPatternStyle.Solid; cellFormat.FillPattern.FgColor = TExcelColor.FromTheme(TThemeColor.Accent3, 0.6); cellFormat.VAlignment = TVFlxAlignment.center; cellFormat.HAlignment = THFlxAlignment.center; xls.SetCellFormat (1, 1, xls.AddFormat (cellFormat)); }
/// <summary> /// Tạo tiêu đề /// </summary> /// <param name="xls"></param> /// <returns></returns> public XlsFile TaoTieuDe(XlsFile xls) { xls.NewFile(1); //Create a new Excel file with 1 sheet. //Set the names of the sheets xls.ActiveSheet = 1; xls.SheetName = "Sheet1"; xls.ActiveSheet = 1; //Set the sheet we are working in. //Global Workbook Options xls.OptionsAutoCompressPictures = true; xls.OptionsCheckCompatibility = false; //Styles. TFlxFormat StyleFmt; StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Format = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2), StyleFmt); //Named Ranges TXlsNamedRange Range; string RangeName; RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles); Range = new TXlsNamedRange(RangeName, 1, 32, "='Sheet1'!$4:$4"); //You could also use: Range = new TXlsNamedRange(RangeName, 1, 1, 4, 1, 4, FlxConsts.Max_Columns + 1, 32); xls.SetNamedRange(Range); //Printer Settings THeaderAndFooter HeadersAndFooters = new THeaderAndFooter(); HeadersAndFooters.AlignMargins = true; HeadersAndFooters.ScaleWithDoc = true; HeadersAndFooters.DiffFirstPage = true; HeadersAndFooters.DiffEvenPages = false; HeadersAndFooters.DefaultHeader = "&R&\"Times New Roman,Italic\"Trang: &P "; HeadersAndFooters.DefaultFooter = ""; HeadersAndFooters.FirstHeader = "&R&\"Times New Roman,Italic\"\n\n\n\nTrang: &P "; HeadersAndFooters.FirstFooter = ""; HeadersAndFooters.EvenHeader = ""; HeadersAndFooters.EvenFooter = ""; xls.SetPageHeaderAndFooter(HeadersAndFooters); //You can set the margins in 2 ways, the one commented here or the one below: // TXlsMargins PrintMargins = xls.GetPrintMargins(); // PrintMargins.Left = 0.590551181102362; // PrintMargins.Top = 0.551181102362205; // PrintMargins.Right = 0.196850393700787; // PrintMargins.Bottom = 0.393700787401575; // PrintMargins.Header = 0.31496062992126; // PrintMargins.Footer = 0.31496062992126; // xls.SetPrintMargins(PrintMargins); xls.SetPrintMargins(new TXlsMargins(0.590551181102362, 0.551181102362205, 0.196850393700787, 0.393700787401575, 0.31496062992126, 0.31496062992126)); xls.PrintXResolution = 600; xls.PrintYResolution = 600; xls.PrintOptions = TPrintOptions.Orientation; xls.PrintPaperSize = TPaperSize.A4; //Printer Driver Settings are a blob of data specific to a printer //This code is commented by default because normally you do not want to hard code the printer settings of an specific printer. // byte[] PrinterData = { // 0x00, 0x00, 0x4D, 0x00, 0x69, 0x00, 0x63, 0x00, 0x72, 0x00, 0x6F, 0x00, 0x73, 0x00, 0x6F, 0x00, 0x66, 0x00, 0x74, 0x00, 0x20, 0x00, 0x58, 0x00, 0x50, 0x00, 0x53, 0x00, 0x20, 0x00, 0x44, 0x00, 0x6F, 0x00, 0x63, 0x00, 0x75, 0x00, 0x6D, 0x00, 0x65, 0x00, 0x6E, 0x00, 0x74, 0x00, 0x20, 0x00, 0x57, 0x00, // 0x72, 0x00, 0x69, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x00, 0x06, 0xDC, 0x00, 0x78, 0x03, 0x03, 0xAF, 0x00, 0x00, 0x01, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x64, 0x00, 0x01, 0x00, 0x0F, 0x00, 0x58, 0x02, 0x02, 0x00, 0x01, 0x00, 0x58, 0x02, // 0x03, 0x00, 0x00, 0x00, 0x4C, 0x00, 0x65, 0x00, 0x74, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x01, 0x00, // 0x00, 0x00, 0xFF, 0xFF, 0xFF, 0xFF, 0x47, 0x49, 0x53, 0x34, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x44, 0x49, 0x4E, 0x55, 0x22, 0x00, 0x20, 0x01, 0x5C, 0x03, 0x1C, 0x00, 0xCA, 0xD2, 0xF6, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x09, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x20, 0x01, 0x00, 0x00, 0x53, 0x4D, // 0x54, 0x4A, 0x00, 0x00, 0x00, 0x00, 0x10, 0x00, 0x10, 0x01, 0x7B, 0x00, 0x30, 0x00, 0x46, 0x00, 0x34, 0x00, 0x31, 0x00, 0x33, 0x00, 0x30, 0x00, 0x44, 0x00, 0x44, 0x00, 0x2D, 0x00, 0x31, 0x00, 0x39, 0x00, 0x43, 0x00, 0x37, 0x00, 0x2D, 0x00, 0x37, 0x00, 0x61, 0x00, 0x62, 0x00, 0x36, 0x00, 0x2D, 0x00, // 0x39, 0x00, 0x39, 0x00, 0x41, 0x00, 0x31, 0x00, 0x2D, 0x00, 0x39, 0x00, 0x38, 0x00, 0x30, 0x00, 0x46, 0x00, 0x30, 0x00, 0x33, 0x00, 0x42, 0x00, 0x32, 0x00, 0x45, 0x00, 0x45, 0x00, 0x34, 0x00, 0x45, 0x00, 0x7D, 0x00, 0x00, 0x00, 0x49, 0x6E, 0x70, 0x75, 0x74, 0x42, 0x69, 0x6E, 0x00, 0x46, 0x4F, 0x52, // 0x4D, 0x53, 0x4F, 0x55, 0x52, 0x43, 0x45, 0x00, 0x52, 0x45, 0x53, 0x44, 0x4C, 0x4C, 0x00, 0x55, 0x6E, 0x69, 0x72, 0x65, 0x73, 0x44, 0x4C, 0x4C, 0x00, 0x49, 0x6E, 0x74, 0x65, 0x72, 0x6C, 0x65, 0x61, 0x76, 0x69, 0x6E, 0x67, 0x00, 0x4F, 0x46, 0x46, 0x00, 0x49, 0x6D, 0x61, 0x67, 0x65, 0x54, 0x79, 0x70, // 0x65, 0x00, 0x4A, 0x50, 0x45, 0x47, 0x4D, 0x65, 0x64, 0x00, 0x4F, 0x72, 0x69, 0x65, 0x6E, 0x74, 0x61, 0x74, 0x69, 0x6F, 0x6E, 0x00, 0x50, 0x4F, 0x52, 0x54, 0x52, 0x41, 0x49, 0x54, 0x00, 0x43, 0x6F, 0x6C, 0x6C, 0x61, 0x74, 0x65, 0x00, 0x4F, 0x46, 0x46, 0x00, 0x52, 0x65, 0x73, 0x6F, 0x6C, 0x75, 0x74, // 0x69, 0x6F, 0x6E, 0x00, 0x4F, 0x70, 0x74, 0x69, 0x6F, 0x6E, 0x31, 0x00, 0x50, 0x61, 0x70, 0x65, 0x72, 0x53, 0x69, 0x7A, 0x65, 0x00, 0x4C, 0x45, 0x54, 0x54, 0x45, 0x52, 0x00, 0x43, 0x6F, 0x6C, 0x6F, 0x72, 0x4D, 0x6F, 0x64, 0x65, 0x00, 0x32, 0x34, 0x62, 0x70, 0x70, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x1C, 0x00, 0x00, 0x00, 0x56, 0x34, 0x44, 0x4D, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // }; // TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData); // xls.SetPrinterDriverSettings(PrinterDriverSettings); //Set up rows and columns xls.DefaultColWidth = 2340; xls.SetColWidth(1, 1901); //(6.68 + 0.75) * 256 TFlxFormat ColFmt; ColFmt = xls.GetFormat(xls.GetColFormat(1)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(1, xls.AddFormat(ColFmt)); xls.SetColWidth(2, 5778); //(21.82 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(2)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(2, xls.AddFormat(ColFmt)); xls.SetColWidth(3, 3840); //(14.25 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(3)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(3, xls.AddFormat(ColFmt)); xls.SetColWidth(4, 1901); //(6.68 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(4)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(4, xls.AddFormat(ColFmt)); xls.SetColWidth(5, 5778); //(21.82 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(5)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(5, xls.AddFormat(ColFmt)); xls.SetColWidth(6, 3840); //(14.25 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(6)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(6, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(7)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(7, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(8)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(8, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(9)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(9, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(10)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(10, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(11)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(11, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(12)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(12, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(13)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(13, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(14)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(14, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(15)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(15, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(16)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(16, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(17)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(17, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(18)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(18, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(19)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(19, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(20)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(20, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(21)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(21, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(22)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(22, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(23)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(23, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(24)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(24, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(25)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(25, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(26)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(26, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(27)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(27, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(28)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(28, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(29)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(29, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(30)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(30, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(31)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(31, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(32)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(32, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(33)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(33, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(34)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(34, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(35)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(35, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(36)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(36, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(37)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(37, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(38)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(38, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(39)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(39, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(40)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(40, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(41)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(41, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(42)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(42, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(43)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(43, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(44)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(44, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(45)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(45, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(46)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(46, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(47)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(47, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(48)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(48, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(49)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(49, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(50)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(50, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(51)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(51, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(52)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(52, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(53)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(53, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(54)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(54, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(55)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(55, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(56)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(56, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(57)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(57, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(58)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(58, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(59)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(59, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(60)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(60, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(61)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(61, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(62)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(62, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(63)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(63, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(64)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(64, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(65)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(65, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(66)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(66, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(67)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(67, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(68)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(68, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(69)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(69, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(70)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(70, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(71)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(71, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(72)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(72, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(73)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(73, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(74)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(74, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(75)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(75, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(76)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(76, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(77)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(77, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(78)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(78, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(79)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(79, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(80)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(80, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(81)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(81, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(82)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(82, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(83)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(83, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(84)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(84, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(85)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(85, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(86)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(86, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(87)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(87, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(88)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(88, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(89)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(89, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(90)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(90, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(91)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(91, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(92)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(92, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(93)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(93, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(94)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(94, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(95)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(95, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(96)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(96, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(97)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(97, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(98)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(98, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(99)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(99, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(100)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(100, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(101)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(101, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(102)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(102, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(103)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(103, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(104)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(104, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(105)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(105, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(106)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(106, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(107)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(107, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(108)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(108, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(109)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(109, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(110)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(110, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(111)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(111, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(112)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(112, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(113)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(113, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(114)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(114, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(115)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(115, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(116)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(116, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(117)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(117, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(118)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(118, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(119)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(119, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(120)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(120, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(121)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(121, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(122)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(122, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(123)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(123, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(124)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(124, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(125)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(125, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(126)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(126, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(127)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(127, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(128)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(128, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(129)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(129, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(130)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(130, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(131)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(131, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(132)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(132, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(133)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(133, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(134)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(134, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(135)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(135, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(136)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(136, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(137)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(137, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(138)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(138, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(139)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(139, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(140)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(140, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(141)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(141, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(142)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(142, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(143)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(143, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(144)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(144, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(145)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(145, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(146)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(146, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(147)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(147, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(148)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(148, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(149)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(149, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(150)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(150, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(151)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(151, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(152)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(152, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(153)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(153, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(154)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(154, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(155)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(155, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(156)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(156, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(157)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(157, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(158)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(158, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(159)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(159, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(160)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(160, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(161)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(161, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(162)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(162, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(163)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(163, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(164)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(164, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(165)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(165, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(166)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(166, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(167)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(167, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(168)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(168, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(169)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(169, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(170)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(170, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(171)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(171, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(172)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(172, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(173)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(173, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(174)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(174, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(175)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(175, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(176)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(176, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(177)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(177, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(178)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(178, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(179)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(179, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(180)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(180, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(181)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(181, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(182)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(182, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(183)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(183, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(184)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(184, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(185)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(185, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(186)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(186, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(187)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(187, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(188)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(188, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(189)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(189, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(190)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(190, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(191)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(191, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(192)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(192, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(193)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(193, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(194)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(194, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(195)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(195, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(196)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(196, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(197)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(197, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(198)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(198, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(199)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(199, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(200)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(200, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(201)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(201, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(202)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(202, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(203)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(203, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(204)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(204, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(205)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(205, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(206)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(206, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(207)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(207, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(208)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(208, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(209)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(209, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(210)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(210, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(211)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(211, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(212)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(212, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(213)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(213, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(214)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(214, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(215)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(215, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(216)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(216, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(217)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(217, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(218)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(218, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(219)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(219, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(220)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(220, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(221)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(221, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(222)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(222, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(223)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(223, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(224)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(224, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(225)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(225, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(226)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(226, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(227)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(227, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(228)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(228, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(229)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(229, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(230)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(230, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(231)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(231, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(232)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(232, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(233)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(233, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(234)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(234, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(235)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(235, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(236)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(236, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(237)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(237, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(238)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(238, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(239)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(239, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(240)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(240, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(241)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(241, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(242)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(242, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(243)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(243, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(244)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(244, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(245)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(245, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(246)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(246, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(247)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(247, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(248)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(248, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(249)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(249, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(250)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(250, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(251)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(251, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(252)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(252, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(253)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(253, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(254)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(254, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(255)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(255, xls.AddFormat(ColFmt)); ColFmt = xls.GetFormat(xls.GetColFormat(256)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; xls.SetColFormat(256, xls.AddFormat(ColFmt)); xls.DefaultRowHeight = 300; xls.SetRowHeight(1, 330); //16.50 * 20 xls.SetRowHeight(2, 315); //15.75 * 20 xls.SetRowHeight(4, 390); //19.50 * 20 //Merged Cells xls.MergeCells(3, 3, 3, 5); xls.MergeCells(1, 1, 1, 6); xls.MergeCells(2, 1, 2, 6); xls.MergeCells(2, 3, 2, 5); //Set the cell values TFlxFormat fmt; fmt = xls.GetCellVisibleFormatDef(1, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 280; fmt.Font.Family = 1; fmt.Font.Style = TFlxFontStyles.Bold; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 1, xls.AddFormat(fmt)); xls.SetCellValue(1, 1, "DANH SÁCH CHI TRẢ CÁ NHÂN"); fmt = xls.GetCellVisibleFormatDef(1, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 7, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 8, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(1, 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 260; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(1, 9, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 1, xls.AddFormat(fmt)); xls.SetCellValue(2, 1, "Tháng <#Thang> / <#Nam>"); fmt = xls.GetCellVisibleFormatDef(2, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 3, xls.AddFormat(fmt)); xls.MergeCells(2, 1, 2, 6); xls.SetCellValue(2, 3, "DANH SÁCH CHI TRẢ CÁ NHÂN"); fmt = xls.GetCellVisibleFormatDef(2, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 7); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 7, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 8); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 8, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 9); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(2, 9, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(3, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 240; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(3, 1, xls.AddFormat(fmt)); xls.SetCellValue(3, 1, "<#Auto page breaks>"); fmt = xls.GetCellVisibleFormatDef(3, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(3, 3, xls.AddFormat(fmt)); xls.SetCellValue(3, 3, ""); fmt = xls.GetCellVisibleFormatDef(3, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(3, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(3, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(3, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(4, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 1, xls.AddFormat(fmt)); xls.SetCellValue(4, 1, "TT"); fmt = xls.GetCellVisibleFormatDef(4, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 2, xls.AddFormat(fmt)); xls.SetCellValue(4, 2, "SỐ TÀI KHOẢN"); fmt = xls.GetCellVisibleFormatDef(4, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 3, xls.AddFormat(fmt)); xls.SetCellValue(4, 3, "SỐ TIỀN"); fmt = xls.GetCellVisibleFormatDef(4, 4); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 4, xls.AddFormat(fmt)); xls.SetCellValue(4, 4, "TT"); fmt = xls.GetCellVisibleFormatDef(4, 5); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 5, xls.AddFormat(fmt)); xls.SetCellValue(4, 5, "SỐ TÀI KHOẢN"); fmt = xls.GetCellVisibleFormatDef(4, 6); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(4, 6, xls.AddFormat(fmt)); xls.SetCellValue(4, 6, "SỐ TIỀN"); //Cell selection and scroll position. xls.SelectCell(11, 2, false); //Protection TSheetProtectionOptions SheetProtectionOptions; SheetProtectionOptions = new TSheetProtectionOptions(false); SheetProtectionOptions.SelectLockedCells = true; SheetProtectionOptions.SelectUnlockedCells = true; xls.Protection.SetSheetProtection(null, SheetProtectionOptions); return xls; }
/// <summary> /// Đổ dữ liệu xuống báo cáo /// </summary> /// <param name="xls"></param> /// <param name="dt"></param> /// <param name="DK1"></param> /// <param name="DK2"></param> /// <param name="DK3"></param> public void FillData(XlsFile xls, DataTable dt, String DK1, String DK2, String DK3) { TFlxFormat fmt, fmt1,fmt2,fmt_TL,fmt_CD,fmt_Ten; Object GiaTriO; int sohang = 45; int sotrang = 1; ///Fill nửa bên trái for (int i = 0; i < dt.Rows.Count; i = i + sohang) { i = i + sohang; #region "Fill nửa bên trái" for (int j = i - sohang; j < i; j++) { if ((j + i - sohang) < dt.Rows.Count) { for (int c = 0; c < 3; c++) { fmt = xls.GetCellVisibleFormatDef(5 + j + i - sohang * sotrang, c + 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.DefaultRowHeight = 300; xls.AutofitRow(5 + j + i - sohang * sotrang, true, 1); GiaTriO = null; if (Convert.ToString(dt.Rows[j + i - sohang][DK1].ToString()) == "" && Convert.ToString(dt.Rows[j + i - sohang][DK2].ToString()) != "" && Convert.ToString(dt.Rows[j + i - sohang][DK3].ToString())=="") { fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; if (c == 0) { fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Style = TFlxBorderStyle.None; GiaTriO = dt.Rows[j + i - sohang][c+1]; } else { fmt.Borders.Left.Style = TFlxBorderStyle.None; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; GiaTriO = dt.Rows[j + i - sohang][c]; } xls.MergeCells(5 + j + i - sohang * sotrang, 1, 5 + j + i - sohang * sotrang, 3); xls.SetCellFormat(5 + j + i - sohang * sotrang, c +1, xls.AddFormat(fmt)); xls.SetCellValue(5 + j + i - sohang * sotrang, c +1, GiaTriO); } else if (Convert.ToString(dt.Rows[j + i - sohang][DK1].ToString()) == "" && Convert.ToString(dt.Rows[j + i - sohang][DK2].ToString()) == "+" && Convert.ToString(dt.Rows[j + i - sohang][DK3].ToString()) != "") { fmt.Font.Style = TFlxFontStyles.Bold; fmt.HAlignment = THFlxAlignment.center; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; xls.MergeCells(5 + j + i - sohang * sotrang, 1, 5 + j + i - sohang * sotrang, 2); if(c==0) GiaTriO = dt.Rows[j + i - sohang][c+1]; else GiaTriO = dt.Rows[j + i - sohang][c]; xls.SetCellFormat(5 + j + i - sohang * sotrang, c + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + j + i - sohang * sotrang, c + 1, GiaTriO); } else if (Convert.ToString(dt.Rows[j + i - sohang][DK1].ToString()) != "" && Convert.ToString(dt.Rows[j + i - sohang][DK2].ToString()) != "+" && Convert.ToString(dt.Rows[j + i - sohang][DK3].ToString()) != "") { fmt.Font.Style = TFlxFontStyles.None; fmt.Font.Family = 1; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; switch(c) { case 0: if (DK1 == "TT_DV") { fmt.HAlignment = THFlxAlignment.center; } else if (DK1 == "TenCB") { fmt.HAlignment = THFlxAlignment.left; } GiaTriO = dt.Rows[j + i - sohang][c]; xls.SetCellFormat(5 + j + i - sohang * sotrang, c + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + j + i - sohang * sotrang, c + 1, GiaTriO); break; case 1: fmt.HAlignment = THFlxAlignment.left; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; GiaTriO = dt.Rows[j + i - sohang][c]; xls.SetCellFormat(5 + j + i - sohang * sotrang, c + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + j + i - sohang * sotrang, c + 1, GiaTriO); break; case 2: fmt.HAlignment = THFlxAlignment.right; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; GiaTriO = dt.Rows[j + i - sohang][c]; xls.SetCellFormat(5 + j + i - sohang * sotrang, c + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + j + i - sohang * sotrang, c + 1, GiaTriO); break; } } } } } #endregion ///Fill nửa bên phải #region "Fill nửa bên phải" for (int z = i; z < i + sohang; z++) { if ((z + i - sohang) < dt.Rows.Count) { for (int h = 3; h < 6; h++) { fmt = xls.GetCellVisibleFormatDef(5 + z + i - sohang - sohang * sotrang, h + 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.DefaultRowHeight = 300; xls.AutofitRow(5 + z + i - sohang - sohang * sotrang, true, 1); GiaTriO = null; GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); if (Convert.ToString(dt.Rows[z + i - sohang][DK1].ToString()) == "" && Convert.ToString(dt.Rows[z + i - sohang][DK2].ToString()) != "" && Convert.ToString(dt.Rows[z + i - sohang][DK3].ToString()) == "") { fmt.Font.Style = TFlxFontStyles.Bold; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; //fmt.Borders.Right.Style = TFlxBorderStyle.Thin; //fmt.Borders.Left.Style = TFlxBorderStyle.Thin; if (h == 3) { fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Style = TFlxBorderStyle.None; GiaTriO = dt.Rows[z + i - sohang][h - 2]; } else { fmt.Borders.Left.Style = TFlxBorderStyle.None; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; GiaTriO = dt.Rows[z + i - sohang][h - 3]; } xls.MergeCells(5 + z + i - sohang - sohang * sotrang, 4, 5 + z + i - sohang - sohang * sotrang, 6); //GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); } else if (Convert.ToString(dt.Rows[z + i - sohang][DK1].ToString()) == "" && Convert.ToString(dt.Rows[z + i - sohang][DK2].ToString()) == "+" && Convert.ToString(dt.Rows[z + i - sohang][DK3].ToString()) != "") { fmt.Font.Style = TFlxFontStyles.Bold; fmt.HAlignment = THFlxAlignment.center; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; xls.MergeCells(5 + z + i - sohang - sohang * sotrang, 4, 5 + z + i - sohang - sohang * sotrang, 5); //xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, 4, xls.AddFormat(fmt)); if (h == 3) GiaTriO = dt.Rows[z + i - sohang][h - 3+1]; else GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); } else if (Convert.ToString(dt.Rows[z + i - sohang][DK1].ToString()) != "" && Convert.ToString(dt.Rows[z + i - sohang][DK2].ToString()) != "+" && Convert.ToString(dt.Rows[z + i - sohang][DK3].ToString()) != "") { fmt.Font.Style = TFlxFontStyles.None; fmt.Font.Family = 1; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; switch (h) { case 3: if (DK1 == "TT_DV") { fmt.HAlignment = THFlxAlignment.center; } else if (DK1 == "TenCB") { fmt.HAlignment = THFlxAlignment.left; } GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); break; case 4: fmt.HAlignment = THFlxAlignment.left; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); break; case 5: fmt.HAlignment = THFlxAlignment.right; fmt.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; GiaTriO = dt.Rows[z + i - sohang][h - 3]; xls.SetCellFormat(5 + z + i - sohang - sohang * sotrang, h + 1, xls.AddFormat(fmt)); xls.SetCellValue(5 + z + i - sohang - sohang * sotrang, h + 1, GiaTriO); break; } } } } } #endregion i = i - sohang; sotrang++; } int hangs = sohang > dt.Rows.Count ? dt.Rows.Count + 1 + 5 : sohang + 1 + 5; fmt1 = xls.GetCellVisibleFormatDef(hangs, 2); fmt1.Font.Style = TFlxFontStyles.Bold | TFlxFontStyles.Italic; fmt1.Font.Name = "Times New Roman"; fmt1.Format = ""; fmt1.Font.Size20 = 220; fmt1.Font.Family = 1; fmt1.Format = "_(* #,##0_);_(* \\-#,##0_);_(* \"\"_);_(@_)"; if (DK1 == "TT_DV") { xls.SetCellFormat(hangs + 1, 2, xls.AddFormat(fmt1)); xls.SetCellValue(hangs - 1, 2, "Tổng số người: "); xls.SetCellValue(hangs - 1, 3, "<#SoNguoi>"); xls.SetCellValue(hangs, 2, "Tổng số tiền là: "); xls.SetCellValue(hangs, 3, "<#SoTien>"); xls.SetCellValue(hangs + 1, 2, "Bằng chữ: " + "<#TienRaChu>"); } else if (DK1 == "TenCB") { xls.SetCellFormat(hangs + 1, 1, xls.AddFormat(fmt1)); xls.SetCellValue(hangs - 1, 1, " Tổng số người: "); xls.SetCellValue(hangs - 1, 3, "<#SoNguoi>"); xls.SetCellValue(hangs, 1, " Tổng số tiền là: "); xls.SetCellValue(hangs, 3, "<#SoTien>"); xls.SetCellValue(hangs + 1, 1, " Bằng chữ: " + "<#TienRaChu>"); } fmt2 = xls.GetCellVisibleFormatDef(hangs, 6); fmt2.Font.Style = TFlxFontStyles.Italic; fmt2.Font.Name = "Times New Roman"; fmt2.Format = ""; fmt2.Font.Size20 = 220; fmt2.Font.Family = 1; fmt2.HAlignment = THFlxAlignment.right; xls.MergeCells(hangs+2,1,hangs+2, 6); xls.SetCellFormat(hangs + 2, 1, xls.AddFormat(fmt2)); xls.SetCellValue(hangs + 2, 1, "<#NgayThang>"); //Thừa lệnh 1 fmt_TL = xls.GetCellVisibleFormatDef(hangs, 1); fmt_TL.Font.Style = TFlxFontStyles.None; fmt_TL.Font.Name = "Times New Roman"; fmt_TL.Format = ""; fmt_TL.Font.Size20 = 220; fmt_TL.Font.Family = 1; fmt_TL.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 4, 1, hangs + 4, 2); xls.SetCellFormat(hangs + 4, 1, xls.AddFormat(fmt_TL)); xls.SetCellValue(hangs + 4, 1, "<#ThuaLenh1>"); //Thừa lệnh 2 fmt_TL = xls.GetCellVisibleFormatDef(hangs, 1); fmt_TL.Font.Style = TFlxFontStyles.None; fmt_TL.Font.Name = "Times New Roman"; fmt_TL.Format = ""; fmt_TL.Font.Size20 = 220; fmt_TL.Font.Family = 1; fmt_TL.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 4, 3, hangs + 4, 3); xls.SetCellFormat(hangs + 4, 3, xls.AddFormat(fmt_TL)); xls.SetCellValue(hangs + 4, 1, "<#ThuaLenh2>"); //Thừa lệnh 3 fmt_TL = xls.GetCellVisibleFormatDef(hangs, 1); fmt_TL.Font.Style = TFlxFontStyles.None; fmt_TL.Font.Name = "Times New Roman"; fmt_TL.Format = ""; fmt_TL.Font.Size20 = 220; fmt_TL.Font.Family = 1; fmt_TL.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 4, 5, hangs + 4, 6); xls.SetCellFormat(hangs + 4, 5, xls.AddFormat(fmt_TL)); xls.SetCellValue(hangs + 4, 5, "<#ThuaLenh3>"); //Chức danh 1 fmt_CD = xls.GetCellVisibleFormatDef(hangs, 1); fmt_CD.Font.Style = TFlxFontStyles.Bold; fmt_CD.Font.Name = "Times New Roman"; fmt_CD.Format = ""; fmt_CD.Font.Size20 = 220; fmt_CD.Font.Family = 1; fmt_CD.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 5, 1, hangs + 5, 2); xls.SetCellFormat(hangs + 5, 1, xls.AddFormat(fmt_CD)); xls.SetCellValue(hangs + 5, 1, "<#ChucDanh1>"); //Chức danh 2 fmt_CD = xls.GetCellVisibleFormatDef(hangs, 1); fmt_CD.Font.Style = TFlxFontStyles.Bold; fmt_CD.Font.Name = "Times New Roman"; fmt_CD.Format = ""; fmt_CD.Font.Size20 = 220; fmt_CD.Font.Family = 1; fmt_CD.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 5, 3, hangs + 5, 4); xls.SetCellFormat(hangs + 5, 3, xls.AddFormat(fmt_CD)); xls.SetCellValue(hangs + 5, 3, "<#ChucDanh2>"); //Chức danh 3 fmt_CD = xls.GetCellVisibleFormatDef(hangs, 4); fmt_CD.Font.Style = TFlxFontStyles.Bold; fmt_CD.Font.Name = "Times New Roman"; fmt_CD.Format = ""; fmt_CD.Font.Size20 = 220; fmt_CD.Font.Family = 1; fmt_CD.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 5, 5, hangs + 5, 6); xls.SetCellFormat(hangs + 5, 5, xls.AddFormat(fmt_CD)); xls.SetCellValue(hangs + 5, 5, "<#ChucDanh3>"); //Tên 1 fmt_Ten = xls.GetCellVisibleFormatDef(hangs, 1); fmt_Ten.Font.Style = TFlxFontStyles.None; fmt_Ten.Font.Name = "Times New Roman"; fmt_Ten.Format = ""; fmt_Ten.Font.Size20 = 220; fmt_Ten.Font.Family = 1; fmt_Ten.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 9, 1, hangs + 9, 2); xls.SetCellFormat(hangs + 9, 1, xls.AddFormat(fmt_Ten)); xls.SetCellValue(hangs + 9, 1, "<#Ten1>"); //Tên 2 fmt_Ten = xls.GetCellVisibleFormatDef(hangs, 1); fmt_Ten.Font.Style = TFlxFontStyles.None; fmt_Ten.Font.Name = "Times New Roman"; fmt_Ten.Format = ""; fmt_Ten.Font.Size20 = 220; fmt_Ten.Font.Family = 1; fmt_Ten.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 9, 3, hangs + 9, 4); xls.SetCellFormat(hangs + 9, 3, xls.AddFormat(fmt_Ten)); xls.SetCellValue(hangs + 9, 3, "<#Ten2>"); //Tên 3 fmt_Ten = xls.GetCellVisibleFormatDef(hangs, 1); fmt_Ten.Font.Style = TFlxFontStyles.None; fmt_Ten.Font.Name = "Times New Roman"; fmt_Ten.Format = ""; fmt_Ten.Font.Size20 = 220; fmt_Ten.Font.Family = 1; fmt_Ten.HAlignment = THFlxAlignment.center; xls.MergeCells(hangs + 9, 5, hangs + 9, 6); xls.SetCellFormat(hangs + 9, 5, xls.AddFormat(fmt_Ten)); xls.SetCellValue(hangs + 9, 5, "<#Ten3>"); // TXlsNamedRange Range; Range = new TXlsNamedRange("KeepRows_1_", 0, 1, 4, 1, hangs + 10, 6, 0); xls.SetNamedRange(Range); Range = new TXlsNamedRange("KeepRows_2_", 0, 1, hangs, 1, hangs + 10, 6, 0); xls.SetNamedRange(Range); }
public static void FilldataLuyKe(XlsFile xls, DataTable dt, DataTable dtLuyKe, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotTrang1, int SoCotTrangLonHon1, String MapCotCoDinh) { TFlxFormat fmt; Object GiaTriO; int TongSoHang = dt.Rows.Count; int _TuCot = TuCot; int TongSoCot = 0; int SoTrang = 1; if ((DenCotCua_DT - TuCotCua_DT) <= SoCotTrang1) { int SoCotDu = ((DenCotCua_DT - TuCotCua_DT)) % SoCotTrang1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrang1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; } else { int SoCotDu = (DenCotCua_DT - TuCotCua_DT - SoCotTrang1) % SoCotTrangLonHon1; int SoCotCanThem = 0; SoCotCanThem = SoCotTrangLonHon1 - SoCotDu; TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem; SoTrang = 1 + (TongSoCot - SoCotTrang1) / SoCotTrangLonHon1; } //set border cho cot can them for (int c = DenCotCua_DT - TuCotCua_DT + TuCot; c < TongSoCot + TuCot; c++) { for (int h = 0; h < dt.Rows.Count; h++) { fmt = xls.GetCellVisibleFormatDef(h + TuHang, c); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(h + TuHang, c, xls.AddFormat(fmt)); } } String[] arrMapCot = MapCotCoDinh.Split('|'); String[] arrCot_Excel = arrMapCot[0].Split(','); String[] arrCot_DT = arrMapCot[1].Split(','); #region Fill dữ liệu những cột động _TuCot = TuCot; for (int c = 0; c < TongSoCot; c++) { Type _Type = typeof(String); if (c + TuCotCua_DT < DenCotCua_DT) _Type = dt.Columns[c + TuCotCua_DT].DataType; switch (_Type.ToString()) { case "System.Decimal": fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; break; default: fmt = xls.GetCellVisibleFormatDef(TuHang, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; break; } for (int h = 0; h < dt.Rows.Count; h++) { if (Convert.ToString(dt.Rows[h]["sNG"]) == "" && Convert.ToString(dt.Rows[h]["sTM"]) == "") { fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; } else { fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; } if (Convert.ToString(dt.Rows[h]["sTTM"]) == "") { fmt.Font.Style = TFlxFontStyles.Bold; } else { fmt.Font.Style = TFlxFontStyles.None; } GiaTriO = null; xls.SetCellFormat(h + TuHang, _TuCot, xls.AddFormat(fmt)); if (c + TuCotCua_DT < DenCotCua_DT) xls.SetCellValue(h + TuHang, _TuCot, dt.Rows[h][c + TuCotCua_DT]); } _TuCot++; } #endregion #region Fill dữ liệu những cột tĩnh _TuCot = TuCot; String KyTu1, strSum; for (int i = 0; i < dt.Rows.Count; i++) { for (int c = 0; c < arrCot_Excel.Length; c++) { fmt = xls.GetCellVisibleFormatDef(TuHang + i, Convert.ToInt32(arrCot_Excel[c])); if (c >= arrCot_Excel.Length - 3) { fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; } else { fmt.Font.Name = "Times New Roman"; fmt.Font.Size20 = 200; fmt.Font.Family = 1; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; } fmt.WrapText = true; xls.AutofitRow(i + TuHang, true, 1); GiaTriO = null; if (c < arrCot_DT.Length) GiaTriO = dt.Rows[i][Convert.ToInt16(arrCot_DT[c])]; if (Convert.ToString(dt.Rows[i]["sTM"]) == "")//nếu cột TM=""; { fmt.Font.Style = TFlxFontStyles.Bold; } else { if (c < 3) { GiaTriO = null; } else { } if (Convert.ToString(dt.Rows[i]["sTTM"]) != "") //nếu cột TTM=="", { if (c < 4) { GiaTriO = null; } } else { fmt.Font.Style = TFlxFontStyles.Bold;//Set Bold cho hàng TM } } if (Convert.ToString(dt.Rows[i]["sNG"]) == "" && Convert.ToString(dt.Rows[i]["sTM"]) == "") { fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; } else { fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted; fmt.Borders.Bottom.Color = TExcelColor.Automatic; } if (c < arrCot_Excel.Length) { xls.SetCellFormat(i + TuHang, Convert.ToInt16(arrCot_Excel[c]), xls.AddFormat(fmt)); xls.SetCellValue(i + TuHang, Convert.ToInt16(arrCot_Excel[c]), GiaTriO); } else { xls.SetCellFormat(i + TuHang, c, xls.AddFormat(fmt)); } } } //set cột tổng cuối báo cáo fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 1); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 1, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 2); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 3); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 4); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 5); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 6); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, 7); fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang, 7, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang, 7, "Cộng: Trong Kỳ: "); _TuCot = TuCot; for (int i = 0; i <= TongSoCot + 2; i++) { fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang, _TuCot - 3 + i, xls.AddFormat(fmt)); KyTu1 = HamChung.ExportExcel_MaCot(_TuCot - 3 + i); if (TongSoHang > 1) { strSum = String.Format("=SUMIF(F{1}:F{3},\"<>\"&\"\",{0}{1}:{2}{3})", KyTu1, TuHang, KyTu1, TongSoHang + TuHang - 1); xls.SetCellFormat(TongSoHang + TuHang, _TuCot - 3 + i, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang, _TuCot - 3 + i, new TFormula(strSum)); } } //XOA gia tri cot luy ke den xls.SetCellValue(TongSoHang + TuHang, _TuCot - 1, ""); // set cot den ky nay fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 1); fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 1, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 2); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 2, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 3); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 4); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 4, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + 1 + TuHang, 5); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 5, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 6); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(TongSoHang + TuHang + 1, 6, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 7); fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang + 1, 7, xls.AddFormat(fmt)); xls.SetCellValue(TongSoHang + TuHang + 1, 7, " Đến kỳ này:"); _TuCot = TuCot; for (int i = 0; i < TongSoCot + 3; i++) { fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Font.Size20 = 200; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; fmt.Format = "#,##0;-#,##0;;@"; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang + 1, _TuCot - 3 + i, xls.AddFormat(fmt)); KyTu1 = HamChung.ExportExcel_MaCot(_TuCot - 3 + i); String ChiTieu = String.Format("=H{0}", TongSoHang + TuHang); if (TongSoHang > 1) { xls.SetCellValue(TongSoHang + TuHang + 1, _TuCot - 3, new TFormula(ChiTieu)); if (i < dtLuyKe.Columns.Count && dtLuyKe.Rows.Count > 0) { xls.SetCellValue(TongSoHang + TuHang + 1, _TuCot - 1 + i, dtLuyKe.Rows[0][i]); } } } _TuCot = TuCot; fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, _TuCot + SoCotTrang1 - 1); fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang, _TuCot + SoCotTrang1 - 1, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, _TuCot + SoCotTrang1 - 1); fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang + 1, _TuCot + SoCotTrang1 - 1, xls.AddFormat(fmt)); _TuCot = TuCot + SoCotTrang1; for (int i = 1; i < SoTrang; i++) { fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang, _TuCot + SoCotTrangLonHon1 * i - 1); fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang, _TuCot + SoCotTrangLonHon1 * i - 1, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, _TuCot + SoCotTrangLonHon1 * i - 1); fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.Font.Style = TFlxFontStyles.Bold; xls.SetCellFormat(TongSoHang + TuHang + 1, _TuCot + SoCotTrangLonHon1 * i - 1, xls.AddFormat(fmt)); } TXlsNamedRange Range; Range = new TXlsNamedRange("KeepRows_1_", 0, 1, TuHang - 4, 1, TongSoHang + TuHang + 3, FlxConsts.Max_Columns + 1, 0); xls.SetNamedRange(Range); Range = new TXlsNamedRange("KeepRows_2_", 0, 1, TongSoHang + TuHang, 1, TongSoHang + TuHang + 3, FlxConsts.Max_Columns + 1, 0); xls.SetNamedRange(Range); #endregion }
private int NoColorBold(XlsFile xls, int row, int col) { TFlxFormat tf4 = xls.GetCellVisibleFormatDef(row, col); TFlxFont tfont = new TFlxFont(); tfont.Style = TFlxFontStyles.Bold; tf4.Font = tfont; return xls.AddFormat(tf4); }
public static XlsFile TaoTieuDe(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotCuaMotTrang) { xls.NewFile(1); //Create a new Excel file with 1 sheet. xls.ActiveSheet = 1; //Set the sheet we are working in. xls.SheetName = "BaoCao";//Set the names of the sheets //Global Workbook Options xls.OptionsAutoCompressPictures = true; //Tính số trang và số cột cần thêm để đủ một trang int SoCotDu = (DenCotCua_DT - TuCotCua_DT) % SoCotCuaMotTrang; int SoCotCanThem = 0; int TongSoCot = 0; if (SoCotDu != 0) { SoCotCanThem = SoCotCuaMotTrang - SoCotDu; } TongSoCot = DenCotCua_DT + SoCotCanThem - TuCotCua_DT; int SoTrang = TongSoCot / SoCotCuaMotTrang; int _C = TuCot; //Styles. TFlxFormat StyleFmt; StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Good, 0)); StyleFmt.Font.CharSet = 163; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Good, 0), StyleFmt); StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0)); StyleFmt.Font.Name = "Calibri"; StyleFmt.Font.Size20 = 220; StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1); StyleFmt.Font.Family = 2; StyleFmt.Font.CharSet = 163; StyleFmt.Format = "_-* #,##0.00\\ _₫_-;\\-* #,##0.00\\ _₫_-;_-* \"-\"??\\ _₫_-;_-@_-"; xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt); //Named Ranges TXlsNamedRange Range; string RangeName; RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles); Range = new TXlsNamedRange(RangeName, 1, 32, "='BaoCao'!$A:$B,'BaoCao'!$1:$7"); //You could also use: Range = new TXlsNamedRange(RangeName, 1, 0, 0, 0, 0, 0, 32); xls.SetNamedRange(Range); #region //Printer Settings THeaderAndFooter HeadersAndFooters = new THeaderAndFooter(); HeadersAndFooters.AlignMargins = true; HeadersAndFooters.ScaleWithDoc = true; HeadersAndFooters.DiffFirstPage = false; HeadersAndFooters.DiffEvenPages = false; HeadersAndFooters.DefaultHeader = ""; HeadersAndFooters.DefaultFooter = ""; HeadersAndFooters.FirstHeader = ""; HeadersAndFooters.FirstFooter = ""; HeadersAndFooters.EvenHeader = ""; HeadersAndFooters.EvenFooter = ""; xls.SetPageHeaderAndFooter(HeadersAndFooters); //You can set the margins in 2 ways, the one commented here or the one below: // TXlsMargins PrintMargins = xls.GetPrintMargins(); // PrintMargins.Left = 0.236220472440945; // PrintMargins.Top = 0.748031496062992; // PrintMargins.Right = 0.236220472440945; // PrintMargins.Bottom = 0.748031496062992; // PrintMargins.Header = 0.31496062992126; // PrintMargins.Footer = 0.31496062992126; // xls.SetPrintMargins(PrintMargins); xls.SetPrintMargins(new TXlsMargins(0.236220472440945, 0.748031496062992, 0.236220472440945, 0.748031496062992, 0.31496062992126, 0.31496062992126)); xls.PrintXResolution = 600; xls.PrintYResolution = 0; xls.PrintOptions = TPrintOptions.LeftToRight; xls.PrintPaperSize = TPaperSize.A4; //Printer Driver Settings are a blob of data specific to a printer //This code is commented by default because normally you do not want to hard code the printer settings of an specific printer. // byte[] PrinterData = { // 0x00, 0x00, 0x5C, 0x00, 0x5C, 0x00, 0x31, 0x00, 0x39, 0x00, 0x32, 0x00, 0x2E, 0x00, 0x31, 0x00, 0x36, 0x00, 0x38, 0x00, 0x2E, 0x00, 0x31, 0x00, 0x34, 0x00, 0x30, 0x00, 0x2E, 0x00, 0x37, 0x00, 0x5C, 0x00, 0x48, 0x00, 0x50, 0x00, 0x20, 0x00, 0x4C, 0x00, 0x61, 0x00, 0x73, 0x00, 0x65, 0x00, 0x72, 0x00, // 0x4A, 0x00, 0x65, 0x00, 0x74, 0x00, 0x20, 0x00, 0x4D, 0x00, 0x31, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x15, 0x06, 0xDC, 0x00, 0x34, 0x03, 0x0F, 0xDF, 0x00, 0x00, 0x02, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x00, 0x00, 0x01, 0x00, 0x07, 0x00, 0x58, 0x02, 0x01, 0x00, 0x01, 0x00, 0x58, 0x02, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xBF, 0x03, 0xC1, 0x02, 0x00, 0x00, 0x00, 0x00, 0x08, 0x04, 0xF8, 0x02, 0x00, 0x00, 0x00, 0x00, 0x52, 0x04, 0x2E, 0x03, 0x00, 0x00, 0x00, 0x00, 0x9C, 0x04, 0x64, 0x03, 0x00, 0x00, 0x00, 0x00, 0xE6, 0x04, 0x9A, 0x03, 0x00, 0x00, 0x00, 0x00, 0x2F, 0x05, 0xD1, 0x03, // 0x00, 0x00, 0x00, 0x00, 0x79, 0x05, 0x07, 0x04, 0x00, 0x00, 0x00, 0x00, 0xC3, 0x05, 0x3D, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x53, 0x44, 0x44, 0x4D, 0x00, 0x06, 0x00, 0x00, 0x00, 0x06, 0x00, 0x00, 0x48, 0x50, 0x20, 0x4C, 0x61, 0x73, 0x65, 0x72, 0x4A, 0x65, 0x74, 0x20, 0x4D, 0x31, 0x33, 0x31, // 0x39, 0x66, 0x20, 0x4D, 0x46, 0x50, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, // 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, // 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x1A, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x0F, 0x00, // 0x00, 0x00, 0x5A, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0x80, 0x80, 0x00, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0x00, // 0xFF, 0x00, 0xFF, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x28, 0x00, 0x00, 0x00, 0x64, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xDE, 0x03, 0x00, 0x00, 0xDE, 0x03, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x59, 0xC3, 0x13, 0xAE, 0x34, 0x03, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, // 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 // }; // TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData); // xls.SetPrinterDriverSettings(PrinterDriverSettings); #endregion #region //Set up rows and columns xls.DefaultColWidth = 2340; xls.SetColWidth(1, 1609); //(5.54 + 0.75) * 256 TFlxFormat ColFmt; ColFmt = xls.GetFormat(xls.GetColFormat(1)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(1, xls.AddFormat(ColFmt)); xls.SetColWidth(2, 6326); //(23.96 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(2)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(2, xls.AddFormat(ColFmt)); xls.SetColWidth(3, 4205); //(15.68 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(3)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(3, xls.AddFormat(ColFmt)); xls.SetColWidth(4, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(4)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(4, xls.AddFormat(ColFmt)); xls.SetColWidth(5, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(5)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(5, xls.AddFormat(ColFmt)); xls.SetColWidth(6, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(6)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(6, xls.AddFormat(ColFmt)); xls.SetColWidth(7, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(7)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(7, xls.AddFormat(ColFmt)); xls.SetColWidth(8, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(8)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(8, xls.AddFormat(ColFmt)); xls.SetColWidth(9, 4059); //(15.11 + 0.75) * 256 ColFmt = xls.GetFormat(xls.GetColFormat(9)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(9, xls.AddFormat(ColFmt)); #endregion #region set độ rộng cột for (int c = 0; c < TongSoCot + SoTrang; c++) { ColFmt = xls.GetFormat(xls.GetColFormat(c + TuCot)); ColFmt.Font.Name = "Times New Roman"; ColFmt.Font.Family = 1; ColFmt.Font.CharSet = 0; xls.SetColFormat(c + TuCot, xls.AddFormat(ColFmt)); xls.SetColWidth(c + TuCot + 1, 4059); } #endregion xls.DefaultRowHeight = 300; xls.SetRowHeight(7, 1545); xls.SetRowHeight(8, 360); #region //Merged Cells xls.MergeCells(6, 1, 7, 1); xls.MergeCells(6, 3, 7, 3); xls.MergeCells(6, 4, 6, 9); xls.MergeCells(5, 1, 5, 2); xls.MergeCells(1, 1, 1, 2); xls.MergeCells(1, 3, 1, 9); xls.MergeCells(2, 3, 2, 9); for (int t = 0; t < SoTrang; t++) { xls.MergeCells(6, _C, 6, _C + SoCotCuaMotTrang-1); _C = _C + SoCotCuaMotTrang; } #endregion //Set the cell values TFlxFormat fmt; #region fmt = xls.GetCellVisibleFormatDef(1, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.HAlignment = THFlxAlignment.left; xls.SetCellFormat(1, 1, xls.AddFormat(fmt)); xls.SetCellValue(1, 1, "BỘ QUỐC PHÒNG"); fmt = xls.GetCellVisibleFormatDef(1, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.HAlignment = THFlxAlignment.center; xls.SetCellValue(1, 3, "DỰ TOÁN CHI NGÂN SÁCH QUỐC PHÒNG NĂM <#Nam>"); xls.SetCellFormat(1, 2, xls.AddFormat(fmt)); xls.SetCellFormat(1, 3, xls.AddFormat(fmt)); xls.SetCellFormat(1, 4, xls.AddFormat(fmt)); xls.SetCellFormat(1, 5, xls.AddFormat(fmt)); xls.SetCellFormat(1, 6, xls.AddFormat(fmt)); xls.SetCellFormat(1, 7, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(2, 3); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(2, 3, xls.AddFormat(fmt)); xls.SetCellValue(2, 3, "(Phần chi cho doanh nghiệp)"); fmt = xls.GetCellVisibleFormatDef(5, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(5, 1, xls.AddFormat(fmt)); xls.SetCellValue(5, 1, "LNS: 1050000 Loại: 460 Khoản: 468"); fmt = xls.GetCellVisibleFormatDef(5, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(5, 2, xls.AddFormat(fmt)); //fmt = xls.GetCellVisibleFormatDef(5, 8); //fmt.Font.Name = "Times New Roman"; //fmt.Font.Family = 1; //fmt.Font.CharSet = 0; //xls.SetCellFormat(5, 8, xls.AddFormat(fmt)); //xls.SetCellValue(5, 8, "Đơn vị tính:1.000 đ"); fmt = xls.GetCellVisibleFormatDef(6, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(6, 1, xls.AddFormat(fmt)); xls.SetCellValue(6, 1, "STT"); fmt = xls.GetCellVisibleFormatDef(6, 2); fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.right; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(6, 2, xls.AddFormat(fmt)); //xls.SetCellValue(6, 2, "Nội dung ngân sách"); fmt = xls.GetCellVisibleFormatDef(6, 3); fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(6, 3, xls.AddFormat(fmt)); xls.SetCellValue(6, 3, "Tổng cộng"); fmt = xls.GetCellVisibleFormatDef(7, 1); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(7, 1, xls.AddFormat(fmt)); xls.SetCellFormat(7, 3, xls.AddFormat(fmt)); fmt = xls.GetCellVisibleFormatDef(7, 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.left; fmt.VAlignment = TVFlxAlignment.center; xls.SetCellFormat(7, 2, xls.AddFormat(fmt)); xls.SetCellValue(7, 2, "Đơn vị"); #region //Tạo tiêu đề cột _C = TuCot; for (int c = 0; c < SoTrang; c++) { xls.MergeCells(1, _C, 1, _C + SoCotCuaMotTrang-1); fmt = xls.GetCellVisibleFormatDef(1, _C); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(1, _C, xls.AddFormat(fmt)); xls.SetCellValue(1, _C, "DỰ TOÁN CHI NGÂN SÁCH QUỐC PHÒNG NĂM <#Nam>"); xls.MergeCells(2, _C, 2, _C + SoCotCuaMotTrang-1); fmt = xls.GetCellVisibleFormatDef(2, _C); fmt.Font.Name = "Times New Roman"; fmt.Font.Style = TFlxFontStyles.Bold; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.HAlignment = THFlxAlignment.center; xls.SetCellFormat(2, _C, xls.AddFormat(fmt)); xls.SetCellValue(2, _C, "(Phần chi cho doanh nghiệp)"); fmt = xls.GetCellVisibleFormatDef(5, _C + SoCotCuaMotTrang - 2); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; xls.SetCellFormat(5, _C + SoCotCuaMotTrang - 1, xls.AddFormat(fmt)); xls.SetCellValue(5, _C + SoCotCuaMotTrang - 1, "Đơn vị tính:1.000 đ"); _C = _C + SoCotCuaMotTrang; } int csdt = TuCotCua_DT; String TenCot = ""; for (int i = 0; i < TongSoCot ; i++) { fmt = xls.GetCellVisibleFormatDef(7, TuCot); fmt.Font.Name = "Times New Roman"; fmt.Font.Family = 1; fmt.Font.CharSet = 0; fmt.Borders.Left.Style = TFlxBorderStyle.Thin; fmt.Borders.Left.Color = TExcelColor.Automatic; fmt.Borders.Right.Style = TFlxBorderStyle.Thin; fmt.Borders.Right.Color = TExcelColor.Automatic; fmt.Borders.Top.Style = TFlxBorderStyle.Thin; fmt.Borders.Top.Color = TExcelColor.Automatic; fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin; fmt.Borders.Bottom.Color = TExcelColor.Automatic; fmt.HAlignment = THFlxAlignment.center; fmt.VAlignment = TVFlxAlignment.center; fmt.WrapText = true; xls.SetCellFormat(7, TuCot, xls.AddFormat(fmt)); xls.SetCellFormat(6, TuCot+i, xls.AddFormat(fmt)); xls.SetCellValue(6, TuCot+i, "Trong đó"); TenCot = ""; if (csdt <= DenCotCua_DT) TenCot = dt.Columns[csdt].ColumnName; xls.SetCellValue(7, TuCot+i, TenCot); xls.SetCellFormat(7, TuCot+i, xls.AddFormat(fmt)); csdt++; } #endregion #endregion //Cell selection and scroll position. xls.SelectCell(17, 2, false); //Protection TSheetProtectionOptions SheetProtectionOptions; SheetProtectionOptions = new TSheetProtectionOptions(false); SheetProtectionOptions.SelectLockedCells = true; SheetProtectionOptions.SelectUnlockedCells = true; xls.Protection.SetSheetProtection(null, SheetProtectionOptions); return xls; }
private int TopBorderOnly(XlsFile xls, int row, int col) { TFlxFormat tf3 = xls.GetCellVisibleFormatDef(row, col); TFlxBorders tfBorderF = new TFlxBorders(); TFlxOneBorder oborder = new TFlxOneBorder(); oborder.Style = TFlxBorderStyle.Thin; tfBorderF.Top = oborder; tf3.Borders = tfBorderF; return xls.AddFormat(tf3); }
public void Report_3(IEnumerable<GetReport3_Levy_Products_Result> daily_trans, string filterType, string exchange_rate, string typ) { //var bK = daily_trans; //if (bK.Count() <= 0) //{ // TempData["Warning"] = "No Data To Export"; // return; //} string Orginal_file_path = string.Empty; if (typ.Equals("pdf")) Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_3_B.xlsx"); else Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_3_A.xlsx"); //temp file name string copy_file_name = DateTime.Now.Ticks + ".xlsx"; //temp file path string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name); //copy to temp destination XlsFile report_3 = new XlsFile(Orginal_file_path); int c = 0; if (!typ.Equals("pdf")) c = 1; report_3.ActiveSheet = 1; report_3.SetCellValue(3, 3 + c, GetDecimal(exchange_rate)); report_3.SetCellValue(2, 1 + c, filterType); //get color 1 int col1 = report_3.AddFormat(GetColor1(report_3.GetCellVisibleFormatDef(6, 2))); //get colo1 2 int col2 = report_3.AddFormat(GetColor2(report_3.GetCellVisibleFormatDef(6, 2))); int style = col1; int i = 6; string category_1 = string.Empty; int dataExist = 0; int j = 0; foreach (var item in daily_trans) { dataExist = 1; if (i != 6 && category_1.Equals(item.Category_Name)) report_3.MergeCells(i - 1, 1, i, 1); else { if (style == col1) style = col2; else style = col1; } //style = res_arr[j]; category_1 = item.Category_Name; report_3.SetCellValue(i, 1 + c, item.Category_Name, style); if (item.Goods_Name.Equals("All")) { //get current style and add bold TFlxFormat tformat = GetBold(report_3.GetCellVisibleFormatDef(i, 1 + c)); int res2 = report_3.AddFormat(tformat); //end style modification report_3.SetCellValue(i, 2 + c, item.Goods_Name, res2); report_3.SetCellValue(i, 3 + c, item.Unit, res2); report_3.SetCellValue(i, 4 + c, item.Total_Quantity, res2); report_3.SetCellValue(i, 5 + c, item.Total_Amount_in_SOS, res2); report_3.SetCellValue(i, 6 + c, item.Total_Amount_in_USD, res2); } else { report_3.SetCellValue(i, 2 + c, item.Goods_Name, style); report_3.SetCellValue(i, 3 + c, item.Unit, style); report_3.SetCellValue(i, 4 + c, item.Total_Quantity, style); report_3.SetCellValue(i, 5 + c, item.Total_Amount_in_SOS, style); report_3.SetCellValue(i, 6 + c, item.Total_Amount_in_USD, style); } i++; } if (dataExist <= 0) { TempData["Warning"] = "No Data To Export"; return; } else { report_3.PrintToFit = true; report_3.PrintPaperSize = TPaperSize.A4; TWorkbookProtectionOptions protoptions = new TWorkbookProtectionOptions(); protoptions.Window = false; protoptions.Structure = false; TSharedWorkbookProtectionOptions op = new TSharedWorkbookProtectionOptions(); op.SharingWithTrackChanges = false; report_3.Protection.SetSharedWorkbookProtection("XXXX", op); report_3.Protection.SetWorkbookProtection("XXXX", protoptions); report_3.Protection.SetModifyPassword("XXXX", true, "XXXXXX"); report_3.Protection.SetWorkbookProtectionOptions(protoptions); report_3.Save(copy_file_path); if (typ.Equals("pdf")) GeneratePDF(report_3, "Report_3_"+filterType); else { Response.AppendHeader("Content-Disposition", "attachment; filename=Report_3_"+filterType+".xlsx"); Response.TransmitFile(copy_file_path); Response.End(); } } }
// // GET: /Home/ExportData_ToExcel/W0009 public ActionResult E_ExportData_ToExcel(int way_bill_id) { if (Session["login_status"] != null) { //temp file name string copy_file_name = DateTime.Now.Ticks + ".xlsx"; //template file name string Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Bolleto_Dogonale_Export.xlsx"); //temp file path string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name); XlsFile bolleto = new XlsFile(Orginal_file_path); bolleto.ActiveSheet = 1; var bolleto_code = (from i in db.Exports where i.e_way_bill_id == way_bill_id select i.e_bollete_dogonale_code).First(); var j = 0; var bollete_dogonale = (from e in db.Exports join w in db.E_Way_Bill on e.e_way_bill_id equals w.e_way_bill_id join sd in db.Ship_Departure on e.ship_departure_id equals sd.ship_departure_id join cpc in db.E_Calculated_Payment_Config on w.e_way_bill_id equals cpc.way_bill_id where e.e_way_bill_id == way_bill_id select new Bolleto_DogonaleModel { way_bill_code = w.e_way_bill_code, import_code = e.export_code, ship_arrival_code = sd.ship_departure_code, bolleto_dogonale_code = e.e_bollete_dogonale_code, date = cpc.calculated_date }); foreach (var item in bollete_dogonale) { bolleto.SetCellValue(2, 3, item.bolleto_dogonale_code); bolleto.SetCellValue(3, 3, item.way_bill_code); bolleto.SetCellValue(4, 3, item.import_code); bolleto.SetCellValue(5, 3, item.ship_arrival_code); bolleto.SetCellValue(6, 3, item.date.ToString("yyyy-MM-dd")); //bolleto.SetCellFormat(1, 1, 8, 7, 1); } var grand_total = db.E_Get_Grand_Total(way_bill_id, 100); string grand_total_value = ""; foreach (var v in grand_total) { grand_total_value = v; } db.E_Display_Tax_Details(way_bill_id, 100); var display1 = from d1 in db.E_TempDisplay1 where d1.way_bill_id == way_bill_id select d1; var display2 = (from d2 in db.E_TempDisplay2 where d2.way_bill_id == way_bill_id select d2).ToList(); TFlxFormat tformatTitle = bolleto.GetCellVisibleFormatDef(9, 1); int titleFormat = bolleto.AddFormat(tformatTitle); TFlxFont tfont = new TFlxFont(); tfont.Style = TFlxFontStyles.Bold; TFlxFormat tformatright = bolleto.GetCellVisibleFormatDef(10, 6); tformatright.Font = tfont; int Formatright = bolleto.AddFormat(tformatright); int iRowCnt = 9; foreach (var item in display1) { // SHOW COLUMNS ON THE TOP. bolleto.SetCellValue(iRowCnt, 1, "Goods", titleFormat); bolleto.SetCellValue(iRowCnt, 2, "Unit Price", titleFormat); bolleto.SetCellValue(iRowCnt, 3, "Calculated Tariff", titleFormat); bolleto.SetCellValue(iRowCnt, 4, "Total Quantity", titleFormat); bolleto.SetCellValue(iRowCnt, 5, "Unit Of Measure", titleFormat); bolleto.SetCellValue(iRowCnt, 6, "Base Taxation", titleFormat); bolleto.SetCellValue(iRowCnt, 7, "Total", titleFormat); //bolleto.SetCellFormat(iRowCnt, 1, iRowCnt, 7, 1); var x = "A" + iRowCnt; var y = "G" + iRowCnt; //xlWorkSheetToExport.Range[x, y].Font.Size = 11; //xlWorkSheetToExport.Range[x, y].Font.Bold = true; iRowCnt = iRowCnt + 1; bolleto.SetCellValue(iRowCnt, 1, item.goods_name); bolleto.SetCellValue(iRowCnt, 2, item.toal_Quantity); bolleto.SetCellValue(iRowCnt, 3, item.Unit_Code); bolleto.SetCellValue(iRowCnt, 4, item.Unit_Price); bolleto.SetCellValue(iRowCnt, 5, item.calculated_Tariff); bolleto.SetCellValue(iRowCnt, 6, item.Base_Taxation); bolleto.SetCellValue(iRowCnt, 7, item.Total); iRowCnt = iRowCnt + 2; x = "A" + iRowCnt; y = "G" + iRowCnt; //xlWorkSheetToExport.Range[x, y].Font.Size = 11; //xlWorkSheetToExport.Range[x, y].Font.Bold = true; bolleto.SetCellValue(iRowCnt, 2, "Levy Name", titleFormat); bolleto.SetCellValue(iRowCnt, 3, "Levy Type", titleFormat); bolleto.SetCellValue(iRowCnt, 4, "Levy", titleFormat); bolleto.SetCellValue(iRowCnt, 5, "Actual Levy", titleFormat); bolleto.SetCellValue(iRowCnt, 6, "Total Levy", titleFormat); bolleto.SetCellValue(iRowCnt, 7, ""); //bolleto.SetCellFormat(iRowCnt, 1, iRowCnt, 7, 1); iRowCnt = iRowCnt + 1; for (var l = 0; l < display2.Count(); l++) { if (l == 0 && j != 0) { l = j; } if (item.Goods_Id != display2[l].Goods_Id) { break; } j++; if (display2[l].Levy_Name == "Total") { bolleto.SetCellValue(iRowCnt, 2, display2[l].Levy_Name, titleFormat); bolleto.SetCellValue(iRowCnt, 3, display2[l].Levy_Type, titleFormat); bolleto.SetCellValue(iRowCnt, 4, display2[l].Levy, titleFormat); //bolleto.SetCellValue(iRowCnt, 4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; bolleto.SetCellValue(iRowCnt, 5, display2[l].Actua_Levy, Formatright); bolleto.SetCellValue(iRowCnt, 6, display2[l].Total_Levi, Formatright); bolleto.SetCellValue(iRowCnt, 7, display2[l].Sum_Levies, Formatright); //bolleto.SetCellFormat(iRowCnt, 1, iRowCnt, 7, 1); iRowCnt = iRowCnt + 1; } else { bolleto.SetCellValue(iRowCnt, 2, display2[l].Levy_Name); bolleto.SetCellValue(iRowCnt, 3, display2[l].Levy_Type); bolleto.SetCellValue(iRowCnt, 4, display2[l].Levy); //bolleto.SetCellValue(iRowCnt, 4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight; bolleto.SetCellValue(iRowCnt, 5, display2[l].Actua_Levy); bolleto.SetCellValue(iRowCnt, 6, display2[l].Total_Levi); bolleto.SetCellValue(iRowCnt, 7, display2[l].Sum_Levies); iRowCnt = iRowCnt + 1; } } iRowCnt = iRowCnt + 1; } var get_payment_config = db.E_Calculated_Payment_Config.Where(cpc => cpc.way_bill_id == way_bill_id).ToList(); foreach (var item in get_payment_config) { TempData["sos_amount"] = item.calculated_sos_amount; TempData["usd_amount"] = item.calculated_usd_amount; TempData["sos"] = item.calculated_sos_part; TempData["usd"] = item.calculated_usd_part; } iRowCnt = iRowCnt + 3; bolleto.SetCellValue(iRowCnt, 4, "USD Amount (" + TempData["usd"] + "%)", titleFormat); bolleto.SetCellValue(iRowCnt, 5, TempData["usd_amount"], titleFormat); iRowCnt++; bolleto.SetCellValue(iRowCnt, 4, "SOS Amount (" + TempData["sos"] + "%)", titleFormat); bolleto.SetCellValue(iRowCnt, 5, TempData["sos_amount"], titleFormat); iRowCnt++; bolleto.SetCellValue(iRowCnt,7,"Date : " + DateTime.Now.ToString("yyyy-MM-dd")); iRowCnt++; bolleto.SetCellValue(iRowCnt, 7, "User : "******"user_name"].ToString()); var save_name = "Bolleto_Dogonale_Export_" + bolleto_code + ".xlsx"; // SAVE THE FILE IN A FOLDER. //bolleto.Save(Path.Combine(Server.MapPath("~/App_Data"), copy_file_name)); bolleto.Save(copy_file_path); this.DownLoadFile_Export(save_name, copy_file_path); return RedirectToAction("Index"); } else { return RedirectToAction("../Home"); } }
//public void Report_4(DateTime Report_date, string typ) //{ // string last_date = "9999/12/31"; // var exchangerate = (from d in db.Exchange_Rate where d.end_date == last_date && d.from_currency_id == 100 && d.to_currency_id == 106 select d.exchange_rate1).FirstOrDefault();//db.GetExchangeRate(dtExchangeRateDate).FirstOrDefault(); // string Orginal_file_path = string.Empty; // if (!typ.Equals("pdf")) // Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_4_B.xlsx"); // else // Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_4_A.xlsx"); // //temp file name // string copy_file_name = DateTime.Now.Ticks + ".xlsx"; // //temp file path // string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name); // //open file // XlsFile report_4 = new XlsFile(Orginal_file_path); // //sheet always 1 // report_4.ActiveSheet = 1; // int tformat_head = report_4.AddFormat(report_4.GetCellVisibleFormatDef(10, 3)); // int tformat_col1 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 3)); // int tformat_col2 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 4)); // string Header = string.Empty; // Header = "Daily Bolleto Dogonale Report :" + Report_date.ToString("dd/MMM/yyyy"); // report_4.SetCellValue(10, 3, Header, tformat_head); // //get import data // var daily_trans_import = db.Report_4_Import(Report_date); // //get import data count // var daily_trans_import_count = db.Report_4_Import(Report_date).Count(); // //skip if no data // if (daily_trans_import_count <= 0) // goto skipImport; // TFlxFormat tf = report_4.GetCellVisibleFormatDef(12, 3); // tf.HAlignment = THFlxAlignment.center; // int tformat_BldandCntr = report_4.AddFormat(tf); // TFlxFormat tf2 = report_4.GetCellVisibleFormatDef(12, 4); // tf2.HAlignment = THFlxAlignment.center; // int tformat_BldandCntr2 = report_4.AddFormat(tf2); // // DataTable dtRows = LinqQueryToDataTable(daily_trans.ToList()); // int i = 12; // int s = 1; // foreach (var item in daily_trans_import) // { // report_4.SetCellValue(i, 3, s.ToString(), tformat_BldandCntr); // report_4.SetCellValue(i, 4, item.Code, tformat_BldandCntr2); // report_4.SetCellValue(i, 5, item.Name, tformat_col1); // report_4.SetCellValue(i, 6, item.Status, tformat_BldandCntr2); // report_4.SetCellValue(i, 7, item.USD, tformat_col1); // report_4.SetCellValue(i, 8, item.SOS, tformat_col2); // i++; // s++; // } // //skiped import //skipImport: // int tformat_head2 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(10, 3)); // int tformat_col12 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 3)); // int tformat_col22 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 4)); // string Header2 = string.Empty; // Header = "Daily Bolleto Dogonale Report :" + Report_date.ToString("dd/MMM/yyyy"); // report_4.SetCellValue(10, 3, Header2, tformat_head2); // TFlxFormat tfa = report_4.GetCellVisibleFormatDef(12, 3); // tfa.HAlignment = THFlxAlignment.center; // int tformat_BldandCntra = report_4.AddFormat(tfa); // TFlxFormat tf2a = report_4.GetCellVisibleFormatDef(12, 4); // tf2a.HAlignment = THFlxAlignment.center; // int tformat_BldandCntr2a = report_4.AddFormat(tf2a); // //get export dtaa // var daily_trans_export = db.Report_4_Export(Report_date); // //get export count // var daily_trans_export_count = db.Report_4_Export(Report_date).Count(); // report_4.ActiveSheet = 2; // //skip export if no data // if (daily_trans_export_count <= 0) // goto skipExport; // //print export // i = 12; // s = 1; // foreach (var item in daily_trans_export) // { // report_4.SetCellValue(i, 3, s.ToString(), tformat_BldandCntra); // report_4.SetCellValue(i, 4, item.Code, tformat_BldandCntr2a); // report_4.SetCellValue(i, 5, item.Name, tformat_col12); // report_4.SetCellValue(i, 6, item.Status, tformat_BldandCntr2a); // report_4.SetCellValue(i, 7, item.USD, tformat_col12); // report_4.SetCellValue(i, 8, item.SOS, tformat_col22); // i++; // s++; // } // //skipped export //skipExport: // //if no import and export return alert msg // if (daily_trans_import_count <= 0 && daily_trans_export_count <= 0) // { // TempData["Warning"] = "No Data To Export"; // return; // } // else // { // //else print // report_4.PrintToFit = true; // report_4.PrintPaperSize = TPaperSize.A4; // TWorkbookProtectionOptions protoptions = new TWorkbookProtectionOptions(); // protoptions.Window = false; // protoptions.Structure = false; // TSharedWorkbookProtectionOptions op = new TSharedWorkbookProtectionOptions(); // op.SharingWithTrackChanges = false; // report_4.Protection.SetSharedWorkbookProtection("XXXX", op); // report_4.Protection.SetWorkbookProtection("XXXX", protoptions); // report_4.Protection.SetModifyPassword("XXXX", true, "XXXXXX"); // report_4.Protection.SetWorkbookProtectionOptions(protoptions); // report_4.Save(copy_file_path); // if (typ.Equals("pdf")) // GeneratePDF(report_4, "Report_4"); // else // { // Response.AppendHeader("Content-Disposition", "attachment; filename=Report_4.xlsx"); // Response.TransmitFile(copy_file_path); // Response.End(); // } // } //} public void Report_4(IEnumerable<Report_4_Import_Result> daily_trans_import, IEnumerable<Report_4_Export_Result> daily_trans_Export, string filterType, string exchange_rate, string typ, string copy_file_path, string Orginal_file_path) { int c = 1; //open File XlsFile report_4 = new XlsFile(Orginal_file_path); //sheet always 1 report_4.ActiveSheet = 1; int tformat_head = report_4.AddFormat(report_4.GetCellVisibleFormatDef(10, 3)); int tformat_col1 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 3)); int tformat_col2 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 4)); int tformat_col3 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 5)); int tformat_col4 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 6)); int tformat_col5 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 7)); int tformat_col6 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 8)); string Header = string.Empty; Header = "Daily Bolleto Dogonale Report :" + filterType; report_4.SetCellValue(10, 3, Header, tformat_head); DataTable dtImport = new DataTable(); //get import data // var daily_trans_import = db.Report_4_Import(Report_date); //get import data count try { if (daily_trans_import != null) dtImport = LinqQueryToDataTable(daily_trans_import.ToList()); if (dtImport.Rows.Count <= 1) goto skip_Import; } catch { goto skip_Import; } int i = 12; int s = 1; c = 2; int datarow = 12; foreach (DataRow dr in dtImport.Rows) { if (dr[1].ToString().Length > 0) { report_4.SetCellValue(i, 1 + c, s, tformat_col1); report_4.SetCellValue(i, 2 + c, dr[3].ToString(), tformat_col2); report_4.SetCellValue(i, 3 + c, dr[4].ToString(), tformat_col3); report_4.SetCellValue(i, 4 + c, dr[5].ToString(), tformat_col4); report_4.SetCellValue(i, 5 + c, GetDecimal(dr[1].ToString()), tformat_col5); report_4.SetCellValue(i, 6 + c, GetDecimal(dr[2].ToString()), tformat_col6); datarow++; } i++; s++; } skip_Import: Header = "Daily Bolleto Dogonale Report :" + filterType; DataTable dtexport = new DataTable(); //get export dtaa report_4.ActiveSheet = 2; report_4.SetCellValue(10, 3, Header, tformat_head); //skip export if no data try { if (daily_trans_Export != null) dtexport = LinqQueryToDataTable(daily_trans_Export.ToList()); if (dtexport.Rows.Count <= 1) goto skipExport; } catch { goto skipExport; } //print export i = 12; s = 1; datarow = 12; foreach (DataRow dr in dtexport.Rows) { if (dr[1].ToString().Length > 0) { report_4.SetCellValue(i, 1 + c, s, tformat_col1); report_4.SetCellValue(i, 2 + c, dr[3].ToString(), tformat_col2); report_4.SetCellValue(i, 3 + c, dr[4].ToString(), tformat_col3); report_4.SetCellValue(i, 4 + c, dr[5].ToString(), tformat_col4); report_4.SetCellValue(i, 5 + c, GetDecimal(dr[1].ToString()), tformat_col5); report_4.SetCellValue(i, 6 + c, GetDecimal(dr[2].ToString()), tformat_col6); datarow++; } i++; s++; } //doing the rest if data is null skipExport: //if no import and export return alert msg if (dtImport.Rows.Count <= 1) TempData["Warning"] = "No Data To Export"; else { report_4.Save(copy_file_path); if (typ.Equals("pdf")) GeneratePDF(report_4, "Report_4_"+filterType); else { Response.AppendHeader("Content-Disposition", "attachment; filename=Report_4_"+filterType+".xlsx"); Response.TransmitFile(copy_file_path); Response.End(); } } }