svn2github / npoi Goto Github PK

public static void SetIndention(this ICell cell, short indention)
{
var cellStyle = cell.CellStyle ?? cell.Sheet.Workbook.CreateCellStyle();
cellStyle.Indention = indention;
cell.CellStyle = cellStyle;
}
IWorkbook workbook = ...;
ISheet sheet = workbook.GetSheetAt(0);
for(int I = 0; I < 10; I++)
{
var row = sheet.CreateRow(i);
var cell = row.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.SetIndention((short)r.Level);
cell.SetCellValue(r.Cname);
}

Result:

All rows in the first column indentation is same, the last line of the first as a standard
I want is each cell is different

Hi. I"m trying to read in a spreadsheet that includes cells with the formula "=TODAY()" and nothing I do works.

I tried:

XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

and also

var evaluator = new XSSFFormulaEvaluator(workbook); evaluator.EvaluateFormulaCell(cell);

and also

workbookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.Create(workbook), null, null); workbookEvaluator.Evaluate(new XSSFEvaluationCell(cell)).ToString();

It would be also nice to allow developer to set custom tab color by passing rgb to SetTabColor method. Thinking of overloading the method?

When a paragraph is added to a cell the text is also created outside the table as a separate paragraph.

           XWPFDocument document = null;
            var parList = new List<XWPFParagraph>();
            try
            {
                using (FileStream file = new FileStream("C:\\Temp\\TechSpec_Documentation.docx", FileMode.Open, FileAccess.Read))
                {
                    document = new XWPFDocument(file);
                    
                    foreach (var paragraph in document.Paragraphs)
                    {
                        var par = paragraph.Style;
                        if (paragraph.Style == "Heading1")
                        {
                            parList.Add(paragraph);
                        }
                        
                    }
                }
            }
            catch (Exception e)
            {

            } 
            XWPFParagraph p1 = document.CreateParagraph();

            XWPFRun r3 = p1.CreateRun();
            r3.IsBold= true;
            r3.SetText("The quick brown fox");
            r3.IsBold= true;
            r3.SetFontFamily("Courier", FontCharRange.Ascii);
            r3.SetUnderline(UnderlinePatterns.DotDotDash);
            r3.SetTextPosition(100);

            table1.GetRow(0).GetCell(0).SetParagraph(p1);
            var sw = new FileStream("C:\\Temp\\TechSpec_ReleaseDocumentation2.docx", FileMode.Create);


            table1.GetRow(2).GetCell(2).SetText("only text");
            document.Write(sw);
            sw.Close();

Is there something changed as in the example files this the way shown to add a paragraph to a table cell.

使用NPOI版本2.3.0生成的带图片的docx，Word打开时报错。但切换回旧版本2.1.3版本则可以正常打开，测试代码：

        XWPFDocument docx = new XWPFDocument();
        using (FileStream picFile = new FileStream("1.png", FileMode.Open, FileAccess.Read))
        {
            docx.CreateParagraph().CreateRun().AddPicture(picFile, (int)PictureType.PNG, "1", (int)(400.0 * 9525), (int)(300.0 * 9525));
        }

        using (FileStream file = File.Create("Image.docx"))
        {
            docx.Write(file);
        }

经过检查，发现用2.3.0版本生成的docx文件中，word/document.xml有问题：

<wp:docPr name="Drawing 0" descr="1"></wp:docPr>

对比2.1.3版本，2.1.3版本则多了id属性：

<wp:docPr id="1" name="Drawing 1" descr="1"></wp:docPr>

将id="1"添加到2.3.0版本的 word/document.xml ，打包生成docx，用Word打开正确。

这可能是序列化时产生的bug。临时解决方案：

XWPFDocument docx = new XWPFDocument();
XWPFParagraph para = docx.CreateParagraph();
XWPFRun run = para.CreateRun();
run.AddPicture(imageStream, (int)PictureType.PNG, "1.png", width, height);
CT_Inline inline = run.GetCTR().GetDrawingList()[0].inline[0];
inline.docPr.id = 1; //id必须从1开始

也就是强制赋予一个Id值以后，问题即可解决。

这样看，是否可在NPOI的代码里修复？

谢谢！

IN order to obtain the rows's sheet now I must to loop from top to end
Could be better a property or method Rows or getrows to get a list

Hi,

The library is excellent but it consumes too much memory. In my case I'm writing 28k records with 50 columns of data in it. Its take about 1GB of memory to process. Can you please look into this issue.

Thanks

