protected void Page_Load(object sender, EventArgs e)
{
PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook();
PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("Sheet1");
PageOffice.ExcelReader.Table table = sheet.OpenTable("B4:D8");
while (!table.EOF)
{
//获取提交的数值
//DataFields.Count标识的是table的列数
if (!table.DataFields.IsEmpty)
{
content += "<br/>月份名称：" + table.DataFields[0].Text;
content += "<br/>计划完成量：" + table.DataFields[1].Text;
content += "<br/>实际完成量：" + table.DataFields[2].Text;
content += "<br/>*********************************************";
}
//循环进入下一行
table.NextRow();
}
table.Close();
workBook.ShowPage(500, 400);
workBook.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook();
PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("Sheet1");
content += "testA1：" + sheet.OpenCellByDefinedName("testA1").Value + "<br/>";
content += "testB1：" + sheet.OpenCellByDefinedName("testB1").Value + "<br/>";
workBook.ShowPage(500, 400);
workBook.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook();
PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("Sheet1");
PageOffice.ExcelReader.Table table = sheet.OpenTable("Info");
int result = 0;
while (!table.EOF)
{
//获取提交的数值
//DataFields.Count标识的是提交过来的table的列数
if (!table.DataFields.IsEmpty)
{
content += "<br/>月份名称：" + table.DataFields[0].Text;
content += "<br/>计划完成量：" + table.DataFields[1].Text;
content += "<br/>实际完成量：" + table.DataFields[2].Text;
content += "<br/>累计完成量：" + table.DataFields[3].Text;
if (string.IsNullOrEmpty(table.DataFields[2].Text) || !int.TryParse(table.DataFields[2].Text, out result) ||
!int.TryParse(table.DataFields[1].Text, out result))
{
content += "<br/>完成率：0";
}
else
{
float f = int.Parse(table.DataFields[2].Text);
f = f / int.Parse(table.DataFields[1].Text);
content += "<br/>完成率：" + string.Format("{0:P}", f);
}
content += "<br/>*********************************************";
}
//循环进入下一行
table.NextRow();
}
table.Close();
workBook.ShowPage(500, 400);
workBook.Close();
}
/// <summary>
/// 更新或插入客户信息
/// </summary>
/// <param name="cmd"></param>
/// <param name="cid">客户信息ID</param>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="maxId">当前客户信息表中最大的ID</param>
/// <returns></returns>
private int UpdateOrInsertCustInfo(OleDbCommand cmd, string cid, PageOffice.ExcelReader.Workbook workBook, PageOffice.ExcelReader.Sheet sheet, int maxId)
{
string sql = "";
string custName = sheet.OpenCell("CustName").Value.Trim(); //获取提交信息，客户名称
string orderId = sheet.OpenCell("OrderNum").Value; //获取提交信息，订单编号
string district = sheet.OpenCell("CustDistrict").Value; //获取提交信息，客户所在区域
double date = 0;
if (sheet.OpenCell("OrderDate").Value.Length > 0)
{
date = Double.Parse(sheet.OpenCell("OrderDate").Value);//获取提交信息，订单日期
}
else
{
date = int.Parse(DateTime.Now.ToShortDateString());
}
string salesName = sheet.OpenCell("SalesName").Value; //获取提交信息，销售人员姓名
string amount = sheet.OpenCell("Amount").Value; //获取提交信息，销售金额
int num = 0;
if (custName.Trim().Length > 0)
{
if (cid.Trim() != "")
{
sql = "Update OrderMaster set orderNum = '" + orderId + "',OrderDate = " + date
+ ",CustName='" + custName + "',CustDistrict='" + district + "',SalesName = '" + salesName + "' ,Amount= " + amount;
sql += " where Id = " + cid;
}
else
{
sql = "Insert into OrderMaster values(" + (maxId + 1) + ",'" + orderId + "'," + date + ",'" + custName + "','"
+ district + "','" + Convert.ToString(Session["UserName"]) + "','" + salesName + "'," + amount + ")";
}
try
{
cmd.CommandText = sql;
num = cmd.ExecuteNonQuery();//更新客户信息
}
catch (Exception ex)
{
strErrHtml += "保存失败，失败原因为：" + ex.Message + "\n";
}
}
else
{
if (custName.Trim().Length <= 0)
{
strErrHtml += "请输入用户名！\n";
}
}
return(num);
}
protected void Page_Load(object sender, EventArgs e)
{
if (Session["UserName"] == null || Session["UserName"].ToString().Length <= 0)
{
Response.Redirect("Login.aspx");
}
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|demo_excelorder.mdb";
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
PageOffice.ExcelReader.Workbook workBook = new PageOffice.ExcelReader.Workbook();;
PageOffice.ExcelReader.Sheet sheet = workBook.OpenSheet("销售订单");
string sql = "";
if (Request.QueryString["ID"] != null && Request.QueryString["ID"].Length > 0)
{
#region 修改保存
string id = "";
id = Request.QueryString["ID"];
int num;
//保存客户信息
num = UpdateOrInsertCustInfo(cmd, id, workBook, sheet, 0);
if (num > 0) //保存成功
{
int resDelete = 0; //要删除的记录条数
//删除当前orderID下的产品数据
sql = "delete from OrderDetail where OrderId = " + id;
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
strErrHtml += "删除客户ID为" + id + "的产品订单信息失败，失败原因为：" + ex.Message + "\n";
resDelete = -1;
}
//删除成功或无数据可删除时
if (resDelete >= 0)
{
//插入产品信息
InsertProductInfo(cmd, workBook, sheet, id);
}
}
else
{
strErrHtml += "<br>客户信息保存失败！";
}
#endregion
}
else
{
#region 新建保存
int maxId = 0;//OrderMaster表中最大ID号
sql = "select max(ID) from OrderMaster ";
cmd.CommandText = sql;
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
maxId = int.Parse(obj.ToString());
//保存客户信息
if (UpdateOrInsertCustInfo(cmd, "", workBook, sheet, maxId) > 0)
{
//插入产品信息
InsertProductInfo(cmd, workBook, sheet, (maxId + 1).ToString());
}
}
}
catch (Exception ex)
{
strErrHtml += "新建订单失败，失败原因为：" + ex.Message;
}
#endregion
}
//保存失败,弹出提示框
if (strErrHtml.Length > 0)
{
strErrHtml = "\n" + strErrHtml;
workBook.ShowPage(410, 260);
workBook.CustomSaveResult = "error";
}
workBook.Close();
conn.Close();
string mScriptName = "updateorder.aspx";
string mHttpUrl = "http://" + Request.ServerVariables["HTTP_HOST"] + Request.ServerVariables["SCRIPT_NAME"];
BaseUrl = mHttpUrl.Substring(0, mHttpUrl.Length - mScriptName.Length);
}
/// <summary>
/// 插入产品信息
/// </summary>
/// <param name="cmd"></param>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="orderId">和产品相对应的客户ID</param>
private void InsertProductInfo(OleDbCommand cmd, PageOffice.ExcelReader.Workbook workBook, PageOffice.ExcelReader.Sheet sheet, string orderId)
{
PageOffice.ExcelReader.Table table = sheet.OpenTable("OrderDetail");
while (!table.EOF)
{
//根据当前OrderID重新插入产品数据
string sql = "insert into OrderDetail(OrderID, ProductCode, ProductName, ProductType, Unit, Quantity, Price) values(" + orderId;
if (!table.DataFields.IsEmpty) //数据字段非空时
{
int qua = 0; //数量
if (table.DataFields[4].Value.Trim().Length > 0 && int.TryParse(table.DataFields[4].Value.Trim(), out qua))
{
qua = int.Parse(table.DataFields[4].Value.Trim());
}
float price = 0.00f;//单价
if (float.TryParse(table.DataFields[5].Value.Trim(), out price))
{
price = float.Parse(table.DataFields[5].Value.Trim());
}
sql += ",'" + table.DataFields[0].Value + "','" + table.DataFields[1].Value + "','" +
table.DataFields[2].Value.Trim() + "','" + table.DataFields[3].Value.Trim() + "'," +
qua + ",'" + price + "')";
try
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
strErrHtml += "客户ID为" + orderId + "的产品订单信息添加失败，失败原因为：" + ex.Message + "\n";;
}
}
table.NextRow(); //跳到下一行
}
table.Close(); //关闭table
}
/// <summary>
/// 提取并保存文档中的数据
/// </summary>
/// <param name="FilePath"></param>
/// <param name="ProjectID"></param>
/// <returns></returns>
public ActionResult SaveData(string FilePath, int ProjectID, int RecordID)
{
PageOffice.ExcelReader.Workbook wb = new PageOffice.ExcelReader.Workbook();
PageOffice.ExcelReader.Sheet sheet = wb.OpenSheet("Sheet1");
E_tb_Project eProject = tProject.GetModel(ProjectID);
PageOffice.ExcelReader.Table table = sheet.OpenTable(eProject.SampleDataRange.Replace("：", ":").ToUpper());
DataTable dt = new DataTable();
for (int i = 0; i < table.DataFields.Count; i++)
{
dt.Columns.Add(table.DataFields[i].Text);
}
table.NextRow();
while (!table.EOF)
{
//获取提交的数值
if (!table.DataFields.IsEmpty)
{
DataRow row = dt.NewRow();
for (int i = 0; i < table.DataFields.Count; i++)
{
row[i] = table.DataFields[i].Text;
}
dt.Rows.Add(row);
}
try
{
table.NextRow(); //循环进入下一行
}
catch
{
break;
}
}
//删除历史数据
tRecordSample.DeleteListByWhere("RecordFilePath='" + FilePath + "'");
tTestReportData.DeleteByWhere("RecordFilePath='" + FilePath + "'");
//关联检验报告
Regex reg = new Regex("^[0-9]+(.[0-9])?$");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (eProject.IsPesCheck != null && Convert.ToInt32(eProject.IsPesCheck.ToString()) == 1) //判断是否为检验农药残留项目
{
DataRow item = dt.Rows[i];
string SampleName = item["样品名称"].ToString(); //样品名称
string strResult = item["复检值2"].ToString(); //复检值2
if (strResult.Trim() == "")
{
strResult = item["最终值"].ToString();//最终值
}
if (!string.IsNullOrEmpty(SampleName.Trim()))
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.RecordID = RecordID; //原始记录ID
eTestReportData.RecordFilePath = FilePath; //原始记录文件名
eTestReportData.ReportID = 0; //检验报告ID
eTestReportData.TestName = SampleName; //检验名称/检验项目名称 (农药残留检验项目，直接显示样品名称)
eTestReportData.TestStandard = ""; //检验标准
eTestReportData.TestResult = strResult; //检验结果
eTestReportData.QualifiedLevel = ""; //是否合格
eTestReportData.TestPersonnelName = ""; //检验人
tTestReportData.Add(eTestReportData);
}
}
else
{
DataRow item = dt.Rows[i];
string strSampleID = item["编号"].ToString();
string strResult = item["最终值"].ToString();
tb_Sample eSample = tSample.GetModelList("sampleNum='" + strSampleID.Trim() + "'").FirstOrDefault();
if (eSample != null && !string.IsNullOrEmpty(strResult))
{
E_tb_TestReportData eTestReportData = new E_tb_TestReportData();
eTestReportData.RecordID = RecordID; //原始记录ID
eTestReportData.RecordFilePath = FilePath; //原始记录文件名
eTestReportData.ReportID = 0; //检验报告ID
eTestReportData.TestName = ""; //检验名称/检验项目名称
eTestReportData.TestStandard = ""; //检验标准
eTestReportData.TestResult = strResult; //检验结果
eTestReportData.QualifiedLevel = ""; //是否合格
eTestReportData.TestPersonnelName = ""; //检验人
tTestReportData.Add(eTestReportData);
}
}
}
table.Close();
wb.Close();
return(View("/views/OriginalRecord/SaveData.cshtml"));
}
