ExcelPackage: Office Open XML Format file creation

New Post: Integer Bug

_lowlevel_ — Tue, 02 Sep 2008 19:48:19 GMT

Hello,

first of all I want to say that ExcelPackage is very good and I like working with it.
However, I discovered a bug. The situation: I want to create a list of material. Each entry represents an article and consists of description, hyperlink and article number.

Example: Resistor http://www.internet.com/resistor XXX123

Now there was an article number of following style: 0122.2124893.0293.33. The Funtion IsNumericValue implemented in ExcelCell.cs declares this as a number. I think it is necessary to change the regular expression:

Regex

objIntPattern = new Regex("^-[0-9,.]+$|^[0-9,.]+$"); // ExcelCell.cs, Function: IsNumericValue, Line: 440

to

Regex

objIntPattern = new Regex("^-[0-9]+[,.]?[0-9]+$|^[0-9]+[,.]?[0-9]+$");

to ensure that there is only one comma or dot between a various count of numbers.

New Post: Help inlcuing new version of ExcelWorksheets.cs

davidw567 — Mon, 01 Sep 2008 14:22:26 GMT

Hello
I have downloaded a new version of ExcelWorksheets.cs to reduce final file size.
This is from Rob Stanbridge, ID 1041. http://www.codeplex.com/ExcelPackage/SourceControl/PatchList.aspx
He has changed the namespace, any reason for that?
When using this file instead of the original the project does not compile.

Any suggestions.

David

Commented Issue: System.ApplicationException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) exception

szmulder — Thu, 28 Aug 2008 21:53:55 GMT

Any idea how to fix this exception? My program get a datatable from sql DB and save all fields to excel file.
Sometime will get following error

System.ApplicationException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
at System.IO.IsolatedStorage.IsolatedStorageFile.nGetRootDir(IsolatedStorageScope scope)
at System.IO.IsolatedStorage.IsolatedStorageFile.InitGlobalsNonRoamingUser(IsolatedStorageScope scope)
at System.IO.IsolatedStorage.IsolatedStorageFile.GetRootDir(IsolatedStorageScope scope)
at System.IO.IsolatedStorage.IsolatedStorageFile.GetGlobalFileIOPerm(IsolatedStorageScope scope)
at System.IO.IsolatedStorage.IsolatedStorageFile.Init(IsolatedStorageScope scope)
at System.IO.IsolatedStorage.IsolatedStorageFile.GetStore(IsolatedStorageScope scope, Type domainEvidenceType, Type assemblyEvidenceType)
at MS.Internal.IO.Packaging.PackagingUtilities.ReliableIsolatedStorageFileFolder..ctor()
at MS.Internal.IO.Packaging.PackagingUtilities.GetDefaultIsolatedStorageFile()
at MS.Internal.IO.Packaging.PackagingUtilities.CreateUserScopedIsolatedStorageFileStreamWithRandomName(Int32 retryCount, String& fileName)
at MS.Internal.IO.Packaging.SparseMemoryStream.EnsureIsolatedStoreStream()
at MS.Internal.IO.Packaging.SparseMemoryStream.SwitchModeIfNecessary()
at MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at MS.Internal.IO.Packaging.CompressStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)
at System.IO.StreamWriter.Write(String value)
at System.Xml.XmlTextEncoder.Write(String text)
at System.Xml.XmlTextWriter.WriteString(String text)
at System.Xml.XmlAttribute.WriteContentTo(XmlWriter w)
at System.Xml.XmlAttribute.WriteTo(XmlWriter w)
at System.Xml.XmlElement.WriteTo(XmlWriter w)
at System.Xml.XmlElement.WriteContentTo(XmlWriter w)
at System.Xml.XmlElement.WriteTo(XmlWriter w)
at System.Xml.XmlElement.WriteContentTo(XmlWriter w)
at System.Xml.XmlElement.WriteTo(XmlWriter w)
at System.Xml.XmlElement.WriteContentTo(XmlWriter w)
at System.Xml.XmlElement.WriteTo(XmlWriter w)
at System.Xml.XmlDocument.WriteContentTo(XmlWriter xw)
at System.Xml.XmlDocument.Save(Stream outStream)
at OfficeOpenXml.ExcelWorksheet.Save()
at OfficeOpenXml.ExcelWorkbook.Save()
at xxx.addDataSetToExcel(DataSet DataSetSource, String fileName, String title, String subject, String category, String comments, String fileType)
Comments: ** Comment from web user: szmulder **

Now one have same problem?

New Post: Pivot Table

someshbaladi — Tue, 19 Aug 2008 13:42:38 GMT

How to set range for pivot table programatically.?
I have a template with Pivot table on other sheet but not able to set the range since the data is dynamically populated.
since we wont be knowing how many rows ,we cant set the range in template . can anybody help me on this.......?

New Post: Strings with ' in them

atmacam — Wed, 13 Aug 2008 18:33:23 GMT

Never mind.

My mistake, had missed the line underneath for setting the inner text also. You may use the line below instead. Thanks.

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

New Post: Stream Instead Of File?

camcombs — Mon, 11 Aug 2008 18:27:46 GMT

I'm going to reply to myself (above) after getting the answer from Ken Tucker. The correct mime type to add to IIS is

xlsx
application/vnd.openxmlformats

New Post: Cannot Download Source Code

iDesmet — Mon, 11 Aug 2008 07:08:05 GMT

socrates877,

You can use ExML is a fork of ExcelPackage. slide_o_mix is doing a great job; he is currently working on some optimizations.

New Post: Cannot Download Source Code

