Beispiel #1
0
        public void TestClonePictures()
        {
            IWorkbook        wb  = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithImages.xls");
            InternalWorkbook iwb = ((HSSFWorkbook)wb).Workbook;

            iwb.FindDrawingGroup();

            for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++)
            {
                EscherBSERecord bse = iwb.GetBSERecord(pictureIndex);
                Assert.AreEqual(1, bse.Ref);
            }

            wb.CloneSheet(0);
            for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++)
            {
                EscherBSERecord bse = iwb.GetBSERecord(pictureIndex);
                Assert.AreEqual(2, bse.Ref);
            }

            wb.CloneSheet(0);
            for (int pictureIndex = 1; pictureIndex <= 4; pictureIndex++)
            {
                EscherBSERecord bse = iwb.GetBSERecord(pictureIndex);
                Assert.AreEqual(3, bse.Ref);
            }
        }
Beispiel #2
0
        /// <summary>
        /// Writes course schedules for each room in the excel file.
        /// </summary>
        /// <param name="courseRepo">The collection of courses.</param>
        /// <param name="roomRepo">The collection of rooms.</param>
        public void Print(ICourseRepository courseRepo, IRoomRepository roomRepo)
        {
            List <Course> courses = courseRepo.Courses.ToList();

            var coursesInRoom = from course in courses
                                where course.State == Course.CourseState.Assigned && course.MeetingDays != null
                                group course by course.RoomAssignment;


            foreach (var courseGroup in coursesInRoom)
            {
                Room   room       = courseGroup.Key;
                ISheet sheet      = _workbook.CloneSheet(_workbook.GetSheetIndex(_scheduleTemplate));
                var    sheetIndex = _workbook.GetSheetIndex(sheet);
                _workbook.SetSheetName(sheetIndex, room.RoomName + " " + room.RoomType);
                _workbook.SetSheetHidden(sheetIndex, SheetState.Visible);

                ICell cell = sheet.GetRow(RoomNameLocation.Item1).GetCell(RoomNameLocation.Item2);
                cell.SetCellValue(room.RoomName);

                cell = sheet.GetRow(RoomCapacityLocation.Item1).GetCell(RoomCapacityLocation.Item2);
                cell.SetCellValue(string.Format("Cap: {0}", room.Capacity));

                PrintCourses(sheet, courseGroup.ToList());
                printLegend(sheet);
            }

            _workbook.SortWorksheets();
            _workbook.SetActiveSheet(0);

            _workbook.WriteToFile(_outputFile);
        }
Beispiel #3
0
        public void TestCloneSheetMultipleTimes()
        {
            IWorkbook       workbook = _testDataProvider.CreateWorkbook();
            ICreationHelper factory  = workbook.GetCreationHelper();
            ISheet          sheet    = workbook.CreateSheet("Test Clone");
            IRow            row      = sheet.CreateRow(0);
            ICell           cell     = row.CreateCell(0);

            cell.SetCellValue(factory.CreateRichTextString("Clone_test"));
            //Clone the sheet multiple times
            workbook.CloneSheet(0);
            workbook.CloneSheet(0);

            Assert.IsNotNull(workbook.GetSheet("Test Clone"));
            Assert.IsNotNull(workbook.GetSheet("Test Clone (2)"));
            Assert.AreEqual("Test Clone (3)", workbook.GetSheetName(2));
            Assert.IsNotNull(workbook.GetSheet("Test Clone (3)"));

            workbook.RemoveSheetAt(0);
            workbook.RemoveSheetAt(0);
            workbook.RemoveSheetAt(0);
            workbook.CreateSheet("abc ( 123)");
            workbook.CloneSheet(0);
            Assert.AreEqual("abc (124)", workbook.GetSheetName(1));
        }
