/// <summary>
/// 根据分类查询配件库存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void TreeVPartCategory_AfterSelect(object sender, TreeViewEventArgs e)
{
try
{
//显示查询进度
ProgressBarFrm ProgFrm = new ProgressBarFrm();
ProgFrm.MaxNum = 100;
ProgFrm.ShowDialog();
CategoryName = e.Node.Text.ToString();//获取选中节点分类名称
if (CategoryName == "全部配件")
{
CategoryName = string.Empty;
}
string QueryWhere = QueryPartWhereCondition();//获取查询条件
if (CheckBGuarantyStock.Checked)
{
GetYuTongPartList(QueryWhere);//宇通三包库存查询
}
else
{
GetPartList(QueryWhere);//配件库存查询
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// 配件库存查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
string QueryWhere = QueryPartWhereCondition();//获取查询条件
//显示查询进度
ProgressBarFrm ProgFrm = new ProgressBarFrm();
ProgFrm.MaxNum = 100;
ProgFrm.ShowDialog();
if (CheckBGuarantyStock.Checked)
{
GetYuTongPartList(QueryWhere);//宇通三包库存查询
}
else
{
GetPartList(QueryWhere);//配件库存查询
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
try
{
string QueryStr = string.Empty;//查询条件
QueryStr += txtBusinUnitCode.Caption.Trim().ToString();
QueryStr += txtBusinUnitName.Caption.Trim().ToString();
QueryStr += txtContact.Caption.Trim().ToString();
QueryStr += txtTelephone.Caption.Trim().ToString();
//显示查询进度
ProgressBarFrm ProgFrm = new ProgressBarFrm();
ProgFrm.MaxNum = 100;
ProgFrm.ShowDialog();
BindSupData(); //获取供应商信息
BindCustData(); //获取客户信息
if (string.IsNullOrEmpty(QueryStr))
{
//获取所有客户和供应商信息
GetSupMsg();
GetCustMsg();
}
else
{
if (CustCounts == 0 && SuppCounts != 0)
{
GetSupMsg();//获取供应商信息
}
else if (SuppCounts == 0 && CustCounts != 0)
{
GetCustMsg(); //获取客户信息
}
else
{
//获取所有客户和供应商信息
GetSupMsg();
GetCustMsg();
}
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// 导入Excel与单据匹配字段的所有数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportExcelData_Click(object sender, EventArgs e)
{
try
{
if (CheckOutExcelField())//验证是否选择匹配字段
{
for (int i = 0; i < dgMatchList.Rows.Count; i++)
{
DataGridViewComboBoxCell dgComBox = (DataGridViewComboBoxCell)dgMatchList.Rows[i].Cells["ExcelField"];//转换为combox单元格
//获取Excel匹配的字段名
MatchFieldHTable.Add(dgMatchList.Rows[i].Cells["BillField"].Value.ToString(), dgComBox.EditedFormattedValue.ToString());
}
DialogResult = DialogResult.OK; //关闭当前对话框
ProgressBarFrm ProgFrm = new ProgressBarFrm();
ProgFrm.MaxNum = ExcelTable.Rows.Count; //获取最大导入记录行
ProgFrm.ShowDialog();
}
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
/// <summary>
/// 利用office组件读取Excel表格中配件信息
/// </summary>
/// <returns></returns>
//public static DataTable ImportExcelFile(string FilesPathName)
//{
// try
// {
// string ExcelFilePath = string.Empty;//存储Excel文件路径
// string ExcelConnStr = string.Empty;//读取Excel表格字符串
// DataTable ExcelTable = null;//获取Excel表格中的数据
// string SheetName = string.Empty;//Excel中表名
// string ExcelSql = string.Empty;//查询Excel表格
// ExcelFilePath = FilesPathName;//获取选定文件的路径名
// if (Path.GetExtension(ExcelFilePath) != ".xlsx" && Path.GetExtension(ExcelFilePath) != ".xls")
// {
// MessageBoxEx.Show("请您输入Excel格式的文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else if (ExcelFilePath == string.Empty)
// {
// MessageBoxEx.Show("请您选择要导入的配件信息文件", "提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
// }
// else
// {
// //HDR=YES代表Excel第一行是标题不作为数据使用，IMEX三种模式：0代表写入模式，1代表读取模式，2代表链接模式（支持同时读取和写入）
// ExcelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
// OleDbConnection DbCon = new OleDbConnection(ExcelConnStr);//打开数据源连接
// DbCon.Open();
// ExcelTable = DbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });//获取Excel表格中的数据
// SheetName = ExcelTable.Rows[0]["table_name"].ToString();//获取Excel表名称
// ExcelSql = "select * from [" + SheetName + "]";
// OleDbDataAdapter DbAdapter = new OleDbDataAdapter(ExcelSql, DbCon);//执行数据查询
// DataSet ds = new DataSet();
// DbAdapter.Fill(ds, SheetName);//填充数据集
// DbCon.Close();//关闭数据源连接
// ExcelTable = ds.Tables[0];
// }
// return ExcelTable;
// }
// catch (Exception ex)
// {
// MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
// return null;
// }
//}
/// <summary>
/// 利用NPOI创建Excel文件
/// </summary>
/// <param name="ExcelTable">excel表格名称</param>
/// <param name="SheetName">sheet表名</param>
public static void NPOIExportExcelFile(DataTable NPOIExcelTable, string SheetName)
{
try
{
string SaveExcelName = string.Empty;//保存的Excel文件名称
SaveFileDialog SFDialog = new SaveFileDialog();
SFDialog.DefaultExt = "xls";
SFDialog.Filter = "Excel文件(*.xls)|*.xls";
SFDialog.ShowDialog();
SFDialog.CheckPathExists = true;
SFDialog.CheckFileExists = true;
SaveExcelName = SFDialog.FileName;//获取保存的Excel文件名称
if (File.Exists(SaveExcelName))
{
if (IsExcelOpen(SaveExcelName))
{
File.Delete(SaveExcelName); //替换同名文件
}
else
{
return;
}
}
if (SaveExcelName.IndexOf(":") < 0)
{
return; //点击取消按钮返回
}
HSSFWorkbook WkBk = new HSSFWorkbook(); //创建工作表流
MemoryStream ms = new MemoryStream(); //创建支持内存的存储区流
HSSFSheet sht = (HSSFSheet)WkBk.CreateSheet(SheetName); //创建Excel表格
sht.CreateFreezePane(0, 1, 0, 1); //设置冻结首行
HSSFRow HeaderRow = (HSSFRow)sht.CreateRow(0); //创建标题行
int TotalCount = NPOIExcelTable.Rows.Count;
int rowRead = 0; //读取行数
ProgressBarFrm ProgBarFrm = new ProgressBarFrm();
ProgBarFrm.MaxNum = TotalCount; //获取最大记录行
ProgBarFrm.ShowDialog(); //显示进度条
for (int i = 0; i < NPOIExcelTable.Columns.Count; i++)
{
HeaderRow.CreateCell(i).SetCellValue(NPOIExcelTable.Columns[i].ColumnName.ToString()); //创建标题列
SetXlsHeaderStyle(HeaderRow, WkBk, i); //设置标题样式
sht.SetColumnWidth(i, 1000 * 5); //设置列宽
}
//创建Excel数据行项
for (int j = 0; j < NPOIExcelTable.Rows.Count; j++)
{
HSSFRow DatasRow = (HSSFRow)sht.CreateRow(j + 1); //创建数据行
for (int k = 0; k < NPOIExcelTable.Columns.Count; k++)
{ //填充Excel表数据
DatasRow.CreateCell(k).SetCellValue(NPOIExcelTable.Rows[j][k].ToString());
}
rowRead++; //自动增长行号
ProgressBarFrm.CurrentValue = rowRead; //传递当前值
Application.DoEvents(); //处理当前在消息队列中所有windows消息
}
//写入内存流数据
WkBk.Write(ms);
ms.Flush();//清空缓存
ms.Position = 0;
sht = null;
HeaderRow = null;
WkBk = null;
FileStream fs = new FileStream(SaveExcelName, FileMode.CreateNew, FileAccess.Write); //创建文件流
byte[] dataXls = ms.ToArray(); //把文件流转换为字节数组
fs.Write(dataXls, 0, dataXls.Length);
fs.Flush(); //清除缓存
fs.Close(); //关闭文件流
dataXls = null;
ms = null;
fs = null;
}
catch (Exception ex)
{
MessageBoxEx.Show(ex.Message, "异常提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
}
}