socrates877 — Sun, 10 Aug 2008 12:30:15 GMT

I have this exact problem too. SVNBridge and Torstoise SVN work just fine for me; I can see all the other projects on the server. However, the ExcelPackage folder is empty. So it seems to me that this is an Excel Package problem. Would someone be ever so kind and upload your checkout of the source code? It's got to be better than using the January 4, 2007 release, right?

New Post: Strings with ' in them

atmacam — Fri, 08 Aug 2008 20:27:26 GMT

Considering special chars in an Xml Doc are {'&', '<', '>', '\"', '\''}, it would be safer to add the lines below before using it in the method.

if(string.IsNullOrEmpty(Value))
Value = Value.Replace("&", "&").Replace("<", "<").Replace(">", ">").Replace("\"", """).Replace("\'", "'");

New Post: Stream Instead Of File?

camcombs — Mon, 04 Aug 2008 19:25:25 GMT

I used Ken Tucker's sample and source code changes. The problem I have now is that IE complains about an invalid file extension. I've added this to the MIME types for my localhost IIS 6 properties:

xlsx
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Is that the correct MIME type for this solution?

Anybody else have this problem using Ken's sample?

thanks in advance

New Post: Reading and Writting Excel File to Server.

varshavmane — Fri, 01 Aug 2008 04:39:28 GMT

Thanks BradRoberts. The problem was coming because I was having Excel 2003 now its not giving that error. But still I am not able to show that excel file on Browser and when client do any changes in that excel it must be save back to server.

Please help me with this.

Thanks again...

New Post: Reading and Writting Excel File to Server.

BradRoberts56 — Thu, 31 Jul 2008 18:51:15 GMT

FYI, something simple can make the corrupt data thing happen. For example, the maximum length of a WorkSheet name is 30 characters. This kind of thing is not enforced when you create a spreadsheet with ExcelPackage as it is.

New Post: Excel complains German date format

Ariadne — Thu, 31 Jul 2008 15:22:26 GMT

The German date format is of the form dd.mm.yyyy
if I use the following statement
worksheet.Cell(1,1)="1.1.2008"
Excel complains bad XML (but is able to correct it)

imho the problem is, that in ExcelCells.cs Line 147
if (IsNumericValue(value))
with value="1.1.2008"
is true
and that may be misleading.
Ariadne, Switzerland

Created Issue: System.ApplicationException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) exception

szmulder — Wed, 30 Jul 2008 02:15:30 GMT

New Post: Reading and Writting Excel File to Server.

varshavmane — Fri, 25 Jul 2008 06:38:38 GMT

Hello All,

I am using ASP.NET 2005 (C#). I using Excel Package dll for reading and writting excel file.
I have to generate excel file and save to server.
I m not able to read excel file giving error of "File contains corrupted data".
Also I have make some cells as readonly.
Finally whenever client opens that file in browser he can change the values in any cell which are not marked readonly and then on save button click event that file must be saved back to server.
Any suggestion.
Please help me. Thanks in advance.

New Post: Is it possible to get the name of the cell??

spongebob — Mon, 21 Jul 2008 19:06:44 GMT

I need to parse the excel based on the cell name to differentiate whether the value is a group name or is it a item??

Commented Issue: speed-up writing large xlsx files

BradRoberts56 — Tue, 15 Jul 2008 18:00:10 GMT

I have noted that when writing more than 1000 rows dramtically slowes down the document generation.
I have a fast computer with etc etc and it only writes 160 rows per minute whihc is too slow for me.

Is it possible to speed up the writespeed??
would it be possible to specify the range to update and write to the whole range at the same time?

I would appreichiate a soloution

Thanx
Magnus
Comments: ** Comment from web user: BradRoberts56 **

Many thanks for the patch by rob_dot_net. That solved MY problem. Boy, that was fast!

Commented Issue: File contains corrupted data

AndreZA — Tue, 08 Jul 2008 08:54:47 GMT

I just downloaded the most recent version of the binary. We have a costing spreadsheet that we use to build proposals. Unfortunately I cannot attach the file. It was created in Excel 2000. I opened it up in 2007 and saved as a 2007 xlsx file. When opening it, I get the error "File contains corrupted data". I can open the xlsx file in Excel 2007 fine with no issues. I think the problem is that certain areas of some of the sheets have restrictions by Excel password protection. I took the same file from Excel 2000, removed protection, saved to XML spreadsheet format and then to Excel 2007 format and it opened as it should in my code. Am I right to assume this error is a password protection issue? Most of the cells are writable, the formulas are what are protected.
Comments: ** Comment from web user: AndreZA **

no hotfix/workaround on this one? I have the same problem with a protected excel 2003 file that was converted to xlsm.

Commented Issue: Support for international settings

SoerenNielsen — Sat, 05 Jul 2008 19:32:35 GMT

In some countries comma "," is used instead of "." as decimal separator. This may provoke mistakes in recognizing numbers with decimals when a value is assigned to a cell.
Comments: ** Comment from web user: SoerenNielsen **

Second that. In addition the thousand number separator is also switched.

Don't know how to best fix this. Either use a canonical representation (are you doing that now?) or (more correct) convert it to the current threads locale.

New Post: How to filter invaild XML char?

slide_o_mix — Tue, 01 Jul 2008 21:32:41 GMT

szmulder wrote:
When i recieved data from sql, always get some char like


And using when i open the excel file create by ExcelPackage
It's shows error "Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Illegal xml character. Line 92954, column 25."
and i check the char is 

 is a vertical tab control character...I wonder how that is getting into your SQL Database in the first place.