private void AddData(ExcelFile Xls)
{
//Create a new file. We could also open an existing file with Xls.Open
Xls.NewFile(1, TExcelFileFormat.v2019);
//Set some cell values.
Xls.SetCellValue(1, 1, "Hello to the world");
Xls.SetCellValue(2, 1, 3);
Xls.SetCellValue(3, 1, 2.1);
Xls.SetCellValue(4, 1, new TFormula("=Sum(A2,A3)"));
//Load an image from disk.
string AssemblyPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
using (Image Img = Image.FromFile(AssemblyPath + Path.DirectorySeparatorChar + ".." + Path.DirectorySeparatorChar + ".." + Path.DirectorySeparatorChar + "Test.bmp"))
{
//Add a new image on cell E2
Xls.AddImage(2, 6, Img);
//Add a new image with custom properties at cell F6
Xls.AddImage(Img, new TImageProperties(new TClientAnchor(TFlxAnchorType.DontMoveAndDontResize, 2, 10, 6, 10, 100, 100, Xls), ""));
//Swap the order of the images. it is not really necessary here, we could have loaded them on the inverse order.
Xls.BringToFront(1);
}
//Add a comment on cell a2
Xls.SetComment(2, 1, "This is 3");
//Custom Format cells a2 and a3
TFlxFormat f = Xls.GetDefaultFormat;
f.Font.Name = "Times New Roman";
f.Font.Color = Color.Red;
f.FillPattern.Pattern = TFlxPatternStyle.LightDown;
f.FillPattern.FgColor = Color.Blue;
f.FillPattern.BgColor = Color.White;
//You can call AddFormat as many times as you want, it will never add a format twice.
//But if you know the format you are going to use, you can get some extra CPU cycles by
//calling addformat once and saving the result into a variable.
int XF = Xls.AddFormat(f);
Xls.SetCellFormat(2, 1, XF);
Xls.SetCellFormat(3, 1, XF);
f.Rotation = 45;
f.FillPattern.Pattern = TFlxPatternStyle.Solid;
int XF2 = Xls.AddFormat(f);
//Apply a custom format to all the row.
Xls.SetRowFormat(1, XF2);
//Merge cells
Xls.MergeCells(5, 1, 10, 6);
//Note how this one merges with the previous range, creating a final range (5,1,15,6)
Xls.MergeCells(10, 6, 15, 6);
//Make the page print in landscape or portrait mode
Xls.PrintLandscape = false;
}
private void CreateFile(ExcelFile Xls)
{
//Create a new file. We could also open an existing file with Xls.Open
Xls.NewFile(1, TExcelFileFormat.v2019);
//Set some cell values.
Xls.SetCellValue(1, 1, "Hello to everybody");
Xls.SetCellValue(2, 1, 3);
Xls.SetCellValue(3, 1, 2.1);
Xls.SetCellValue(4, 1, new TFormula("=Sum(A2,A3)"));
//Load an image from disk.
string AssemblyPath = HttpContext.Current.Request.PhysicalApplicationPath;
using (System.Drawing.Image Img = System.Drawing.Image.FromFile(Path.Combine(Path.Combine(AssemblyPath, "images"), "Test.bmp")))
{
//Add a new image on cell E5
Xls.AddImage(2, 6, Img);
//Add a new image with custom properties at cell F6
Xls.AddImage(Img, new TImageProperties(new TClientAnchor(TFlxAnchorType.DontMoveAndDontResize, 2, 10, 6, 10, 100, 100, Xls), ""));
//Swap the order of the images. it is not really necessary here, we could have loaded them on the inverse order.
Xls.BringToFront(1);
}
//Add a comment on cell a2
Xls.SetComment(2, 1, "This is 3");
//Custom Format cells a2 and a3
TFlxFormat f = Xls.GetDefaultFormat;
f.Font.Name = "Times New Roman";
f.Font.Color = Color.Red;
f.FillPattern.Pattern = TFlxPatternStyle.LightDown;
f.FillPattern.FgColor = Color.Blue;
f.FillPattern.BgColor = Color.White;
int XF = Xls.AddFormat(f);
Xls.SetCellFormat(2, 1, XF);
Xls.SetCellFormat(3, 1, XF);
f.Rotation = 45;
f.FillPattern.Pattern = TFlxPatternStyle.Solid;
int XF2 = Xls.AddFormat(f);
//Apply a custom format to all the row.
Xls.SetRowFormat(1, XF2);
//Merge cells
Xls.MergeCells(5, 1, 10, 6);
//Note how this one merges with the previous range, creating a final range (5,1,15,6)
Xls.MergeCells(10, 6, 15, 6);
//Make sure rows are autofitted for pdf export.
Xls.AutofitRowsOnWorkbook(false, true, 1);
}
private void CopyRowAndColFormat(ExcelFile Workbook, TWaitingCoords Coords, ExcelFile IncludedReport, TXlsCellRange range)
{
//Columns go before rows.
if (CopyColFormats || (InsertMode == TFlxInsertMode.ShiftColRight && (range.Top > 1 || range.Bottom < FlxConsts.Max_Rows + 1)))
{
for (int c = range.Left; c < range.Right; c++)
{
if (!IncludedReport.IsNotFormattedCol(c))
{
int c1 = c - range.Left + Left + Coords.ColOfs;
int cw = IncludedReport.GetColWidth(c);
Workbook.SetColWidth(c1, cw);
int co = IncludedReport.GetColOptions(c);
if (cw != IncludedReport.DefaultColWidth || cw != Workbook.DefaultColWidth)
{
co |= 0x02; //the column has no standard width.
}
Workbook.SetColOptions(c1, co);
TFlxFormat fmt = IncludedReport.GetFormat(IncludedReport.GetColFormat(c));
fmt.LinkedStyle.AutomaticChoose = false;
Workbook.SetColFormat(c1, Workbook.AddFormat(fmt));
if (c1 + 1 <= FlxConsts.Max_Columns)
{
Workbook.KeepColsTogether(c1, c1 + 1, IncludedReport.GetKeepColsTogether(c), true);
}
}
}
}
if (CopyRowFormats || (InsertMode == TFlxInsertMode.ShiftRowDown && (range.Left > 1 || range.Right < FlxConsts.Max_Columns + 1)))
{
for (int r = range.Top; r < range.Bottom; r++)
{
if (!IncludedReport.IsEmptyRow(r))
{
int r1 = r - range.Top + Top + Coords.RowOfs;
Workbook.SetRowHeight(r1, IncludedReport.GetRowHeight(r));
Workbook.SetRowOptions(r1, IncludedReport.GetRowOptions(r));
TFlxFormat fmt = IncludedReport.GetFormat(IncludedReport.GetRowFormat(r));
fmt.LinkedStyle.AutomaticChoose = false;
Workbook.SetRowFormat(r1, Workbook.AddFormat(fmt));
if (r1 + 1 <= FlxConsts.Max_Rows)
{
Workbook.KeepRowsTogether(r1, r1 + 1, IncludedReport.GetKeepRowsTogether(r), true);
}
}
}
}
}
public int GetFootnoteFormat(ExcelFile xls)
{
if (footnoteFormat == null)
{
footnoteFormat = xls.DefaultFormatId;
getDefaultFormat = xls.GetDefaultFormat;
getDefaultFormat.HAlignment = THFlxAlignment.left;
xls.AddFormat(getDefaultFormat);
getDefaultFormat.HAlignment = THFlxAlignment.right;
footnoteFormat = xls.AddFormat(getDefaultFormat);
}
return(footnoteFormat.Value);
}
public int GetSummationFormat(ExcelFile xls)
{
if (summationFormat == null)
{
getDefaultFormat = xls.GetDefaultFormat;
getDefaultFormat.Font.Style = TFlxFontStyles.Bold;
summationFormat = xls.AddFormat(getDefaultFormat);
}
return(summationFormat.Value);
}
public int GetHeaderFormat(ExcelFile xls)
{
if (headerFormat == null)
{
headerFormat = xls.DefaultFormatId;
getDefaultFormat = xls.GetDefaultFormat;
getDefaultFormat.Font.Color = Color.Black;
getDefaultFormat.VAlignment = TVFlxAlignment.center;
getDefaultFormat.HAlignment = THFlxAlignment.center;
getDefaultFormat.WrapText = true;
headerFormat = xls.AddFormat(getDefaultFormat);
}
return(headerFormat.Value);
}
public int GetSectionNameFormat(ExcelFile xls)
{
if (sectionNameFormat == null)
{
sectionNameFormat = xls.DefaultFormatId;
getDefaultFormat = xls.GetDefaultFormat;
getDefaultFormat.Font.Style = TFlxFontStyles.Bold;
getDefaultFormat.Font.Color = Color.Black;
getDefaultFormat.FillPattern.Pattern = TFlxPatternStyle.Solid;
getDefaultFormat.FillPattern.FgColor = Color.Silver;
getDefaultFormat.HAlignment = THFlxAlignment.left;
sectionNameFormat = xls.AddFormat(getDefaultFormat);
}
return(sectionNameFormat.Value);
}
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
}
public static void SetCellColour(ref ExcelFile xls, string colour, int row, int col, string sheetname = "")
{
if (sheetname != "")
{
xls.ActiveSheetByName = sheetname;
}
if (colour == "")
{
return;
}
switch (colour.ToLower())
{
case "blue":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(47);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "bluenumber":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(47);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Format = "#0.00";
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blackheader":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(1);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blackfooter":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(1);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blackfooternumber":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(1);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Format = "#0.00";
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blueprojectheader":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(47);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blueprojectfooter":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(47);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Borders.Top = new TFlxOneBorder(TFlxBorderStyle.Thin, TExcelColor.FromIndex(47));
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
case "blueprojectfooternumber":
{
TFlxFormat tf = xls.GetDefaultFormat;
tf.Font.Color = TExcelColor.FromIndex(47);
tf.Font.Style = TFlxFontStyles.Bold;
tf.Font.Size20 = 200;
tf.Format = "#0.00";
tf.Borders.Top = new TFlxOneBorder(TFlxBorderStyle.Thin, TExcelColor.FromIndex(47));
tf.Borders.Bottom = new TFlxOneBorder(TFlxBorderStyle.Thick, TExcelColor.FromIndex(47));
int c = xls.AddFormat(tf);
xls.SetCellFormat(row, col, c);
break;
}
}
}