locked
Windows application storing data in excel sheet RRS feed

  • Question

  • I am creating an library app using windows application.

    I want to store the data in excel sheet..

    Please tell me how to store data in excel sheet using windows application 

    Monday, August 26, 2013 8:48 AM

Answers

  • The code below I developed using OleDB method.  It creates an XLS workbook from a DataTable without requiring a Office License.  I used the Jet driver which is winhdows (doesn't support XLSX) format.  The ACE driver requires an Office License to run and ACE will not create a new workbook (only can modify exisitng workbooks).  The is code was intended to write DataTables with 400,000 rows.  So the code creates worksheet like sheet_1, sheet_2, ...  An XLS file only allows 65,536 rows per sheet so I had to all more than one sheet.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    using System.Data;
    using System.IO;
    using System.Windows.Forms;
    namespace ConsoleApplication2
    {
        struct ColumnHeader
        {
            public string name;
            public string format;
        }
        class Program
        {
            static void Main(string[] args)
            {
                //build a test datatable
                DataTable table = new DataTable();
                DataRow newRow;
                table.Columns.Add("ColA");
                table.Columns.Add("ColB");
                newRow = table.Rows.Add();
                newRow["ColA"] = "abc";
                newRow["ColB"] = "123";
                newRow = table.Rows.Add();
                newRow["ColA"] = "def";
                newRow["ColB"] = "456";
                Workbook workbook = new Workbook();
                workbook.CreateWorkbook(@"c:\temp\newbook1.xls");
                workbook.CreateHeaders(table);
                workbook.WriteData(table);
                workbook.Dispose();
            }
        }
        class Workbook : IDisposable
        {
            OleDbConnection conn;
            OleDbCommand cmd;
            string connectionStr;
            const int MAX_COLUMN_WIDTH = 255;
            List<ColumnHeader> columnDefinitions = new List<ColumnHeader>();
            long rowCount = 0;
            int sheetCount = 0;
            public Boolean CreateWorkbook(string filename)
            {
                Boolean succesful = true;
                FileInfo file = new FileInfo(filename);
                DialogResult answer = new DialogResult();
                answer = DialogResult.Yes;
                if (file.Exists == true)
                {
                    // If the workbooks already exist, prompt to delete.
                    answer = MessageBox.Show(
                       "Delete existing workbooks (" + filename + ")?",
                       "Workbook Exists",
                    MessageBoxButtons.YesNo);
                    if (answer == DialogResult.Yes)
                    {
                        try
                        {
                            file.Delete();
                            Application.DoEvents();
                        }
                        catch
                        {
                            MessageBox.Show("Cannot Delete file");
                            succesful = false;
                        }
                    }
                }
                if (succesful)
                {
                    connectionStr = string.Format(
                       "Provider=Microsoft.Jet.OLEDB.4.0;" +
                       @"Data Source={0};" +
                       "Extended Properties=\"Excel 8.0;HDR=YES\"",
                       filename);
                    try
                    {
                        conn = new OleDbConnection();
                        conn.ConnectionString = connectionStr;
                        conn.Open();
                        cmd = new OleDbCommand();
                        cmd.Connection = conn;
                        Application.DoEvents();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show("Cannot create Excel workbook, Error : {0}", e.Message);
                        succesful = false;
                    }
                }
                return succesful;
            }
            public Boolean CreateHeaders(DataTable table)
            {
                Boolean succesful = true;
                string columnFormatText = "";
                string newcolFormat = "";
                foreach (DataColumn col in table.Columns)
                {
                    switch (col.DataType.Name)
                    {
                        case "Byte":
                            newcolFormat = "'" + col.ColumnName + "' byte";
                            break;
                        case "Int16":
                            newcolFormat = "'" + col.ColumnName + "' integer";
                            break;
                        case "Int32":
                            newcolFormat = "'" + col.ColumnName + "' integer";
                            break;
                        case "Int64":
                            newcolFormat = "'" + col.ColumnName + "' long";
                            break;
                        case "Double":
                            newcolFormat = "'" + col.ColumnName + "' double";
                            break;
                        case "Guid":
                        case "String":
                            newcolFormat = "'" + col.ColumnName + "' char(" + MAX_COLUMN_WIDTH.ToString() + ")";
                            break;
                        case "DateTime":
                            newcolFormat = "'" + col.ColumnName + "' date";
                            break;
                        default:
                            break;
                    }
                    if (columnFormatText.Length == 0)
                    {
                        columnFormatText = newcolFormat;
                    }
                    else
                    {
                        columnFormatText += "," + newcolFormat;
                    }
                    ColumnHeader newColHeader = new ColumnHeader();
                    newColHeader.name = col.ColumnName;
                    newColHeader.format = newcolFormat;
                    columnDefinitions.Add(newColHeader);
                } //end foreach
                try
                {
                    sheetCount += 1;
                    rowCount = 1;
                    cmd.CommandText = string.Format("CREATE TABLE Results_{0} ({1})",
                        sheetCount.ToString(), columnFormatText);
                    cmd.ExecuteNonQuery();
                    rowCount += 1;
                    Application.DoEvents();
                }
                catch (Exception e)
                {
                    MessageBox.Show("Cannot add columns to Excel workbook ({0})", e.Message);
                    succesful = false;
                }
                return succesful;
            }
            public Boolean WriteData(DataTable table)
            {
                Boolean succesful = true;
                string columnValuesText = "";
                string columnValues = "";
                int rownumber = 0;
                string columnsHeaderText;
                foreach (DataRow row in table.Rows)
                {
                    try
                    {
                        int colIndex = 0;
                        columnValuesText = "";
                        columnsHeaderText = "";
                        foreach (var colText in row.ItemArray)
                        {
                            if (!(colText is DBNull))
                            {
                                if (columnsHeaderText.Length == 0)
                                {
                                    columnsHeaderText = "'" + columnDefinitions[colIndex].name + "'";
                                }
                                else
                                {
                                    columnsHeaderText += "," + "'" + columnDefinitions[colIndex].name + "'";
                                }
                                if (colText.GetType().ToString() == "System.String")
                                {
                                    columnValues = (string)colText;
                                    if (columnValues.Length > (MAX_COLUMN_WIDTH - 2))
                                    {
                                        columnValues = columnValues.Substring(0, MAX_COLUMN_WIDTH - 2);
                                    }
                                    //Mar 13, 2013 changed from: columnValues to: columnValuesText 
                                    if (columnValuesText.Length == 0)
                                    {
                                        columnValuesText = "'" + columnValues + "'";
                                    }
                                    else
                                    {
                                        columnValuesText += ",'" + columnValues + "'";
                                    }
                                }
                                else
                                {
                                    if (columnValuesText.Length == 0)
                                    {
                                        columnValuesText = "'" + colText + "'";
                                    }
                                    else
                                    {
                                        columnValuesText += ",'" + colText + "'";
                                    }
                                }
                            }
                            colIndex += 1;
                        }
                        if (rowCount > 65536)
                        {
                            CreateHeaders(table);
                        }
                        cmd.CommandText = string.Format("INSERT INTO Results_{0} ({1}) values ({2})",
                            sheetCount.ToString(), columnsHeaderText, columnValuesText);
                        rowCount += 1;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        string errormsg = string.Format("Cannot cannot add row to Excel workbook {0}",
                            e.Message);
                        MessageBox.Show("Cannot cannot add row to Excel workbook");
                        succesful = false;
                    }
                    rownumber++;
                }
                return succesful;
            }
            public void Dispose()
            {
                conn.Close();
                columnDefinitions = null;
            }
        }
    }


    jdweng

    Monday, August 26, 2013 10:32 AM