Exporting to multiple excel spreadsheets. RSS

• • Reply
  

  accyboy1981

  Member

  24 Points

  25 Posts

  Exporting to multiple excel spreadsheets.

  Jan 17, 2008 04:03 PM|LINK

  Hi,

  I want to export the contents of a gridview to an excel document. However the problems is there are too many columns in the gridview than Excel can handle. The code I normally use to export to excel is as follows:

  Response.Clear();
  
  
  		Response.AddHeader( "content-disposition", "attachment;filename=" + fileName );
  
  
  		Response.Charset = "";
  
  
  		Response.ContentType = "application/vnd.xls";
  
  
  		System.IO.StringWriter stringWrite = new System.IO.StringWriter();
  
  
  		System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter( stringWrite );
  
  
  		GridView exportGridView = new GridView();
  
  
  		// binds to gridview
  		exportGridView.DataSource = GetGridViewDataTable();
  		exportGridView.DataBind();
  
  
  		exportGridView.RenderControl( htmlWrite );
  
  
  		Response.Write( stringWrite.ToString() );
  
  
  		Response.End();

  However when I try to run the above code twice, once for the first half of the table, second time for the remaining columns, only first Excel spreadsheet is produced and the code ends. Is there anyway I can run the above code twice on one button click or is there an alternative method?

  Thanks in advance.

  Simon 

  Excel

• • Reply
  

  krishnada25

  Member

  631 Points

  906 Posts

  Re: Exporting to multiple excel spreadsheets.

  Jan 17, 2008 04:51 PM|LINK

  Could you check whether you have Included the event handlers in you code??

  Please Don't mark the Question I posted as Answered until I do it.
  
  Thanks,
  Kris
• • Reply
  

  krishnada25

  Member

  631 Points

  906 Posts

  Re: Exporting to multiple excel spreadsheets.

  Jan 17, 2008 04:52 PM|LINK

  and if your code works please post it I wants to learn how to do that.

  Thanks.

  Please Don't mark the Question I posted as Answered until I do it.
  
  Thanks,
  Kris
