export data from sql to excel

try

{

int i = 0;

Workbook myBook = null;

Microsoft.Office.Interop.Excel.Application myApp = null;

Worksheet mySheet = null;

string str = "E:\\excel1.xls";

myApp = new Microsoft.Office.Interop.Excel.Application();

myApp.Visible = false;

myBook = myApp.Workbooks.Add(1);

mySheet = (Worksheet)myBook.Sheets[1];

Application xlApp;

Workbook xlWorkBook;

Worksheet xlWorkSheet;

object misValue = System.Reflection.Missing.Value;

xlApp = new Application();

xlWorkBook = xlApp.Workbooks.Add(misValue);

xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

ImportDataContext db = new ImportDataContext();

xlWorkSheet.Cells[1, 1] = "StoreName";

xlWorkSheet.Cells[1, 2] = "StoreCode";

xlWorkSheet.Cells[1, 3] = "City";

xlWorkSheet.Cells[1, 4] = "Location";

xlWorkSheet.Cells[1, 5] = "Title";

xlWorkSheet.Cells[1, 6] = "MetaDescription";

xlWorkSheet.Cells[1, 7] = "Tag1";

xlWorkSheet.Cells[1, 8] = "Tag2";

xlWorkSheet.Cells[1, 9] = "Tag3";

xlWorkSheet.Cells[1, 10] = "Tag4";

xlWorkSheet.Cells[1, 11] = "Tag5";

xlWorkSheet.Cells[1, 12] = "Tag6";

xlWorkSheet.Cells[1, 13] = "Tag7";

xlWorkSheet.Cells[1, 14] = "Tag8";

xlWorkSheet.Cells[1, 15] = "Tag9";

xlWorkSheet.Cells[1, 16] = "Tag10";

List itemList = new List();

this.StockItemRead(itemList);

myApp.DisplayAlerts = false;

myBook.SaveAs(str, XlFileFormat.xlWorkbookNormal);

myBook.CheckCompatibility = false;

myBook.Save();

myBook.Close(false);

myApp.Quit();

//xlWorkBook.SaveAs("E:\\excel3.xls", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

//xlWorkBook.Close(true, misValue, misValue);

//xlApp.Quit();

}

catch (Exception ex)

{

throw ex;

}

return View();

}

private void StockItemRead(List itemList)

{

try

{

List export = itemList.ToList();

int lastRow = 1;

Worksheet mySheet = null;

int count = 0;

foreach (Storepage ic in export)

{

if (ic.ID > 0)

{

lastRow += 1;

count = count + 1;

mySheet.Cells[lastRow, 1] =ic.StoreName;

mySheet.Cells[lastRow, 2] = ic.StoreCode;

mySheet.Cells[lastRow, 3] = ic.City;

mySheet.Cells[lastRow, 4] = ic.Location;

mySheet.Cells[lastRow, 5] = ic.Title;

mySheet.Cells[lastRow, 6] = ic.MetaDescription;

mySheet.Cells[lastRow, 7] = ic.Tag1;

mySheet.Cells[lastRow, 8] = ic.Tag2;

mySheet.Cells[lastRow, 9] = ic.Tag3;

mySheet.Cells[lastRow, 10] = ic.Tag4;

mySheet.Cells[lastRow, 11] = ic.Tag5;

mySheet.Cells[lastRow, 12] = ic.Tag6;

mySheet.Cells[lastRow, 13] = ic.Tag7;

mySheet.Cells[lastRow, 14] = ic.Tag8;

mySheet.Cells[lastRow, 15] = ic.Tag9;

mySheet.Cells[lastRow, 16] = ic.Tag10;

}

catch(Exception e)

{

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp10y ago
Accepted answer
Hi,
You are not adding any items to your "itemList".
You are just declaring "itemList"and then passing it to your"StockItemRead" function.
Please check your code and assign items to "itemList".
alice yang10y ago
Hi,
Check the following link:
http://www.c-sharpcorner.com/UploadFile/83fe73/solutions-to-export-data-from-database-to-excel-in-C-Sharp/
Midhun Tp10y ago
Hi,
Actually I'm also not an expert with mvc & linq. You can refer below articles for exporting sql data to excel using linq -

http://www.c-sharpcorner.com/UploadFile/70dbe6/export-to-excel-data-by-linq-to-sql/

http://mvc4beginner.com/Sample-Code/ImportExportExcelData/MVC4-Export-Data-to-Excel.html
Nagarjuna Naga10y ago
but i am new to mvc ..and also linq tpo sql can you give in a code foramte
Nagarjuna Naga10y ago
in this
private void StockItemRead(List itemList)
{
itemlist is not taking values...count=0 coming ..please tell me