C# HSSFWorkbook.Write方法代码示例

        public ActionResult CreateExcel()
        {
            HSSFWorkbook wk = new HSSFWorkbook();
            ISheet tb = wk.CreateSheet("MyFirst");
            //创建一行，此行为第二行:创建行是从0开始
            IRow row = tb.CreateRow(1);
            ICell cell;
            for (int i = 0; i < 20; i++)
            {
                cell = row.CreateCell(i);  //在第二行中创建单元格
                cell.SetCellValue("This is a test");//循环往第二行的单元格中添加数据
            }
            //            ISheet tb = wk.CreateSheet("MyFirst");
            //            HSSFPatriarch patr = sheet.CreateDrawingPatriarch();
            //            HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));
            //            comment1.String = new HSSFRichTextString("Hello World");
            //            comment1.Author = "NPOI Team"
            //;
            //            cell.CellComment = comment1;

            FileStream fs = new FileStream(@"f:/test.xls", FileMode.Create);
            wk.Write(fs);
            fs.Close();
            return View();
        }

 /// <summary>
 ///     导出WOrd文件
 /// </summary>
 public void Generate()
 {
     //首先在内存中创建一个Excel文件
     var fileStream = new FileStream(outputPath, FileMode.OpenOrCreate);
     var workbook = new HSSFWorkbook();
     //创建一个表(sheet)--并获取他
     var sheet = (HSSFSheet) workbook.CreateSheet("华中科技大学工程");
     //首先把需要多少个cell创建好----有多少个数据就有多少行--每行有3个数据
     for (var i = 0; i <= dataList.Count; i++)
     {
         sheet.CreateRow(i);
     }
     //创建表头
     GenerateHeaderRow(workbook, sheet);
     //然后给每行---创建单元格---添加数据
     GenerateDataRow(workbook, sheet);
     try
     {
         //获取目标文件流--写入文件数据
         workbook.Write(fileStream);
         fileStream.Close();
     }
     catch (Exception e)
     {
         Console.Error.WriteLine(e.StackTrace);
         MessageBox.Show("文件导出失败", "失败");
         return;
     }
     finally
     {
         if (fileStream != null)
             fileStream.Close();
     }
     MessageBox.Show("Excel文件导出成功", "完成");
 }

 MemoryStream GenerateData()
 {
     HSSFWorkbook hssfworkbook = new HSSFWorkbook();
     MemoryStream ms = new MemoryStream();
     var movies = (from m in db.Movies
                   select m).ToList();
     ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
     IRow headerRow = sheet1.CreateRow(0);
     headerRow.CreateCell(0).SetCellValue("ID");
     headerRow.CreateCell(1).SetCellValue("电影名");
     headerRow.CreateCell(2).SetCellValue("发布日期");
     headerRow.CreateCell(3).SetCellValue("类型");
     headerRow.CreateCell(4).SetCellValue("价格");
     for (int i = 0; i < movies.Count; i++)
     {
         IRow Row = sheet1.CreateRow(i + 1);
         Row.CreateCell(0).SetCellValue(movies[i].ID);
         Row.CreateCell(1).SetCellValue(movies[i].Title);
         Row.CreateCell(2).SetCellValue(movies[i].ReleaseDate);
         Row.CreateCell(3).SetCellValue(movies[i].Genre);
         Row.CreateCell(4).SetCellValue(movies[i].Price.ToString());
     }
     hssfworkbook.Write(ms);
     return ms;
 }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>   
        /// <param name="sheetName">工作表名称</param>
        /// <returns>Excel工作表</returns>   
        private static Stream ExportDataSetToExcel(DataSet sourceDs)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();

            for (int i = 0; i < sourceDs.Tables.Count; i++)
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceDs.Tables[i].TableName);
                HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
                // handling header.           
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                // handling value.           
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }

        public override void Write(Stream outStream)
        {
            Workbook workbook = new HSSFWorkbook();

            IEnumerable<User> users = User.SelectAll();
            foreach (User user in users)
            {
                IEnumerable<TimeBlock> timeBlocks = TimeBlock.SelectByUserAndDateRange(user.UserId, FromDate, ToDate);
                if (timeBlocks.Count() > 0)
                {
                    Sheet sheet = workbook.CreateSheet(user.Name);
                    Row header = sheet.CreateRow(0);
                    header.CreateCell(0).SetCellValue("Resource");
                    header.CreateCell(1).SetCellValue("Date");
                    header.CreateCell(2).SetCellValue("Client");
                    header.CreateCell(3).SetCellValue("Work Package");
                    header.CreateCell(4).SetCellValue("Hours");

                    int i = 1;
                    foreach (TimeBlock timeBlock in timeBlocks)
                    {
                        Row row = sheet.CreateRow(i ++);
                        row.CreateCell(0).SetCellValue(timeBlock.User.Name);
                        row.CreateCell(1).SetCellValue(timeBlock.Date.ToString("dd/MM/yy"));
                        row.CreateCell(2).SetCellValue(timeBlock.Job.Client.Name);
                        row.CreateCell(3).SetCellValue(timeBlock.JobId);
                        row.CreateCell(4).SetCellValue(Convert.ToDouble(timeBlock.Time));
                    }
                }
            }

            workbook.Write(outStream);
        }

        static void Main(string[] args)
        {
            //Excel worksheet combine example
            //You will be prompted to select two Excel files. test.xls will be created that combines the sheets
			//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel document (*.xls)|*.xls";
            ofd.Title = "Select first Excel document";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                ofd.Title = "Select second Excel document";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                    HSSFWorkbook product = new HSSFWorkbook();

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
                        sheet1.CopyTo(product, sheet1.SheetName, true, true);
                    }
                    for (int j = 0; j < book2.NumberOfSheets; j++)
                    {
                        HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
                        sheet2.CopyTo(product, sheet2.SheetName, true, true);
                    }
                    product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
                }
                
            }
        }

        private Stream RenderDataSetToExcel(DataSet ds) {
            MemoryStream ms = new MemoryStream();
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet;
            HSSFRow headerRow;

            //for (int k = ds.Tables.Count - 1, len = 0; len <= k; k--) {
            //    DataTable dt = ds.Tables[k];
            foreach(DataTable dt in ds.Tables) {
                sheet = (HSSFSheet)workbook.CreateSheet(dt.TableName);
                headerRow = (HSSFRow)sheet.CreateRow(0);

                foreach (DataColumn column in dt.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

                int rowIndex = 1;
                foreach (DataRow row in dt.Rows) {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in dt.Columns) {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }

                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }

        public static void Main(String[] args)
        {
            WriteJavaDoc();

            HSSFWorkbook wb = new HSSFWorkbook();
            SheetWriter sw = new SheetWriter(wb);

            NumberToTextConversionExamples.ExampleConversion[] exampleValues = NumberToTextConversionExamples.GetExampleConversions();
            for (int i = 0; i < exampleValues.Length; i++)
            {
                TestCases.SS.Util.NumberToTextConversionExamples.ExampleConversion example = exampleValues[i];
                sw.AddTestRow(example.RawDoubleBits, example.ExcelRendering);
            }

            MemoryStream baos = new MemoryStream();
            wb.Write(baos);
            byte[] fileContent = baos.ToArray();
            ReplaceNaNs(fileContent, sw.GetReplacementNaNs());

            FileInfo outputFile = new FileInfo("ExcelNumberRendering.xls");

            FileStream os = File.OpenWrite(outputFile.FullName);
            os.Write(fileContent, 0, fileContent.Length);
            os.Close();
            Console.WriteLine("Finished writing '" + outputFile.FullName + "'");
        }

        public byte[] GetExcelBufferFromDataTable(DataTable dt)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            Sheet sheet = hssfworkbook.CreateSheet("Sheet1");

            sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.GetRow(0).CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                sheet.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    sheet.GetRow(i).CreateCell(j).SetCellValue(dt.Rows[i - 1][dt.Columns[j].ColumnName].ToString());
                }
            }

            MemoryStream memstream = new MemoryStream();
            try
            {
                hssfworkbook.Write(memstream);

                byte[] buffer = memstream.GetBuffer();

                return buffer;
            }
            finally
            {
                memstream.Close();
            }
        }

        public void SaveFile(string path, List<string> text)
        {
            var wb = new HSSFWorkbook();
            var sheet = wb.CreateSheet("Dictionary");

            int rowIndex = 0;
            var row = sheet.CreateRow(rowIndex);
            row.CreateCell(0).SetCellValue("Word");
            row.CreateCell(1).SetCellValue("Count");
            rowIndex++;

            foreach(var str in text)
            {
                var first = str.Split(new char[] {' '}, StringSplitOptions.RemoveEmptyEntries)[0];
                var second = str.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries)[1];

                row = sheet.CreateRow(rowIndex);
                row.CreateCell(0).SetCellValue(first);
                row.CreateCell(1).SetCellValue(second);
                rowIndex++;
            }

            using(var fileStr = new FileStream(path, FileMode.Create))
            {
                wb.Write(fileStr);
            }
        }

 public FileResult Generate(SearchEntity searchEntity)
 {
     int num;
     var arrayData = this.GetArrayData(searchEntity, out num);
     var str = "根基对账单";
     using (var stream = System.IO.File.OpenRead(base.Server.MapPath(string.Format("~/Excel/Market/{0}.xls", str))))
     {
         var xlBook = new HSSFWorkbook(stream);
         this.GenerateM1(arrayData, xlBook);
         this.GenerateM2(arrayData, xlBook);
         this.GenerateM3(arrayData, xlBook);
         if (xlBook.NumberOfSheets > 3)
         {
             xlBook.RemoveSheetAt(0);
             xlBook.RemoveSheetAt(0);
             xlBook.RemoveSheetAt(0);
             xlBook.FirstVisibleTab = 0;
         }
         var str3 = string.Format("{0}{1:yyMMddHHmmss}.xls", str, DateTime.Now);
         var path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
         if (System.IO.File.Exists(path))
         {
             str3 = str3 + Guid.NewGuid().ToString() + ".xls";
             path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
         }
         using (var stream2 = System.IO.File.OpenWrite(path))
         {
             xlBook.Write(stream2);
         }
         return this.File(path, "application/vnd.ms-excel", str3);
     }
 }

 private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
 {
     //Write the stream data of workbook to the root directory
     MemoryStream file = new MemoryStream();
     hssfworkbook.Write(file);
     return file;
 }

        //数据导出为Excel
        private void btnExport_Click(object sender, EventArgs e)
        {
            string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text))
            {
                if (reader.HasRows)
                {

                    IWorkbook wb = new HSSFWorkbook();
                    ISheet sheet = wb.CreateSheet("customers");
                    int rowIndex = 0;//手动处理行索引
                    while (reader.Read())
                    {
                        int autoId = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        string cellPhone = reader.GetString(2);
                        //数据为空,那么就不能Get..,所以要在这里判断
                        string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空

                        DateTime BuyDate = reader.GetDateTime(4);
                        string carNum = reader.GetString(5);
                        string bracketNum = reader.GetString(6);
                        IRow row = sheet.CreateRow(rowIndex);
                        rowIndex++;

                        row.CreateCell(0).SetCellValue(autoId);
                        row.CreateCell(1).SetCellValue(name);
                        row.CreateCell(2).SetCellValue(cellPhone);
                        //如果为空或Null,把Excel这个单元格设置为值为空
                        if (string.IsNullOrEmpty(landLine))
                        {
                            row.CreateCell(3).SetCellType(CellType.BLANK);
                        }
                        else
                        {
                            row.CreateCell(3).SetCellValue(landLine);
                        }

                        //============设置日期格式=======
                        //创建单元格样式对象
                        ICellStyle cellStyle = wb.CreateCellStyle();
                        //设置单元格样式对象属性
                        cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
                        ICell BuyDateRow = row.CreateCell(4);
                        BuyDateRow.CellStyle = cellStyle;
                        BuyDateRow.SetCellValue(BuyDate);
                        //==============设置完成========

                        row.CreateCell(5).SetCellValue(carNum);
                        row.CreateCell(6).SetCellValue(bracketNum);

                    }
                    using (FileStream fs = File.OpenWrite("Customers.xls"))
                    {
                        wb.Write(fs);
                        MessageBox.Show("ok");
                    }
                }
            }
        }

        public static MemoryStream getExcel(DataTable dt, HttpContextBase content)
        {
            var book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                    row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
            }


            //写入到客户端
            var ms = new MemoryStream();
            book.Write(ms);
            book = null;
            ms.Close();
            ms.Dispose();
            return ms;

        }

        static void Main(string[] args)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            //So we insert three sheet just like what Excel does
            hssfworkbook.CreateSheet("Sheet1");
            hssfworkbook.CreateSheet("Sheet2");
            hssfworkbook.CreateSheet("Sheet3");
            hssfworkbook.CreateSheet("Sheet4");

            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }