ExcelHelper

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Collections;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.IO;
 
namespace Support.ExcelHelper
{
 
/// <summary>
/// Helper class for generating XML Excel spreadsheets
/// http://stackoverflow.com/questions/3978463/create-csv-with-asp-net-and-open-in-excel/3978982#3978982
/// </summary>
    public class XlsImport
    {
        public int StartRow = 0;
        public int ColumnNameRow = -1;
        public string FileName = "";
 
        // Name of sheet to get when opening existing file, or new sheet name
        public string SheetName
        {
            get
            {
                if (_SheetName == "")
                {
                    return ("Sheet 1");
                }
                else
                {
                    return (_SheetName);
                }
            }
            set { _SheetName = value; }
        } protected string _SheetName = "";
 
        public XlsWorksheet Worksheet = null;
        public void Load()
        {
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + FileName + ";" +
            "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1;\"";
 
 
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
 
            OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [" + SheetName + "$]", conn);
 
            Worksheet = new XlsWorksheet();
 
            using (OleDbDataReader reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
            {
                int rowNum = 0;
                while (reader.Read())
                {
                    XlsRow row = new XlsRow();
                    for (int i = 0; i < reader.VisibleFieldCount; i++)
                    {
                        if (rowNum == 0 && ColumnNameRow < 0)
                        {
                            XlsColumn col = new XlsColumn();
                            col.Name = reader.GetName(i);
                            Worksheet.Columns.Add(col);
                        } else if (rowNum == ColumnNameRow) {
                            XlsColumn col = new XlsColumn();
                            col.Name = reader[i].ToString();
                            Worksheet.Columns.Add(col);
                        }
                        XlsCell cell = new XlsCell();
                        cell.Text = reader[i].ToString();
                        row.Cells.Add(cell);
                    }
                    if (rowNum >= StartRow)
                    {
                        Worksheet.Rows.Add(row);
                    }
                    rowNum++;
                }
            }
        }
    }
    public class XlsWorkbook
    {
        public string TemplateFile = "";
        public bool UseTemplate
        {
            get { return (TemplateFile != ""); }
        }
        public XlsWorksheetCollection Worksheets = new XlsWorksheetCollection();
 
