using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HPSF;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using SiteCore;
using BizCom;

public partial class plug_imports : ReportBase
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Request.Files.Count > 0 && Request.Files[0].FileName != "")
            {
                HttpPostedFile file = Request.Files[0];
                UploadFile(file);

                iSheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = iSheet.GetRowEnumerator();
                StringBuilder sql = new StringBuilder();
                int i = 0;

                List<object> keys = new List<object>();
                while (rows.MoveNext())
                {
                    if (i == 0) { i++; continue; }
                    IRow row = (HSSFRow)rows.Current;
                    ICell cell = row.GetCell(0);
                    if (cell == null) break;
                    string code = row.GetCell(0).StringCellValue;
                    if (code == "" || code == "条码号") break;
                    string name = "";
                    if (row.GetCell(1) != null) name = row.GetCell(1).StringCellValue;
                    //insert into S_LyxyUser(UserNo, UserName, Faculties)values('XW00404', '王雅琦', '校外读者500')
                    name = name.Replace(" ", "").Replace("　", "");
                    sql = new StringBuilder();
                    sql.AppendFormat(" if (select count(0) from S_LyxyUser where UserName='{0}' and UserNo='{1}') < 1 ", name,code);
                    sql.Append(" begin ");
                    sql.AppendFormat(" insert into S_LyxyUser(UserNo,UserName) values('{0}','{1}') ;", code, name);
                    sql.Append(" end ");
                    try
                    {
                        SqlHelper.ExecuteNonQuery(sql.ToString());
                    }
                    catch (Exception ex)
                    {
                         XLog.SaveLog(0,code + "," + name, ex);
                        break;
                    }
                    i++;
                }
                ShowResult("导入成功!", "closeFn(1)");
            }
        }
    }

    public void UploadFile(HttpPostedFile file)
    {
        //FileStream f = new FileStream(
        Stream s = file.InputStream;
        hssfworkbook = new HSSFWorkbook(s);
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

        dsi.Company = "连城鑫港";
        hssfworkbook.DocumentSummaryInformation = dsi;

        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = excelName;
        hssfworkbook.SummaryInformation = si;
    }
}