我写的一个ExcelHelper通用类,可用于读取或生成数据

读取或生成EXCEL数据的方法有很多,一般常见的有:

1.通过OFFICE EXCEL组件,优点:读取与生成EXCEL文件方便,缺点:服务器上必须安装OFFICE软件,且进程无法及时释放

2.通过第三方组件(比如:NPOI),优点:无需安装OFFICE软件,缺点:需要引入第三方组件,当然这个还是比较强的

3.通过把EXCEL当成数据库,连接后运用SQL语句读取,写入的话就自行拼接成HTML表格,优点:无需另外的组件,缺点:需要会SQL及拼接HTML表格较麻烦;

三种方法我都有用过,若开发BS网站程序,建议采用第二种、第三种方法,若开发CS结构,建议采用第一种或第二种;

以下是我针对BS端写的一个ExcelHelper通用类,可用于读取或生成数据,比较方便,技术原理是上述的第三种方法,代码如下,可能存在缺陷,高手见谅:

namespace ASOTS.Models
{
    public abstract class ExcelHelper
    {
        /// <summary>
        /// 获取EXCEL中指定sheet内容
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTableFromExcel(string filePath, string fileExt, string tableName, int colsCount)
        {
            string connstr = null;
            if (fileExt == ".xls")
            {
                connstr = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            }
            else
            {
                connstr = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            }

            using (OleDbConnection excelConn = new OleDbConnection(connstr))
            {
                excelConn.Open();

                //获取EXCEL架构信息
                DataTable schemaTable = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });

                //判断指定sheet名是否存在
                DataView schemaView = new DataView(schemaTable);
                schemaView.RowFilter = "TABLE_NAME='" + tableName + "$'";
                schemaTable = schemaView.ToTable();

                if (schemaTable != null && schemaTable.Rows.Count > 0)
                {
                    DataTable schemaTable_Cols = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName + "$", null });
                    schemaView = new DataView(schemaTable_Cols);
                    schemaView.RowFilter = "ORDINAL_POSITION<=" + colsCount.ToString();
                    schemaView.Sort = "ORDINAL_POSITION asc";
                    schemaTable_Cols = schemaView.ToTable();
                    string selectCols = "";
                    for (int i = 0; i < schemaTable_Cols.Rows.Count; i++)
                    {
                        selectCols += "," + schemaTable_Cols.Rows[i]["COLUMN_NAME"].ToString();
                    }

                    selectCols = selectCols.Substring(1);

