根据Excel文档—动态建立目录结构

来源：互联网发布：淘宝游戏专营清退编辑：程序博客网时间：2024/06/03 18:12

//后台代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data;
using System.Text;
using Mis.Model.Product;
using Found.Base;
using Mis.ErpBll;
using Mis.ErpFacade;
using Mis.IErpBll.Product;
using Mis.IErpFacade.Product;
using System.Configuration;
using System.Data.SqlClient;
using Found.Db;
using Mis.IErpView;

namespace Mis.ErpView.Forms.Product
{
public partial class ImportDictroy : BasePage
{

        OleDbConnection OleCon = new OleDbConnection();
        OleDbCommand OleCmd = new OleDbCommand();
        OleDbDataAdapter OleDa = new OleDbDataAdapter();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (base.CheckLogin(Request, Response))
            {

this.RegisterPageRight();

            }
            else
                base.TransportToLogin(Request, Response);
        }

        protected void RegisterPageRight()
        {
            Dictionary<string, string> ActionList = new Dictionary<string, string>();
            ActionList.Add("Purchasing_productmge_ImportDictroy_manage", "产品结构");
            base.RegisterRight(ActionList, 81);
        }

private string UploadCSVFile(System.Web.UI.HtmlControls.HtmlInputFile inputFile)
{

            string oriFileName = string.Empty;
            string uploadFileName = string.Empty;
            string modifyFileName = string.Empty;
            string fileExt = "";//文件扩展名

            int fileSize = 0;
            try
            {

                if (inputFile.Value != string.Empty)
                {
                    //得到文件大小
                    fileSize = inputFile.PostedFile.ContentLength;
                    if (fileSize == 0)
                    {
                        this.lblMessage.Text = "导入的csv文件大小为0，请确认是否正确！";

                    }
                    //得到扩展名
                    fileExt = inputFile.Value.Substring(inputFile.Value.LastIndexOf(".") + 1);
                    if (fileExt.ToLower() != "csv")
                    {
                        this.lblMessage.Text = "你选择的文件格式是" + fileExt + "，请导入csv文件！";
                    }
                    //路径
                    uploadFileName = Server.MapPath("../../Upload/" + Data.GetString(ConfigurationManager.AppSettings["ProductCsvPath"]) + "/");

                    //新文件名
                    modifyFileName = System.Guid.NewGuid().ToString();
                    modifyFileName += "." + inputFile.Value.Substring(inputFile.Value.LastIndexOf(".") + 1);
                    //判断是否有该目录
                    System.IO.DirectoryInfo directory = new DirectoryInfo(uploadFileName);
                    if (!directory.Exists)
                    {
                        directory.Create();

                    }
                    oriFileName = uploadFileName + modifyFileName;
                    //如果存在删除文件
                    if (File.Exists(oriFileName))
                    {
                        File.Delete(oriFileName);
                    }

//上传文件
inputFile.PostedFile.SaveAs(oriFileName);

                }
                else
                {
                    return "";
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return oriFileName;

}

        protected void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                string fileName = UploadCSVFile(FileExcel1);
                FileInfo file = new FileInfo(fileName);

                if (CheakData(fileName))
                {
                    this.ImportCsv(file.FullName.Replace(file.Name, ""), file.Name);
                }
                DeleteFile(fileName);
            }
            catch (Exception ex)
            {
                this.lblMessage.Text = ex.Message;
            }

        }
        private void ImportCsv(string FilePath, string fileName)
        {
            if (FilePath == "")
            {
                this.lblMessage.Text = "对不起，请先选择你要导入的csv文件！";
                return;
            }
            DataTable dataTable = this.getDataTable(FilePath, fileName);
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                this.lblMessage.Text = "没有任何要导入的数据！";
                return;
            }
            this.CheckData(dataTable);

}
string[] Headers = { "类别", "目录", "品牌", "型号" };

        // 检测数据
        private bool CheckData(DataTable dataTable)
        {
            this.lblMessage.Text = "";
            int count =0;
            //检测列表头
            foreach (string c in Headers)
            {
                if (!dataTable.Columns.Contains(c))
                {
                    this.lblMessage.Text += " '" + c + "'";
                    continue;
                }
            }
            if (this.lblMessage.Text != "")
            {
                this.lblMessage.Text = "导入的数据文件，找不到列：<br/>" + this.lblMessage.Text;
                return false;
            }
            //检测不能为空和数据类型是否匹配:(目录，品牌，型号，品质不能空)
            //补上默认值

            int rowCount = dataTable.Rows.Count;
            string[] columns = { "类别", "目录", "品牌", "型号" };
            string[] DbColumns = { "PathName", "PathName", "PathName", "PathName" };
            Dictionary<string, string> dictColumns = new Dictionary<string, string>();
            for (int i = 0; i < columns.Length; i++)
            {
                dictColumns.Add(columns[i], DbColumns[i]);
            }

            IDictDirectoryBll dictDirectoryBll = BllFactory.GetDictDirectoryBll(this);
            for (int index = 0; index < rowCount; index++)
            {
                DictDirectory dictDirectory = new DictDirectory();
                foreach (string c in columns)
                {
                    DataTable TempDataTable = new DataTable();
                    string where = Data.GetString(dataTable.Rows[index][c]);

                    switch (c)
                    {
                        case "目录"://1
                            string firstDict11 = Data.GetString(dataTable.Rows[index]["类别"]);
                            string secondDict11 = Data.GetString(dataTable.Rows[index]["目录"]);
                            dictDirectoryBll.BuildDictDirectory(firstDict11, firstDict11 + "|" + secondDict11, secondDict11);
                            break;
                        case "品牌"://2
                            string firstDict21 = Data.GetString(dataTable.Rows[index]["类别"]);
                            string firstDict22 = Data.GetString(dataTable.Rows[index]["目录"]);
                            string firstDict23 = Data.GetString(dataTable.Rows[index]["品牌"]);
                            dictDirectoryBll.BuildDictDirectory(firstDict21 + "|" + firstDict22, firstDict21 + "|" + firstDict22 + "|" + firstDict23, firstDict23);

                            break;
                        case "型号"://3
                            string firstDict31 = Data.GetString(dataTable.Rows[index]["类别"]);
                            string firstDict32 = Data.GetString(dataTable.Rows[index]["目录"]);
                            string firstDict33 = Data.GetString(dataTable.Rows[index]["品牌"]);
                            string firstDict34 = Data.GetString(dataTable.Rows[index]["型号"]);
                            dictDirectoryBll.BuildDictDirectory(firstDict31 + "|" + firstDict32 + "|" + firstDict33, firstDict31 + "|" + firstDict32 + "|" + firstDict33 + "|" + firstDict34, firstDict34);

break;
}

                }
                count++;

            }

            this.lblMessage.Text += "" + count + "导入成功";
            return true;
        }

        private DataTable getDataTable(string FilePath, string FileName)
        {
            try
            {

DataTable dt = new DataTable();

                string strConnCSV = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='TEXT'";//FMT=Delimited;HDR=Yes;IMEX=1;
                System.Data.OleDb.OleDbConnection objConn = new OleDbConnection(strConnCSV);
                objConn.Open();
                string strSql = "SELECT * FROM [" + FileName + "]";
                System.Data.OleDb.OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, strConnCSV);

odbcCSVDataAdapter.Fill(dt);
return dt;

            }
            catch
            {
                return null;

}
}

        #region   删除文件
        /// <summary>
        /// 删除文件
        /// </summary>
        /// <param name="filename">待删除的文件名</param>

