Solved

asp.net mvc

Posted on 2016-08-04
1
74 Views
Last Modified: 2016-08-05
Hi Guys,

I'm exporting data from my app to excel spreadsheet and I'm using "Microsoft.Office.Interop.Excel".

Users search for items then when the get the items in the view they click on the button below end export all data to excel.
 <a href="@Url.Action("Exporttoexcel", "Excelgenerator")">Export Excel</a>

Open in new window


All works fine so far.

Now I will give you an example of my issue:

Let's say I format one column to make all cells NumberFormat look example below:
 var rang_currencyprice = worksheet.get_Range("C2", "C16");
            rang_currencyprice.NumberFormat = "$* #,##0.00";

Open in new window


The case above you can see that I gave range between C2 to C16, but in my case I need to give these values dynamically as I don't know how many rows user query before he export to excel.

1. How can I do this "worksheet.get_Range("C2", "C16");" to work dynamically.
2. Also I would like to know how do I show the excel file in the bottom of my browser after user done exporting.

Here is my full code for exporting:
        public ActionResult Exporttoexcel()
        {
            Itemmodel itm = new Itemmodel();
            try
            {
                Excel.Application application = new Excel.Application();
                Excel.Workbook workbook = application.Workbooks.Add(System.Reflection.Missing.Value);
                Excel.Worksheet worksheet = workbook.ActiveSheet;

                worksheet.Cells[1, 1] = "Itemlookup";
                worksheet.Cells[1, 2] = "Description";
                worksheet.Cells[1, 3] = "Price";
                worksheet.Cells[1, 4] = "Cost";
                int row = 2;
                foreach(var it in itm.Findall())
                {
                    worksheet.Cells[row, 1] = it.Itemlookup;
                    worksheet.Cells[row, 2] = it.Description;
                    worksheet.Cells[row, 3] = it.Price;
                    worksheet.Cells[row, 4] = it.Cost;
                    row++;
                }
                Formatexcel(worksheet);

                workbook.SaveAs("d:\\test\\Item_list.xlsx");
                workbook.Close();
                Marshal.ReleaseComObject(workbook);
                application.Quit();
                Marshal.FinalReleaseComObject(application);

            }
            catch(Exception ex)
            {
               ViewBag.message = ex.Message;
            }
            return RedirectToAction("Index");
        }

        public void Formatexcel(Excel.Worksheet worksheet)
        {
            //Format Cells in loop
            worksheet.get_Range("A1", "D1").EntireColumn.AutoFit();

            //Format Heading
            var range_heading = worksheet.get_Range("A1", "D1");
            range_heading.Font.Bold = true;
            range_heading.Font.Color = Color.Red;
            range_heading.Font.Size = 13;

            //Format Currency
            //column price
            var rang_currencyprice = worksheet.get_Range("C2", "C16");
            rang_currencyprice.NumberFormat = "$* #,##0.00";

            //column cost
            var rang_currencycost = worksheet.get_Range("D2", "D16");
            rang_currencycost.NumberFormat = "$* #,##0.00";

            //Format Date
            //var range_date = worksheet.get_Range("A1", "D1");
            //range_date.NumberFormat = "mm/dd/yyyy";
        }

Open in new window


Thanks,
0
Comment
Question by:Moti Mashiah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
Moti Mashiah earned 0 total points
ID: 41743633
Sorry, Guys I found the solution for question one:
I just did something like this:
            var rang_currencyprice = worksheet.get_Range("C2", "C"+ row);
            rang_currencyprice.NumberFormat = "$* #,##0.00";

Open in new window


I count the rows and send it to the controller.

Please, if anybody can answer on  question 2 it will be great.
2.  2. Also I would like to know how do I show the excel file in the bottom of my browser after user done exporting.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question