Excel与datable的转换 用于Excel的导入

转载 2012年03月22日 12:17:47

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.IO;
/// <summary>
///Excel 的摘要说明
/// </summary>
public class Excel
{

    //DataTable中的数据导出Excel文件
    /// <summary>
    /// 将DataTable中的数据导出到指定的Excel文件中
    /// </summary>
    /// <param name="page">Web页面对象</param>
    /// <param name="tab">包含被导出数据的DataTable对象</param>
    /// <param name="FileName">Excel文件的名称</param>
    public static void Export(System.Web.UI.Page page, System.Data.DataTable tab, string FileName)
    {
        System.Web.HttpResponse httpResponse = page.Response;
        System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
        dataGrid.DataSource = tab.DefaultView;
        dataGrid.AllowPaging = false;
        dataGrid.HeaderStyle.BackColor = System.Drawing.Color.Green;
        dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        dataGrid.HeaderStyle.Font.Bold = true;
        dataGrid.DataBind();
        httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); //filename="*.xls";
        httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        httpResponse.ContentType = "application/ms-excel";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        dataGrid.RenderControl(hw);
        string filePath = page.Server.MapPath("~/") + "Files//" + FileName;
        System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
        sw.Write(tw.ToString());
        sw.Close();
        DownFile(httpResponse, FileName, filePath);
        httpResponse.End();
    }
    private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
    {
        try
        {
            Response.ContentType = "application/octet-stream";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" +
            HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
            System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
            long fLen = fs.Length;
            int size = 102400;//每100K同时下载数据
            byte[] readData = new byte[size];//指定缓冲区的大小
            if (size > fLen) size = Convert.ToInt32(fLen);
            long fPos = 0;
            bool isEnd = false;
            while (!isEnd)
            {
                if ((fPos + size) > fLen)
                {
                    size = Convert.ToInt32(fLen - fPos);
                    readData = new byte[size];
                    isEnd = true;
                }
                fs.Read(readData, 0, size);//读入一个压缩块
                Response.BinaryWrite(readData);
                fPos += size;
            }
            fs.Close();
            System.IO.File.Delete(fullPath);
            return true;
        }
        catch
        {
            return false;
        }
    }

    //将指定Excel文件中的数据转换成DataTable
    /// <summary>
    /// 将指定Excel文件中的数据转换成DataTable对象,供应用程序进一步处理
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static System.Data.DataTable Import(string filePath)
    {
        System.Data.DataTable rs = new System.Data.DataTable();
        bool canOpen = false;

        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=" + filePath + ";" +
        "Extended Properties=/"Excel 8.0;/"");

        try//尝试数据连接是否可用
        {
            conn.Open();
            conn.Close();
            canOpen = true;
        }
        catch { }

        if (canOpen)
        {
            try//如果数据连接可以打开则尝试读入数据
            {
                OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
                OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
                myData.Fill(rs);
                conn.Close();
            }
            catch//如果数据连接可以打开但是读入数据失败,则从文件中提取出工作表的名称,再读入数据
            {
                string sheetName = GetSheetName(filePath);
                if (sheetName.Length > 0)
                {
                    OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "$]", conn);
                    OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
                    myData.Fill(rs);
                    conn.Close();
                }
            }
        }
        else
        {
            System.IO.StreamReader tmpStream = File.OpenText(filePath);
            string tmpStr = tmpStream.ReadToEnd();
            tmpStream.Close();
            rs = GetDataTableFromString(tmpStr);
            tmpStr = "";
        }
        return rs;
    }
    /// <summary>
    /// 将指定Html字符串的数据转换成DataTable对象 --根据“<tr><td>”等特殊字符进行处理
    /// </summary>
    /// <param name="tmpHtml">Html字符串</param>
    /// <returns></returns>
    private static DataTable GetDataTableFromString(string tmpHtml)
    {
        string tmpStr = tmpHtml;
        DataTable TB = new DataTable();
        //先处理一下这个字符串,删除第一个<tr>之前合最后一个</tr>之后的部分
        int index = tmpStr.IndexOf("<tr");
        if (index > -1)
            tmpStr = tmpStr.Substring(index);
        else
            return TB;

        index = tmpStr.LastIndexOf("</tr>");
        if (index > -1)
            tmpStr = tmpStr.Substring(0, index + 5);
        else
            return TB;

        bool existsSparator = false;
        char Separator = Convert.ToChar("^");

        //如果原字符串中包含分隔符“^”则先把它替换掉
        if (tmpStr.IndexOf(Separator.ToString()) > -1)
        {
            existsSparator = true;
            tmpStr = tmpStr.Replace("^", "^$&^");
        }

        //先根据“</tr>”分拆
        string[] tmpRow = tmpStr.Replace("</tr>", "^").Split(Separator);

        for (int i = 0; i < tmpRow.Length - 1; i++)
        {
            DataRow newRow = TB.NewRow();

            string tmpStrI = tmpRow[i];
            if (tmpStrI.IndexOf("<tr") > -1)
            {
                tmpStrI = tmpStrI.Substring(tmpStrI.IndexOf("<tr"));
                if (tmpStrI.IndexOf("display:none") < 0 || tmpStrI.IndexOf("display:none") > tmpStrI.IndexOf(">"))
                {
                    tmpStrI = tmpStrI.Replace("</td>", "^");
                    string[] tmpField = tmpStrI.Split(Separator);

                    for (int j = 0; j < tmpField.Length - 1; j++)
                    {
                        tmpField[j] = RemoveString(tmpField[j], "<font>");
                        index = tmpField[j].LastIndexOf(">") + 1;
                        if (index > 0)
                        {
                            string field = tmpField[j].Substring(index, tmpField[j].Length - index);
                            if (existsSparator) field = field.Replace("^$&^", "^");
                            if (i == 0)
                            {
                                string tmpFieldName = field;
                                int sn = 1;
                                while (TB.Columns.Contains(tmpFieldName))
                                {
                                    tmpFieldName = field + sn.ToString();
                                    sn += 1;
                                }
                                TB.Columns.Add(tmpFieldName);
                            }
                            else
                            {
                                newRow[j] = field;
                            }
                        }//end of if(index>0)
                    }

                    if (i > 0)
                        TB.Rows.Add(newRow);
                }
            }
        }

        TB.AcceptChanges();
        return TB;
    }

    /// <summary>
    /// 从指定Html字符串中剔除指定的对象
    /// </summary>
    /// <param name="tmpHtml">Html字符串</param>
    /// <param name="remove">需要剔除的对象--例如输入"<font>"则剔除"<font ???????>"和"</font>>"</param>
    /// <returns></returns>
    public static string RemoveString(string tmpHtml, string remove)
    {
        tmpHtml = tmpHtml.Replace(remove.Replace("<", "</"), "");
        tmpHtml = RemoveStringHead(tmpHtml, remove);
        return tmpHtml;
    }
    /// <summary>
    /// 只供方法RemoveString()使用
    /// </summary>
    /// <returns></returns>
    private static string RemoveStringHead(string tmpHtml, string remove)
    {
        //为了方便注释,假设输入参数remove="<font>"
        if (remove.Length < 1) return tmpHtml;//参数remove为空:不处理返回
        if ((remove.Substring(0, 1) != "<") || (remove.Substring(remove.Length - 1) != ">")) return tmpHtml;//参数remove不是<?????>:不处理返回

        int IndexS = tmpHtml.IndexOf(remove.Replace(">", ""));//查找“<font”的位置
        int IndexE = -1;
        if (IndexS > -1)
        {
            string tmpRight = tmpHtml.Substring(IndexS, tmpHtml.Length - IndexS);
            IndexE = tmpRight.IndexOf(">");
            if (IndexE > -1)
                tmpHtml = tmpHtml.Substring(0, IndexS) + tmpHtml.Substring(IndexS + IndexE + 1);
            if (tmpHtml.IndexOf(remove.Replace(">", "")) > -1)
                tmpHtml = RemoveStringHead(tmpHtml, remove);
        }
        return tmpHtml;
    }

    /// <summary>
    /// 将指定Excel文件中读取第一张工作表的名称
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    private static string GetSheetName(string filePath)
    {
        string sheetName = "";

        System.IO.FileStream tmpStream = File.OpenRead(filePath);
        byte[] fileByte = new byte[tmpStream.Length];
        tmpStream.Read(fileByte, 0, fileByte.Length);
        tmpStream.Close();

        byte[] tmpByte = new byte[]{Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(11),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),Convert.ToByte(0),
Convert.ToByte(30),Convert.ToByte(16),Convert.ToByte(0),Convert.ToByte(0)};

        int index = GetSheetIndex(fileByte, tmpByte);
        if (index > -1)
        {

            index += 16 + 12;
            System.Collections.ArrayList sheetNameList = new System.Collections.ArrayList();

            for (int i = index; i < fileByte.Length - 1; i++)
            {
                byte temp = fileByte[i];
                if (temp != Convert.ToByte(0))
                    sheetNameList.Add(temp);
                else
                    break;
            }
            byte[] sheetNameByte = new byte[sheetNameList.Count];
            for (int i = 0; i < sheetNameList.Count; i++)
                sheetNameByte[i] = Convert.ToByte(sheetNameList[i]);

            sheetName = System.Text.Encoding.Default.GetString(sheetNameByte);
        }
        return sheetName;
    }
    /// <summary>
    /// 只供方法GetSheetName()使用
    /// </summary>
    /// <returns></returns>
    private static int GetSheetIndex(byte[] FindTarget, byte[] FindItem)
    {
        int index = -1;

        int FindItemLength = FindItem.Length;
        if (FindItemLength < 1) return -1;
        int FindTargetLength = FindTarget.Length;
        if ((FindTargetLength - 1) < FindItemLength) return -1;

        for (int i = FindTargetLength - FindItemLength - 1; i > -1; i--)
        {
            System.Collections.ArrayList tmpList = new System.Collections.ArrayList();
            int find = 0;
            for (int j = 0; j < FindItemLength; j++)
            {
                if (FindTarget[i + j] == FindItem[j]) find += 1;
            }
            if (find == FindItemLength)
            {
                index = i;
                break;
            }
        }
        return index;
    }
}

