Remove From My Forums

export-data-to-multiple-excel-sheets

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

0

Sign in to vote

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
0

Sign in to vote

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

Asked by:

export-data-to-multiple-excel-sheets

Question

All replies