ID

Uploaded

Status

Description

Work Items

Action

11137
by rmbuda
Dec 22, 2011
1:30 AM

Being evaluated

Optimise creation of worksheets by adding Row.AppendCell().
This bypasses a lot of low-level DOM XML searching used by the Cell(x,y) method and speeds up worksheet creation by a factor of 10+.

e.g:
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Sheet1");

var reportItems = GetTestData();
foreach (var item in reportItems)
{
ExcelRow row = worksheet.AppendRow();
row.AppendCell().Value = item[0];
row.AppendCell().Value = item[1];
row.AppendCell().Value = item[2];
row.AppendCell().Value = item[3];
}

xlPackage.Save();

Download

9643
by DanielTrommel
Jun 2, 2011
9:16 PM

Being evaluated

Not sure this is a common bug, but I got the following exception: I tried to add a new worksheet and then the ExcelWorkSheet.Add() method produced at the last line of the following code sniplet:

// add the new worksheet to the package
Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"); <=== threw exception =====

When I inspected (using a ZIP file manager) the Excel file to which I tried to add the new worksheet, it turned out that the directory xl/worksheets/... contains TWO files, whilst the file xl/workbook.xml only thinks there is one.

Now, the thing is that ExcelPackage uses the information in the xl/workbook.xml to choose the next ID for a new sheet in the ExcelWorkSheet.Add() method, and thinks that sheet with ID=1 is the one with the highest ID and thinks ID=2 can be chosen for the new worksheet. However, that one already exists: xl/worksheets/sheet2.xml

To circumvent this problem, I replaced the above code sniplet with the following:

// add the new worksheet to the package
Uri uriWorksheet = new Uri("/xl/worksheets/sheet" + sheetID.ToString() + ".xml", UriKind.Relative);
// although not registered, the part might actually exists (causing an error at the .CreatePart() method;
if( _xlPackage.Package.PartExists( uriWorksheet ))
{
// since it is not referenced in the workbook, we can delete it
_xlPackage.Package.DeletePart(uriWorksheet);
}
PackagePart worksheetPart = _xlPackage.Package.CreatePart(uriWorksheet, @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

Download

8311
by amitfrenz
Feb 9, 2011
12:54 PM

Being evaluated

Attached is the Sample Source Code of Using ExcelPackage library.
1. I have made an enhancement to it where we can download the zipped Excel File to Client Machine.
2. Multiple sheets in a single file are implement.
3. Have worked on a function which can Adjust the width of the Column.(as like Autofit )
Hope this will help someone.
There are 2 zipped files attached one is the Sample Code and other is the Lib version i used.
Please Create a folder Named as "Report" in C:\ : as it is hard coded in the application.

Thanks
Amit Srivastava

8665

Download

7256
by vrp
Nov 3, 2010
3:01 PM

Being evaluated

when accessing rows and cells , everytime it process a search for that cell or row in the sheet xml.
We are using this project for one of our customer and when writing 3000 rows to an excel file , this was taking more then 30 minutes, so we tried to improve the performans.
we have just added a simple cashing for cells and rows and once we access one of the row or cell then we cash it and later we access it then read the cell or row from that cash. so it helped us a lot.
now it takes less then 3 minutes to write 3000 rows data to an excel file. i haven't tried for reading cell or row value, ı hope it will improve the performance of reading excel cells.

-------------------------------------------------------------------------------NEW-----------------------------------------------
public ExcelCell Cell(int row, int col)
{
ExcelCell ret;
string key = String.Format("{0}-{1}",row,col);
if (_cells.ContainsKey(key))
ret = (ExcelCell)_cells[key];
else {
ret = new ExcelCell(this, row, col);
_cells.Add(key, ret);
}
return (ret);
}


public ExcelRow Row(int row)
{
ExcelRow ret;
if (_rows.ContainsKey(row))
ret = (ExcelRow)_rows[row];
else
{
ret = new ExcelRow(this, row);
_rows.Add(row, ret);
}
return (ret);
}

------------------------------------------------------------------OLD-------------------------------------------------------------------
public ExcelCell Cell(int row, int col)
{
return (new ExcelCell(this, row, col));
}

public ExcelRow Row(int row)
{
return (new ExcelRow(this, row));
}
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Download

5751
by jmanning
Apr 13, 2010
3:32 PM

Being evaluated

if you create a new package from a template but don't make any changes, you hit a null ref in Save because in ExcelWorksheet.Save, there's no null check around the region #region Delete the printer settings component (if it exists).

I just moved the null check from the "save header and footer" section (lower in the same method) so all the method's contents are in the null check now.

Download

3217
by Agent_9191
Jun 30, 2009
4:53 PM

Being evaluated

The XPath used for setting up the Shared String information in ExcelCell.cs doesn't account for an apostrophes in the string. Need to change the line that looks like

XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t='{0}']", Value), _xlWorksheet.NameSpaceManager);

