troubleshooting Question

asp.net mvc

Avatar of Moti Mashiah
Moti MashiahFlag for Canada asked on
ASP.NETC#.NET Programming
1 Comment1 Solution124 ViewsLast Modified:
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>

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";

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";
        }

Thanks,
ASKER CERTIFIED SOLUTION
Moti Mashiah
.NET Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros