示例#1
0
        protected void Page_Load(object sender, EventArgs e)
        {
            cgApp   = new CateGoryApplication();
            tickApp = new TicketsApplication();
            tsApp   = new TimeSheetApplication();
            projApp = new ProjectApplication();

            try
            {
                if (!string.IsNullOrEmpty(Request.Params["date"]))
                {
                    this.SelectedDate = Convert.ToDateTime(Request.QueryString["date"]);
                    if (!TimeSheetTicket.CanEdit(this.SelectedDate))
                    {
                        Alert("This date is invalid", "ListTimesheet.aspx");
                    }
                }
                else
                {
                    this.SelectedDate = DateTime.Now;
                }
            }
            catch
            {
                this.SelectedDate = DateTime.Now;
            }
            if (!IsPostBack)
            {
                InitControls();
                this.Title = string.Format("Write Timesheet - {0}", this.SelectedDate.ToString("MM/dd/yyyy"));
            }
        }
示例#2
0
        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(Request.Params["date"]))
                {
                    this.SelectedDate = Convert.ToDateTime(Request.QueryString["date"]);
                    if (!TimeSheetTicket.CanEdit(this.SelectedDate))
                    {
                        Redirect("Index.aspx");
                    }
                    if (SelectedDate.Date > DateTime.Now)
                    {
                        btnSubmit.Visible = false;
                    }
                }
                else
                {
                    this.SelectedDate = DateTime.Now;
                }
            }
            catch
            {
                this.SelectedDate = DateTime.Now;
            }
            if (!IsPostBack)
            {
                Title = "Write timesheet - " + SelectedDate.ToString("MM/dd/yyyy");

                List <CateGoryEntity> list = cgApp.GetCateGroyListByUserID(UserInfo.ID);
                if (list == null)
                {
                    list = new List <CateGoryEntity>();
                }
                var category = list.Select(x => new { category = x.Title, id = x.ID }).ToList();
                category.Insert(0, new { category = DefaulSelectText, id = 0 });
                this.CategoryJson = JsonConvert.SerializeObject(category, DoBase.DateConverter);

                List <ProjectDetailDTO> listProj = projApp.GetUserProjects(UserInfo);
                if (listProj != null && listProj.Count > 0)
                {
                    listProj.RemoveAll(r => r.Status == ProjectStatus.Cancelled || r.Status == ProjectStatus.Completed);
                }
                var project = listProj.Select(x => new { title = x.Title, id = x.ProjectID, tickets = new List <int>() }).ToList();
                project.Insert(0, new { title = DefaulSelectText, id = 0, tickets = new List <int>() });
                this.ProjectJson = JsonConvert.SerializeObject(project, DoBase.DateConverter);
            }
            totalQWeeklyHours = tsApp.TotalWeeklyHours(this.SelectedDate, UserInfo.ID);
        }
示例#3
0
        protected string GetOutputFileName(List <TimeSheetTicket> tss, UsersEntity user, string projectTitle, DateTime eDate)
        {
            if (tss.Count == 0)
            {
                return(user.FirstName + "_TimeSheet.xlsx");
            }
            TimeSheetTicket ts = tss[0];
            string          pn = projectTitle.Replace(" ", "_");
            string          ed = "All";
            string          fn = user.FirstName;

            if (eDate <= new DateTime(1800, 1, 1))
            {
                ed = eDate.ToString("MMddyyyy");
            }
            return(pn + "_" + ed + "_" + fn + ".xlsx");
        }
