--引用dll 免费下载地址:
http://download.csdn.net/detail/qq285679784/8569327
--命名空间
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;
/// <summary>
/// 导出正式Excel文件,用于导入盘点数据/// </summary>
/// <param name="ds2"></param>
private void ToExcel(DataSet ds2)
{
if (ds2.Tables[0].Rows.Count > 0)
{
DataTable dt = ds2.Tables[0];
string filename = "KCPD_" + Session["UserName"].ToString() + "_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
//临时存放路径
string filePath = Server.MapPath("~/upload/" + filename);
//Excel模版
string masterPath = Server.MapPath("~/upload/Master/PDCK.xls");
//复制Excel模版
File.Copy(masterPath, filePath);
#region 将文件的只读勾取消 以免发生不必要的错误
// 先把文件的属性读取出来
FileAttributes attrs = File.GetAttributes(filePath);
// 下面表达式中的 1 是 FileAttributes.ReadOnly 的值
// 此表达式是把 ReadOnly 所在的位改成 0,
attrs = (FileAttributes)((int)attrs & ~(1));
File.SetAttributes(filePath, attrs);
#endregion
// 使用OleDb驱动程序连接到副本
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;'");
using (conn)
{
conn.Open();
// 增加记录
for (int i = 0; i < dt.Rows.Count; i++)
{
OleDbCommand cmd = new OleDbCommand(@"INSERT INTO [Sheet1$]
(
[配件ID],[识别码],[配件名称],[配件规格],[配件代码],[版本],
[上月库存],[采购入库],[良品入库],[维修返回],[维修入库],
[调整库存],[调拨入库],[销售出库],[免费出库],
[调拨出库],[维修出库],[其它出库],[现有库存],[盘点库存]
)
values('" + dt.Rows[i]["配件ID"] + "', '" + dt.Rows[i]["识别码"] + "','" + dt.Rows[i]["配件名称"] + "', '" + dt.Rows[i]["配件规格"] + "','" + dt.Rows[i]["配件代码"] + "', '" + dt.Rows[i]["版本"] + "', '" + dt.Rows[i]["上月库存"] + "','" + dt.Rows[i]["采购入库"] + "', '" + dt.Rows[i]["良品入库"] + "', '" + dt.Rows[i]["维修返回"] + "','" + dt.Rows[i]["维修入库"] + "', '" + dt.Rows[i]["调整库存"] + "', '" + dt.Rows[i]["调拨入库"] + "', '" + dt.Rows[i]["销售出库"] + "', '" + dt.Rows[i]["免费出库"] + "', '" + dt.Rows[i]["调拨出库"] + "', '" + dt.Rows[i]["维修出库"] + "', '" + dt.Rows[i]["其它出库"] + "', '" + dt.Rows[i]["现有库存"] + "', '" + dt.Rows[i]["盘点库存"] + "')", conn);
cmd.ExecuteNonQuery();
}
conn.Close();
}
// 输出副本的二进制字节流
HttpContext.Current.Response.Charset = "UTF-8"; // 或UTF-7 以防乱码
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/ms-excel";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.GetEncoding(65001).GetBytes(filename)));
Response.BinaryWrite(File.ReadAllBytes(filePath));
//删除副本
File.Delete(filePath);
}
}
#endregion
#region 导入Excel
/// <summary>
/// 立即导入Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnUp_Click(object sender, System.EventArgs e)
{
if (this.drop_DepotId_In.SelectedItem.Value == "-1")
{ US.Components.MessageBox.Show(this.Page, "仓库不能为空!"); return; }
if (this.fileExcel.PostedFile.FileName.Length == 0)
{ excel_msg.Text = "导入返回信息:没有选择文件!"; this.excel_msg.ForeColor = System.Drawing.Color.Red; return; }
string ext = this.fileExcel.PostedFile.FileName.Substring(fileExcel.PostedFile.FileName.LastIndexOf('.') + 1).ToLower();
if (ext != "xls")
{ excel_msg.Text = "导入返回信息:只能导入xls格式的Excel文件!"; this.excel_msg.ForeColor = System.Drawing.Color.Red; return; }
//记录导入耗时
DateTime time = DateTime.Now;
US.Components.UploadFileHelper upfile = new US.Components.UploadFileHelper();
upfile.UserId = Session["UserID"].ToString();
upfile.Upload_Type = 0;
upfile.Save(this.fileExcel.PostedFile);
//临时表名
string Nname = ("KCPD_" + Guid.NewGuid().ToString()).Replace("-", "_");
//创建唯一的临时表
string CreateSql = string.Format(@"
CREATE TABLE [dbo].[{0}]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[f_id] [varchar](200),
[sn] [varchar](200),
[FittingName] [varchar](200),
[SpecName] [varchar](200),
[FittingId] [varchar](100),
[FittingVersion] [varchar](50),
[LastAmount] [varchar](200),
[采购入库] [varchar](200),
[良品入库] [varchar](200),
[维修返回] [varchar](200),
[维修入库] [varchar](200),
[调整库存] [varchar](200),
[调拨入库] [varchar](200),
[销售发货] [varchar](200),
[免费发货] [varchar](200),
[办事处发货] [varchar](200),
[维修] [varchar](200),
[其它] [varchar](200),
[现有库存] [varchar](200),
[盘点库存] [varchar](200),
[CollectTime] [datetime] ,
[DepotId] [int],
[UserId] [int]
)", Nname);
run.ExecSQL(CreateSql);
try
{
string connectionString = ConfigurationSettings.AppSettings["ConnectionString"].Replace("True", "False");
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + upfile.FilePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
string sqlstr = "select * from [Sheet1$]";
#region OleDb导入
using (OleDbConnection sourceConnection = new OleDbConnection(constr))
{
sourceConnection.Open();
OleDbCommand ocomm = new OleDbCommand(sqlstr, sourceConnection);
OleDbDataReader reader = ocomm.ExecuteReader();
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))
{
bulkCopy.BatchSize = 1000;//每次传输的行数
bulkCopy.BulkCopyTimeout = 3600;
//f_id,sn,FittingName,SpecName,FittingId,FittingVersion,LastAmount,采购入库,良品入库,维修返回,
//维修入库,调整库存,调拨入库,销售发货,免费发货,办事处发货,维修,其它,现有库存,盘点库存,
//CollectTime,DepotId,UserId
//第一个参数为Excel中的字段
//第二个参数为数据库表中的字段
bulkCopy.ColumnMappings.Add("配件ID", "f_id");
bulkCopy.ColumnMappings.Add("识别码", "sn");
bulkCopy.ColumnMappings.Add("配件名称", "FittingName");
bulkCopy.ColumnMappings.Add("配件规格", "SpecName");
bulkCopy.ColumnMappings.Add("配件代码", "FittingId");
bulkCopy.ColumnMappings.Add("版本", "FittingVersion");
bulkCopy.ColumnMappings.Add("上月库存", "LastAmount");
bulkCopy.ColumnMappings.Add("采购入库", "采购入库");
bulkCopy.ColumnMappings.Add("良品入库", "良品入库");
bulkCopy.ColumnMappings.Add("维修返回", "维修返回");
bulkCopy.ColumnMappings.Add("维修入库", "维修入库");
bulkCopy.ColumnMappings.Add("调整库存", "调整库存");
bulkCopy.ColumnMappings.Add("调拨入库", "调拨入库");
bulkCopy.ColumnMappings.Add("销售出库", "销售发货");
bulkCopy.ColumnMappings.Add("免费出库", "免费发货");
bulkCopy.ColumnMappings.Add("调拨出库", "办事处发货");
bulkCopy.ColumnMappings.Add("维修出库", "维修");
bulkCopy.ColumnMappings.Add("其它出库", "其它");
bulkCopy.ColumnMappings.Add("现有库存", "现有库存");
bulkCopy.ColumnMappings.Add("盘点库存", "盘点库存");
//tbl_cell_gh_temp为目标表
bulkCopy.DestinationTableName = Nname;
//将数据复制到数据库
bulkCopy.WriteToServer(reader);
//关闭reader
reader.Close();
/*对导入的数据,进行全面扫描。
如果有异常则导入失败,将异常信息以Excel的方式返回客户端,提供下载
(返回的InsertRow=0,UpdateRow=0则为异常)*/
//获取影响行数
DataSet ds = run.GetDataSet(
string.Format(@"
exec SP_Check_Import_T_Fittings_Collect_tmp '{0}',{1},{2},'{3}',{4}", Nname, Session["UserID"].ToString(), drop_DepotId_In.SelectedValue, this.txt_inTime.Text + "-01", this.txtIsImport.Value)
);//@Nname、@UserID、@DepotID、@CollectTime、@IsImport
//Insert总数
string InsertRow = ds.Tables[0].Rows[0]["InsertRow"].ToString();
string UpdateRow = ds.Tables[0].Rows[0]["UpdateRow"].ToString();
//无异常,导入成功提示
if (InsertRow != "0" || UpdateRow != "0")
{
//隐藏下载Excel按钮
Linkbtn.Visible = false;
//Tips
excel_msg.Text = "<br/><br/>导入成功,总耗时:" + (DateTime.Now - time).ToString() + "<br/><br/>共插入数据:" + InsertRow + "条,修改数据:" + UpdateRow + "条";
excel_msg.ForeColor = System.Drawing.Color.Blue;
}
else
{
//Tips
excel_msg.Text = "<br/><br/>导入失败,数据存在异常,查看异常详细信息,请下载:";
excel_msg.ForeColor = System.Drawing.Color.Blue;
//将异常数据缓存起来
Cache.Insert("Error", ds);
//显示下载Excel按钮
Linkbtn.Visible = true;
}
}
}
}
#endregion
}
catch (Exception ex)
{
excel_msg.Text = "导入返回信息:导入失败!错误信息:" + ex.Message;
this.excel_msg.ForeColor = System.Drawing.Color.Red;
}
finally
{
if (upfile.FilePath != null)
{
delfile(upfile.FilePath);//删除已上传文件
}
//删除临时表
//string sql = "drop table [dbo].[" + Nname + "]";
//run.ExecSQL(sql);
}
}
/// <summary>
/// 删除上传的临时文件
/// </summary>
/// <param name="filestr"></param>
private void delfile(string filestr)
{
FileInfo fi = new FileInfo(filestr);
if (fi.Exists)
{
fi.Delete();
}
}
/// <summary>
/// 下载异常信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Linkbtn_Click(object sender, EventArgs e)
{
if (Cache["Error"] != null)
{
DataSet ds = (DataSet)Cache["Error"];
//数据存在异常,将异常详细信息导入Excel放到输出流,提供用户下载浏览
CreateExcel(ds, "1", "系统扫描结果.xls");
}
else
{
excel_msg.Text = "无异常信息!";
this.excel_msg.ForeColor = System.Drawing.Color.Red;
//隐藏下载Excel按钮
Linkbtn.Visible = false;
}
}
/// <summary>
/// 将DataSet中的数据导出到Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="typeid"></param>
/// <param name="FileName"></param>
public void CreateExcel(DataSet ds, string typeid, string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ";");
//resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0;
//定义表对象和行对像,同时用DataSet对其值进行初始化
DataTable dt = ds.Tables[1];
DataRow[] myRow = dt.Select("");
// typeid=="1"时导出为EXCEL格式文档;typeid=="2"时导出为XML格式文档
if (typeid == "1")
{
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
}
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for (i = 0; i < dt.Columns.Count; i++)
{
if (i == dt.Columns.Count - 1)
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文档中
resp.End();
}
#endregion
}