Beispiel #4
0
        /// <summary>
        /// 生成工作簿
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="dtData"></param>
        /// <returns></returns>
        private string BuildTableWorkbook(ExportTemplateEntity helpEntity, IEnumerable <CellHelpEntity> list, params DynamicEntity[] entitys)
        {
            string errorMessage = string.Empty;

            List <ISheet> listSheet = new List <ISheet>();
            ISheet        sheet     = list.FirstOrDefault().Cell.Sheet;
            int           sheetNum  = workBook.GetSheetIndex(sheet.SheetName);

            listSheet.Add(sheet);
            int len = entitys.Length;

            if (len > 1)
            {
                for (var i = 0; i < len - 1; i++)
                {
                    ISheet tempSheet = workBook.CloneSheet(sheetNum);
                    listSheet.Add(tempSheet);
                }
            }
            int index = 0;

            foreach (var entity in entitys)
            {
                BuildTableSheet(listSheet[index], list, entity);
                index++;
            }
            return(errorMessage);
        }
        public void Test53691()
        {
            ISheetConditionalFormatting cf;
            IWorkbook wb = HSSFITestDataProvider.Instance.OpenSampleWorkbook("53691.xls");

            /*
             * FileInputStream s = new FileInputStream("C:\\temp\\53691bbadfixed.xls");
             * try {
             *  wb = new HSSFWorkbook(s);
             * } finally {
             *  s.Close();
             * }
             *
             * wb.RemoveSheetAt(1);*/

            // Initially it is good
            WriteTemp53691(wb, "agood");

            // clone sheet corrupts it
            ISheet sheet = wb.CloneSheet(0);

            WriteTemp53691(wb, "bbad");

            // removing the sheet Makes it good again
            wb.RemoveSheetAt(wb.GetSheetIndex(sheet));
            WriteTemp53691(wb, "cgood");

            // cloning again and removing the conditional formatting Makes it good again
            sheet = wb.CloneSheet(0);
            RemoveConditionalFormatting(sheet);
            WriteTemp53691(wb, "dgood");

            // cloning the conditional formatting manually Makes it bad again
            cf = sheet.SheetConditionalFormatting;
            ISheetConditionalFormatting scf = wb.GetSheetAt(0).SheetConditionalFormatting;

            for (int j = 0; j < scf.NumConditionalFormattings; j++)
            {
                cf.AddConditionalFormatting(scf.GetConditionalFormattingAt(j));
            }
            WriteTemp53691(wb, "ebad");

            // remove all conditional formatting for comparing BIFF output
            RemoveConditionalFormatting(sheet);
            RemoveConditionalFormatting(wb.GetSheetAt(0));
            WriteTemp53691(wb, "fgood");

            wb.Close();
        }
Beispiel #6
0
        /**
         *  cloning sheets with feat records
         */
        public void TestCloneSheetWithFeatRecord()
        {
            IWorkbook wb =
                HSSFTestDataSamples.OpenSampleWorkbook("46136-WithWarnings.xls");

            wb.CloneSheet(0);
        }
Beispiel #7
0
        public void TestCloneSheet()
        {
            IWorkbook       workbook = _testDataProvider.CreateWorkbook();
            ICreationHelper factory  = workbook.GetCreationHelper();
            ISheet          sheet    = workbook.CreateSheet("Test Clone");
            IRow            row      = sheet.CreateRow(0);
            ICell           cell     = row.CreateCell(0);
            ICell           cell2    = row.CreateCell(1);

            cell.SetCellValue(factory.CreateRichTextString("Clone_test"));
            cell2.CellFormula = (/*setter*/ "SIN(1)");

            ISheet clonedSheet = workbook.CloneSheet(0);
            IRow   clonedRow   = clonedSheet.GetRow(0);

            //Check for a good clone
            Assert.AreEqual(clonedRow.GetCell(0).RichStringCellValue.String, "Clone_test");

            //Check that the cells are not somehow linked
            cell.SetCellValue(factory.CreateRichTextString("Difference Check"));
            cell2.CellFormula = (/*setter*/ "cos(2)");
            if ("Difference Check".Equals(clonedRow.GetCell(0).RichStringCellValue.String))
            {
                Assert.Fail("string cell not properly Cloned");
            }
            if ("COS(2)".Equals(clonedRow.GetCell(1).CellFormula))
            {
                Assert.Fail("formula cell not properly Cloned");
            }
            Assert.AreEqual(clonedRow.GetCell(0).RichStringCellValue.String, "Clone_test");
            Assert.AreEqual(clonedRow.GetCell(1).CellFormula, "SIN(1)");
        }
