예제 #1
0
파일: Program.cs 프로젝트: zqb971/npoi-1
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet1=hssfworkbook.CreateSheet("Sheet1");

            //font style1: underlined, italic, red color, fontsize=20
            IFont font1 = hssfworkbook.CreateFont();
            font1.Color = HSSFColor.Red.Index;
            font1.IsItalic = true;
            font1.Underline = FontUnderlineType.Double;
            font1.FontHeightInPoints = 20;

            //bind font with style 1
           ICellStyle style1 = hssfworkbook.CreateCellStyle();
            style1.SetFont(font1);

            //font style2: strikeout line, green color, fontsize=15, fontname='宋体'
            IFont font2 = hssfworkbook.CreateFont();
            font2.Color = HSSFColor.OliveGreen.Index;
            font2.IsStrikeout=true;
            font2.FontHeightInPoints = 15;
            font2.FontName = "宋体";

            //bind font with style 2
           ICellStyle style2 = hssfworkbook.CreateCellStyle();
            style2.SetFont(font2);
            
            //apply font styles
            ICell cell1 = HSSFCellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
            cell1.CellStyle = style1;
            ICell cell2 = HSSFCellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好!");
            cell2.CellStyle = style2;

            //cell with rich text 
            ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
            HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");

            //apply font to "Microsoft Office"
            IFont font4 = hssfworkbook.CreateFont();
            font4.FontHeightInPoints = 12;
            richtext.ApplyFont(0, 16, font4);
            //apply font to "TM"
            IFont font3=hssfworkbook.CreateFont();
            font3.TypeOffset = FontSuperScript.Super;
            font3.IsItalic = true;
            font3.Color = HSSFColor.Blue.Index;
            font3.FontHeightInPoints=8;
            richtext.ApplyFont(16, 18,font3);
            
            cell3.SetCellValue(richtext);

            WriteToFile();
        }
예제 #2
0
        public static bool Generate(Entities.ReportInfo info)
        {
            bool res = false;

            try
            {
                string title = DomainModel.Application.
                               ResourceManager.GetText("rep_monthly_bill_title");

                int   colnum = 0;
                int   rownum = -1;
                Sheet sheet;
                Row   row;
                Cell  head;

                TypeDic    typeDic = new TypeDic();
                ServiceDic srvDic  = new ServiceDic();
                HeaderRow  headRow = new HeaderRow();

                // Create header
                int index = 0;

                // Create first col: day#
                HeaderCell hc = new HeaderCell(index);
                hc.Text = DomainModel.
                          Application.ResourceManager.GetText("rep_monthly_bill_col_day");
                headRow.Add(hc);
                index++;

                // create service type cols
                srvDic.Clear();
                foreach (Entities.Service srv in DomainModel.Services.GetAll())
                {
                    hc      = new HeaderCell(index);
                    hc.Text = string.Format("  {0} {1} ({2})  ",
                                            srv.ServiceType.Name,
                                            srv.Name,
                                            srv.PricePerPerson.FormattedValue);

                    headRow.Add(hc);

                    srvDic.Add(srv, new ServiceCell(index));

                    index++;
                }

                // Create payment type columns
                foreach (Entities.GeneralType type in DomainModel.PaymentTypes.GetAll())
                {
                    hc      = new HeaderCell(index);
                    hc.Text = "  " + type.Name + "  ";
                    headRow.Add(hc);

                    typeDic.Add(type, new ServiceCell(index));
                    index++;
                }

                HSSFWorkbook hssfworkbook = new HSSFWorkbook();

                // Create theme
                Font headFont = hssfworkbook.CreateFont();
                headFont.Color      = HSSFColor.WHITE.index;
                headFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;

                CellStyle headstyle = hssfworkbook.CreateCellStyle();
                headstyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
                headstyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                headstyle.SetFont(headFont);

                //// Create a entry of DocumentSummaryInformation
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "Ms. Anke Maass";
                hssfworkbook.DocumentSummaryInformation = dsi;

                //// Create a entry of SummaryInformation
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "Tour monthly bill";
                hssfworkbook.SummaryInformation = si;

                // For each month in report inf
                for (DateTime date = info.StartTime; date.Month <= info.EndTime.Month; date = date.AddMonths(1))
                {
                    colnum = 0;

                    string month      = date.ToString("MMMM");
                    string sheetTitle = string.Format("{0} {1:00} {2:0000}",
                                                      title, month, DateTime.Now.Year);

                    // Create sheet
                    sheet = hssfworkbook.CreateSheet(month);
                    //sheet.DefaultColumnWidth = 25;

                    rownum = -1;
                    row    = sheet.CreateRow(++rownum); // Title
                    row    = sheet.CreateRow(++rownum); // Space
                    row    = sheet.CreateRow(++rownum); // Col. headers

                    // Set columns titles
                    foreach (HeaderCell headCell in headRow)
                    {
                        row.CreateCell(colnum).SetCellValue(headCell.Text);
                        row.GetCell(colnum).CellStyle = headstyle;
                        sheet.AutoSizeColumn(colnum);

                        colnum++;
                    }

                    // Set sheet title (After columns for autowidth calculations)
                    row            = sheet.GetRow(0);
                    head           = HSSFCellUtil.CreateCell(row, 0, sheetTitle);
                    head.CellStyle = headstyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));

                    row            = sheet.GetRow(1);
                    head           = HSSFCellUtil.CreateCell(row, 0, "");
                    head.CellStyle = headstyle;
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 6));

                    // Create count rows
                    for (DateTime dt = date; dt.Month <= date.Month; dt = dt.AddDays(1))
                    {
                        row = sheet.CreateRow(++rownum);

                        row.CreateCell(0).SetCellValue(dt.Day);
                        row.GetCell(0).CellStyle = headstyle;

                        Entities.TourCollection tours = new Entities.TourCollection();
                        DomainModel.Tours.LoadByDate(dt, tours);

                        // Reset counters
                        foreach (Entities.Service key in srvDic.Keys)
                        {
                            srvDic[key].Count = 0;
                        }

                        foreach (Entities.GeneralType key in typeDic.Keys)
                        {
                            typeDic[key].Value = 0;
                        }

                        // calc columns
                        foreach (Entities.Tour tour in tours)
                        {
                            foreach (Entities.TourGroup group in tour.Groups)
                            {
                                foreach (Entities.Abstract.ITourService srv in group.Services)
                                {
                                    srvDic[srv.Detail].Count += srv.CostDetails.ServiceCount;

                                    foreach (Entities.TourPayment payment in srv.Payments)
                                    {
                                        typeDic[payment.Type].Value += payment.Amount.Value;
                                    }
                                }
                            }
                        }

                        // Set row cells
                        foreach (Entities.Service key in srvDic.Keys)
                        {
                            if (srvDic[key].Count != 0)
                            {
                                row.CreateCell(srvDic[key].Index).SetCellValue(srvDic[key].Count);
                            }
                        }

                        foreach (Entities.GeneralType key in typeDic.Keys)
                        {
                            if (typeDic[key].Value > 0.0M)
                            {
                                row.CreateCell(typeDic[key].Index).SetCellValue(
                                    (double)typeDic[key].Value);
                            }
                        }
                    }
                }

                // Create footer

                //Write the stream data of workbook to the root directory
                FileStream file = new FileStream(info.Path, FileMode.Create);
                hssfworkbook.Write(file);
                file.Close();

                res = true;
            }
            catch (Exception ex)
            {
            }

            return(res);
        }