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