Solved

asp.net mvc

Posted on 2016-08-04
1
64 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
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

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…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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