Example #1
0
 // Token: 0x06000173 RID: 371 RVA: 0x00008170 File Offset: 0x00007170
 public void AddMergeArea(MergeArea mergeArea)
 {
     foreach (MergeArea mergeArea2 in this._mergeAreas)
     {
         bool flag  = false;
         bool flag2 = false;
         if (mergeArea.ColMin < mergeArea2.ColMin && mergeArea2.ColMax < mergeArea.ColMax)
         {
             flag = true;
         }
         else if ((mergeArea2.ColMin <= mergeArea.ColMin && mergeArea2.ColMax >= mergeArea.ColMin) || (mergeArea2.ColMin <= mergeArea.ColMax && mergeArea2.ColMax >= mergeArea.ColMax))
         {
             flag = true;
         }
         if (mergeArea.RowMin < mergeArea2.RowMin && mergeArea2.RowMax < mergeArea.RowMax)
         {
             flag2 = true;
         }
         else if ((mergeArea2.RowMin <= mergeArea.RowMin && mergeArea2.RowMax >= mergeArea.RowMin) || (mergeArea2.RowMin <= mergeArea.RowMax && mergeArea2.RowMax >= mergeArea.RowMax))
         {
             flag2 = true;
         }
         if (flag && flag2)
         {
             throw new ArgumentException("overlaps with existing MergeArea", "mergeArea");
         }
     }
     this._mergeAreas.Add(mergeArea);
 }
Example #2
0
        //TODO: Optionally provide overload with bool parameter to decide whether to throw
        //exception instead of losing values.
        /// <summary>
        /// Adds a MergeArea to this Worksheet.  The mergeArea is verified not to
        /// overlap with any previously defined area.  NOTE Values and formatting
        /// in all cells other than the first in mergeArea (scanning left to right,
        /// top to bottom) will be lost.
        /// </summary>
        /// <param name="mergeArea">The MergeArea to add to this Worksheet.</param>
        public void AddMergeArea(MergeArea mergeArea)
        {
            foreach (MergeArea existingArea in _mergeAreas)
            {
                bool colsOverlap = false;
                bool rowsOverlap = false;

                //if they overlap, either mergeArea will surround existingArea,
                if (mergeArea.ColMin < existingArea.ColMin && existingArea.ColMax < mergeArea.ColMax)
                {
                    colsOverlap = true;
                }
                //or existingArea will contain >= 1 of mergeArea's Min and Max indices
                else if ((existingArea.ColMin <= mergeArea.ColMin && existingArea.ColMax >= mergeArea.ColMin) ||
                         (existingArea.ColMin <= mergeArea.ColMax && existingArea.ColMax >= mergeArea.ColMax))
                {
                    colsOverlap = true;
                }

                if (mergeArea.RowMin < existingArea.RowMin && existingArea.RowMax < mergeArea.RowMax)
                {
                    rowsOverlap = true;
                }
                else if ((existingArea.RowMin <= mergeArea.RowMin && existingArea.RowMax >= mergeArea.RowMin) ||
                         (existingArea.RowMin <= mergeArea.RowMax && existingArea.RowMax >= mergeArea.RowMax))
                {
                    rowsOverlap = true;
                }

                if (colsOverlap && rowsOverlap)
                {
                    throw new ArgumentException("overlaps with existing MergeArea", "mergeArea");
                }
            }

            //TODO: Add ref to this mergeArea to all rows in its range, and add checking on Cell
            //addition methods to validate they are not being added within the mergedarea, other
            //than as the top-left cell.

            _mergeAreas.Add(mergeArea);
        }
Example #3
0
 // Token: 0x060002C4 RID: 708 RVA: 0x0000DB0C File Offset: 0x0000CB0C
 public MergeArea(int rowMin, int rowMax, int colMin, int colMax)
 {
     this = new MergeArea((ushort)rowMin, (ushort)rowMax, (ushort)colMin, (ushort)colMax);
     if (rowMin < 1)
     {
         throw new ArgumentOutOfRangeException("rowMin", "must be >= 1");
     }
     if (rowMin > 65535)
     {
         throw new ArgumentOutOfRangeException("rowMin", "must be <= " + ushort.MaxValue);
     }
     if (rowMax < rowMin)
     {
         throw new ArgumentOutOfRangeException("rowMax", "must be >= rowMin (" + rowMin + ")");
     }
     if (rowMax > 65535)
     {
         throw new ArgumentOutOfRangeException("rowMax", "must be <=" + ushort.MaxValue);
     }
     if (colMin < 1)
     {
         throw new ArgumentOutOfRangeException("colMin", "must be >= 1");
     }
     if (colMin > 255)
     {
         throw new ArgumentOutOfRangeException("colMin", "must be <= " + 255);
     }
     if (colMax < colMin)
     {
         throw new ArgumentOutOfRangeException("colMax", "must be >= colMin (" + colMin + ")");
     }
     if (colMax > 255)
     {
         throw new ArgumentOutOfRangeException("colMax", "must be <= " + 255);
     }
     Util.ValidateUShort(rowMin, "rowMin");
     Util.ValidateUShort(rowMax, "rowMax");
     Util.ValidateUShort(colMin, "colMin");
     Util.ValidateUShort(colMax, "colMax");
 }
