529 lines
23 KiB
Plaintext
529 lines
23 KiB
Plaintext
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 System.Collections.Generic;
|
||
using System.Text.RegularExpressions;
|
||
using GThinking.N_MISPROAS;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
|
||
namespace GThinking.N_XLXSBJ
|
||
{
|
||
public partial class DRJGBYZZ1_AXJGZDR : SingleCardBase
|
||
{
|
||
protected void Page_Load(object sender, EventArgs e)
|
||
{
|
||
this.UMID = 100860017;
|
||
this.UMName = "按型号规格电压等级标准调表价";
|
||
base.CurMaster = Master;
|
||
this.PageHeight = 200;
|
||
this.PageWidth = 480;
|
||
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;
|
||
fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
|
||
fupdPost.Filter = "XLS,XLSX";
|
||
base.InitPage();
|
||
|
||
if (!IsPostBack)
|
||
{
|
||
txtJGBID.Value = PageParam.GetRTrimString("JGBID");
|
||
txtYZID.Value = PageParam.GetRTrimString("sYZID");
|
||
txtYZMC.Text = GetYZMC(txtYZID.Value.Trim());
|
||
}
|
||
}
|
||
|
||
#region 控件事件
|
||
|
||
protected void btnQD_Click(object sender, EventArgs e)
|
||
{
|
||
if (fupdPost.FilePath.Equals(""))
|
||
{
|
||
ShowMessage("请选择需要导入的文件！");
|
||
return;
|
||
}
|
||
if (txtYZID.Value.Trim().Equals(""))
|
||
{
|
||
ShowMessage("调价因子为空，请从正确入口打开！");
|
||
return;
|
||
}
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL sSQL = new TSQL(dberp);
|
||
try
|
||
{
|
||
lblTitle.Text = "正在导入数据，请稍后...";
|
||
dberp.BeginTrans();
|
||
DataSet aDataExcel = new DataSet();
|
||
Mac.MacLog.TraceFormat("DR0 ReadAllSheets Start");
|
||
string sMessage = "";
|
||
aDataExcel = ReadAllSheets(fupdPost.FilePath, 0, true, ref sMessage);
|
||
if (!sMessage.Trim().Equals(""))
|
||
{
|
||
throw new Exception("文件读取失败！具体原因：" + sMessage);
|
||
}
|
||
Mac.MacLog.TraceFormat("DR0.1 ReadAllSheets Done TablesCount={0}", aDataExcel.Tables.Count);
|
||
ExcelColumnValid(aDataExcel);
|
||
InsertYZZ1(dberp, sSQL, aDataExcel);
|
||
SaveExcelToServer(aDataExcel);
|
||
dberp.CommitTrans();
|
||
ShowMessage("设置完成！");
|
||
|
||
//执行完毕后关闭当前页面
|
||
DialogOperationScript dos = new DialogOperationScript(this);
|
||
dos.ClickOwnerButton(PageParam.GetRTrimString("btnRefresh1CID"));
|
||
dos.CloseSelf();
|
||
dos.Execute();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR_ERR: {0}", ex.Message);
|
||
lblTitle.Text = "";
|
||
dberp.RollbackTrans();
|
||
ShowMessage(ex.Message);
|
||
}
|
||
}
|
||
|
||
protected void btnQX_Click(object sender, EventArgs e)
|
||
{
|
||
CloseWindow();
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 自定义事件
|
||
|
||
private string GetYZMC(string sYZID)
|
||
{
|
||
string sYZMC = "";
|
||
if (sYZID.Equals(""))
|
||
return sYZMC;
|
||
TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
|
||
TSQL SQL = new TSQL(dberp);
|
||
SQL.Clear();
|
||
SQL.Add("select XLXSTZYZ_YZMC from XLXSTZYZ where XLXSTZYZ_YZID = :pYZID");
|
||
SQL.ParamByName("pYZID").Value = sYZID;
|
||
DataSet ds = dberp.OpenDataSet(SQL);
|
||
if (ds.Tables[0].Rows.Count > 0)
|
||
{
|
||
sYZMC = ds.Tables[0].Rows[0]["XLXSTZYZ_YZMC"].ToString().Trim();
|
||
}
|
||
return sYZMC;
|
||
}
|
||
|
||
private void SaveExcelToServer(DataSet dsExcel)
|
||
{
|
||
if (dsExcel == null || dsExcel.Tables.Count < 2)
|
||
return;
|
||
string sUploadPath = HttpContext.Current.Server.MapPath("~/work/temp/外径盘具金额/");
|
||
if (!Directory.Exists(sUploadPath))
|
||
Directory.CreateDirectory(sUploadPath);
|
||
string sTargetPath = Path.Combine(sUploadPath, "外径盘具金额.xlsx");
|
||
if (File.Exists(sTargetPath))
|
||
File.Delete(sTargetPath);
|
||
DataSet dsSheet2 = new DataSet();
|
||
dsSheet2.Tables.Add(dsExcel.Tables[1].Copy());
|
||
string sError = "";
|
||
TExcelData.CreateExcel_NPOI(sTargetPath, dsSheet2, ref sError);
|
||
if (!sError.Equals(""))
|
||
throw new Exception("保存第二个sheet到服务器失败：" + sError);
|
||
}
|
||
|
||
private void InsertYZZ1(TDatabase dberp, TSQL sSQL, DataSet ds)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR1.6 InsertYZZ1Entered");
|
||
//第二个sheet：外径->普通线盘金额、大芯线盘金额
|
||
Dictionary<string, double> dicPTXP = new Dictionary<string, double>();
|
||
Dictionary<string, double> dicDXXP = new Dictionary<string, double>();
|
||
Dictionary<string, double> dicZXJM = new Dictionary<string, double>();
|
||
DataTable dtSheet2 = ds.Tables[1];
|
||
Mac.MacLog.TraceFormat("DR1.7 Sheet2Rows={0}", dtSheet2.Rows.Count);
|
||
for (int i = 0; i < dtSheet2.Rows.Count; i++)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR1.8 Sheet2Row[{0}]", i);
|
||
string sWJ = sGetCellText(dtSheet2.Rows[i], 0);
|
||
if (sWJ.Equals(""))
|
||
continue;
|
||
double dPTXP = CMISproConvert.StrToFloat(sGetCellText(dtSheet2.Rows[i], 1), 0);
|
||
double dDXXP = CMISproConvert.StrToFloat(sGetCellText(dtSheet2.Rows[i], 2), 0);
|
||
if (!dicPTXP.ContainsKey(sWJ))
|
||
dicPTXP.Add(sWJ, dPTXP);
|
||
if (!dicDXXP.ContainsKey(sWJ))
|
||
dicDXXP.Add(sWJ, dDXXP);
|
||
}
|
||
|
||
Mac.MacLog.TraceFormat("DR1.9 Sheet2Processed PTXP={0} DXXP={1}", dicPTXP.Count, dicDXXP.Count);
|
||
Mac.MacLog.TraceFormat("DR2");
|
||
|
||
//矿物电缆基本型号
|
||
string[] arrBaseModels = new string[] { "BTLY", "BTTRZ", "BTTVZ", "BTTYRZ", "BTTYZ", "BTTZ", "RTTVZ", "RTTYZ", "RTTZ", "YTTW", "YTTWG", "YTTWV", "YTTWY", "YTTWZ", "BTTVQ", "BTTYQ" };
|
||
|
||
DataTable dtSheet1 = ds.Tables[0];
|
||
string sJGBID = txtJGBID.Value.Trim();
|
||
string sYZID = txtYZID.Value.Trim();
|
||
int iInsertCount = 0;
|
||
//A|× B|*
|
||
string sXLJGU007 = GetOption(102579000, "XLJGU007");
|
||
string sDefaultXLBZID = XLBJDUtils.GetDefaultXLBZID();
|
||
Dictionary<string, string> dicDRMXBZ = new Dictionary<string, string>();
|
||
dicDRMXBZ.Clear();
|
||
sSQL.Clear();
|
||
sSQL.Add("select XLBZ_XLBZID,XLBZ_XLBZLXMC from XLBZ where 1 = 1 ");
|
||
DataSet dsXLBZ = dberp.OpenDataSet(sSQL);
|
||
if (dsXLBZ.Tables[0].Rows.Count > 0)
|
||
{
|
||
for (int k = 0; k < dsXLBZ.Tables[0].Rows.Count; k++)
|
||
{
|
||
dicDRMXBZ.Add(dsXLBZ.Tables[0].Rows[k]["XLBZ_XLBZID"].ToString().Trim(), dsXLBZ.Tables[0].Rows[k]["XLBZ_XLBZLXMC"].ToString().Trim());
|
||
}
|
||
}
|
||
Mac.MacLog.TraceFormat("DR3");
|
||
|
||
for (int i = 0; i < dtSheet1.Rows.Count; i++)
|
||
{
|
||
string sXLXHID = sGetCellText(dtSheet1.Rows[i], 0);
|
||
string sXLGGID = sGetCellText(dtSheet1.Rows[i], 1);
|
||
string sXLBZID = sGetCellText(dtSheet1.Rows[i], 2);
|
||
string sDYDJID = sGetCellText(dtSheet1.Rows[i], 3);
|
||
string sWJ = sGetCellText(dtSheet1.Rows[i], 4);
|
||
string sLB = sGetCellText(dtSheet1.Rows[i], 5);
|
||
string sDW = sGetCellText(dtSheet1.Rows[i], 6);
|
||
|
||
if (sXLXHID.Equals(""))
|
||
throw new Exception("第" + (i + 2).ToString() + "行型号为空，不允许导入！");
|
||
if (sXLGGID.Equals(""))
|
||
throw new Exception("第" + (i + 2).ToString() + "行规格为空，不允许导入！");
|
||
if (sDYDJID.Equals(""))
|
||
throw new Exception("第" + (i + 2).ToString() + "行电压等级为空，不允许导入！");
|
||
if (sWJ.Equals(""))
|
||
throw new Exception("第" + (i + 2).ToString() + "行外径为空，不允许导入！");
|
||
|
||
if (sXLJGU007.Equals("A"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("*", "×");
|
||
}
|
||
else if (sXLJGU007.Equals("B"))
|
||
{
|
||
sXLGGID = sXLGGID.Replace("x", "X").Replace("X", "*").Replace("×", "*");
|
||
}
|
||
sXLBZID = XLBJDUtils.GetBZXLZBZID(sXLBZID, sDefaultXLBZID, dicDRMXBZ);
|
||
if (sXLBZID.Equals(""))
|
||
sXLBZID = " ";
|
||
|
||
//规则1：所有产品，包装要求按扎为单位的，均不考虑计算装盘数据
|
||
if (sDW.Contains("扎"))
|
||
continue;
|
||
|
||
bool bIsCable = sLB.Contains("电缆");
|
||
bool bIsWire = sLB.Contains("电线");
|
||
bool bNeedCalc = false;
|
||
|
||
if (bIsCable)
|
||
{
|
||
//规则3：所有归类于电缆产品的各型号规格的产品，均默认需计算装盘数据
|
||
bNeedCalc = true;
|
||
}
|
||
else if (bIsWire)
|
||
{
|
||
//规则2：归类于电线类产品，规格在16平方（含）及以下，按米为单位的，均不考虑计算装盘数据；25平方（含）规格以上的产品，则需要计算装盘数据
|
||
if (sDW.Contains("米") || sDW.ToUpper().Contains("M"))
|
||
{
|
||
double dMaxMM = 0;
|
||
if (dicZXJM.ContainsKey(sXLGGID))
|
||
dMaxMM=dicZXJM[sXLGGID];
|
||
else
|
||
{
|
||
Dictionary<string, string> dicXLGGB = XLBJDUtils.GetXXJGIDByXLGGID(sXLGGID, sXLXHID);
|
||
string fZXJM = dicXLGGB["ZXJM"];
|
||
dMaxMM = CMISproConvert.StrToFloat(fZXJM);
|
||
dicZXJM[sXLGGID] = dMaxMM;
|
||
}
|
||
|
||
if (dMaxMM >= 25)
|
||
bNeedCalc = true;
|
||
}
|
||
}
|
||
|
||
if (!bNeedCalc)
|
||
continue;
|
||
|
||
//规则4：基本型号及矿物电缆算大芯盘
|
||
bool bUseDXXP = false;
|
||
string sUpperXLXHID = sXLXHID.ToUpper();
|
||
for (int j = 0; j < arrBaseModels.Length; j++)
|
||
{
|
||
if (sUpperXLXHID.Contains(arrBaseModels[j]))
|
||
{
|
||
bUseDXXP = true;
|
||
break;
|
||
}
|
||
}
|
||
|
||
double dAmount = 0;
|
||
if (bUseDXXP)
|
||
{
|
||
if (!dicDXXP.ContainsKey(sWJ))
|
||
throw new Exception("第" + (i + 2).ToString() + "行外径[" + sWJ + "]在第二个sheet中未找到对应大芯线盘金额！");
|
||
dAmount = dicDXXP[sWJ];
|
||
}
|
||
else
|
||
{
|
||
if (!dicPTXP.ContainsKey(sWJ))
|
||
throw new Exception("第" + (i + 2).ToString() + "行外径[" + sWJ + "]在第二个sheet中未找到对应普通线盘金额！");
|
||
dAmount = dicPTXP[sWJ];
|
||
}
|
||
Mac.MacLog.TraceFormat("DR4");
|
||
|
||
if (!CMISproDataBase.ExistsRecord("DBERP", string.Format("select 1 from XLXSJGBYZZ1 where XLXSJGBYZZ1_JGBID='{0}' and XLXSJGBYZZ1_XLXHID='{1}' and XLXSJGBYZZ1_XLGGID='{2}' and XLXSJGBYZZ1_DYDJID='{3}' and XLXSJGBYZZ1_XLBZID='{4}' and XLXSJGBYZZ1_YZID='{5}'",
|
||
sJGBID, sXLXHID, sXLGGID, sDYDJID, sXLBZID, sYZID)))
|
||
{
|
||
sSQL.Clear();
|
||
sSQL.Add("insert into XLXSJGBYZZ1(XLXSJGBYZZ1_JGBID,XLXSJGBYZZ1_XLXHID,XLXSJGBYZZ1_XLGGID,XLXSJGBYZZ1_DYDJID,XLXSJGBYZZ1_XLBZID,XLXSJGBYZZ1_YZID,XLXSJGBYZZ1_YZZ,XLXSJGBYZZ1_TJDH,XLXSJGBYZZ1_WHR,XLXSJGBYZZ1_WHRID,XLXSJGBYZZ1_WHSJ)");
|
||
sSQL.Add(" values(:XLXSJGBYZZ1_JGBID,:XLXSJGBYZZ1_XLXHID,:XLXSJGBYZZ1_XLGGID,:XLXSJGBYZZ1_DYDJID,:XLXSJGBYZZ1_XLBZID,:XLXSJGBYZZ1_YZID,:XLXSJGBYZZ1_YZZ,:XLXSJGBYZZ1_TJDH,:XLXSJGBYZZ1_WHR,:XLXSJGBYZZ1_WHRID,:XLXSJGBYZZ1_WHSJ)");
|
||
sSQL.ParamByName("XLXSJGBYZZ1_JGBID").Value = sJGBID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLXHID").Value = sXLXHID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLGGID").Value = sXLGGID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_DYDJID").Value = sDYDJID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_YZID").Value = sYZID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_YZZ").Value = dAmount;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_TJDH").Value = " ";
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHR").Value = tSession.UserName;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHRID").Value = tSession.UserId;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHSJ").Value = DateTime.Now;
|
||
dberp.ExecSQL(sSQL);
|
||
}
|
||
else
|
||
{
|
||
sSQL.Clear();
|
||
sSQL.Add("update XLXSJGBYZZ1 set XLXSJGBYZZ1_YZZ=:XLXSJGBYZZ1_YZZ,XLXSJGBYZZ1_TJDH=:XLXSJGBYZZ1_TJDH,XLXSJGBYZZ1_WHR=:XLXSJGBYZZ1_WHR,XLXSJGBYZZ1_WHRID=:XLXSJGBYZZ1_WHRID,XLXSJGBYZZ1_WHSJ=:XLXSJGBYZZ1_WHSJ");
|
||
sSQL.Add(" where XLXSJGBYZZ1_JGBID=:XLXSJGBYZZ1_JGBID");
|
||
sSQL.Add(" and XLXSJGBYZZ1_XLXHID=:XLXSJGBYZZ1_XLXHID");
|
||
sSQL.Add(" and XLXSJGBYZZ1_XLGGID=:XLXSJGBYZZ1_XLGGID");
|
||
sSQL.Add(" and XLXSJGBYZZ1_DYDJID=:XLXSJGBYZZ1_DYDJID");
|
||
sSQL.Add(" and XLXSJGBYZZ1_XLBZID=:XLXSJGBYZZ1_XLBZID");
|
||
sSQL.Add(" and XLXSJGBYZZ1_YZID=:XLXSJGBYZZ1_YZID");
|
||
sSQL.ParamByName("XLXSJGBYZZ1_JGBID").Value = sJGBID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLXHID").Value = sXLXHID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLGGID").Value = sXLGGID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_DYDJID").Value = sDYDJID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_XLBZID").Value = sXLBZID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_YZID").Value = sYZID;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_YZZ").Value = dAmount;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_TJDH").Value = " ";
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHR").Value = tSession.UserName;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHRID").Value = tSession.UserId;
|
||
sSQL.ParamByName("XLXSJGBYZZ1_WHSJ").Value = DateTime.Now;
|
||
dberp.ExecSQL(sSQL);
|
||
}
|
||
iInsertCount++;
|
||
}
|
||
if (iInsertCount == 0)
|
||
throw new Exception("没有符合计算规则的数据可导入！");
|
||
}
|
||
|
||
private double GetMaxSpecMM(string sXLGGID)
|
||
{
|
||
double dMax = 0;
|
||
MatchCollection mc = Regex.Matches(sXLGGID, @"\d+(\.\d+)?");
|
||
for (int i = 0; i < mc.Count; i++)
|
||
{
|
||
double d = CMISproConvert.StrToFloat(mc[i].Value, 0);
|
||
if (d > dMax)
|
||
dMax = d;
|
||
}
|
||
return dMax;
|
||
}
|
||
|
||
private void ExcelColumnValid(DataSet ds)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR1");
|
||
Mac.MacLog.TraceFormat("DR1.1 TablesCount={0}", ds.Tables.Count);
|
||
if (ds.Tables.Count < 2)
|
||
throw new Exception("导入文件必须包含两个sheet！");
|
||
|
||
Mac.MacLog.TraceFormat("DR1.2 Sheet1Columns={0} Sheet2Columns={1}", ds.Tables[0].Columns.Count, ds.Tables[1].Columns.Count);
|
||
|
||
string[] arrSheet1Cols = new string[] { "型号", "规格", "标准", "电压等级", "外径", "类别", "单位" };
|
||
for (int i = 0; i < arrSheet1Cols.Length; i++)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR1.3 Sheet1Col[{0}]={1}", i, ds.Tables[0].Columns[i].ColumnName);
|
||
if (i == 4) // 第5列兼容"外径"或"线径"
|
||
{
|
||
string colName = ds.Tables[0].Columns[i].ColumnName.Trim();
|
||
if (!colName.Equals("外径") && !colName.Equals("线径"))
|
||
throw new Exception("导入文件第一个sheet栏目格式不正确，第5列应为：外径或线径！");
|
||
}
|
||
else
|
||
{
|
||
if (!ds.Tables[0].Columns[i].ColumnName.Trim().Equals(arrSheet1Cols[i]))
|
||
throw new Exception("导入文件第一个sheet栏目格式不正确，依次为：型号、规格、标准、电压等级、外径/线径、类别、单位！");
|
||
}
|
||
}
|
||
|
||
string[] arrSheet2Cols = new string[] { "外径", "普通线盘金额", "大芯线盘金额" };
|
||
for (int i = 0; i < arrSheet2Cols.Length; i++)
|
||
{
|
||
Mac.MacLog.TraceFormat("DR1.4 Sheet2Col[{0}]={1}", i, ds.Tables[1].Columns[i].ColumnName);
|
||
if (i == 0) // 第1列兼容"外径"或"线径"
|
||
{
|
||
string colName = ds.Tables[1].Columns[i].ColumnName.Trim();
|
||
if (!colName.Equals("外径") && !colName.Equals("线径"))
|
||
throw new Exception("导入文件第二个sheet栏目格式不正确，第1列应为：外径或线径！");
|
||
}
|
||
else
|
||
{
|
||
if (!ds.Tables[1].Columns[i].ColumnName.Trim().Equals(arrSheet2Cols[i]))
|
||
throw new Exception("导入文件第二个sheet栏目格式不正确，依次为：外径/线径、普通线盘金额、大芯线盘金额！");
|
||
}
|
||
}
|
||
Mac.MacLog.TraceFormat("DR1.5 ExcelColumnValidDone");
|
||
}
|
||
|
||
private void ReadWJ(string sPath, ref DataSet aDataExcel)
|
||
{
|
||
string sMessage = "";
|
||
aDataExcel = ReadAllSheets(sPath.Trim(), 0, true, ref sMessage);
|
||
if (!sMessage.Trim().Equals(""))
|
||
{
|
||
throw new Exception("文件读取失败！具体原因：" + sMessage);
|
||
}
|
||
}
|
||
|
||
private DataSet ReadAllSheets(string filePath, int fromRowIndex, bool hasHeaderRow, ref string errorMessage)
|
||
{
|
||
try
|
||
{
|
||
DataSet ds = new DataSet();
|
||
string ext = System.IO.Path.GetExtension(filePath).ToLowerInvariant();
|
||
IWorkbook wk = null;
|
||
using (FileStream fs = File.OpenRead(filePath))
|
||
{
|
||
if (ext == ".xlsx")
|
||
wk = new XSSFWorkbook(fs);
|
||
else
|
||
wk = new HSSFWorkbook(fs);
|
||
}
|
||
|
||
for (int k = 0; k < wk.NumberOfSheets; k++)
|
||
{
|
||
ISheet sheet = wk.GetSheetAt(k);
|
||
if (sheet == null)
|
||
continue;
|
||
string SheetName = sheet.SheetName;
|
||
|
||
if (ds.Tables.Contains(SheetName))
|
||
continue;
|
||
else
|
||
ds.Tables.Add(SheetName);
|
||
|
||
int minCellNum = -1;//最小有效列数
|
||
int maxCellNum = -1;//最大有效列数
|
||
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow iRow = sheet.GetRow(i);
|
||
if (iRow != null && iRow.Cells.Count > 0)
|
||
{
|
||
if (minCellNum == -1)
|
||
minCellNum = iRow.FirstCellNum;
|
||
else
|
||
{
|
||
if (iRow.FirstCellNum < minCellNum)
|
||
minCellNum = iRow.FirstCellNum;
|
||
}
|
||
if (maxCellNum == -1)
|
||
maxCellNum = iRow.LastCellNum;
|
||
else
|
||
{
|
||
if (iRow.LastCellNum > maxCellNum)
|
||
maxCellNum = iRow.LastCellNum;
|
||
}
|
||
}
|
||
}
|
||
|
||
IRow headerRow = sheet.GetRow(fromRowIndex);
|
||
//添加列
|
||
for (int i = minCellNum; i < maxCellNum; i++)
|
||
{
|
||
if (i < headerRow.FirstCellNum || i >= headerRow.LastCellNum)
|
||
{
|
||
DataColumn column = new DataColumn("F" + Convert.ToString(i + 1));
|
||
ds.Tables[SheetName].Columns.Add(column);
|
||
}
|
||
else
|
||
{
|
||
if (hasHeaderRow)//第一行为标题行
|
||
{
|
||
DataColumn column = new DataColumn(headerRow.GetCell(i) == null ? " " : headerRow.GetCell(i).StringCellValue);
|
||
ds.Tables[SheetName].Columns.Add(column);
|
||
}
|
||
else
|
||
{
|
||
DataColumn column = new DataColumn("F" + Convert.ToString(i + 1));
|
||
ds.Tables[SheetName].Columns.Add(column);
|
||
}
|
||
}
|
||
}
|
||
//添加行
|
||
int iFirstLine = fromRowIndex;
|
||
if (hasHeaderRow)
|
||
iFirstLine = fromRowIndex + 1;
|
||
for (int i = iFirstLine; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
DataRow dataRow = ds.Tables[SheetName].NewRow();
|
||
|
||
if (row != null && row.Cells.Count > 0)
|
||
{
|
||
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
|
||
{
|
||
if (row.GetCell(j) != null)
|
||
dataRow[j] = TExcelData.GetCellValue(row.GetCell(j));
|
||
}
|
||
}
|
||
ds.Tables[SheetName].Rows.Add(dataRow);
|
||
}
|
||
}
|
||
|
||
return ds;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
errorMessage = ex.Message;
|
||
return null;
|
||
}
|
||
}
|
||
|
||
private string sGetCellText(DataRow Row, int ColumnIndex)
|
||
{
|
||
return Row[ColumnIndex].ToString().Trim().Replace("\r\n", "").Replace("\n", "");
|
||
}
|
||
|
||
#endregion
|
||
}
|
||
}
|