Beispiel #8
0
        public void Test22720()
        {
            IWorkbook workBook = _testDataProvider.CreateWorkbook();

            workBook.CreateSheet("TEST");
            ISheet template = workBook.GetSheetAt(0);

            template.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
            template.AddMergedRegion(new CellRangeAddress(1, 2, 0, 2));

            ISheet clone          = workBook.CloneSheet(0);
            int    originalMerged = template.NumMergedRegions;

            Assert.AreEqual(2, originalMerged, "2 merged regions");

            //remove merged regions from clone
            for (int i = template.NumMergedRegions - 1; i >= 0; i--)
            {
                clone.RemoveMergedRegion(i);
            }

            Assert.AreEqual(originalMerged, template.NumMergedRegions, "Original Sheet's Merged Regions were Removed");
            //check if template's merged regions are OK
            if (template.NumMergedRegions > 0)
            {
                // fetch the first merged region...EXCEPTION OCCURS HERE
                template.GetMergedRegion(0);
            }
            //make sure we dont exception
        }
Beispiel #9
0
        public void Bug48495()
        {
            try
            {
                IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48495.xlsx");

                assertSheetOrder(wb, "Sheet1");

                ISheet sheet = wb.GetSheetAt(0);
                sheet.ShiftRows(2, sheet.LastRowNum, 1, true, false);
                IRow newRow = sheet.GetRow(2);
                if (newRow == null)
                {
                    newRow = sheet.CreateRow(2);
                }
                newRow.CreateCell(0).SetCellValue(" Another Header");
                wb.CloneSheet(0);

                assertSheetOrder(wb, "Sheet1", "Sheet1 (2)");

                IWorkbook read = XSSFTestDataSamples.WriteOutAndReadBack(wb);
                Assert.IsNotNull(read);
                assertSheetOrder(read, "Sheet1", "Sheet1 (2)");
            }
            catch (Exception)
            {
            }
        }
        public void Print(ICourseRepository courseRepo, IRoomRepository roomRepo)
        {
            List <Course> courses       = courseRepo.Courses;
            var           test          = courses.FindAll(x => x.RoomAssignment?.Equals("PKI 157") == true);
            List <Course> roomedCourses = courses.FindAll(x => x.AlreadyAssignedRoom && x.MeetingDays != null);
            Dictionary <string, List <Course> > roomCourseMap = getRoomNameToCoursesMap(roomedCourses);

            List <string> roomWithCourses = roomCourseMap.Keys.ToList <string>();

            foreach (string room in roomWithCourses)
            {
                ISheet sheet      = _workbook.CloneSheet(_workbook.GetSheetIndex(_scheduleTemplate));
                var    sheetIndex = _workbook.GetSheetIndex(sheet);
                _workbook.SetSheetName(sheetIndex, room);
                _workbook.SetSheetHidden(sheetIndex, SheetState.Visible);

                ICell cell = sheet.GetRow(RoomNameLocation.Item1).GetCell(RoomNameLocation.Item2);
                cell.SetCellValue(room);

                PrintCourses(sheet, roomCourseMap[room]);
                printLegend(sheet);
            }

            SortWorkbookSheets();

            using (var fileStream = File.OpenWrite(_outputFile))
            {
                _workbook.Write(fileStream);
            }
        }
