ahmedwalid05/fastexcel

Fast Excel Reading and Writing in .Net

fastexcel's Issues

FastExcel incorrectly handles decimal separator (eg Russian ,)

When running under a Russian environment with ',' separator, FastExcel creates xlsx files that Excel reports as corrupt and it repairs them. This is caused by FastExcel using the implicit ToString() on double and decimal Value types in Cell.ToXmlString. This writes for example '1,2' to the xml string. Excel only supports an xml string with dot as the decimal separator, eg '1.2'. By forcing the decimal separator to '.' in the current thread prior to using fastExcel.Write, it solves the issue but it is an undesirable workaround. It would be better if FastExcel wrote '.' as the decimal separator without globally changing the separator. The dot is only in the file format, Excel correctly displays ',' in the UI.

Here is how I fixed it:
fix.txt
I uploaded it as txt, since the whitespace was removed from the code fragments from this post making it hard to read.

Create Add Sheet Function

Allow new sheets to be added to the workbook

Add functionality to freeze excel top row

Hi, please consider to add functionality to freeze top row which would be very useful even in simple reports

Object reference error while reading a Excel 2007 file

The fastExcel.Read method in the following code throws "Object reference not set to an instance of an object" exception for some excel files. A sample excel file is attached.

            FileInfo inputFile = new FileInfo(filePath);
            using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(inputFile, true))
            {
                var worksheet = fastExcel.Read(1, 1);
            }

RouteMaster.xlsx

Dates not handled correctly in Worksheet.PopulateRowsFromDataTable

Consider adding:

            if (value is DateTime d)
                    {
                        value = d.ToString("yyyy-MM-dd HH:mm:ss.mmmm");
                    }

immediately after

if (value == null) continue;

export

when creating the spreadsheet there are errors.

the sheet is corrupted. do you need help fixing this?

DateTime problem

I can't change cell.Value to a datetime object because i don't know a cell's data type c. value always converting a double value therefore i am not sure from it's data type.

for example;

foreach (var c in r.Cells) {
dr[index] = c.Value.ToString();
}

is c.Value a datetime it convert to a double and i don't know the column's data type.

how to get the cell value after reading the excel

The excel file can be read quickly by the "read" method, but it is not so convenient to query cell values in the worksheet. Now I get those values in a cell-by-cell way and add those values to a Datatable. I wonder whether there is a better way to query the cell values in the worksheet? Thanks!

Does not support DateTime column？

Out of memory exception

On import of a file a out of memory exception can occur (800k rows, 30 cols, ~115 meg file). I assume because of using xdocument instead of stream.

At any rate thanks for this library, it's great.

Convert number to text column

Hello,

I have some values in a column which start with zero for example, 00072 which a valid word. Before exporting to excel I verified in datatable and the array you used rawData[row + 1, col], it is showing as 00072 only. But when it comes to excel, it is showing 72.

Note that none of the values in this column has a character.

Can you please help here?

Thanks in advance,
Sarath.K.S
[phone]

Defined Names doesn't seem to work

Hi guys, not sure if I'm doing something wrong - docs don't seem to cover this

doing this through powershell so I can interact with it easily while I'm playing

GetCellRangesByDefinedName for "AllColumns" "PatternName" or "Readme" returns null

what am I missing? Really need to use the Defined Names to find the correct ranges in my spreadsheet so I can access simply

Write & in Excel cell

I need to write a text that contains a & to excel and it fails due to the fact that a & is a special char in excel and therefore interpreted in a special way. After writing excel opens with an error and has wiped out the whole text.
I tried to fix this by using a single leading ' to format the whole text as 'text' but without success.

Can you help me?

NullReferenceException in CheckFiles method

FastExcel.cs: 100

else if (!this.ExcelFile.Exists)
{
     this.ExcelFile = null;
     throw new Exception(string.Format("Input file '{0}' does not exist", this.ExcelFile.FullName));
}

Setting ExcelFile to null and then trying to read it's FullName property causing NullReferenceException instead of throwing the intented exception.

Error while load sharedString from file with merged cells

test.xlsx

error found in function:
internal SharedStrings(ZipArchive archive)

After load string sequence is worng if Excel file contain vertically merged cells.

I make some changes like this:
foreach (XElement si in document.Descendants().Where(d => d.Name.LocalName == "si") )
{
String s;
s = "";
foreach (String X in si.Descendants().Where(d => d.Name.LocalName == "t"))
{
if (s != "") s += " ";
s = s + X;
}
int i = AddString(s);
System.Diagnostics.Debug.Print(s + ":" + i.ToString());
}

Unable to read worksheet contents

property Rows on the worksheet object is null for the following worksheet:

2016-01-17 LE Worksheets - Fri - v1.0.xlsx

However I have another similar worksheet that reads fine.

FastExcel.Read(): "An item with the same key has already been added."

Hello,

I'm having the same problem as in the previously closed issue: #2

Basically, when I try to read a specific sheet, I get:

System.ArgumentException: 'An item with the same key has already been added.'

Example Excel Files
success.xlsx
error.xlsx

Example code:
var inputFile = new FileInfo("C:\error.xlsx"); using (var fastExcel = new FastExcel.FastExcel(inputFile, true)) { var sheet = fastExcel.Read(1); }

Andrew

Cell Format (Font Color, Font Style, Cell back Color ..Etc)

Hi,

Thank you for your efforts, please does that support cell formatting? and if yes, please how can I call that?
Regards,
Firas

Why can't I iterate over all worksheets?

From an API design standpoint worksheet = fastExcel.Read("sheet1"); doesn't really make any sense. Why can't I do something like the following:

foreach(var curWorksheet in fastExcelt.WorkSheets)
{
    Console.WriteLine(curWorksheet.Name);
}

target NetStandard 2

targeting NetStandard < 2.0 causes bin bloat on framework biuilds

Initialize with a stream

Would it make sense to initialize the FastExcel with a stream instead of FileInfo?

Should support file flow read and output will be better

it supports the netcore?

Hi it supports the netcore?

How to create a new excel file?

Hello,

In the examples of writing to Excel, a ready-made excel file is requested. (templateFile) Is there a way to create a new excel file from scratch? Can you show us an example where we set the file path and name ourselves?

Thank you from now.

Issue while saving data to excel file

Hi,

I tried to use FastExcel in a mobile app project done using Xamarin forms. I installed version 3.0.2 via NuGet.
I tried several examples (with similar outcomes) but I ended up focusing on "Update Demo".
I tried running the Update Demo code on three different excel files and got the below outcome:

1. "test_2003.xls"
   I created this file by opening Excel 365 and clicking save as 97-2003 *.xls file (empty file).
   When I run the Update Demo code I get this error (I've put here only the first few lines):
   System.IO.InvalidDataException: Number of entries expected in End Of Central Directory does not correspond to number of entries in Central Directory.
   at System.IO.Compression.ZipArchive.ReadCentralDirectory () [0x0004d] in /Users/builder/jenkins/workspace/archive-mono/2019-10/android/release/external/corefx/src/System.IO.Compression/src/System/IO/Compression/ZipArchive.cs:517
2. "test_365.xlsx"
   I created this file using Office 365 and saving it simply as a xlsx file (empty file).
   When I run the Update Demo code I don't get any errors but when I open the file I can't see any changes
3. "test.xlsx"
   This is a old xls file which I had on my computer prior to installing Office 365 so it is older than 365 but newer than xls files.
   The file contains just some text and links without any special stuff like text formatting, formulas, graphs, etc.
   After I run the Update Demo code and try to open the file, Excel says the file has error and after it tries to correct them it says "Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Incorrect document syntax. Line 1, column 5773."

I must say that I am completely new to using FastExcel and also I just started making mobile apps just a year ago so it could be my mistake. However, after trying various things and thinking about it I am tempted to think there may be a bug with the library, hence I've made this post.

I have uploaded the files (both before and after running Update Demo - the ones that contain "original" are before running the code). Download link: https://wetransfer.com/downloads/3f1362963ede75b3fa62ec99767b593d20210111155642/a1ad2875e6e116f8049ff6b979aad57c20210111155656/9e046f

Kind regards,
Marius

FastExcel.Read() for both an index and name throw an ArgumentException

An unhandled exception of type 'System.ArgumentException' occurred in mscorlib.dll

Additional information: An item with the same key has already been added.

The code is right out of the example:

using(FastExcel.FastExcel excel = new FastExcel.FastExcel(new FileInfo(MyReport), true))
{
    var sheet1 = excel.Read("Sheet1"); // I've tried 0, 1, "sheet1", and "Sheet1" (Sheet1 is the name of the actual sheet) all to no avail
}

Issue with Cell class

I have a slight issue in how the Cell class works.

It seems there's two ways to add cells to a row. 1) Worksheet.AddValue 2) Add via IEnumerable<Cell> cells (after casting to a list) property on Row.

First one is slow as noted, but a handy shortcut.
Second one is error prone as no checks are made. So you could make two identical cells and add them to the row. i.e.

row.Cells.Add(new Cell(1, 0));
row.Cells.Add(new Cell(1, 0));

Also if Cells were added in incorrect order there is no sorting to put it right.

Although this would be a breaking change, I think a better approach would be to make Cells a IReadOnlyDictionary<int, Cell> instead. (The current IEnumerable is read only but casting to a list make it not read only, which is how the internal code updates).
For updating cells,
Row.SetCellValue(int/string column, object value)
Row.SetCellValue(Cell cell)
This could set existing cell value, add cell if it doesn't exist, or remove existing if value is null.
As a dictionary (underlying dictionary being SortedDictionary), it is ordered by index and prevents duplicates.

Of course, this would also apply to the Row class and Rows IEnumerable on worksheet too.

As it's a breaking change it may not be worth doing, however as the version is going to version 2, this indicates there is breaking changes, so this might be a good time to change this behaviour.

Handle excel cell formats

The library does not handle different cell formats .
This causes causes an issue with numbers and dates.

List of all issues mentioning this:
#46, #43, #33,

Read cell/column value by column name

How to read column value by column name?

FastExcel is in ReadOnly mode so cannot perform a write

I followed the demo you have here but i got this system exception after using fastExcel.Update method. Is there any way to enable write on this?

When a particular .xlsx file is read, strings are ok but numbers are all zero.

Puget+Sound+Soaring+Association_Balance+Sheet (4).xlsx

When reading the above file with FastExcel, cells with string contents behave as expected, whereas cells with numeric contents return zero. In the original file, only some cells have zero contents; most of them have non-zero numbers. So far, I have noticed this problem only with the attached file; FastExcel handles other .xlsx files properly. Here is what the beginning of the file looks like when opened with Microsoft Excel:

Here is what the beginning of the file looks like after processing with the code shown later below:

To demonstrate the problem, I use this C# code in file FastExcelRead.aspx.cs belonging to an ASP.Net web page:

using FastExcel;
using System;
using System.Data;
using System.IO;

namespace TSoar.Developer.SWLab
{
    public partial class FastExcelRead : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e){
            if (!IsPostBack)
            {   string sPath = Server.MapPath("~/AppData/ClubFinStat/Historical/2021-11-24/");
                DirectoryInfo di = new DirectoryInfo(sPath);
                FileInfo[] files = di.GetFiles("Puget+Sound+Soaring+Association_Balance+Sheet*.xlsx", 0);
                DataTable dt = new DataTable();
                int iNumCols = 0;
                using (FastExcel.FastExcel fEx = new FastExcel.FastExcel(files[0], true))
                {   Worksheet ws = fEx.Read(1); // There is only one worksheet
                    foreach (FastExcel.Row r in ws.Rows){
                        foreach (var cell in r.Cells)
                        {    iNumCols++; }
                        break;
                    }
                    for (int j = 0; j < iNumCols; j++)
                    {   dt.Columns.Add("Column " + (101 + j).ToString().Substring(1));
                    }
                    foreach (FastExcel.Row r in ws.Rows)
                    {   DataRow dr = dt.NewRow();
                        int j = 0;
                        foreach (var cell in r.Cells)
                        {   dr[j++] = cell.Value;
                        }
                        dt.Rows.Add(dr);
                    }
                }
                gv.DataSource = dt;
                gv.DataBind();
            }
        }
    }
}

Here is the code in the corresponding FastExcelRead.aspx file:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="FastExcelRead.aspx.cs" Inherits="TSoar.Developer.SWLab.FastExcelRead" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Demo of FastExcel Reading all Numbers as Zeroes</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="gv" runat="server" AutoGenerateColumns="true"></asp:GridView>
        </div>
    </form>
</body>
</html>

It is always possible that I am doing something wrong in using the FastExcel package, but I kind of doubt it. A similar problem occurred with this particular .xlsx file when I processed it with ExcelDataReader.DataSet.

README.md has dead link

I noticed that you have a dead link in README.md

https://github.com/mrjono1/FastExcel/blob/master/FastExcelDemo/Program.cs

can this library support read cell annotation？

collaborators

@Sibz are you interested in being a collaborator on this project?

Date format?

When the worksheet is readed, the cells formated as complete Dates return an odd number (43115.47584490741 e.g) and are of String type.
How do I convert these into dates?

Can I write a Cell() with a formula?

e.g.
passing "=RAND()" as a value doesn't quite work.
Best,
Nestor

Demo gives me errors

When trying the Read Demo I get that DataSet and FileInfo can't be found. if I include System.Data, then fastExcel.Read stops working.

fastExcel.Update() breaks when making changes to an existing sheet

I have an existing excel file that I need to go through and change a cell value depending on the column values around it. Basically, I'm changing the cell values of 1 column. Everything works well until I get to update and then it throws a System.IO.IOException. However, if I call update before making changes, there is no error. I also tried using the original file as a template but it threw the same error. I included an abbreviated version of my code.

using FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(new System.IO.FileInfo(excelPath));

var sheet = fastExcel.Read(1);
var rows = sheet.Rows.ToList();

for (int i = 1; i < rows.Count; i++)
{
      if(rows[i].GetCellByColumnName("H").Value == "Change me") {
         rows[i].GetCellByColumnName("G").Value = "Changed"
      } 
}

fastExcel.Update(sheet, 1); // errors here

Maybe I'm misunderstanding how the update works, but I'm hoping to get some clarity on this issue.
Full error message:
System.IO.IOException HResult=0x80131620 Message=Entries cannot be opened multiple times in Update mode. Source=System.IO.Compression StackTrace: at System.IO.Compression.ZipArchiveEntry.OpenInUpdateMode() at System.IO.Compression.ZipArchiveEntry.Open() at FastExcel.Worksheet.GetWorksheetPropertiesAndValidateNewName(FastExcel fastExcel, Nullable1 sheetNumber, String sheetName, String newSheetName) at FastExcel.Worksheet.GetWorksheetProperties(FastExcel fastExcel, Nullable1 sheetNumber, String sheetName) at FastExcel.Worksheet.Read(Nullable1 sheetNumber, String sheetName, Int32 existingHeadingRows) at FastExcel.FastExcel.Read(Nullable1 sheetNumber, String sheetName, Int32 existingHeadingRows) at FastExcel.FastExcel.Update(Worksheet data, Nullable`1 sheetNumber, String sheetName) at FastExcel.FastExcel.Update(Worksheet data, Int32 sheetNumber) at PopulateAllCells.Program.Main(String[] args) in

How to inset X Y Scatter Chart to excel document with FastExcel?

Hi
I want to inset X Y scatter chart to plot x and y data to excel. Can I do it with FastExcel?

Add unit tests

need to add unit tests to project

Template file

Why do I need a template file? I just want to create an empty sheet without a template.

Fails parsing row with spreadsheetDrawing

Tries to parse the row number for an image http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing

This causes an exception when trying to get rows.

Would recommend filtering out spreadsheetDrawing rows. Unless these are to be parsed.

Example write CellRange

please if you could support me

try this code and it is not as expected

        Worksheet worksheet = new Worksheet();
        List<Row> rows = new List<Row>();

        //CellRange cellRange = new CellRange("A", "M", 1);

        for (int rowNumber = 1; rowNumber < 6; rowNumber++)
        {
            List<Cell> cells = new List<Cell>();
            cells.Add(new Cell(1, "Demo"));                
            cells.Add(new Cell(13, "Hello" + rowNumber));
            cells.Add(new Cell(14, "Some Text"));
            
            cells[0].Merge(cells[1]);

            rows.Add(new Row(rowNumber, cells));
        }

Cells with formulas

A spreadsheet I am reading contains formulas.
For example cell H32 contains a formula =+F32*G32, which evaluates to 26670.

Having a look inside the Cell constructor I can see the element for the cell contains both the formula and calculated value:

<c r="H32" s="97" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <f>+F32*G32</f>
  <v>26670</v>
</c>

However the Value property on the cell XElement contains the string "+F32*G3226670".

Can we detect when a formula is in use, and read the calculated value as the value of the cell?

Update excel worksheet

Hi,

I'm using the update template code to update some cells, however when I open the file I need to repair it. (Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Incorrect document syntax. Line 1, column 1013.
)

        FileInfo inputFile = new FileInfo(@"D:\SERVICE\ReportingService\repository\Report_Teste2.xlsx");

        //Create a some rows in a worksheet
        fexcel.Worksheet worksheet = new fexcel.Worksheet();
        List<fexcel.Row> rows = new List<fexcel.Row>();

        for (int rowNumber = 1; rowNumber < 10; rowNumber += 5)
        {
            List<fexcel.Cell> cells = new List<fexcel.Cell>();
            for (int columnNumber = 1; columnNumber < 4; columnNumber += 2)
            {
                cells.Add(new fexcel.Cell(columnNumber, rowNumber));
            }
            cells.Add(new fexcel.Cell(4, "Updated Row"));

            rows.Add(new fexcel.Row(rowNumber, cells));
        }
        worksheet.Rows = rows;
        

        // Create an instance of Fast Excel
        using (fexcel.FastExcel fastExcel = new fexcel.FastExcel(inputFile))
        {
            // Read the data
            fastExcel.Update(worksheet, "parser_reports_type");
        }

Report_Teste2.xlsx

Looking for a new owner

Hi, I am no longer interested in owning this project

There is nuget, repo and the appveyor to hand over

post a reply here if you are interested

FastExcel includes EqualityContract of C# 9 Record types

Hi, I try ed using FastExcel with C# 9 Record types and those generate a runtime type called EqualityContract.

Using FastExcel:
Write(IEnumerable objectList, int sheetNumber, bool usePropertiesAsHeadings)
objectList will include that EqualityContract as output to the first column, anyway to exclude all runtime types from Write?

Update multiple worksheets at onces

We have the situation that we want to fill an excel template that has several worksheets. Is there a better way to do this instead of:

using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(templateFile, outputFileTmp))
{
    fastExcel.Write(worksheet1, "sheet1", 1);
}
using (FastExcel.FastExcel fastExcel = new FastExcel.FastExcel(new FileInfo(outputPathTmp), outputFile))
{
    fastExcel.Write(worksheet2, "sheet2", 1);
}
File.Delete(outputPathTmp);

Support Special Characters

Support special characters like &.
A workaround was discussed in #44

Row style and big data

Hello

Firstly, I would like to congratulate you on your work.

Come on.

I have 2 problems.

Is it possible to paint the first line, or even make a zebra?

I tried to generate a file with 700,000 rows and 70 columns and got the exception (OutOfMemory) returned at the moment "fastExcel.Write (worksheet," sheet1 ");", any solution to this?