locked
Export to Excel Slow Using OleDB RRS feed

  • Question

  • Other threads dealing with this topic did not seem to address the particular question that I am asking.


    I created a simple AccessViewer in C#. It displays the tables of .MDb databases (Jet databases) without Access installed. One of the features is an export of any table to Excel, if desired.  For example it can export the Customers table using the following query:

    SELECT * INTO [Excel 8.0; Database=C:\export.xls].Sheet1 FROM Customers

    I run this query using an oleDBCommand object (cmd.ExecuteNonquery).  Fine. It only took 10 seconds to export 32,000 rows (35 columns).  Not bad.

    However, I also have a routine for exporting datatables - this too uses an oleDBCommand object. Here again, I want to stress that in both cases, I'm using an OleDbCommand object to do the export. But here it takes 90 seconds. It looks like this:

      foreach (DataRow row in selectedRows)
                {
                    foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
                        param.Value = row[colNames[param.SourceColumn]];
                    cmd.ExecuteNonQuery();  //INSERT INTO command.
                }
    Looping through the rows and columns isn't the slow part. In fact, I created  a second version of my code where I not only looped through the rows, I even copied them into a cloned datatable, row by row. This copying was VERY fast (took less than two seconds). Here I called da.Update once-for-all on the cloned table. This once-for-all call to da.Update took 50% longer  (about 135 seconds) than the 90 seconds it took to repeatedly call cmd.ExecuteNonquery.


    But what I don't get is this. Why the jump from 10 seconds to 90 seconds (not to mention 135 seconds)? In all cases I'm using the OleDb provider. Again,  it's not the looping through the datatable - that part takes less than two seconds.


    Here's why this is especially weird. The 10-second method is a query executed against a database stored on disk. Disk access tends to be slower than memory access. Therefore a datatable operation should be faster than a database operation. But I'm getting the opposite result - it's taking 10 seconds to move the data from the .MDB database to Excel, versus 90 seconds (or even 135 seconds) to move it from the datatable to Excel. Can anyone explain this discrepancy?


    Why is all this important to me? Because all my apps support exporting grids to Excel. In many cases, the data is in a datatable, NOT in a Jet database, so this  rules out using the 10-second method described above. 

    Wednesday, December 16, 2009 1:39 AM