Beispiel #11
0
        public void CloneSheet()
        {
            IWorkbook book  = _testDataProvider.CreateWorkbook();
            ISheet    sheet = book.CreateSheet("TEST");

            sheet.CreateRow(0).CreateCell(0).SetCellValue("Test");
            sheet.CreateRow(1).CreateCell(0).SetCellValue(36.6);
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 2));
            Assert.IsTrue(sheet.IsSelected);

            ISheet ClonedSheet = book.CloneSheet(0);

            Assert.AreEqual("TEST (2)", ClonedSheet.SheetName);
            Assert.AreEqual(2, ClonedSheet.PhysicalNumberOfRows);
            Assert.AreEqual(2, ClonedSheet.NumMergedRegions);
            Assert.IsFalse(ClonedSheet.IsSelected);

            //Cloned sheet is a deep copy, Adding rows or merged regions in the original does not affect the clone
            sheet.CreateRow(2).CreateCell(0).SetCellValue(1);
            sheet.AddMergedRegion(new CellRangeAddress(4, 5, 0, 2));
            Assert.AreEqual(2, ClonedSheet.PhysicalNumberOfRows);
            Assert.AreEqual(2, ClonedSheet.NumMergedRegions);

            ClonedSheet.CreateRow(2).CreateCell(0).SetCellValue(1);
            ClonedSheet.AddMergedRegion(new CellRangeAddress(6, 7, 0, 2));
            Assert.AreEqual(3, ClonedSheet.PhysicalNumberOfRows);
            Assert.AreEqual(3, ClonedSheet.NumMergedRegions);
            book.Close();
        }
Beispiel #12
0
        public void TestCloneSheetIntValid()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();

            wb.CreateSheet("Sheet01");
            wb.CloneSheet(0);
            Assert.AreEqual(2, wb.NumberOfSheets);
            try
            {
                wb.CloneSheet(2);
                Assert.Fail("ShouldFail");
            }
            catch (ArgumentException e)
            {
                // expected here
            }
        }
Beispiel #13
0
        public int CloneSheet(int arg, string name)
        {
            var s   = book.CloneSheet(arg);
            var idx = book.GetSheetIndex(s.SheetName);

            SetSheetName(idx, name);
            return(idx);
        }
Beispiel #14
0
        /// <summary>
        /// 将一个工作薄克隆出多个工作薄
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="sheetCount"></param>
        public static void CloneOneToManySheet(IWorkbook workbook, ISheet sheet, int sheetCount)
        {
            int sheetIndex = workbook.GetSheetIndex(sheet);

            for (int i = 1; i <= sheetCount - 1; i++)
            {
                workbook.CloneSheet(sheetIndex);
                workbook.SetSheetName(sheetIndex + i, sheet.SheetName + (i + 1).ToString());
            }
        }
Beispiel #15
0
        public void TestCloneSheetBasic()
        {
            IWorkbook b = _testDataProvider.CreateWorkbook();
            ISheet    s = b.CreateSheet("Test");

            s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
            ISheet ClonedSheet = b.CloneSheet(0);

            Assert.AreEqual(1, ClonedSheet.NumMergedRegions, "One merged area");

            b.Close();
        }
Beispiel #16
0
        public void Bug47090c()
        {
            IWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("47090.xlsx");

            assertSheetOrder(workbook, "Sheet1", "Sheet2");
            workbook.RemoveSheetAt(0);
            assertSheetOrder(workbook, "Sheet2");
            workbook.CloneSheet(0);
            assertSheetOrder(workbook, "Sheet2", "Sheet2 (2)");
            IWorkbook read = XSSFTestDataSamples.WriteOutAndReadBack(workbook);

            assertSheetOrder(read, "Sheet2", "Sheet2 (2)");
        }
Beispiel #17
0
        public void Test35084()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet    s  = wb.CreateSheet("Sheet1");
            IRow      r  = s.CreateRow(0);

            r.CreateCell(0).SetCellValue(1);
            r.CreateCell(1).CellFormula = (/*setter*/ "A1*2");
            ISheet s1 = wb.CloneSheet(0);

            r = s1.GetRow(0);
            Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 0, "double"); // sanity check
            Assert.IsNotNull(r.GetCell(1));
            Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2", "formula");
        }
Beispiel #18
0
        public void Test57165()
        {
            IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("57171_57163_57165.xlsx");

            Assert.AreEqual(5, wb.ActiveSheetIndex);
            RemoveAllSheetsBut(3, wb);
            Assert.AreEqual(0, wb.ActiveSheetIndex);
            wb.CreateSheet("New Sheet1");
            Assert.AreEqual(0, wb.ActiveSheetIndex);
            wb.CloneSheet(0); // Throws exception here
            wb.SetSheetName(1, "New Sheet");
            Assert.AreEqual(0, wb.ActiveSheetIndex);

            //wb.Write(new FileOutputStream("/tmp/57165.xls"));
        }
