C# XlsFile.SetCellValue方法代码示例

        void CreateSpreadsheet()
        {
            var locations = LocationHelper.Instance.Locations;

            var xls = new XlsFile (1, true);
            xls.SetCellValue (1, 1, "Location data from Xamarin.iOS");
            xls.SetCellValue (2, 1, "Latitude");
            xls.SetCellValue (2, 2, "Longitude");
            xls.SetCellValue (2, 3, "Altitude");
            int row = 2;

            xls.MergeCells(1, 1, 1, 3);

            SetTitleCellFormat (xls);

            locations.ForEach (l => {
                xls.SetCellValue (++row, 1, l.Coordinate.Latitude);
                xls.SetCellValue (row, 2, l.Coordinate.Longitude);
                xls.SetCellValue (row, 3, l.Altitude);
            });

            xls.Save (path);
        }

 internal void SetCellValue(XlsFile xls, Rectangle area, object value, bool hasBorder)
 {
     xls.SetCellValue(area.Top, area.Left, value);
     if( hasBorder )
     {
         SetBorder(xls, area, hasBorder);
     }
 }

        //
        // GET: /Tally_Sheet_Export/Details/T0001
        public ActionResult Download_CM(int id)
        {
            //temp file name
            string copy_file_name = DateTime.Now.Ticks + ".xlsx";
            //template file name
            string Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Cargo_Manifest_Export.xlsx");
            //temp file path
            string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/App_Data"), copy_file_name);

            XlsFile cargo = new XlsFile(Orginal_file_path);

            cargo.ActiveSheet = 1;

            cargo.SetCellValue(1, 2, "Cargo Manifest - " + id);

            var cargo_details = from t in db.E_Tally_Sheet
                                join sd in db.Ship_Departure on t.ship_departure_id equals sd.ship_departure_id
                                join s in db.Ships on sd.shipp_id equals s.ship_id
                                join e in db.Employees on t.employee_id equals e.employee_id
                                where t.tally_sheet_id == id
                                select new { t.tally_sheet_id, s.ship_name, sd.starting_date, sd.ship_departure_code, e.first_name, e.middle_name, e.last_name };

            foreach (var item in cargo_details)
            {
                cargo.SetCellValue(2, 2, item.ship_name);
                cargo.SetCellValue(3, 2, item.starting_date);
                cargo.SetCellValue(4, 2, item.ship_departure_code);
                cargo.SetCellValue(5, 2, item.first_name + " " + item.middle_name + " " + item.last_name);
            }
            var cargo_goods_details = db.Cargo_Manifest_Export(id).OrderBy(e => e.way_bill_id);

            var iRowCnt = 8;

            foreach (var item in cargo_goods_details)
            {

                cargo.SetCellValue(iRowCnt, 1, item.way_bill_code);
                cargo.SetCellValue(iRowCnt, 2, item.exporter);
                cargo.SetCellValue(iRowCnt, 3, item.goods);
                cargo.SetCellValue(iRowCnt, 4, item.quantity);
                cargo.SetCellValue(iRowCnt, 5, item.unit_of_measure);
                cargo.SetCellValue(iRowCnt, 6, item.is_damaged);
                iRowCnt = iRowCnt + 1;
            }

            FlexCelPdfExport flexpdf = new FlexCelPdfExport();
            flexpdf.Workbook = cargo;
            flexpdf.AllowOverwritingFiles = true;
            flexpdf.Export(copy_file_path.Replace(".xlsx",".pdf"));

            var save_name = "Cargo_Manifest_" + id+".pdf";

            this.DownLoadFile(save_name, copy_file_name);
            this.DeleteFile(copy_file_name);

            return RedirectToAction("Index");
        }

        public void Report_2(IEnumerable<GetReport_Levy_Transactions_Import_Rep2_Result> daily_trans_import, IEnumerable<GetReport_Levy_Transactions_Export_Rep2_Result> daily_trans_Export, string filterType, string exchange_rate, string typ, string copy_file_path, string Orginal_file_path)
        {
            int nocolor = 0;
            int coloronly = 0;

            int leftborder = 0;
            int rightborder = 0;

            int nocolorleft = 0;
            int nocolorright = 0;

            int nocolorbold = 0;
            int colorbold = 0;

            int c = 1;

            //open File
            XlsFile report_2 = new XlsFile(Orginal_file_path);

            //Keeping Impot Sheet Active
            report_2.ActiveSheet = 1;

            //tables for Import & export
            DataTable dtImport = new DataTable();
            // DataTable dtExport = new DataTable();

            //if import data is null , then import will be skipped --using goto skipImport else get the value in dtimport
            try
            {
                if(daily_trans_import!=null)
                dtImport = LinqQueryToDataTable(daily_trans_import.ToList());

                if (dtImport.Rows.Count <= 1)
                    goto skip_Import;

            }
            catch
            { goto skip_Import; }

            //Transpose result for excel
            DataTable dtTranspose1 = GenerateTransposedTable(dtImport);

            //// integer c to handle the cell number in both sheets
            //if (!typ.Equals("pdf"))
            //    c = 1;

            //to set exchange_rate
            report_2.SetCellValue(3, 3 + c, GetDecimal(exchange_rate));

            //to set header
            report_2.SetCellValue(2, 2 + c, filterType);

             nocolor = NoColory(report_2, 7, 2);
             coloronly = ColorOnly(report_2, 7, 2);

             leftborder = ColorAndLeftBorder(report_2, 7, 2);
             rightborder = ColorAndRightBorder(report_2, 7, 2);

             nocolorleft = NoColorAndLeftBorder(report_2, 7, 2);
             nocolorright = NoColorAndRightBorder(report_2, 7, 2);

             nocolorbold = NoColorBold(report_2, 7, 2);
             colorbold = ColorBold(report_2, 7, 2);

            //to print all data
            int i = 7;

            int color1 = 0;
            int color2 = 0;
            int color3 = 0;
            int color4 = 0;

            int datarow = 7;
            foreach (DataRow dr in dtTranspose1.Rows)
            {

                if (dr[1].ToString().Length > 0)
                {
                    if (i % 2 != 0)
                    {
                        color1 = nocolorleft;
                        color2 = nocolor;
                        color3 = nocolorright;
                        color4 = nocolorbold;

                    }
                    else
                    {
                        color1 = leftborder;
                        color2 = coloronly;
                        color3 = rightborder;
                        color4 = colorbold;

                    }

                    report_2.SetCellValue(i, 1 + c, dr[1].ToString().TrimEnd(']').TrimStart('['), color1);
                    report_2.SetCellValue(i, 2 + c, GetDecimal(dr[2].ToString()), color2);
                    report_2.SetCellValue(i, 3 + c, GetDecimal(dr[3].ToString()), color2);
                    report_2.SetCellValue(i, 4 + c, GetDecimal(dr[4].ToString()), color4);
                    report_2.SetCellValue(i, 5 + c, GetDecimal(dr[5].ToString()), color3);
//.........这里部分代码省略.........

        public void Report_1(DateTime Report_date, string typ)
        {
            //default last date
            string last_date = "9999/12/31";

            //current exchanfge rate
            var exchangerate = (from d in db.Exchange_Rate where d.end_date == last_date && d.from_currency_id == 100 && d.to_currency_id == 106 select d.exchange_rate1).FirstOrDefault();//db.GetExchangeRate(dtExchangeRateDate).FirstOrDefault();

            //file path W:r:to type(pdf/excel)
            string Orginal_file_path = string.Empty;

            if (typ.Equals("pdf"))
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_1_A.xlsx");

            else
                Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Report_1_B.xlsx");

            //temp file name
            string copy_file_name = DateTime.Now.Ticks + ".xlsx";
            //temp file path
            string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name);

            //open file
            XlsFile report_1 = new XlsFile(Orginal_file_path);

            //sheet always 1
            report_1.ActiveSheet = 1;

            //get import data
            var daily_trans_import = db.Report_1_Import(Report_date);

            //get import data count
            var daily_trans_import_count = db.Report_1_Import(Report_date).Count();

            //skip if no data
            if (daily_trans_import_count <= 0)
                goto skipImport;

            //print
            int i = 6;

            report_1.SetCellValue(3, 4, exchangerate);

            report_1.SetCellValue(2, 2, Report_date.ToString("dd/MM/yyyy"));

            foreach (var item in daily_trans_import)
            {

                report_1.SetCellValue(i, 2, item.BY_CASH_CHEQUE);
                report_1.SetCellValue(i, 3, item.USD);
                report_1.SetCellValue(i, 4, item.SOS);
                report_1.SetCellValue(i, 5, item.TOTAL_USD);
                report_1.SetCellValue(i, 6, item.TOTAL_SOS);
                i++;
            }

            //skiped import
            skipImport:

            //get export dtaa
            var daily_trans_export = db.Report_1_Export(Report_date);

            //get export count
            var daily_trans_export_count = db.Report_1_Export(Report_date).Count();

            //skip export if no data
            if (daily_trans_export_count <= 0)
                goto skipExport;

            //print export
            i = 14;

            foreach (var item in daily_trans_export)
            {

                report_1.SetCellValue(i, 2, item.BY_CASH_CHEQUE);
                report_1.SetCellValue(i, 3, item.USD);
                report_1.SetCellValue(i, 4, item.SOS);
                report_1.SetCellValue(i, 5, item.TOTAL_USD);
                report_1.SetCellValue(i, 6, item.TOTAL_SOS);
                i++;
            }

            //skipped export
            skipExport:

            //if no import and export return alert msg
            if (daily_trans_import_count <= 0 && daily_trans_export_count <= 0)
            {
                TempData["Warning"] = "No Data To Export";
                return;
            }
            else
            {

                //else print

                report_1.PrintToFit = true;
                report_1.PrintPaperSize = TPaperSize.A4;

//.........这里部分代码省略.........

//.........这里部分代码省略.........
        //        report_4.Save(copy_file_path);
        //        if (typ.Equals("pdf"))
        //            GeneratePDF(report_4, "Report_4");
        //        else
        //        {
        //            Response.AppendHeader("Content-Disposition", "attachment; filename=Report_4.xlsx");
        //            Response.TransmitFile(copy_file_path);
        //            Response.End();
        //        }
        //    }
        //}
        public void Report_4(IEnumerable<Report_4_Import_Result> daily_trans_import, IEnumerable<Report_4_Export_Result> daily_trans_Export, string filterType, string exchange_rate, string typ, string copy_file_path, string Orginal_file_path)
        {
            int c = 1;

            //open File
            XlsFile report_4 = new XlsFile(Orginal_file_path);

            //sheet always 1
            report_4.ActiveSheet = 1;

            int tformat_head = report_4.AddFormat(report_4.GetCellVisibleFormatDef(10, 3));
            int tformat_col1 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 3));
            int tformat_col2 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 4));
            int tformat_col3 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 5));
            int tformat_col4 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 6));
            int tformat_col5 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 7));
            int tformat_col6 = report_4.AddFormat(report_4.GetCellVisibleFormatDef(12, 8));

            string Header = string.Empty;

            Header = "Daily Bolleto Dogonale Report     :" + filterType;

            report_4.SetCellValue(10, 3, Header, tformat_head);

            DataTable dtImport = new DataTable();

            //get import data
               // var daily_trans_import = db.Report_4_Import(Report_date);

            //get import data count
            try
            {
                if (daily_trans_import != null)
                    dtImport = LinqQueryToDataTable(daily_trans_import.ToList());

                if (dtImport.Rows.Count <= 1)
                    goto skip_Import;

            }
            catch
            { goto skip_Import; }

               int i = 12;
            int s = 1;
            c = 2;
            int datarow = 12;
            foreach (DataRow dr in dtImport.Rows)
            {

                if (dr[1].ToString().Length > 0)
                {

                    report_4.SetCellValue(i, 1 + c, s, tformat_col1);
                    report_4.SetCellValue(i, 2 + c, dr[3].ToString(), tformat_col2);
                    report_4.SetCellValue(i, 3 + c, dr[4].ToString(), tformat_col3);

//.........这里部分代码省略.........
            #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;

        /// <summary>
        /// 
        /// </summary>
        internal void Export()
        {
            string file = Path.GetTempFileName("xls");
            XlsFile xls = new XlsFile();
            xls.NewFile();
            string title = string.Format("鑫丰热力供热处职工人员考勤表 {0}-{1}", _month.Year, _month.Month);
            xls.SetCellValue(1, 1, title);

            int r = 3;
            int c = 1;
            foreach (DataColumn col in _tbl.Columns)
            {
                xls.SetCellValue(r, c, col.ColumnName);
                c++;
            }

            foreach (DataRow row in _tbl.Rows)
            {
                r++;
                for (int i = 0; i < _tbl.Columns.Count; i++)
                {
                    xls.SetCellValue(r, i + 1, row[i]);
                }
            }
            xls.Save(file);

            Open(file);
        }

//.........这里部分代码省略.........
            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;

//.........这里部分代码省略.........
                        fmt.Borders.Right.Color = TExcelColor.Automatic;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Bottom.Color = TExcelColor.Automatic;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted;
                        fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Top.Color = TExcelColor.Automatic;
                        fmt.HAlignment = THFlxAlignment.center;
                        fmt.VAlignment = TVFlxAlignment.center;
                        fmt.Format = "_(* #,###_);_(* \\(#,###\\);_(* \"\"??_);_(@_)";
                        break;
                    default:
                        fmt = xls.GetCellVisibleFormatDef(TuHang, 2);
                        fmt.Font.Name = "Times New Roman";
                        fmt.Font.Size20 = 160;
                        fmt.Font.Family = 1;
                        fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Left.Color = TExcelColor.Automatic;
                        fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Right.Color = TExcelColor.Automatic;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Bottom.Color = TExcelColor.Automatic;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted;
                        fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Top.Color = TExcelColor.Automatic;
                        fmt.HAlignment = THFlxAlignment.right;
                        fmt.VAlignment = TVFlxAlignment.center;
                        break;
                }
                for (int h = 0; h < dt.Rows.Count; h++)
                {
                    GiaTriO = null;
                    xls.SetCellFormat(h + TuHang, _TuCot, xls.AddFormat(fmt));
                    if (c + TuCotCua_DT < DenCotCua_DT)
                        xls.SetCellValue(h + TuHang, _TuCot, dt.Rows[h][c + TuCotCua_DT]);
                }
                _TuCot++;
            }
            #endregion

            #region Fill dữ liệu những cột tĩnh
            _TuCot = TuCot;
            String KyTu1, KyTu2, strSum;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int c = 0; c < arrCot_Excel.Length; c++)
                {
                    fmt = xls.GetCellVisibleFormatDef(TuHang + i, Convert.ToInt32(arrCot_Excel[c]));
                    if (c >= arrCot_Excel.Length - 3)
                    {
                        fmt.Font.Name = "Times New Roman";
                        fmt.Font.Size20 = 160;
                        fmt.Font.Family = 1;
                        fmt.Borders.Left.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Left.Color = TExcelColor.Automatic;
                        fmt.Borders.Right.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Right.Color = TExcelColor.Automatic;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Bottom.Style = TFlxBorderStyle.Dotted;
                        fmt.Borders.Bottom.Color = TExcelColor.Automatic;
                        fmt.Borders.Top.Style = TFlxBorderStyle.Thin;
                        fmt.Borders.Top.Color = TExcelColor.Automatic;
                        fmt.HAlignment = THFlxAlignment.left;
                        fmt.VAlignment = TVFlxAlignment.center;
                        fmt.Format = "_(* #,###_);_(* \\(#,###\\);_(* \"\"??_);_(@_)";

                    }

//.........这里部分代码省略.........
            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));

        /// <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;
                                        }
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                        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
                    {

//.........这里部分代码省略.........

            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;

        //
        // GET: /Home/ExportData_ToExcel/W0009
        public ActionResult E_ExportData_ToExcel(int way_bill_id)
        {
            if (Session["login_status"] != null)
            {

                //temp file name
                string copy_file_name = DateTime.Now.Ticks + ".xlsx";
                //template file name
                string Orginal_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Report_Templates"), "Bolleto_Dogonale_Export.xlsx");
                //temp file path
                string copy_file_path = Path.Combine(System.Web.HttpContext.Current.Server.MapPath("~/Images"), copy_file_name);

                XlsFile bolleto = new XlsFile(Orginal_file_path);

                bolleto.ActiveSheet = 1;

                var bolleto_code = (from i in db.Exports
                                    where i.e_way_bill_id == way_bill_id
                                    select i.e_bollete_dogonale_code).First();
                var j = 0;

                var bollete_dogonale = (from e in db.Exports
                                        join w in db.E_Way_Bill on e.e_way_bill_id equals w.e_way_bill_id
                                        join sd in db.Ship_Departure on e.ship_departure_id equals sd.ship_departure_id
                                        join cpc in db.E_Calculated_Payment_Config on w.e_way_bill_id equals cpc.way_bill_id
                                        where e.e_way_bill_id == way_bill_id
                                        select new Bolleto_DogonaleModel { way_bill_code = w.e_way_bill_code, import_code = e.export_code, ship_arrival_code = sd.ship_departure_code, bolleto_dogonale_code = e.e_bollete_dogonale_code, date = cpc.calculated_date });

                foreach (var item in bollete_dogonale)
                {

                    bolleto.SetCellValue(2, 3, item.bolleto_dogonale_code);
                    bolleto.SetCellValue(3, 3, item.way_bill_code);
                    bolleto.SetCellValue(4, 3, item.import_code);
                    bolleto.SetCellValue(5, 3, item.ship_arrival_code);
                    bolleto.SetCellValue(6, 3, item.date.ToString("yyyy-MM-dd"));
                    //bolleto.SetCellFormat(1, 1, 8, 7, 1);

                }
                var grand_total = db.E_Get_Grand_Total(way_bill_id, 100);
                string grand_total_value = "";
                foreach (var v in grand_total)
                {
                    grand_total_value = v;
                }
                db.E_Display_Tax_Details(way_bill_id, 100);
                var display1 = from d1 in db.E_TempDisplay1
                               where d1.way_bill_id == way_bill_id
                               select d1;
                var display2 = (from d2 in db.E_TempDisplay2
                                where d2.way_bill_id == way_bill_id
                                select d2).ToList();

                TFlxFormat tformatTitle = bolleto.GetCellVisibleFormatDef(9, 1);
                int titleFormat = bolleto.AddFormat(tformatTitle);

                TFlxFont tfont = new TFlxFont();
                tfont.Style = TFlxFontStyles.Bold;

                TFlxFormat tformatright = bolleto.GetCellVisibleFormatDef(10, 6);
                tformatright.Font = tfont;
                int Formatright = bolleto.AddFormat(tformatright);

                int iRowCnt = 9;

                foreach (var item in display1)
                {

                    // SHOW COLUMNS ON THE TOP.

                    bolleto.SetCellValue(iRowCnt, 1, "Goods", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 2, "Unit Price", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 3, "Calculated Tariff", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 4, "Total Quantity", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 5, "Unit Of Measure", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 6, "Base Taxation", titleFormat);
                    bolleto.SetCellValue(iRowCnt, 7, "Total", titleFormat);

                    //bolleto.SetCellFormat(iRowCnt, 1, iRowCnt, 7, 1);
                    var x = "A" + iRowCnt;
                    var y = "G" + iRowCnt;

                    //xlWorkSheetToExport.Range[x, y].Font.Size = 11;
                    //xlWorkSheetToExport.Range[x, y].Font.Bold = true;

                    iRowCnt = iRowCnt + 1;
                    bolleto.SetCellValue(iRowCnt, 1, item.goods_name);
                    bolleto.SetCellValue(iRowCnt, 2, item.toal_Quantity);
                    bolleto.SetCellValue(iRowCnt, 3, item.Unit_Code);
                    bolleto.SetCellValue(iRowCnt, 4, item.Unit_Price);
                    bolleto.SetCellValue(iRowCnt, 5, item.calculated_Tariff);
                    bolleto.SetCellValue(iRowCnt, 6, item.Base_Taxation);
                    bolleto.SetCellValue(iRowCnt, 7, item.Total);
                    iRowCnt = iRowCnt + 2;
                    x = "A" + iRowCnt;
                    y = "G" + iRowCnt;
                    //xlWorkSheetToExport.Range[x, y].Font.Size = 11;
                    //xlWorkSheetToExport.Range[x, y].Font.Bold = true;
//.........这里部分代码省略.........