Solved

asp.net mvc

Posted on 2016-08-04
1
39 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
1 Comment
 
LVL 1

Accepted Solution

by:
Moti Mashiah earned 0 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now