Beispiel #19
0
        public void TestClone()
        {
            IWorkbook wb      = _testDataProvider.CreateWorkbook();
            ISheet    sheet   = wb.CreateSheet();
            String    formula = "7";

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1   = sheetCF.CreateConditionalFormattingRule(formula);
            IFontFormatting            fontFmt = rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            IPatternFormatting patternFmt = rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index);


            IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");

            IConditionalFormattingRule[] cfRules =
            {
                rule1, rule2
            };

            short col = 1;

            CellRangeAddress[] regions =
            {
                new CellRangeAddress(0, 65535, col, col)
            };

            sheetCF.AddConditionalFormatting(regions, cfRules);

            try
            {
                wb.CloneSheet(0);
            }
            catch (Exception e)
            {
                if (e.Message.IndexOf("needs to define a clone method") > 0)
                {
                    Assert.Fail("Indentified bug 45682");
                }
                throw e;
            }
            Assert.AreEqual(2, wb.NumberOfSheets);
        }
Beispiel #20
0
        public void Test18800()
        {
            IWorkbook book = _testDataProvider.CreateWorkbook();

            book.CreateSheet("TEST");
            ISheet sheet = book.CloneSheet(0);

            book.SetSheetName(1, "CLONE");
            sheet.CreateRow(0).CreateCell(0).SetCellValue("Test");

            book  = _testDataProvider.WriteOutAndReadBack(book);
            sheet = book.GetSheet("CLONE");
            IRow  row  = sheet.GetRow(0);
            ICell cell = row.GetCell(0);

            Assert.AreEqual("Test", cell.RichStringCellValue.String);
        }
Beispiel #21
0
        public void TestPageBreakClones()
        {
            IWorkbook b = _testDataProvider.CreateWorkbook();
            ISheet    s = b.CreateSheet("Test");

            s.SetRowBreak(3);
            s.SetColumnBreak((short)6);

            ISheet clone = b.CloneSheet(0);

            Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
            Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");

            s.RemoveRowBreak(3);

            Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");

            b.Close();
        }
Beispiel #22
0
        public void SheetClone()
        {
            // First up, try a simple file
            IWorkbook b = _testDataProvider.CreateWorkbook();

            Assert.AreEqual(0, b.NumberOfSheets);
            b.CreateSheet("Sheet One");
            b.CreateSheet("Sheet Two");
            Assert.AreEqual(2, b.NumberOfSheets);
            b.CloneSheet(0);
            Assert.AreEqual(3, b.NumberOfSheets);
            // Now try a problem one with drawing records in it
            IWorkbook bBack = HSSFTestDataSamples.OpenSampleWorkbook("SheetWithDrawing.xls");

            Assert.AreEqual(1, bBack.NumberOfSheets);
            bBack.CloneSheet(0);
            Assert.AreEqual(2, bBack.NumberOfSheets);
            bBack.Close();
            b.Close();
        }
