c#导入导出Excel

最新推荐文章于 2021-06-10 09:26:49 发布

GX_WGY

最新推荐文章于 2021-06-10 09:26:49 发布

阅读量290

点赞数

分类专栏： c# 文章标签：技术 c# excel

本文链接：https://blog.csdn.net/GX_WGY/article/details/78675690

版权

c# 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前端js代码:

<script src="http://cdn.static.runoob.com/libs/jquery/2.1.1/jquery.min.js" type="text/javascript"></script>
    <script src="../Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
    <script src="../Scripts/jquery.dataTables.min.js" type="text/javascript"></script>
    <link href="../Styles/jquery.dataTables.min.css" rel="stylesheet" type="text/css" />
    <script language="javascript" type="text/javascript">
        $(document).ready(function () {
            var str1 = 导入导出项目代码.Account.List.SearchAllProduct().value.toString();
            var obj = JSON.parse(str1);
            var dataLength = $(obj).length;
            for (var i = 0; i < dataLength; i++) {
                $("#table1").append("<tr id='tr1'><td style='width:100px' align='center'><input type='checkbox' id='chk2' name='chk2' /></td>" +
                "<td align='center' width='3%'>" + obj[i].商品ID + "</td>" + "<td align='center' width='300px'>" + obj[i].商品名称 + "</td>" + "<td align='center' width='300px'>" + obj[i].商品编号 + "</td>" +
                "<td align='center' width='3%'>" + obj[i].品牌名称 + "</td>" + "<td align='center' width='300px'>" + obj[i].型号 + "</td>" + "<td align='center' width='300px'>" + obj[i].规格 + "</td>" +
                "<td align='center' width='3%'>" + obj[i].通用编码 + "</td>" + "<td align='center' width='300px'>" + obj[i].上限 + "</td>" + "<td align='center' width='300px'>" + obj[i].下限 + "</td>" +
                "<td align='center' width='3%'>" + obj[i].重量 + "</td>" + "<td align='center' width='300px'>" + obj[i].单位 + "</td>" + "<td align='center' width='300px'>" + obj[i].市场价 + "</td>" +
                "<td align='center' width='3%'>" + obj[i].会员价 + "</td>" + "<td align='center' width='300px'>" + obj[i].库存 + "</td>" + "<td align='center' width='300px'>" + obj[i].供应商 + "</td>" +
                "</tr>");
            }
        });

        function SearchByContent() {
            $("#table1 #tr1").remove();
            var selectedValue = document.getElementById("ddlDatas");
            var selectedObject = selectedValue.options[selectedValue.options.selectedIndex].value;
            if ($("#txtSearchContent").val() != "") {
                var inputContent = $("#txtSearchContent").val();
                var str2 = 导入导出项目代码.Account.List.SearchByInput(selectedObject, inputContent).value.toString();
                var obj2 = JSON.parse(str2);
                var dataLength2 = $(obj2).length;
                for (var j = 0; j < dataLength2; j++) {
                    $("#table1").append("<tr><td style='width:100px' align='center'><input type='checkbox' id='chk2' name='chk2' /></td>" +
                    "<td align='center' width='3%'>" + obj2[j].商品ID + "</td>" + "<td align='center' width='300px'>" + obj2[j].商品名称 + "</td>" + "<td align='center' width='300px'>" + obj2[j].商品编号 + "</td>" +
                    "<td align='center' width='3%'>" + obj2[j].品牌名称 + "</td>" + "<td align='center' width='300px'>" + obj2[j].型号 + "</td>" + "<td align='center' width='300px'>" + obj2[j].规格 + "</td>" +
                    "<td align='center' width='3%'>" + obj2[j].通用编码 + "</td>" + "<td align='center' width='300px'>" + obj2[j].上限 + "</td>" + "<td align='center' width='300px'>" + obj2[j].下限 + "</td>" +
                    "<td align='center' width='3%'>" + obj2[j].重量 + "</td>" + "<td align='center' width='300px'>" + obj2[j].单位 + "</td>" + "<td align='center' width='300px'>" + obj2[j].市场价 + "</td>" +
                    "<td align='center' width='3%'>" + obj2[j].会员价 + "</td>" + "<td align='center' width='300px'>" + obj2[j].库存 + "</td>" + "<td align='center' width='300px'>" + obj2[j].供应商 + "</td>" +
                    "</tr>");
                }
            }
            else {
                alert("搜索内容不能为空！");
            }

        }

        function checkeAll() {
            var checkedId = document.getElementById("chk1");
            var checkedValue = document.getElementsByName("chk2");
            var checkedLength = document.getElementsByName("chk2").length;
            if (checkedId.checked) {
                for (var k = 0; k < checkedLength; k++) {
                    checkedValue[k].checked = true;
                }
            }
            else {
                for (var k = 0; k < checkedLength; k++) {
                    checkedValue[k].checked = false;
                }
            }
        }
        var str3 = [];
        function ExportChecked1() {
            var checkedValue = document.getElementsByName("chk2");
            var checkedLength = document.getElementsByName("chk2").length;
            for (var p = 0; p < checkedLength; p++) {
                if (checkedValue[p].checked) {
                    var ShangPinBianHao = $("#table1").find("tr")[p + 1].children[3].innerText;
                    str3.push(ShangPinBianHao);

                }
            }
            if (str3.length > 0) {
                导入导出项目代码.Account.List.ExportCheckedToExcel(str3.toString()).value.toString();
            } else {
                alert("请选择至少一条数据进行导出！");
            }

        }