相关文章推荐

小猫统计导入excel数据用于批量证书打印时,时间数据格式的规范化处理

导入excel数据的时候,由于时间格式不统一导致的各种问题很多,格式转化经常出问题,此处记录一下处理过程。 excel单元格内容:          小猫统计程序中,项目——自动建表导入外部数...

对大数据量Excel文件自动排版、转换成PDF用于印刷出版

原文: http://pan.baidu.com/s/1pJDlk4V 2015.3. 目录 一、主要功能 2 二、系统需求 3 三、文件名要求 3 四、目录说明: 4 1、Excel目录 4 2、m...
  • bq_cui
  • bq_cui
  • 2015-04-04 22:15
  • 3727

数据库数据(SQLSERVER/ACCESS/EXCEL)导入导出转换

熟悉SQL SERVER 2000的数据库管理员都知道,其DTS可以进行数据的导入导出,其实,我们也可以使用Transact-SQL语句进行导入导出操作。在 Transact-SQL语句中,我们主要使...

PHPExcel:用于Excel等文档生成的PHP开源类库

PHPExcel是一个十分强大的文档生成PHP开源类库,它不仅支持生成Excel(.xls)、Excel2007(.xlsx)文档,同时也支持PDF、HTML、CSV文档的生成。此外,PHPExcel...

PoiUtil.java 用于excel间sheet复制

前言:apache提供的poi功能确实比较强大,但是不明白为什么没有相应的方法实现不同excel文件中sheet的复制功能。这也是本文整理PoiUtil工具类的初衷。网上有相关的解决方案,在参考了网上...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)