• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

Using C#, how to write out a new row with a cell containing the value "END OF REPORT" after a RESULT SET exported to Excel?

I am wring a C# Windows platform application using VS2005.
 
I use the following code to write out the RESULT SET of a SQL Server table to an Excel file.

I wish to write out the string "END OF REPORT" after the last record of the table is exported to the Excel file.

However, I commented out my 2 lines of code to write out a record with the value "END OF REPORT" because I was getting the error:
-----------------------
Problems came up in the following areas during load:
Table
This file cannot be opened because of errors. Errors are listed in C:\Documents and Settings\<user name>\Local Settings\Temporary Internet Files\Content.MSO\69160819.log

----------------------

How would you write a new <ss:/Row> using the existing code below
for a cell with the value "END OF REPORT",
since it would be outside of the </ss:Row> in the following code?
 
A cell cannot stand by itself--it needs to be part of a row.
---------------------------------------------------------------------------------
<ss:Row>
     <ss:Cell ss:StyleID="General">
        <ss:Data ss:Type="String">END OF REPORT</ss:Data>
      </ss:Cell>
</ss:Row>

---------------------------------------------------------------------------------

                        //Write header row
                        writer.WriteStartRow();
                        foreach (DataColumn col in data.Columns)
                            writer.WriteExcelUnstyledCell(col.Caption);
                        writer.WriteEndRow();

                        //write data
                        foreach (DataRow row in data.Rows)
                        {
                            writer.WriteStartRow();
                            foreach (object o in row.ItemArray)
                            {
                                writer.WriteExcelAutoStyledCell(o);
                            }
                            writer.WriteEndRow();
                        }

                        //write END OF REPORT at the end
                        //writer.WriteExcelAutoStyledCell("END OF REPORT");  //commented out
                        //writer.Wr iteEndRow();                              //commented out
                       

                        // Close up the document
                        writer.WriteEndWorksheet();
                        writer.WriteEndDocument();    //<--this statement generates an Excpetion
                        writer.Close();
---------------------------------------------------

The last few lines of output generated by the C# code as it stands now is as follows:

<ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">N</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">+0</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">   </ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">Y</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">0</ss:Data>
        </ss:Cell>
      </ss:Row>
      <ss:Cell ss:StyleID="General">
        <ss:Data ss:Type="String">END OF REPORT</ss:Data>
      </ss:Cell>
    </ss:Table>
  </ss:Worksheet>
</ss:Workbook>
0
zimmer9
Asked:
zimmer9
  • 2
2 Solutions
 
anarki_jimbelCommented:
I believe you just need to create a row and write your message to the first cell.

By the way, no one knows what writes etc you are using. It would be helpful to know a bit more. I believe you are using code from: http://stackoverflow.com/questions/373925/c-sharp-winforms-app-export-dataset-to-excel . Aren't you?

OK, try to insert my code instead your two commented out lines. I didn't test it of course because I have no your setup :). But it should work.

            //basically, we need to know how many cells in a row and use for loop
            // or can do this way:
            DataRow row  =  data.Rows[0]; //just take the first row, e.g.

            writer.WriteStartRow();// start new row
            int loopCounter = 0;
            foreach (object o in row.ItemArray)
            {
                if (loopCounter == 0)
                {
                    writer.WriteExcelAutoStyledCell("END OF REPORT"); // write first cell not blank
                    loopCounter += 1; // increase the counter
                }
                else
                {
                    writer.WriteExcelAutoStyledCell(""); // write a blank cells
                }
            }
            writer.WriteEndRow(); // conclude the row

Open in new window

0
 
zimmer9Author Commented:
Error      1      A local variable named 'row' cannot be declared in this scope because it would give a different meaning to 'row', which is already used in a 'child' scope to denote something else      U:\Visual Studio 2005\Projects\UDLApplication\UDLApplication\Form1.cs      982      33      UDLApplication


public static void ExcelExport(DataTable data, String fileName)
        {
            //export a DataTable to Excel
            DialogResult retry = DialogResult.Retry;

            while (retry == DialogResult.Retry)
            {
                try
                {
                    using (ExcelWriter writer = new ExcelWriter(fileName))
                    {
                        writer.WriteStartDocument();

                        // Write the worksheet contents
                        writer.WriteStartWorksheet("Sheet1");

                        //Write header row
                        writer.WriteStartRow();
                        foreach (DataColumn col in data.Columns)
                            writer.WriteExcelUnstyledCell(col.Caption);
                        writer.WriteEndRow();

                        //write data
                       
                        foreach (DataRow row in data.Rows)
                        {
                            writer.WriteStartRow();
                            foreach (object o in row.ItemArray)
                            {
                                writer.WriteExcelAutoStyledCell(o);
                            }
                            writer.WriteEndRow();
                        }
                       

                        //basically, we need to know how many cells in a row and use for loop
                        // or can do this way:
                        DataRow row = data.Rows[0]; //just take the first row, e.g.  //Error **

                        writer.WriteStartRow();// start new row
                        int loopCounter = 0;
                        foreach (object o in row.ItemArray)
                        {
                            if (loopCounter == 0)
                            {
                                writer.WriteExcelAutoStyledCell("END OF REPORT"); // write first cell not blank
                                loopCounter += 1; // increase the counter
                            }
                            else
                            {
                                writer.WriteExcelAutoStyledCell(""); // write a blank cells
                            }
                        }
                        writer.WriteEndRow(); // conclude the row

                        //write END OF REPORT at the end
                        //writer.WriteExcelAutoStyledCell("END OF REPORT");
                        //writer.WriteEndRow();

                        // Close up the document
                        writer.WriteEndWorksheet();
                        writer.WriteEndDocument();
                        writer.Close();

                        retry = DialogResult.Cancel;
                    }
                }
                catch (Exception myException)
                {
                    //myException.Message;
                    retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
                }
            }
        }
0
 
Miguel OzSoftware EngineerCommented:
Replace in your original code:
//write END OF REPORT at the end
                        //writer.WriteExcelAutoStyledCell("END OF REPORT");  //commented out
                        //writer.Wr iteEndRow();                              
with
//write END OF REPORT at the end
writer.WriteStartRow();// start new row
writer.WriteExcelAutoStyledCell("END OF REPORT");  //commented out
writer.WriteEndRow();                              

Open in new window

0
 
anarki_jimbelCommented:
"Error      1      A local variable named 'row' cannot be declared in this scope because it would give a different meaning to 'row', which is already used in a 'child' scope to denote something else      U:\Visual Studio 2005\Projects\UDLApplication\UDLApplication\Form1.cs      982      33      UDLApplication
"

Come on! This is basics. Just give a different name to the variable, say, 'row_temp'. See code below.Unfortunately, I can't see all your code and can't run and debug.

Really, I agree to  mas_oz2003. I'd try his solution. Basically, it is same as mine but a bit simpler. He writes a row with one cell, I write a row with the same number of cells as all other rows. But the idea is same.

            DataRow row_temp  =  data.Rows[0]; //just take the first row, e.g.

            writer.WriteStartRow();// start new row
            int loopCounter = 0;
            foreach (object o in row_temp.ItemArray)
            {
                if (loopCounter == 0)
                {
                    writer.WriteExcelAutoStyledCell("END OF REPORT"); // write first cell not blank
                    loopCounter += 1; // increase the counter
                }
                else
                {
                    writer.WriteExcelAutoStyledCell(""); // write a blank cells
                }
            }
            writer.WriteEndRow(); // conclude the row

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now