Beispiel #23
0
        private void renderByTemplate(DataTable data, int maxrow, Dictionary <string, string> keyValues,
                                      List <FillSetting> settings, ISheet sheet, IWorkbook workbook)
        {
            foreach (var s in settings)
            {
                if (s.Flag == FlagType.DataSource)
                {
                    ICellStyle style = null;

                    IRow   row        = sheet.GetRow(s.StartRowIndex);
                    ISheet oldsheet   = sheet;
                    int    rdrowindex = s.StartRowIndex;

                    for (int p = 0; p < data.Rows.Count; p++, rdrowindex++)
                    {
                        DataRow dr = data.Rows[p];
                        if (p >= maxrow)
                        {
                            int idx = workbook.GetSheetIndex(oldsheet);
                            sheet = workbook.CloneSheet(idx);
                            for (int yy = 0; yy < sheet.PhysicalNumberOfRows; yy++)
                            {
                                sheet.RemoveRowBreak(yy);
                            }

                            rdrowindex = s.StartRowIndex + 1;
                        }
                        if (p == 0)
                        {
                            foreach (var col in s.Columns)
                            {
                                var cell = row.GetCell(col.ColumnIndex);

                                if (col.ColumnType != CellType.FORMULA)
                                {
                                    if (!data.Columns.Contains(col.FieldName))
                                    {
                                        cell.SetCellValue(string.Empty);
                                    }
                                    else
                                    {
                                        SetCellValue(cell, data.Columns[col.FieldName], dr[col.FieldName].ToString());
                                    }
                                }
                            }
                        }
                        else
                        {
                            sheet.ShiftRows(rdrowindex, rdrowindex + 1, 1, true, false);
                            IRow newrow = sheet.CreateRow(rdrowindex);
                            foreach (var col in s.Columns)
                            {
                                if (col.ColumnType != CellType.FORMULA)
                                {
                                    var newcell = newrow.CreateCell(col.ColumnIndex);
                                    newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle;
                                    if (!data.Columns.Contains(col.FieldName))
                                    {
                                        newcell.SetCellValue(string.Empty);
                                    }
                                    else
                                    {
                                        SetCellValue(newcell, data.Columns[col.FieldName], dr[col.FieldName].ToString());
                                    }
                                }
                                else
                                {
                                    var newcell = newrow.CreateCell(col.ColumnIndex);
                                    newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle;

                                    newcell.SetCellFormula(col.FieldName.Replace("{0}",
                                                                                 (rdrowindex + 1).ToString()));
                                }
                            }
                        }
                    }

                    for (int zz = 0; zz < s.Columns.Count; zz++)
                    {
                        sheet.AutoSizeColumn(s.Columns[zz].ColumnIndex);
                    }
                    sheet.ForceFormulaRecalculation = true;
                }
            }
        }
        private void renderWageList(DataTable data, int maxrow,
                                    List <FillSetting> settings, ISheet sheet, IWorkbook workbook,
                                    int colHeaderRowIndex, int splitterRowIdx)
        {
            IRow repeatHrow = colHeaderRowIndex >= 0 ? sheet.GetRow(colHeaderRowIndex) : null;
            IRow repeatFrow = splitterRowIdx >= 0 ? sheet.GetRow(splitterRowIdx) : null;

            foreach (var s in settings)
            {
                if (s.Flag == FlagType.DataSource)
                {
                    ICellStyle style = null;

                    IRow   row        = sheet.GetRow(s.StartRowIndex);
                    ISheet oldsheet   = sheet;
                    int    rdrowindex = s.StartRowIndex;

                    for (int p = 0; p < data.Rows.Count; p++, rdrowindex++)
                    {
                        if (repeatHrow != null)
                        {
                            sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false);
                            NPOIHelper.CopyRow(sheet, sheet, colHeaderRowIndex, rdrowindex);

                            rdrowindex++;
                        }

                        DataRow dr = data.Rows[p];
                        if (p >= maxrow)
                        {
                            int idx = workbook.GetSheetIndex(oldsheet);
                            sheet = workbook.CloneSheet(idx);
                            for (int yy = 0; yy < sheet.PhysicalNumberOfRows; yy++)
                            {
                                sheet.RemoveRowBreak(yy);
                            }

                            rdrowindex = s.StartRowIndex + 1;
                        }
                        if (p == 0)
                        {
                            foreach (var col in s.Columns)
                            {
                                var cell = row.GetCell(col.ColumnIndex);

                                if (col.ColumnType != CellType.FORMULA)
                                {
                                    if (!data.Columns.Contains(col.FieldName))
                                    {
                                        break;
                                    }

                                    SetCellValue(cell, data.Columns[col.FieldName], dr[col.FieldName].ToString());
                                }
                            }
                        }
                        else
                        {
                            sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false);
                            IRow newrow = sheet.CreateRow(rdrowindex);
                            foreach (var col in s.Columns)
                            {
                                if (col.ColumnType != CellType.FORMULA)
                                {
                                    if (!data.Columns.Contains(col.FieldName))
                                    {
                                        break;
                                    }

                                    var newcell = newrow.CreateCell(col.ColumnIndex);
                                    newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle;
                                    SetCellValue(newcell, data.Columns[col.FieldName], dr[col.FieldName].ToString());
                                }
                                else
                                {
                                    var newcell = newrow.CreateCell(col.ColumnIndex);
                                    newcell.CellStyle = row.GetCell(col.ColumnIndex).CellStyle;

                                    newcell.SetCellFormula(col.FieldName.Replace("{0}",
                                                                                 (rdrowindex + 1).ToString()));
                                }
                            }
                        }

                        if (repeatFrow != null)
                        {
                            sheet.ShiftRows(rdrowindex, sheet.LastRowNum, 1, true, false);
                            NPOIHelper.CopyRow(sheet, sheet, splitterRowIdx, rdrowindex);

                            rdrowindex++;
                        }
                    }


                    for (int zz = 0; zz < s.Columns.Count; zz++)
                    {
                        sheet.AutoSizeColumn(s.Columns[zz].ColumnIndex);
                    }
                    sheet.ForceFormulaRecalculation = true;
                }
            }
        }
