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

In C# in a Windows form, how would you write a string on the first availabe row after a result set is exported to an Excel file?

I am writing a C# application on a Windows platform using VS2005 with SQL server as the back end database and I write out a SQL Result set to an Excel file. How would you modify the following methods to write the

string strFootnote with a value "END OF REPORT"

on the next available row after the last line of the RESULT SET has been exported to an Excel file?


private void ReportToExcel(string reportyr, string cmdText, string ftnote, string flename)
        {            
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            string connetionString = null;
            SqlConnection connection;
            SqlDataAdapter adapter;
            SqlCommand command = new SqlCommand();
            SqlParameter param;            

            connetionString = MyGlobals.connectLiteral;
            connection = new SqlConnection(connetionString);

            connection.Open();
            command.Connection = connection;
            command.CommandType = CommandType.StoredProcedure;
           
            command.CommandText = cmdText;    

            DataSet ds = new DataSet();
            adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);

            string MyProjectDir = DirProject();
           
            string footnote = ftnote;
            string ExportedFile;
            string flenamemod = flename.Substring(1, flename.Length);

            DataTable myDataTable = ds.Tables[0];
         
            ExportedFile = MyProjectDir + flename + "_" + reportyr + "_" + DateTime.Now.ToString("MMddhhmmss") + ".XLS";
            ExcelExport(myDataTable, ExportedFile);        
           
            ExcelFormat(ExportedFile, footnote);
            Cursor.Current = Cursors.Default;  

        }

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

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

        public class ExcelWriter : IDisposable
        {
            private XmlWriter _writer;

            public enum CellStyle { General, Number, Currency, DateTime, ShortDate };

            public void WriteStartDocument()
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
                _writer.WriteStartElement("ss", "Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
                WriteExcelStyles();
            }

            public void WriteEndDocument()
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteEndElement();
            }

            private void WriteExcelStyleElement(CellStyle style)
            {
                _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
                _writer.WriteEndElement();
            }

            private void WriteExcelStyleElement(CellStyle style, string NumberFormat)
            {
                _writer.WriteStartElement("Style", "urn:schemas-microsoft-com:office:spreadsheet");

                _writer.WriteAttributeString("ID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
                _writer.WriteStartElement("NumberFormat", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteAttributeString("Format", "urn:schemas-microsoft-com:office:spreadsheet", NumberFormat);
                _writer.WriteEndElement();

                _writer.WriteEndElement();

            }

            private void WriteExcelStyles()
            {
                _writer.WriteStartElement("Styles", "urn:schemas-microsoft-com:office:spreadsheet");

                WriteExcelStyleElement(CellStyle.General);
                WriteExcelStyleElement(CellStyle.Number, "General Number");
                WriteExcelStyleElement(CellStyle.DateTime, "General Date");
                WriteExcelStyleElement(CellStyle.Currency, "Currency");
                WriteExcelStyleElement(CellStyle.ShortDate, "Short Date");

                _writer.WriteEndElement();
            }

            public void WriteStartWorksheet(string name)
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteStartElement("Worksheet", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteAttributeString("Name", "urn:schemas-microsoft-com:office:spreadsheet", name);
                _writer.WriteStartElement("Table", "urn:schemas-microsoft-com:office:spreadsheet");
            }

            public void WriteEndWorksheet()
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteEndElement();
                _writer.WriteEndElement();
            }

            public ExcelWriter(string outputFileName)
            {
                XmlWriterSettings settings = new XmlWriterSettings();
                settings.Indent = true;
                _writer = XmlWriter.Create(outputFileName, settings);
            }

            public void Close()
            {
                if (_writer == null) throw new NotSupportedException("Already closed.");

                _writer.Close();
                _writer = null;
            }

            public void WriteExcelColumnDefinition(int columnWidth)
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteStartElement("Column", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteStartAttribute("Width", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteValue(columnWidth);
                _writer.WriteEndAttribute();
                _writer.WriteEndElement();
            }

            public void WriteExcelUnstyledCell(string value)
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
                _writer.WriteValue(value);
                _writer.WriteEndElement();
                _writer.WriteEndElement();
            }

            public void WriteStartRow()
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteStartElement("Row", "urn:schemas-microsoft-com:office:spreadsheet");
            }

            public void WriteEndRow()
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteEndElement();
            }

            public void WriteExcelStyledCell(object value, CellStyle style)
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                _writer.WriteStartElement("Cell", "urn:schemas-microsoft-com:office:spreadsheet");
                _writer.WriteAttributeString("StyleID", "urn:schemas-microsoft-com:office:spreadsheet", style.ToString());
                _writer.WriteStartElement("Data", "urn:schemas-microsoft-com:office:spreadsheet");
                switch (style)
                {
                    case CellStyle.General:
                        _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
                        break;
                    case CellStyle.Number:
                    case CellStyle.Currency:
                        _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "Number");
                        break;
                    case CellStyle.ShortDate:
                    case CellStyle.DateTime:
                        _writer.WriteAttributeString("Type", "urn:schemas-microsoft-com:office:spreadsheet", "DateTime");
                        break;
                }
                _writer.WriteValue(value == DBNull.Value ? string.Empty : value);

                _writer.WriteEndElement();
                _writer.WriteEndElement();
            }

            public void WriteExcelAutoStyledCell(object value)
            {
                if (_writer == null) throw new NotSupportedException("Cannot write after closing.");

                //write the <ss:Cell> and <ss:Data> tags for something
                if (value is Int16 || value is Int32 || value is Int64 || value is SByte ||
                    value is UInt16 || value is UInt32 || value is UInt64 || value is Byte)
                {
                    WriteExcelStyledCell(value, CellStyle.Number);
                }
                else if (value is Single || value is Double || value is Decimal) //we'll assume it's a currency
                {
                    WriteExcelStyledCell(value, CellStyle.Currency);
                }
                else if (value is DateTime)
                {
                    //check if there's no time information and use the appropriate style
                    WriteExcelStyledCell(value, ((DateTime)value).TimeOfDay.CompareTo(new TimeSpan(0, 0, 0, 0, 0)) == 0 ? CellStyle.ShortDate : CellStyle.DateTime);
                }
                else
                {
                    WriteExcelStyledCell(value, CellStyle.General);
                }
            }

            #region IDisposable Members

            public void Dispose()
            {
                if (_writer == null)
                    return;

                _writer.Close();
                _writer = null;
            }

            #endregion
        }

        public static void ExcelFormat(string filename, string footnote)
        {
            Excel.Application oXL = new Excel.Application();
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            Excel._Workbook oWB = (Excel._Workbook)oXL.Workbooks.Open(filename, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, false, false, Missing.Value, false, false, false);
            Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            oSheet.Activate();

            try
            {

                oSheet.get_Range(oSheet.Cells[2, 1], oSheet.Cells[65535, 1]).EntireColumn.NumberFormat = "#,##0";

            }
            finally
            {
                oXL.Visible = true;
               

                DialogResult result = MessageBox.Show("Do you want to save the file ?", "Yes or No", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
                if (result == DialogResult.Yes)
                {                    
                    oXL.DisplayAlerts = true;
                }
                else
                {
                    oXL.Workbooks.Close();
                    oXL.Quit();                    
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                    oSheet = null;
                    oWB = null;
                    oXL = null;
                    File.Delete(filename);
                }
            }
        }
0
zimmer9
Asked:
zimmer9
  • 7
  • 6
1 Solution
 
Bob LearnedCommented:
It looks like your are using Open XML to create an Excel worksheet, and you are looking for just the right spot to put some code.  There is a butt load of code there, and I am not a big fan of digging through someone else's code like that, so I would be more than happy to help you, but you need to help me...
0
 
apeterCommented:
Try belowcode in the method "ExcelExport".

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

                        // Close up the document
                        writer.WriteEndWorksheet();
...
0
 
zimmer9Author Commented:
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();
                        }

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

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

                        retry = DialogResult.Cancel;
                    }
                }
                catch (Exception myException)
                {
                    //myException.Message;
                    retry = MessageBox.Show(myException.Message, "Excel Export", MessageBoxButtons.RetryCancel, MessageBoxIcon.Asterisk);
                }
            }
        }

