You must Sign In to post a response.
  • Category: ASP.NET

    How to save database date in to excel using foreach loop

    In table record as follows

    Select * from Employee

    Cityid AssName ProjectName Mobile Source Destination
    1 Rakesh Java 987445 TK BLR
    1 Suresh Dotnet 884554 RM BTP
    1 Vignesh Testing 451211 RP KOL
    1 Suresh Mainframe 457845 RF KOL

    2 Ramesh Animation 454542 JS KOC
    2 Magesh Warehouse 211455 WH KOC
    2 Santhosh Database 445545 RO CHN
    2 Vignesh ETLTool 154555 VJ CHN


    console application code as follows

    i am displaying the above data from table Employee(Database) in to excel file using console application.

    My console application code as follows


    string connectionstring = "Server=(local);initial catalog=OneC;Trusted_Connection=True";
    SqlConnection con = new SqlConnection(connectionstring);
    SqlCommand command= new SqlCommand();
    SqlDataReader dr;
    DataSet ds= new DataSet();
    command.CommandText = "Select * from Employee";
    command.CommandType = CommandType.Text;
    command.Connection = con;
    con.Open();
    dr= cmd.ExecuteReader();
    if (dr.HasRows)
    {

    using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"C:\Details\Excel.xls"))
    {
    while (dr.Read())
    {
    for (int i = 0; i < dr.FieldCount; i++)
    {
    sw.Autoflush = true;
    sw.write(dr[i].Tostring() + "\t");
    }
    sw,writeline("\n");
    }
    }
    con.Close();
    }


    When i exeucte above query in my system in c folder under the folder details excel file is created as follows

    in my system C Folder created as details in that details folder excel file is created a
    1 Rakesh Java 987445 TK BLR
    1 Suresh Dotnet 884554 RM BTP
    1 Vignesh Testing 451211 RP KOL
    1 Suresh Mainframe 457845 RF KOL

    2 Ramesh Animation 454542 JS KOC
    2 Magesh Warehouse 211455 WH KOC
    2 Santhosh Database 445545 RO CHN
    2 Vignesh ETLTool 154555 VJ CHN


    But i want excel file to be saved based on city id. The city id 1 details to be saved in one excel

    And another city id 2 details to be saved in another excel


    for that how can i do in asp.net using my above code in console application

    I want City id 1 details to be saved in one excel as follows

    1 Rakesh Java 987445 TK BLR
    1 Suresh Dotnet 884554 RM BTP
    1 Vignesh Testing 451211 RP KOL
    1 Suresh Mainframe 457845 RF KOL


    I want City id 2 details to be saved in another excel as follows

    2 Ramesh Animation 454542 JS KOC
    2 Magesh Warehouse 211455 WH KOC
    2 Santhosh Database 445545 RO CHN
    2 Vignesh ETLTool 154555 VJ CHN
  • #767543
    Hi
    rao

    try this code working good



    string connectionstring = "Server=ADMIN-PC;initial catalog=Test;Trusted_Connection=True";
    string sFilename = "";
    SqlConnection con = new SqlConnection(connectionstring);
    SqlCommand command = new SqlCommand();
    SqlDataReader dr;
    DataSet ds = new DataSet();
    command.CommandText = "Select * from Employee";
    command.CommandType = CommandType.Text;
    command.Connection = con;
    con.Open();
    dr = command.ExecuteReader();
    if (dr.HasRows)
    {
    while (dr.Read())
    {
    string a = dr[0].ToString();

    if(a=="1")
    {
    sFilename = "Data1.xls";
    }
    else if (a == "2")
    {
    sFilename = "Data2.xls";
    }
    using (System.IO.StreamWriter sw = new System.IO.StreamWriter(@"D:\Details\"+ sFilename))
    {
    for (int i = 0; i < dr.FieldCount; i++)
    {
    sw.AutoFlush = true;
    sw.Write(dr[i].ToString() + "\t");
    //sw.Autoflush = true;
    //sw.write(dr[i].Tostring() + "\t");
    }
    //sw,writeline("\n");
    sw.WriteLine("\n");
    }
    }
    con.Close();
    }
    }

    Name : Dotnet Developer-2015
    Email Id : kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #767547
    Simply you can fetch your data in dataset and then with the help of C# Excel Interop object, see below code

    static void Main(string[] args)
    {
    Program p = new Program();

    //Create an Emplyee DataTable
    DataTable employeeTable = new DataTable("Employee");
    employeeTable.Columns.Add("Employee ID");
    employeeTable.Columns.Add("Employee Name");
    employeeTable.Rows.Add("1", "ABC");
    employeeTable.Rows.Add("2", "DEF");
    employeeTable.Rows.Add("3", "PQR");
    employeeTable.Rows.Add("4", "XYZ");

    //Create a Department Table
    DataTable departmentTable = new DataTable("Department");
    departmentTable.Columns.Add("Department ID");
    departmentTable.Columns.Add("Department Name");
    departmentTable.Rows.Add("1", "IT");
    departmentTable.Rows.Add("2", "HR");
    departmentTable.Rows.Add("3", "Finance");

    //Create a DataSet with the existing DataTables
    DataSet ds = new DataSet("Organization");
    ds.Tables.Add(employeeTable);
    ds.Tables.Add(departmentTable);

    p.ExportDataSetToExcel(ds);
    }

    /// <summary>
    /// This method takes DataSet as input paramenter and it exports the same to excel
    /// </summary>
    /// <param name="ds"></param>
    private void ExportDataSetToExcel(DataSet ds)
    {
    //Creae an Excel application instance
    Excel.Application excelApp = new Excel.Application();

    //Create an Excel workbook instance and open it from the predefined location
    Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx");

    foreach (DataTable table in ds.Tables)
    {
    //Add a new worksheet to workbook with the Datatable name
    Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
    excelWorkSheet.Name = table.TableName;

    for (int i = 1; i < table.Columns.Count + 1; i++)
    {
    excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
    }

    for (int j = 0; j < table.Rows.Count; j++)
    {
    for (int k = 0; k < table.Columns.Count; k++)
    {
    excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
    }
    }
    }

    excelWorkBook.Save();
    excelWorkBook.Close();
    excelApp.Quit();

    }


    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #767567
    Hi,

    We have no of articles available in Google, have you checked?

    First Google it, then you come to know the solution, please refer below article "aspsnippets.com/Articles/Export-GridView-to-Excel-in-ASPNet-with-Formatting-using-C-and-VBNet.aspx"

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #767599
    Hi,

    Check this article which demonstrated 4 solutions to exporting data along with specific data from database to excel:
    http://www.c-sharpcorner.com/UploadFile/83fe73/solutions-to-export-data-from-database-to-excel-in-C-Sharp/


  • Sign In to post your comments