Example #4
0
        // Token: 0x06000289 RID: 649 RVA: 0x0000BFF0 File Offset: 0x0000AFF0
        public void Merge(int rowMin, int rowMax, int colMin, int colMax)
        {
            MergeArea mergeArea = new MergeArea(rowMin, rowMax, colMin, colMax);

            this._worksheet.AddMergeArea(mergeArea);
        }
Example #5
0
 /// <summary>
 /// Merges cells within the defined range of Rows and Columns.  The ranges are
 /// verified not to overlap with any previously defined Merge areas.  NOTE 
 /// Values and formatting in all cells other than the first in the range 
 /// (scanning left to right, top to bottom) will be lost.
 /// </summary>
 /// <param name="rowMin">The first index in the range of Rows to merge.</param>
 /// <param name="rowMax">The last index in the range of Rows to merge.</param>
 /// <param name="colMin">The first index in the range of Columns to merge.</param>
 /// <param name="colMax">The last index in the range of Columns to merge.</param>
 public void Merge(int rowMin, int rowMax, int colMin, int colMax)
 {
     MergeArea mergeArea = new MergeArea(rowMin, rowMax, colMin, colMax);
     _worksheet.AddMergeArea(mergeArea);
 }
Example #6
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            XlsDocument xls = new XlsDocument();//新建一个xls文档
            xls.FileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            string MainID = Request.QueryString["ID"].ToString();
            cs.DBCommand dbc = new cs.DBCommand();
            string sqlmain = "select * from ETravel where ID=" + MainID;
            DataTable dtMain = dbc.GetData("eReimbursement", sqlmain);
            if (dtMain.Rows.Count != 1)
            {
                ErrorHandle("Data Error.");
                return;
            }
            string sqlTocity = "select (ROW_NUMBER() OVER (ORDER BY Tocity)-1) % 4 AS SubRow,(ROW_NUMBER() OVER (ORDER BY Tocity) - 1) / 4 AS Row,Tocity from (select distinct Tocity from ETraveleDetail where [No]='" + MainID + "') t1";
            DataTable dtTocity = dbc.GetData("eReimbursement", sqlTocity);
            int pagecount = 0;
            for (int i = 0; i < dtTocity.Rows.Count; i++)
            {
                if (Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString())>pagecount)
                {
                    pagecount = Convert.ToInt32(dtTocity.Rows[i]["Row"].ToString());
                }
            }
            for (int j = 0; j < pagecount + 1; j++)
            {
                Worksheet sheet;
                sheet = xls.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyyMMddHHmmss" + j.ToString()));
                //首行空白行
                XF titleXF = xls.NewXF(); // 为xls生成一个XF实例,XF是单元格格式对象
                titleXF.HorizontalAlignment = HorizontalAlignments.Left; // 设定文字居中
                titleXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
                titleXF.UseBorder = false; // 使用边框
                titleXF.Font.Height = 12 * 20; // 字大小(字体大小是以 1/20 point 为单位的)
                //第二行
                XF titleXF1 = xls.NewXF(); // 为xls生成一个XF实例,XF是单元格格式对象
                titleXF1.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
                titleXF1.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
                titleXF1.UseBorder = false; // 使用边框
                titleXF1.Font.Underline = UnderlineTypes.Single;
                titleXF1.Font.Height = 18 * 20;

                XF columnTitleXF41 = xls.NewXF();

                XF columnTitleXF42 = xls.NewXF();
                columnTitleXF42.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF42.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF42.Font.Height = 12 * 20;

                XF columnTitleXF43 = xls.NewXF();
                columnTitleXF43.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF43.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF43.Font.Height = 12 * 20;
                columnTitleXF43.UseBorder = true;
                columnTitleXF43.LeftLineStyle = 2;
                columnTitleXF43.TopLineStyle = 2;
                columnTitleXF43.RightLineStyle = 2;
                columnTitleXF43.BottomLineStyle = 2;

                XF columnTitleXF44 = xls.NewXF();
                columnTitleXF44.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF44.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF44.Font.Height = 12 * 20;
                columnTitleXF44.UseBorder = true;
                columnTitleXF44.TopLineStyle = 2;
                columnTitleXF44.BottomLineStyle = 2;

                XF columnTitleXF46 = xls.NewXF();
                columnTitleXF46.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF46.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF46.Font.Height = 12 * 20;
                columnTitleXF46.UseBorder = true;
                columnTitleXF46.TopLineStyle = 2;
                columnTitleXF46.BottomLineStyle = 2;
                columnTitleXF46.RightLineStyle = 2;

                XF columnTitleXF412 = xls.NewXF();
                columnTitleXF412.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF412.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF412.Font.Height = 12 * 20;
                columnTitleXF412.UseBorder = true;
                columnTitleXF412.TopLineStyle = 2;
                columnTitleXF412.BottomLineStyle = 2;
                columnTitleXF412.RightLineStyle = 2;

                XF columnTitleXF62 = xls.NewXF();
                columnTitleXF62.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF62.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF62.Font.Height = 10 * 20;
                columnTitleXF62.Font.Bold = true;
                columnTitleXF62.UseBorder = true;
                columnTitleXF62.LeftLineStyle = 2;
                columnTitleXF62.TopLineStyle = 2;
                columnTitleXF62.RightLineStyle = 2;
                columnTitleXF62.BottomLineStyle = 2;

                XF columnTitleXF63 = xls.NewXF();
                columnTitleXF63.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF63.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF63.Font.Height = 10 * 20;
                columnTitleXF63.UseBorder = true;
                columnTitleXF63.TopLineStyle = 2;
                columnTitleXF63.LeftLineStyle = 1;

                XF columnTitleXF64 = xls.NewXF();
                columnTitleXF64.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF64.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF64.Font.Height = 10 * 20;
                columnTitleXF64.UseBorder = true;
                columnTitleXF64.TopLineStyle = 2;
                columnTitleXF64.RightLineStyle = 1;

                XF columnTitleXF66 = xls.NewXF();
                columnTitleXF66.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF66.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF66.Font.Height = 10 * 20;
                columnTitleXF66.UseBorder = true;
                columnTitleXF66.TopLineStyle = 2;

                XF columnTitleXF67 = xls.NewXF();
                columnTitleXF67.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF67.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF67.Font.Height = 10 * 20;
                columnTitleXF67.Font.Bold = true;
                columnTitleXF67.UseBorder = true;
                columnTitleXF67.LeftLineStyle = 2;
                columnTitleXF67.TopLineStyle = 2;
                columnTitleXF67.BottomLineStyle = 1;

                XF columnTitleXF68 = xls.NewXF();
                columnTitleXF68.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF68.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF68.Font.Height = 10 * 20;
                columnTitleXF68.UseBorder = true;
                columnTitleXF68.TopLineStyle = 2;
                columnTitleXF68.RightLineStyle = 2;

                XF columnTitleXF72 = xls.NewXF();
                columnTitleXF72.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF72.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF72.Font.Height = 10 * 20;
                columnTitleXF72.Font.Bold = true;
                columnTitleXF72.UseBorder = true;
                columnTitleXF72.LeftLineStyle = 2;
                columnTitleXF72.RightLineStyle = 2;
                columnTitleXF72.BottomLineStyle = 2;

                XF columnTitleXF73 = xls.NewXF();
                columnTitleXF73.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF73.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF73.Font.Height = 10 * 20;
                columnTitleXF73.UseBorder = true;
                columnTitleXF73.LeftLineStyle = 1;
                columnTitleXF73.TopLineStyle = 1;
                columnTitleXF73.RightLineStyle = 1;
                columnTitleXF73.BottomLineStyle = 2;

                XF columnTitleXF77 = xls.NewXF();
                columnTitleXF77.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF77.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF77.Font.Height = 10 * 20;
                columnTitleXF77.Font.Bold = true;
                columnTitleXF77.UseBorder = true;
                columnTitleXF77.LeftLineStyle = 2;
                columnTitleXF77.TopLineStyle = 1;
                columnTitleXF77.RightLineStyle = 1;
                columnTitleXF77.BottomLineStyle = 2;

                XF columnTitleXF78 = xls.NewXF();
                columnTitleXF78.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF78.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF78.Font.Height = 10 * 20;
                columnTitleXF78.Font.Bold = true;
                columnTitleXF78.UseBorder = true;
                columnTitleXF78.TopLineStyle = 1;
                columnTitleXF78.RightLineStyle = 2;
                columnTitleXF78.BottomLineStyle = 2;

                XF columnTitleXF82 = xls.NewXF();
                columnTitleXF82.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF82.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF82.Font.Height = 10 * 20;
                columnTitleXF82.UseBorder = true;
                columnTitleXF82.LeftLineStyle = 2;
                columnTitleXF82.TopLineStyle = 2;
                columnTitleXF82.RightLineStyle = 2;
                columnTitleXF82.BottomLineStyle = 1;

                XF columnTitleXF83 = xls.NewXF();
                columnTitleXF83.HorizontalAlignment = HorizontalAlignments.Right;
                columnTitleXF83.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF83.Font.Height = 10 * 20;
                columnTitleXF83.UseBorder = true;
                columnTitleXF83.LeftLineStyle = 1;
                columnTitleXF83.TopLineStyle = 1;
                columnTitleXF83.RightLineStyle = 1;
                columnTitleXF83.BottomLineStyle = 1;

                XF columnTitleXF812 = xls.NewXF();
                columnTitleXF812.HorizontalAlignment = HorizontalAlignments.Right;
                columnTitleXF812.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF812.Font.Height = 10 * 20;
                columnTitleXF812.UseBorder = true;
                columnTitleXF812.LeftLineStyle = 1;
                columnTitleXF812.TopLineStyle = 1;
                columnTitleXF812.RightLineStyle = 2;
                columnTitleXF812.BottomLineStyle = 1;

                XF columnTitleXF92 = xls.NewXF();
                columnTitleXF92.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF92.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF92.Font.Height = 10 * 20;
                columnTitleXF92.UseBorder = true;
                columnTitleXF92.LeftLineStyle = 2;
                columnTitleXF92.TopLineStyle = 1;
                columnTitleXF92.RightLineStyle = 2;
                columnTitleXF92.BottomLineStyle = 1;

                XF columnTitleXF93 = xls.NewXF();
                columnTitleXF93.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF93.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF93.Font.Height = 10 * 20;
                columnTitleXF93.UseBorder = true;
                columnTitleXF93.LeftLineStyle = 1;
                columnTitleXF93.TopLineStyle = 1;

                XF columnTitleXF192 = xls.NewXF();
                columnTitleXF192.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF192.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF192.Font.Height = 10 * 20;
                columnTitleXF192.UseBorder = true;
                columnTitleXF192.LeftLineStyle = 2;
                columnTitleXF192.TopLineStyle = 1;
                columnTitleXF192.RightLineStyle = 2;
                columnTitleXF192.BottomLineStyle = 2;

                XF columnTitleXF202 = xls.NewXF();
                columnTitleXF202.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF202.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF202.Font.Height = 10 * 20;
                columnTitleXF202.UseBorder = true;
                columnTitleXF202.LeftLineStyle = 2;
                columnTitleXF202.TopLineStyle = 2;

                XF columnTitleXF208 = xls.NewXF();
                columnTitleXF208.HorizontalAlignment = HorizontalAlignments.Centered;
                columnTitleXF208.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF208.Font.Height = 10 * 20;
                columnTitleXF208.UseBorder = true;
                columnTitleXF208.LeftLineStyle = 1;

                XF columnTitleXF215 = xls.NewXF();
                columnTitleXF215.HorizontalAlignment = HorizontalAlignments.Left;
                columnTitleXF215.VerticalAlignment = VerticalAlignments.Centered;
                columnTitleXF215.Font.Height = 10 * 20;
                columnTitleXF215.UseBorder = true;
                columnTitleXF215.LeftLineStyle = 1;

                // 列标题行
                ColumnInfo col1 = new ColumnInfo(xls, sheet); // 列对象
                col1.ColumnIndexStart = 0; // 起始列为第1列,索引从0开始
                col1.ColumnIndexEnd = 0; // 终止列为第1列,索引从0开始
                col1.Width = 256; // 列的宽度计量单位为 1/256 字符宽
                sheet.AddColumnInfo(col1); // 把格式附加到sheet页上

                ColumnInfo col2 = new ColumnInfo(xls, sheet);
                col2 = new ColumnInfo(xls, sheet); // 列对象
                col2.ColumnIndexStart = 1;
                col2.ColumnIndexEnd = 1;
                col2.Width = 7680;
                sheet.AddColumnInfo(col2);

                ColumnInfo col3 = new ColumnInfo(xls, sheet);
                col3 = new ColumnInfo(xls, sheet); // 列对象
                col3.ColumnIndexStart = 2;
                col3.ColumnIndexEnd = 9;
                col3.Width = 1800;
                sheet.AddColumnInfo(col3);

                ColumnInfo col4 = new ColumnInfo(xls, sheet);
                col4 = new ColumnInfo(xls, sheet); // 列对象
                col4.ColumnIndexStart = 10;
                col4.ColumnIndexEnd = 11;
                col4.Width = 3900;
                sheet.AddColumnInfo(col4);

                //行
                RowInfo rol1 = new RowInfo();
                rol1.RowHeight = 10 * 20;
                rol1.RowIndexStart = 1;
                rol1.RowIndexEnd = 1;
                sheet.AddRowInfo(rol1);

                rol1 = new RowInfo();
                rol1.RowHeight = 20 * 20;
                rol1.RowIndexStart = 2;
                rol1.RowIndexEnd = 2;
                sheet.AddRowInfo(rol1);

                rol1 = new RowInfo();
                rol1.RowHeight = 5 * 20;
                rol1.RowIndexStart = 3;
                rol1.RowIndexEnd = 3;
                sheet.AddRowInfo(rol1);

                rol1 = new RowInfo();
                rol1.RowHeight = 5 * 20;
                rol1.RowIndexStart = 5;
                rol1.RowIndexEnd = 5;
                sheet.AddRowInfo(rol1);

                rol1 = new RowInfo();
                rol1.RowHeight = 18 * 20;
                rol1.RowIndexStart = 6;
                rol1.RowIndexEnd = 23;
                sheet.AddRowInfo(rol1);

                // 数据单元格样式
                XF dataXF = xls.NewXF(); // 为xls生成一个XF实例,XF是单元格格式对象
                dataXF.HorizontalAlignment = HorizontalAlignments.Centered; // 设定文字居中
                dataXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中
                dataXF.UseBorder = true; // 使用边框
                dataXF.LeftLineStyle = 1; // 左边框样式
                dataXF.LeftLineColor = Colors.Black; // 左边框颜色
                dataXF.BottomLineStyle = 1;  // 下边框样式
                dataXF.BottomLineColor = Colors.Black;  // 下边框颜色
                dataXF.Font.FontName = "宋体";
                dataXF.Font.Height = 9 * 20; // 设定字大小(字体大小是以 1/20 point 为单位的)
                dataXF.UseProtection = false; // 默认的就是受保护的,导出后需要启用编辑才可修改
                dataXF.TextWrapRight = true; // 自动换行

                // 合并单元格
                MergeArea titleArea = new MergeArea(1, 1, 1, 12);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(2, 2, 1, 12);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(3, 3, 1, 12);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(4, 4, 3, 6);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(4, 4, 7, 10);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(4, 4, 11, 12);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 6, 11, 12);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 6, 3, 4);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 6, 5, 6);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 6, 7, 8);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 6, 9, 10);
                sheet.AddMergeArea(titleArea);
                titleArea = new MergeArea(6, 7, 2, 2);
                sheet.AddMergeArea(titleArea);
                // 开始填充数据到单元格
                org.in2bits.MyXls.Cells cells = sheet.Cells;
                cells.Add(1, 1, "", titleXF);
                cells.Add(2, 1, "Travel Expense Report", titleXF1);
                cells.Add(3, 1, "", titleXF);
                cells.Add(4, 1, "", columnTitleXF41);
                cells.Add(4, 2, "Applicant:", columnTitleXF42);
                cells.Add(4, 3, dtMain.Rows[0]["Person"].ToString(), columnTitleXF43);
                cells.Add(4, 4, "", columnTitleXF44);
                cells.Add(4, 5, "", columnTitleXF44);
                cells.Add(4, 6, "", columnTitleXF46);

                cells.Add(4, 7, "Travel Period:", columnTitleXF42);
                string bb = "";
                if (dtMain.Rows[0]["Bdate"].ToString()!="")
                {
                    bb += Convert.ToDateTime(dtMain.Rows[0]["Bdate"].ToString()).ToString("yyyy/MM/dd");
                }
                if (dtMain.Rows[0]["Edate"].ToString() != "")
                {
                    bb += " - " + Convert.ToDateTime(dtMain.Rows[0]["Edate"].ToString()).ToString("yyyy/MM/dd");
                }
                cells.Add(4, 11, bb, columnTitleXF43);
                cells.Add(4, 12, "", columnTitleXF412);
                cells.Add(5, 1, "", columnTitleXF41);
                cells.Add(6, 1, "", columnTitleXF41);
                cells.Add(6, 2, "Travel Destination", columnTitleXF62);
                cells.Add(6, 3, "", columnTitleXF63);
                cells.Add(6, 4, "", columnTitleXF64);
                cells.Add(6, 5, "", columnTitleXF63);
                cells.Add(6, 6, "", columnTitleXF64);
                cells.Add(6, 7, "", columnTitleXF63);
                cells.Add(6, 8, "", columnTitleXF64);
                cells.Add(6, 9, "", columnTitleXF63);
                cells.Add(6, 10, "", columnTitleXF64);
                cells.Add(6, 11, "Total Expenses", columnTitleXF67);
                cells.Add(6, 12, "", columnTitleXF68);
                cells.Add(7, 1, "", columnTitleXF41);
                cells.Add(7, 2, "", columnTitleXF72);
                cells.Add(7, 3, "", columnTitleXF73);
                cells.Add(7, 4, "", columnTitleXF73);
                cells.Add(7, 5, "", columnTitleXF73);
                cells.Add(7, 6, "", columnTitleXF73);
                cells.Add(7, 7, "", columnTitleXF73);
                cells.Add(7, 8, "", columnTitleXF73);
                cells.Add(7, 9, "", columnTitleXF73);
                cells.Add(7, 10, "", columnTitleXF73);
                cells.Add(7, 11, "Reimbursement", columnTitleXF77);
                cells.Add(7, 12, "Company Paid", columnTitleXF78);
                cells.Add(8, 1, "", columnTitleXF41);
                cells.Add(8, 2, "1. Air Ticket - Int'l", columnTitleXF82);
                cells.Add(9, 1, "", columnTitleXF41);
                cells.Add(9, 2, "Domestic", columnTitleXF92);
                cells.Add(10, 1, "", columnTitleXF41);
                cells.Add(10, 2, "2. Hotel Bill", columnTitleXF92);
                cells.Add(11, 1, "", columnTitleXF41);
                cells.Add(11, 2, "3. Meals", columnTitleXF92);
                cells.Add(12, 1, "", columnTitleXF41);
                cells.Add(12, 2, "4. Entertainment", columnTitleXF92);
                cells.Add(13, 1, "", columnTitleXF41);
                cells.Add(13, 2, "5. Car Rental/Transportation", columnTitleXF92);
                cells.Add(14, 1, "", columnTitleXF41);
                cells.Add(14, 2, "6. Communication", columnTitleXF92);
                cells.Add(15, 1, "", columnTitleXF41);
                cells.Add(15, 2, "7. Local Trip NTD800(CNY60)/day", columnTitleXF92);
                cells.Add(16, 1, "", columnTitleXF41);
                cells.Add(16, 2, "8. Overseas Trip USD15/day", columnTitleXF92);
                cells.Add(17, 1, "", columnTitleXF41);
                cells.Add(17, 2, "9. Airport Tax/Travel Insurance", columnTitleXF92);
                cells.Add(18, 1, "", columnTitleXF41);
                cells.Add(18, 2, "10. Others", columnTitleXF92);
                cells.Add(19, 1, "", columnTitleXF41);
                cells.Add(19, 2, "Total", columnTitleXF192);
                cells.Add(20, 1, "", columnTitleXF41);
                cells.Add(20, 2, "Remarks:          USD: NTD =", columnTitleXF202);
                cells.Add(20, 12, "Total Trip Expense", columnTitleXF208);
                cells.Add(21, 7, "Less: Advance", columnTitleXF215);
                cells.Add(22, 7, "Bal Due to Company", columnTitleXF215);
                cells.Add(23, 7, "Bal. Due to Employee", columnTitleXF215);
                for (int i = 0; i < 12; i++)
                {
                    for (int ii = 0; ii < 9; ii++)
                    {
                        cells.Add(8 + i, 3 + ii, "", columnTitleXF83);
                    }
                }
                for (int i = 0; i < 12; i++)
                {
                    cells.Add(8 + i, 12, "", columnTitleXF812);
                }
                string sqlDetail = "select * from ETraveleDetail where [No]='" + MainID + "'";
                DataTable dtDetail = dbc.GetData("eReimbursement", sqlDetail);
                decimal row1TC = 0M;
                decimal row1TP = 0M;
                decimal row2TC = 0M;
                decimal row2TP = 0M;
                decimal row3TC = 0M;
                decimal row3TP = 0M;
                decimal row4TC = 0M;
                decimal row4TP = 0M;

                decimal row5TC = 0M;
                decimal row5TP = 0M;
                decimal row6TC = 0M;
                decimal row6TP = 0M;
                decimal row7TC = 0M;
                decimal row7TP = 0M;
                decimal row8TC = 0M;
                decimal row8TP = 0M;
                decimal row9TC = 0M;
                decimal row9TP = 0M;
                decimal row10TC = 0M;
                decimal row10TP = 0M;
                decimal row11TC = 0M;
                decimal row11TP = 0M;
                for (int p = 0; p < dtTocity.Rows.Count; p++)
                {
                    if (Convert.ToInt32(dtTocity.Rows[p]["Row"].ToString()) == j)
                    {
                        decimal row1Pamount = 0M;
                        decimal row1Camount = 0M;
                        decimal row2Pamount = 0M;
                        decimal row2Camount = 0M;
                        decimal row3Pamount = 0M;
                        decimal row3Camount = 0M;
                        decimal row4Pamount = 0M;
                        decimal row4Camount = 0M;

                        decimal row5Pamount = 0M;
                        decimal row5Camount = 0M;
                        decimal row6Pamount = 0M;
                        decimal row6Camount = 0M;
                        decimal row7Pamount = 0M;
                        decimal row7Camount = 0M;
                        decimal row8Pamount = 0M;
                        decimal row8Camount = 0M;
                        decimal row9Pamount = 0M;
                        decimal row9Camount = 0M;
                        decimal row10Pamount = 0M;
                        decimal row10Camount = 0M;
                        decimal row11Pamount = 0M;
                        decimal row11Camount = 0M;


                        decimal column0TC = 0M;
                        decimal column1TP = 0M;
                        
                        for (int i = 0; i < dtDetail.Rows.Count; i++)
                        {
                            if (dtDetail.Rows[i]["Tocity"].ToString() == dtTocity.Rows[p]["Tocity"].ToString())
                            {
                                if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012023")//Air Ticket - Int'l
                                {
                                    row1Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row1Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row1TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012011" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012021")//Hotel Bill
                                {
                                    row3Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row3Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row3TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row3TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010920")//Enter
                                {
                                    row5Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row5Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row5TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row5TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62011901" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011910" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011920" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011930" || dtDetail.Rows[i]["AccountCode"].ToString() == "62011940" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012013")//Car
                                {
                                    row6Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row6TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row6TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62010501" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010510" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010520" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010530" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010540" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010550" || dtDetail.Rows[i]["AccountCode"].ToString() == "62010560")//Commu
                                {
                                    row7Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row6Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row7TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row7TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012012")//Local
                                {
                                    row8Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row8Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row8TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row8TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012022")//Oversea
                                {
                                    row9Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row9Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row9TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row9TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62020630")//Airport
                                {
                                    row10Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row10Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row10TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row10TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else if (dtDetail.Rows[i]["AccountCode"].ToString() == "62012014" || dtDetail.Rows[i]["AccountCode"].ToString() == "62012024")//Others
                                {
                                    row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                else
                                {
                                    row11Pamount += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row11Camount += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());

                                    row11TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                    row11TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                                }
                                column0TC += dtDetail.Rows[i]["Pamount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Pamount"].ToString());
                                column1TP += dtDetail.Rows[i]["Camount"].ToString() == "" ? 0 : Convert.ToDecimal(dtDetail.Rows[i]["Camount"].ToString());
                            }
                        }

                        cells.Add(6, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, dtTocity.Rows[p]["Tocity"].ToString(), columnTitleXF63);
                        cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, "Reim", columnTitleXF73);
                        cells.Add(7, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, "Comp", columnTitleXF73);
                        if (row1Pamount != 0M)
                        {
                            cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row1Pamount, columnTitleXF83);
                        }
                        if (row1Camount != 0M)
                        {
                            cells.Add(8, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row1Camount, columnTitleXF83);
                        }
                        if (row2Pamount != 0M)
                        {
                            cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row2Pamount, columnTitleXF83);
                        }
                        if (row2Camount != 0M)
                        {
                            cells.Add(9, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row2Camount, columnTitleXF83);
                        }
                        if (row3Pamount != 0M)
                        {
                            cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row3Pamount, columnTitleXF83);
                        }
                        if (row3Camount != 0M)
                        {
                            cells.Add(10, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row3Camount, columnTitleXF83);
                        }
                        if (row4Pamount != 0M)
                        {
                            cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row4Pamount, columnTitleXF83);
                        }
                        if (row4Camount != 0M)
                        {
                            cells.Add(11, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row4Camount, columnTitleXF83);
                        }
                        if (row5Pamount != 0M)
                        {
                            cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row5Pamount, columnTitleXF83);
                        }
                        if (row5Camount != 0M)
                        {
                            cells.Add(12, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row5Camount, columnTitleXF83);
                        }
                        if (row6Pamount != 0M)
                        {
                            cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row6Pamount, columnTitleXF83);
                        }
                        if (row6Camount != 0M)
                        {
                            cells.Add(13, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row6Camount, columnTitleXF83);
                        }
                        if (row7Pamount != 0M)
                        {
                            cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row7Pamount, columnTitleXF83);
                        }
                        if (row7Camount != 0M)
                        {
                            cells.Add(14, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row7Camount, columnTitleXF83);
                        }
                        if (row8Pamount != 0M)
                        {
                            cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row8Pamount, columnTitleXF83);
                        }
                        if (row8Camount != 0M)
                        {
                            cells.Add(15, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row8Camount, columnTitleXF83);
                        }
                        if (row9Pamount != 0M)
                        {
                            cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row9Pamount, columnTitleXF83);
                        }
                        if (row9Camount != 0M)
                        {
                            cells.Add(16, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row9Camount, columnTitleXF83);
                        }
                        if (row10Pamount != 0M)
                        {
                            cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row10Pamount, columnTitleXF83);
                        }
                        if (row10Camount != 0M)
                        {
                            cells.Add(17, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row10Camount, columnTitleXF83);
                        }
                        if (row11Pamount != 0M)
                        {
                            cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, row11Pamount, columnTitleXF83);
                        }
                        if (row11Camount != 0M)
                        {
                            cells.Add(18, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, row11Camount, columnTitleXF83);
                        }

                        if (column0TC != 0M)
                        {
                            cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 3, column0TC, columnTitleXF83);
                        }
                        if (column1TP != 0M)
                        {
                            cells.Add(19, Convert.ToInt32(dtTocity.Rows[p]["SubRow"].ToString()) * 2 + 4, column1TP, columnTitleXF83);
                        }
                    }
                }
                if (row1TC != 0M)
                {
                    cells.Add(8, 11, row1TC, columnTitleXF83);
                }
                if (row1TP != 0M)
                {
                    cells.Add(8, 12, row1TP, columnTitleXF812);
                }
                if (row2TC != 0M)
                {
                    cells.Add(9, 11, row2TC, columnTitleXF83);
                }
                if (row2TP != 0M)
                {
                    cells.Add(9, 12, row2TP, columnTitleXF812);
                }
                if (row3TC != 0M)
                {
                    cells.Add(10, 11, row3TC, columnTitleXF83);
                }
                if (row3TP != 0M)
                {
                    cells.Add(10, 12, row3TP, columnTitleXF812);
                }
                if (row4TC != 0M)
                {
                    cells.Add(11, 11, row4TC, columnTitleXF83);
                }
                if (row4TP != 0M)
                {
                    cells.Add(11, 12, row4TP, columnTitleXF812);
                }
                if (row5TC != 0M)
                {
                    cells.Add(12, 11, row5TC, columnTitleXF83);
                }
                if (row5TP != 0M)
                {
                    cells.Add(12, 12, row5TP, columnTitleXF812);
                }
                if (row6TC != 0M)
                {
                    cells.Add(13, 11, row6TC, columnTitleXF83);
                }
                if (row6TP != 0M)
                {
                    cells.Add(13, 12, row6TP, columnTitleXF812);
                }
                if (row7TC != 0M)
                {
                    cells.Add(14, 11, row7TC, columnTitleXF83);
                }
                if (row7TP != 0M)
                {
                    cells.Add(14, 12, row7TP, columnTitleXF812);
                }
                if (row8TC != 0M)
                {
                    cells.Add(15, 11, row8TC, columnTitleXF83);
                }
                if (row8TP != 0M)
                {
                    cells.Add(15, 12, row8TP, columnTitleXF812);
                }
                if (row9TC != 0M)
                {
                    cells.Add(16, 11, row9TC, columnTitleXF83);
                }
                if (row9TP != 0M)
                {
                    cells.Add(16, 12, row9TP, columnTitleXF812);
                }
                if (row10TC != 0M)
                {
                    cells.Add(17, 11, row10TC, columnTitleXF83);
                }
                if (row10TP != 0M)
                {
                    cells.Add(17, 12, row10TP, columnTitleXF812);
                }
                if (row11TC != 0M)
                {
                    cells.Add(18, 11, row11TC, columnTitleXF83);
                }
                if (row11TP != 0M)
                {
                    cells.Add(18, 12, row11TP, columnTitleXF812);
                }
                decimal tc = row1TC + row2TC + row3TC + row4TC + row5TC + row6TC + row7TC + row8TC + row9TC + row10TC + row11TC;
                if (tc!=0M)
                {
                    cells.Add(19, 11, tc, columnTitleXF83);
                }
                decimal tp = row1TP + row2TP + row3TP + row4TP + row5TP + row6TP + row7TP + row8TP + row9TP + row11TP;
                if (tp != 0M)
                {
                    cells.Add(19, 12, tp, columnTitleXF812);
                }
            }
            xls.Send();
        }