                    //查询sheet中的数据
                    string strSql = "select " + selectCols + " from [" + tableName + "$]";
                    OleDbDataAdapter da = new OleDbDataAdapter(strSql, excelConn);
                    DataSet ds = new DataSet();
                    da.Fill(ds, tableName);
                    excelConn.Close();
                    return ds.Tables[tableName];
                }
                else
                {
                    excelConn.Close();
                    return null;
                }
            }

        }

        /// <summary>
        /// 将数据模型集合对象生成HTML表格字符串
        /// </summary>
        /// <param name="data"></param>
        /// <param name="tableAttributes"></param>
        /// <param name="headers"></param>
        /// <returns></returns>
        public static string SetDataToHtmlTable(IEnumerable data, string tableAttributes, params KeyValuePair<string, string>[] headers)
        {
            StringBuilder htmlTableBuilder = new StringBuilder();
            htmlTableBuilder.AppendFormat("<table {0}>", tableAttributes);

            if (data.GetEnumerator().Current == null)
            {
                throw new Exception("没有获取到任何数据!");
            }

            Type t = data.GetEnumerator().Current.GetType();

            string[] cellIndexs = new string[headers.Count()];

            htmlTableBuilder.Append("<tr>");
            for (int i = 0; i < headers.Count(); i++)
            {
                cellIndexs[i] = headers[i].Key;
                htmlTableBuilder.AppendFormat("<th>{0}</th>", headers[i].Value);
            }
            htmlTableBuilder.Append("</tr>");

            foreach (var item in data)
            {
                htmlTableBuilder.Append("<tr>");
                for (int i = 0; i < cellIndexs.Length; i++)
                {
                    object pValue = t.GetProperty(cellIndexs[i]).GetValue(item, null);
                    htmlTableBuilder.AppendFormat("<td>{0}</td>", pValue);
                }
                htmlTableBuilder.Append("</tr>");
            }

            htmlTableBuilder.Append("</table>");

            return htmlTableBuilder.ToString();
        }



        /// <summary>
        /// 将DataTable对象生成HTML表格字符串
        /// </summary>
        /// <param name="data"></param>
        /// <param name="tableAttributes"></param>
        /// <param name="headers"></param>
        /// <returns></returns>
        public static string SetDataToHtmlTable(DataTable dataTable, string tableAttributes, params KeyValuePair<string, string>[] headers)
        {
            StringBuilder htmlTableBuilder = new StringBuilder();
            htmlTableBuilder.AppendFormat("<table {0}>", tableAttributes);

            htmlTableBuilder.Append("<tr>");
            for (int i = 0; i < headers.Count(); i++)
            {
                htmlTableBuilder.AppendFormat("<th>{0}</th>", headers[i].Value);
            }
            htmlTableBuilder.Append("</tr>");

            foreach (DataRow row in dataTable.Rows)
            {
                htmlTableBuilder.Append("<tr>");
                for (int i = 0; i < headers.Count(); i++)
                {
                    htmlTableBuilder.AppendFormat("<td>{0}</td>", row[headers[i].Key]);
                }
                htmlTableBuilder.Append("</tr>");
            }

            htmlTableBuilder.Append("</table>");

            return htmlTableBuilder.ToString();
        }

    }

    public class KeyValueList<TKey, TValue> : List<KeyValuePair<TKey, TValue>>
    {
        public void Add(TKey key, TValue value)
        {
            base.Add(new KeyValuePair<TKey, TValue>(key, value));
        }
    }
}

调用方法如下:

//读数据
DataTable resultTable = ExcelHelper.GetTableFromExcel(saveFilePath, fileExt, "data", 10);

//生成表格(以下是MVC调用,WEBFORM同理)
KeyValueList<string, string> headers = new KeyValueList<string, string>() { 
                        {"year","年 份"},
                        {"month","月 份"},
                        {"stage1count","一 阶"},
                        {"stage2count","二 阶"},
                        {"stage3count","三 阶"},
                        {"stage4count","四 阶"},
                        {"yearincount","一年内进厂"},
                        {"stagetotalcount","基盘客户总数"},
                        {"stage1rate","一阶占比"},
                        {"stage2rate","二阶占比"},
                        {"stage3rate","三阶占比"},
                        {"stage4rate","四阶占比"}
                };

                string tableAttributes = "border='1' cellspacing='3' cellpadding='3'";

                string htmlTable=ExcelHelper.SetDataToHtmlTable(model, tableAttributes, headers.ToArray());
                byte[] b = System.Text.Encoding.UTF8.GetBytes(htmlTable);

                return File(b, "application/vnd.ms-excel", string.Format("StageSummary_{0}_{1}_{2}.xls",orgcode,startym,endym));

其中:KeyValueList是我创建的一个集合类,主要用于生成表头,以及表头与数据列对应,之所以写成类,是因为若直接使用:List<KeyValuePair<TKey, TValue>>,则无法直接使用集合初始化器,就必需得一个一个的添加对象,有些繁琐,增加了ADD方法后,就可以直接用:new KeyValueList<string, string>() {{"",""},...}很方便,有人可能说为什么不用SortedDictionary等现有排序类,原因是SortedDictionary是基于Key排序,而此处是采用ADD的先后顺序来固定顺序的。

更多IT相关资讯与技术文章,欢迎光临我的个人网站:http://www.zuowenjun.cn/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值