示例#4
0
        //private delegate List<ClientInfo> AsyncGetDelegate(List<Guid> gIds);

        private string GenerateReport(List <TimeSheetTicket> tss, UsersEntity user)
        {
            tss = tss.OrderBy(ts => ts.SheetDate).ToList <TimeSheetTicket>();

            string filePath = AppDomain.CurrentDomain.BaseDirectory + "Template\\MyTimesheetTemplate.xlsx";

            wb = app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            ws = (Worksheet)wb.Worksheets[1];

            #region replace username
            Range  rng       = ws.get_Range(ws.Cells[1, 1], ws.Cells[999, 20]);
            object tmp       = "{UserName}";
            Range  userRange = rng.Find(tmp, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);
            if (userRange != null)
            {
                userRange.Value2     = "SunNet - " + user.FirstName;
                userRange.Font.Color = this.DefaultColor;
            }
            #endregion

            if (tss.Count != 0)
            {
                //get what will show on client range
                IEnumerable <TimeSheetTicket> wholeSheets = tss.Distinct <TimeSheetTicket>(new Compairint());
                string       clientTmp  = string.Empty;
                var          q          = from w in wholeSheets select w.ProjectID;
                List <Int32> projectIDs = q.Distinct().ToList <Int32>();

                //ClientManager cMgr = new ClientManager();
                //AsyncGetDelegate pGetDelegate = new AsyncGetDelegate(cMgr.GetClients);

                //List<ClientInfo> clients = new List<ClientInfo>();
                //IAsyncResult ar = null;
                //if (projectIDs.Count > 0)
                //{
                //begin get projects
                //ar = pGetDelegate.BeginInvoke(projectIDs, null, null);
                //}

                TimeSheetTicket startTimeSheetTicket = tss.First();
                TimeSheetTicket endTimeSheetTicket   = tss.Last();
                int             cellRowStart         = 8;

                decimal totalHours = decimal.Zero;

                for (DateTime dt = startTimeSheetTicket.SheetDate; dt <= endTimeSheetTicket.SheetDate; dt = dt.AddDays(1))
                {
                    //get sheets in this day
                    List <TimeSheetTicket> dailySheets = new List <TimeSheetTicket>();
                    dailySheets = tss.FindAll(ts => ts.SheetDate == dt);
                    //get distinct count of projects in this sheets
                    IEnumerable <TimeSheetTicket> distinct = dailySheets.Distinct <TimeSheetTicket>(new Compairint());

                    int count = distinct.Count();
                    //merge date column caused by the counts of projects in this day
                    Range dateRange;
                    Range projectRange;
                    Range rngTicket;
                    //when user have multiple project in one day
                    if (count > 1)
                    {
                        dateRange = ws.get_Range(ws.Cells[cellRowStart, 2], ws.Cells[cellRowStart + count - 1, 2]);
                        dateRange.Merge(False);
                    }
                    else
                    {
                        dateRange = (Range)ws.Cells[cellRowStart, 2];
                    }

                    int i = 0;
                    foreach (TimeSheetTicket p in distinct)
                    {
                        //set project title
                        projectRange = (Range)ws.Cells[cellRowStart + i, 3];
                        RangeSetting(projectRange, p.ProjectTitle, false, false);
                        projectRange.Font.Color = this.DefaultColor;
                        //get ticket column data from daily data
                        List <TimeSheetTicket> sheetOnTickets = dailySheets.FindAll(ds => ds.ProjectID == p.ProjectID);

                        //get ticket column of the project
                        rngTicket = (Range)ws.Cells[cellRowStart + i, 4];
                        StringBuilder ticketColumntext = new StringBuilder();
                        decimal       hoursSpent       = decimal.Zero;
                        int           j = 1;
                        foreach (TimeSheetTicket ts in sheetOnTickets)
                        {
                            totalHours += ts.Hours;
                            hoursSpent += ts.Hours;
                            ticketColumntext.Append(j.ToString() + (ts.TicketCode.Length == 0 ? "[ ]" : ". [" + ts.TicketCode + "]") +
                                                    (ts.TicketTitle.Length == 0 ? "[ ]" : "[" + ts.TicketTitle + "]/\t\n"));


                            if (ts.TicketDescription.Length > 0)
                            {
                                ticketColumntext.Append("[" + ts.TicketDescription + "]\t\n");
                            }
                            if (ts.WorkDetail.Length > 0)
                            {
                                ticketColumntext.Append("[" + ts.WorkDetail + "]\t\n");
                            }
                            //ticketColumntext.Append("\t\n");
                            j++;
                        }
                        ticketColumntext.Remove(ticketColumntext.Length - 2, 2);
                        //set ticket column text
                        RangeSetting(rngTicket, ticketColumntext.ToString(), false, false);
                        rngTicket.Font.Color = this.DefaultColor;
                        //set hour column
                        Range rngHours = (Range)ws.Cells[cellRowStart + i, 6];
                        RangeSetting(rngHours, hoursSpent.ToString("#,#0.00"), false, false);
                        rngHours.Font.Color   = this.DefaultColor;
                        rngHours.NumberFormat = "#,#0.00";
                        i++;
                    }


                    //set date column

                    RangeSetting(dateRange, dt.ToString("MM/dd/yyyy"), false, true);
                    dateRange.Font.Color = this.DefaultColor;
                    cellRowStart         = cellRowStart + i;
                }
                #region end

                for (int k = 8; k < cellRowStart; k++)
                {
                    Range dataRange = (Range)ws.get_Range("B" + k.ToString(), "H" + k.ToString());
                    if (k % 2 == 0)
                    {
                        dataRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(229, 235, 247));
                    }
                    else
                    {
                        dataRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(240, 243, 250));
                    }
                }



                Range wholeRange = (Range)ws.get_Range("B8", "H" + cellRowStart.ToString());
                if (tss.Count > 0)
                {
                    Range totalHoursName = (Range)ws.Cells[cellRowStart, 5];
                    RangeSetting(totalHoursName, "Total Hours:", true, false);
                    totalHoursName.Font.Color = ColorTranslator.ToOle(Color.FromArgb(0, 0, 102));



                    Range totalHoursColumn = (Range)ws.Cells[cellRowStart, 6];
                    //RangeSetting(totalHoursColumn, totalHours.ToString("f1"), true, false);

                    RangeSetting(totalHoursColumn, string.Format("=SUM(F8:F{0})", cellRowStart - 1), true, false);

                    totalHoursColumn.NumberFormat = "#,#0.00";
                    totalHoursColumn.Font.Color   = ColorTranslator.ToOle(Color.FromArgb(0, 0, 102));
                    Range hourSameRowRange = (Range)ws.get_Range("B" + cellRowStart.ToString(), "D" + cellRowStart.ToString());
                    hourSameRowRange.Merge(False);

                    hourSameRowRange = (Range)ws.get_Range("G" + cellRowStart.ToString(), "H" + cellRowStart.ToString());
                    hourSameRowRange.Merge(False);

                    hourSameRowRange = (Range)ws.get_Range("B" + cellRowStart.ToString(), "H" + cellRowStart.ToString());


                    hourSameRowRange.Borders.Color = ColorTranslator.ToOle(Color.FromArgb(127, 127, 127));
                    hourSameRowRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle                   = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                    hourSameRowRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    hourSameRowRange.Interior.Color    = ColorTranslator.ToOle(Color.FromArgb(255, 159, 87));


                    wholeRange.EntireRow.AutoFit();
                    //wholeRange.EntireColumn.AutoFit();
                }
                for (Char a = 'B'; a <= 'H'; a = Convert.ToChar(Convert.ToInt16(a) + 1))
                {
                    Range tmpRange = (Range)ws.get_Range(a.ToString() + "8", a.ToString() + (cellRowStart - 1).ToString());
                    tmpRange.Borders.Color = ColorTranslator.ToOle(Color.FromArgb(127, 127, 127));
                    tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle                   = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                    tmpRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    if (a != 'D')
                    {
                        //tmpRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(207, 207, 207));
                        tmpRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    }
                    else
                    {
                        tmpRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    }
                }


                if (projectIDs.Count > 0)
                {
                    Range topRange = (Range)ws.get_Range("F5", "H5");
                    //clients = pGetDelegate.EndInvoke(ar);
                    Range clientRange = topRange.Find("{Client}", Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);

                    //var q1 = (from cp in clients select cp.ClientName).Distinct();
                    //if (q1.Count() == 1)
                    //{
                    //    if (clientRange != null)
                    //    {
                    //        RangeSetting(clientRange, q1.First(), false, false);
                    //        clientRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    //        clientRange.Font.Color = this.DefaultColor;
                    //    }
                    //}
                    //else
                    //{
                    RangeSetting(clientRange, "", false, false);
                    Range rngClientName = topRange.Find("Client:", Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);
                    // clientRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlLineStyleNone;
                    topRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlLineStyleNone;
                    RangeSetting(rngClientName, "", false, false);
                    //}
                }



                #endregion
            }
            //Range ticketRange = (Range)ws.get_Range("B8", "B" + cellRowStart.ToString());
            //ticketRange.Interior.Color =

            return(SaveExcel(ws, wb));
        }
