nissl-lab / npoi-examples Goto Github PK

Hi,
I;m trying to RemoveRow using

 sheet.RemoveRow(row);   // this only deletes all the cell values

        int rowIndex = row.RowNum;

        int lastRowNum = sheet.LastRowNum;

        if (rowIndex >= 0 && rowIndex < lastRowNum)
        {
            sheet.ShiftRows(rowIndex + 1, lastRowNum, -1);
        }

Above solution causes some issues:

• Index out of range on some cells with values
• Excel file after opening have pre-selected last row and column(Despite it's empty)

How can i remove rows without complications

Hi ~ I need to use npoi to create a line chart in Excel. Which example should I refer to?

nissl-lab/npoi#637

I found this pretty limiting.

Hi. I have Excel sheet like this

A         B         C
1         2          =A1*B1
2         2          =A2*B2
3         2          =A2*B3

After i open this file in NPOI 2.5.2 and try to replace formula with just text (for example in C2 cell) i save it. When i open it with MS Excel i got the following error "Removed Records: Shared formula from /xl/worksheets/sheet1.xml"
"Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)".

Here my code

void Test02()
        {
            using (System.IO.FileStream fsIn = new System.IO.FileStream("SourceExcel.xlsx", System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                // Open simple sheet
                NPOI.XSSF.UserModel.XSSFWorkbook xBookIn = new NPOI.XSSF.UserModel.XSSFWorkbook(fsIn);
                var xSheetIn = xBookIn.GetSheetAt(0);

                xSheetIn.GetRow(2).CreateCell(2); xSheetIn.GetRow(2).GetCell(2).SetCellValue("justNewCell");


                var fsOut = new System.IO.FileStream("ResultedExcel.xlsx", System.IO.FileMode.Create);
                xBookIn.Write(fsOut, false);
            }
        }

I am also attaching source and destination file (with error). Thanks.
ResultedExcel.xlsx
SourceExcel.xlsx

How to read unformatted cell value, for example cell value: 0.013856 but NPOI returns 0.01 (cell format "#,##0.00").
From example: npoi-examples/ss/ReadAndPrintData

Many of the XWPF examples show calling run.SetText() multiple times on the same run. From these examples, I would expect this usage to append text to the run. But, calling SetText() actually replaces the text in the run. I believe the examples should use run.AppendText() on the second and successive calls.

Hi everyone,

using @tonyqus example (xssf/CreateTableInXlsx/Program.cs) changing it to create two sheets with two tables i am getting the following excel repair message complaining about the tables/parts:

Tried @tonyqus example without changing it produces no errors.
As soon as i add the second sheet with the second table it has to be repaired.

Thanks for any advice on this.
Best regards,
Peter

Minimal changes to the example:

using NPOI.XSSF.UserModel;

namespace npoiTest
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (var workbook = new XSSFWorkbook())
            {
                CreateSheetWithTable(workbook, 1);
                CreateSheetWithTable(workbook, 2);

                using (FileStream sw = File.Create("test.xlsx"))
                {
                    workbook.Write(sw, false);
                }
            }
        }

        private static void CreateSheetWithTable(XSSFWorkbook workbook, uint i)
        {
            var sheet = (XSSFSheet)workbook.CreateSheet("Sheet" + i);
            //create the table in sheet
            var table = sheet.CreateTable();
            table.Name = "Test" + i;
            var ctTable = table.GetCTTable();
            ctTable.id = 1;
            table.IsHasTotalsRow = false;
            table.DisplayName = "Table" + i;
            table.SetCellReferences(new NPOI.SS.Util.AreaReference("A1:C5", NPOI.SS.SpreadsheetVersion.EXCEL2007));

            //CreateColumn method is available since NPOI 2.6.0
            table.CreateColumn(null, 0);
            table.CreateColumn(null, 1);
            table.CreateColumn(null, 2);
            table.StyleName = XSSFBuiltinTableStyleEnum.TableStyleMedium27.ToString();


            table.Style.IsShowColumnStripes = false;
            table.Style.IsShowRowStripes = true;

            //fill in the data
            for (int r = 0; r < 5; r++)
            {
                var row = sheet.CreateRow(r);
                for (int c = 0; c < 3; c++)
                {
                    var cell = row.CreateCell(c);
                    if (r == 0)
                    { //first row is for column headers
                        cell.SetCellValue("Column" + (c + 1)); //content **must** be here for table column names
                    }
                    else
                    {
                        cell.SetCellValue($"R{r + 1}C{c + 1}");
                    }
                }
            }
        }
    }
}

