Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

asp.net mvc

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
Moti Mashiah
Asked:
Moti Mashiah
1 Solution
 
Moti Mashiah.NET DeveloperAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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