两Excel表导入对比，并生成新Excel表

来源：互联网发布：麦迪季后赛数据编辑：程序博客网时间：2024/04/29 03:48

//这是一个将两张Excel表的产品名称进行对比并生成新Excel表的小程序

//发布之后可能会报出一个错误（具体搞忘了），除了网上的办法外，还可以试试将需要导入的文件

//与主程序放入一个盘符，这个错也有可能是IIS服务的访问权限引起，office版本为2007

--------------------------------万恶的分割线--------------------------------------------

//项目结构

//产品信息表

//订单表

--------------------------------万恶的分割线--------------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="index.aspx.cs" Inherits="_Default" %>

<!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 runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table>
            <tr>
            <td><asp:Label ID="Label2" runat="server" Text="选择产品Excel："></asp:Label>
                <asp:FileUpload ID="fu_chanpin" runat="server" /></td>
            </tr>
            <tr>
            <td><asp:Label ID="Label1" runat="server" Text="选择订单Excel："></asp:Label>
                <asp:FileUpload ID="fu_dingdan" runat="server" />
                <asp:Button ID="btnSubmit" runat="server" Text="确定" OnClick="btnSubmit_Click" /></td>
            </tr>
                <tr>
                    <td style="width: 100%;" colspan="2">
                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="1500px">
                            <Columns>
                                <asp:BoundField DataField="dingdanName" HeaderText="产品名称" />
                                <asp:BoundField DataField="dingdanJinghanliang" HeaderText="净含量" />
                                <asp:BoundField DataField="dingdanChangjiajia" HeaderText="厂家价" />
                                <asp:BoundField DataField="dingdanPifajia" HeaderText="批发价3折" />
                                <asp:BoundField DataField="dingdanShuliang" HeaderText="数量" />
                                <asp:BoundField DataField="dingdanZongjia" HeaderText="总价" />
                                <asp:BoundField DataField="dingdanBeizhu" HeaderText="备注" />
                                <asp:BoundField DataField="dingdanPeisong" HeaderText="配送" />
                            </Columns>
                        </asp:GridView>
                    </td>
                </tr>
            <tr>
            <td>
                <asp:Button ID="btnNewExcel" runat="server" Text="生成Excel" OnClick="btnNewExcel_Click"/></td>
            </tr>
            </table>
        </div>
    </form>
</body>
</html>

--------------------------------万恶的分割线--------------------------------------------

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using OrderToCheckModels;
using System.Data.OleDb;
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        //清理GridView
        DataSet dsNull = null;
        this.GridView1.DataSource = dsNull;
        this.GridView1.DataBind();

        if (fu_chanpin.PostedFile.ContentLength == 0 || fu_chanpin.PostedFile.FileName == "" || fu_chanpin.PostedFile == null)
        {
            Response.Write("<script   language='javascript'>alert('请选择要导入的产品Excel文件。');</script>");
            return;
        }
        if (fu_dingdan.PostedFile.ContentLength == 0 || fu_dingdan.PostedFile.FileName == "" || fu_dingdan.PostedFile == null)
        {
            Response.Write("<script   language='javascript'>alert('请选择要导入的订单Excel文件。');</script>");
            return;
        }
        string strFileName = "";
        strFileName = this.fu_chanpin.PostedFile.FileName;
        int intLastPoint = strFileName.LastIndexOf('.');

        string strFileNameDd = "";
        strFileNameDd = this.fu_dingdan.PostedFile.FileName;
        int intLastPointDd = strFileNameDd.LastIndexOf('.');

        if (!(intLastPoint > 0 && intLastPoint < (strFileName.Length - 3)))
        {
            Response.Write("<script language='javascrip'>alert('请选择产品Excel文件！');</script>");
            return;
        }
        if (!(intLastPointDd > 0 && intLastPointDd < (strFileNameDd.Length - 3)))
        {
            Response.Write("<script language='javascrip'>alert('请选择订单Excel文件！');</script>");
            return;
        }

string strExtension = strFileName.Substring(intLastPoint).ToLower();
string strExtensionDd = strFileNameDd.Substring(intLastPointDd).ToLower();

        if (strExtension != ".xls" || strExtensionDd != ".xls")
        {
            Response.Write("<script language='javascript'>alert('选择的文件不符合条件，你是否选择的是Excel表，请选择Excel文件！')</script>");
            return;
        }

        //指定服务器上的存储的文件名
        string strFileName2 = DateTime.Now.ToString("yyyymmddhhmmssff") + strExtension;
        string strDataFilePath = Server.MapPath("up");

        if (!System.IO.Directory.Exists(strDataFilePath))
        {
            System.IO.Directory.CreateDirectory(strDataFilePath);
        }

string strDataFilePath2 = strDataFilePath + "\\" + strFileName2;

fu_chanpin.PostedFile.SaveAs(strDataFilePath2);

        if (!System.IO.File.Exists(strDataFilePath2))
        {
            Response.Write("<script language='javascript'>alert('产品Excel文件的路径没有建立成功！')</script>");
            return;
        }
        else
        {
            ViewState["DataFilePath"] = strDataFilePath2;
        }

        //从保存路径读取Excel内容
        string conn = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + strDataFilePath2 + ";Extended Properties=Excel 8.0";//相当于数据库连接字符
        OleDbConnection objcon = new OleDbConnection(conn);
        objcon.Open();
        DataTable dt = objcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string tableName = dt.Rows[0][2].ToString().Trim();

        string Sql = "select * from [" + tableName + "]";
        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, objcon);
        DataSet ds = new DataSet();
        mycommand.Fill(ds);
        objcon.Close();

        //订单Excel不另保存，直接读取
        string connDd = " Provider = Microsoft.Jet.OLEDB.4.0;Data Source =" + strFileNameDd + ";Extended Properties=Excel 8.0";
        OleDbConnection objconDd = new OleDbConnection(connDd);
        objconDd.Open();
        DataTable dtDd = objconDd.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        string tableNameDd = dtDd.Rows[0][2].ToString().Trim();

        string SqlDd = "select * from [" + tableNameDd + "]";
        OleDbDataAdapter mycommandDd = new OleDbDataAdapter(SqlDd, objconDd);
        DataSet dsDd = new DataSet();
        mycommandDd.Fill(dsDd);
        objconDd.Close();

