C# IWorkbook.Write方法代码示例

 public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
 {
     IWorkbook result;
     try
     {
         using (MemoryStream baos = new MemoryStream(8192))
         {
             wb.Write(baos);
             using (Stream is1 = new MemoryStream(baos.ToArray()))
             {
                 if (wb is HSSFWorkbook)
                 {
                     result = new HSSFWorkbook(is1);
                 }
                 else if (wb is XSSFWorkbook)
                 {
                     result = new XSSFWorkbook(is1);
                 }
                 else
                 {
                     throw new RuntimeException("Unexpected workbook type ("
                             + wb.GetType().Name + ")");
                 }
             }
         }
     }
     catch (IOException e)
     {
         throw new RuntimeException(e);
     }
     return result;
 }

        public void AssertDataValidation(IWorkbook wb)
        {

            MemoryStream baos = new MemoryStream(22000);
            try
            {
                wb.Write(baos);
                baos.Close();
            }
            catch (IOException e)
            {
                throw new RuntimeException(e);
            }
            byte[] generatedContent = baos.ToArray();
#if !HIDE_UNREACHABLE_CODE
            bool isSame;
            if (false)
            {
                // TODO - add proof spreadsheet and compare
                Stream proofStream = HSSFTestDataSamples.OpenSampleFileStream("TestDataValidation.xls");
                isSame = CompareStreams(proofStream, generatedContent);
            }
            isSame = true;

            if (isSame)
            {
                return;
            }

            //File tempDir = new File(System.GetProperty("java.io.tmpdir"));
            string tempDir = Path.GetTempFileName();
            //File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls");
            try
            {
                FileStream fileOut = new FileStream(tempDir, FileMode.Create);
                fileOut.Write(generatedContent, 0, generatedContent.Length);
                fileOut.Close();
            }
            catch (IOException e)
            {
                throw new RuntimeException(e);
            }


            Console.WriteLine("This Test case has failed because the generated file differs from proof copy '"
                    ); // TODO+ proofFile.AbsolutePath + "'.");
            Console.WriteLine("The cause is usually a change to this Test, or some common spreadsheet generation code.  "
                    + "The developer has to decide whether the Changes were wanted or unwanted.");
            Console.WriteLine("If the Changes to the generated version were unwanted, "
                    + "make the fix elsewhere (do not modify this Test or the proof spreadsheet to Get the Test working).");
            Console.WriteLine("If the Changes were wanted, make sure to open the newly generated file in Excel "
                    + "and verify it manually.  The new proof file should be submitted After it is verified to be correct.");
            Console.WriteLine("");
            Console.WriteLine("One other possible (but less likely) cause of a failed Test is a problem in the "
                    + "comparison logic used here. Perhaps some extra file regions need to be ignored.");
            Console.WriteLine("The generated file has been saved to '" + tempDir + "' for manual inspection.");

            Assert.Fail("Generated file differs from proof copy.  See sysout comments for details on how to fix.");
#endif
        }

        public static void CreateExcel(string fileName, IWorkbook workbook)
        {
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }

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

 public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
 {
     IWorkbook result;
     try
     {
         using (MemoryStream baos = new MemoryStream(8192))
         {
             Stopwatch sw = new Stopwatch();
             sw.Start();
             wb.Write(baos);
             sw.Stop();
             Debug.WriteLine("XSSFWorkbook write time: " + sw.ElapsedMilliseconds + "ms");
             using (Stream is1 = new MemoryStream(baos.ToArray()))
             {
                 if (wb is HSSFWorkbook)
                 {
                     result = new HSSFWorkbook(is1);
                 }
                 else if (wb is XSSFWorkbook)
                 {
                     Stopwatch sw2 = new Stopwatch();
                     sw2.Start();
                     result = new XSSFWorkbook(is1);
                     sw2.Stop();
                     Debug.WriteLine("XSSFWorkbook parse time: " + sw2.ElapsedMilliseconds + "ms");
                 }
                 else
                 {
                     throw new RuntimeException("Unexpected workbook type ("
                             + wb.GetType().Name + ")");
                 }
             }
         }
     }
     catch (IOException e)
     {
         throw new RuntimeException(e);
     }
     return result;
 }

 public void Save(IWorkbook workbook, string FileName)
 {
     using (FileStream file = new FileStream(FileName, FileMode.Create))
     {
         workbook.Write(file);
         file.Close();
     }
 }

    // <summary>
    /// DataTable转换成Excel文档流
    /// </summary>
    /// <param name="table"></param>
    /// <param name="headRow">设备编号#设备名称#型号</param>
    /// <returns></returns>
    public static MemoryStream RenderToExcel(DataTable table, IWorkbook workbook)
    {
        MemoryStream ms = new MemoryStream();

        using (table)
        {
            //using (IWorkbook workbook = new HSSFWorkbook())
            //{
                using (ISheet sheet = workbook.GetSheetAt(0))
                {
                    //IRow headerRow = sheet.CreateRow(0);
                    //headerRow = irowhead;
                    // handling header.
                    //foreach (DataColumn column in table.Columns)
                    //  headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
                    //if (headRow.Length > 0)
                    //{
                    //    if (headRow.Contains("#"))
                    //    {
                    //        string[] headArr = headRow.Split('#');
                    //        for (int i = 0; i < headArr.Length; i++)
                    //        {

                    //            headerRow.CreateCell(i).SetCellValue(headArr[i]);
                    //        }
                    //    }
                    //    else
                    //    {
                    //        headerRow.CreateCell(0).SetCellValue(headRow);
                    //    }
                    //}
                    //else
                    //{
                    //    foreach (DataColumn column in table.Columns)
                    //        headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
                    //}

                    // handling value.

                    int rowIndex = sheet.PhysicalNumberOfRows;
                    ICellStyle style = workbook.CreateCellStyle();
                    style.Alignment = HorizontalAlignment.CENTER;
                    style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
                    style.BorderTop = NPOI.SS.UserModel.CellBorderType.THIN;
                    style.BorderRight = NPOI.SS.UserModel.CellBorderType.THIN;
                    style.BorderLeft = NPOI.SS.UserModel.CellBorderType.THIN;
                    style.BorderBottom = NPOI.SS.UserModel.CellBorderType.THIN;
                    foreach (DataRow row in table.Rows)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);

                        foreach (DataColumn column in table.Columns)
                        {
                            ICell ic= dataRow.CreateCell(column.Ordinal);
                            ic.CellStyle = style;
                            ic.SetCellValue(row[column].ToString());
                        }

                        rowIndex++;
                    }
                    AutoSizeColumns(sheet,1);

                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                }
            //}
        }
        return ms;
    }

        private void SaveAndReloadReport(IWorkbook wb, FileInfo outFile)
        {
            // run some method on the font to verify if it is "disconnected" already
            //for(short i = 0;i < 256;i++)
            {
                IFont font = wb.GetFontAt((short)0);
                if (font is XSSFFont)
                {
                    XSSFFont xfont = (XSSFFont)wb.GetFontAt((short)0);
                    CT_Font ctFont = (CT_Font)xfont.GetCTFont();
                    Assert.AreEqual(0, ctFont.sizeOfBArray());
                }
            }

            FileStream fileOutStream = new FileStream(outFile.FullName, FileMode.Truncate, FileAccess.ReadWrite);
            wb.Write(fileOutStream);
            fileOutStream.Close();
            //Console.WriteLine("File \""+outFile.GetName()+"\" has been saved successfully");

            Stream is1 = new FileStream(outFile.FullName, FileMode.Truncate, FileAccess.ReadWrite);
            try
            {
                IWorkbook newWB;
                if (wb is XSSFWorkbook)
                {
                    newWB = new XSSFWorkbook(is1);
                }
                else if (wb is HSSFWorkbook)
                {
                    newWB = new HSSFWorkbook(is1);
                    //} else if(wb is SXSSFWorkbook) {
                    //    newWB = new SXSSFWorkbook(new XSSFWorkbook(is1));
                }
                else
                {
                    throw new InvalidOperationException("Unknown workbook: " + wb);
                }
                Assert.IsNotNull(newWB.GetSheet("test"));
            }
            finally
            {
                is1.Close();
            }
        }

        public static void WriteToFile(string folder, String fileName, IWorkbook workbook)
        {
            if (!Directory.Exists(folder))
                Directory.CreateDirectory(folder);

            FileStream file = new FileStream(folder + "/" + fileName, FileMode.Create);
            workbook.Write(file);
            file.Close();
        }

 private static void Save(IWorkbook workbook, string FilePath)
 {
     using (var fs = File.OpenWrite(FilePath))
     {
         workbook.Write(fs);
     }
 }

        /// <summary>
        /// 保存文件
        /// </summary>
        /// <param name="hssfworkbook"></param>
        /// <param name="filename"></param>
        /// <returns>返回文件保存路径</returns>
        private string SaveFile(IWorkbook hssfworkbook, string filename, QueryParameters para)
        {
            try
            {
                string pPath = config;
                if (pPath.Substring(pPath.Length - 1) != "\\")
                    pPath = pPath + "\\";
                var tmpPath = pPath + DateTime.Now.ToString("yyyyMMdd") + "\\" + para.ReportType;
                //创建存储目录
                if (!Directory.Exists(tmpPath))
                {
                    Directory.CreateDirectory(tmpPath);
                }
                //创建文件
                string path = tmpPath + "\\" + filename;
                FileStream files = new FileStream(path, FileMode.Create);
                hssfworkbook.Write(files);
                files.Close();
                //添加数据导出记录
                ContactInfo pContactinfo = new ContactInfo();
                OT_ExportHis contactinfo = new OT_ExportHis();
                contactinfo.Id = Guid.NewGuid();
                contactinfo.DataDate = para.StartTime;
                contactinfo.HDayId = para.HolidayId;
                contactinfo.CalcuTime = DateTime.Now;
                contactinfo.SavePath = path;
                contactinfo.TableType = para.ReportType;
                pContactinfo.Create(contactinfo);

                return filename;
            }
            catch (Exception e)
            {
                SystemLog.GetInstance().Error(e);
            }
            return null;

        }

        public void Execute()
        {
            //set destination
            if (File.Exists(_settings.ApplicationSettings.FileLocations.Destination))
            {
                File.Delete(_settings.ApplicationSettings.FileLocations.Destination);
            }

            //get source files
            if (File.Exists(_settings.ApplicationSettings.FileLocations.Source))
            {
                _sourceFiles.Add(_settings.ApplicationSettings.FileLocations.Source);
            }
            else if (Directory.Exists(_settings.ApplicationSettings.FileLocations.Source))
            {
                GetSourceFiles(_settings.ApplicationSettings.FileLocations.Source);
            }


            _workbook = new XSSFWorkbook();

            //create sheets
            foreach (var sheet in _settings.Sheets)
            {
                if (_workbook.GetSheet(sheet.Name) == null)
                {
                    _workbook.CreateSheet(sheet.Name);
                }
            }

            var dateStyle = _workbook.CreateCellStyle();
            dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");

            //add columns
            foreach (var sheet in _settings.Sheets)
            {
                var wbsheet = _workbook.GetSheet(sheet.Name);
                sheet.ItemCount = 0;
                foreach (var column in sheet.Elements.Columns)
                {
                    ICell cell = wbsheet.GetOrCreateCell(sheet.HeaderRow, column.HeaderCol);
                    cell.SetCellValue(column.Header);
                    cell.CellStyle = dateStyle;
                    if (column.Width != 0)
                    {
                        wbsheet.SetColumnWidth(column.HeaderCol, column.Width);
                    }
                    wbsheet.GetRow(sheet.HeaderRow).RowStyle = dateStyle;
                }
            }

            //run through all documents
            for (int i = 0; i < _sourceFiles.Count; i++)
            {
                Console.Write(string.Format("{0}...", Path.GetFileName(_sourceFiles[i])));
                var srcworkbook = WorkbookFactory.Create(_sourceFiles[i]);
                foreach (var sheet in _settings.Sheets)
                {
                    var wbsheet = _workbook.GetSheet(sheet.Name);
                    var srcwbsheet = srcworkbook.GetSheet(sheet.Name);
                    if (srcwbsheet != null)
                    {
                        //find first row by searching first column header name
                        var allheaders = (from a in sheet.Elements.Columns select a.Header.ToLower()).ToArray();
                        var indexes = new int?[allheaders.Length];
                        var rowenum = srcwbsheet.GetRowEnumerator();
                        while (rowenum.MoveNext())
                        {
                            var r = rowenum.Current as XSSFRow;
                            IRow ActiveRow = null;
                            var searchHeader = (from a in indexes where a != null select a).FirstOrDefault() == null;
                            if (!searchHeader)
                            {
                                ActiveRow = wbsheet.GetOrCreateRow(sheet.ItemCount + sheet.HeaderRow + 1);
                                sheet.ItemCount++;
                                ActiveRow.Height = r.Height;
                            }
                            foreach (var c in r.Cells)
                            {
                                if (searchHeader)
                                {
                                    var index = Array.IndexOf(allheaders, (c.StringCellValue ?? "").ToLower());
                                    if (index >= 0)
                                    {
                                        indexes[index] = c.ColumnIndex;
                                        if (sheet.ItemCount == 0)
                                        {
                                            if (sheet.Elements.Columns[index].Width == 0)
                                            {
                                                wbsheet.SetColumnWidth(sheet.Elements.Columns[index].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[index].HeaderCol)));
                                            }
                                            if (r.RowStyle != null)
                                            {
                                                wbsheet.GetRow(sheet.HeaderRow).RowStyle.CloneStyleFrom(r.RowStyle);
                                            }
                                            wbsheet.GetRow(sheet.HeaderRow).GetCell(sheet.Elements.Columns[index].HeaderCol).CellStyle.CloneStyleFrom(c.CellStyle);
                                        }
                                    }
                                }
                                else
//.........这里部分代码省略.........

        /// <summary>
        /// 获取错误信息Excel
        /// </summary>
        /// <param name="wb">excel对象</param>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public static string GetErrorExcel(IWorkbook wb, string fileName)
        {
            string ext = Path.GetExtension(fileName);
            string name = Path.GetFileNameWithoutExtension(fileName);

            string dirPath = FileHelper.GetAbsolutePath("/TempFile/ErrorExcel");
            if (!Directory.Exists(dirPath))
            {
                Directory.CreateDirectory(dirPath);
            }
            string relativePath = string.Format("/TempFile/ErrorExcel/{0}{1}{2}", name, DateTime.Now.ToString("MMddHHmmss"), ext);
            string path = FileHelper.GetAbsolutePath(relativePath);

            using (FileStream fs = File.OpenWrite(path))
            {
                wb.Write(fs);
            }
            return relativePath;
        }

 private byte[] ToByte(IWorkbook wb)
 {
     using (MemoryStream ms = new MemoryStream())
     {
         //XSSFWorkbook即读取.xlsx文件返回的MemoryStream是关闭
         //但是可以ToArray(),这是NPOI的bug
         wb.Write(ms);
         return ms.ToArray();
     }
 }