ahmedwalid05 / fastexcel Goto Github PK
View Code? Open in Web Editor NEWFast Excel Reading and Writing in .Net
License: MIT License
Fast Excel Reading and Writing in .Net
License: MIT License
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?
How to read column value by column name?
Would it make sense to initialize the FastExcel
with a stream instead of FileInfo
?
@Sibz are you interested in being a collaborator on this project?
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));
}
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.
Hi,
Thank you for your efforts, please does that support cell formatting? and if yes, please how can I call that?
Regards,
Firas
Consider adding:
if (value is DateTime d)
{
value = d.ToString("yyyy-MM-dd HH:mm:ss.mmmm");
}
immediately after
if (value == null) continue;
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.
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.
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.
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");
}
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);
}
Allow new sheets to be added to the workbook
need to add unit tests to project
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
+919645809996
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, Nullable
1 sheetNumber, String sheetName) at FastExcel.Worksheet.Read(Nullable1 sheetNumber, String sheetName, Int32 existingHeadingRows) at FastExcel.FastExcel.Read(Nullable
1 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
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.
Hi
I want to inset X Y scatter chart to plot x and y data to excel. Can I do it with FastExcel?
I noticed that you have a dead link in README.md
https://github.com/mrjono1/FastExcel/blob/master/FastExcelDemo/Program.cs
e.g.
passing "=RAND()" as a value doesn't quite work.
Best,
Nestor
Why do I need a template file? I just want to create an empty sheet without a template.
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
}
Hi it supports the netcore?
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 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?
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.
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.
when creating the spreadsheet there are errors.
the sheet is corrupted. do you need help fixing this?
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.
targeting NetStandard < 2.0 causes bin bloat on framework biuilds
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());
}
Support special characters like &
.
A workaround was discussed in #44
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?
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:
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
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);
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.
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
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?
Hi, please consider to add functionality to freeze top row which would be very useful even in simple reports
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?
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.
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!
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);
}
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.