Nagarjuna Naga

Nagarjuna Naga

  • 2.1k
  • 53
  • 6.1k

export data from sql to excel

Oct 26 2016 12:33 AM
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<Storepage> itemList = new List<Storepage>();
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<Storepage> itemList)
{
try
{
List<Storepage> 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)
{
}
}
}
}

Answers (5)