예제 #1
0
        public void TestCloneSheetIntStringValidName()
        {
            ISheet cloned = wb.CloneSheet(0, OTHER_SHEET_NAME);

            Assert.AreEqual(OTHER_SHEET_NAME, cloned.SheetName);
            Assert.AreEqual(2, wb.NumberOfSheets);
        }
예제 #2
0
        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet("new sheet");
            wb.CreateSheet("second sheet");
            ISheet cloneSheet = wb.CloneSheet(0);

            FileStream sw = File.Create("newWorksheet.xls");

            wb.Write(sw);
            sw.Close();
        }
예제 #3
0
        public void TestClone()
        {
            XSSFWorkbook wb     = XSSFTestDataSamples.OpenSampleWorkbook("WithDrawing.xlsx");
            XSSFSheet    sheet1 = wb.GetSheetAt(0) as XSSFSheet;

            XSSFSheet sheet2 = wb.CloneSheet(0) as XSSFSheet;

            //the source sheet has one relationship and it is XSSFDrawing
            List <POIXMLDocumentPart> rels1 = sheet1.GetRelations();

            Assert.AreEqual(1, rels1.Count);
            Assert.IsTrue(rels1[(0)] is XSSFDrawing);

            List <POIXMLDocumentPart> rels2 = sheet2.GetRelations();

            Assert.AreEqual(1, rels2.Count);
            Assert.IsTrue(rels2[(0)] is XSSFDrawing);

            XSSFDrawing drawing1 = (XSSFDrawing)rels1[0];
            XSSFDrawing drawing2 = (XSSFDrawing)rels2[0];

            Assert.AreNotSame(drawing1, drawing2);  // Drawing2 is a clone of Drawing1

            List <XSSFShape> shapes1 = drawing1.GetShapes();
            List <XSSFShape> shapes2 = drawing2.GetShapes();

            Assert.AreEqual(shapes1.Count, shapes2.Count);

            for (int i = 0; i < shapes1.Count; i++)
            {
                XSSFShape sh1 = (XSSFShape)shapes1[(i)];
                XSSFShape sh2 = (XSSFShape)shapes2[i];

                Assert.IsTrue(sh1.GetType() == sh2.GetType());
                Assert.AreEqual(sh1.GetShapeProperties().ToString(), sh2.GetShapeProperties().ToString());
            }

            checkRewrite(wb);
            wb.Close();
        }
예제 #4
0
        public async Task <MemoryStream> Merge(Reports reports, IFormFile excelFile)
        {
            string       sFileName      = excelFile.FileName;
            string       sWebRootFolder = _webHostEnvironment.WebRootPath;
            XSSFWorkbook workbook;
            MemoryStream memory = new MemoryStream();

            using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.ReadWrite))
            {
                excelFile.CopyTo(stream);
                stream.Position = 0;
                workbook        = new XSSFWorkbook(stream);
            }

            if (workbook.GetSheet("Final") == null)
            {
                workbook.CloneSheet(0, "Final");
            }
            var finalSheet = workbook.GetSheet("Final");

            foreach (var reportVal in reports.Report.ReportVal)
            {
                int cellRow = 0;
                int cellCol = 0;

                for (int row = 10; row <= 11; row++)
                {
                    if (finalSheet.GetRow(row).GetCell(1).StringCellValue.Substring(1) == reportVal.ReportRow)
                    {
                        cellRow = row;
                        break;
                    }
                }
                for (int col = 4; col <= 10; col++)
                {
                    if (finalSheet.GetRow(9).GetCell(col).StringCellValue.Substring(1) == reportVal.ReportCol)
                    {
                        cellCol = col;
                        break;
                    }
                }
                if (cellRow != 0 && cellCol != 0)
                {
                    var cell = finalSheet.GetRow(cellRow).GetCell(cellCol);
                    cell.SetCellValue(reportVal.Val);
                }
            }

            using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Create, FileAccess.ReadWrite))
            {
                workbook.Write(stream);
            }

            using (var stream = new FileStream(Path.Combine(sWebRootFolder, sFileName), FileMode.Open))
            {
                await stream.CopyToAsync(memory);
            }

            memory.Position = 0;
            return(memory);
        }
