c#导入导出Excel
来源：互联网 发布：查身份证真假软件 编辑：程序博客网 时间：2024/05/16 12:24
前端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 onclick="javascript:window.print()"> <asp:Button ID="PrintPage" runat="server" Text="打印" /></a> <input type="button" value="导出选择数据" id="ExportChecked" onclick="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" onclick="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" onclick="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; } } }}阅读全文
0 0
- c#导入导出Excel
- c# 导入导出excel
- c# 导入导出excel
- C#导入、导出Excel
- c# 导入导出excel
- C#导入导出EXCEL
- C# excel导入导出
- c#导入导出Excel
- C#操作Excel导入导出
- C#操作Excel导入导出
- C#操作Excel导入导出
- C# 导出和导入excel
- C# 导出和导入excel
- C# 导入和导出EXCEL
- C#操作Excel（导入导出）
- C#操作Excel（导入导出）
- C#操作Excel（导入导出）
- C#操作Excel(导入导出)[转贴]
- 如何基于OSS和MTS，快速搭建音视频文件上传服务？
- Android的onLayout、layout方法讲解
- 关于嵌套类与外围类的详细解释
- mt6735[Call] USSD是否支持USSD Phase 1 & 2 2
- Spring boot + Maven环境搭建comet4j前端页面推送
- c#导入导出Excel
- vipkid创始人米雯娟传奇：高二退学，创业逆袭成50亿教育巨头
- 17.Python
- 乐变热更新后台的使用
- Docker1.12.1之swarm集群搭建与使用
- Android使用Http访问网络
- 7、HTML5元素
- Html之路
- idea bookmark