Hi i would like to know how i can set the picture layout type after adding it with addPicture().
Thanks.

I have code to Generate Excel File where with my data on excel sheet , it creates Column Chart using Npoi package. In that I have 4 columns in my column chart where all columns color is blue by default. I want to set custom colors for each Column in Column chart.

Chart generated
Chart Wanted

Why is there a method to delete removenodyelement. There is no way to add bodyelement？

ca_apparel_browse_tree_guide.TTH.xls (download from amazon)

This poi bug was first discovered in 2012,but there are still problems with the repair method.As a result, the hyperlink cannot be read in the sheet.

Hi
I've got such a case: while opening a .docx file I want to get all information from every cell in table inside the document. But I can't use foreach method with Rows to check every cell in a row form table as XWPFTableRaw doesn't have GetEnumerator() method.

An example is:

using (var fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open))
{
    XWPFDocument document = new XWPFDocument(fs);

    var tables = document.Tables;

    foreach (var table in tables)
    {
       for (int i = 0;  i < table.Rows.Count; i++)
        {
            var row = table.Rows[i];
            foreach (var cell in row)
            {
                //an error occures as there is no GetEnumerator() method in XWPFTableRaw 
            }
          //  ...
        }
    }
}

So is it possible to implement a feature for using loops while working with rows ?

@tonyqus I think your examples are excellent. I noticed you even put character in them that require a large character set interpretation (not just ASCII).

I think your examples would be more powerful if you added a line like this (especially the ReadAndPrintData example):

            Console.OutputEncoding = System.Text.Encoding.UTF8;

Does anyone know a good way to get pictures or tables placed in a document similarly to how ReplaceText works?

In the provided case of ChangeOrientation, it seems that only the entire page has been changed

Hi,
Is any one able to provide an example on how to do the following chart:

• Scatter Chart with 1 Series. Dots only
• Display X and Y Axis Titles (Something X Axis Title, Something Y Axis Title)
• Add Trendline and display Equation and R value.

Generating a workbook with data a series containing decimal values and a line chart and saving it to xlsx, under the Italian CultureInfo, NPOI save in xl\charts\Chart1.xml all double values with comma instead of dot as decimal separator.
Opening the generated file with excel give an error and the option to recover the workbook, then it will fix the number format and the file finally opens.

I tried exporting to xlsx the same file but setting in my application the CurrentCulture to InvariantCulture and NPOI write the file in the correct way.

After generating and saving de the XWPF file we can't rotate the picture.
Only the frame can be rotated.

nissl-lab/npoi#238

based on NPOI 2.5.5 and above

Could not resolve this reference. Could not locate the assembly "NPOI, Version=2.5.2.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. UseBasicFormula

I have tried several projects.

Severity Code Description Project File Line Suppression State
Error NuGet Package restore failed for project Naos.FileStorage.Rest: Unable to find version '1.0.20.6742' of package 'Architecture.IO'.
C:\Program Files (x86)\Microsoft SDKs\NuGetPackages: Package 'Architecture.IO.1.0.20.6742' is not found on source 'C:\Program Files (x86)\Microsoft SDKs\NuGetPackages'.
https://api.nuget.org/v3/index.json: Package 'Architecture.IO.1.0.20.6742' is not found on source 'https://api.nuget.org/v3/index.json'.
. Please see Error List window for detailed warnings and errors.

Does NPOI support protecting a workbook with password?

I see examples of protecting a sheet from modification. But I am looking for something which protects the workbook with password, such that only users with password and open the xlsx file.

I need to add some validations to the parsing Excel to DataTable process.
for (int i = firstDataRow; i < sheet.PhysicalNumberOfRows; i++)
{
var sheetRow = sheet.GetRow(i);
if (sheetRow == null)
{
continue;
}
var dtRow = fileDataTable.NewRow();

                for (int colNumber = 0; colNumber < numberOfColumns; colNumber++)
                {
                    var cell = sheetRow.GetCell(colNumber, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                                        
                    dtRow[colNumber] = cell.GetFormattedCellValue();

//TODO:validation of the row
}

                fileDataTable.Rows.Add(dtRow);
            }

How do I add validation to make sure required cells are not left blank or null? And more importantly how do I set HasError, RowError with the message?