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