302 lines
14 KiB
C#
302 lines
14 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Configuration;
|
|
using System.Collections;
|
|
using System.Web;
|
|
using System.Web.Security;
|
|
using System.Web.UI;
|
|
using System.Web.UI.WebControls;
|
|
using System.Web.UI.WebControls.WebParts;
|
|
using System.Web.UI.HtmlControls;
|
|
using GThinking.N_MISPRO;
|
|
using GThinking.N_MISPRO.Data;
|
|
using GThinking.N_MODEL.Page;
|
|
using GThinking.N_WORKFLOW;
|
|
using GThinking.N_MODEL.Dialog;
|
|
using GThinking.N_PUBLIC;
|
|
using GThinking.N_MISPRO.Utility;
|
|
using GThinking.N_CONTROL;
|
|
using System.IO;
|
|
using System.Data.OleDb;
|
|
using System.Data.Common;
|
|
using GThinking.N_MISPROAS;
|
|
using System.Collections.Generic;
|
|
using GThinking.N_PUBLIC2;
|
|
|
|
namespace GThinking.N_XLXSBJ
|
|
{
|
|
public partial class XLBJD_WBPICDRInfo : SingleCardBase
|
|
{
|
|
XLBJD_WBPICDR entity = new XLBJD_WBPICDR();
|
|
|
|
protected void Page_Load(object sender, EventArgs e)
|
|
{
|
|
this.UMID = 100860053;
|
|
this.UMName = "按外部报价单图片导入";
|
|
base.CurMaster = Master;
|
|
base.CurEntity = entity;
|
|
this.PageHeight = 180;
|
|
this.PageWidth = 420;
|
|
Master.EnableAttachBtn = false;
|
|
Master.EnableCancelBtn = false;
|
|
Master.EnableCopyBtn = false;
|
|
Master.EnableDeleteBtn = false;
|
|
Master.EnableEditBtn = false;
|
|
Master.EnableInsertBtn = false;
|
|
Master.EnablePrintBtn = false;
|
|
Master.EnableRefreshBtn = false;
|
|
Master.EnableSaveBtn = false;
|
|
Server.ScriptTimeout = 3600;
|
|
fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
|
|
fupdPost.Filter = "JPG,JPEG,PNG,BMP";
|
|
|
|
base.InitPage();
|
|
|
|
}
|
|
|
|
#region 控件事件
|
|
|
|
protected void btnQD_Click(object sender, EventArgs e)
|
|
{
|
|
if (fupdPost.FilePath.Equals(""))
|
|
{
|
|
ShowMessage("请选择需要导入的图片！");
|
|
return;
|
|
}
|
|
|
|
if (AppRuntime.BitType == AppBitType.Bit32)
|
|
{
|
|
ShowMessage("请联系系统管理员，将Web服务器[IIS管理器-应用程序池-启用32位应用程序]设为False后再试！");
|
|
return;
|
|
}
|
|
|
|
string img_file = fupdPost.FilePath;// @"D:\02.jpg";
|
|
FileStream fs = null;
|
|
try
|
|
{
|
|
fs = new FileStream(img_file, FileMode.Open);
|
|
BinaryReader br = new BinaryReader(fs);
|
|
byte[] contentBytes = br.ReadBytes(Convert.ToInt32(fs.Length));
|
|
String base64 = System.Convert.ToBase64String(contentBytes);
|
|
ALYOCRResult result = OCRUtils.GetExcelByImageFromGTServer(base64);
|
|
|
|
if (result.Success)
|
|
{
|
|
byte[] fileBytes = null;
|
|
string sBytes = result.SuccessVaule;
|
|
sBytes = sBytes.Replace("\n", "");
|
|
fileBytes = Convert.FromBase64String(sBytes);
|
|
|
|
string strPath = System.AppDomain.CurrentDomain.BaseDirectory + "\\work\\" + "temp";
|
|
if (!Directory.Exists(strPath))
|
|
Directory.CreateDirectory(strPath);
|
|
string tmpExcelPath = strPath + "\\" + fupdPost.FileName + "[" + Guid.NewGuid().ToString() + "]" + ".xlsx";
|
|
FileStream fileStream = new FileStream(tmpExcelPath, FileMode.Create, FileAccess.Write);
|
|
fileStream.Write(fileBytes, 0, fileBytes.Length);
|
|
fileStream.Flush();
|
|
fileStream.Close();
|
|
|
|
ArrayList sheetNameList = GetSheetNames(tmpExcelPath);
|
|
for (int i = 0; i < sheetNameList.Count; i++)
|
|
{
|
|
DataSet aDataExcel = new DataSet();
|
|
ReadWJ(tmpExcelPath, sheetNameList[i].ToString().Trim(), ref aDataExcel);//图片识别出来的表格是xlsx格式的，暂还无法使用NPOI的方式读取
|
|
string sWBBJDID = XLWBBJDUtils.GetNewWBBJDID();//传入图片名
|
|
ExecDRSQL(aDataExcel, PageParam.GetRTrimString("BJDID"), sWBBJDID, fupdPost.FileName, sheetNameList[i].ToString().Trim());
|
|
}
|
|
|
|
ShowMessage("导入成功！");
|
|
DialogOperationScript dos = new DialogOperationScript(this);
|
|
//dos.ClickOwnerButton(PageParam.GetRTrimString("btnCallBack"));
|
|
dos.CloseSelf();
|
|
dos.Execute();
|
|
}
|
|
else
|
|
{
|
|
ShowMessage(result.ErrorMessage);
|
|
return;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
ShowMessage(ex.Message);
|
|
return;
|
|
}
|
|
finally
|
|
{
|
|
fs.Close();
|
|
}
|
|
}
|
|
|
|
protected void btnQX_Click(object sender, EventArgs e)
|
|
{
|
|
CloseWindow();
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 自定义事件
|
|
|
|
private void ExecDRSQL(DataSet ds, string sBJDID, string sWBBJDID, string sFileName, string sheetName)
|
|
{
|
|
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
|
TSQL sSQL = new TSQL(dberp);
|
|
|
|
try
|
|
{
|
|
dberp.BeginTrans();
|
|
|
|
#region 生成XLWBBJDML表数据--WBBJDID|外部报价单/BJDID
|
|
sSQL.Clear();
|
|
sSQL.Add("insert into XLWBBJDML");
|
|
sSQL.Add(" ( XLWBBJDML_BJDID,XLWBBJDML_WBBJDID,XLWBBJDML_SJLY,XLWBBJDML_WBBJDMC,XLWBBJDML_PICNAME, ");
|
|
sSQL.Add(" XLWBBJDML_LMHHS,XLWBBJDML_LMHHZ,XLWBBJDML_NRHHS,XLWBBJDML_NRHHZ, ");
|
|
sSQL.Add(" XLWBBJDML_DRSJ,XLWBBJDML_WHR,XLWBBJDML_WHRID,XLWBBJDML_WHSJ ) ");
|
|
sSQL.Add(" values ");
|
|
sSQL.Add(" ( :pBJDID,:pWBBJDID,'BJTP',:pWBBJDMC,:pPICNAME, ");
|
|
sSQL.Add(" :pLMHHS,:pLMHHZ,:pNRHHS,:pNRHHZ, ");
|
|
sSQL.Add(" :pDRSJ,:pWHR,:pWHRID,:pWHSJ ) ");
|
|
sSQL.ParamByName("pBJDID").Value = sBJDID;
|
|
sSQL.ParamByName("pWBBJDID").Value = sWBBJDID;
|
|
sSQL.ParamByName("pWBBJDMC").Value = XLBJDUtils.SubStringFunc(sFileName + "【" + sheetName + "】", 100);
|
|
sSQL.ParamByName("pPICNAME").Value = XLBJDUtils.SubStringFunc(sFileName, 60);
|
|
sSQL.ParamByName("pLMHHS").Value = 0;
|
|
sSQL.ParamByName("pLMHHZ").Value = 0;
|
|
sSQL.ParamByName("pNRHHS").Value = 0;
|
|
sSQL.ParamByName("pNRHHZ").Value = 0;
|
|
sSQL.ParamByName("pDRSJ").Value = DateTime.Now;
|
|
sSQL.ParamByName("pWHR").Value = tSession.UserName;
|
|
sSQL.ParamByName("pWHRID").Value = tSession.UserId;
|
|
sSQL.ParamByName("pWHSJ").Value = DateTime.Now;
|
|
dberp.ExecSQL(sSQL);
|
|
#endregion
|
|
|
|
#region 生成XLWBBJDNR表数据------WBBJDID|外部报价单ID=======DRHH|导入行号/BJDID
|
|
sSQL.Clear();
|
|
sSQL.Add("insert into XLWBBJDNR(");
|
|
sSQL.Add(" XLWBBJDNR_BJDID, XLWBBJDNR_WBBJDID, XLWBBJDNR_DRHH, ");
|
|
sSQL.Add(" XLWBBJDNR_LMNR01, XLWBBJDNR_LMNR02, XLWBBJDNR_LMNR03, XLWBBJDNR_LMNR04, XLWBBJDNR_LMNR05, XLWBBJDNR_LMNR06,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR07, XLWBBJDNR_LMNR08, XLWBBJDNR_LMNR09, XLWBBJDNR_LMNR10, XLWBBJDNR_LMNR11, XLWBBJDNR_LMNR12,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR13, XLWBBJDNR_LMNR14, XLWBBJDNR_LMNR15, XLWBBJDNR_LMNR16, XLWBBJDNR_LMNR17, XLWBBJDNR_LMNR18,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR19, XLWBBJDNR_LMNR20, XLWBBJDNR_LMNR21, XLWBBJDNR_LMNR22, XLWBBJDNR_LMNR23, XLWBBJDNR_LMNR24,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR25, XLWBBJDNR_LMNR26, XLWBBJDNR_LMNR27, XLWBBJDNR_LMNR28, XLWBBJDNR_LMNR29, XLWBBJDNR_LMNR30,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR31, XLWBBJDNR_LMNR32, XLWBBJDNR_LMNR33, XLWBBJDNR_LMNR34, XLWBBJDNR_LMNR35, XLWBBJDNR_LMNR36,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR37, XLWBBJDNR_LMNR38, XLWBBJDNR_LMNR39, XLWBBJDNR_LMNR40, XLWBBJDNR_LMNR41, XLWBBJDNR_LMNR42,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR43, XLWBBJDNR_LMNR44, XLWBBJDNR_LMNR45, XLWBBJDNR_LMNR46, XLWBBJDNR_LMNR47, XLWBBJDNR_LMNR48,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR49, XLWBBJDNR_LMNR50, XLWBBJDNR_LMNR51, XLWBBJDNR_LMNR52, XLWBBJDNR_LMNR53, XLWBBJDNR_LMNR54,");
|
|
sSQL.Add(" XLWBBJDNR_LMNR55, XLWBBJDNR_LMNR56, XLWBBJDNR_LMNR57, XLWBBJDNR_LMNR58, XLWBBJDNR_LMNR59, XLWBBJDNR_LMNR60)");
|
|
sSQL.Add("values (:XLWBBJDNR_BJDID, :XLWBBJDNR_WBBJDID, :XLWBBJDNR_DRHH, ");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR01, :XLWBBJDNR_LMNR02, :XLWBBJDNR_LMNR03, :XLWBBJDNR_LMNR04, :XLWBBJDNR_LMNR05, :XLWBBJDNR_LMNR06,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR07, :XLWBBJDNR_LMNR08, :XLWBBJDNR_LMNR09, :XLWBBJDNR_LMNR10, :XLWBBJDNR_LMNR11, :XLWBBJDNR_LMNR12,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR13, :XLWBBJDNR_LMNR14, :XLWBBJDNR_LMNR15, :XLWBBJDNR_LMNR16, :XLWBBJDNR_LMNR17, :XLWBBJDNR_LMNR18,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR19, :XLWBBJDNR_LMNR20, :XLWBBJDNR_LMNR21, :XLWBBJDNR_LMNR22, :XLWBBJDNR_LMNR23, :XLWBBJDNR_LMNR24,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR25, :XLWBBJDNR_LMNR26, :XLWBBJDNR_LMNR27, :XLWBBJDNR_LMNR28, :XLWBBJDNR_LMNR29, :XLWBBJDNR_LMNR30,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR31, :XLWBBJDNR_LMNR32, :XLWBBJDNR_LMNR33, :XLWBBJDNR_LMNR34, :XLWBBJDNR_LMNR35, :XLWBBJDNR_LMNR36,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR37, :XLWBBJDNR_LMNR38, :XLWBBJDNR_LMNR39, :XLWBBJDNR_LMNR40, :XLWBBJDNR_LMNR41, :XLWBBJDNR_LMNR42,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR43, :XLWBBJDNR_LMNR44, :XLWBBJDNR_LMNR45, :XLWBBJDNR_LMNR46, :XLWBBJDNR_LMNR47, :XLWBBJDNR_LMNR48,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR49, :XLWBBJDNR_LMNR50, :XLWBBJDNR_LMNR51, :XLWBBJDNR_LMNR52, :XLWBBJDNR_LMNR53, :XLWBBJDNR_LMNR54,");
|
|
sSQL.Add(" :XLWBBJDNR_LMNR55, :XLWBBJDNR_LMNR56, :XLWBBJDNR_LMNR57, :XLWBBJDNR_LMNR58, :XLWBBJDNR_LMNR59, :XLWBBJDNR_LMNR60)");
|
|
//循环ds
|
|
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
|
|
{
|
|
sSQL.ParamByName("XLWBBJDNR_BJDID").Value = sBJDID;
|
|
sSQL.ParamByName("XLWBBJDNR_WBBJDID").Value = sWBBJDID;
|
|
sSQL.ParamByName("XLWBBJDNR_DRHH").Value = i + 1;
|
|
for (int j = 1; j <= 60; j++)
|
|
{
|
|
if (j <= ds.Tables[0].Columns.Count)
|
|
{
|
|
string sLMNR = ds.Tables[0].Rows[i][j - 1].ToString().Trim();
|
|
if (j <= 12)
|
|
sLMNR = XLBJDUtils.SubStringFunc(sLMNR, 250);
|
|
else
|
|
sLMNR = XLBJDUtils.SubStringFunc(sLMNR, 60);
|
|
sSQL.ParamByName("XLWBBJDNR_LMNR" + j.ToString().Trim().PadLeft(2, '0')).Value = sLMNR;
|
|
}
|
|
else
|
|
{
|
|
sSQL.ParamByName("XLWBBJDNR_LMNR" + j.ToString().Trim().PadLeft(2, '0')).Value = null;
|
|
}
|
|
}
|
|
dberp.ExecSQL(sSQL);
|
|
}
|
|
#endregion
|
|
|
|
dberp.CommitTrans();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
dberp.RollbackTrans();
|
|
ShowMessage(ex.Message);
|
|
return;
|
|
}
|
|
}
|
|
|
|
private void ReadWJ(string sPath, string sSheetName, ref DataSet aDataExcel)
|
|
{
|
|
string sMessage = "";
|
|
aDataExcel = TExcelData.ReadExcel(sPath.Trim(), sSheetName, "", false, false, ref sMessage);
|
|
if (!sMessage.Trim().Equals(""))
|
|
{
|
|
throw new Exception("文件读取失败！失败原因：" + sMessage.Trim());
|
|
}
|
|
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取Excel包含的工作簿名
|
|
/// </summary>
|
|
/// <param name="sFilePath"></param>
|
|
/// <returns></returns>
|
|
public static ArrayList GetSheetNames(string sFilePath)
|
|
{
|
|
ArrayList sheetNameList = new ArrayList();
|
|
|
|
// 根据32/64位运行时返回不同的连接字符串。
|
|
string connStr = "";
|
|
if (AppRuntime.BitType == AppBitType.Bit32)
|
|
{
|
|
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";" + "Extended Properties=Excel 8.0;";
|
|
}
|
|
else // AppBitType.Bit64
|
|
{
|
|
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + sFilePath + ";" + "Extended Properties=Excel 8.0;";
|
|
}
|
|
|
|
OleDbConnection m_conn = new OleDbConnection(connStr);
|
|
m_conn.Open();
|
|
DataTable sheetNames = m_conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
|
|
if (sheetNames.Rows[0].Table.Rows.Count == 1)
|
|
{
|
|
string sSheetName = sheetNames.Rows[0].Table.Rows[0]["TABLE_NAME"].ToString().Trim();
|
|
if (sSheetName.StartsWith("'") && sSheetName.EndsWith("'"))
|
|
sSheetName = sSheetName.TrimStart('\'').TrimEnd('\'');
|
|
if (sSheetName.EndsWith("$"))
|
|
sSheetName = sSheetName.TrimEnd('$');
|
|
sheetNameList.Add(sSheetName);
|
|
}
|
|
else
|
|
{
|
|
for (int i = 0; i < sheetNames.Rows[0].Table.Rows.Count; i++)
|
|
{
|
|
string sSheetName = sheetNames.Rows[0].Table.Rows[i]["TABLE_NAME"].ToString().Trim();
|
|
if (sSheetName.StartsWith("'") && sSheetName.EndsWith("'"))
|
|
sSheetName = sSheetName.TrimStart('\'').TrimEnd('\'');
|
|
if (sSheetName.EndsWith("$"))
|
|
sSheetName = sSheetName.TrimEnd('$');
|
|
sheetNameList.Add(sSheetName);
|
|
}
|
|
}
|
|
if (m_conn.State == ConnectionState.Open)
|
|
{
|
|
m_conn.Dispose();
|
|
m_conn = null;
|
|
}
|
|
|
|
return sheetNameList;
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
}
|