public static void saveAs2(DataGridView dgv, string dt)
{
if (dgv.RowCount < 1)
{
MessageBox.Show("没有可导出的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SaveFileDialog dialog = new SaveFileDialog();
dialog.RestoreDirectory = true;
dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*";
dialog.FileName = DateTime.Now.Date.ToString("yyyyMMdd") + ".xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
XlsFile xls = new XlsFile(true);
xls.NewFile(1); //Create a new Excel file with 1 sheet.
xls.ActiveSheet = 1; //Set the sheet we are working in.
xls.SheetName = "Sheet1"; //Sheet Options
//Styles.默认单元格格式
TFlxFormat StyleFmt;
StyleFmt = xls.GetStyle("Normal");
StyleFmt.Font.Name = "宋体";
StyleFmt.Font.Size20 = 160;
StyleFmt.Font.CharSet = 134;
StyleFmt.Borders.Left.Style = TFlxBorderStyle.Thin;
StyleFmt.Borders.Left.ColorIndex = xls.NearestColorIndex(Color.Black);
StyleFmt.Borders.Right.Style = TFlxBorderStyle.Thin;
StyleFmt.Borders.Right.ColorIndex = xls.NearestColorIndex(Color.Black);
StyleFmt.Borders.Top.Style = TFlxBorderStyle.Thin;
StyleFmt.Borders.Top.ColorIndex = xls.NearestColorIndex(Color.Black);
StyleFmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
StyleFmt.Borders.Bottom.ColorIndex = xls.NearestColorIndex(Color.Black);
StyleFmt.HAlignment = THFlxAlignment.center;
StyleFmt.VAlignment = TVFlxAlignment.center;
xls.SetStyle("Normal", StyleFmt);
//Printer Settings 默认打印设置
xls.SetPrintMargins(new TXlsMargins(0.7, 0.75, 0.7, 0.75, 0.3, 0.3));
xls.PrintXResolution = 600;
xls.PrintYResolution = 600;
xls.PrintOptions = TPrintOptions.None;
xls.PrintPaperSize = TPaperSize.A4;
xls.PrintHCentered = true;
//Set up rows and columns 默认单元格大小
//11.88为100像素
xls.DefaultColWidth = 2400;
xls.DefaultRowHeight = 160;
//设置表格值 加载数据
StyleFmt.Font.Size20 = 160;
//分组
xls.SetColWidth(3, 1500);
xls.MergeCells(3, 1, 4, 1);
xls.SetCellValue(3, 1, "分组");
//站点名称
xls.SetColWidth(2, 3200);
xls.MergeCells(3, 2, 4, 2);
xls.SetCellValue(3, 2, "站点名称");
//时间
xls.SetColWidth(3, 4700);
xls.MergeCells(3, 3, 4, 3);
xls.SetCellValue(3, 3, "时间");
//供水压力
xls.SetCellValue(4, 4, "供水压力");
//回水压力
xls.SetCellValue(4, 5, "回水压力");
//供水温度
xls.SetCellValue(4, 6, "供水温度");
//回水温度
xls.SetCellValue(4, 7, "回水温度");
//瞬时流量
xls.SetCellValue(4, 8, "瞬时流量");
//累计流量
xls.SetCellValue(4, 9, "累计流量");
//一次参数
xls.MergeCells(3, 4, 3, 9);
xls.SetCellValue(3, 4, "一次参数");
//供水压力
xls.SetCellValue(4, 10, "供水压力");
//回水压力
xls.SetCellValue(4, 11, "回水压力");
//供水温度
xls.SetCellValue(4, 12, "供水温度");
//回水温度
xls.SetCellValue(4, 13, "回水温度");
//供水温度基准
xls.SetCellValue(4, 14, "供温基准");
//压差设定
xls.SetCellValue(4, 15, "供压基准");
//二次参数
xls.MergeCells(3, 10, 3, 15);
xls.SetCellValue(3, 10, "二次参数");
//室外温度
xls.MergeCells(3, 16, 4, 16);
xls.SetCellValue(3, 16, "室外温度");
//调节阀开度
xls.MergeCells(3, 17, 4, 17);
xls.SetCellValue(3, 17, "调节阀开度");
//水箱水位
xls.MergeCells(3, 18, 4, 18);
xls.SetCellValue(3, 18, "水箱水位");
for (int j = 0; j < dgv.Rows.Count; j++)
{
// xls.SetCellFormat(j + 5, 1, xls.AddFormat(fmt));
xls.SetCellValue(j + 5, 1, dgv.Rows[j].Cells["GroupName"].Value);
xls.SetCellValue(j + 5, 2, dgv.Rows[j].Cells["StationName"].Value);
xls.SetCellValue(j + 5, 3, Convert.ToDateTime(dgv.Rows[j].Cells["DT"].Value).ToString("yyyy/MM/dd HH:mm"));
xls.SetCellValue(j + 5, 4, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["GP1"].Value), 2));
xls.SetCellValue(j + 5, 5, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["BP1"].Value), 2));
xls.SetCellValue(j + 5, 6, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["GT1"].Value), 2));
xls.SetCellValue(j + 5, 7, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["BT1"].Value), 2));
xls.SetCellValue(j + 5, 8, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["WI1"].Value), 2));
xls.SetCellValue(j + 5, 9, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["WS1"].Value), 2));
xls.SetCellValue(j + 5, 10, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["GP2"].Value), 2));
xls.SetCellValue(j + 5, 11, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["BP2"].Value), 2));
xls.SetCellValue(j + 5, 12, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["GT2"].Value), 2));
xls.SetCellValue(j + 5, 13, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["BT2"].Value), 2));
xls.SetCellValue(j + 5, 14, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["GTB2"].Value), 2));
xls.SetCellValue(j + 5, 15, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["BPB2"].Value), 2));
xls.SetCellValue(j + 5, 16, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["OT"].Value), 2));
xls.SetCellValue(j + 5, 17, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["OD"].Value), 2));
xls.SetCellValue(j + 5, 18, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells["WL"].Value), 2));
}
//Merged Cells 合并单元格
xls.MergeCells(1, 1, 2, 18);
xls.SetCellValue(1, 1, "供热报表" + dt);
//Cell selection and scroll position.默认单元格
xls.SelectCell(1, 1, true);
//Protection
TSheetProtectionOptions SheetProtectionOptions;
SheetProtectionOptions = new TSheetProtectionOptions(false);
SheetProtectionOptions.SelectLockedCells = true;
SheetProtectionOptions.SelectUnlockedCells = true;
xls.Protection.SetSheetProtection(null, SheetProtectionOptions);
xls.Save(dialog.FileName);
try
{
System.Diagnostics.Process.Start(dialog.FileName);
}
catch
{
MessageBox.Show("文件已打开无法覆盖！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
public static void saveAs(DataGridView dgv, string name)
{
if (dgv.RowCount < 1)
{
MessageBox.Show("没有可导出的数据", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
SaveFileDialog dialog = new SaveFileDialog();
dialog.RestoreDirectory = true;
dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*";
dialog.FileName = DateTime.Now.Date.ToString("yyyyMMdd") + ".xls";
if (dialog.ShowDialog() == DialogResult.OK)
{
XlsFile xls = new XlsFile(true);
xls.NewFile(1); //Create a new Excel file with 1 sheet.
xls.ActiveSheet = 1; //Set the sheet we are working in.
xls.SheetName = "Sheet1"; //Sheet Options
//Styles.默认单元格格式
TFlxFormat StyleFmt;
StyleFmt = xls.GetStyle("Normal");
StyleFmt.Font.Name = "宋体";
StyleFmt.Font.Size20 = 240;
StyleFmt.Font.CharSet = 134;
xls.SetStyle("Normal", StyleFmt);
//Printer Settings 默认打印设置
xls.SetPrintMargins(new TXlsMargins(0.7, 0.75, 0.7, 0.75, 0.3, 0.3));
xls.PrintXResolution = 600;
xls.PrintYResolution = 600;
xls.PrintOptions = TPrintOptions.Orientation;
xls.PrintPaperSize = TPaperSize.A4;
//Set up rows and columns 默认单元格大小
//11.88为100像素
xls.DefaultColWidth = 2340;
xls.DefaultRowHeight = 285;
//Set the cell values 设置单元格风格
TFlxFormat fmt;
fmt = xls.GetCellVisibleFormatDef(1, 1);
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.ColorIndex = xls.NearestColorIndex(Color.Black);
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.ColorIndex = xls.NearestColorIndex(Color.Black);
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.ColorIndex = xls.NearestColorIndex(Color.Black);
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.ColorIndex = xls.NearestColorIndex(Color.Black);
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
//设置表格值 加载数据
//字体10号
fmt.Font.Size20 = 200;
int k = -1;
for (int i = 0; i < dgv.Columns.Count; i++)
{
k++;
object cd = dgv.Columns[i].GetType();
if (dgv.Columns[i].Visible == false || dgv.Columns[i].GetType() == typeof(DataGridViewImageColumn))
{
k--;
continue;
}
xls.SetColWidth(k + 1, Convert.ToInt32(0.1188 * dgv.Columns[i].Width * 255));
xls.SetCellFormat(3, k + 1, xls.AddFormat(fmt));
xls.SetCellValue(3, k + 1, dgv.Columns[i].HeaderText);
for (int j = 0; j < dgv.Rows.Count; j++)
{
xls.SetCellFormat(j + 4, k + 1, xls.AddFormat(fmt));
if (dgv.Rows[j].Cells[i].Value.GetType() == typeof(DateTime))
{
xls.SetCellValue(j + 4, k + 1, Convert.ToDateTime(dgv.Rows[j].Cells[i].Value).ToString("yyyy/MM/dd HH:mm"));
}
else
{
if (dgv.Rows[j].Cells[i].Value.GetType() == typeof(Single))
{
xls.SetCellValue(j + 4, k + 1, Math.Round(Convert.ToSingle(dgv.Rows[j].Cells[i].Value), 2));
}
else
{
xls.SetCellValue(j + 4, k + 1, dgv.Rows[j].Cells[i].Value);
}
}
}
}
//设置表头
//字体12号
fmt.Font.Size20 = 240;
//Merged Cells 合并单元格
xls.MergeCells(1, 1, 2, k + 1);
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
xls.SetCellValue(1, 1, name);
//Cell selection and scroll position.默认单元格
xls.SelectCell(1, 1, true);
//Protection
TSheetProtectionOptions SheetProtectionOptions;
SheetProtectionOptions = new TSheetProtectionOptions(false);
SheetProtectionOptions.SelectLockedCells = true;
SheetProtectionOptions.SelectUnlockedCells = true;
xls.Protection.SetSheetProtection(null, SheetProtectionOptions);
xls.Save(dialog.FileName);
try
{
System.Diagnostics.Process.Start(dialog.FileName);
}
catch
{
MessageBox.Show("文件已打开无法覆盖！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
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>
/// 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;
}
private void AddData(ExcelFile Xls)
{
string TemplateFile = "template.xls";
if (cbXlsxTemplate.Checked)
{
if (!XlsFile.SupportsXlsx)
{
throw new Exception("Xlsx files are not supported in this version of the .NET framework");
}
TemplateFile = "template.xlsm";
}
// Open an existing file to be used as template. In this example this file has
// little data, in a real situation it should have as much as possible. (Or even better, be a report)
Xls.Open(Path.Combine(PathToExe, TemplateFile));
//Find the cell where we want to fill the data. In this case, we have created a named range "data" so the address
//is not hardcoded here.
TXlsNamedRange DataCell = Xls.GetNamedRange("Data", -1);
//Add a chart with totals
AddChart(DataCell, Xls);
//Note that "DataCell" will change because we inserted rows above it when creating the chart. But we will keep using the old one.
//Add the captions. This should probably go into the template, but in a dynamic environment it might go here.
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left, "Country");
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left + 1, "Quantity");
//Add a rectangle around the cells
TFlxApplyFormat ApplyFormat = new TFlxApplyFormat();
ApplyFormat.SetAllMembers(false);
ApplyFormat.Borders.SetAllMembers(true); //We will only apply the borders to the existing cell formats
TFlxFormat fmt = Xls.GetDefaultFormat;
fmt.Borders.Left.Style = TFlxBorderStyle.Double;
fmt.Borders.Left.Color = Color.Black;
fmt.Borders.Right.Style = TFlxBorderStyle.Double;
fmt.Borders.Right.Color = Color.Black;
fmt.Borders.Top.Style = TFlxBorderStyle.Double;
fmt.Borders.Top.Color = Color.Black;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Double;
fmt.Borders.Bottom.Color = Color.Black;
Xls.SetCellFormat(DataCell.Top - 1, DataCell.Left, DataCell.Top, DataCell.Left + 1, fmt, ApplyFormat, true); //Set last parameter to true so it draws a box.
//Freeze panes
Xls.FreezePanes(new TCellAddress(DataCell.Top, 1));
Random Rnd = new Random();
//Fill the data
int z = 0;
int OutlineLevel = 0;
for (int r = 0; r <= DataRows; r++)
{
//Fill the values.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left, Country[z % Country.Length]); //For non C# users, "%" means "mod" or modulus in other languages. It is the rest of the integer division.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left + 1, Rnd.Next(1000));
//Add the country to the outline
Xls.SetRowOutlineLevel(DataCell.Top + r, OutlineLevel);
//increment the country randomly
if (Rnd.Next(3) == 0)
{
z++;
OutlineLevel = 0; //Break the group and create a new one.
}
else
{
OutlineLevel = 1;
}
}
//Make the "+" signs of the outline appear at the top.
Xls.OutlineSummaryRowsBelowDetail = false;
//Collapse the outline to the first level.
Xls.CollapseOutlineRows(1, TCollapseChildrenMode.Collapsed);
//Add Data Validation for the first column, it must be a country.
TDataValidationInfo dv = new TDataValidationInfo(
TDataValidationDataType.List, //We will use a built in list.
TDataValidationConditionType.Between, //This parameter does not matter since it is a list. It will not be used.
"=\"" + GetCountryList() + "\"", //We could have used a range of cells here with the values (like "=C1..C4") Instead, we directly entered the list in the formula.
null, //no need for a second formula, not used in List
false,
true,
true, //Note that as we entered the data directly in FirstFormula, we need to set this to true
true,
"Unknown country",
"Please make sure that the country is in the list",
false, //We will not use an input box, so this is false and the 2 next entries are null
null,
null,
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left, DataCell.Top + DataRows, DataCell.Left), dv);
//Add Data Validation for the second column, it must be an integer between 0 and 1000.
dv = new TDataValidationInfo(
TDataValidationDataType.WholeNumber, //We will request a number.
TDataValidationConditionType.Between,
"=0", //First formula marks the first part of the "between" condition.
"=1000", //Second formula is the second part.
false,
false,
false,
true,
"Invalid Quantity",
null, //We will leave the default error message.
true,
"Quantity:",
"Please enter a quantity between 0 and 1000",
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left + 1, DataCell.Top + DataRows, DataCell.Left + 1), dv);
//Search country "Unknown" and replace it by "no".
//This does not make any sense here (we could just have entered "no" to begin)
//but it shows how to do it when modifying an existing file
Xls.Replace("Unknown", "no", TXlsCellRange.FullRange(), true, false, true);
//Autofit the rows. As we keep the row height automatic this will not show when opening in Excel, but will work when directly printing from FlexCel.
Xls.AutofitRowsOnWorkbook(false, true, 1);
Xls.Recalc(); //Calculate the SUMIF formulas so we can sort by them. Note that FlexCel automatically recalculates before saving,
//but in this case we haven't saved yet, so the sheet is not recalculated. You do not normally need to call Recalc directly.
//Sort the data. As in the case with replace, this does not make much sense. We could have entered the data sorted to begin
//But it shows how you can use the feature.
//Find the cell where the chart goes.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
Xls.Sort(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top + Country.Length, ChartRange.Left + 1),
true, new int[] { 2 }, new TSortOrder[] { TSortOrder.Descending }, null);
//Protect the Sheet
TSheetProtectionOptions Sp = new TSheetProtectionOptions(false); //Create default protection options that allows everything.
Sp.InsertColumns = false; //Restrict inserting columns.
Xls.Protection.SetSheetProtection("flexcel", Sp);
//Set a modify password. Note that this does *not* encrypt the file.
Xls.Protection.SetModifyPassword("flexcel", true, "flexcel");
Xls.Protection.OpenPassword = "******"; //OpenPasword is the only password that will actually encrypt the file, so you will not be able to open it with flexcel if you do not know the password.
//Select cell A1
Xls.SelectCell(1, 1, true);
}
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;
}