예제 #5
0
        public void GenerateDocument(List <string> tableNames, string templatePath, string outputDocPath)
        {
            const string sheetName4TableListTempalte   = "#TableListTemplate";
            const string sheetName4TableSchemaTempalte = "#TableSchemaTemplate";
            const string tagName4TableNo           = "#table.no";
            const string tagName4TableName         = "#table.name";
            const string tagName4TableDesc         = "#table.description";
            const string tagName4TableView         = "#table.view";
            const string tagName4TableType         = "#table.type";
            const string tagName4ColumnNo          = "#column.no";
            const string tagName4ColumnName        = "#column.name";
            const string tagName4ColumnPK          = "#column.pk";
            const string tagName4ColumnFK          = "#column.fk";
            const string tagName4ColumnFKReference = "#column.fkreference";
            const string tagName4ColumnNullable    = "#column.nullable";
            const string tagName4ColumnIdentity    = "#column.identity";
            const string tagName4ColumnDataType    = "#column.datatype";
            const string tagName4ColumnDefault     = "#column.default";
            const string tagName4ColumnDesc        = "#column.description";


            // open template file
            XSSFWorkbook workbook;

            using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
            {
                workbook = new XSSFWorkbook(fs);
            }



            // TABLE LIST

            // clone tempalte table list sheet & rename sheet
            var tableSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableListTempalte));

            workbook.SetSheetName(workbook.NumberOfSheets - 1, "Table List");

            var tableIndex = 1;

            foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
            {
                var noLocation = tableSheet.FindCellLocation(tagName4TableNo);
                if (noLocation.HasValue)
                {
                    // copy the table info row from tempalte
                    tableSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);

                    var newRowIndex = noLocation.Value.Y;
                    tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableNo, tableIndex.ToString());
                    tableSheet.SetFirstMatchCellHyperlinkInRow(newRowIndex, tagName4TableName, table.TableName, tableIndex.ToString());
                    tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableDesc, table.Description);
                    tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableView, table.IsViewTable ? "V" : "");
                    tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableType, table.TableType.ToTitleCase());

                    tableIndex++;
                }
            }

            // remove the last template row
            tableSheet.RemoveFirstMatchRow(tagName4TableNo);


            // TABLE SCHEMA SHEETS

            tableIndex = 1;
            foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName)))
            {
                // clone tempalte schema sheet & rename sheet
                var schemaSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));
                workbook.SetSheetName(workbook.NumberOfSheets - 1, tableIndex.ToString() /*table.TableName*/);

                // table name & description
                schemaSheet.SetFirstMatchCellContent(tagName4TableName, table.TableName);
                schemaSheet.SetFirstMatchCellContent(tagName4TableDesc, table.Description);
                schemaSheet.SetFirstMatchCellContent(tagName4TableView, table.IsViewTable ? "V" : "");
                schemaSheet.SetFirstMatchCellContent(tagName4TableType, table.TableType.ToTitleCase());

                // prepare each column info
                foreach (var column in table.Columns)
                {
                    var noLocation = schemaSheet.FindCellLocation(tagName4ColumnNo);
                    if (noLocation.HasValue)
                    {
                        // copy the column info row from tempalte
                        schemaSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1);

                        var newRowIndex = noLocation.Value.Y;
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNo, column.No.ToString());
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnName, column.ColumnName);
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnPK, column.IsPrimaryKey ? "V" : "");
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFK, column.IsForeignKey ? "V" : "");
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFKReference, column.FkReferencedInfo);
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNullable, column.IsNullable ? "V" : "");
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnIdentity, column.IsIdentity ? "V" : "");
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDataType, column.FullDataType);
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDefault, column.Default);
                        schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDesc, column.Description);
                    }
                }

                // remove the last template row
                schemaSheet.RemoveFirstMatchRow(tagName4ColumnNo);

                tableIndex++;
            }

            // remove template sheet
            workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableListTempalte));
            workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableSchemaTempalte));

            // save as another excel file
            using (FileStream stream = new FileStream(outputDocPath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(stream);
            }

            // set null
            workbook = null;
        }