internal void InsertSheets(int CopyFrom, int BeforeSheet, int SheetCount, TSheetInfo SheetInfo, bool CopyFilterDatabase)
{
int aCount = SheetInfo.SourceNames.Count;
int DestCount = Count; //Get this before adding the new sheets.
for (int i = DestCount - 1; i >= 0; i--)
{
this[i].ArrangeInsertSheets(BeforeSheet, SheetCount);
}
for (int i = 0; i < aCount; i++)
{
if (
CopyFrom >= 0 &&
NameMustBeCopiedToSheet(CopyFrom, SheetInfo, i) &&
(CopyFilterDatabase || SheetInfo.SourceNames[i].Name != TXlsNamedRange.GetInternalName(InternalNameRange.Filter_DataBase)) //filterdatabase will be copied when copying autofilters.
)
{
for (int k = 0; k < SheetCount; k++)
{
SheetInfo.InsSheet = BeforeSheet + k;
TNameRecord name = (TNameRecord.Clone(SheetInfo.SourceNames[i], SheetInfo) as TNameRecord).ArrangeCopySheet(SheetInfo); //this could add its own names, so we need to recheck name wasn't added in next line.
if (!NameAlreadyExistsInLocal(SheetInfo.DestFormulaSheet, name.Name))
{
Add(name);
}
CheckInternalNames(SheetInfo.SourceNames[i].OptionFlags, SheetInfo.SourceGlobals);
}
}
}
}
private void LoadFile(string FileName)
{
Xls.Open(FileName);
ActualValue = 0;
ValueRange = Xls.GetNamedRange("Value", 0);
if (ValueRange == null)
{
throw new Exception("There is no range named \"value\" in the template");
}
MinValue = ReadDoubleName("Minimum");
MaxValue = ReadDoubleName("Maximum");
StepValue = ReadDoubleName("Step");
ChartIndex = -1;
for (int i = 1; i <= Xls.ObjectCount; i++)
{
string ObjName = Xls.GetObjectName(i);
if (String.Compare(ObjName, "DataChart", true) == 0)
{
ChartIndex = i;
break;
}
}
if (ChartIndex < 0)
{
throw new Exception("There is no object named \"DataChart\" in the template");
}
ChartProps = Xls.GetObjectProperties(ChartIndex, true);
}
private void DoPreload(TBand aParentBand, string aRange, string aFileName, TDataSourceInfoList aDsInfoList, int aNestedLevel, ExcelFile Result, MemoryStream MStream, FlexCelReport aParentReport)
{
Result.Open(MStream);
Result.ActiveFileName = aFileName;
TXlsNamedRange XlsRange = Result.GetNamedRange(aRange, -1);
if (XlsRange == null)
{
FlxMessages.ThrowException(FlxErr.ErrCantFindNamedRange, aRange);
}
FRangeName = aRange;
MainBand = CreateStartingBand(XlsRange, aParentBand, aRange);
Report = new FlexCelReport(aNestedLevel, FTagText, aDsInfoList, aParentReport);
if (!StaticInclude)
{
Report.PreLoad(Result, ref MainBand, XlsRange.SheetIndex, ref FData, out KeepRows, out KeepCols);
}
Result.ActiveSheet = XlsRange.SheetIndex;
}
internal int AddName(TXlsNamedRange Range, TWorkbookGlobals Globals, TCellList CellList)
{
int aCount = Count;
bool IsInternal;
bool ValidName = TXlsNamedRange.IsValidRangeName(Range.Name, out IsInternal);
if (IsInternal)
{
Range.OptionFlags |= 0x020;
}
for (int i = 0; i < aCount; i++)
{
//Documentation is wrong. We need the sheet index (1 based) not the externsheetindex
/*int rSheet=-1;
* if (this[i].RangeSheet>=0)
* {
* rSheet = GetSheet(this[i].RangeSheet);
* }
*/
int rSheet = this[i].RangeSheet;
if (
(rSheet == Range.NameSheetIndex) &&
(String.Equals(this[i].Name, Range.Name, StringComparison.CurrentCultureIgnoreCase))
)
{
this[i] = new TNameRecord(Range, Globals, CellList); //We have to be careful not to change name ordering, or formulas would point to wrong ranges.
//If we found it, then it *is* a valid name
return(i);
}
}
if (!ValidName)
{
XlsMessages.ThrowException(XlsErr.ErrInvalidNameForARange, Convert.ToString(Range.Name));
}
Add(new TNameRecord(Range, Globals, CellList));
CheckInternalNames(Range.OptionFlags, Globals);
return(Count - 1);
}
internal void ReplaceName(int Index, TXlsNamedRange Range, TWorkbookGlobals Globals, TCellList CellList)
{
bool IsInternal;
bool ValidName = TXlsNamedRange.IsValidRangeName(Range.Name, out IsInternal);
if (!ValidName)
{
XlsMessages.ThrowException(XlsErr.ErrInvalidNameForARange, Convert.ToString(Range.Name));
}
if (Index < 0 || Index >= Count)
{
XlsMessages.ThrowException(XlsErr.ErrXlsIndexOutBounds, Index, "Index", 0, Count - 1);
}
if (IsInternal)
{
Range.OptionFlags |= 0x020;
}
this[Index] = new TNameRecord(Range, Globals, CellList); //We have to be careful not to change name ordering, or formulas would point to wrong ranges.
CheckInternalNames(Range.OptionFlags, Globals);
}
/// <summary>
/// 创建 [Excel报表模版] 中的“数据表”部分
/// </summary>
/// <typeparam name="T">要绑定的集合元素的类型</typeparam>
/// <param name="startRow">“数据表”的开始行（从1开始）</param>
/// <param name="startColumn">“数据表”的开始列（从1开始）</param>
public void CreateExcelReportTemlate <T>(int startRow = 2, int startColumn = 1) where T : class, new()
{
// 属性集合
int columnCount = 0;
PropertyInfo[] propertyArray = typeof(T).GetProperties();
foreach (PropertyInfo property in propertyArray)
{
object[] attributes = property.GetCustomAttributes(typeof(DisplayNameAttribute), true);
if (attributes.Length == 0)
{
continue;
}
columnCount++;
string propertyName = property.Name;
string displayName = ((DisplayNameAttribute)attributes[0]).DisplayName;
// 设置列标题
int titleRowIndex = startRow;
int titleColumnIndex = columnCount + (startColumn - 1);
SetTitleCell(titleRowIndex, titleColumnIndex, displayName);
// 设置属性绑定标记
int dataRowIndex = titleRowIndex + 1;
int dataColumnIndex = titleColumnIndex;
SetDataBindCell(dataRowIndex, dataColumnIndex, propertyName);
}
// 定义名称区域
char[] excelColumn = new char[] { 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
string 数据表区域 = string.Format("='{0}'!${2}${1}:${3}${1}", SheetName, startRow + 1, excelColumn[startColumn - 1], excelColumn[columnCount - 1]);
xls.SetNamedRange(new TXlsNamedRange("__数据表__", 0, 0, 数据表区域));
string 打印区域 = string.Format("='{0}'!$A$1:${2}${1}", SheetName, startRow + 3, excelColumn[columnCount]);
xls.SetNamedRange(new TXlsNamedRange(TXlsNamedRange.GetInternalName(InternalNameRange.Print_Area), 1, 32, 打印区域));
}
private void AddChart(TXlsNamedRange DataCell, ExcelFile Xls)
{
//Find the cell where the cart will go.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
//Insert cells to expand the range for the chart. It already has 2 rows, so we need to insert Country.Length - 2
//Note also that we insert after ChartRange.Top, so the chart is updates with the new range.
Xls.InsertAndCopyRange(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top, ChartRange.Left + 1),
ChartRange.Top + 1, ChartRange.Left, Country.Length - 2, TFlxInsertMode.ShiftRangeDown); //we use shiftrangedown so not all the row goes down and the chart stays in place.
//Get the cell addresses of the data range.
TCellAddress FirstCell = new TCellAddress(DataCell.Top, DataCell.Left);
TCellAddress SecondCell = new TCellAddress(DataCell.Top + DataRows, DataCell.Left + 1);
TCellAddress FirstSumCell = new TCellAddress(DataCell.Top, DataCell.Left + 1);
//Fill a table with the data to be used in the chart.
for (int r = ChartRange.Top; r < ChartRange.Top + Country.Length; r++)
{
Xls.SetCellValue(r, ChartRange.Left, Country[r - ChartRange.Top]);
Xls.SetCellValue(r, ChartRange.Left + 1, new TFormula("=SUMIF(" + FirstCell.CellRef + ":" + SecondCell.CellRef +
",\"" + Country[r - ChartRange.Top] + "\", " + FirstSumCell.CellRef + ":" + SecondCell.CellRef + ")"));
}
}
private static void EmailNormal(Dictionary <string, object> valueList, DTO.LIST_TASKInfo infTask)
{
ReportGenerator rgAtt = null;
ReportGenerator rgCnt = null;
if (infTask.AttQD_ID != "")
{
QueryBuilder.SQLBuilder sqlBuiderA = QueryBuilder.SQLBuilder.LoadSQLBuilderFromDataBase(infTask.AttQD_ID, _db, "");
rgAtt = new ReportGenerator(sqlBuiderA, infTask.AttQD_ID, "", _repConnect, _tempPath, _reptPath, __documentDirectory);
}
else
{
DataSet ds = new DataSet();
rgAtt = new ReportGenerator(ds, infTask.AttTmp, _db, _reptPath, _tempPath, _reptPath, __documentDirectory);
}
if (infTask.CntQD_ID != "")
{
QueryBuilder.SQLBuilder sqlBuiderC = QueryBuilder.SQLBuilder.LoadSQLBuilderFromDataBase(infTask.CntQD_ID, _db, "");
rgCnt = new ReportGenerator(sqlBuiderC, infTask.CntQD_ID, "", _repConnect, _tempPath, _reptPath, __documentDirectory);
}
else
{
DataSet ds = new DataSet();
rgCnt = new ReportGenerator(ds, infTask.CntTmp, _db, _repConnect, _tempPath, _reptPath, __documentDirectory);
}
rgAtt.ValueList = valueList;
rgCnt.ValueList = valueList;
ExcelFile xls = rgAtt.CreateReport();
rgAtt.UserID = _userID;
rgCnt.Close();
bool flagRun = false;
string[] arrVRange = infTask.ValidRange.Split(';');
if (arrVRange.Length >= 1)
{
for (int i = 1; i <= xls.SheetCount; i++)
{
TXlsNamedRange range = xls.GetNamedRange(arrVRange[0], 0);
if (range != null)
{
xls.ActiveSheet = range.SheetIndex;
object flag = xls.GetCellValue(range.Top, range.Left);
if (flag != null && !String.IsNullOrEmpty(flag.ToString().Trim()) && flag.ToString().Trim() != "0")
{
flagRun = true; break;
}
}
}
}
string title = infTask.Description;
if (flagRun)
{
try
{
using (TextWriter wt = rgCnt.ExportHTML(_reptPath))
{
ExcelFile xls1 = rgCnt.XlsFile;
if (arrVRange.Length >= 2)
{
for (int i = 1; i <= xls1.SheetCount; i++)
{
TXlsNamedRange range = xls1.GetNamedRange(arrVRange[1], 0);
if (range != null)
{
xls1.ActiveSheet = range.SheetIndex;
object flag = xls1.GetCellValue(range.Top, range.Left);
if (flag != null && !String.IsNullOrEmpty(flag.ToString()))
{
title = flag.ToString();
break;
}
}
}
}
string content = wt.ToString();
string filename = rgAtt.ExportExcelToFile(_reptPath, infTask.Description + ".xls");
Sendmail sendMail = new Sendmail(infTask.UserID, infTask.Password, infTask.Server, infTask.Protocol, Convert.ToInt32(infTask.Port));
string[] emails = infTask.Emails.Split(',');
Dictionary <string, string> arrayMail = new Dictionary <string, string>();
for (int i = 0; i < emails.Length; i++)
{
Match name = Regex.Match(emails[i], "\".+\"");
Match mail = Regex.Match(emails[i], "<.+>");
arrayMail.Add(mail.Value.Substring(1, mail.Value.Length - 2), name.Value.Substring(1, name.Value.Length - 2));
}
sErr = sendMail.SendMail(title, content, arrayMail, filename, true, true);
}
}
catch (Exception ex)
{
sErr = ex.Message;
}
}
}
public static XlsFile TaoTieuDe(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotCuaMotTrang)
{
xls.NewFile(1); //Create a new Excel file with 1 sheet.
xls.ActiveSheet = 1; //Set the sheet we are working in.
xls.SheetName = "BaoCao";//Set the names of the sheets
//Global Workbook Options
xls.OptionsAutoCompressPictures = true;
//Tính số trang và số cột cần thêm để đủ một trang
int SoCotDu = (DenCotCua_DT - TuCotCua_DT) % SoCotCuaMotTrang;
int SoCotCanThem = 0;
int TongSoCot = 0;
if (SoCotDu != 0)
{
SoCotCanThem = SoCotCuaMotTrang - SoCotDu;
}
TongSoCot = DenCotCua_DT + SoCotCanThem - TuCotCua_DT;
int SoTrang = TongSoCot / SoCotCuaMotTrang;
int _C = TuCot;
//Styles.
TFlxFormat StyleFmt;
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Good, 0));
StyleFmt.Font.CharSet = 163;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Good, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Font.CharSet = 163;
StyleFmt.Format = "_-* #,##0.00\\ _₫_-;\\-* #,##0.00\\ _₫_-;_-* \"-\"??\\ _₫_-;_-@_-";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt);
//Named Ranges
TXlsNamedRange Range;
string RangeName;
RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles);
Range = new TXlsNamedRange(RangeName, 1, 32, "='BaoCao'!$A:$B,'BaoCao'!$1:$7");
//You could also use: Range = new TXlsNamedRange(RangeName, 1, 0, 0, 0, 0, 0, 32);
xls.SetNamedRange(Range);
#region //Printer Settings
THeaderAndFooter HeadersAndFooters = new THeaderAndFooter();
HeadersAndFooters.AlignMargins = true;
HeadersAndFooters.ScaleWithDoc = true;
HeadersAndFooters.DiffFirstPage = false;
HeadersAndFooters.DiffEvenPages = false;
HeadersAndFooters.DefaultHeader = "";
HeadersAndFooters.DefaultFooter = "";
HeadersAndFooters.FirstHeader = "";
HeadersAndFooters.FirstFooter = "";
HeadersAndFooters.EvenHeader = "";
HeadersAndFooters.EvenFooter = "";
xls.SetPageHeaderAndFooter(HeadersAndFooters);
//You can set the margins in 2 ways, the one commented here or the one below:
// TXlsMargins PrintMargins = xls.GetPrintMargins();
// PrintMargins.Left = 0.236220472440945;
// PrintMargins.Top = 0.748031496062992;
// PrintMargins.Right = 0.236220472440945;
// PrintMargins.Bottom = 0.748031496062992;
// PrintMargins.Header = 0.31496062992126;
// PrintMargins.Footer = 0.31496062992126;
// xls.SetPrintMargins(PrintMargins);
xls.SetPrintMargins(new TXlsMargins(0.236220472440945, 0.748031496062992, 0.236220472440945, 0.748031496062992, 0.31496062992126, 0.31496062992126));
xls.PrintXResolution = 600;
xls.PrintYResolution = 0;
xls.PrintOptions = TPrintOptions.LeftToRight;
xls.PrintPaperSize = TPaperSize.A4;
//Printer Driver Settings are a blob of data specific to a printer
//This code is commented by default because normally you do not want to hard code the printer settings of an specific printer.
// byte[] PrinterData = {
// 0x00, 0x00, 0x5C, 0x00, 0x5C, 0x00, 0x31, 0x00, 0x39, 0x00, 0x32, 0x00, 0x2E, 0x00, 0x31, 0x00, 0x36, 0x00, 0x38, 0x00, 0x2E, 0x00, 0x31, 0x00, 0x34, 0x00, 0x30, 0x00, 0x2E, 0x00, 0x37, 0x00, 0x5C, 0x00, 0x48, 0x00, 0x50, 0x00, 0x20, 0x00, 0x4C, 0x00, 0x61, 0x00, 0x73, 0x00, 0x65, 0x00, 0x72, 0x00,
// 0x4A, 0x00, 0x65, 0x00, 0x74, 0x00, 0x20, 0x00, 0x4D, 0x00, 0x31, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x15, 0x06, 0xDC, 0x00, 0x34, 0x03, 0x0F, 0xDF, 0x00, 0x00, 0x02, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x00, 0x00, 0x01, 0x00, 0x07, 0x00, 0x58, 0x02, 0x01, 0x00, 0x01, 0x00, 0x58, 0x02,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xBF, 0x03, 0xC1, 0x02, 0x00, 0x00, 0x00, 0x00, 0x08, 0x04, 0xF8, 0x02, 0x00, 0x00, 0x00, 0x00, 0x52, 0x04, 0x2E, 0x03, 0x00, 0x00, 0x00, 0x00, 0x9C, 0x04, 0x64, 0x03, 0x00, 0x00, 0x00, 0x00, 0xE6, 0x04, 0x9A, 0x03, 0x00, 0x00, 0x00, 0x00, 0x2F, 0x05, 0xD1, 0x03,
// 0x00, 0x00, 0x00, 0x00, 0x79, 0x05, 0x07, 0x04, 0x00, 0x00, 0x00, 0x00, 0xC3, 0x05, 0x3D, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x53, 0x44, 0x44, 0x4D, 0x00, 0x06, 0x00, 0x00, 0x00, 0x06, 0x00, 0x00, 0x48, 0x50, 0x20, 0x4C, 0x61, 0x73, 0x65, 0x72, 0x4A, 0x65, 0x74, 0x20, 0x4D, 0x31, 0x33, 0x31,
// 0x39, 0x66, 0x20, 0x4D, 0x46, 0x50, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00,
// 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00,
// 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x1A, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x0F, 0x00,
// 0x00, 0x00, 0x5A, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0x80, 0x80, 0x00, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0xFF, 0x00,
// 0xFF, 0x00, 0xFF, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x28, 0x00, 0x00, 0x00, 0x64, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xDE, 0x03, 0x00, 0x00, 0xDE, 0x03, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x59, 0xC3, 0x13, 0xAE, 0x34, 0x03, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x04, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00
// };
// TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData);
// xls.SetPrinterDriverSettings(PrinterDriverSettings);
#endregion
#region //Set up rows and columns
xls.DefaultColWidth = 2340;
xls.SetColWidth(1, 1609); //(5.54 + 0.75) * 256
TFlxFormat ColFmt;
ColFmt = xls.GetFormat(xls.GetColFormat(1));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(1, xls.AddFormat(ColFmt));
xls.SetColWidth(2, 6326); //(23.96 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(2));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(2, xls.AddFormat(ColFmt));
xls.SetColWidth(3, 4205); //(15.68 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(3));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(3, xls.AddFormat(ColFmt));
xls.SetColWidth(4, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(4));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(4, xls.AddFormat(ColFmt));
xls.SetColWidth(5, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(5));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(5, xls.AddFormat(ColFmt));
xls.SetColWidth(6, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(6));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(6, xls.AddFormat(ColFmt));
xls.SetColWidth(7, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(7));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(7, xls.AddFormat(ColFmt));
xls.SetColWidth(8, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(8));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(8, xls.AddFormat(ColFmt));
xls.SetColWidth(9, 4059); //(15.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(9));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(9, xls.AddFormat(ColFmt));
#endregion
#region set độ rộng cột
for (int c = 0; c < TongSoCot + SoTrang; c++)
{
ColFmt = xls.GetFormat(xls.GetColFormat(c + TuCot));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
ColFmt.Font.CharSet = 0;
xls.SetColFormat(c + TuCot, xls.AddFormat(ColFmt));
xls.SetColWidth(c + TuCot + 1, 4059);
}
#endregion
xls.DefaultRowHeight = 300;
xls.SetRowHeight(7, 1545);
xls.SetRowHeight(8, 360);
#region //Merged Cells
xls.MergeCells(6, 1, 7, 1);
xls.MergeCells(6, 3, 7, 3);
xls.MergeCells(6, 4, 6, 9);
xls.MergeCells(5, 1, 5, 2);
xls.MergeCells(1, 1, 1, 2);
xls.MergeCells(1, 3, 1, 9);
xls.MergeCells(2, 3, 2, 9);
for (int t = 0; t < SoTrang; t++)
{
xls.MergeCells(6, _C, 6, _C + SoCotCuaMotTrang-1);
_C = _C + SoCotCuaMotTrang;
}
#endregion
//Set the cell values
TFlxFormat fmt;
#region
fmt = xls.GetCellVisibleFormatDef(1, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.HAlignment = THFlxAlignment.left;
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
xls.SetCellValue(1, 1, "BỘ QUỐC PHÒNG");
fmt = xls.GetCellVisibleFormatDef(1, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellValue(1, 3, "DỰ TOÁN CHI NGÂN SÁCH QUỐC PHÒNG NĂM <#Nam>");
xls.SetCellFormat(1, 2, xls.AddFormat(fmt));
xls.SetCellFormat(1, 3, xls.AddFormat(fmt));
xls.SetCellFormat(1, 4, xls.AddFormat(fmt));
xls.SetCellFormat(1, 5, xls.AddFormat(fmt));
xls.SetCellFormat(1, 6, xls.AddFormat(fmt));
xls.SetCellFormat(1, 7, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(2, 3, xls.AddFormat(fmt));
xls.SetCellValue(2, 3, "(Phần chi cho doanh nghiệp)");
fmt = xls.GetCellVisibleFormatDef(5, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(5, 1, xls.AddFormat(fmt));
xls.SetCellValue(5, 1, "LNS: 1050000 Loại: 460 Khoản: 468");
fmt = xls.GetCellVisibleFormatDef(5, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(5, 2, xls.AddFormat(fmt));
//fmt = xls.GetCellVisibleFormatDef(5, 8);
//fmt.Font.Name = "Times New Roman";
//fmt.Font.Family = 1;
//fmt.Font.CharSet = 0;
//xls.SetCellFormat(5, 8, xls.AddFormat(fmt));
//xls.SetCellValue(5, 8, "Đơn vị tính:1.000 đ");
fmt = xls.GetCellVisibleFormatDef(6, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(6, 1, xls.AddFormat(fmt));
xls.SetCellValue(6, 1, "STT");
fmt = xls.GetCellVisibleFormatDef(6, 2);
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.right;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(6, 2, xls.AddFormat(fmt));
//xls.SetCellValue(6, 2, "Nội dung ngân sách");
fmt = xls.GetCellVisibleFormatDef(6, 3);
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(6, 3, xls.AddFormat(fmt));
xls.SetCellValue(6, 3, "Tổng cộng");
fmt = xls.GetCellVisibleFormatDef(7, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(7, 1, xls.AddFormat(fmt));
xls.SetCellFormat(7, 3, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(7, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(7, 2, xls.AddFormat(fmt));
xls.SetCellValue(7, 2, "Đơn vị");
#region //Tạo tiêu đề cột
_C = TuCot;
for (int c = 0; c < SoTrang; c++)
{
xls.MergeCells(1, _C, 1, _C + SoCotCuaMotTrang-1);
fmt = xls.GetCellVisibleFormatDef(1, _C);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(1, _C, xls.AddFormat(fmt));
xls.SetCellValue(1, _C, "DỰ TOÁN CHI NGÂN SÁCH QUỐC PHÒNG NĂM <#Nam>");
xls.MergeCells(2, _C, 2, _C + SoCotCuaMotTrang-1);
fmt = xls.GetCellVisibleFormatDef(2, _C);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(2, _C, xls.AddFormat(fmt));
xls.SetCellValue(2, _C, "(Phần chi cho doanh nghiệp)");
fmt = xls.GetCellVisibleFormatDef(5, _C + SoCotCuaMotTrang - 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
xls.SetCellFormat(5, _C + SoCotCuaMotTrang - 1, xls.AddFormat(fmt));
xls.SetCellValue(5, _C + SoCotCuaMotTrang - 1, "Đơn vị tính:1.000 đ");
_C = _C + SoCotCuaMotTrang;
}
int csdt = TuCotCua_DT;
String TenCot = "";
for (int i = 0; i < TongSoCot ; i++)
{
fmt = xls.GetCellVisibleFormatDef(7, TuCot);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Font.CharSet = 0;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(7, TuCot, xls.AddFormat(fmt));
xls.SetCellFormat(6, TuCot+i, xls.AddFormat(fmt));
xls.SetCellValue(6, TuCot+i, "Trong đó");
TenCot = "";
if (csdt <= DenCotCua_DT)
TenCot = dt.Columns[csdt].ColumnName;
xls.SetCellValue(7, TuCot+i, TenCot);
xls.SetCellFormat(7, TuCot+i, xls.AddFormat(fmt));
csdt++;
}
#endregion
#endregion
//Cell selection and scroll position.
xls.SelectCell(17, 2, false);
//Protection
TSheetProtectionOptions SheetProtectionOptions;
SheetProtectionOptions = new TSheetProtectionOptions(false);
SheetProtectionOptions.SelectLockedCells = true;
SheetProtectionOptions.SelectUnlockedCells = true;
xls.Protection.SetSheetProtection(null, SheetProtectionOptions);
return xls;
}
/// <summary>
/// tạo tiêu để cho báo cáo
/// </summary>
/// <param name="xls"></param>
/// <param name="dt"></param>
/// <param name="TuHang"></param>
/// <param name="TuCot"></param>
/// <param name="TuCotCua_DT"></param>
/// <param name="DenCotCua_DT"></param>
/// <param name="SoCotTrang1"></param>
/// <param name="SoCotTrangLonHon1"></param>
/// <returns></returns>
public XlsFile TaoTieuDe(XlsFile xls, DataTable dt, int TuHang, int TuCot, int TuCotCua_DT, int DenCotCua_DT, int SoCotTrang1, int SoCotTrangLonHon1)
{
xls.NewFile(1); //Create a new Excel file with 1 sheet.
//Set the names of the sheets
xls.ActiveSheet = 1;
xls.SheetName = "Sheet1";
xls.ActiveSheet = 1; //Set the sheet we are working in.
//Global Workbook Options
xls.OptionsAutoCompressPictures = true;
#region //Styles.
//Styles.
TFlxFormat StyleFmt;
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "#,##0;-#,##0;;@";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "#,##0;-#,##0;;@";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "#,##0;-#,##0;;@";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "#,##0;-#,##0;;@";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2), StyleFmt);
#endregion
#region //Named Ranges
//Named Ranges
TXlsNamedRange Range;
string RangeName;
RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles);
Range = new TXlsNamedRange(RangeName, 1, 32, "='Sheet1'!$A:$B,'Sheet1'!$1:$4");
//You could also use: Range = new TXlsNamedRange(RangeName, 1, 0, 0, 0, 0, 0, 32);
xls.SetNamedRange(Range);
#endregion
#region //Printer Settings
THeaderAndFooter HeadersAndFooters = new THeaderAndFooter();
HeadersAndFooters.AlignMargins = true;
HeadersAndFooters.ScaleWithDoc = true;
HeadersAndFooters.DiffFirstPage = false;
HeadersAndFooters.DiffEvenPages = false;
HeadersAndFooters.DefaultHeader = "&L&\"Times New Roman,Bold\" <#Cap1>\n <#Cap2>&C&\"Times New Roman,Bold\"TỔNG HỢP CẤP NGÂN SÁCH - <#TruongTien>\n<#Dot>&R&\"Times New Roman,Italic\"\n\n\nĐơn vị tính:1000 đồng Trang:&P/&N ";
HeadersAndFooters.DefaultFooter = "";
HeadersAndFooters.FirstHeader = "";
HeadersAndFooters.FirstFooter = "";
HeadersAndFooters.EvenHeader = "";
HeadersAndFooters.EvenFooter = "";
xls.SetPageHeaderAndFooter(HeadersAndFooters);
//You can set the margins in 2 ways, the one commented here or the one below:
// TXlsMargins PrintMargins = xls.GetPrintMargins();
// PrintMargins.Left = 0.708661417322835;
// PrintMargins.Top = 0.393700787401575;
// PrintMargins.Right = 0.196850393700787;
// PrintMargins.Bottom = 0.590551181102362;
// PrintMargins.Header = 0.31496062992126;
// PrintMargins.Footer = 0.31496062992126;
// xls.SetPrintMargins(PrintMargins);
xls.SetPrintMargins(new TXlsMargins(0.708661417322835, 0.393700787401575, 0.196850393700787, 0.590551181102362, 0.31496062992126, 0.31496062992126));
xls.PrintXResolution = 600;
xls.PrintYResolution = 600;
xls.PrintOptions = TPrintOptions.None;
xls.PrintPaperSize = TPaperSize.A4;
//Printer Driver Settings are a blob of data specific to a printer
//This code is commented by default because normally you do not want to hard code the printer settings of an specific printer.
// byte[] PrinterData = {
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x00, 0x06, 0xDC, 0x00, 0x00, 0x00, 0x03, 0x2F, 0x00, 0x00, 0x02, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x64, 0x00, 0x01, 0x00, 0x0F, 0x00, 0x58, 0x02, 0x02, 0x00, 0x01, 0x00, 0x58, 0x02,
// 0x03, 0x00, 0x01, 0x00, 0x4C, 0x00, 0x65, 0x00, 0x74, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x01, 0x00,
// 0x00, 0x00, 0xFF, 0xFF, 0xFF, 0xFF, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00
// };
// TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData);
// xls.SetPrinterDriverSettings(PrinterDriverSettings);
#endregion
int TongSoHang = dt.Rows.Count;
int _TuCot = TuCot;
int TongSoCot = 0;
int SoTrang = 1;
if ((DenCotCua_DT - TuCotCua_DT) <= SoCotTrang1)
{
int SoCotDu = ((DenCotCua_DT - TuCotCua_DT)) % SoCotTrang1;
int SoCotCanThem = 0;
SoCotCanThem = SoCotTrang1 - SoCotDu;
TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem;
}
else
{
int SoCotDu = (DenCotCua_DT - TuCotCua_DT - SoCotTrang1) % SoCotTrangLonHon1;
int SoCotCanThem = 0;
SoCotCanThem = SoCotTrangLonHon1 - SoCotDu;
TongSoCot = (DenCotCua_DT - TuCotCua_DT) + SoCotCanThem;
SoTrang = 1 + (TongSoCot - SoCotTrang1) / SoCotTrangLonHon1;
}
#region //Set up rows and columns
//Set up rows and columns
xls.DefaultColWidth = 2340;
xls.SetColWidth(1, 1170); //(3.82 + 0.75) * 256
TFlxFormat ColFmt;
ColFmt = xls.GetFormat(xls.GetColFormat(1));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(1, xls.AddFormat(ColFmt));
xls.SetColWidth(2, 4827); //(18.11 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(2));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(2, xls.AddFormat(ColFmt));
xls.SetColWidth(3, 3657); //(13.54 + 0.75) * 256
for (int i = 0; i < TongSoCot; i++)
{
xls.SetColWidth(_TuCot + i, 3600);
}
xls.SetRowHeight(4, 800);
xls.DefaultRowHeight = 300;
#endregion
#region MagerCell
#endregion
#region //Set the cell values
#region set tieu de cot tinh
TFlxFormat fmt;
fmt = xls.GetCellVisibleFormatDef(4, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 1, xls.AddFormat(fmt));
xls.SetCellValue(4, 1, "STT");
fmt = xls.GetCellVisibleFormatDef(4, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 2, xls.AddFormat(fmt));
xls.SetCellValue(4, 2, "Tên đơn vị");
fmt = xls.GetCellVisibleFormatDef(4, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 3, xls.AddFormat(fmt));
xls.SetCellValue(4, 3, "Tổng cộng");
fmt = xls.GetCellVisibleFormatDef(4, 4);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 4, xls.AddFormat(fmt));
xls.SetCellValue(4, 4, 1);
fmt = xls.GetCellVisibleFormatDef(4, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 5, xls.AddFormat(fmt));
xls.SetCellValue(4, 5, 2);
fmt = xls.GetCellVisibleFormatDef(4, 6);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 6, xls.AddFormat(fmt));
xls.SetCellValue(4, 6, 3);
fmt = xls.GetCellVisibleFormatDef(4, 7);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 7, xls.AddFormat(fmt));
xls.SetCellValue(4, 7, 4);
fmt = xls.GetCellVisibleFormatDef(4, 8);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 8, xls.AddFormat(fmt));
xls.SetCellValue(4, 8, 5);
fmt = xls.GetCellVisibleFormatDef(4, 9);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 9, xls.AddFormat(fmt));
xls.SetCellValue(4, 9, 6);
fmt = xls.GetCellVisibleFormatDef(4, 10);
fmt.Font.Name = "Times New Roman";
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(4, 10, xls.AddFormat(fmt));
xls.SetCellValue(4, 10, 7);
#endregion
#region cau hinh chu ku
xls.MergeCells(TongSoHang + TuHang + 4, 1, TongSoHang + TuHang + 4, 2);
xls.MergeCells(TongSoHang + TuHang + 4, 3, TongSoHang + TuHang + 4, 4);
xls.MergeCells(TongSoHang + TuHang + 4, 5, TongSoHang + TuHang + 4, 6);
xls.MergeCells(TongSoHang + TuHang +4, 7, TongSoHang + TuHang + 4, 8);
xls.MergeCells(TongSoHang + TuHang + 4, 9, TongSoHang + TuHang + 4, 10);
// Thua lenh - chuc danh - ten
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 1, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 1, "<#row height(autofit)><#ThuaLenh1> \n<#ChucDanh1>\n\n\n\n\n\n\n<#Ten1>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang +4, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 3, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 3, "<#row height(autofit)><#ThuaLenh2> \n<#ChucDanh2>\n\n\n\n\n\n\n<#Ten2>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 5, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang +4, 5, "<#row height(autofit)><#ThuaLenh3> \n<#ChucDanh3>\n\n\n\n\n\n\n<#Ten3>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 5, 7);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 5, 7, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 5, 7, "<#row height(autofit)><#ThuaLenh4>\n<#ChucDanh4>\n\n\n\n\n\n\n<#Ten4>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 9);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 9, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 9, "<#row height(autofit)><#ThuaLenh5>\n<#ChucDanh5>\n\n\n\n\n\n\n<#Ten5>");
#endregion
#region set tieu de cot dong
#region set hang LNS
_TuCot = TuCot;
//neu so trang =1
//fmt = xls.GetCellVisibleFormatDef(3, _TuCot +5);
//fmt.Font.Name = "Times New Roman";
//fmt.Font.Style = TFlxFontStyles.Italic;
//fmt.Font.Family = 1;
//xls.SetCellFormat(3, _TuCot + 5, xls.AddFormat(fmt));
//xls.SetCellValue(3, _TuCot + 5, "Đơn vị tính: 1000 đ");
// ngay
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 9);
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 2, 9);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Italic;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.bottom;
xls.SetRowHeight(TongSoHang + TuHang + 2, 400);
fmt.Font.Family = 1;
xls.SetCellFormat(TongSoHang + TuHang + 2, 9, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 2, 9, "<#Ngay>");
for (int j = _TuCot + 1; j <= _TuCot + SoCotTrang1 - 1; j++)
{
fmt = xls.GetCellVisibleFormatDef(4, _TuCot);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 200;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, j, xls.AddFormat(fmt));
}
_TuCot = _TuCot + SoCotTrang1;
//set cac trang con lai
for (int i = 1; i < SoTrang; i++)
{
//fmt = xls.GetCellVisibleFormatDef(3, _TuCot + 5);
//fmt.Font.Name = "Times New Roman";
//fmt.Font.Style = TFlxFontStyles.Italic;
//fmt.Font.Family = 1;
//xls.SetCellFormat(3, _TuCot + 5, xls.AddFormat(fmt));
//xls.SetCellValue(3, _TuCot + 5, "Đơn vị tính: 1000 đ");
//Ngay
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i);
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Italic;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.right;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 1, 10 + SoCotTrangLonHon1 * i, "<#Ngay>");
//cau hinh chu ki cho trang lon hon 1
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 3 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh2> \n<#ChucDanh2>\n\n\n\n\n\n\n<#Ten2>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 5 + SoCotTrangLonHon1 * i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang +4, 5 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 5 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh3> \n<#ChucDanh3>\n\n\n\n\n\n\n<#Ten3>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 7 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh4> \n<#ChucDanh4>\n\n\n\n\n\n\n<#Ten4>");
fmt = xls.GetCellVisibleFormatDef(TongSoHang + TuHang +4, 9 + SoCotTrangLonHon1 * i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 220;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
fmt.WrapText = true;
xls.SetCellFormat(TongSoHang + TuHang + 4, 9 + SoCotTrangLonHon1 * i, xls.AddFormat(fmt));
xls.SetCellValue(TongSoHang + TuHang + 4, 9 + SoCotTrangLonHon1 * i, "<#row height(autofit)><#ThuaLenh5> \n<#ChucDanh5>\n\n\n\n\n\n\n<#Ten5>");
//
for (int j = _TuCot + 1; j <= _TuCot + SoCotTrangLonHon1 - 1; j++)
{
fmt = xls.GetCellVisibleFormatDef(4, _TuCot);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 160;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, j, xls.AddFormat(fmt));
}
_TuCot = _TuCot + SoCotTrangLonHon1;
}
#endregion
#endregion
#region set cac cot loai ngan sach
_TuCot = TuCot;
String TenCot;
int _TuCotCua_DT = TuCotCua_DT;
if (SoTrang == 1)
{
for (int i = 0; i < TongSoCot; i++)
{
fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 160;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.WrapText = true;
fmt.VAlignment = TVFlxAlignment.center;
TenCot = "";
if (DenCotCua_DT > _TuCotCua_DT)
{
TenCot = "<#LNS" + i + ">";
}
xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt));
xls.SetCellValue(4, _TuCot + i, TenCot);
}
}
else
{
for (int i = 0; i < SoCotTrang1; i++)
{
fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 160;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.WrapText = true;
fmt.VAlignment = TVFlxAlignment.center;
TenCot = "";
if (DenCotCua_DT > _TuCotCua_DT)
{
TenCot = "<#LNS" + i + ">";
}
xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt));
xls.SetCellValue(4, _TuCot + i, TenCot);
}
_TuCotCua_DT = _TuCotCua_DT + SoCotTrang1;
_TuCot = _TuCot + SoCotTrang1;
for (int i = 0; i < TongSoCot - SoCotTrang1; i++)
{
fmt = xls.GetCellVisibleFormatDef(4, _TuCot + i);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 160;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.WrapText = true;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
TenCot = "";
int a = Convert.ToInt16(SoCotTrang1) + i;
if (DenCotCua_DT > _TuCotCua_DT)
{
TenCot = "<#LNS" + a + ">";
}
xls.SetCellFormat(4, _TuCot + i, xls.AddFormat(fmt));
xls.SetCellValue(4, _TuCot + i, TenCot);
}
}
#endregion
#endregion
//Cell selection and scroll position.
//Protection
TSheetProtectionOptions SheetProtectionOptions;
SheetProtectionOptions = new TSheetProtectionOptions(false);
SheetProtectionOptions.SelectLockedCells = true;
SheetProtectionOptions.SelectUnlockedCells = true;
xls.Protection.SetSheetProtection(null, SheetProtectionOptions);
return xls;
}
/// <summary>
/// Tạo tiêu đề
/// </summary>
/// <param name="xls"></param>
/// <returns></returns>
public XlsFile TaoTieuDe(XlsFile xls)
{
xls.NewFile(1); //Create a new Excel file with 1 sheet.
//Set the names of the sheets
xls.ActiveSheet = 1;
xls.SheetName = "Sheet1";
xls.ActiveSheet = 1; //Set the sheet we are working in.
//Global Workbook Options
xls.OptionsAutoCompressPictures = true;
xls.OptionsCheckCompatibility = false;
//Styles.
TFlxFormat StyleFmt;
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 4), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Normal, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "_(* #,##0_);_(* \\(#,##0\\);_(* \"-\"_);_(@_)";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Comma0, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "_(\"$\"* #,##0_);_(\"$\"* \\(#,##0\\);_(\"$\"* \"-\"_);_(@_)";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency0, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Percent, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
StyleFmt.Format = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.Currency, 0), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 1), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.RowLevel, 2), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 1), StyleFmt);
StyleFmt = xls.GetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2));
StyleFmt.Font.Name = "Calibri";
StyleFmt.Font.Size20 = 220;
StyleFmt.Font.Color = TExcelColor.FromTheme(TThemeColor.Foreground1);
StyleFmt.Font.Family = 2;
xls.SetStyle(xls.GetBuiltInStyleName(TBuiltInStyle.ColLevel, 2), StyleFmt);
//Named Ranges
TXlsNamedRange Range;
string RangeName;
RangeName = TXlsNamedRange.GetInternalName(InternalNameRange.Print_Titles);
Range = new TXlsNamedRange(RangeName, 1, 32, "='Sheet1'!$4:$4");
//You could also use: Range = new TXlsNamedRange(RangeName, 1, 1, 4, 1, 4, FlxConsts.Max_Columns + 1, 32);
xls.SetNamedRange(Range);
//Printer Settings
THeaderAndFooter HeadersAndFooters = new THeaderAndFooter();
HeadersAndFooters.AlignMargins = true;
HeadersAndFooters.ScaleWithDoc = true;
HeadersAndFooters.DiffFirstPage = true;
HeadersAndFooters.DiffEvenPages = false;
HeadersAndFooters.DefaultHeader = "&R&\"Times New Roman,Italic\"Trang: &P ";
HeadersAndFooters.DefaultFooter = "";
HeadersAndFooters.FirstHeader = "&R&\"Times New Roman,Italic\"\n\n\n\nTrang: &P ";
HeadersAndFooters.FirstFooter = "";
HeadersAndFooters.EvenHeader = "";
HeadersAndFooters.EvenFooter = "";
xls.SetPageHeaderAndFooter(HeadersAndFooters);
//You can set the margins in 2 ways, the one commented here or the one below:
// TXlsMargins PrintMargins = xls.GetPrintMargins();
// PrintMargins.Left = 0.590551181102362;
// PrintMargins.Top = 0.551181102362205;
// PrintMargins.Right = 0.196850393700787;
// PrintMargins.Bottom = 0.393700787401575;
// PrintMargins.Header = 0.31496062992126;
// PrintMargins.Footer = 0.31496062992126;
// xls.SetPrintMargins(PrintMargins);
xls.SetPrintMargins(new TXlsMargins(0.590551181102362, 0.551181102362205, 0.196850393700787, 0.393700787401575, 0.31496062992126, 0.31496062992126));
xls.PrintXResolution = 600;
xls.PrintYResolution = 600;
xls.PrintOptions = TPrintOptions.Orientation;
xls.PrintPaperSize = TPaperSize.A4;
//Printer Driver Settings are a blob of data specific to a printer
//This code is commented by default because normally you do not want to hard code the printer settings of an specific printer.
// byte[] PrinterData = {
// 0x00, 0x00, 0x4D, 0x00, 0x69, 0x00, 0x63, 0x00, 0x72, 0x00, 0x6F, 0x00, 0x73, 0x00, 0x6F, 0x00, 0x66, 0x00, 0x74, 0x00, 0x20, 0x00, 0x58, 0x00, 0x50, 0x00, 0x53, 0x00, 0x20, 0x00, 0x44, 0x00, 0x6F, 0x00, 0x63, 0x00, 0x75, 0x00, 0x6D, 0x00, 0x65, 0x00, 0x6E, 0x00, 0x74, 0x00, 0x20, 0x00, 0x57, 0x00,
// 0x72, 0x00, 0x69, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x04, 0x00, 0x06, 0xDC, 0x00, 0x78, 0x03, 0x03, 0xAF, 0x00, 0x00, 0x01, 0x00, 0x09, 0x00, 0xEA, 0x0A, 0x6F, 0x08, 0x64, 0x00, 0x01, 0x00, 0x0F, 0x00, 0x58, 0x02, 0x02, 0x00, 0x01, 0x00, 0x58, 0x02,
// 0x03, 0x00, 0x00, 0x00, 0x4C, 0x00, 0x65, 0x00, 0x74, 0x00, 0x74, 0x00, 0x65, 0x00, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x02, 0x00, 0x00, 0x00, 0x01, 0x00,
// 0x00, 0x00, 0xFF, 0xFF, 0xFF, 0xFF, 0x47, 0x49, 0x53, 0x34, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x44, 0x49, 0x4E, 0x55, 0x22, 0x00, 0x20, 0x01, 0x5C, 0x03, 0x1C, 0x00, 0xCA, 0xD2, 0xF6, 0x72, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x09, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x20, 0x01, 0x00, 0x00, 0x53, 0x4D,
// 0x54, 0x4A, 0x00, 0x00, 0x00, 0x00, 0x10, 0x00, 0x10, 0x01, 0x7B, 0x00, 0x30, 0x00, 0x46, 0x00, 0x34, 0x00, 0x31, 0x00, 0x33, 0x00, 0x30, 0x00, 0x44, 0x00, 0x44, 0x00, 0x2D, 0x00, 0x31, 0x00, 0x39, 0x00, 0x43, 0x00, 0x37, 0x00, 0x2D, 0x00, 0x37, 0x00, 0x61, 0x00, 0x62, 0x00, 0x36, 0x00, 0x2D, 0x00,
// 0x39, 0x00, 0x39, 0x00, 0x41, 0x00, 0x31, 0x00, 0x2D, 0x00, 0x39, 0x00, 0x38, 0x00, 0x30, 0x00, 0x46, 0x00, 0x30, 0x00, 0x33, 0x00, 0x42, 0x00, 0x32, 0x00, 0x45, 0x00, 0x45, 0x00, 0x34, 0x00, 0x45, 0x00, 0x7D, 0x00, 0x00, 0x00, 0x49, 0x6E, 0x70, 0x75, 0x74, 0x42, 0x69, 0x6E, 0x00, 0x46, 0x4F, 0x52,
// 0x4D, 0x53, 0x4F, 0x55, 0x52, 0x43, 0x45, 0x00, 0x52, 0x45, 0x53, 0x44, 0x4C, 0x4C, 0x00, 0x55, 0x6E, 0x69, 0x72, 0x65, 0x73, 0x44, 0x4C, 0x4C, 0x00, 0x49, 0x6E, 0x74, 0x65, 0x72, 0x6C, 0x65, 0x61, 0x76, 0x69, 0x6E, 0x67, 0x00, 0x4F, 0x46, 0x46, 0x00, 0x49, 0x6D, 0x61, 0x67, 0x65, 0x54, 0x79, 0x70,
// 0x65, 0x00, 0x4A, 0x50, 0x45, 0x47, 0x4D, 0x65, 0x64, 0x00, 0x4F, 0x72, 0x69, 0x65, 0x6E, 0x74, 0x61, 0x74, 0x69, 0x6F, 0x6E, 0x00, 0x50, 0x4F, 0x52, 0x54, 0x52, 0x41, 0x49, 0x54, 0x00, 0x43, 0x6F, 0x6C, 0x6C, 0x61, 0x74, 0x65, 0x00, 0x4F, 0x46, 0x46, 0x00, 0x52, 0x65, 0x73, 0x6F, 0x6C, 0x75, 0x74,
// 0x69, 0x6F, 0x6E, 0x00, 0x4F, 0x70, 0x74, 0x69, 0x6F, 0x6E, 0x31, 0x00, 0x50, 0x61, 0x70, 0x65, 0x72, 0x53, 0x69, 0x7A, 0x65, 0x00, 0x4C, 0x45, 0x54, 0x54, 0x45, 0x52, 0x00, 0x43, 0x6F, 0x6C, 0x6F, 0x72, 0x4D, 0x6F, 0x64, 0x65, 0x00, 0x32, 0x34, 0x62, 0x70, 0x70, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x1C, 0x00, 0x00, 0x00, 0x56, 0x34, 0x44, 0x4D, 0x01, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
// 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00
// };
// TPrinterDriverSettings PrinterDriverSettings = new TPrinterDriveSettings(PrinterData);
// xls.SetPrinterDriverSettings(PrinterDriverSettings);
//Set up rows and columns
xls.DefaultColWidth = 2340;
xls.SetColWidth(1, 1901); //(6.68 + 0.75) * 256
TFlxFormat ColFmt;
ColFmt = xls.GetFormat(xls.GetColFormat(1));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(1, xls.AddFormat(ColFmt));
xls.SetColWidth(2, 5778); //(21.82 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(2));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(2, xls.AddFormat(ColFmt));
xls.SetColWidth(3, 3840); //(14.25 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(3));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(3, xls.AddFormat(ColFmt));
xls.SetColWidth(4, 1901); //(6.68 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(4));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(4, xls.AddFormat(ColFmt));
xls.SetColWidth(5, 5778); //(21.82 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(5));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(5, xls.AddFormat(ColFmt));
xls.SetColWidth(6, 3840); //(14.25 + 0.75) * 256
ColFmt = xls.GetFormat(xls.GetColFormat(6));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(6, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(7));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(7, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(8));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(8, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(9));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(9, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(10));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(10, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(11));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(11, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(12));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(12, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(13));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(13, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(14));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(14, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(15));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(15, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(16));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(16, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(17));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(17, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(18));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(18, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(19));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(19, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(20));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(20, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(21));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(21, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(22));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(22, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(23));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(23, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(24));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(24, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(25));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(25, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(26));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(26, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(27));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(27, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(28));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(28, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(29));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(29, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(30));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(30, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(31));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(31, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(32));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(32, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(33));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(33, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(34));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(34, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(35));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(35, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(36));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(36, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(37));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(37, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(38));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(38, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(39));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(39, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(40));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(40, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(41));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(41, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(42));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(42, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(43));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(43, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(44));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(44, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(45));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(45, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(46));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(46, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(47));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(47, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(48));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(48, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(49));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(49, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(50));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(50, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(51));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(51, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(52));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(52, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(53));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(53, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(54));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(54, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(55));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(55, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(56));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(56, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(57));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(57, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(58));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(58, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(59));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(59, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(60));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(60, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(61));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(61, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(62));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(62, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(63));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(63, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(64));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(64, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(65));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(65, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(66));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(66, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(67));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(67, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(68));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(68, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(69));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(69, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(70));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(70, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(71));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(71, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(72));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(72, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(73));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(73, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(74));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(74, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(75));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(75, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(76));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(76, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(77));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(77, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(78));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(78, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(79));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(79, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(80));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(80, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(81));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(81, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(82));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(82, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(83));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(83, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(84));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(84, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(85));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(85, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(86));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(86, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(87));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(87, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(88));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(88, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(89));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(89, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(90));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(90, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(91));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(91, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(92));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(92, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(93));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(93, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(94));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(94, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(95));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(95, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(96));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(96, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(97));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(97, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(98));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(98, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(99));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(99, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(100));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(100, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(101));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(101, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(102));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(102, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(103));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(103, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(104));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(104, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(105));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(105, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(106));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(106, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(107));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(107, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(108));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(108, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(109));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(109, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(110));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(110, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(111));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(111, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(112));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(112, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(113));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(113, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(114));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(114, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(115));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(115, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(116));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(116, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(117));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(117, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(118));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(118, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(119));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(119, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(120));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(120, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(121));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(121, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(122));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(122, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(123));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(123, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(124));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(124, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(125));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(125, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(126));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(126, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(127));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(127, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(128));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(128, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(129));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(129, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(130));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(130, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(131));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(131, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(132));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(132, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(133));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(133, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(134));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(134, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(135));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(135, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(136));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(136, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(137));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(137, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(138));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(138, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(139));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(139, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(140));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(140, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(141));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(141, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(142));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(142, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(143));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(143, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(144));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(144, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(145));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(145, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(146));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(146, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(147));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(147, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(148));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(148, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(149));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(149, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(150));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(150, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(151));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(151, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(152));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(152, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(153));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(153, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(154));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(154, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(155));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(155, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(156));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(156, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(157));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(157, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(158));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(158, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(159));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(159, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(160));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(160, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(161));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(161, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(162));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(162, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(163));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(163, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(164));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(164, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(165));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(165, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(166));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(166, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(167));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(167, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(168));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(168, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(169));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(169, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(170));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(170, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(171));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(171, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(172));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(172, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(173));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(173, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(174));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(174, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(175));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(175, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(176));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(176, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(177));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(177, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(178));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(178, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(179));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(179, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(180));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(180, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(181));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(181, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(182));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(182, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(183));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(183, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(184));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(184, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(185));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(185, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(186));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(186, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(187));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(187, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(188));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(188, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(189));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(189, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(190));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(190, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(191));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(191, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(192));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(192, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(193));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(193, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(194));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(194, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(195));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(195, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(196));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(196, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(197));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(197, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(198));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(198, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(199));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(199, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(200));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(200, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(201));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(201, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(202));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(202, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(203));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(203, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(204));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(204, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(205));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(205, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(206));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(206, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(207));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(207, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(208));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(208, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(209));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(209, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(210));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(210, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(211));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(211, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(212));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(212, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(213));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(213, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(214));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(214, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(215));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(215, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(216));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(216, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(217));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(217, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(218));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(218, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(219));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(219, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(220));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(220, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(221));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(221, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(222));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(222, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(223));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(223, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(224));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(224, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(225));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(225, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(226));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(226, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(227));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(227, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(228));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(228, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(229));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(229, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(230));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(230, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(231));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(231, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(232));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(232, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(233));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(233, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(234));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(234, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(235));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(235, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(236));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(236, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(237));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(237, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(238));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(238, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(239));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(239, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(240));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(240, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(241));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(241, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(242));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(242, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(243));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(243, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(244));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(244, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(245));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(245, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(246));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(246, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(247));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(247, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(248));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(248, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(249));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(249, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(250));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(250, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(251));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(251, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(252));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(252, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(253));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(253, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(254));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(254, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(255));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(255, xls.AddFormat(ColFmt));
ColFmt = xls.GetFormat(xls.GetColFormat(256));
ColFmt.Font.Name = "Times New Roman";
ColFmt.Font.Family = 1;
xls.SetColFormat(256, xls.AddFormat(ColFmt));
xls.DefaultRowHeight = 300;
xls.SetRowHeight(1, 330); //16.50 * 20
xls.SetRowHeight(2, 315); //15.75 * 20
xls.SetRowHeight(4, 390); //19.50 * 20
//Merged Cells
xls.MergeCells(3, 3, 3, 5);
xls.MergeCells(1, 1, 1, 6);
xls.MergeCells(2, 1, 2, 6);
xls.MergeCells(2, 3, 2, 5);
//Set the cell values
TFlxFormat fmt;
fmt = xls.GetCellVisibleFormatDef(1, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 280;
fmt.Font.Family = 1;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
xls.SetCellValue(1, 1, "DANH SÁCH CHI TRẢ CÁ NHÂN");
fmt = xls.GetCellVisibleFormatDef(1, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 2, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 3, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 4);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 4, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 5, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 6);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 6, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 7);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 7, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 8);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 8, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(1, 9);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 260;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(1, 9, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 1, xls.AddFormat(fmt));
xls.SetCellValue(2, 1, "Tháng <#Thang> / <#Nam>");
fmt = xls.GetCellVisibleFormatDef(2, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 2, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 3, xls.AddFormat(fmt));
xls.MergeCells(2, 1, 2, 6);
xls.SetCellValue(2, 3, "DANH SÁCH CHI TRẢ CÁ NHÂN");
fmt = xls.GetCellVisibleFormatDef(2, 4);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 4, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.HAlignment = THFlxAlignment.left;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 5, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 6);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 6, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 7);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 7, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 8);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 8, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(2, 9);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(2, 9, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(3, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Size20 = 240;
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(3, 1, xls.AddFormat(fmt));
xls.SetCellValue(3, 1, "<#Auto page breaks>");
fmt = xls.GetCellVisibleFormatDef(3, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(3, 3, xls.AddFormat(fmt));
xls.SetCellValue(3, 3, "");
fmt = xls.GetCellVisibleFormatDef(3, 4);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(3, 4, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(3, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
xls.SetCellFormat(3, 5, xls.AddFormat(fmt));
fmt = xls.GetCellVisibleFormatDef(4, 1);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 1, xls.AddFormat(fmt));
xls.SetCellValue(4, 1, "TT");
fmt = xls.GetCellVisibleFormatDef(4, 2);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 2, xls.AddFormat(fmt));
xls.SetCellValue(4, 2, "SỐ TÀI KHOẢN");
fmt = xls.GetCellVisibleFormatDef(4, 3);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 3, xls.AddFormat(fmt));
xls.SetCellValue(4, 3, "SỐ TIỀN");
fmt = xls.GetCellVisibleFormatDef(4, 4);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 4, xls.AddFormat(fmt));
xls.SetCellValue(4, 4, "TT");
fmt = xls.GetCellVisibleFormatDef(4, 5);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 5, xls.AddFormat(fmt));
xls.SetCellValue(4, 5, "SỐ TÀI KHOẢN");
fmt = xls.GetCellVisibleFormatDef(4, 6);
fmt.Font.Name = "Times New Roman";
fmt.Font.Style = TFlxFontStyles.Bold;
fmt.Font.Family = 1;
fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
fmt.Borders.Left.Color = TExcelColor.Automatic;
fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
fmt.Borders.Right.Color = TExcelColor.Automatic;
fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
fmt.Borders.Top.Color = TExcelColor.Automatic;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
fmt.Borders.Bottom.Color = TExcelColor.Automatic;
fmt.HAlignment = THFlxAlignment.center;
fmt.VAlignment = TVFlxAlignment.center;
xls.SetCellFormat(4, 6, xls.AddFormat(fmt));
xls.SetCellValue(4, 6, "SỐ TIỀN");
//Cell selection and scroll position.
xls.SelectCell(11, 2, false);
//Protection
TSheetProtectionOptions SheetProtectionOptions;
SheetProtectionOptions = new TSheetProtectionOptions(false);
SheetProtectionOptions.SelectLockedCells = true;
SheetProtectionOptions.SelectUnlockedCells = true;
xls.Protection.SetSheetProtection(null, SheetProtectionOptions);
return xls;
}
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 void AddData(ExcelFile Xls)
{
string TemplateFile = "template.xls";
if (cbXlsxTemplate.Checked)
{
if (!XlsFile.SupportsXlsx)
{
throw new Exception("Xlsx files are not supported in this version of the .NET framework");
}
TemplateFile = "template.xlsm";
}
// Open an existing file to be used as template. In this example this file has
// little data, in a real situation it should have as much as possible. (Or even better, be a report)
Xls.Open(Path.Combine(PathToExe, TemplateFile));
//Find the cell where we want to fill the data. In this case, we have created a named range "data" so the address
//is not hardcoded here.
TXlsNamedRange DataCell = Xls.GetNamedRange("Data", -1);
//Add a chart with totals
AddChart(DataCell, Xls);
//Note that "DataCell" will change because we inserted rows above it when creating the chart. But we will keep using the old one.
//Add the captions. This should probably go into the template, but in a dynamic environment it might go here.
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left, "Country");
Xls.SetCellValue(DataCell.Top - 1, DataCell.Left + 1, "Quantity");
//Add a rectangle around the cells
TFlxApplyFormat ApplyFormat = new TFlxApplyFormat();
ApplyFormat.SetAllMembers(false);
ApplyFormat.Borders.SetAllMembers(true); //We will only apply the borders to the existing cell formats
TFlxFormat fmt = Xls.GetDefaultFormat;
fmt.Borders.Left.Style = TFlxBorderStyle.Double;
fmt.Borders.Left.Color = Color.Black;
fmt.Borders.Right.Style = TFlxBorderStyle.Double;
fmt.Borders.Right.Color = Color.Black;
fmt.Borders.Top.Style = TFlxBorderStyle.Double;
fmt.Borders.Top.Color = Color.Black;
fmt.Borders.Bottom.Style = TFlxBorderStyle.Double;
fmt.Borders.Bottom.Color = Color.Black;
Xls.SetCellFormat(DataCell.Top - 1, DataCell.Left, DataCell.Top, DataCell.Left + 1, fmt, ApplyFormat, true); //Set last parameter to true so it draws a box.
//Freeze panes
Xls.FreezePanes(new TCellAddress(DataCell.Top, 1));
Random Rnd = new Random();
//Fill the data
int z = 0;
int OutlineLevel = 0;
for (int r = 0; r <= DataRows; r++)
{
//Fill the values.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left, Country[z % Country.Length]); //For non C# users, "%" means "mod" or modulus in other languages. It is the rest of the integer division.
Xls.SetCellValue(DataCell.Top + r, DataCell.Left + 1, Rnd.Next(1000));
//Add the country to the outline
Xls.SetRowOutlineLevel(DataCell.Top + r, OutlineLevel);
//increment the country randomly
if (Rnd.Next(3) == 0)
{
z++;
OutlineLevel = 0; //Break the group and create a new one.
}
else
{
OutlineLevel = 1;
}
}
//Make the "+" signs of the outline appear at the top.
Xls.OutlineSummaryRowsBelowDetail = false;
//Collapse the outline to the first level.
Xls.CollapseOutlineRows(1, TCollapseChildrenMode.Collapsed);
//Add Data Validation for the first column, it must be a country.
TDataValidationInfo dv = new TDataValidationInfo(
TDataValidationDataType.List, //We will use a built in list.
TDataValidationConditionType.Between, //This parameter does not matter since it is a list. It will not be used.
"=\"" + GetCountryList() + "\"", //We could have used a range of cells here with the values (like "=C1..C4") Instead, we directly entered the list in the formula.
null, //no need for a second formula, not used in List
false,
true,
true, //Note that as we entered the data directly in FirstFormula, we need to set this to true
true,
"Unknown country",
"Please make sure that the country is in the list",
false, //We will not use an input box, so this is false and the 2 next entries are null
null,
null,
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left, DataCell.Top + DataRows, DataCell.Left), dv);
//Add Data Validation for the second column, it must be an integer between 0 and 1000.
dv = new TDataValidationInfo(
TDataValidationDataType.WholeNumber, //We will request a number.
TDataValidationConditionType.Between,
"=0", //First formula marks the first part of the "between" condition.
"=1000", //Second formula is the second part.
false,
false,
false,
true,
"Invalid Quantity",
null, //We will leave the default error message.
true,
"Quantity:",
"Please enter a quantity between 0 and 1000",
TDataValidationIcon.Stop); //We will use the stop icon so no invalid input is permitted.
Xls.AddDataValidation(new TXlsCellRange(DataCell.Top, DataCell.Left + 1, DataCell.Top + DataRows, DataCell.Left + 1), dv);
//Search country "Unknown" and replace it by "no".
//This does not make any sense here (we could just have entered "no" to begin)
//but it shows how to do it when modifying an existing file
Xls.Replace("Unknown", "no", TXlsCellRange.FullRange(), true, false, true);
//Autofit the rows. As we keep the row height automatic this will not show when opening in Excel, but will work when directly printing from FlexCel.
Xls.AutofitRowsOnWorkbook(false, true, 1);
Xls.Recalc(); //Calculate the SUMIF formulas so we can sort by them. Note that FlexCel automatically recalculates before saving,
//but in this case we haven't saved yet, so the sheet is not recalculated. You do not normally need to call Recalc directly.
//Sort the data. As in the case with replace, this does not make much sense. We could have entered the data sorted to begin
//But it shows how you can use the feature.
//Find the cell where the chart goes.
TXlsNamedRange ChartRange = Xls.GetNamedRange("ChartData", -1);
Xls.Sort(new TXlsCellRange(ChartRange.Top, ChartRange.Left, ChartRange.Top + Country.Length, ChartRange.Left + 1),
true, new int[] { 2 }, new TSortOrder[] { TSortOrder.Descending }, null);
//Protect the Sheet
TSheetProtectionOptions Sp = new TSheetProtectionOptions(false); //Create default protection options that allows everything.
Sp.InsertColumns = false; //Restrict inserting columns.
Xls.Protection.SetSheetProtection("flexcel", Sp);
//Set a modify password. Note that this does *not* encrypt the file.
Xls.Protection.SetModifyPassword("flexcel", true, "flexcel");
Xls.Protection.OpenPassword = "******"; //OpenPasword is the only password that will actually encrypt the file, so you will not be able to open it with flexcel if you do not know the password.
//Select cell A1
Xls.SelectCell(1, 1, true);
}
public static 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
}
internal override void Execute(ExcelFile Workbook, TWaitingCoords Coords, TBand Band)
{
ExcelFile IncludedReport = FInclude.Run();
//Workbook.InsertAndCopyRange(IncludedReport.GetNamedRange(FInclude.RangeName, IncludedReport.ActiveSheet),
// Top+RowOfs, Left+ColOfs, 1, InsertMode , TRangeCopyMode.All, IncludedReport, IncludedReport.ActiveSheet);
//This is to avoid inserting one row more on the include.
TXlsNamedRange range = IncludedReport.GetNamedRange(FInclude.RangeName, IncludedReport.ActiveSheet);
//We don't want to copy he full range even if using "__". Just *insert* the full range
//if (InsertMode == TFlxInsertMode.ShiftRowDown) {range.Left = 1; range.Right = FlxConsts.Max_Columns + 1;}
//if (InsertMode == TFlxInsertMode.ShiftColRight) {range.Top = 1; range.Bottom = FlxConsts.Max_Rows + 1;}
if (InsertMode == TFlxInsertMode.ShiftColRight || InsertMode == TFlxInsertMode.ShiftRangeRight)
{
TXlsCellRange rangeToInsert = new TXlsCellRange(range.Top, range.Left, range.Bottom, range.Right - 1);
if (InsertMode == TFlxInsertMode.ShiftColRight)
{
rangeToInsert.Top = 1; rangeToInsert.Bottom = FlxConsts.Max_Rows + 1;
}
if (range.Left > range.Right)
{
} // not possible. Workbook.DeleteRange();
else if (range.Left < range.Right)
{
int CopyTop = Top + Coords.RowOfs;
if (InsertMode == TFlxInsertMode.ShiftColRight)
{
CopyTop = 1;
}
Workbook.InsertAndCopyRange(rangeToInsert, CopyTop, Left + Coords.ColOfs, 1, InsertMode, TRangeCopyMode.None);
if (Band != null)
{
Band.AddTmpExpandedCols(rangeToInsert.ColCount, Top + Coords.RowOfs, Top + Coords.RowOfs + rangeToInsert.RowCount);
Band.TmpPartialCols += rangeToInsert.ColCount;
}
}
CopyRowAndColFormat(Workbook, Coords, IncludedReport, range);
if (range.Left <= range.Right)
{
Workbook.InsertAndCopyRange(range, Top + Coords.RowOfs, Left + Coords.ColOfs, 1, TFlxInsertMode.NoneRight, TRangeCopyMode.AllIncludingDontMoveAndSizeObjects, IncludedReport, IncludedReport.ActiveSheet);
}
}
else
{
TXlsCellRange rangeToInsert = new TXlsCellRange(range.Top, range.Left, range.Bottom - 1, range.Right);
if (InsertMode == TFlxInsertMode.ShiftRowDown)
{
rangeToInsert.Left = 1; rangeToInsert.Right = FlxConsts.Max_Columns + 1;
}
if (range.Top > range.Bottom)
{
} // not possible. Workbook.DeleteRange();
else if (range.Top < range.Bottom)
{
int CopyLeft = Left + Coords.ColOfs;
if (InsertMode == TFlxInsertMode.ShiftRowDown)
{
CopyLeft = 1;
}
Workbook.InsertAndCopyRange(rangeToInsert, Top + Coords.RowOfs, CopyLeft, 1, InsertMode, TRangeCopyMode.None);
if (Band != null)
{
Band.AddTmpExpandedRows(rangeToInsert.RowCount, Left + Coords.ColOfs, Left + Coords.ColOfs + rangeToInsert.ColCount);
Band.TmpPartialRows += rangeToInsert.RowCount;
}
}
CopyRowAndColFormat(Workbook, Coords, IncludedReport, range);
if (range.Top <= range.Bottom)
{
Workbook.InsertAndCopyRange(range, Top + Coords.RowOfs, Left + Coords.ColOfs, 1, TFlxInsertMode.NoneDown, TRangeCopyMode.AllIncludingDontMoveAndSizeObjects, IncludedReport, IncludedReport.ActiveSheet);
}
}
}
protected void CalcBounds(TOneCellValue aRange)
{
TValueAndXF val = new TValueAndXF();
val.Workbook = aRange.Workbook;
aRange.Evaluate(0, 0, 0, 0, val);
TXlsNamedRange XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, val.Workbook.ActiveSheet);
if (XlsRange == null)
{
XlsRange = val.Workbook.GetNamedRange(FlxConvert.ToString(val.Value), -1, 0);
}
if (XlsRange != null)
{
FTop = XlsRange.Top;
FLeft = XlsRange.Left;
Bottom = XlsRange.Bottom;
Right = XlsRange.Right;
Sheet1 = XlsRange.SheetIndex;
Sheet2 = XlsRange.SheetIndex;
RowAbs1 = false; ColAbs1 = false; RowAbs2 = false; ColAbs2 = false;
return;
}
string[] Addresses = FlxConvert.ToString(val.Value).Split(TFormulaMessages.TokenChar(TFormulaToken.fmRangeSep));
if (Addresses == null || (Addresses.Length != 2 && Addresses.Length != 1))
{
FlxMessages.ThrowException(FlxErr.ErrInvalidRef, FlxConvert.ToString(val.Value));
}
TCellAddress FirstCell = new TCellAddress(Addresses[0]);
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet1 = -1;
}
else
{
Sheet1 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
FTop = FirstCell.Row;
FLeft = FirstCell.Col;
RowAbs1 = FirstCell.RowAbsolute;
ColAbs1 = FirstCell.ColAbsolute;
if (Addresses.Length > 1)
{
FirstCell = new TCellAddress(Addresses[1]);
}
if (FirstCell.Sheet == null || FirstCell.Sheet.Length == 0)
{
Sheet2 = Sheet1;
}
else
{
Sheet2 = aRange.Workbook.GetSheetIndex(FirstCell.Sheet);
}
Bottom = FirstCell.Row;
Right = FirstCell.Col;
RowAbs2 = FirstCell.RowAbsolute;
ColAbs2 = FirstCell.ColAbsolute;
}
/// <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);
}