to one that looks like this:

XmlNode stringNode = _xlWorksheet.xlPackage.Workbook.SharedStringsXml.SelectSingleNode(string.Format("//d:si[d:t=\"{0}\"]", Value), _xlWorksheet.NameSpaceManager);


(Line 456 in the attached file.)

Download

1847
by jefight
Sep 18, 2008
1:27 AM

Being evaluated

test case to cause error:

public void CauseErrorOnSave()
{
FileInfo fileInfo = new FileInfo("c:\\myfile.xlsx");
using (ExcelPackage xlPackage = new ExcelPackage(fileInfo))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("one");
ExcelWorksheet worksheet2 = xlPackage.Workbook.Worksheets.Add("two");

xlPackage.Save(); //will err on ExcelWorksheet.cs:
//protected internal void Save():
//XmlNode pageSetup = __worksheetXml.SelectSingleNode("//d:pageSetup", NameSpaceManager);
//where: _worksheetXml == null
}
}

Download

1233
by lsdeans
May 13, 2008
11:36 PM

Being evaluated

Adapted from rob_dot_net's patch (1042) to add in the following functionality via overloads:

Can force all data to be treated as strings (critical for cases where an employeeId or similar data is used that can begin with 0)

Can dynamically select which columns to use from DataTable and optionally set header row label (instead of default of DataTable's column name)

Can set the style Id to be used for the header row

Default still works as before (although name changed to be more descriptive):
worksheet.LoadFromDataTable(dt);

Example overload:
worksheet.LoadFromDataTable(dt, new string[] { "PersonId", "PersonName", "Department" });

Assuming your DataTable had more fields like Telephone, Email, etc, only the three fields specified above would be output to the Excel file.

Again working witht he above if you wanted the header columns labeled you could do:

KeyValuePair<string, string>[] columns = new KeyValuePair<string, string>[3];
columns[0] = new KeyValuePair<string, string>("PersonId", "Badge Number");
columns[1] = new KeyValuePair<string, string>("PersonName", "Employee Name");
columns[2] = new KeyValuePair<string, string>("Department", "Department");
worksheet.LoadFromDataTable(dt, columns);

Download

1042
by rob_dot_net
Mar 27, 2008
2:36 PM

Being evaluated

New "Load" method for ExcelWorksheet class to quickly load the contents of a DataTable to an empty sheet.

I needed to be able to quickly populate an empty sheet with data without the need for any formatting etc... This seems to do the job quite nicely!
Very fast performance... on my box easily loading 10000 rows by 5 columns in less than 1 sec.

Example usage:

DataTable dt = new DataTable();
// some stuff to populate dt...
worksheet.Load(dt);

... That's it! An exception is thrown if the target sheet is already populated.
Hope this helps...
Rob.

10409

Download

125
by jwparker1
Jul 10, 2007
10:29 PM

Being evaluated

Improved write performance on large xlsx files by adding a "Sequential Mode" to the worksheet class that assumes no rows, columns or cells exist in the worksheet. Cells must be added sequentially from left to right, and rows from top to bottom. Sequential mode can only be used on completely empty worksheets. Also changed Cell.Value to object instead of string to eliminate the need for regular expression searches to determine it's data type. Sequential mode improves write performance to about 10,000 rows per second.

Example usage below:

private void CopyDataTableToExcelSheet(DataTable dt, ExcelWorksheet ws)
{
int xlrow = 1, xlcol = 1;

ws.BeginSequentialMode();

//Write data
foreach (DataRow drRow in dt.Rows)
{
xlrow++;
xlcol = 1;
foreach (DataColumn dcCol in dt.Columns)
{
ws.Cell(xlrow, xlcol++).Value = drRow[dcCol];
}
}
ws.EndSequentialMode();
}

I hope I fixed more than I broke =)! Thanks for putting together this great project!

10409

Download

View All