コード例 #1
0
        public void RepeatingRowsAndColumns(int Sheetindex, int RowIdex, int CellIndex)
        {
            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.GetSheetAt(Sheetindex);

            IFont boldFont = hssfworkbook.CreateFont();

            boldFont.FontHeightInPoints = 22;
            boldFont.Boldweight         = (short)FontBoldWeight.BOLD;

            ICellStyle boldStyle = hssfworkbook.CreateCellStyle();

            boldStyle.SetFont(boldFont);

            IRow  row  = sheet1.GetRow(RowIdex);
            ICell cell = row.GetCell(CellIndex);

            cell.CellStyle = (boldStyle);

            // Set the columns to repeat from column 0 to 2 on the first sheet
            hssfworkbook.SetRepeatingRowsAndColumns(Sheetindex, 0, 2, -1, -1);

            WriteToFile();
        }
コード例 #2
0
ファイル: TestHSSFWorkbook.cs プロジェクト: purehzj/npoi-1
        public void TestFindBuiltInNameRecord()
        {
            // TestRRaC has multiple (3) built-in name records
            // The second print titles name record has SheetNumber==4
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRRaC.xls");
            NameRecord   nr;

            Assert.AreEqual(3, wb.Workbook.NumNames);
            nr = wb.Workbook.GetNameRecord(2);
            // TODO - render full row and full column refs properly
            Assert.AreEqual("Sheet2!$A$1:$IV$1", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // 1:1

            try
            {
                wb.SetRepeatingRowsAndColumns(3, 4, 5, 8, 11);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Builtin (7) already exists for sheet (4)"))
                {
                    // there was a problem in the code which locates the existing print titles name record
                    throw new Exception("Identified bug 45720b");
                }
                throw e;
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            Assert.AreEqual(3, wb.Workbook.NumNames);
            nr = wb.Workbook.GetNameRecord(2);
            Assert.AreEqual("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // E:F,9:12
        }
コード例 #3
0
ファイル: TestHSSFName.cs プロジェクト: xewn/Npoi.Core
        public void TestRepeatingRowsAndColumsNames()
        {
            // First Test that Setting RR&C for same sheet more than once only Creates a
            // single  Print_Titles built-in record
            HSSFWorkbook wb    = new HSSFWorkbook();
            HSSFSheet    sheet = (HSSFSheet)wb.CreateSheet("FirstSheet");

            // set repeating rows and columns twice for the first sheet
            for (int i = 0; i < 2; i++)
            {
                wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
                sheet.CreateFreezePane(0, 3);
            }
            Assert.AreEqual(1, wb.NumberOfNames);
            IName nr1 = wb.GetNameAt(0);

            Assert.AreEqual("Print_Titles", nr1.NameName);
            if (false)
            {
#if !HIDE_UNREACHABLE_CODE
                //     TODO - full column references not rendering properly, absolute markers not present either
                Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
#endif
            }
            else
            {
                Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
            }

            // Save and re-open
            HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            Assert.AreEqual(1, nwb.NumberOfNames);
            nr1 = nwb.GetNameAt(0);

            Assert.AreEqual("Print_Titles", nr1.NameName);
            Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);

            // check that Setting RR&C on a second sheet causes a new Print_Titles built-in
            // name to be Created
            sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet");
            nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);

            Assert.AreEqual(2, nwb.NumberOfNames);
            IName nr2 = nwb.GetNameAt(1);

            Assert.AreEqual("Print_Titles", nr2.NameName);
            Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);

            //if (false) {
            //    // In case you fancy Checking in excel, to ensure it
            //    //  won't complain about the file now
            //        File tempFile = File.CreateTempFile("POI-45126-", ".xls");
            //        FileOutputStream fout = new FileOutputStream(tempFile);
            //        nwb.Write(fout);
            //        fout.close();
            //        Console.WriteLine("check out " + tempFile.GetAbsolutePath());
            //}
        }