When I use the above code, adding the statements:

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

then when the application executes the statement

writer.WriteEndDocument();
the system then goes to the catch statement yielding the following error message:

$exception {"Token EndElement in state EndRootElement would result in an invalid XML document. Make sure that the ConformanceLevel setting is set to ConformanceLevel.Fragment or ConformanceLevel.Auto if you want to write an XML fragment. " }      System.Exception {System.InvalidOperationException}
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Bob LearnedCommented:
Does WriteExcelAutoStyledCell("END OF REPORT") generate a complete begin and end cell.
0
 
zimmer9Author Commented:
How can I determine whether or not WriteExcelAutoStyledCell("END OF REPORT") generates a
complete begin and end cell? The Excel file never opens.
0
 
Bob LearnedCommented:
You should be able to open an Open XML file with a text editor, since it should just be ASCII text.
0
 
zimmer9Author Commented:
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

The problem is that the Content.MSO directory does not even exist so I can't read the file to see what the problem is. This seems to be a message generated by Excel
0
 
Bob LearnedCommented:
You need to be able to open the file in a text editor, like Notepad, to verify the XML structure.
0
 
Bob LearnedCommented:
You can access that folder, if you turn off the Hide Protected Operating System Files in Windows Explorer, or you can just paste that into the address bar for Windows Explorer, since the folder does exist--it is just being hidden.
0
 
zimmer9Author Commented:
Here is the contents of the log file from the hidden folder:

XML ERROR in Table
REASON:      Illegal Tag
FILE:      U:\Visual Studio 2005\Projects\Fall_QueryACSToExcel_2012_0917030903.XLS
GROUP:      Table
TAG:      Cell
0
 
Bob LearnedCommented:
Did you open the file with Notepad, and look at the XML generated?
0
 
zimmer9Author Commented:
Here are the last few rows of the Excel file that I opened in Notepad:

         <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">MIL                      </ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">MD</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">21108-0000</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">BANK DEPOSIT PROGRAM          </ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String"></ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2008-09-01T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Currency">
          <ss:Data ss:Type="Number">0.00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Currency">
          <ss:Data ss:Type="Number">8244.10000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">637 000000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">061871000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">000000360</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">BDPS        </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"></ss:Data>
        </ss:Cell>
        <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:Row>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String"></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">212-17-0000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
0
 
zimmer9Author Commented:
Correction, it looks like this:

        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">21108-0000</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">W HLDG CO                     </ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String"></ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="ShortDate">
          <ss:Data ss:Type="DateTime">2008-09-01T00:00:00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Currency">
          <ss:Data ss:Type="Number">0.00</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="Currency">
          <ss:Data ss:Type="Number">301.00000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">637 000000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">254000000</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">000023995</ss:Data>
        </ss:Cell>
        <ss:Cell ss:StyleID="General">
          <ss:Data ss:Type="String">DKS         </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"></ss:Data>
        </ss:Cell>
        <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
 
Bob LearnedCommented:
You need to write a new <ss:/Row> for that cell, since it is outside of the </ss:Row>.  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>
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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