Answers

  •  The problem with the schema.ini file is that I need a setup immediately readable by OpenOffice Org spreadsheet (Calc), as well as Excel.  Another thought was to use the dataSet.WriteXML method and dataSet.WriteXmlSchema to save the datatypes, but Excel doesn't much like such standard XML. So my solution was to learn enough Excel XML to manually create a spreadsheet. I just typed in a couple of values into an Excel spreadsheet, saved it as "Excel XML Spreadsheet Format", and then viewed the xml in Wordpad. Basically has a long intro (a bunch of xml tags), then the rows of data, and then a long conclusion (more xml tags). The intro and conclusion were basically a copy-and-paste effort.  And then I just used a loop to write out the rows. I hear this is a pretty common technique - I'm now surprised I never stumbled upon articles on this topic before. So here's my solution (only took 5 seconds to export 25,000 rows, 40 columns). And it preserved the numeric datatypes instead of converting them to string (that was one of my main goals). Needs more testing.

      /// <summary>
            /// Pass in null/nothing for either selectedRows or the datatable.
            /// </summary>
            public void subExportToExcel(List<DataRow> selectedRows, DataTable dtOriginal,  string tableName, string pathToSheet)
            {
                string q = Convert.ToString('\"'); //quotation mark
                StreamWriter sw = new StreamWriter(pathToSheet);
                sw.WriteLine("<?xml version=" + q+ "1.0"+ q + "?>");
                sw.WriteLine("<?mso-application progid=" + q + "Excel.Sheet" + q + "?>");
                sw.WriteLine("<Workbook xmlns=" + q + "urn:schemas-microsoft-com:office:spreadsheet" + q);
                sw.WriteLine("xmlns:o=" + q + "urn:schemas-microsoft-com:office:office" + q);
                sw.WriteLine("xmlns:x=" + q + "urn:schemas-microsoft-com:office:excel" + q);
                sw.WriteLine("xmlns:ss=" + q + "urn:schemas-microsoft-com:office:spreadsheet" + q);
                sw.WriteLine ("xmlns:html=" + q + "http://www.w3.org/TR/REC-html40" + q + ">");
                sw.WriteLine("<DocumentProperties xmlns=" + q + "urn:schemas-microsoft-com:office:office" + q + ">");
                sw.WriteLine("<Author>ROMEO</Author>");
                sw.WriteLine("<LastAuthor>ROMEO</LastAuthor>");
                sw.WriteLine("<Created>2009-12-18T03:04:39Z</Created>");
                sw.WriteLine("<Version>11.9999</Version>");
                sw.WriteLine("</DocumentProperties>");
                sw.WriteLine("<ExcelWorkbook xmlns=" + q + "urn:schemas-microsoft-com:office:excel" + q + ">");
                sw.WriteLine("<WindowHeight>8400</WindowHeight>");
                sw.WriteLine("<WindowWidth>6615</WindowWidth>");
                sw.WriteLine("<WindowTopX>480</WindowTopX>");
                sw.WriteLine("<WindowTopY>60</WindowTopY>");
                sw.WriteLine("<ProtectStructure>False</ProtectStructure>");
                sw.WriteLine("<ProtectWindows>False</ProtectWindows>");
                sw.WriteLine("</ExcelWorkbook>");
                sw.WriteLine("<Styles>");
                sw.WriteLine("<Style ss:ID=" + q + "Default" + q + " ss:Name=" + q + "Normal" + q + ">");
                sw.WriteLine("<Alignment ss:Vertical=" + q + "Bottom"+ q + "/>");
                sw.WriteLine("<Borders/>");
                sw.WriteLine("<Font/>");
                sw.WriteLine("<Interior/>");
                sw.WriteLine("<NumberFormat/>");
                sw.WriteLine("<Protection/>");
                sw.WriteLine("</Style>");
                sw.WriteLine("</Styles>");
                sw.WriteLine("<Worksheet ss:Name=" + q + tableName + q + ">");
                if (selectedRows == null || selectedRows.Count == 0)
                {
                    selectedRows = new List<DataRow>();
                    foreach (DataRow row in dtOriginal.Rows) selectedRows.Add(row);
                }
                if (dtOriginal == null) dtOriginal = selectedRows[0].Table;
                List<string> colNames = new List<string>();
                List<string> dataTypes = new List<string>();
                foreach (DataColumn dc in dtOriginal.Columns)
                {//Just in case Excel is picky about column names...
                    string modifiedcolName = dc.ColumnName.Replace(' ', '_').Replace('.', '#');
                    colNames.Add(modifiedcolName);
                    string dataType = "String";
                    switch (dc.DataType.ToString())
                    {
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Decimal":
                        case "System.Double":
                        case "System.Single": dataType = "Number"; break;
                    }
                    dataTypes.Add(dataType);
                }
                string numCols = dtOriginal.Columns.Count.ToString();
                string numRows = Convert.ToString(selectedRows.Count + 1);
                sw.WriteLine("<Table x:FullColumns=" + q + "1" + q + " x:FullRows=" + q + "1" + q + ">");
                sw.WriteLine("<Column ss:Index=" + q + "3" + q + " ss:Width=" + q + "53.25" + q + "/>");
                sw.WriteLine("<Row>"); //The first row is the column names. 
                foreach (string col in colNames)
                    sw.WriteLine("<Cell><Data ss:Type=" + q + "String" + q + ">" + col + "</Data></Cell>");
                sw.WriteLine("</Row>"); //ends the row of column names. 
                foreach (DataRow row in selectedRows) 
                 {
                       sw.WriteLine("<Row>");
                       for (int i = 0; i <= colNames.Count - 1; i++)
                       {
                           string val = String.Empty;
                           if (!row.IsNull(i)) val = row[i].ToString();
                           sw.WriteLine("<Cell><Data ss:Type=" + q + dataTypes[i] + q + ">" + val + "</Data></Cell>");
                       }
                       sw.WriteLine("</Row>");
                }
               sw.WriteLine("</Table>");
                sw.WriteLine("<WorksheetOptions xmlns=" + q + "urn:schemas-microsoft-com:office:excel" + q + ">");
                sw.WriteLine("<Selected/>");
                sw.WriteLine("<ProtectObjects>False</ProtectObjects>");
                sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
                sw.WriteLine("</WorksheetOptions>");
                sw.WriteLine("</Worksheet>");
                sw.WriteLine("</Workbook>");
                sw.Close();
            }
    


    • Marked as answer by jal2 Friday, December 18, 2009 6:05 AM
    Friday, December 18, 2009 6:00 AM