コード例 #4
0
ファイル: TestHSSFWorkbook.cs プロジェクト: purehzj/npoi-1
        public void TestSetRepeatingRowsAndColumns()
        {
            // Test bug 29747
            HSSFWorkbook b = new HSSFWorkbook();

            b.CreateSheet();
            b.CreateSheet();
            b.CreateSheet();
            b.SetRepeatingRowsAndColumns(2, 0, 1, -1, -1);
            NameRecord nameRecord = b.Workbook.GetNameRecord(0);

            Assert.AreEqual(3, nameRecord.SheetNumber);
        }
コード例 #5
0
ファイル: TestWorkbook.cs プロジェクト: hiodava/Romero
        public void TestRepeatingColsRowsMinusOne()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = (HSSFSheet)workbook.CreateSheet("Test Print Titles");

            IRow row = sheet.CreateRow(0);

            ICell cell = row.CreateCell(1);

            cell.SetCellValue(new HSSFRichTextString("hi"));


            workbook.SetRepeatingRowsAndColumns(0, -1, 1, -1, 0);

            FileInfo file = TempFile.CreateTempFile("testPrintTitlesA", ".xls");

            FileStream fileOut = new FileStream(file.FullName, FileMode.Create, FileAccess.ReadWrite);

            workbook.Write(fileOut);
            fileOut.Close();
            Assert.IsTrue(file.Exists, "file exists");
        }
コード例 #6
0
ファイル: TestWorkbook.cs プロジェクト: hiodava/Romero
        public void TestRepeatingColsRows()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Test Print Titles");

            IRow row = sheet.CreateRow(0);

            ICell cell = row.CreateCell(1);

            cell.SetCellValue(new HSSFRichTextString("hi"));


            workbook.SetRepeatingRowsAndColumns(0, 0, 1, 0, 0);

            string filepath = TempFile.GetTempFilePath("TestPrintTitles", ".xls");

            FileStream fileOut = new FileStream(filepath, FileMode.OpenOrCreate);

            workbook.Write(fileOut);
            fileOut.Close();

            Assert.IsTrue(File.Exists(filepath), "file exists");
        }
コード例 #7
0
ファイル: TestNamedRange.cs プロジェクト: thinhmascot/NPOI
        public void TestRepeatingRowsAndColumsNames()
        {
            // First Test that setting RR&C for same sheet more than once only Creates a
            // single  Print_Titles built-in record
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("FirstSheet");

            // set repeating rows and columns twice for the first sheet
            for (int i = 0; i < 2; i++)
            {
                wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1);
                sheet.CreateFreezePane(0, 3);
            }
            Assert.AreEqual(1, wb.NumberOfNames);
            NPOI.SS.UserModel.Name nr1 = wb.GetNameAt(0);

            Assert.AreEqual("Print_Titles", nr1.NameName);
            if (false)
            {
                //  TODO - full column references not rendering properly, absolute markers not present either
                Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula);
            }
            else
            {
                Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);
            }

            // Save and re-Open
            HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            Assert.AreEqual(1, nwb.NumberOfNames);
            nr1 = nwb.GetNameAt(0);

            Assert.AreEqual("Print_Titles", nr1.NameName);
            Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula);

            // Check that setting RR&C on a second sheet causes a new Print_Titles built-in
            // name to be Created
            sheet = nwb.CreateSheet("SecondSheet");
            nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0);

            Assert.AreEqual(2, nwb.NumberOfNames);
            NPOI.SS.UserModel.Name nr2 = nwb.GetNameAt(1);

            Assert.AreEqual("Print_Titles", nr2.NameName);
            Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula);

            if (false)
            {
                // In case you fancy Checking in excel, to ensure it
                //  won't complain about the file now
                try
                {
                    string     tmppath = NPOI.Util.TempFile.GetTempFilePath("POI-45126-", ".xls");
                    FileStream fout    = new FileStream(tmppath, FileMode.OpenOrCreate);
                    nwb.Write(fout);
                    fout.Close();
                    Console.WriteLine("Check out " + Path.GetFullPath(tmppath));
                }
                catch (IOException)
                {
                    throw;
                }
            }
        }
コード例 #8
0
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="records"></param>
        /// <param name="root"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="columns"></param>
        internal void SetData <T>(IList <T> records, Root root, HSSFWorkbook workbook, ISheet sheet, List <PropertyInfo> columns)
        {
            IList <HSSFCellStyle> ics = new List <HSSFCellStyle>();
            Int32 height = 20;

            for (Int32 r = 0; r < records.Count; r++)
            {
                IRow row = null;
                if (!sheet.IsNullOrEmpty())
                {
                    row = sheet.CreateRow(root.head.rowspan.GetInt32() + r);
                }

                for (Int32 i = 0; i < columns.Count; i++)
                {
                    if (columns[i].IsNullOrEmpty())
                    {
                        continue;
                    }
                    var    value   = columns[i].GetValue(records[r], null);
                    String drValue = String.Empty;
                    if (value != null)
                    {
                        drValue = value.ToString();
                    }

                    drValue = drValue.Replace("<br/>", "\n");
                    drValue = drValue.Replace("<br>", "\n");
                    if (!row.IsNullOrEmpty())
                    {
                        ICell newCell = row.CreateCell(i);

                        if (ics.Count < columns.Count)
                        {
                            ExportColumn ec = root.head.columns.SingleOrDefault(c => c.dataIndex == columns[i].Name);

                            SetHeadCellBold(ec);
                            // 设置行高
                            if (!ec.dheight.IsNullOrEmpty())
                            {
                                height = ec.dheight.GetInt32() == 0 ? 1 : ec.dheight.GetInt32();
                            }
                            // 获得样式
                            ICellStyle style = GetCellStyle(workbook, ExcelStyle.Default, ec);
                            ics.Add(style as HSSFCellStyle);
                            newCell.CellStyle = style;
                            //设置单元格的宽度
                            if (!root.head.defaultwidth.IsNullOrEmpty() && !ec.width.IsNullOrEmpty())
                            {
                                sheet.SetColumnWidth(i, ec.width * 40);
                            }
                            else
                            {
                                sheet.AutoSizeColumn(i); //每列宽度自适应
                            }
                        }
                        else if (ics.Count == columns.Count)
                        {
                            HSSFCellStyle style = ics[i];
                            newCell.CellStyle = style;
                        }
                        if (height > 20)
                        {
                            row.Height = (short)(height * 20);
                        }
                        newCell.SetCellValue(drValue);
                    }
                }
            }

            // 打印水平居中
            sheet.HorizontallyCenter = true;
            // 设置打印方向
            sheet.PrintSetup.Landscape = root.head.landscape;
            // 设置网络线
            sheet.DisplayGridlines = false;

            // 上边距
            sheet.SetMargin(MarginType.TopMargin, root.topMargin.IsNullOrEmpty() ? 1.05 : root.topMargin.GetDouble());
            // 下边距
            sheet.SetMargin(MarginType.BottomMargin, root.bottomMargin.IsNullOrEmpty() ? 0.7 : root.bottomMargin.GetDouble());
            // 左边距
            sheet.SetMargin(MarginType.LeftMargin, root.leftMargin.IsNullOrEmpty() ? 0.7 : root.leftMargin.GetDouble());
            // 右边距
            sheet.SetMargin(MarginType.RightMargin, root.rightMargin.IsNullOrEmpty() ? 0.7 : root.rightMargin.GetDouble());
            //// 页眉边距
            sheet.PrintSetup.HeaderMargin = root.headerMargin.IsNullOrEmpty() ? 0.5 : root.headerMargin.GetDouble();
            //// 页脚边距
            sheet.PrintSetup.FooterMargin = root.footerMargin.IsNullOrEmpty() ? 0.5 : root.footerMargin.GetDouble();

            // 设置缩放比例
            sheet.PrintSetup.FitWidth  = 100;
            sheet.PrintSetup.FitHeight = 100;
            sheet.PrintSetup.Scale     = 100;
            sheet.SetZoom(1, 1);
            // 设置纸张
            sheet.PrintSetup.PaperSize = 9;

            // 设置顶端标题行
            Int32 endRow = root.head.rowspan.GetInt32() > 1 ? root.head.rowspan.GetInt32() - 1 : 0;

            workbook.SetRepeatingRowsAndColumns(0, 0, 0, 0, endRow);
        }