none
cannot open a .xlsx file RRS feed

  • Question

  • Hi,

    This code outputs a string into excel.

    when i use **.xls;i don cells will not be formatted as written in the code(op system used win7/exce vr l2012).Ex

    when i change it to ***.xlsx then the excel file dont open.it gives me the following error

    "Excel cannot open the file '(filename)'.xlsx' because the file  format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

    Please let me know how i could achive this with all the formattting into .xlsx file


    namespace xyz
    {
        class Excel12
        
    
            public  void getHtml(DataSet dataSet)
               
            {
                try
                {
    
                                    string messageBody=null;
    
                    if (dataSet.Tables[0].Rows.Count == 0)
                        return messageBody;
                    string htmlTableStart = "<table style=\"border-collapse:collapse;  text-align:center;\" >";
                    string htmlTableEnd = "</table>";
                    string htmlHeaderRowStart = "<tr style =\"background-color:#6FA1D2; color:#ffffff;\">";
                    string htmlHeaderRowEnd = "</tr>";
                    string htmlTrStart = "<tr style =\"color:#555555;\">";
                    string htmlTrEnd = "</tr>";
                    
                   string htmlTd1grStart= "<td style=\" border-color:#5c87b2; background-color:#00CC00; border-style:solid; border-width:thin; padding: 5px;\">";
                   string htmlTd1yeStart= "<td style=\" border-color:#5c87b2;  background-color:#FFFF66; border-style:solid; border-width:thin; padding: 5px;\">";
                   string htmlTd1wdStart= "<td style=\" border-color:#5c87b2;   width:700; border-style:solid; border-width:thin; padding: 5px;\">";
    
                    string htmlTdStart = "<td style=\" border-color:#5c87b2; border-style:solid; border-width:thin; padding: 5px;\">";
                    /*string htmlTdgrStart="<td style=\" border-color:#5c87b2; border-style:solid; border-width:thin; padding: 5px;\">";
                    string htmlTdyeStart = "<td style=\" border-color:#5c87b2; border-style:solid; border-width:thin; padding: 5px;\">";*/
                    //string htmlTd1Start= "<td width=\"300;\" style=\" border-color:#5c87b2;background-color:#FFFF66; border-style:solid; width:700px; border-width:thin; padding: 5px;\">";
                    string htmlTdEnd = "</td>";
                   
                    messageBody += htmlTableStart;
                    messageBody += htmlHeaderRowStart;
                   
                    foreach (DataColumn column in dataSet.Tables[0].Columns)
                    {
                                                                  
                        messageBody += htmlTdStart + column.ColumnName + htmlTdEnd;
                    }
    
                    
                    messageBody += htmlHeaderRowEnd;
    
                   
                    foreach (DataRow Row in dataSet.Tables[0].Rows)
                    {
                        
                        messageBody = messageBody + htmlTrStart;
                        
                        foreach (DataColumn column in dataSet.Tables[0].Columns)
                        {
                            string var123 = Convert.ToString(Row[column.ColumnName]);
    
                            
                            
                            if (var123 == "Green")
                                messageBody = messageBody + htmlTd1grStart + Row[column.ColumnName] + htmlTdEnd;
    
                            else if (var123 == "Yellow")
                                messageBody = messageBody + htmlTd1yeStart + Row[column.ColumnName] + htmlTdEnd;
    
                            else if (column == dataSet.Tables[0].Columns[1])
                                messageBody = messageBody + htmlTd1wdStart + Row[column.ColumnName] + htmlTdEnd;
                            else
                                messageBody = messageBody + htmlTdStart + Row[column.ColumnName] + htmlTdEnd;
                        }
                  
                        messageBody = messageBody + htmlTrEnd;
                    }
    
                   
                    messageBody = messageBody + htmlTableEnd;
    
                   excelwr(messageBody);
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
    
    
    
            public  void Excelwr(string htmlString)
            {
    
                string path = @"D:/Report.xlsx";
                StreamWriter sw = new StreamWriter(path);
                if (!File.Exists(path))
                {
                    sw = File.CreateText(path);
                }
    
                using (sw)
                {
    
    
                    sw.WriteLine(htmlString);
    
                }
                sw.Close();
            }
             catch (Exception excpt)
             {
                 Console.WriteLine(excpt.Message);
                 return;
             }
             
        }
    }


    • Moved by Mike Feng Monday, June 17, 2013 5:59 AM
    Saturday, June 15, 2013 3:26 AM

Answers

  • It appears you're trying to build an Excel file from HTML? You'd need to check with Excel specialists (Excel for Developers forum, for example), but I believe the HTML format the Excel can read/write was changed significantly with the release of Office 2007, in part due to new technologies introduced in that version. That being the case, unsupported round-trip HTML that worked for earlier version file formats (*.xls) will no longer be correctly recognized if it's in a *.xlsx file.

    If you want to build an Excel file "from scratch" you need to use the Office Open XML file format introduced in Office 2007. That means you'd need to rewrite your program pretty much from the ground-up. You can find out more about the file format and what tools are available for working with it at OpenXMLDeveloper.org. One of those tools is the Open XML SDK for Visual Studio, which is the topic of this forum.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, June 17, 2013 3:45 PM
    Moderator
  • Hi Leena,

    Thank you for posting in the MSDN Forum.

    You can not directly write html into .xlsx file. I suggest you use OpenXML SDK. 

    Start from: Open XML for Office developers
    See also: AltChunk class

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 17, 2013 2:49 PM
    Moderator

All replies

  • Hi Leena,

    Thank you for posting in the MSDN Forum.

    You can not directly write html into .xlsx file. I suggest you use OpenXML SDK. 

    Start from: Open XML for Office developers
    See also: AltChunk class

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, June 17, 2013 2:49 PM
    Moderator
  • It appears you're trying to build an Excel file from HTML? You'd need to check with Excel specialists (Excel for Developers forum, for example), but I believe the HTML format the Excel can read/write was changed significantly with the release of Office 2007, in part due to new technologies introduced in that version. That being the case, unsupported round-trip HTML that worked for earlier version file formats (*.xls) will no longer be correctly recognized if it's in a *.xlsx file.

    If you want to build an Excel file "from scratch" you need to use the Office Open XML file format introduced in Office 2007. That means you'd need to rewrite your program pretty much from the ground-up. You can find out more about the file format and what tools are available for working with it at OpenXMLDeveloper.org. One of those tools is the Open XML SDK for Visual Studio, which is the topic of this forum.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, June 17, 2013 3:45 PM
    Moderator
  • Hi,

    I saved one blank excel sheet in document library and now i want to open it programatically and want to add some columns.

    am using open xml.sdk (Spreadsheet).

    Can anyone please tell me ?

    Thanks.

    Tuesday, June 25, 2013 6:19 AM