private void DeleteFile(string filename)
{

if (filename != string.Empty && File.Exists(filename))
{

File.Delete(filename);

}

}
#endregion

        //检测导入的csv文件和csv工作表名称
        public Boolean CheakData(string strFiles)
        {

            if (strFiles == "")
            {
                this.lblMessage.Text = "对不起，请先选择你要导入的csv文件！";
                return false;
            }
            if (this.txtFileName.Text == "" || this.txtFileName.Text == null)
            {
                this.lblMessage.Text = "csv文件工作表名称不能为空！";
                return false;
            }

return true;
}

#region 读取csv文件通过TextFileParser读取

public override void ShowInfogMsg(string InfoMsg, bool IsSuccess, string Title)
{

        }
        public override void ShowErrorMsg(string ErrorMsg, bool IsSuccess, string Title)
        {

        }
        public override void ShowWarningMsg(string WarningMsg, bool IsSuccess, string Title)
        {

}

#endregion
}
}

//前台代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportDictroy.aspx.cs" Inherits="Mis.ErpView.Forms.Product.ImportDictroy" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>附件产品导入</title>
    <style type="text/css">
        .td_text
        {
            font-size: 12px;
        }
        .input_css
        {
            border: 1px solid #b5b8c8;
            width: 256px;
        }
        .style1
        {
            height: 19px;
        }
    </style>

function FileExcel_onclick() {

}

// ]]>
</script>

</head>
<body>
    <form id="form1" runat="server">
    <table style="width: 100%; background-color: #dfe8f7" align="center" class="td_text">
        <tr>
            <td align="left" style="text-align: right">
                请选择要导入的文件：
            </td>
            <td>
                <input class="input_css" id="FileExcel1" style="width: 329px;" type="file" size="42"
                    name="FilePhoto" runat="server" onclick="return FileExcel_onclick()" />
                

            </td>
        </tr>
        <tr>
            <td style="text-align: right">
                请输入csv文件工作表名称：
            </td>
            <td>
                <asp:TextBox ID="txtFileName" runat="server" Width="250px"></asp:TextBox>

            </td>
        </tr>
        <tr>
            <td style="text-align: right" class="style1">
                 </td>
            <td class="style1">
                 
            </td>
        </tr>
        <tr>
            <td>
                 
            </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td colspan="2" style="text-align: center">

                <asp:Button style=" width: 74px; height: 24px;"
                    id="btnImport" text="导入" runat="server" onclick="btnImport_Click"/>
            </td>
            <tr>
                <td colspan="2">
                    <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
                </td>
            </tr>
        </tr>
    </table>
    <br />
    <br />
    <br />
    </form>
</body>
</html>