C# XSSFWorkbook.CreateSheet方法代码示例

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet s1 = workbook.CreateSheet("Sheet1");

            ICellStyle rowstyle = workbook.CreateCellStyle();
            rowstyle.FillForegroundColor = IndexedColors.Red.Index;
            rowstyle.FillPattern = FillPattern.SolidForeground;

            ICellStyle c1Style = workbook.CreateCellStyle();
            c1Style.FillForegroundColor = IndexedColors.Yellow.Index;
            c1Style.FillPattern = FillPattern.SolidForeground;

            IRow r1 = s1.CreateRow(1);
            IRow r2= s1.CreateRow(2);
            r1.RowStyle = rowstyle;
            r2.RowStyle = rowstyle;

            ICell c1 = r2.CreateCell(2);
            c1.CellStyle = c1Style;
            c1.SetCellValue("Test");

            ICell c4 = r2.CreateCell(4);
            c4.CellStyle = c1Style;

            using(var fs=File.Create("test.xlsx"))
            {
                workbook.Write(fs);
            }
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");


            // Aqua background
            ICellStyle style = wb.CreateCellStyle();
            style.FillBackgroundColor = IndexedColors.Aqua.Index;
            style.FillPattern = FillPattern.BigSpots;

            IRow row = ws.CreateRow(0);
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("X");
            cell.CellStyle = style;            

            // Orange "foreground", foreground being the fill foreground not the font color.
            style = wb.CreateCellStyle();
            style.FillBackgroundColor = IndexedColors.Orange.Index;
            style.FillPattern = FillPattern.SolidForeground;
           
            cell = row.CreateCell(2);
            cell.SetCellValue("X");
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();

            
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("First Sheet");
            ISheet sheet2 = wb.CreateSheet("Second Sheet");
            

            // Note that sheet name is Excel must not exceed 31 characters
            // and must not contain any of the any of the following characters:
            // 0x0000
            // 0x0003
            // colon (:)
            // backslash (\)
            // asterisk (*)
            // question mark (?)
            // forward slash (/)
            // opening square bracket ([)
            // closing square bracket (])

            // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
            // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
            String safeName = WorkbookUtil.CreateSafeSheetName("[O'Brien's sales*?]");
            ISheet sheet3 = wb.CreateSheet(safeName);

            FileStream sw = File.Create("newWorksheet.xls");
            wb.Write(sw);
            sw.Close();            
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ////cell style for hyperlinks
            ////by default hyperlinks are blue and underlined
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
            hlink_font.Color = HSSFColor.BLUE.index;
            hlink_style.SetFont(hlink_font);

            ICell cell;
            ISheet sheet = workbook.CreateSheet("Hyperlinks");

            //URL
            cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL Link");
            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
            link.Address = ("http://poi.apache.org/");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a file in the current directory
            cell = sheet.CreateRow(1).CreateCell(0);
            cell.SetCellValue("File Link");
            link = new XSSFHyperlink(HyperlinkType.FILE);
            link.Address = ("link1.xls");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //e-mail link
            cell = sheet.CreateRow(2).CreateCell(0);
            cell.SetCellValue("Email Link");
            link = new XSSFHyperlink(HyperlinkType.EMAIL);
            //note, if subject contains white spaces, make sure they are url-encoded
            link.Address = ("mailto:[email protected]?subject=Hyperlinks");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a place in this workbook

            //Create a target sheet and cell
            ISheet sheet2 = workbook.CreateSheet("Target ISheet");
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");

            cell = sheet.CreateRow(3).CreateCell(0);
            cell.SetCellValue("Worksheet Link");
            link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
            link.Address = ("'Target ISheet'!A1");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            workbook.CreateSheet("Sheet A1");
            workbook.CreateSheet("Sheet A2");
            workbook.CreateSheet("Sheet A3");

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        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(); 
        }

        protected void btn_click(object sender, EventArgs e)
        {
            //FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
            //XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
            //ISheet nsheet = temWorkBook.GetSheet("Sheet1");
            //IRow datarow = nsheet.GetRow(4);

            //datarow.GetCell(0).SetCellValue(77);
            //nsheet.ForceFormulaRecalculation = true;

            //using (var ms = new MemoryStream())
            //{
            //    temWorkBook.Write(ms);

            //    Response.Clear();
            //    Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //    Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
            //    Response.BinaryWrite(ms.ToArray());
            //    Response.End();
            //}

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("New Sheet");
            ISheet sheet2 = workbook.CreateSheet("Second Sheet");
            ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
            IFont fontBold = workbook.CreateFont();
            fontBold.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(fontBold);
            cell1.CellStyle = style1;
            cell1.SetCellValue("sample value");
            int x = 1;
            for (int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for(int j = 0;j < 15;j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);

                Response.Clear();
                Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
                Response.BinaryWrite(ms.ToArray());
                Response.End();
            }
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();
            wb.CreateSheet("new sheet");
            wb.CreateSheet("second sheet");
            wb.CreateSheet("third sheet");

            wb.SetSheetOrder("second sheet", 0);
            wb.SetSheetOrder("new sheet", 1);
            wb.SetSheetOrder("third sheet", 2);

            FileStream sw = File.Create("../../data/Reordered.xls");
            wb.Write(sw);
            sw.Close(); 
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();

            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            
            // Setting support for Right To Left
            sheet1.IsRightToLeft = true;

            sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");

            int x = 1;

            for(int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);

                for(int j = 0; j < 15; j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }

            FileStream sw = File.Create("test.xlsx");

            workbook.Write(sw);

            sw.Close();
        }

        public void Create(Bilan bilan, string path)
        {
            var splitPath = path.Split('\\');
            var pathFile = splitPath.Take(splitPath.Count() - 1).Aggregate((e, p) => e + "\\" + p) + "\\";

            var fileNameOld = splitPath.Last();

            var fileName = "synoptique_" + fileNameOld;

            using (var stream = new FileStream(pathFile + fileName, FileMode.Create, FileAccess.Write))
            {
                var workbook = new XSSFWorkbook();
                var sheet = workbook.CreateSheet("synoptique");

                var cadreArmoire = new CadreArmoire(sheet, workbook);
                cadreArmoire.Create(bilan.PageDeGarde.ChambrePMZ);

                var cadrePA = new CadrePA(sheet, workbook);
                cadrePA.Create(bilan.PageDeGarde.ChambrePointAboutement);

                var positionnementEtudeCreator = new PositionnementEtudeCreator(sheet, workbook);

                var hauteur = 9;
                var numeroCassette = 0;
                foreach (var positionnementEtude in bilan.PositionnementEtudes)
                {
                    positionnementEtudeCreator.Create(positionnementEtude, ref hauteur, ref numeroCassette);
                }

                SetWidth(sheet);

                workbook.Write(stream);
            }
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");

            ICellStyle style = wb.CreateCellStyle();

            //Setting the line of the top border
            style.BorderTop = BorderStyle.Thick;
            style.TopBorderColor = 256;

            style.BorderLeft = BorderStyle.Thick;
            style.LeftBorderColor = 256;

            style.BorderRight = BorderStyle.Thick;
            style.RightBorderColor = 256;

            style.BorderBottom = BorderStyle.Thick;
            style.BottomBorderColor = 256;

            IRow row = ws.CreateRow(0);
            ICell cell = row.CreateCell(1);
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();
        }

        public override void CreateWorkBook(string destinationPath)
        {
            try
            {
                var workbook = new XSSFWorkbook();
                var sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1");

                sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample  xlsx Sheet");
                //var x = 1;
                //for (var i = 1; i <= 10000; i++)
                //{
                //    var row = sheet1.CreateRow(i);
                //    for (var j = 0; j < 50; j++)
                //    {
                //        row.CreateCell(j).SetCellValue("cell" + x++);
                //    }
                //}
                var s = sheet1 as ISheet;

                CreateRows(20000, 40, ref s);

                using (var f = File.Create(destinationPath))
                {
                    workbook.Write(f);
                }
            }
            catch (Exception e)
            {
                throw new Exception("Cannot create Excell SpreadSheet (.xlsx)", e);
            }
        }

        public void TestBug48936()
        {
            IWorkbook w = new XSSFWorkbook();
            ISheet s = w.CreateSheet();
            int i = 0;
            List<String> lst = ReadStrings("48936-strings.txt");
            foreach (String str in lst)
            {
                s.CreateRow(i++).CreateCell(0).SetCellValue(str);
            }

            try
            {
                w = XSSFTestDataSamples.WriteOutAndReadBack(w);
            }
            catch (POIXMLException)
            {
                Assert.Fail("Detected Bug #48936");
            }
            s = w.GetSheetAt(0);
            i = 0;
            foreach (String str in lst)
            {
                String val = s.GetRow(i++).GetCell(0).StringCellValue;
                Assert.AreEqual(str, val);
            }
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("First Sheet");

           //add picture data to this workbook.
            byte[] bytes = File.ReadAllBytes("../../data/aspose.png");
            int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);

            ICreationHelper helper = wb.GetCreationHelper();

            // Create the drawing patriarch.  This is the top level container for all shapes.
            IDrawing drawing = sheet1.CreateDrawingPatriarch();

            // add a picture shape
            IClientAnchor anchor = helper.CreateClientAnchor();

            //set top-left corner of the picture,
            //subsequent call of Picture#resize() will operate relative to it
            anchor.Col1 = 3;
            anchor.Row1 = 2;
            IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
            //auto-size picture relative to its top-left corner
            pict.Resize();

            FileStream sw = File.Create("../../data/image.xlsx");
            wb.Write(sw);
            sw.Close();
        }

        public void ExportExcel(IList<dynamic> items, string fileName)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet worksheet = workbook.CreateSheet("Sheet1");

            for (int rownum = 0; rownum < items.Count; rownum++)
            {
                IRow row = worksheet.CreateRow(rownum);
                var item = items[rownum];
                var keys = item.Keys.ToArray();
                for (int index = 0; index < keys.Length; index++)
                {
                    var key = keys[index];
                    ICell cell = row.CreateCell(index);
                    var value = item[key];
                    if (value != null)
                    {
                        cell.SetCellValue(value.ToString());
                    }
                }
            }

            using (FileStream sw = File.Create(fileName))
            {
                workbook.Write(sw);
                sw.Close();
            }
        }