前端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;
}
}
}
}