前端html代码：

<body>
    <form id="form1" runat="server">
    <div runat="server" id="div1">
        <asp:HiddenField runat="server" ID="hiddenText" />
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <asp:Button ID="ImportData" runat="server" Text="导入" OnClick="ImportData_Click" />
        <asp:Button ID="ExportData" runat="server" Text="导出全部" OnClick="ExportData_Click" />
        <a οnclick="javascript:window.print()">
            <asp:Button ID="PrintPage" runat="server" Text="打印" /></a>
        <input type="button" value="导出选择数据" id="ExportChecked" οnclick="ExportChecked1()" />
        <input id="checkedValues" type="hidden" runat="server" />
        <asp:DropDownList ID="ddlDatas" runat="server">
            <asp:ListItem Text="商品名称"></asp:ListItem>
            <asp:ListItem Text="商品编号"></asp:ListItem>
            <asp:ListItem Text="品牌名称"></asp:ListItem>
        </asp:DropDownList>
        <input type="text" id="txtSearchContent" />
        <input type="button" value="搜索" id="Button1" οnclick="SearchByContent()" />
        <table id="table1" class="table table-border table-bordered table-bg table-hover">
            <tr>
                <td width="1%" align="center" bgcolor="#E8F0F7">
                    <input type="checkbox" id="chk1" name="chk1" οnclick="checkeAll()" value="1" />
                </td>
                <td width="3%" align="center" bgcolor="#E8F0F7">
                    商品ID
                </td>
                <td width="7%" align="center" bgcolor="#E8F0F7">
                    商品名称
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    商品编号
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    品牌名称
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    型号
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    规格
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    通用编码
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    上限
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    下限
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    重量
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    单位
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    市场价
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    会员价
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    库存
                </td>
                <td width="5%" align="center" bgcolor="#E8F0F7">
                    供应商
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>

后台代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using OrderList;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing.Printing;
using System.IO;
using System.Text;
using Newtonsoft.Json;

namespace 导入导出项目代码.Account
{
    public partial class List : System.Web.UI.Page
    {
        string path = "";
        string SavePath = "";
        SqlConnection conn1;
        string connString1 = "Data Source=192.168.0.203;Initial Catalog=test1;User ID=sa;Password=123";
        DataTable dtGoods1;
        protected void Page_Load(object sender, EventArgs e)
        {
            AjaxPro.Utility.RegisterTypeForAjax(typeof(List));
            string sql = "SELECT * FROM ProductsTest";
            DataTable dtGoods = SqlHealper1.ExecuteDataTable(sql);
            dtGoods1 = dtGoods;
        }

