rabanti-github / nanoxlsx Goto Github PK

Hi,

First of all very nice Library and good interface 😊.

Do you plan to implement to add vba macro support?

Like https://github.com/EPPlusSoftware/EPPlus/wiki/VBA

Thanks alot

Exceuse, How to use the nanoxlsx to fixed first row,using the style setting?

I'm trying to create Workbook on c# side, to create input for existing matlab script. Currently I'm looking into an issue, that matlab is not able to load worksheet. I'm getting an error "Worksheet not found" from matlab.

After uploading xlsx to office365, and then downloading it from office365 without any change as a copy, matlab is able to load data from second file.
It seems that office365 is adding some missing fields, which matlab requires.

I believe that problem exists in all of 3 libs, matlab, office365 and NanoXLSX, but I have no idea how to deal with that problem.

When Fill.ValidateColor() is called (internally) with an invalid-length hex code, the error message within the exception indicates that 'A valid value must contain six hex characters', however this message does not respect that there may be an alpha value specified. Error message should be updated to output the value of the 'num' variable used in the length check instead of the hardcoded 'six' in the string.

Pseudo-Sample Code

try {
	// Get the cell and set the background color
	Cell c = ws.GetCell(0, 0);

	// This works as expected (8-char hex string)
	c.CellStyle.CurrentFill.BackgroundColor = string.Format("FF{0:X2}{1:X2}{2:X2}", fmt.BackColor.R, fmt.BackColor.G, fmt.BackColor.B);

	// Generates exception with message (6-char hex string)
	c.CellStyle.CurrentFill.BackgroundColor = string.Format("{0:X2}{1:X2}{2:X2}", fmt.BackColor.R, fmt.BackColor.G, fmt.BackColor.B);

} catch (StyleException ex) {
	// Generates exception with message
	int brk = 0;
}

Hi dear, If I try to create new empty xlsx file from Excel and after load with Nanoxlsx it It work
Workbook wb = Workbook.Load("Test.xlsx");
If I update the file and write something with excel and I try to load I receive this error
InnerException = {"The XML entry could not be read from the input stream. Please see the inner exception:"}

In conclusion impossible to update file from excel and after load from Nanoxlsx

My excel version : Microsoft® Excel® 2021 MSO (Version 2207 Build 16.0.15427.20182) 64 bit

Thanks for support
Fabio

Describe the goal you want to achieve using NanoXLSX
I want to add a hyperlink (ie. URL) to a cell, so in excel when I click the cell I navigate to the specified URL. This differs from the display-text, which is a human readable text.

In Excel I can click any cell, then press CTL+K and insert a URL in the appearing dialog. After that the cell text acts like a hyperlink.

Can someone point me in the right direction?

thanks!

How can I inport to datagridview
I only get long list

Excel file
Name1, Name2, Name3, Name4

Same in Datagridview
column1,column 2,column 3,column 4

Dim wb As Workbook = Workbook.Load("Importa.xlsx") Dim ws As Worksheet = wb.Worksheets(0) Dim Cx As Dictionary(Of String, Cell) = ws.Cells

Hi Rabanti,

I just catch an other weird bug. If I rename a worksheet with this code:
string Filename = @"C:\_Local\Sample.xlsx"; Workbook Wb = Workbook.Load(Filename); Wb.GetWorksheet("Sheet1").SetSheetName("NewSheetName"); Wb.Filename = Filename; Wb.Save();
I get the dates corrupted.

Before:

After:

Sample.xlsx

Using a simple table with 7 rows and 13 columns, GetLastRowNumber returns 11. Sample file is attached.
testdoc.xlsx

Bug description
In a xlsx where multiple ranges are selected, Workbook.Load() raises an FormatException: The cell range (...) is malformed and could not be resolved.

You can reproduce the error with the attached: Test.xlsx
It is just an empty workbook where the cells A1:A3 and A6:A8 are selected.

Sample Code
var wb = NanoXLSX.Workbook.Load(@"C:\Temp\Test.xlsx");

Expected behavior
NanoXLSX should be able to read the file.

Environment (please complete the following information):
OS: Window 10
.NET framework: .NET 6
System locale: de-DE
Version of NanoXLSX: 2.0.4

How can i set the value of a cell retrieved with the Worksheet.GetCell metod? I try set Value property but not work

Hi Raphael,

