I am getting an error when trying to open a file ([https://www.dropbox.com/s/lzig35pofzq5k86/example.xls?dl=0]). The file worked before I updated to .net framework 4.7.2 from 4.6, same NPOI framework. I am getting an exception on reading documentSummaryInformation or writing a workbook to file:

NPOIFSFileSystem fs = new NPOIFSFileSystem(new System.IO.FileInfo(PlateTemplateFilePath), true);
HSSFWorkbook wb = new HSSFWorkbook(fs.Root, true);
var x = wb.DocumentSummaryInformation; // Throws exception
FileStream outstream = new FileStream(OutputXlsFilePath, FileMode.Create, FileAccess.Write, FileShare.None);
wb.Write(outstream); //throws exception

NPOI.HPSF.ReadingNotSupportedException
HResult=0x80131500
Message=HPSF does not yet support the variant type 4126 (unknown variant type, 000000000000101E). If you want support for this variant type in one of the next POI releases please submit a request for enhancement (RFE) To http://issues.apache.org/bugzilla/! Thank you!
Source=NPOI
StackTrace:
at NPOI.HPSF.VariantSupport.Read(Byte[] src, Int32 offset, Int32 length, Int64 type, Int32 codepage)

使用Isheet CreatRow操作xls新建一行，期望是插入一行，但是实际是直接覆盖了一行（即直接覆盖了原来的数据行）

版本: 2.4.0
操作xlsx格式的表格。往已存在的excel中写入值，完成后打开excel，提示文件内容有问题，修复后损坏。


我的代码如下:

public static void Write2SourceTable()
{
    string fileName = Environment.CurrentDirectory + @"\Files\SourceFiles\Chinese\" + "bh_帮会传功表.xlsx";
    
    XSSFWorkbook hssfwb;
    using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        hssfwb = new XSSFWorkbook(file); 
        file.Close();
    }

    ISheet sheet = hssfwb.GetSheet("策划备注");
    sheet.GetRow(0).GetCell(0).SetCellValue("aaaaaaaaaaaaaaaaaaaaaaaaaa");
    
    using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
    {
        hssfwb.Write(file);
        file.Close();
    }
}

操作xls时正常。
使用的表格如下:
bh_帮会传功表.xlsx

如果考虑的是有人还在用 vs2017- , 我觉得完全没这个必要， 因为完全可以用 vscode + C# 扩展来开发，而且也仅限于开发，使用 nuget 包不受影响

Please, can you signing your libraries?

I was trying to open a Word (docx) file using NPOI and I got this exception:

System.ArgumentException: 'Requested value 'false' was not found.'

As I dug deeper, I found that the problem is about the ST_OnOff enum. Here is a snippet of my document:

<w:latentStyles w:count="371" w:defQFormat="false" w:defUnhideWhenUsed="false" w:defSemiHidden="false" w:defUIPriority="99" w:defLockedState="false">

The enum is written so the "false" values above cannot be converted because it does not match exactly with the enum. Your code does not use the ignoreCase flag to parse the value:

   if (node.Attributes["w:defLockedState"] != null)
                ctObj.defLockedState = (ST_OnOff)Enum.Parse(typeof(ST_OnOff), node.Attributes["w:defLockedState"].Value);

If I change my document like this below it works:

<w:latentStyles w:count="371" w:defQFormat="False" w:defUnhideWhenUsed="False" w:defSemiHidden="False" w:defUIPriority="99" w:defLockedState="False">

Of course, if I change NPOI code it will also work. My question is: is this an intendend behavior? Should I change my documents?

用npoi2.4导出Excel，原Excel中包含条件格式，条件格式填充颜色为黄色，导出的Excel中条件格式没问题，但是填充颜色虽然已经选择了黄色，但是却没有显示。

word表单元格cell赋值为空时生成的docx用office word2007打不开,但wpf可以正常打开。
报错为“无法打开office Open XML 文件 xxxxx.docx,因为内容有误”
targetCell.Paragraphs[0].Style = null

How can I merge cells in word?

private short lastDateFormat = short.MinValue; private List<FormatRecord> lastFormats = null; private String getDataFormatStringCache = null; 在 HSSFCellStyle.cs文件104行，导致多线程下同时读取cell值报错

请问NPOI有对EXCEL电子签名,电子证书的支持计划吗?

I'm using NPOI.SS.UserModel.IChart to insert chart into NPOI.SS.UserModel.ISheet,need help,please.
1.can't set Title to chart,without function like SetTitle ,attribute Title(XSSFChart) is readonly as well.
2.how to display GridLine with code,can anyone show me please.
3.can't set DispBlanksAs,like gap ,then it display as zero,which I change IChart to XSSFChart to make it happen.

   public void DispBlanksAsGap(IChart xChart)
    {
        XSSFChart tempXSSFChart = xChart as XSSFChart;
        NPOI.OpenXmlFormats.Dml.Chart.CT_DispBlanksAs ctDispBlanksAsGap = new NPOI.OpenXmlFormats.Dml.Chart.CT_DispBlanksAs();
        ctDispBlanksAsGap.val = NPOI.OpenXmlFormats.Dml.Chart.ST_DispBlanksAs.gap;
        tempXSSFChart.SetCTDispBlanksAs(ctDispBlanksAsGap);
    }

When I merge cells while creating EXCEL I always get error when I try to open file I get error from the picture.

When I click on Yes button, a file can be opened. Only last sheet remains without cells merging.

But when I create a file without cells merging, I don't get any error when I open it.

Here is my code for cells merging:

for (int s = 0; s < 40; s++)
{
    excelSheets[i].AutoSizeColumn(s);
    if (i >= 4 && i <= 8) 
    continue;
    var merge = new CellRangeAddress(0, 1, s, s);
    excelSheets[i].AddMergedRegion(merge);
}

SharpZipLib 已经发布了 1.0 正式版了，要求 .net >= 4.5

我的项目，目前已经更新到了 net4.5.2，但是，我只能使用 NOPI 2.3

不能使用新版的NOPI 2.4 ，是因为 2.4 要求必须版本为 SharpZipLib 0.86

所以，我不知道这是BUG，还是未支持。目前项目使用： NOPI 2.3 + SharpZipLib 1.0

Clickonce dependency NPOI.OPENXML4NET MISSING

Thank you for this good job. I used it in a project and i appreciate it.

I tried to use NPOI in VS2017, with a 4.5 framework. Installing the package works fine (NuGet package utility in VS), compilation is done successfully, but when i ran my application returned an error when code try to access to excel file: "Impossible de charger le fichier ou l'assembly 'NPOI.OOXML, Version=2.4.0.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1' ou une de ses dépendances. Le fichier spécifié est introuvable." Translated: Could not load file or assembly 'NPOI.OOXML, Version=2.4.0.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1' or one of its dependencies. The specified file is not found.

Today, i created a new C# project and installed 2.4.1 package with NuGet.
I still have the same error : "Impossible de charger le fichier ou l'assembly 'NPOI.OOXML, Version=2.4.1.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1' ou une de ses dépendances. Le fichier spécifié est introuvable."
I don't understand why. NPOI.OOXML is displayed in references and the file exists.

Best regards,
Olivier

CT_Positive2D simplePos = new CT_Positive2D();

这个 提示 缺少命名空间 ？

r2.AddPicture(gfs, (int)NPOI.XWPF.UserModel.PictureType.JPEG, "2.jpg", 1000000, 100000);
参数3 2.jpg 什么意思？有什么用？有什么效果？

请用中文问答

当插入行时，报错“Could not find 'internal references' EXTERNALBOOK''”。
经查，报表内完全没有LinkTable，该错误出现原因未知。
在网页中也未找到InternalWorkbook与IWorkbook有何关系。
附件“轧钢棒线电气.xls”为我的操作对象，操作的工作表为“棒线”,操作行为是“插入行”。
注，多数时候，如果将xls升级为xlsx，再另存外xls，同样的操作不会报错。

轧钢棒线电气.zip

部分简化源码
XSSFFont : IFont {
double FontHeight {
get {
if (ct_FontSize != null)
return (double)(short)(ct_FontSize.val * 20.0);
return DEFAULT_FONT_SIZE * 20.0;
}
set {
ct_FontSize.val = value; // BUG: 赋值没有除于 20;
}
}
short FontHeightInPoints {
get {
return (short)(this.FontHeight / 20.0);
}
set {
ct_FontSize.val = value;
}
}
}
FontHeight 属性的 set 部分 没有将 value 除于 20 再保存..
此 BUG 在 NPOIv2.2.1.0 已存在至v2.4, 更早的版本没接触...
HSSFFont 没有问题...
微软关于 CT_FontSize 的定义, val 的单位是 Point.
https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.fontsize.aspx

一点建议:
FontHeight/FontHeightInPoints 的数据类型设计很不合理...类型设置相反了.
FontHeight 使用 short, 单位为 1/20 Point, 单位很小了, 没必要有小数部分.
FontHeightInPoints 使用 double 或者 float, 单位为 Point, 需要小数部分;
Excel 软件是支持小数字号的, 例如 10.5 磅...
目前 NPOI 只能设置整数字号, 即使设置小数, 也会在内部类型转换后损失小数部分...

I'm trying to create an Excel file (xlsx) with conditional formatting on some columns, such as icon sets and data bars for percentages. Is it possible to achieve this? It seems supported by poi but not mapped on npoi.
Thanks

安装引用时提示JQuery版本不兼容，项目用的JQuery版本为3.3.1，提示最高只支持到2.0.0
除了变更JQuery版本以外有什么办法吗

npoi-master\main\NPOI.csproj(93)

False
..\solution\lib\ICSharpCode.SharpZipLib.dll

npoi-master\main\SS\Formula\Functions\Averageifs.cs(139)
if( valuesCount > 0 ) return (sum / valuesCount); else throw new EvaluationException(ErrorEval.VALUE_INVALID);

npoi-master\main\SS\Formula\Functions\Countif.cs(427)
if (_operator.Code == CmpOp.NE) return true; //is -> ==

npoi-master\ooxml\XWPF\Usermodel\XWPFSharedRun.cs(24,50-58,154)
public bool IsBold { get{ throw new NotImplementedException();} set{ throw new NotImplementedException();} }// public bool IsBold { get{ throw new NotImplementedException();} set{ throw new NotImplementedException();} }

Source： cloned zip version

Hi @tonyqus , @antony-liu , @jstedfast
I have installed the NPOI 2.4.0 NuGet package in to my project to read and write the excel (xlsx) files.
I am first reading an excel file and evaluating the formulas. I am then writing that workbook to a different location.
I am using XSSFWorkBook.Write(FileStream) function for that.
But I am getting the following error message:

System.IO.FileNotFoundException
HResult=0x80070002
Message=Could not load file or assembly 'NPOI.OpenXmlFormats.XmlSerializers, Version=2.4.0.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1' or one of its dependencies. The system cannot find the file specified.
Source=mscorlib
StackTrace:
at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

The code generates a corrupted excel file.
Note: The NuGet package has installed all the required dlls and dependencies.

Could you please advise what am I missing?

Thanks.
-Anu

Hello.

I noticed that the lib don't works very well for big sheets.

Probably because some compatibility concerns and code reuse, limits has been defined to evaluation of areas.

Using '=SUM(A:A)' as example, if we try to do a formula evaluation with this we get the right value only for the 65536 first rows.

This is a limitation of Excel before 2007 for area references, but its working like this for all versions.

I have commited a solution to solve this on external sheets references (since this is a limitation for 2007+), but cant turn to a pull request yet as is just a quick fix.

Maybe is just something to keep on radar.

Thanks.

读取了有背景图的word。再保存后打不开！去掉背景图后正常
下载模板文件

I need to have a few columns in my xlsx output which need to have WrapText set. For the other columns I am using AutoSizeColumns. In the output I am finding that the cells which need WrapText do have the property set however they are not wrapping.

I am not setting a row height at any point and I have tried setting the row height to -1 , as a stackoverflow posting suggested this would work.

When I remove the call to AutoSizeColumns the wrapping works and row heights are appropriate but columns widths for those we want autosized are not correct.

There does seem to be something with AutoSizeColumn and the row height being over-ridden?

Could you advise us on how to resolve this situation.
Thanks.

The supplied spreadsheet seems to be an Encrypted .xlsx file. It must be decrypted before use by XSSF, it cannot be used by HSSF

workbook = WorkbookFactory.Create(fs);

dll version 2.4.1.0

Hi,
I'm allready using SharpZipLib version 1.0 in my project and can't use package version 2.3 due to a bug fixed in 2.4. Is there any reason to target specific version 0.86 of SharpZibLib?
Can you help me?
Thanks

FileStream gfs = null;
gfs = new FileStream(@"d:\C#\图像\鸟.jpg", FileMode.Open, FileAccess.Read);
XWPFDocument mydoc = new XWPFDocument();
CT_P p1 = mydoc.Document.body.AddNewP(); // 向文档中添加段落
CT_P p2 = mydoc.Document.body.AddNewP();
CT_P p3 = mydoc.Document.body.AddNewP();

        p1.AddNewPPr().AddNewJc().val = ST_Jc.center;//段落水平居中,
        p2.AddNewPPr().AddNewJc().val = ST_Jc.center;//段落水平居中

        XWPFParagraph gp = new XWPFParagraph(p2, mydoc);//创建段落对象,使段落指向mydoc文档的p2段落
        XWPFRun gr = gp.CreateRun();//创建内容块
        gr.AddPicture(gfs, (int)NPOI.XWPF.UserModel.PictureType.JPEG, "鸟.jpg", 1000000, 1000000);//插入图片
        gfs.Close();



        //保存文件
        FileStream sw = File.Create(@"d:\ss\cutput.docx"); //创建覆盖文件
        mydoc.Write(sw);
        sw.Close();

插入图片后 word不能打开

I'm trying to create multiple shapes on my sheet, but they seem to share the same properties (the last set).

XSSFClientAnchor a = new XSSFClientAnchor(100000, 20000, 450000, 240000, 3, 8, 3, 8);
XSSFSimpleShape shape1 = pat.CreateSimpleShape(a);
shape1.SetFillColor(100, 100, 255);
shape1.SetLineStyleColor(0, 0, 0);
shape1.SetText("A");
shape1.LineWidth = 1;
// ...
XSSFClientAnchor a2 = new XSSFClientAnchor(100000, 20000, 450000, 240000, 5, 8, 5, 8);
XSSFSimpleShape shape2 = pat2.CreateSimpleShape(a2);
shape2.SetFillColor(255, 100, 100);
shape2.SetLineStyleColor(0, 0, 0);
shape2.SetText("B");
shape2.LineWidth = 1;

Result:

Both shapes were set with the text "B" and the color red, although the text for the first one clearly should be "A" and its color should be blue.

I've tried using the same Drawing Patriarch for both shapes and I've tried using separated Drawing Patriarchs for each shape, but I get the same result in both cases.

This can occur when writing multiple files in async tasks. I believe this is due to the use of the Random class and, could be solved by using Guids.

System.IO.IOException

The file 'C:\Users...\AppData\Local\Temp\poifiles\OpenXml4Net636821901000972894510840101.tmp' already exists.

System.IO.IOException: The file 'C:\Users...\AppData\Local\Temp\poifiles\OpenXml4Net636821901000972894510840101.tmp' already exists.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)
at NPOI.Util.TempFile.CreateTempFile(String prefix, String suffix)

