C# XSSFWorkbook.Write方法代码示例

        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");

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

        static void Main(string[] args)
        {
            if (args.Length != 2)
                return;

            string src = args[0];
            string target = args[1];
            

            RunMode mode= RunMode.Excel;
            if (src.Contains(".docx"))
                mode = RunMode.Word;

            if (mode == RunMode.Excel)
            {
                Stream rfs = File.OpenRead(src);
                IWorkbook workbook = new XSSFWorkbook(rfs);
                rfs.Close();
                using (FileStream fs = File.Create(target))
                {
                    workbook.Write(fs);
                }
            }
            else
            {
                Stream rfs = File.OpenRead(src);
                XWPFDocument workbook = new XWPFDocument(rfs);
                rfs.Close();
                using (FileStream fs = File.Create(target))
                {
                    workbook.Write(fs);
                }
            }
        }

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

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

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

            // Create a new font and alter it
            IFont font = wb.CreateFont();
            font.FontHeightInPoints = 24;
            font.FontName = "Courier New";
            font.IsItalic = true;
            font.IsStrikeout = true;            

            // Fonts are set into a style so create a new one to use.
            ICellStyle style = wb.CreateCellStyle();
            style.SetFont(font);

            IRow row = ws.CreateRow(0);

            // Create a cell and put a value in it.
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("Thisi s a test of fonts");
            cell.CellStyle = style;

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

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


            IDrawing patriarch = sheet1.CreateDrawingPatriarch();
            //create the anchor
            XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
            anchor.AnchorType = 2;
            //load the picture and get the picture index in the workbook
            //first picture
            int imageId= LoadImage("../../image/HumpbackWhale.jpg", workbook);
            XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
            //Reset the image to the original size.
            //picture.Resize();   //Note: Resize will reset client anchor you set.
            picture.LineStyle = LineStyle.DashDotGel;

            //second picture
            int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
            XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
            XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
            picture.LineStyle = LineStyle.DashDotGel;

            FileStream sw = File.Create("test.xlsx");
            workbook.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();
            }
        }

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

        protected void Button1_Click(object sender, EventArgs e)
        {
            string filename = "test.xlsx";

            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));

            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            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++);
                }
            }
            using (var f = File.Create(@"c:\test.xlsx"))
            {
                workbook.Write(f);
            }
            Response.WriteFile(@"c:\test.xlsx");
            //http://social.msdn.microsoft.com/Forums/en-US/3a7bdd79-f926-4a5e-bcb0-ef81b6c09dcf/responseoutputstreamwrite-writes-all-but-insetrs-a-char-every-64k?forum=ncl
            //workbook.Write(Response.OutputStream); cannot be used 
            //root cause: Response.OutputStream will insert unnecessary byte into the response bytes.
            Response.Flush();
            Response.End();
        }

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


            // Create a row and put some cells in it. Rows are 0 based.
            IRow row;
            ICell cell;
            for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
            {
                row = sheet.CreateRow((short)rowIndex);
                for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
                {
                    cell = row.CreateCell((short)colIndex);
                    cell.SetCellValue(colIndex * (rowIndex + 1));
                }
            }

            IDrawing drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
            CreateChart(drawing, sheet, anchor1, "title1","title2");
            IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);
            CreateChart(drawing, sheet, anchor2, "s1", "s2");
            using (FileStream fs =File.Create("test.xlsx"))
            {
                wb.Write(fs);
            }
        }

        public static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();
            ISheet s1=wb.CreateSheet("Monthly Salary Report");
            IRow headerRow = s1.CreateRow(0);
            headerRow.CreateCell(0).SetCellValue("First Name");
            s1.SetColumnWidth(0, 20 * 256);
            headerRow.CreateCell(1).SetCellValue("Last Name");
            s1.SetColumnWidth(1, 20 * 256);
            headerRow.CreateCell(2).SetCellValue("Salary");
            headerRow.CreateCell(3).SetCellValue("Tax Rate");
            headerRow.CreateCell(4).SetCellValue("Tax");
            headerRow.CreateCell(5).SetCellValue("Delivery");

            int row = 1;
            GenerateRow(s1, row++, "Bill", "Zhang", 5000, 9.0/100);
            GenerateRow(s1, row++, "Amy", "Huang", 8000, 11.0/100);
            GenerateRow(s1, row++, "Tomos", "Johnson", 6000, 9.0/100);
            GenerateRow(s1, row++, "Macro", "Jeep", 12000, 15.0/100);
            s1.ForceFormulaRecalculation = false;

            FileStream fs = File.Create("test.xlsx");
            wb.Write(fs);
            fs.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);
            }
        }

        public static bool UpdateExcelData(string excelFilePath, string excelName)
        {
            XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open));
            var sheet = excelBook.GetSheetAt(0);
            var headerRow = sheet.GetRow(0);
            //total columns
            int cellCount = headerRow.LastCellNum;
            //total rows
            int rowCount = sheet.LastRowNum;

            switch (excelName)
            {
                case Constant.Water:
                    UpdateWaterData(sheet, cellCount, rowCount);
                    break;
                case Constant.Air:
                    UpdateAirData(sheet, cellCount, rowCount);
                    break;
            }

            try
            {
                FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write);
                excelBook.Write(writefile);
                writefile.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

 public ActionResult ExportExcel()
 {
     var data = this.rptRepository.All();
     string path = Server.MapPath("~/Templates/客戶資訊.xlsx");
     FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
     IWorkbook wb = new XSSFWorkbook(stream);
     stream.Close();
     ISheet sheet = wb.GetSheetAt(0);
     ICellStyle cs = sheet.GetRow(1).Cells[0].CellStyle;
     int Index = 1;
     foreach (var item in data)
     {
         IRow row = sheet.CreateRow(Index);
         CreateCell(item.客戶名稱, row, 0, cs);
         CreateCell(item.聯絡人數量.ToString(), row, 1, cs);
         CreateCell(item.客戶帳戶數量.ToString(), row, 2, cs);
         Index++;
     }
     string serverPath = @"D:/repot.xlsx";
     using (FileStream file = new FileStream(serverPath, FileMode.Create))
     {
         wb.Write(file);
         file.Close();
     }
     return File(serverPath, "application/excel","Report.xlsx");
 }