thanks for this little library. Just found it and it works very well!

One nice enhancement would be the auto-resizing of the columns based on the contents of the cells (also taking the auto-filter dropdown-button into account)!

Could you take a look into this, please?

Thanks and regards,
Jan

Is it possible to read the cell data as string or unformatted data ?
I have a excel file, which has column data type correct , (values text and numbers), but due to saving excel file with openoffice, it get changed to DATE format and then all values are read as dates, which is not usable for our application.

Can you change the column data type, so the values would be read as string and not date ?

Bug description
Functions GetLastDataColumnNumber() and GetFirstDataColumnNumber() has swapped Min and Max functions. It is necessary to fix the function GetBoundaryDataNumber(bool row, bool min, bool ignoreEmpty) in the Worksheet.cs file ln: 1324.

Steps To Reproduce
Steps to reproduce the behavior:

1. Create a workbook
2. Create worksheet and fill values into A1,B1,C1
3. Call worksheet.GetLastDataColumnNumber() and worksheet.GetFirstDataColumnNumber()

Expected behavior
If you follow Steps to reproduce the behavior expected GetLastDataColumnNumber() return 3 and GetFirstColumnNumber() returns 0.

Environment (please complete the following information):

• Windows 10
• .NET Framework 4.7.2
• 'en-US'
• Version of NanoXLSX 2.3.0

If I try to open an already existing Excel file, update it and then save I get this exception:

So the obvious move it to set the filename (which should have been set automatically on load), but then it fails to save and also corrupts the file!

It works fine on a "C:\path" kind of path, but fails on network "\server\path" addresses. But the strange thing is that it do hit the file as it manage to corrupt it

I'm not including a test file here as it happens with any file

Is your feature request related to a problem? Please describe.
I want to add a list of strings in one cell

Describe the solution you'd like
Add multiline support

Additional context

When trying to read a workbook:

Dim wb As Workbook = Workbook.Load(Caminho)

An exception is throw:

IOException: The XML entry could not be read from the input stream. Please see the inner exception:
ArgumentException: Not a legal OleAut date.

error.xlsx

Bug description
We have seen a number of times that amounts in Excel are getting rounded of in weird ways. Please see the attached xlsx file for an example where 4372449.78 is rounded to 4372450 after reading it with NanoXLSX.

Steps To Reproduce
Steps to reproduce is shown here: da00f28#diff-ab6f3e2c36de0c57128180f0a5bb2e99cbc6b003609f5122cf243bea2a8c6598

Expected behavior
I would expect the number not to be rounded off, but instead be parsed to the same number as I can see in Excel (4372449.78 in this case)

large-ish-amount.xlsx

Excelfile

