public static void FillData(ExcelFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotCuaMotTrang, String CoDienDuLieu, Boolean bSTT)
{
TFlxFormat fmt;
//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;
//SET border cho số cột cần thêm
for (int c = DenCotCua_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.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.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(h + TuHang, c, xls.AddFormat(fmt));
}
}
int _C = TuCot;
int widthcolTong = 4205;
int w6 = widthcolTong / 6;
int d = 0;
object GiaTriO = null;
#region Fill dữ liệu những cột động
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.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.right;
fmt.VAlignment = TVFlxAlignment.center;
fmt.Format = "_-* #,##0\\ _₫_-;\\-* #,##0\\ _₫_-;_-* \"-\"??\\ _₫_-;_-@_-";
break;
default:
fmt = xls.GetCellVisibleFormatDef(TuHang, 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.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.left;
fmt.VAlignment = TVFlxAlignment.center;
break;
}
for (int h = 0; h < dt.Rows.Count; h++)
{
GiaTriO = null;
if (c == TuCot - 1)
GiaTriO = dt.Rows[h][c];
xls.SetCellFormat(h + TuHang, _C, xls.AddFormat(fmt));
if (c + TuCotCua_DT <= DenCotCua_DT)
xls.SetCellValue(h + TuHang, _C, dt.Rows[h][c + TuCotCua_DT]);
if (d > 6)
xls.SetColWidth(_C, w6 + 4059);
}
_C++;
d++;
}
#endregion
#region Fill dữ liệu những cột cố định
String KyTu1, KyTu2, strSum;
int cot = 1;
for (int h = 0; h < dt.Rows.Count; h++)
{
//set cho cột STT
if (bSTT)
{
fmt = xls.GetCellVisibleFormatDef(h + TuHang, 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;
fmt.WrapText = true;
xls.SetCellFormat(h + TuHang, 1, xls.AddFormat(fmt));
xls.SetCellValue(h + TuHang, 1, h + 1);
}
//set cho cột Đơn vị
fmt = xls.GetCellVisibleFormatDef(h + TuHang, 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.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.left;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(h + TuHang, 2, xls.AddFormat(fmt));
xls.SetCellValue(h + TuHang, 2, dt.Rows[h][0]);
//Set cho cột tổng sô
fmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), true);
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.right;
fmt.VAlignment = TVFlxAlignment.center;
fmt.Format = "_-* #,##0\\ _₫_-;\\-* #,##0\\ _₫_-;_-* \"-\"??\\ _₫_-;_-@_-";
xls.SetCellFormat(h + TuHang, TuCot - 1, xls.AddFormat(fmt));
KyTu1 = HamChung.ExportExcel_MaCot(TuCot);
KyTu2 = HamChung.ExportExcel_MaCot(TuCot + TongSoCot - 1);
strSum = String.Format("=SUM({0}{1}:{2}{1})", KyTu1, h + TuHang, KyTu2);
xls.SetCellFormat(h + TuHang, TuCot - 1, xls.AddFormat(fmt));
xls.SetCellValue(h + TuHang, TuCot - 1, new TFormula(strSum));
}
#endregion
}