Example #7
0
 // Token: 0x0600016E RID: 366 RVA: 0x000080CB File Offset: 0x000070CB
 private Bytes CellRangeAddress(MergeArea mergeArea)
 {
     return(this.CellRangeAddress(mergeArea.RowMin, mergeArea.RowMax, mergeArea.ColMin, mergeArea.ColMax));
 }
Example #8
0
 private Bytes CellRangeAddress(MergeArea mergeArea)
 {
     return CellRangeAddress(mergeArea.RowMin, mergeArea.RowMax, mergeArea.ColMin, mergeArea.ColMax);
 }
Example #9
0
        //TODO: Optionally provide overload with bool parameter to decide whether to throw
        //exception instead of losing values.
        /// <summary>
        /// Adds a MergeArea to this Worksheet.  The mergeArea is verified not to
        /// overlap with any previously defined area.  NOTE Values and formatting
        /// in all cells other than the first in mergeArea (scanning left to right,
        /// top to bottom) will be lost.
        /// </summary>
        /// <param name="mergeArea">The MergeArea to add to this Worksheet.</param>
        public void AddMergeArea(MergeArea mergeArea)
        {
            foreach (MergeArea existingArea in _mergeAreas)
            {
                bool colsOverlap = false;
                bool rowsOverlap = false;

                //if they overlap, either mergeArea will surround existingArea,
                if (mergeArea.ColMin < existingArea.ColMin && existingArea.ColMax < mergeArea.ColMax)
                    colsOverlap = true;
                //or existingArea will contain >= 1 of mergeArea's Min and Max indices
                else if ((existingArea.ColMin <= mergeArea.ColMin && existingArea.ColMax >= mergeArea.ColMin) ||
                    (existingArea.ColMin <= mergeArea.ColMax && existingArea.ColMax >= mergeArea.ColMax))
                    colsOverlap = true;

                if (mergeArea.RowMin < existingArea.RowMin && existingArea.RowMax < mergeArea.RowMax)
                    rowsOverlap = true;
                else if ((existingArea.RowMin <= mergeArea.RowMin && existingArea.RowMax >= mergeArea.RowMin) ||
                    (existingArea.RowMin <= mergeArea.RowMax && existingArea.RowMax >= mergeArea.RowMax))
                    rowsOverlap = true;

                if (colsOverlap && rowsOverlap)
                    throw new ArgumentException("overlaps with existing MergeArea", "mergeArea");
            }

            //TODO: Add ref to this mergeArea to all rows in its range, and add checking on Cell
            //addition methods to validate they are not being added within the mergedarea, other
            //than as the top-left cell.

            _mergeAreas.Add(mergeArea);
        }