Create two sheets (Name: "Visible" and "Invisible")
Hide the sheet named "Invisible" (https://support.microsoft.com/en-us/office/hide-or-unhide-worksheets-69f2701a-21f5-4186-87d7-341a8cf53344)
Save the excelfile as "excel_with_two_sheets.xlsx"

Dependency:

  <ItemGroup>
    <PackageReference Include="NanoXLSX" Version="1.8.1" />
  </ItemGroup>

Minimal Code

using System;
using NanoXLSX;

namespace nanoxlsx_minimal
{
    internal class Program
    {
        static void Main(string[] args)
        {
            var workBook = Workbook.Load("excel_with_two_sheets.xlsx");

            foreach (var sheet in workBook.Worksheets)
            {
                Console.WriteLine($"{sheet.SheetName}: Hidden: {sheet.Hidden}");
            }
        }
    }
}

Current result

Visible: Hidden: False
Invisible: Hidden: False

Both sheets return "false" for the "Hidden" property.

Is it possible to add data validations to a column in the resulting spreadsheet? The excel file I am generating will also be used as an import file for easy of updating large amounts of changes and adding new records. As such, I am wondering if it is possible to implement the Excels data validation functionality? When added to the spreadsheet, via Excel, entries are added to the sheet itself, but I am unable to see any methods on the worksheet, in NanoXLSX, that would allow me to add such functionality to the spreadsheet I am creating. If this functionality does exist, could someone please point me int the right direction.

I'm using NanoXLSX v2.0.1 trying to open an excel with a custom cell style and get the exception

StyleException: The number '44' is not a valid custom format ID. Must be at least 164

I just want to add a new Sheet (and delete an other), so I could live without loading the cell styles. Can you help me with this?

I attach the sample file.
Test.xlsx

Semi-colon does not seem to work in formulas.
Added a fix in #26 - Thank you for your efforts with this library!

I's there any way to Read the background color assigned to a cell ?

Currently it is not possible to insert images into cells.
If you try to import and save a file with images, they are simply deleted.

Hi,

I have a problem when I use AddCell() method because the incremental value is false but the columnNumber or rowNumber is still increased.

Is this an issue or there is something i don't see ?

Thanks.

Bug description
Opening an xlsx with a chartsheet throws a null exception.

Steps To Reproduce
Steps to reproduce the behavior:
An excel file I can't provide, sorry.

in NanoXLSX.LowLevel.XlsxReader.Read() there's a line get a sheet entry stream MemoryStream entryStream3 = this.GetEntryStream(name, archive);, which returns null since this sheet does not actually exist in worksheets but in chartsheets.
workbook.xml does list it though in .

I am currently attempting to freeze the first row of the spreadsheet I am creating using NanoXLSX.

Here is the psuedo code for what I am doing...

• Create a new Cell object passing the cell value and CellType into the constructor.
• Create a new Style object setting the Fill to a hex string and the FillType to fillColor.
• Call the Style method of the cell passing in the style object, created above, and setting unmanaged to true, I have also tried false.
• Call the SetCellLockState of the cell passing in 'true', of isLocked, and 'false', for isHIdden. NOTE: when I debug my code I am unable to see any properties, in my cell object, that indicate that the cell is marked as being locked.
• Add the cell to the worksheet, by way of the AddeCell method, passing in the parameters of cell, my current column value, my current row value.

When my spreadsheet is generated all of the styles have been applied and the subsequent row data, which is added after the above code is run, appears as expected. However, the top row, the on in which I have place my column headers, is not locked.

When I go throught the available methods for both the cell and the row, it is only the cell which seems to contain a method that deals with locking. With this being the case, I am getting the impression that the SetCellLockState is not doing what one would think it should.

Am I going about this the wrong way?

Is it possible to set the Ident attribute for alignment in CurrentCellXf?

In the Excel Styles.xml would be something like:

<cellXfs count="4"> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/> <xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/> <xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"> <alignment horizontal="left" indent="3"/> </xf> </cellXfs>

I'm reading a spreadsheet with this:

        Dim ws As Worksheet = wb.Worksheets(0)
        Dim Cx As Dictionary(Of String, Cell) = ws.Cells()

In the spreadsheet the cell has this value: 1.1.2.03.00001

I need to read as a text, but the Cells() return it as a date.

Is it possible to force Cells to read in the specific format?

Is your feature request related to a problem? Please describe.
Very large xlsx files explodes the memory usage or is unable to be loaded.

Describe the solution you'd like
If one only cares about reading data from very large xlsx files, then by utilizing the streams it is possible to minimize the memory usage and improve performance.

Additional context
A working sketch can be found on my fork. If you have any interest in such a feature, please let me know.

Can you implement a method to clone an existing sheet with a new name?

Hi,
I use the command WorkBook.Load to load an existing file, I edit it only by adding some cells, in the end I use command SaveAs, but the file lost all styles.

Thank you.

Bug description
I have a workbook with protected worksheets from an external source. I don't know how the file was processed, but it is opening without errors in Microsoft Excel 365 (current). NanoXLSX (2.0.4 and current github-version) throws an:

System.FormatException
HResult=0x80131537
Nachricht = Input string was not in a correct format.
Quelle = System.Private.CoreLib
Stapelüberwachung:
bei System.Number.ThrowOverflowOrFormatException(ParsingStatus status, TypeCode type)
bei System.Number.ParseInt32(ReadOnlySpan`1 value, NumberStyles styles, NumberFormatInfo info)
bei System.Int32.Parse(String s, IFormatProvider provider)
bei NanoXLSX.LowLevel.ReaderUtils.ParseInt(String rawValue) in C:\github\NanoXLSX\NanoXLSX\LowLevel\ReaderUtils.cs: Zeile110
bei NanoXLSX.LowLevel.WorksheetReader.ManageSheetProtection(XmlNode node, SheetProtectionValue sheetProtectionValue) in C:\github\NanoXLSX\NanoXLSX\LowLevel\WorksheetReader.cs: Zeile331
bei NanoXLSX.LowLevel.WorksheetReader.GetSheetProtection(XmlDocument xmlDocument) in C:\github\NanoXLSX\NanoXLSX\LowLevel\WorksheetReader.cs: Zeile297
bei NanoXLSX.LowLevel.WorksheetReader.Read(MemoryStream stream) in C:\github\NanoXLSX\NanoXLSX\LowLevel\WorksheetReader.cs: Zeile187
bei NanoXLSX.LowLevel.XlsxReader.Read() in C:\github\NanoXLSX\NanoXLSX\LowLevel\XlsxReader.cs: Zeile136
bei NanoXLSX.Workbook.Load(String filename, ImportOptions options) in C:\github\NanoXLSX\NanoXLSX\Workbook.cs: Zeile907
bei Demo.Program.Main(String[] args) in C:\github\NanoXLSX\Demo\Program.cs: Zeile33

on loading. (After removing the try/catch-blocks in XlsxReader.Read() and WorksheetReader.Read())

The sheet1.xml contains the following element:

So it looks like NanoXLS is failing on converting the "false" expressions into an int.

I am not sure how there "false"-expressions where generated. But NanoXLS could easy accept those in

ReaderUtils.ParseInt():

    internal static int ParseInt(String rawValue)
    {
        if (rawValue == "false") return 0;
        if (rawValue == "true") return 1;
        return int.Parse(rawValue, CultureInfo.InvariantCulture);
    }

When reading a numeric cell that contain a decimal value. It reads as an integer number.
This only happens if the regional settings of the computer it's configured to use , (comma) as a decimal deliminator rather then . (dot) as in the us.

A fast workaround is to set the culture of the thread to en-US.

System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

We are trying to use NanoXLSX within a website (asp.net) application. The library won't load as it is not strong named.
https://learn.microsoft.com/en-us/dotnet/standard/assembly/sign-strong-name

Can you add a key and enable strong naming please?

Bug description
I have xlsx exported from Google sheet. It failed to load in NanoXLSX with
FormatException: Input string was not in a correct format.

Good that I have access to source and be able to pinpoint the line.
The xlsx sheet has frozen pane, and from tracing your source code, the library wont be able to handle when "ySplit" = "1.0"

The lib helper function ReaderUtils.ParseInt inside is calling
int.Parse( rawValue, CultureInfo.InvariantCulture )
which is equivalent to
int.Parse( rawValue, NumberStyles.Integer, CultureInfo.InvariantCulture )

passing "1.0" make this call throws "FormatException"

Steps To Reproduce
Steps to reproduce the behavior:

1. Go to google sheet link in the attachment section. Export it as XLSX to your PC.
2. Use NanoXLSX to load this xlsx.
3. I got FormatException

Sample Code
Just Workbook.Load( Filename );

Expected behavior
I guess this is trivial value formatting problem?
Changing inside of ReaderUtils.ParseInt to call to int.Parse( rawValue, NumberStyles.Any, CultureInfo.InvariantCulture ) would also make this pass.
But that was just my naive guess, I don't have in-depth knowledge of XLSX standard, idk if that would break any other compatibility.

Screenshots and/or attachments
Screenshot is above
Sample sheet is here
https://docs.google.com/spreadsheets/d/1H1fRjvA16enlCLTG17RdTXHgHV6rif-bFkAQhGDTda8/edit?usp=sharing

Environment (please complete the following information):

• OS: win11
• .NET framework: This was on Unity3D C# project, it has equivalent of .NET Framework 4.8 (see next section)
• Your system locale: en-US
• Version of NanoXLSX: Lastest github commit as of 2024/Jan/22

Additional context
The code environment was ran on Unity3D C# project, which is using its own .NET implementation (Mono)
But I guess this should not make this problem unique to UnityC# (?)
The int.Parse( rawValue, CultureInfo.InvariantCulture ) and int.Parse( rawValue, NumberStyles.Integer, CultureInfo.InvariantCulture ) part behaviour should be identical to offical .NET nonetheless.

Hi,
what is the correct way to create multiple custom formats?

I create the two formats and use them as followed and get a error on opening the Excel file:

var style1 = new Style();
style1.CurrentNumberFormat.CustomFormatCode = "yyyy-mm-dd HH:mm:ss";
style1.CurrentNumberFormat.Number = NumberFormat.FormatNumber.custom;

var style2 = new Style();
style2.CurrentNumberFormat.CustomFormatCode = "#,##0.00 \\s";
style2.CurrentNumberFormat.Number = NumberFormat.FormatNumber.custom;

...

wb.WS.Value(DateTime.Now, style1);
wb.WS.Value(1.234, style2);

...

Thanks for the help.

So i was using nanoxlsx to save my item database to an xlsx sheet. I escaped empty strings with "EMPTY_STRING", so that the cell wouldn't be empty.

Note that i did not try to write the quotation marks. The string i tried to save was EMPTY_STRING

When i tried reading one of these EMPTY_STRING Cells i would just get the value of another random cell instead.

When investigating the xlsx file via Excel, i found that these cells would in fact not contain the string "EMPTY_STRING" but "EMPTY_STRING]". Note the extra ']'.
I would venture that this bracket belongs in the xml code and is now missing there messing up the parsing.

I solved the issue by just changing my escape string form "EMPTY_STRING" to "(EMPTY_STRING)".

When generating files, RichTextString is not supported, similar to Excel's Cell.Characters( )

Is it possible to create a custom format for positive and negative number?

I can create in Excel, with this pattern: #.##0,00"D";#.##0,00"c";#.##0,00 , but it does not work if i create the same in the library.

If i create just like this: CurrentNumberFormat.CustomFormatCode = "R$ #,##0.00C

It works, but just for positive numbers, if i try to add more rules, using the semicolon, like excel. it stop working.

There is a bug in the AddNextCellFormula method, it does not apply the style supplied in the method.

There is an easy fix.

    public void AddNextCellFormula(string formula, Style style)
    {
        Cell c = new Cell(formula, Cell.CellType.FORMULA, currentColumnNumber, currentRowNumber, this);
        AddNextCell(c, true, null);
    }

Should be:

    public void AddNextCellFormula(string formula, Style style)
    {
        Cell c = new Cell(formula, Cell.CellType.FORMULA, currentColumnNumber, currentRowNumber, this);
        AddNextCell(c, true, style);
    }

Numeric values are usually described as follows:

    <row r="1" spans="1:1" x14ac:dyDescent="0.25">
      <c r="A1">
        <v>0</v>
      </c>
    </row>

However, NanoXLSX and PicoXLSX seems so generate this:

<row r="7">
      <c t="s" r="A7">
        <v>7</v>
      </c>
      <c t="n" r="B7">
        <v>0</v>
      </c>
...
    </row>

Issues are:

• The "t" attribute seems to be wrong or outdated
• Excel seems to generate one row tag per cell (maybe not optimized)

The datatypes of worksheets must be investigated and aligned to the current OOXML specs.

Is it possible to create a pivot table?

I did not found a way to do it.

Hi,
when creating a custom format, there is a problem with the escaped characters.

Also take a look at the count attribute of numFmts. Its 3 and not 2, as expected.

`
style.CurrentNumberFormat.CustomFormatCode = "yyyy-mm-dd HH:mm:ss";

style.CurrentNumberFormat.CustomFormatCode = @"#,##0\ \s";
`

style.xml result:

...
<numFmts count="3">
	<numFmt formatCode="yyyy-mm-dd HH:mm:ss" numFmtId="164"/>
	<numFmt formatCode="#,##0\\ \\s" numFmtId="165"/>
</numFmts>
...

style.xml corrected with Microsoft Excel:

...
<numFmts count="2">
	<numFmt numFmtId="164" formatCode="yyyy\-mm\-dd\ hh:mm:ss"/>
	<numFmt numFmtId="166" formatCode="#,##0\ \s"/>
</numFmts>
...

An xlsx file created with the Japanese version of Excel 2019 contains an annotation called Furigana.
When that xlsx file is read in the NanoXLSX file, the furigana is mixed in the Cell.Value, resulting in an incorrect value.

Cell.Value is expected to ignore the furigana.

Steps to reproduce the problem:

1. use tokyo.xlsx
   
2. Execute the following code.

    using NanoXLSX;
    var wb = Workbook.Load("tokyo.xlsx");
    Console.Write.WriteLine(wb.Worksheets[0].Cells["A1"].Value);
    
    // expect value is "ここは東京"
    // but, print "ここは東京トウキョウ"