//判断是否存在身份证号相同的列

        List<Dingdan> listGaloi = new List<Dingdan>();
        List<Chanpin> listChanpin = new List<Chanpin>();

int count = ds.Tables[0].Rows.Count;
int columns = ds.Tables[0].Columns.Count;

        int countDd = dsDd.Tables[0].Rows.Count;
        int columnsDd = dsDd.Tables[0].Columns.Count;
        //进行对比，这里是进行产品名称的对比
        //相同的名称或相近的名称修改备注，不同则赋值为“-1”
        for (int i = 0; i < countDd; i++)
        {
            string kucun = "-1";
            for (int j = 0; j < count; j++)
            {
                if (ds.Tables[0].Rows[j][2].ToString().Trim().IndexOf(dsDd.Tables[0].Rows[i][0].ToString().Trim()) > 0 || dsDd.Tables[0].Rows[i][0].ToString().Trim() == ds.Tables[0].Rows[j][2].ToString().Trim())
                {
                    kucun = ds.Tables[0].Rows[j][5].ToString().Trim() + "(" + ds.Tables[0].Rows[j][2].ToString().Trim() + ")";
                }
            }
            //存入List对象，方便GridView显示
            Dingdan _dingdan = new Dingdan();

            _dingdan.dingdanName = dsDd.Tables[0].Rows[i][0].ToString().Trim();
            _dingdan.dingdanJinghanliang = dsDd.Tables[0].Rows[i][1].ToString().Trim();
            _dingdan.dingdanChangjiajia = dsDd.Tables[0].Rows[i][2].ToString().Trim();
            _dingdan.dingdanPifajia = dsDd.Tables[0].Rows[i][3].ToString().Trim();
            _dingdan.dingdanShuliang = dsDd.Tables[0].Rows[i][4].ToString().Trim();
            _dingdan.dingdanZongjia = dsDd.Tables[0].Rows[i][5].ToString().Trim();
            _dingdan.dingdanBeizhu = kucun;
            _dingdan.dingdanPeisong = dsDd.Tables[0].Rows[i][7].ToString().Trim();

            listGaloi.Add(_dingdan);
        }
        this.GridView1.DataSource = listGaloi;
        this.GridView1.DataBind();

    }
    //以下是从GridView提取数据，生成Excel
    protected void btnNewExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Random rand = new Random(100);
            string ExcelName = "生成" + DateTime.Now.ToString("yyyymmddhhmmssff") + rand.ToString();//"0" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + rand.ToString();

HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(ExcelName, System.Text.Encoding.UTF8) + ".xls");

HttpContext.Current.Response.Charset = "GB2312";

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //System.Text.Encoding.UTF8;

HttpContext.Current.Response.ContentType = "application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword

this.GridView1.Page.EnableViewState = false;

System.IO.StringWriter tw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

this.ClearControls(GridView1);

this.GridView1.RenderControl(hw);

HttpContext.Current.Response.Write(tw.ToString());

            HttpContext.Current.Response.End();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void ClearControls(System.Web.UI.Control control)
    {
        for (int i = control.Controls.Count - 1; i >= 0; i--)
        {
            ClearControls(control.Controls[i]);
        }

        if (!(control is System.Web.UI.WebControls.TableCell))
        {
            if (control.GetType().GetProperty("SelectedItem") != null)
            {
                System.Web.UI.LiteralControl literal = new System.Web.UI.LiteralControl();
                control.Parent.Controls.Add(literal);
                try
                {
                    literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control, null);
                }
                catch
                {

}

control.Parent.Controls.Remove(control);
}

else

                if (control.GetType().GetProperty("Text") != null)
                {
                    System.Web.UI.LiteralControl literal = new System.Web.UI.LiteralControl();
                    control.Parent.Controls.Add(literal);
                    literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control, null);
                    control.Parent.Controls.Remove(control);
                }
        }
        return;

}

    public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }

}

--------------------------------万恶的分割线--------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;

namespace OrderToCheckModels
{
    public class Chanpin
    {
        private string _chanpinBianhao = String.Empty;
        private string _chanpinTiaoma = String.Empty;
        private string _chanpinName = String.Empty;
        private string _chanpinGuige = String.Empty;
        private string _chanpinDanwei = String.Empty;
        private string _chanpinShuliang = String.Empty;

public Chanpin() { }

        public string chanpinBianhao
        {
            get { return this._chanpinBianhao; }
            set { this._chanpinBianhao = value; }
        }

。。。。。。省略

--------------------------------万恶的分割线--------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;

namespace OrderToCheckModels
{
    public class Dingdan
    {
        private string _dingdanName = String.Empty;
        private string _dingdanJinghanliang = String.Empty;
        private string _dingdanChangjiajia = String.Empty;
        private string _dingdanPifajia = String.Empty;
        private string _dingdanShuliang = String.Empty;
        private string _dingdanZongjia = String.Empty;
        private string _dingdanBeizhu = String.Empty;
        private string _dingdanPeisong = String.Empty;

public Dingdan() { }

        public string dingdanName
        {
            get { return this._dingdanName; }
            set { this._dingdanName = value; }
        }

。。。。。。继续省略