Not an issue but a usecase question:
Cant figure out how to

• get page number and page total (e.g. page 3 of 10).

• add Contence of document with page numbers.

Thanks in advance!

doc.Write(outFile);
{System.NullReferenceException: Object reference not set to an instance of an object.
at NPOI.OpenXml4Net.Util.XmlHelper.EncodeXml(String xml)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Text.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_R.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_P.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Tc.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Row.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Tbl.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Body.Write(StreamWriter sw, String nodeName)
at NPOI.OpenXmlFormats.Wordprocessing.CT_Document.Write(StreamWriter sw)
at NPOI.OpenXmlFormats.Wordprocessing.DocumentDocument.Save(Stream stream)
at NPOI.XWPF.UserModel.XWPFDocument.Commit()
at NPOI.POIXMLDocumentPart.OnSave(List`1 alreadySaved)
at NPOI.POIXMLDocument.Write(Stream stream)

excel的单个sheet 能转为html table 表格吗？按照sheet 单独转

I am using NPOI to generate a word document. When I try to create a hyperlink content (with external URL), there are not enough documentations available on how to do it. I referred some documentation from POI. But didn't find the equivalent methods in NPOI.

Is hyperlink supported in NPOI with word document? If so, can someone please post some example code ?

如图以上3项都丢失。

HSSFSheet CPS = sheet as HSSFSheet;
CPS.CopyTo(workbookParent, sheetName, true, false);

CopyTo后打印区域以外的其他单元格还在（未复制打印区域的设置，和未复制打印标题的设置）。
CopySheet也存在问题。如何解决？

麻烦作者看到尽快回复下，万分感谢！！!

注：顺便求一个把多个sheet不同样式，放进一个工作表的简单方法，现在我的做法是循环创建工作表，然后拷贝在其他工作表

// byteArr 是Sheet样式
HSSFWorkbook workbookParent = new HSSFWorkbook();
foreach (var byteArr in list)
{
      HSSFWorkbook workbook = new HSSFWorkbook(new MemoryStream(byteArr));
      var sheet = workbook.GetSheetAt(0);
      HSSFSheet CPS = sheet as HSSFSheet;
      CPS.CopyTo(workbookParent, sheetName, true, true);
}

When generating a file with version 2.4.1, Excel now brings up this message (in french)

English message = same as https://github.com/tonyqus/npoi/issues/158

		private ICellStyle GetHeaderStyle()
		{
			var headerFont = Workbook.CreateFont();
			headerFont.Boldweight = (short)FontBoldWeight.Bold;
			var headerRowStyle = Workbook.CreateCellStyle();
			headerRowStyle.BorderBottom = BorderStyle.Thin;
			headerRowStyle.SetFont(headerFont);
			return headerRowStyle;
		}

If I use this style for my header cells, Excel bugs and all headers are repaired with font of size 1.
It was not the case with version 2.3.0.

I can solve the problem by adding this line :

headerFont.FontHeightInPoints = 11;

Suggestion:
Deprecate CreateFont() method and add a new method that requires a font size parameter.

现有一个项目，需要读取excel中checkbox，其中office2003的xls格式可以读出，但是office2007以上的xlsx格式的不知道怎么读出，例子中都也没有找到有关checkbox的？？

XWPFTable table = m_Docx.CreateTable(1, 1);//创建1行1列表
CT_Tbl m_CTTbl = m_Docx.Document.body.GetTblArray()[0];//获得文档第一张表

这是在文档的最后追加一张表和一个段落，如何在中间段落插入表格？？

We Waiting Long Time... PLZ

Hello,

In XSSF workbook chart title is added, but not visible. Please, fix this bug.

代码
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.File);
link.Address = "2018-12-24\10997\4526951\0.jpg";

编译正常

运行错误
System.ArgumentException
"Address of hyperlink must be a valid URI:2018-12-24\10997\4526951\0.jpg"
NPOI.XSSF.UserModel.XSSFHyperlink.Validate(String address)
NPOI.XSSF.UserModel.XSSFHyperlink.Address

我是想指向本地文件,不是远程地址,所以Address 不是URI

DateCellValue='
((NPOI.XSSF.UserModel.XSSFCell)new System.Collections.Generic.Mscorlib_CollectionDebugView<NPOI.SS.UserModel.ICell>(((NPOI.XSSF.UserModel.XSSFRow)row).Cells).Items[0]).DateCellValue' threw an exception of type 'System.NullReferenceException'
NumbreCellvalue=43289.6486111111
当读取 日期类型的时候 `。无法读取。

EXCEL 文件是日期。2018/7/9 8:55:00

`

If anyone can guide me that how can we access password protected file with NPOI in c# then it would really very grateful for me.

Thank You

Hi,
I'm trying to add a picture into the footer of a word document, using the method AddPicture of Run object.
The word document is generated, but open the document the image is not available.

Using the same method with a Paragraph and a Run contained into the body of the document it work well.

How can I fix this problem?

Thanks

Excel file download URL：https://drive.google.com/open?id=1v-FvzOJ2hgoT5WSGuuGXxnhYd7Ohas2R

Hi,
How can I replace a picture in an XLS file?
I need to keep the new picture in the same position, width, heigth of the old one... and only replace picturedata, but PictureData seem's to be readonly.
Is there a solution???
I attached an XLS file for example with the picture I need to replace.
FOA CH 2(LUMID060) 03-06-2018 18h00m41s.zip

Resolved with 1.0