        string GoodsCode1 = "";
        public void ImportData_Click(object sender, EventArgs e)
        {
            DataTable dt = getxlsData(path);
            conn1 = new SqlConnection(connString1);
            conn1.Open();
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    DataRow dr = null;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        GoodsCode1 = dr["商品编号"].ToString();
                        string sql2 = "SELECT * FROM ProductsTest WHERE ProductsTest.商品编号='" + GoodsCode1 + "'";
                        DataTable dtGoods = SqlHealper1.ExecuteDataTable(sql2);
                        if (dtGoods.Rows.Count > 0)
                        {
                            updateToSql(dr);
                        }
                        else
                        {
                            insertToSql(dr);
                        }
                    }
                }
                Response.Write("<script>alert('导入成功！')</script>");
            }
            conn1.Close();
        }

        private DataTable getxlsData(string path)
        {
            if (!FileUpload1.HasFile)
            {
                Response.Write("<script>alert('请先选择上传文件')</script>");
                return null;
            }
            SavePath = Server.MapPath("~\\upload\\");//文件保存到文件夹下
            this.FileUpload1.PostedFile.SaveAs(SavePath + "\\" + FileUpload1.FileName);//保存路径
            string connString = "";
            string fileExrensio = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();//ToLower()方法转化成小写
            using (DataSet ds = new DataSet())
            {
                if (fileExrensio == ".xls" || fileExrensio == ".xlsx")
                {
                    connString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + SavePath + FileUpload1.FileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
                }
                //读取文件
                OleDbConnection conn = new OleDbConnection(connString);
                conn.Open();

                string strExcel = "SELECT * FROM [Sheet1$]";
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, connString);
                DataTable dt = new DataTable();
                myCommand.Fill(dt);
                return dt;
            }
        }


        private void updateToSql(DataRow dr)
        {
            //excel表中的列名和数据库中的列名一定要对应  
            string GoodsId = dr["商品ID"].ToString();
            string GoodsName = dr["商品名称"].ToString();
            string GoodsCode = dr["商品编号"].ToString();
            string BrandName = dr["品牌名称"].ToString();
            string Model = dr["型号"].ToString();
            string Standard = dr["规格"].ToString();
            string UniversalCode = dr["通用编码"].ToString();
            string UpperLimit = dr["上限"].ToString();
            string LowerLimit = dr["下限"].ToString();
            string Weight = dr["重量"].ToString();
            string Unit = dr["单位"].ToString();
            string MarketPrice = dr["市场价"].ToString();
            string MemberPrice = dr["会员价"].ToString();
            string StorageNumber = dr["库存"].ToString();
            string Supplier = dr["供应商"].ToString();
            string sql = "update ProductsTest set 商品ID='" + GoodsId + "',商品名称='" + GoodsName + "',商品编号='" + GoodsCode + "',品牌名称='" + BrandName + "',型号='" + Model + "',规格='" + Standard + "',通用编码='" + UniversalCode + "',上限='" +
                                                UpperLimit + "',下限='" + LowerLimit + "',重量='" + Weight + "',单位='" + Unit + "',市场价='" + MarketPrice + "',会员价='" + MemberPrice + "',库存='" + StorageNumber + "',供应商='" + Supplier + "' where " +
                                                "商品编号='" + GoodsCode1 + "'";
            SqlCommand cmd = new SqlCommand(sql, conn1);
            cmd.ExecuteNonQuery();
        }


        private void insertToSql(DataRow dr)
        {
            //excel表中的列名和数据库中的列名一定要对应  
            string GoodsId = dr["商品ID"].ToString();
            string GoodsName = dr["商品名称"].ToString();
            string GoodsCode = dr["商品编号"].ToString();
            string BrandName = dr["品牌名称"].ToString();
            string Model = dr["型号"].ToString();
            string Standard = dr["规格"].ToString();
            string UniversalCode = dr["通用编码"].ToString();
            string UpperLimit = dr["上限"].ToString();
            string LowerLimit = dr["下限"].ToString();
            string Weight = dr["重量"].ToString();
            string Unit = dr["单位"].ToString();
            string MarketPrice = dr["市场价"].ToString();
            string MemberPrice = dr["会员价"].ToString();
            string StorageNumber = dr["库存"].ToString();
            string Supplier = dr["供应商"].ToString();
            string sql = "insert into ProductsTest values('" + GoodsId + "','" + GoodsName + "','" + GoodsCode + "','" + BrandName + "','" + Model + "','" + Standard + "','" + UniversalCode + "','" + UpperLimit + "','" + LowerLimit +
            "','" + Weight + "','" + Unit + "','" + MarketPrice + "','" + MemberPrice + "','" + StorageNumber + "','" + Supplier + "')";
            SqlCommand cmd = new SqlCommand(sql, conn1);
            cmd.ExecuteNonQuery();
        }

        public void PrintPage_Click(object sender, EventArgs e)
        {
            PrintDocument printDoc = new PrintDocument();
            printDoc.Print();
        }

        protected void ExportData_Click(object sender, EventArgs e)
        {

            if (dtGoods1.Rows.Count == 0)
            {
                Response.Write("<script>alert('没有数据可以导出！')</script>");

            }
            else if (dtGoods1.Rows.Count > 0)
            {
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    return;
                }
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                Microsoft.Office.Interop.Excel.Range range;
                long totalCount = dtGoods1.Rows.Count;
                long rowRead = 0;
                float percent = 0;
                if (dtGoods1.Rows.Count > 0)
                {
                    for (int i = 0; i < dtGoods1.Columns.Count; i++)
                    {
                        worksheet.Cells[1, i + 1] = dtGoods1.Columns[i].ColumnName;
                        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                        range.Interior.ColorIndex = 15;
                    }
                    for (int r = 0; r < dtGoods1.Rows.Count; r++)
                    {
                        for (int i = 0; i < dtGoods1.Columns.Count; i++)
                        {
                            try
                            {
                                worksheet.Cells[r + 2, i + 1] = dtGoods1.Rows[r][i].ToString();
                            }
                            catch
                            {
                                worksheet.Cells[r + 2, i + 1] = dtGoods1.Rows[r][i].ToString().Replace("=", "");
                            }
                        }
                        rowRead++;
                        percent = ((float)(100 * rowRead)) / totalCount;
                    }
                    string filepath = "D:\\项目\\项目7\\导入导出项目代码\\导入导出项目代码\\export\\商品资料.xls";
                    workbook.Saved = true;
                    workbook.SaveCopyAs(filepath);
                    xlApp.Visible = true;
                }
            }
        }

        [AjaxPro.AjaxMethod]
        public string SearchAllProduct()
        {
            string sql = "SELECT * FROM ProductsTest";
            DataTable dtGoods = SqlHealper1.ExecuteDataTable(sql);
            string JsonString = string.Empty;
            JsonString = JsonConvert.SerializeObject(dtGoods);
            return JsonString;
        }

        [AjaxPro.AjaxMethod]
        public string getAllProduct()
        {
            string sql = "SELECT * FROM ProductsTest";
            DataTable dtGoods1 = SqlHealper1.ExecuteDataTable(sql);
            StringBuilder sbs = new StringBuilder();
            if (dtGoods1.Rows.Count > 0)//如果有记录  
            {
                sbs.Append("{'" + dtGoods1.TableName + "':[");
                string str = "";
                foreach (DataRow dr in dtGoods1.Rows)//开始拼  
                {
                    string result = "";
                    foreach (DataColumn dc in dtGoods1.Columns)
                    {
                        result += string.Format(",'{0}':'{1}'",
                            dc.ColumnName, dr[dc.ColumnName]);
                    }
                    result = result.Substring(1);
                    result = ",{" + result + "}";
                    str += result;
                }
                str = str.Substring(1);
                sbs.Append(str);
                sbs.Append("]}");
            }
            else//如果没有记录  
            {
                sbs.Append("");
            }
            return sbs.ToString();
        }

        [AjaxPro.AjaxMethod]
        public string SearchByInput(string SouSuoTiaoJian, string InputText)
        {
            string sql = "";
            if (SouSuoTiaoJian == "商品名称")
            {
                sql = "SELECT * FROM ProductsTest WHERE ProductsTest.商品名称 LIKE '%" + InputText.Trim() + "%'";
            }
            else if (SouSuoTiaoJian == "商品编号")
            {
                sql = "SELECT * FROM ProductsTest WHERE ProductsTest.商品编号 LIKE '%" + InputText.Trim() + "%'";
            }
            else if (SouSuoTiaoJian == "品牌名称")
            {
                sql = "SELECT * FROM ProductsTest WHERE ProductsTest.品牌名称 LIKE '%" + InputText.Trim() + "%'";
            }

            DataTable dtGoods = SqlHealper1.ExecuteDataTable(sql);
            string JsonString = string.Empty;
            JsonString = JsonConvert.SerializeObject(dtGoods);
            return JsonString;
        }

        [AjaxPro.AjaxMethod]
        public void ExportCheckedToExcel(string strGoodsCode)
        {
            string[] strArr = strGoodsCode.Split(',');
            int strArrLength = strArr.Length;
            DataTable dt = new DataTable();
            dt.Columns.Add("商品ID", typeof(string));
            dt.Columns.Add("商品名称", typeof(string));
            dt.Columns.Add("商品编号", typeof(string));
            dt.Columns.Add("品牌名称", typeof(string));
            dt.Columns.Add("型号", typeof(string));
            dt.Columns.Add("规格", typeof(string));
            dt.Columns.Add("通用编码", typeof(string));
            dt.Columns.Add("上限", typeof(string));
            dt.Columns.Add("下限", typeof(string));
            dt.Columns.Add("重量", typeof(string));
            dt.Columns.Add("单位", typeof(string));
            dt.Columns.Add("市场价", typeof(string));
            dt.Columns.Add("会员价", typeof(string));
            dt.Columns.Add("库存", typeof(string));
            dt.Columns.Add("供应商", typeof(string));
            for (int j = 0; j < strArrLength; j++)
            {
                DataRow dr = dt.NewRow();
                dt.Rows.Add(dr);
                string GoodNumber = strArr[j];
                string sql = "SELECT * FROM ProductsTest WHERE ProductsTest.商品编号='" + GoodNumber + "'";
                DataTable dtGoods = SqlHealper1.ExecuteDataTable(sql);
                dt.Rows[j]["商品ID"] = dtGoods.Rows[0]["商品ID"];
                dt.Rows[j]["商品名称"] = dtGoods.Rows[0]["商品名称"];
                dt.Rows[j]["商品编号"] = dtGoods.Rows[0]["商品编号"];
                dt.Rows[j]["品牌名称"] = dtGoods.Rows[0]["品牌名称"];
                dt.Rows[j]["型号"] = dtGoods.Rows[0]["型号"];
                dt.Rows[j]["规格"] = dtGoods.Rows[0]["规格"];
                dt.Rows[j]["通用编码"] = dtGoods.Rows[0]["通用编码"];
                dt.Rows[j]["上限"] = dtGoods.Rows[0]["上限"];
                dt.Rows[j]["下限"] = dtGoods.Rows[0]["下限"];
                dt.Rows[j]["重量"] = dtGoods.Rows[0]["重量"];
                dt.Rows[j]["单位"] = dtGoods.Rows[0]["单位"];
                dt.Rows[j]["市场价"] = dtGoods.Rows[0]["市场价"];
                dt.Rows[j]["会员价"] = dtGoods.Rows[0]["会员价"];
                dt.Rows[j]["库存"] = dtGoods.Rows[0]["库存"];
                dt.Rows[j]["供应商"] = dtGoods.Rows[0]["供应商"];

            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                return;
            }
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    range.Interior.ColorIndex = 15;
                }
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        try
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                        }
                        catch
                        {
                            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString().Replace("=", "");
                        }
                    }
                    rowRead++;
                    percent = ((float)(100 * rowRead)) / totalCount;
                }
                string filepath = "D:\\项目\\项目7\\导入导出项目代码\\导入导出项目代码\\export\\商品资料.xls";
                workbook.Saved = true;
                workbook.SaveCopyAs(filepath);
                xlApp.Visible = true;
            }
        }
    }
}

GX_WGY

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
c#导入导出Excel

前端js代码: $(document).ready(function () { var str1 = 导入导出项目代码.Account.List.SearchAllProduct().value.toString(); var obj = JSON.parse(str1);
复制链接

扫一扫