Beispiel #25
0
        /// <summary>
        /// output the processData to excel
        /// excel is genrate accorint to input templatefile
        /// </summary>
        /// <param name="templatefile"></param>
        /// <param name="outputpath"></param>
        public void generateExcel(string templatefile, string outputpath)
        {
            const int templatepage  = 3;
            const int formatline    = 11;
            const int datastartline = 11;
            const int notestartline = 12;
            const int existdataline = notestartline - datastartline;

            string outputFullName = Path.Combine(outputpath, Path.GetFileName(templatefile));

            if (false == File.Exists(templatefile))
            {
                TraceMethod.Record(TraceMethod.TraceKind.ERROR,
                                   $"CI-LEU report templelate file is missing {templatefile}, please check!");
                return;
            }
            using (FileStream sw = new FileStream(templatefile, FileMode.Open, FileAccess.ReadWrite))
            {
                workBook = createworkbook(sw);
                if (null == workBook)
                {
                    return;
                }

                getstyles(workBook, templatepage, formatline);

                int sheetidx = 1;
                foreach (var page in processData)
                {
                    ISheet sheet = workBook.CloneSheet(templatepage);
                    workBook.SetSheetName(templatepage + sheetidx, $"{page.Key}联锁区");
                    ++sheetidx;

                    int recordnum = processData[page.Key].Count();
                    sheet.ShiftRows(notestartline, sheet.LastRowNum, recordnum - existdataline, true, true);
                    for (int wor = 0; wor < recordnum; ++wor)
                    {
                        IRow row = sheet.CreateRow(datastartline + wor);
                        if (null == processData[page.Key][wor])
                        {
                            //empty row need do nothing
                        }
                        else if (defaultmsgcols.Count() == processData[page.Key][wor].Count)
                        {
                            setDefaultMsgRow(row, processData[page.Key][wor]);
                        }
                        else if (cols.Count() == processData[page.Key][wor].Count)
                        {
                            setRow(row, processData[page.Key][wor], cols);
                        }
                        else
                        {
                            TraceMethod.Record(TraceMethod.TraceKind.ERROR,
                                               $"get invalid data reocrd cols count {processData[page.Key][wor].Count}\n"
                                               + $"{processData[page.Key][wor].ToString()}");
                        }
                    }
                    //set MergedRegion, but the existed line should not set again or excel will raise error while open
                    for (int rowidx = 0; rowidx < recordnum - existdataline; rowidx++)
                    {
                        for (int mergedidx = 0; mergedidx < mergedcolinfo.Count() / 2; ++mergedidx)
                        {
                            CellRangeAddress region = new CellRangeAddress(
                                datastartline + existdataline + rowidx, datastartline + existdataline + rowidx,
                                mergedcolinfo[mergedidx * 2], mergedcolinfo[mergedidx * 2 + 1]);
                            sheet.AddMergedRegion(region);
                        }
                    }
                }
                workBook.RemoveSheetAt(templatepage);

                FileStream sw1 = new FileStream(outputFullName, FileMode.Create, FileAccess.ReadWrite);
                workBook.Write(sw1);
                sw.Close();
                sw1.Close();
            }
        }