I am using the NPOI .NET libraries to append data returned by a web service to an Excel document. Essentially my program is reading 25,000 records from an Excel document into a C# List, sending the data as input in a request to a web service, and then storing the data being return in an Excel document on 5 different sheets. I am noticing the processing speed and performance of my program is exponentially decreasing as more data is added to my output Excel document. I ran a code analysis on on my application and C# revealed that 30% of the CPU is being devoted to the function below, particularly the lines that reads data from my output document and writes data to it. The function below is called every single time a successful response is returned by the web service. (it is called 5 times per response).

How can rewrite the function below to improve the performance of my application?

``
public static void AppendToExcelFileNPOI(string
filePath, int sheetIndex, List contents)
{
FileStream fileWriter = null;
ISheet sheet = null;

try
{
    if (!File.Exists(filePath))
    {
        CreateExcelOutputFile(filePath);
    }

    FileStream fileReader = new 
    FileStream(filePath, FileMode.Open, 
    FileAccess.Read);

    XSSFWorkbook workbook = new 
    XSSFWorkbook(fileReader); //XSSFWorkBook will write to 2007 Excel format  
    sheet = workbook.GetSheetAt(sheetIndex);  //Get the Excel Sheet specified by the integer stored in sheetIndex

    int indexOfLastRow = sheet.LastRowNum;

    IRow worksheetRow = 
  sheet.CreateRow(indexOfLastRow+1);

    int column = 0;
    foreach(var value in contents)
    {
        ICell cell = 
        worksheetRow.CreateCell(column);
        cell.SetCellValue(value);
        sheet.AutoSizeColumn(column);
        column++;
    }

    fileWriter = new FileStream(filePath, 
    FileMode.Create);
    workbook.Write(fileWriter);

}
catch (IOException)
{
    MessageBox.Show($"The file {filePath} is 
    opened by another program or process." +
                  " Please close the file and 
     restart the batch process.", "Critical 
     Warning",
                  MessageBoxButtons.OK, 
     MessageBoxIcon.Warning);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    if (fileWriter != null)
    {
        fileWriter.Close();
    }

}

}

While evaluating cell with formula as below:
=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

throws exception as below:
Invalid arg type for SUMPRODUCT: (ErrorEval)

Any workaround?

The part using StringBuilder including the "ToString ()" method is hard to read and I'm fixing it.
Is it okay to send a pull request if it's in the process of fixing it?

Hi guys,

is it possible to add XSSF specific properties to XSSFPatternFormatting class? I mean properties using classes XSSFColor and FillPattern enum? Or is there anyway I can set custom color in XSSFConditionalFormattingRule for some XSSFConditionalFormattingRule with current implementation?

Thanks,
Miro

NPOI 我正在学习和使用它，不得不说 NPOI 是一个非常优秀的 Excel 组件。可是最近我遇到了一些困扰，我尝试通过百度、必应等查找资料始终没能解决 XSSFWorkbook 当遇到60万数据的时候内存溢出问题。

我的电脑系统
Win7 32位 4GB 内存。

Write 的时候内存开始猛增。可否考虑按行写入数据，我想这样的话 数据写入压力是否会小很多？

Following is the problem:
I used version NPOI 2.1.1 with c# （C sharp）.
I uploaded an excel with Microsoft excel 2003 on client. I opened the create custom settings with NPOI on the server and use “write” of hssfworkbook into filestream. However, an error occurred- buffer overrun i=xx;endindex=xx;writeindex=xxx. ( Excel was 10 columns 20 rows , which only added a filter function.)

So basically these are my steps

1. Create an xlsx with excel online, nothing to fancy, just 2 columns, I added some data on those columns.
2. Download this xlsx from oneDrive.
3. Open with NPOI this excel, try to read data, success on this step.
4. Try to update an existing value with another value.
5. Try to read the cell I just updated, I can get updated value properly.
6. Save the excel.
7. Open excel again, try to read updated value, I get lattest value I updated properly.
8. Upload this file to OneDrive and try to open it. ERROR: It says something like "Maybe file is corrupted, would you like to open it on excel?"
9. Try to open it on a Desktop excel, it says same thing, file is corrupt.
10. Strange thing is that NPOI can still read this 'Corrupt' file without problems...

Please find attached, corrupted excel on this example.
ExcelCorruptedFile.xlsx

FYI, same code but using XLS class (no XLSX) works fine, I create a IWorkbook object that decides if should be XLSX or XLS handling, exactly same code for XLS works perfect.

regards.
Leonardo

The part using StringBuilder including the "ToString ()" method is hard to read and I'm fixing it.
Is it okay to send a pull request if it's in the process of fixing it?

Hi, there is a bug that has been fixed few years ago for POI, but that still exists for NPOI, the bug is described there :

