locked
How to Get print area height RRS feed

  • Question

  • User432931684 posted

    Hi all,

    I am exporting my data (from data table) to excel using the following approach and its working fine,

        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>");
            }
        }

         While printing the excel, because of huge data,only first page contains the header,remaining pages are not.Without headers,its making hard to read the datas.So using the above approach,is it possible to insert header row on each print page?I tried it by setting some fixed height in the code, its working for some reports but not for all, because we are generating and exporting the reports dynamically.Is it possible to get the print area height through code behind? so that i can try to insert the header in that particular position.or is there any other way to implement this?Please suggest me some solutions.thanks in advance.

    Monday, December 1, 2008 9:16 AM

Answers

  • User-1136466523 posted

    Hi,

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    Based on my knowledge, I’m afraid it’s not possible to add the header in each page, since what you export the data from datatable to excel, and what you defined is the general schema of that excel file, so it seems that we don’t have the chance to repeat the header in each page.

    <o:p> </o:p>

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2008 11:40 PM

All replies

  • User-1136466523 posted

    Hi,

    <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

    Based on my knowledge, I’m afraid it’s not possible to add the header in each page, since what you export the data from datatable to excel, and what you defined is the general schema of that excel file, so it seems that we don’t have the chance to repeat the header in each page.

    <o:p> </o:p>

    Thanks.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 4, 2008 11:40 PM
  • User-2023223889 posted

    Excel will "print titles" by itself.  You can set this manually with Excel before you print - just tell it which row(s) to put at the top of each page.  I think this is in File/Page Setup with 2003 and before (sorry, I use 2007 now and the memory is fading), and it's Page Layout/Print Titles in 2007.

     I tried setting Excel to print the top row as titles, and this snippet indicates what it added to the .xml (2003) version of the spreadsheet:

     <Worksheet ss:Name="XML II">
      <Names>
       <NamedRange ss:Name="Print_Titles" ss:RefersTo="='XML II'!R1"/>
      </Names>
      <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="48" x:FullColumns="1"
       x:FullRows="1">
       <Column ss:AutoFitWidth="0" ss:Width="204.75"/>
       <Column ss:AutoFitWidth="0" ss:Width="69.75" ss:Span="1"/>
       <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="64.5"/>
       <Row ss:AutoFitHeight="0" ss:Height="63.75">
        <Cell ss:StyleID="s62"><Data ss:Type="String">Task</Data><NamedCell
          ss:Name="Print_Titles"/></Cell>
        <Cell ss:StyleID="s62"><Data ss:Type="String">Start Date (optional)</Data><NamedCell
          ss:Name="Print_Titles"/></Cell>
        <Cell ss:StyleID="s62"><Data ss:Type="String">Due Date</Data><NamedCell
          ss:Name="Print_Titles"/></Cell>
        <Cell ss:StyleID="s62"><Data ss:Type="String">N/A</Data><NamedCell
          ss:Name="Print_Titles"/></Cell>
        <Cell ss:StyleID="s62"><Data ss:Type="String">Date Completed</Data><NamedCell
          ss:Name="Print_Titles"/></Cell>
       </Row>

     

    Here's the "before" version:

     <Worksheet ss:Name="XML II">
      <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="2" x:FullColumns="1"
       x:FullRows="1">
       <Column ss:AutoFitWidth="0" ss:Width="204.75"/>
       <Column ss:AutoFitWidth="0" ss:Width="69.75" ss:Span="1"/>
       <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="64.5"/>
       <Row ss:Height="63.75">
        <Cell ss:StyleID="s21"><Data ss:Type="String">Task</Data></Cell>
        <Cell ss:StyleID="s21"><Data ss:Type="String">Start Date (optional)</Data></Cell>
        <Cell ss:StyleID="s21"><Data ss:Type="String">Due Date</Data></Cell>
        <Cell ss:StyleID="s21"><Data ss:Type="String">N/A</Data></Cell>
        <Cell ss:StyleID="s21"><Data ss:Type="String">Date Completed</Data></Cell>
       </Row>

    Apparently, Excel has a sort of "reserved named range" that tells it to print those cells at the top of every report page ("print titles") My guess is that you could "tweak" your code pretty easily to incorporate the former, and Excel would automatically know to "print titles" when you opened the resulting file.

     

    [edit]Don't sweat the different style IDs.  I don't think it's pertinent.[/edit]

    Wednesday, January 7, 2009 6:33 PM