All replies

  • here is the perfect document what microsoft has created to demonstrate export using OLEDB... can u try to use this concept...


    hope this helped...



    Narayanan Dayalan - Zeetaa Business Solutions ------- Please "Mark As Answer", if my answer works well with ur Query
    Wednesday, December 16, 2009 5:22 AM

  • I'm having trouble translating the article into C#. I did succeed in the Create TAble statement that creates the empty Excel workbook with all the column names in place, by building the Create Table statement in a loop and then calling:


    object outVAl = null;
    cmd.Execute(out outVAl, ref oMissing, -1);  //Create Table
    

    Then I use a similar loop to build the INSERT INTO  statement.


        string insert = "INSERT INTO [" + tableName + "] (";
                foreach (string colName in colNames.Keys) insert += colName + ",";
                insert = insert.TrimEnd(new char[] { ',' }) + ")  VALUES(";
                count = 0;
                foreach (string colName in colNames.Keys)   insert += "@" + colName  + ",";
                insert = insert.TrimEnd(new char[] { ',' }) + ")";
                cmd.CommandText = insert;
                count = 0;
                foreach (string colName in colNames.Keys)
                {
                    string paramName = "@" + colName;
                    insert += paramName + ",";
                    switch (datatypes[count])
                    {
                        case "LONG": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamInput, 20, oMissing)); break;
                        case "DECIMAL": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adDecimal, ADODB.ParameterDirectionEnum.adParamInput, 40, oMissing)); break;
                        case "TEXT": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 99000, oMissing)); break;
                    }
                    count++;
                }
                foreach (DataRow row in selectedRows)
                {
                    for (int i = 0; i <= cmd.Parameters.Count - 1; i++)
                        cmd.Parameters[i].Value = row[colNames[cmd.Parameters[i].Name.TrimStart(new char[] { '@' })]];
                   cmd.Execute(out outVAl, ref  oMissing, -1);  //INSERT INTO command. 
                }
    



    It fails on the last  line of code:
    cmd.Execute(out outVAl, ref oMissing, -1); //INSERT INTO command.
    "Too few parameters, expected 11".

    The INSERT INTO statement dumped during debug is:

    ? cmd.CommandText
    "INSERT INTO [Export] (CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax)  VALUES(@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@City,@Region,@PostalCode,@Country,@Phone,@Fax)"

    ? cmd.Parameters.Count
    11

    One weird thing I noticed is that Intellisense depicts the cmd.Execute like this:

    ADODB.Recordset   Execute(out object RecordsAffected, ref object Parameters, int Options)

    I'm not sure what the "ref object Parameters" means so I used oMissing (which worked fine when I called cmd.Execute for the CREATE TABLE statement).

    In VB.Net these would be optinal parameters which I would normally omit, but C# is forcing me to supply them. I even tried this:

    cmd.Execute(out outVAl, ref  cmd.Parameters, -1); 

    but that won't even compile.

    Wednesday, December 16, 2009 10:20 AM
  • Here is the complete code that I have so far (still throwing the error stated above) in case anyone wants to try this.

            public void subExportToExcel(string sheetToCreate, DataTable dtToExport, string tableName)
            {
                List<DataRow> rows = new List<DataRow>();
                foreach (DataRow row in dtToExport.Rows) rows.Add(row);
                subExportToExcel(sheetToCreate, rows, dtToExport, tableName);
            }
    
            public void subExportToExcel(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
            {
                char Space = ' ';
                string dest = sheetToCreate;
                int count = 0;
                while (File.Exists(dest))
                {
                    dest = Path.GetDirectoryName(sheetToCreate) + "\\" + Path.GetFileName(sheetToCreate) + count + Path.GetExtension(sheetToCreate);
                    count += 1;
                }
                sheetToCreate = dest;
                if (tableName == null) tableName = string.Empty;
                tableName = tableName.Trim().Replace(Space, '_');
                if (tableName.Length == 0) tableName = origDataTable.TableName.Replace(Space, '_');
                if (tableName.Length == 0) tableName = "NoTableName";
                if (tableName.Length > 30) tableName = tableName.Substring(0, 30);
                //Excel names are less than 31 chars 
                string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
                Dictionary<string, string> colNames = new Dictionary<string, string>();
                List<string> datatypes = new List<string>();
                
                foreach (DataColumn dc in origDataTable.Columns)
                {
                    string dataType = "TEXT";
                    switch (dc.DataType.ToString())
                    {
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                            dataType = "LONG"; break;
                        case "System.Decimal":
                        case "System.Double":
                        case "System.Single":
                            dataType = "DECIMAL"; break;
                    }
                    //Cause the query to name each of the columns to be created. 
                    string modifiedcolName = dc.ColumnName.Replace(Space, '_').Replace('.', '#');
                    string origColName = dc.ColumnName;
                    colNames.Add(modifiedcolName, origColName);
                    queryCreateExcelTable += "[" + modifiedcolName + "] " + dataType + ",";
                    datatypes.Add(dataType);
                }
                queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
                //adds the closing parentheses to the query string 
                if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
                {
                    //use Excel 2007 for large sheets. 
                    sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
                }
                string strCn = string.Empty;
                string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
                if (ext == ".xls") strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
                if (ext == ".xlsx") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
                if (ext == ".xlsb") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
                if (ext == ".xlsm") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";
                ADODB.Connection cn = new ADODB.Connection();
                cn.Provider = "MSDASQL";
                cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};"
                    + "DBQ=" + sheetToCreate + "; ReadOnly=False;";
                ADODB.Command cmd = new ADODB.Command();
                object oMissing = (object)System.Reflection.Missing.Value;
                cn.Open(cn.ConnectionString, "", "", -1);
                cmd.ActiveConnection = cn;
                cmd.CommandText = queryCreateExcelTable;
                object outVAl = null;
                cmd.Execute(out outVAl, ref oMissing, -1);  //Create Table
                string insert = "INSERT INTO [" + tableName + "] (";
                foreach (string colName in colNames.Keys) insert += colName + ",";
                insert = insert.TrimEnd(new char[] { ',' }) + ")  VALUES(";
                count = 0;
                foreach (string colName in colNames.Keys)   insert += "@" + colName  + ",";
                insert = insert.TrimEnd(new char[] { ',' }) + ")";
                cmd.CommandText = insert;
                count = 0;
                foreach (string colName in colNames.Keys)
                {
                    string paramName = "@" + colName;
                    insert += paramName + ",";
                    switch (datatypes[count])
                    {
                        case "LONG": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adBigInt, ADODB.ParameterDirectionEnum.adParamInput, 20, oMissing)); break;
                        case "DECIMAL": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adDecimal, ADODB.ParameterDirectionEnum.adParamInput, 40, oMissing)); break;
                        case "TEXT": cmd.Parameters.Append(cmd.CreateParameter(paramName, ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 99000, oMissing)); break;
                    }
                    count++;
                }
                foreach (DataRow row in selectedRows)
                {
                    for (int i = 0; i <= cmd.Parameters.Count - 1; i++)
                    {
                        cmd.Parameters[i].Value = row[colNames[cmd.Parameters[i].Name.TrimStart(new char[] { '@' })]];
                        MessageBox.Show(cmd.Parameters[i].Name + "\r\n" + cmd.Parameters[i].Value);
                    }
                   cmd.Execute(out outVAl, ref  oMissing, -1);  //INSERT INTO command. 
                }
    
                cn.Close();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
    
    
    I also did a dump of the paramater names and values for the first row and they show up correct.

    Wednesday, December 16, 2009 10:49 AM
  • I tried an oversimplified version of the code, just to see if I could get it to work:

    ADODB.Connection cn = new ADODB.Connection();
                cn.Provider = "MSDASQL";
                cn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};"
                    + "DBQ=" + sheetToCreate + "; ReadOnly=False;";
                ADODB.Command cmd = new ADODB.Command();
                object oMissing = (object)System.Reflection.Missing.Value;
                cn.Open(cn.ConnectionString, "", "", -1);
                cmd.ActiveConnection = cn;
                cmd.CommandText = "CREATE TABLE Customers (LastName TEXT)";
                object outVAl = null;
                cmd.Execute(out outVAl, ref oMissing, -1);  //Create Table
                cmd.CommandText = "INSERT INTO Customers (LastName) VALUES (@LastName) ";
                cmd.Parameters.Append(cmd.CreateParameter("@LastName", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 99000, oMissing));
                cmd.Parameters["@LastName"].Value = "Johnson";
                cmd.Execute(out outVAl, ref oMissing, -1);
    "Too few parameters, expected 1".    The only way I've been able to get this to work is to do it without parameters (but that won't suffice for my applications).
    Wednesday, December 16, 2009 11:23 AM
  • If I recall correctly using @ for parameters is a T-SQL thing.  I believe when working with Excel/Access you'd need to use a ? for your parameters.

    Example:

            cmd.CommandText = "INSERT INTO Customers (LastName) VALUES (?)";
            cmd.Parameters.AddWithValue("LastName", "Johnson");
    • Proposed as answer by JediJohn82 Friday, December 18, 2009 12:41 PM
    Wednesday, December 16, 2009 1:32 PM
  • If I recall correctly using @ for parameters is a T-SQL thing.  I believe when working with Excel/Access you'd need to use a ? for your parameters.

    Example:

            cmd.CommandText = "INSERT INTO Customers (LastName) VALUES (?)";
            cmd.Parameters.AddWithValue("LastName", "Johnson");

    Thanks - you're correct. I changed it to the question marks and it worked, well, sort of worked. The article recommended by the other poster mentioned two providers - OleDB and ADODB.  i began this thread because OleDB was too slow. I just got ADODB working using the question marks as you suggested, but it's much slower than OleDB - took about 7  minutes. (Maybe because .Net has to use interop to use ADODB?).  Heck, it would be faster to move the values from the datatable into an Access DB and then use the quick method mentioned at the start of this thread.

    So I am still perplexed - the original question remains unanswered - why is OleDB so fast in one situation, but so slow in another? What's the difference?



    Wednesday, December 16, 2009 2:15 PM
  • Here's another method I tried, based on a MS article.

     DataTable dt = (DataTable)dgData.DataSource;
                dt.TableName = "Export";
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                System.IO.FileStream fs = new System.IO.FileStream(oPaths.exportSheet, System.IO.FileMode.Create);
                System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(   fs, System.Text.Encoding.Unicode);
                xtw.WriteProcessingInstruction("xml", "version='1.0'");
                ds.WriteXml(xtw, XmlWriteMode.WriteSchema);
                xtw.Close();
                if (fs!=null) fs.Close();
    
    The export was reasonably fast, but when the user tries to open tthe sheet he gets a message "The specified xml source does not refer to a schema. Excel will create a schema based on the XML data source." So the user clicks OK and then has to wait forever (I gave up after a long period of waiting).  I'll have to research this error message as I don't understand XML too well.

    Wednesday, December 16, 2009 2:31 PM
  • This version worked for the Customers table in the Northwind database:

      private void btnExportDataToExcel_Click(object sender, EventArgs e)
            {
                if (dgData.Rows.Count == 0) return;
                try { File.Delete(oPaths.exportSheet); }
                catch { M = new Msg("Please close the spreadsheet, and try again.", Colors.Lime); return; }
                if (!connectToDB(enumTypeOfConnect.ADO)) return;
                DataTable dt = (DataTable)dgData.DataSource;
                dt.TableName = "Customers";
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                System.IO.FileStream fs = new System.IO.FileStream(oPaths.exportSheet, System.IO.FileMode.Create);
                System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode);
                xtw.WriteProcessingInstruction("xml", "version='1.0'");
                xtw.WriteProcessingInstruction("xml-stylesheet", "type='text/xsl' href='" + dt.TableName + ".xsl'");
                ds.WriteXml(xtw);
                xtw.Close();
                if (fs != null) fs.Close();
                        }
    
    
    When the user opens the sheet, Excel asks if he wants to use the "Customers" stylesheet. If he says Yes, Excel displays the spreadsheet. However, when i tried the same code on my own data, and here again the user said Yes to the question, he gets this error: "An XML error has occurred."


    Wednesday, December 16, 2009 2:44 PM
  • My previous post was incorrect. I thought the ds.WriteXml method was working for the Customers table. I didn't understand that I needed to create a stylesheet. I'm still working on this...
    Wednesday, December 16, 2009 11:14 PM
  • I've tried two different methods of creating the stylesheet, plus, as a third try, i copied and pasted the stylesheet from Microsoft's article (it was designed for the Customers table of the Northwind database).

    In all three cases I get the same result - an Excel file with column names but no rows. I can view the generated XML file in Wordpad and it DOES contain all the rows.  But when I open it in Excel, there's no rows. Here's one version of my code:

     private void btnExportDataToExcel_Click(object sender, EventArgs e)
            {
                if (dgData.Rows.Count == 0) return;
                try { File.Delete(oPaths.exportSheet); }
                catch { M = new Msg("Please close the spreadsheet, and try again.", Colors.Lime); return; }
                if (!connectToDB(enumTypeOfConnect.ADO)) return;
                DataTable dt = (DataTable)dgData.DataSource;
                dt.TableName = "Export";
               DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                System.IO.FileStream fs = new System.IO.FileStream(oPaths.exportSheet, System.IO.FileMode.Create);
                System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode);
                xtw.WriteProcessingInstruction("xml", "version='1.0'");
                xtw.WriteProcessingInstruction("xml-stylesheet", "type='text/xsl' href='" + oPaths.styleSheet + "'");
                ds.WriteXml(xtw);
                ds.Tables.Remove(dt);
                xtw.Close();
                if (fs != null) fs.Close();
                StreamWriter sw = new StreamWriter(oPaths.styleSheet, false);
                string q = Convert.ToString('\"'); //quote
                sw.WriteLine("<xsl:stylesheet xmlns:xsl=" + q + "http://www.w3.org/1999/XSL/Transform" + q +  " version=" + q + "1.0" + q + ">");
                sw.WriteLine("<xsl:template match=" + q + "/" + q + ">");
                sw.WriteLine(" <HTML>");
              sw.WriteLine("<HEAD>");
               sw.WriteLine("<STYLE>");
                sw.WriteLine(".HDR { background-color:white;font-weight:bold }");
                sw.WriteLine("</STYLE>");
                sw.WriteLine("</HEAD>");
                sw.WriteLine("<BODY>");
                sw.WriteLine("<TABLE>");
                foreach(DataColumn dc in dt.Columns)
                    sw.WriteLine("<COLGROUP WIDTH=" + q + "100" + q + " ALIGN=" + q + "LEFT" + q + "></COLGROUP>");
                foreach(DataColumn dc in dt.Columns)
                    sw.WriteLine("<TD CLASS=" + q + "HDR" + q + ">" + dc.ColumnName + "</TD>");
                sw.WriteLine("<xsl:for-each select=" + q + "NewDataSet/Table" + q + ">");
                sw.WriteLine("<TR>");
                foreach(DataColumn dc in dt.Columns)
                    sw.WriteLine("<TD><xsl:value-of select=" + q + dc.ColumnName + q + "/></TD>");
                sw.WriteLine("</TR>");
                sw.WriteLine("</xsl:for-each>");
                sw.WriteLine("</TABLE>");
                sw.WriteLine("</BODY>");
                sw.WriteLine("</HTML>");
                sw.WriteLine("</xsl:template>");
                sw.WriteLine("</xsl:stylesheet>");
                sw.Close();
                System.Diagnostics.Process.Start(oPaths.exportSheet);
            }
    
    
    


    Thursday, December 17, 2009 3:53 AM
  • I ended up trying Rama Krishna's export class from Code Project. It uses xml.

    It works but I don't like how CSV solutions tend to convert numeric datatypes to strings.  I wonder if a schema.ini file will cause Excel to determine the correct datatypes? I'll have to try that when I get time.

    Thursday, December 17, 2009 1:21 PM
  •  The problem with the schema.ini file is that I need a setup immediately readable by OpenOffice Org spreadsheet (Calc), as well as Excel.  Another thought was to use the dataSet.WriteXML method and dataSet.WriteXmlSchema to save the datatypes, but Excel doesn't much like such standard XML. So my solution was to learn enough Excel XML to manually create a spreadsheet. I just typed in a couple of values into an Excel spreadsheet, saved it as "Excel XML Spreadsheet Format", and then viewed the xml in Wordpad. Basically has a long intro (a bunch of xml tags), then the rows of data, and then a long conclusion (more xml tags). The intro and conclusion were basically a copy-and-paste effort.  And then I just used a loop to write out the rows. I hear this is a pretty common technique - I'm now surprised I never stumbled upon articles on this topic before. So here's my solution (only took 5 seconds to export 25,000 rows, 40 columns). And it preserved the numeric datatypes instead of converting them to string (that was one of my main goals). Needs more testing.

      /// <summary>
            /// Pass in null/nothing for either selectedRows or the datatable.
            /// </summary>
            public void subExportToExcel(List<DataRow> selectedRows, DataTable dtOriginal,  string tableName, string pathToSheet)
            {
                string q = Convert.ToString('\"'); //quotation mark
                StreamWriter sw = new StreamWriter(pathToSheet);
                sw.WriteLine("<?xml version=" + q+ "1.0"+ q + "?>");
                sw.WriteLine("<?mso-application progid=" + q + "Excel.Sheet" + q + "?>");
                sw.WriteLine("<Workbook xmlns=" + q + "urn:schemas-microsoft-com:office:spreadsheet" + q);
                sw.WriteLine("xmlns:o=" + q + "urn:schemas-microsoft-com:office:office" + q);
                sw.WriteLine("xmlns:x=" + q + "urn:schemas-microsoft-com:office:excel" + q);
                sw.WriteLine("xmlns:ss=" + q + "urn:schemas-microsoft-com:office:spreadsheet" + q);
                sw.WriteLine ("xmlns:html=" + q + "http://www.w3.org/TR/REC-html40" + q + ">");
                sw.WriteLine("<DocumentProperties xmlns=" + q + "urn:schemas-microsoft-com:office:office" + q + ">");
                sw.WriteLine("<Author>ROMEO</Author>");
                sw.WriteLine("<LastAuthor>ROMEO</LastAuthor>");
                sw.WriteLine("<Created>2009-12-18T03:04:39Z</Created>");
                sw.WriteLine("<Version>11.9999</Version>");
                sw.WriteLine("</DocumentProperties>");
                sw.WriteLine("<ExcelWorkbook xmlns=" + q + "urn:schemas-microsoft-com:office:excel" + q + ">");
                sw.WriteLine("<WindowHeight>8400</WindowHeight>");
                sw.WriteLine("<WindowWidth>6615</WindowWidth>");
                sw.WriteLine("<WindowTopX>480</WindowTopX>");
                sw.WriteLine("<WindowTopY>60</WindowTopY>");
                sw.WriteLine("<ProtectStructure>False</ProtectStructure>");
                sw.WriteLine("<ProtectWindows>False</ProtectWindows>");
                sw.WriteLine("</ExcelWorkbook>");
                sw.WriteLine("<Styles>");
                sw.WriteLine("<Style ss:ID=" + q + "Default" + q + " ss:Name=" + q + "Normal" + q + ">");
                sw.WriteLine("<Alignment ss:Vertical=" + q + "Bottom"+ q + "/>");
                sw.WriteLine("<Borders/>");
                sw.WriteLine("<Font/>");
                sw.WriteLine("<Interior/>");
                sw.WriteLine("<NumberFormat/>");
                sw.WriteLine("<Protection/>");
                sw.WriteLine("</Style>");
                sw.WriteLine("</Styles>");
                sw.WriteLine("<Worksheet ss:Name=" + q + tableName + q + ">");
                if (selectedRows == null || selectedRows.Count == 0)
                {
                    selectedRows = new List<DataRow>();
                    foreach (DataRow row in dtOriginal.Rows) selectedRows.Add(row);
                }
                if (dtOriginal == null) dtOriginal = selectedRows[0].Table;
                List<string> colNames = new List<string>();
                List<string> dataTypes = new List<string>();
                foreach (DataColumn dc in dtOriginal.Columns)
                {//Just in case Excel is picky about column names...
                    string modifiedcolName = dc.ColumnName.Replace(' ', '_').Replace('.', '#');
                    colNames.Add(modifiedcolName);
                    string dataType = "String";
                    switch (dc.DataType.ToString())
                    {
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Decimal":
                        case "System.Double":
                        case "System.Single": dataType = "Number"; break;
                    }
                    dataTypes.Add(dataType);
                }
                string numCols = dtOriginal.Columns.Count.ToString();
                string numRows = Convert.ToString(selectedRows.Count + 1);
                sw.WriteLine("<Table x:FullColumns=" + q + "1" + q + " x:FullRows=" + q + "1" + q + ">");
                sw.WriteLine("<Column ss:Index=" + q + "3" + q + " ss:Width=" + q + "53.25" + q + "/>");
                sw.WriteLine("<Row>"); //The first row is the column names. 
                foreach (string col in colNames)
                    sw.WriteLine("<Cell><Data ss:Type=" + q + "String" + q + ">" + col + "</Data></Cell>");
                sw.WriteLine("</Row>"); //ends the row of column names. 
                foreach (DataRow row in selectedRows) 
                 {
                       sw.WriteLine("<Row>");
                       for (int i = 0; i <= colNames.Count - 1; i++)
                       {
                           string val = String.Empty;
                           if (!row.IsNull(i)) val = row[i].ToString();
                           sw.WriteLine("<Cell><Data ss:Type=" + q + dataTypes[i] + q + ">" + val + "</Data></Cell>");
                       }
                       sw.WriteLine("</Row>");
                }
               sw.WriteLine("</Table>");
                sw.WriteLine("<WorksheetOptions xmlns=" + q + "urn:schemas-microsoft-com:office:excel" + q + ">");
                sw.WriteLine("<Selected/>");
                sw.WriteLine("<ProtectObjects>False</ProtectObjects>");
                sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
                sw.WriteLine("</WorksheetOptions>");
                sw.WriteLine("</Worksheet>");
                sw.WriteLine("</Workbook>");
                sw.Close();
            }
    


    • Marked as answer by jal2 Friday, December 18, 2009 6:05 AM
    Friday, December 18, 2009 6:00 AM
  • Hello,

    you can easily export DataTable to Excel with this Excel .NET library.

    Here is a sample Excel C# code how to accomplish this:

    // Create new ExcelFile.
    var ef = new ExcelFile();
    
    // Add new worksheet to the file.
    var ws = ef.Worksheets.Add(dataTable.TableName);
    
    // Insert the data from DataTable to the worksheet starting at cell "A1".
    ws.InsertDataTable(dataTable, "A1", true);
    
    // Save the file to XLS format.
    ef.SaveXls("DataTable.xls");
    

    Friday, October 29, 2010 9:28 AM