示例#5
0
        private string GenerateSearchResultReport(List <TimeSheetTicket> tss)
        {
            tss = tss.OrderBy(ts => ts.SheetDate).ToList <TimeSheetTicket>();
            string filePath = AppDomain.CurrentDomain.BaseDirectory + "Template\\AllTemplate.xlsx";

            wb = app.Workbooks.Open(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                                    , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            ws = (Worksheet)wb.Worksheets[1];

            int     cellRowStart = 8;
            decimal totalHours   = decimal.Zero;

            if (tss.Count != 0)
            {
                TimeSheetTicket startTimeSheetTicket = tss.First();
                TimeSheetTicket endTimeSheetTicket   = tss.Last();


                for (DateTime dt = startTimeSheetTicket.SheetDate; dt <= endTimeSheetTicket.SheetDate; dt = dt.AddDays(1))
                {
                    List <TimeSheetTicket> dailySheets = new List <TimeSheetTicket>();
                    dailySheets = tss.FindAll(ts => ts.SheetDate == dt);

                    if (dailySheets == null || dailySheets.Count <= 0)
                    {
                        continue;
                    }

                    int cellRowStartInner = cellRowStart;
                    int DateRows          = cellRowStart;

                    //daily project
                    List <TimeSheetTicket> projectDistinct = dailySheets.Distinct <TimeSheetTicket>(new Compairint()).ToList <TimeSheetTicket>();

                    foreach (TimeSheetTicket ts in projectDistinct)
                    {
                        List <TimeSheetTicket> dailyUser = new List <TimeSheetTicket>();
                        dailyUser = dailySheets.FindAll(tv => tv.ProjectID == ts.ProjectID).Distinct <TimeSheetTicket>(new CompairintUsers()).ToList <TimeSheetTicket>();

                        int i = 0;
                        int projectBeforeMerge = cellRowStartInner;
                        foreach (TimeSheetTicket tUser in dailyUser)
                        {
                            List <TimeSheetTicket> userSheetsInOneday = dailySheets.FindAll(tx => tx.UserID == tUser.UserID && tx.ProjectID == ts.ProjectID);

                            decimal dailyUserProjectHours = decimal.Zero;
                            Range   userTickets           = (Range)ws.Cells[cellRowStartInner, 4];
                            string  tmp = string.Empty;

                            int j = 1;
                            foreach (TimeSheetTicket tu in userSheetsInOneday)
                            {
                                tmp += j.ToString() + ". [" + tu.TicketCode + "] [" + tu.TicketTitle + "] /\t\n";
                                if (tu.TicketDescription.Length > 0)
                                {
                                    tmp += "[" + tu.TicketDescription + "]";
                                }
                                if (tu.WorkDetail.Length > 0)
                                {
                                    tmp += "\r\n[" + tu.WorkDetail + "]";
                                }
                                tmp += "\r\n";
                                dailyUserProjectHours += tu.Hours;
                                totalHours            += tu.Hours;
                                j++;
                            }
                            RangeSetting(userTickets, tmp, false, false);
                            userTickets.Font.Color = this.DefaultColor;

                            Range userNameRange = (Range)ws.Cells[cellRowStartInner, 5];
                            RangeSetting(userNameRange, string.Format("{0} {1}", tUser.FirstName, tUser.LastName), false, false);
                            userNameRange.Font.Color = this.DefaultColor;

                            Range hourTmp = (Range)ws.Cells[cellRowStartInner, 6];
                            RangeSetting(hourTmp, dailyUserProjectHours.ToString("#,#0.00"), false, false);
                            hourTmp.Font.Color = this.DefaultColor;

                            hourTmp.NumberFormat = "#,#0.00";
                            cellRowStartInner++;
                            i++;
                        }


                        Range projectTitleRange;
                        if (i > 1)
                        {
                            DateRows          = i;
                            projectTitleRange = (Range)ws.get_Range(ws.Cells[projectBeforeMerge, 3], ws.Cells[cellRowStartInner - 1, 3]);
                            projectTitleRange.Merge(False);
                        }
                        else
                        {
                            projectTitleRange = (Range)ws.Cells[projectBeforeMerge, 3];
                        }

                        RangeSetting(projectTitleRange, ts.ProjectTitle, false, false);
                        projectTitleRange.Font.Color = this.DefaultColor;
                        projectBeforeMerge           = cellRowStartInner;
                    }

                    Range dateRange;
                    if (DateRows > 1)
                    {
                        dateRange = (Range)ws.get_Range(ws.Cells[cellRowStart, 2], ws.Cells[cellRowStartInner - 1, 2]);
                        dateRange.Merge(False);
                    }
                    else
                    {
                        dateRange = (Range)ws.Cells[cellRowStart, 2];
                    }
                    RangeSetting(dateRange, dt.ToString("MM/dd/yyyy"), false, true);
                    dateRange.Font.Color = this.DefaultColor;
                    cellRowStart         = cellRowStartInner;
                }

                #region end

                Range dataRange = (Range)ws.get_Range("B8", "H" + cellRowStart.ToString());
                dataRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(229, 235, 247));
                //for (int k = 8; k < cellRowStart; k++)
                //{

                //    if (k % 2 == 0)
                //    {
                //        dataRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(229, 235, 247));
                //    }
                //    else
                //    {
                //        dataRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(240, 243, 250));
                //    }
                //}


                Range wholeRange = (Range)ws.get_Range("B8", "H" + cellRowStart.ToString());
                if (tss.Count > 0)
                {
                    Range totalHoursName = (Range)ws.Cells[cellRowStart, 5];
                    RangeSetting(totalHoursName, "Total Hours:", true, false);
                    totalHoursName.Font.Color = ColorTranslator.ToOle(Color.FromArgb(0, 0, 102));
                    //totalHoursName.Borders.Color = ColorTranslator.ToOle(Color.FromArgb(0, 0, 102));


                    Range totalHoursColumn = (Range)ws.Cells[cellRowStart, 6];
                    //RangeSetting(totalHoursColumn, totalHours.ToString("f1"), true, false);
                    RangeSetting(totalHoursColumn, string.Format("=SUM(F8:F{0})", cellRowStart - 1), true, false);

                    totalHoursColumn.Font.Color   = ColorTranslator.ToOle(Color.FromArgb(0, 0, 102));
                    totalHoursColumn.NumberFormat = "#,#0.00";

                    Range hourSameRowRange = (Range)ws.get_Range("B" + cellRowStart.ToString(), "D" + cellRowStart.ToString());
                    hourSameRowRange.Merge(False);

                    hourSameRowRange = (Range)ws.get_Range("G" + cellRowStart.ToString(), "H" + cellRowStart.ToString());
                    hourSameRowRange.Merge(False);

                    hourSameRowRange = (Range)ws.get_Range("B" + cellRowStart.ToString(), "H" + cellRowStart.ToString());


                    hourSameRowRange.Borders.Color = ColorTranslator.ToOle(Color.FromArgb(127, 127, 127));
                    hourSameRowRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle                   = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                    hourSameRowRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                    hourSameRowRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    hourSameRowRange.Interior.Color    = ColorTranslator.ToOle(Color.FromArgb(255, 159, 87));


                    wholeRange.EntireRow.AutoFit();
                }
                for (Char a = 'B'; a <= 'H'; a = Convert.ToChar(Convert.ToInt16(a) + 1))
                {
                    Range tmpRange = (Range)ws.get_Range(a.ToString() + "8", a.ToString() + (cellRowStart - 1).ToString());
                    tmpRange.Borders.Color = ColorTranslator.ToOle(Color.FromArgb(127, 127, 127));
                    tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle                   = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                    tmpRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                    tmpRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                    if (a != 'D')
                    {
                        //tmpRange.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(207, 207, 207));
                        tmpRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    }
                    else
                    {
                        tmpRange.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    }
                }
            }
            #endregion
            return(SaveExcel(ws, wb));
        }