https://bz.apache.org/bugzilla/show_bug.cgi?id=51158

"When using the write() method of a XSSFWorkbook multiple times, the content of the created Excel files is not readable by Excel. The reason for this problem is that the content of some files (that are contained in the .xlsx zip archive) is appended to the old content of those files."

For example you may want to see the actual workbook in Excel, while the workbook is not completed : so you have to write the workbook this time, and another time when the workbook is finished.

你好，使用NPOI最新的版本，执行 InsertTable(int pos, XWPFTable table)函数后，bodyElements和tables中都表现正常，但查看xmldocument中相关的node总数没有变化 ，最后输出到文件中时，新增的表格存在，但插入的表格不存在，请帮忙分析下原因。谢谢

OPCPackage pack = POIXMLDocument.OpenPackage(fileName);
XWPFDocument doc = new XWPFDocument(pack);

var newTable = doc.CreateTable();
XWPFTable t = new XWPFTable(doc.Document.body.GetTblArray(0), doc);
doc.InsertTable(0, t);
doc.InsertTable(0, newTable);
XmlDocument xmldoc = POIXMLDocumentPart.ConvertStreamToXml(doc.Part.GetPackagePart().GetInputStream()); //不支持读取输出流

System.IO.MemoryStream stream = new MemoryStream();
doc.Write(stream);
stream.Flush();
doc.Close();

是否已经支持.net core 2.0 或者计划支持.net core 2.0?

带有换行的字符串，在使用NPOI添加到run中时，显示并不能换行。请问除了对字符串预处理，还有什么好的办法吗？

When we use XSSF writes to memoryStream, memoryStream will be closed. So we can NOT download xlsx file from web.

is any plan？

RemoveRow 只能清空行内容，ShiftRows 移动也不行，总行数还没有变，有何种方法能删除行，行数也会相应减少的

Hi,

With a HSSFSheet I can use the CopyTo() function to copy a sheet to another workbook. This method does not exist on XSSFSheet. Is there another way to do this with XSSFSheet or is copying sheets impossible on .xlsx-files?

随便调用比如CopyRow(0,1)，会显示错误
'Invalid row number (1048576) outside allowable range (0..1048575)'

Hello,

Is npoi compatible with office 365 files?

Regards

Julien

when set PaperSize to PrintSetup and I print by Excel , the pagesize Selected is A3,and I selected A3 the Pagesize Selected is the other one. What I used Is Microsoft Office 2013

hello,
I am currently using NPOI in my project and I encounter this very weird situation when try to write IWorkbook to file. The write function seems buggy to me. When you do:

var workbook =  WorkbookFactory.Create("template.xlsx"); //open an existing excel file
 //do editing with the workbook
workbook.write(new FileStream("test.xlsx", FileMode.Create))
workbook.close()

Both original file and the new file will be updated. However if you comment out the write() call, nothing would happen. So my question is how am I supposed to update an existing file or save changes to another file only(keeping original file intact)?

many thanks in advance!

在word里面如何导出复选框，类似这样的效果

提示 Unable to construct record instance

I was updating the nugets packege on my Visual Studio
While I noticed that the NPOI was not working,
So I took a while untill notice that it wasn't compatible with the SharpZipLib 1.0.0
as it is the only one dependence on the NPOI project.
The issues is that the Stream Cannot be generated and is always returning 0 Bytes
I am using C# on Asp.net and WPF.
I was only able to get it working after downgrade the package to 0.86.0
But I hope it is not the solution as the package may be update as soon as other requeried dependence needs it.

How to set the default view option to (Page Break Preview) using code, so the user can see the rows in the (Page Break Preview) mode if he downloaded the generated Excel file from my App ?

Hi, is dot.net 4.7 supported?
thanks

Hi, this is to let you know this repository will no longer be updated to match the original SVN repository on January 20th. The svn2github service is closing down on January 27th. To read the detailed blog post, go to: http://piotr.gabryjeluk.pl/blog:closing-svn2github

Hi
Are you going to make DOC format support?

                    var headerFont = workbook.CreateFont() as XSSFFont;
                    headerFont.IsBold = true;
                    headerFont.FontHeight = 11;
                    headerFont.SetColor(new XSSFColor(Color.White));
                    var headerStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                    headerStyle.SetFont(headerFont);
                    headerStyle.FillPattern = FillPattern.SolidForeground;
                    headerStyle.SetFillForegroundColor(new XSSFColor(Color.Black));

this code make XLSX with xml error
How i can to make cell with black background and bolded text with white color? I wont to use this as "cell header".

I can't use InsertNewParagraph (XMLDocument cursor)