• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1444
  • Last Modified:

ASP.NET C# 3.5 :- Ecel File Creation Using OfficeOpenXml

Hi,
   
  I want to create an excel file or want to export repeater contents to an excel file. For that, I opened the question
"http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27373660.html" and I got the solution also.

I downloaded the source / sample code from the URLs http://www.leniel.net/2009/07/creating-excel-spreadsheets-xls-xlsx-c.html and http://npoi.codeplex.com/releases/view/19351. Without any modifications, I exected the code, I got the perfect solution.

Please see the attached code, this is downloaded from above 2 URLs,
 ExcelExport.aspx.cs

Here I saw the line, worksheet.Cell(3, 2).Value = "3" and I tried to chage the value as worksheet.Cell(3, 2).Value = "345". And It was working fine.

Now I chaged the value as
worksheet.Cell(3, 2).Value = "345678965413328";

Now the output is like "3.46E+14". But I want the real correct data without any exponatial. How will I format that?
0
chrisdtrinityphysicians
Asked:
chrisdtrinityphysicians
1 Solution
 
Minh Võ CôngCommented:
You can set format the cell to interger value
0
 
chrisdtrinityphysiciansAuthor Commented:
How? Can you give me some correct sample code? Please.
0
 
chrisdtrinityphysiciansAuthor Commented:
Dear minhvc,

   To implement the Expert Comment ID:36977941, can you give me detailed sample code?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Paulmc999Commented:
wouldn't this be an autosize problem rather than a formatting problem?
0
 
chrisdtrinityphysiciansAuthor Commented:
This is not SIZE problem. Because, I manually changed the size, but no use. If I set Format Cells --> Number --> Number --> Decimal Places ZERO in the excel, then I am getting correct value in excel file.

But I want to do the above process from my ASP.NET C# 3.5 Code. How?
0
 
ltlbearand3Commented:
I believe all you need to do is add this line:
worksheet.Cell(3, 2).NumberFormat = "0";

Open in new window


After:
worksheet.Cell(3, 2).Value = "345678965413328";

Open in new window


-Bear
0
 
chrisdtrinityphysiciansAuthor Commented:
Hi ltlbearand3,
NumberFormat Property Not Avaliable
      Really Sorry. There is no "NumberFormat" property. What will I do?
0
 
ltlbearand3Commented:
Sorry, don't actually have this installed on my machine right now to test more solidly.

Trying to remember.  How about:
worksheet.Cell(3, 2).Style.Numberformat.Format = "#0";
0
 
chrisdtrinityphysiciansAuthor Commented:
Sorry ltlbearand3, it is not working. Because "Style" is a string.
0
 
ltlbearand3Commented:
What references do you have loaded?  Maybe you are using a different reference than me as you are using
worksheet.Cell(3, 2)

Open in new window


When I load the reference I end up with
worksheet.Cells[3, 2]

Open in new window


Here is my simple code that set the cell correctly and allowed the full number to show:
 const string myFileName = "E:\\EETest\\Microsoft Open Office XML\\ExampleData.xlsx";
FileInfo newXLFile = new FileInfo(myFileName);
using (ExcelPackage xlPackage = new ExcelPackage(newXLFile))
{
     // add a new worksheet to the empty workbook
     ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");
     // write some strings into column 1
     worksheet.Cells[1, 1].Value = "12345678901234567890123";
     worksheet.Cells[1, 1].Style.Numberformat.Format = "#0";
     xlPackage.Save();
}

Open in new window

0
 
chrisdtrinityphysiciansAuthor Commented:
Hi ltlbearand3,

    I am thinking, we both are not using same excel package source code. Because, in my code there is no "Cells" property. Here I attached the source code which I downloaded with the help of experts exchange comment. Can you correct that plz?
ExcelStudy5a.zip
0
 
ltlbearand3Commented:
Yes we are using different references.  I downloaded the one you are using and cannot make it work.  You may want to look at switching to this reference - http://epplus.codeplex.com/.  Remove the reference to ExcelPackage in your code and download and add a reference to this dll.  Then try my code above.  Many of the objects are the same - it looks like this EPPlus was built of the reference you are using.

-Bear
0
 
chrisdtrinityphysiciansAuthor Commented:
I will try and let you know later.

Thanks
0
 
chrisdtrinityphysiciansAuthor Commented:
Hi ltlbearand3,

      Next week I will work on it and let you know. Please.
0
 
chrisdtrinityphysiciansAuthor Commented:
I started working on this. Surely we both are using different DLLs. Now I am using the dll suggested by you (http://epplus.codeplex.com/). I will let you know the status within this week-end.
0
 
chrisdtrinityphysiciansAuthor Commented:
Hi ltlbearand3,

     The new dll is supporting the formats. Thanks.

     I read the license details of (http://epplus.codeplex.com/) and they are providing the "GNU General Public License version 2 (GPLv2)" license. So  I am believing that I can customise this code, am I correct?
0
 
ltlbearand3Commented:
Glad the DLL is working.  I do believe it is the GPLv2 License.  However, I am not qualified to comment about how to interpret the license.  I have not needed to customize it myself.
0
 
chrisdtrinityphysiciansAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for chrisdtrinityphysicians's comment http:/Q_27399758.html#37211346

for the following reason:

Thankyou very much
0
 
chrisdtrinityphysiciansAuthor Commented:
No I think, I am wrongly pressed some other buttons. I am happy with this answer and so I want to award the points to Mr. ltlbearand3.
0
 
chrisdtrinityphysiciansAuthor Commented:
Thanks and working fine. If I have any other doubts, I will open next question.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now