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