locked
export-data-to-multiple-excel-sheets RRS feed

  • Question

  • User1132102078 posted

    How can I export data to excel in sheet 2, without using Interop

    Current code,

    Response.AppendHeader("content-disposition", "attachment;filename=ExportedExcel.xls");
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.ms-excel";
                                    
    
    this.EnableViewState = false;
    string str = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>";
    str += Information;
    str += "</body></html>";
    Response.Write(str);
    
    Response.End();

    Where can I change to add an extra information in sheet 2? 

    //Here I added extra sheet. Bu I am not able to add the information to the extra sheet I created. Where should I add from my previous code shown
    
    "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>
    <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>" + worksheet + "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet><x:ExcelWorksheet><x:Name>Hello</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>";
    



    Thank You,

    Thursday, March 17, 2016 3:47 AM

All replies

  • User269602965 posted

    Use OLEDB in code behind web page to insert data into one or more sheets.

    general concept is connect to the Excel file (yes permissions required),

    then update a sheet and cell address with data

    then update another sheet and cell address with other data

    With datasets, you can update RANGE of cells (not shown here)

      Private Sub btnUpdateXLS_Click(sender As System.Object, e As System.Windows.RoutedEventArgs) Handles btnUpdateXLS.Click
    
    ' Note this OLEDB driver ver 12.0 referecne is for XLSX versions of Excel, for older XLS you would use another driver
    
        Dim strDir   As String = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetModules()(0).FullyQualifiedName)
        Dim strXLSX   As String = strDir & "\{YOURSPREADSHEET}.xlsx"
        Dim strData1 As String = "DataPage1"
        Dim strData2 As String = "DataPage2"
    
        Try
          Dim strOLEDBConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strXLSX & ";" & "Extended Properties='Excel 12.0 Macro;HDR=NO;'"
          Using OLEDBConn As New OleDbConnection(strOLEDBConn)
            OLEDBConn.Open()
            Using cmdUpdate As OleDbCommand = OLEDBConn.CreateCommand()
              ' here you define the first sheet and cell to insert the data
              cmdUpdate.CommandText = "UPDATE [{SHEET1NAME}$A1:A1] SET F1='" & strData1 & "'"
              cmdUpdate.ExecuteNonQuery()
            End Using
            Using cmdUpdate As OleDbCommand = OLEDBConn.CreateCommand()
              ' here you define the second sheet and cell to insert the data
              cmdUpdate.CommandText = "UPDATE [{SHEET2NAME}$A1:A1] SET F1='" & strData2 & "'"
              cmdUpdate.ExecuteNonQuery()
            End Using
          End Using
        Catch oledbex As OleDbException
          MsgBox("Error: OLEDB failed to update worksheet." & oledbex.ToString, MsgBoxStyle.OkOnly, "Error: OLEDB error logged")
        End Try
      End Sub
    

    Friday, March 18, 2016 3:10 AM
  • User1132102078 posted

    Thank You for your respond. 

    If possible, I would like to use HTML to generate Excel. Cos my whole coding generates HTML table. 

    Thank You

    Friday, March 18, 2016 4:17 AM
  • User269602965 posted

    You can use OLEDB in code behind page to export a data set from your HTML table or DATAGRID to Excel sheet(s) to a range of cells or to a beginning cell.

    There are also many third party libraries by TELERIK, SPREADSHEET GEAR, and other vendors that provide rich export of data grids to excel capabilities.

    I have used the first two vendor libraries in WinForms, WPF, and ASP.NET applications.

    Friday, March 18, 2016 5:32 PM