        public string XmlOutput()
        {
 
            StringBuilder output = new StringBuilder();
            output.Append(ExcelHeader());
            for (int i = 0; i < Worksheets.Count; i++)
            {
                XlsWorksheet wrk = (XlsWorksheet)Worksheets[i];
                string wsName = wrk.Name;
                if (wsName == "")
                {
                    wsName = "Sheet " + Convert.ToString(i + 1);
                }
                output.Append("<Worksheet ss:Name=\"" + wsName + "\"><Table>");
                // Check for column attributes
                for (int j=0; j<wrk.Columns.Count;j++) {
                    output.Append(wrk.Columns[j].XmlOutput());
                }
                output.Append("\n");
                for (int j = 0; j < wrk.Rows.Count; j++)
                {
                    XlsRow theRow = (XlsRow)wrk.Rows[j];
 
                    output.Append("<Row ss:AutoFitHeight=\"1\" >\n");
                    for (int k = 0; k < theRow.Cells.Count; k++)
                    {
                        XlsCell theCell = theRow.Cells[k];
                        output.Append("<Cell><Data ss:Type=\"" + DataTypeString(theCell.DataType) + "\">" + theCell.Text + "</Data></Cell>\n");
                    }
                    output.Append("</Row>");
                }
                output.Append("</Table></Worksheet>");
            }
            output.Append("</Workbook>");
            return (output.ToString());
        }
        private string DataTypeString(DataTypes dataType)
        {
            string result="";
            switch (dataType)
            {
                case DataTypes.String:
                    result = "String";
                    break;
                case DataTypes.Numeric:
                    result = "Number";
                    break;
                default:
                    result = "String";
                    break;
            }
            return (result);
        }
        private string ExcelHeader()
        {
            // Excel header
 
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<?xml version=\"1.0\"?>\n");
            sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
            sb.Append(
              "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
            sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
            sb.Append(
              "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            sb.Append("</DocumentProperties>");
            sb.Append(
              "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
            sb.Append("<ProtectStructure>False</ProtectStructure>\n");
            sb.Append("<ProtectWindows>False</ProtectWindows>\n");
            sb.Append("</ExcelWorkbook>\n");
            return sb.ToString();
        }
    }
    public class XlsWorksheet
    {
        public XlsRowCollection Rows = new XlsRowCollection();
        public XlsColumnCollection Columns=new XlsColumnCollection();
        public string Name = "";
        public XlsCell GetCell(int row, int col)
        {
            return(this.Rows[row].Cells[col]);
        }
 
    }
    public class XlsColumn
    {
        public bool AutoFitWidth=true;
        public double? Width = null;
        public string Name="";
        public string XmlOutput()
        {
            string output = "<Column";
            if (AutoFitWidth) {
                output += " ss:AutoFitWidth=\"" + ((AutoFitWidth) ? "1" : "0") + "\"";
            }
            output += " />";
            return (output);
 
        }
    }
    public class XlsRow
    {
        public XlsCellCollection Cells = new XlsCellCollection();
        public bool? AutoFitHeight = null;
        public string XmlOutput()
        {
            string output = "<Row";
            if (AutoFitHeight != null)
            {
                output += " ss:AutoFitHeight=\"" + (((bool)AutoFitHeight) ? "1" : "0") + "\">\n";
            }
            return (output);
        }
    }
    public class XlsCell
    {
        public List<String> ZeroValueStrings=new List<String>();
        public XlsCell()
        {
            Initialize();
        }
        public XlsCell(string text)
        {
            Initialize();
            Text = text;
            if (IsNumeric(text))
            {
                DataType = DataTypes.Numeric;
            }
            else if (IsValidDate(text))
            {
                DataType = DataTypes.DateTime;
            }
 
        }
        public Object Value
        {
            get
            {
                switch (DataType)
                {
                    case DataTypes.Numeric:
                        return (ValueDouble);
                    case DataTypes.DateTime:
                        return (ValueDateTime);
                    case DataTypes.String:
                        return (Text);
                    default:
                        return (Text);
                }
 
            }
        }
        private void Initialize()
        {
            ZeroValueStrings.Add("");
            ZeroValueStrings.Add("-");
        }
        public DateTime? ValueDateTime
        {
            get {
                if (IsValidDate(Text)) {
                    return(Convert.ToDateTime(Text));
                } else {
                    return(null);
                }
            }
        }
 
        public double ValueDouble
        {
             get 
            {
                if (Text==null || ZeroValueStrings.Contains(Text))
                {
                    return (0);
                }
                else
                {
                    double result=0;
                    if (CleanNumber(Text,out result)) {
                        return(result);
                    } else {
                        throw new Exception("Invalid double value '" + Text);
                    }
                }
            }
        }
        public long ValueLong
        {
            get
            {
                if (Text==null || ZeroValueStrings.Contains(Text))
                {
                    return (0);
                }
                else
                {
                    long result=0;
                    if (CleanNumber(Text,out result)) {
                        return(result);
                    } else {
                        throw new Exception("Invalid long value '" + Text);
                    }
                }
            }
        }
        public string ValueString
        {
            get
            {
                return (Text);
            }
        }
        public string Text {
            get {
                if (_Text==null) {
                    return(String.Empty);
                } else {
                    return(_Text);
                }
            }
            set
            {
                _Text = value;
            }
        } protected string _Text= "";
 
        public DataTypes DataType = DataTypes.String;
        public static bool IsNumeric(string value)
        {
            bool pass;
            try
            {
 
                double theResult = Convert.ToDouble(value);
                pass = true;
            }
            catch
            {
                pass = false;
            }
            return (pass);
        }
        public static bool IsValidDate(string DateText)
        {
            DateTime d;
            try
            {
                d = DateTime.Parse(DateText);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static bool CleanNumber(string NumberString, out long Number)
        {
            long numericResult;
            string theCleanNumber;
            bool isNumber = false;
            bool isPct = NumberString.IndexOf("%") >= 0;
            theCleanNumber = NumberString.Replace("$", "").Replace(",", "").Replace("%", "");
            if (long.TryParse(theCleanNumber, out numericResult))
            {
                Number = numericResult;
                if (isPct)
                {
                    Number = numericResult / 100;
                }
                else
                {
                    Number = numericResult;
                }
                isNumber = true;
            }
            else
            {
                Number = 0;
            }
            return isNumber;
        }
        public static bool CleanNumber(string numberString, out double Number)
        {
            double numericResult;
            string theCleanNumber;
            bool isNumber = false;
            bool isPct = numberString.IndexOf("%") >= 0;
            theCleanNumber = numberString.Replace("$", "").Replace(",", "").Replace("%", "");
 
            if (Double.TryParse(theCleanNumber, out numericResult))
            {
                if (isPct)
                {
                    Number = numericResult / 100;
                }
                else
                {
                    Number = numericResult;
                }
                isNumber = true;
            }
            else
            {
                Number = 0;
            }
            return isNumber;
        }
    }
    public class XlsWorksheetCollection : CollectionBase
    {
        public int Add(XlsWorksheet worksheet)
        {
            //fieldName.OnChanged += new Field.ChangedHandler(OnChanged);
            return (InnerList.Add(worksheet));
        }
        public void Insert(int index, XlsWorksheet worksheet)
        {
            //fieldName.OnChanged += new Field.ChangedHandler(OnChanged);
            InnerList.Insert(index, worksheet);
        }
 
        public void Remove(XlsWorksheet worksheet)
        {
            InnerList.Remove(worksheet);
        }
        public XlsWorksheet this[int index]
        {
            get { return ((XlsWorksheet)InnerList[index]); }
        }
 
    }
    public class XlsRowCollection : CollectionBase
    {
        public int Add(XlsRow row)
        {
            return (InnerList.Add(row));
        }
        public void Insert(int index, XlsRow row)
        {
            InnerList.Insert(index, row);
        }
 
        public void Remove(XlsRow row)
        {
            InnerList.Remove(row);
        }
        public XlsRow this[int index]
        {
            get { return ((XlsRow)InnerList[index]); }
        }
    }
    public class XlsCellCollection : CollectionBase
    {
        public int Add(XlsCell cell)
        {
            return (InnerList.Add(cell));
        }
        public void Insert(int index, XlsCell cell)
        {
            InnerList.Insert(index, cell);
        }
 
        public void Remove(XlsCell cell)
        {
            InnerList.Remove(cell);
        }
        public XlsCell this[int index]
        {
            get { return ((XlsCell)InnerList[index]); }
        }
    }
    public class XlsColumnCollection : CollectionBase
    {
        public int Add(XlsColumn column)
        {
            return (InnerList.Add(column));
        }
        public void Insert(int index, XlsColumn column)
        {
            InnerList.Insert(index, column);
        }
 
        public void Remove(XlsColumn column)
        {
            InnerList.Remove(column);
        }
        public XlsColumn this[int index]
        {
            get { return ((XlsColumn)InnerList[index]); }
        }
 
    }
    public enum DataTypes
    {
        String = 1,
        DateTime=2,
        Numeric = 3,
        Formula = 4
    }
}