• • Reply
  

  Vince Xu - M...

  All-Star

  80367 Points

  6801 Posts

  Re: Exporting to multiple excel spreadsheets.

  Jan 21, 2008 12:58 AM|LINK

  Hi,

  Based on my understanding, you want to split the datasource into two table. But only first table can be generated.

  I'm not sure that you want to export them into one excel file or two different excel file, into one sheet of excel or two. On matter what scenario you want, you can use the below codes to export the excel from dataset directly and it can be handle more conveniently. You can make use of it to split columns of table into two different sheet. After the below codes, you can export the excel file to the client by using response stream.

  private void ConvertToXls()
      {
       try
          {
  
  
              DataSet dsBook = GetGridViewDataSet();//Get the DataSet from your DataSource
              int rows = dsBook.Tables[0].Rows.Count + 1;
              int cols = dsBook.Tables[0].Columns.Count;
            
  
              string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "a.xls");
              if (File.Exists(ExcelFileName))
              {
                  File.Delete(ExcelFileName);
              }
              StreamWriter writer = new StreamWriter(ExcelFileName, false);
              writer.WriteLine("<?xml version=\"1.0\"?>");
              writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
              writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
              writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
              writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
              writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
              writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
              writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">;");
              writer.WriteLine("  <Author>Automated Report Generator Example</Author>");
              writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
              writer.WriteLine("  <Company>51aspx.com</Company>");
              writer.WriteLine("  <Version>11.6408</Version>");
              writer.WriteLine(" </DocumentProperties>");
              writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
              writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
              writer.WriteLine("  <WindowTopX>480</WindowTopX>");
              writer.WriteLine("  <WindowTopY>15</WindowTopY>");
              writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
              writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
              writer.WriteLine(" </ExcelWorkbook>");
              writer.WriteLine(" <Styles>");
              writer.WriteLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
              writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\"/>");
              writer.WriteLine("   <Borders/>");
              writer.WriteLine("   <Font/>");
              writer.WriteLine("   <Interior/>");
              writer.WriteLine("   <Protection/>");
              writer.WriteLine("  </Style>");
              writer.WriteLine("  <Style ss:ID=\"s21\">");
              writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
              writer.WriteLine("  </Style>");
              writer.WriteLine(" </Styles>");
              writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
              writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
              writer.WriteLine("   x:FullRows=\"1\">");
  
  
              //generate title
              writer.WriteLine("<Row>");
              foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)  // you can write a half columns of table and put the remaining columns in sheet2
              {
                  writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                  writer.Write(eachCloumn.ColumnName.ToString());
                  writer.WriteLine("</Data></Cell>");
              }
              writer.WriteLine("</Row>");
  
  
              //generate data
              foreach (DataRow eachRow in dsBook.Tables[0].Rows)
              {
                  writer.WriteLine("<Row>");
                  for (int currentRow = 0; currentRow != cols; currentRow++)
                  {
                      writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                      writer.Write(eachRow[currentRow].ToString());
                      writer.WriteLine("</Data></Cell>");
                  }
                  writer.WriteLine("</Row>");
              }
              writer.WriteLine("  </Table>");
              writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine("   <Selected/>");
              writer.WriteLine("   <Panes>");
              writer.WriteLine("    <Pane>");
              writer.WriteLine("     <Number>3</Number>");
              writer.WriteLine("     <ActiveRow>1</ActiveRow>");
              writer.WriteLine("    </Pane>");
              writer.WriteLine("   </Panes>");
              writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine("  </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
              writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine("  </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
              writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine("  </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine("</Workbook>");
              writer.Close();
              Response.Write("<script language=\"javascript\">" + "alert('" + "convert completed!')" + "</script>");
          }
          catch (Exception ex)
          {
              Response.Write("<script language=\"javascript\">" + "alert('" + "error! " + ex.Message + "')" + "</script>");
          }
      }

   Hope it helps.

   

  If you have any feedback about my replies, please contact msdnmg@microsoft.com
  • Edited by Vince Xu - MSFT on Jan 23, 2008 05:18 AM
  • Edited by Vince Xu - MSFT on Jan 23, 2008 08:24 AM
  • Edited by Vince Xu - MSFT on Jan 24, 2008 07:35 AM
  • Marked as answer by Vince Xu - MSFT on Jan 25, 2008 08:04 AM
• • Reply
  

  nmandaliya

  Member

  2 Points

  1 Post

  Re: Exporting to multiple excel spreadsheets.

  Jan 29, 2008 06:14 AM|LINK

   great... I got good solution for generate Excel with multiple sheet without EXCEL Object.

  But What if I have to insert IMAGE in Excel File like Logo...?

   

  thanks in advance 

• • Reply
  

  shabbir_hsn

  Member

  2 Points

  1 Post

  Re: Exporting to multiple excel spreadsheets.

  May 29, 2008 11:40 PM|LINK

  Hi,

  thnks for great support,

  but still i am getting some problems with excel export

  i need to export an image into excel sheet.

  
   

• • Reply
  

  gnanam02

  Member

  425 Points

  90 Posts

  Re: Exporting to multiple excel spreadsheets.

  Nov 05, 2008 09:16 AM|LINK

  Really its good post

   

  If this post helps you, please mark it as Answer.
  
  
  Gnana Prakash S
  Software Engineer
  Coimbatore
  http://www.sgnanaprakash.com
• • Reply
  

  Kirti.Darji....

  Member

  2 Points

  1 Post

  Re: Exporting to multiple excel spreadsheets.

  Sep 10, 2009 01:02 PM|LINK

   

  H have problem when generated file open

  probelm come up with following areas during load

  Table...

  this file can not be open because of errors

  Regards

  Kirti Darji

   

   

   

• • Reply
  

  BarbaMarioli...

  Member

  208 Points

  136 Posts

  Re: Exporting to multiple excel spreadsheets.

  Nov 02, 2009 10:04 AM|LINK

  I think that you are using wrong way of exporting to Excel. You are basically fooling browser to redirect request to Excel application although underlying data is HTML.

  Excel is known to have some problems when importing HTML content, so the cleanest way of exporting to Excel is to generate Excel file on server and send it to client.

  You should check out: web sample demonstrates CSV/XLS/XLSX/ODS/HMTL writing using GemBox.Spreadsheet
  

  
  

• • Reply
  

  Peter Cong

  Member

  527 Points

  681 Posts

  Re: Exporting to multiple excel spreadsheets.

  Oct 19, 2011 03:05 AM|LINK

  Vince Xu - MSFT

  Hi,

  Based on my understanding, you want to split the datasource into two table. But only first table can be generated.

  I'm not sure that you want to export them into one excel file or two different excel file, into one sheet of excel or two. On matter what scenario you want, you can use the below codes to export the excel from dataset directly and it can be handle more conveniently. You can make use of it to split columns of table into two different sheet. After the below codes, you can export the excel file to the client by using response stream.

  private void ConvertToXls()
      {
       try
          {
  
              DataSet dsBook = GetGridViewDataSet();//Get the DataSet from your DataSource
              int rows = dsBook.Tables[0].Rows.Count + 1;
              int cols = dsBook.Tables[0].Columns.Count;
            
              string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "a.xls");
              if (File.Exists(ExcelFileName))
              {
                  File.Delete(ExcelFileName);
              }
              StreamWriter writer = new StreamWriter(ExcelFileName, false);
              writer.WriteLine("<?xml version=\"1.0\"?>");
              writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
              writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
              writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
              writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
              writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
              writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
              writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">;");
              writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
              writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
              writer.WriteLine(" <Company>51aspx.com</Company>");
              writer.WriteLine(" <Version>11.6408</Version>");
              writer.WriteLine(" </DocumentProperties>");
              writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
              writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
              writer.WriteLine(" <WindowTopX>480</WindowTopX>");
              writer.WriteLine(" <WindowTopY>15</WindowTopY>");
              writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
              writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
              writer.WriteLine(" </ExcelWorkbook>");
              writer.WriteLine(" <Styles>");
              writer.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
              writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>");
              writer.WriteLine(" <Borders/>");
              writer.WriteLine(" <Font/>");
              writer.WriteLine(" <Interior/>");
              writer.WriteLine(" <Protection/>");
              writer.WriteLine(" </Style>");
              writer.WriteLine(" <Style ss:ID=\"s21\">");
              writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
              writer.WriteLine(" </Style>");
              writer.WriteLine(" </Styles>");
              writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
              writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
              writer.WriteLine(" x:FullRows=\"1\">");
  
              //generate title
              writer.WriteLine("<Row>");
              foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)  // you can write a half columns of table and put the remaining columns in sheet2
              {
                  writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                  writer.Write(eachCloumn.ColumnName.ToString());
                  writer.WriteLine("</Data></Cell>");
              }
              writer.WriteLine("</Row>");
  
              //generate data
              foreach (DataRow eachRow in dsBook.Tables[0].Rows)
              {
                  writer.WriteLine("<Row>");
                  for (int currentRow = 0; currentRow != cols; currentRow++)
                  {
                      writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
                      writer.Write(eachRow[currentRow].ToString());
                      writer.WriteLine("</Data></Cell>");
                  }
                  writer.WriteLine("</Row>");
              }
              writer.WriteLine(" </Table>");
              writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine(" <Selected/>");
              writer.WriteLine(" <Panes>");
              writer.WriteLine(" <Pane>");
              writer.WriteLine(" <Number>3</Number>");
              writer.WriteLine(" <ActiveRow>1</ActiveRow>");
              writer.WriteLine(" </Pane>");
              writer.WriteLine(" </Panes>");
              writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine(" </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
              writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine(" </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
              writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
              writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
              writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
              writer.WriteLine(" </WorksheetOptions>");
              writer.WriteLine(" </Worksheet>");
              writer.WriteLine("</Workbook>");
              writer.Close();
              Response.Write("<script language=\"javascript\">" + "alert('" + "convert completed!')" + "</script>");
          }
          catch (Exception ex)
          {
              Response.Write("<script language=\"javascript\">" + "alert('" + "error! " + ex.Message + "')" + "</script>");
          }
      }

   Hope it helps.

   Hi, I am trying to use your example codes. The code can be used to export a SQL table to spreadsheet. It works.

  But, now I want to format the speadsheet title to make it as good as possible, basically, I need title, columns and some summary on the bottom of speadsheet. Plus , I need to display the spreadsheet to user after finished exporting, then user decides if they want to save it not.

  Do you know how ot achieve it? thanks.