Moti Mashiah
asked on
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.
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:
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:
Thanks,
I'm exporting data from my app to excel spreadsheet and I'm using "Microsoft.Office.Interop.
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",
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.