private void btnSel_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel|*.xls|Excel|*.xlsx|All File|*.*";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string fName = openFileDialog.FileName;
ClsExcel clsExcel = ClsExcel.Instance();
string sSQL = @"
select *
from [Sheet1$]
";
DataTable dt = clsExcel.ExcelToDT(fName, sSQL, true);
DataColumn dc = new DataColumn();
dc.ColumnName = "bChoose";
dc.DataType = Type.GetType("System.Boolean");
dc.DefaultValue = false;
dt.Columns.Add(dc);
dc = new DataColumn();
dc.ColumnName = "bUsed";
dc.DataType = Type.GetType("System.Boolean");
dc.DefaultValue = false;
dt.Columns.Add(dc);
gridControl1.DataSource = dt;
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "xlsx|*.xlsx|xls|*.xls|All File|*.*";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string sSQL = "";
string fName = openFileDialog.FileName;
ClsExcel clsExcel = ClsExcel.Instance();
SetTxtNull();
sSQL = "select * from [PL$A4:B8]";
DataTable dtHead = clsExcel.ExcelToDT(fName, sSQL, true);
txtCurrency.Text = dtHead.Rows[0][1].ToString().Trim();
txtInvoiceNO.Text = dtHead.Rows[1][1].ToString().Trim();
dateEdit1.DateTime = BaseFunction.ReturnDate(dtHead.Rows[2][1]);
txtCompany.Text = dtHead.Rows[3][1].ToString().Trim();
sSQL = "select * from [PL$A11:N65535]";
DataTable dtParkingList = clsExcel.ExcelToDT(fName, sSQL, true);
for (int i = 0; i < dtParkingList.Columns.Count; i++)
{
dtParkingList.Columns[i].ColumnName = dtParkingList.Rows[0][i].ToString().Trim();
}
DataColumn dc = new DataColumn();
dc.ColumnName = "U8RDCode";
dtParkingList.Columns.Add(dc);
dc = new DataColumn();
dc.ColumnName = "Rds01ID";
dtParkingList.Columns.Add(dc);
dtParkingList.Rows.RemoveAt(1); //去除列表中的 标题
dtParkingList.Rows.RemoveAt(0); //去除列表中的 ATUP PARTS 这行数据
for (int i = dtParkingList.Rows.Count - 1; i >= 0; i--)
{
if (dtParkingList.Rows[i][0].ToString().Trim().ToUpper().StartsWith("CURRENCY"))
{
dtParkingList.Rows.RemoveAt(i);
break;
}
dtParkingList.Rows.RemoveAt(i);
}
for (int i = dtParkingList.Rows.Count - 1; i >= 0; i--)
{
if (dtParkingList.Rows[i][0].ToString().Trim() == "")
{
dtParkingList.Rows.RemoveAt(i);
}
}
for (int i = 0; i < dtParkingList.Columns.Count; i++)
{
string sColName = dtParkingList.Columns[i].ColumnName.ToString().Trim();
sColName = sColName.Replace(" ", "");
sColName = sColName.Replace("#", "");
sColName = sColName.Replace(".", "");
sColName = sColName.Replace("(", "");
sColName = sColName.Replace(")", "");
dtParkingList.Columns[i].ColumnName = sColName;
}
gridControlPL.DataSource = dtParkingList;
gridViewPL.BestFitColumns();
//sSQL = "select * from [IN$A5:B8]";
//DataTable dt_Head = clsExcel.ExcelToDT(fName, sSQL, true);
//txt_InvoiceNO.Text = dt_Head.Rows[0][1].ToString().Trim();
//dateEdit_1.DateTime = BaseFunction.ReturnDate(dt_Head.Rows[1][1]);
//txt_Company.Text = dt_Head.Rows[2][1].ToString().Trim();
sSQL = "select * from [IN$A11:I65535]";
DataTable dtInvoice = clsExcel.ExcelToDT(fName, sSQL, true);
for (int i = 0; i < dtInvoice.Columns.Count; i++)
{
dtInvoice.Columns[i].ColumnName = dtInvoice.Rows[0][i].ToString().Trim();
}
dtInvoice.Rows.RemoveAt(1); //去除列表中的 标题
dtInvoice.Rows.RemoveAt(0); //去除列表中的 ATUP PARTS 这行数据
for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
{
if (dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith("CURRENCY"))
{
dtInvoice.Rows.RemoveAt(i);
break;
}
dtInvoice.Rows.RemoveAt(i);
}
for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
{
if (dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith(""))
{
dtInvoice.Rows.RemoveAt(i);
break;
}
dtInvoice.Rows.RemoveAt(i);
}
for (int i = dtInvoice.Rows.Count - 1; i >= 0; i--)
{
if (dtInvoice.Rows[i][0].ToString().Trim() == "" || dtInvoice.Rows[i][0].ToString().Trim().ToUpper().StartsWith("TOTAL"))
{
dtInvoice.Rows.RemoveAt(i);
}
}
for (int i = 0; i < dtInvoice.Columns.Count; i++)
{
string sColName = dtInvoice.Columns[i].ColumnName.ToString().Trim();
sColName = sColName.Replace(" ", "");
sColName = sColName.Replace("#", "");
sColName = sColName.Replace(".", "");
sColName = sColName.Replace("(", "");
sColName = sColName.Replace(")", "");
sColName = sColName.Replace("\n", "");
sColName = sColName.Replace("\r", "");
sColName = sColName.Replace("UNITPRICE", "PRICEPERUNIT");
dtInvoice.Columns[i].ColumnName = sColName;
}
gridControlIN.DataSource = dtInvoice;
gridViewIN.BestFitColumns();
dateEdit1.DateTime = BaseFunction.ReturnDate(sLogDate);
}
catch (Exception ee)
{
SetTxtNull();
MessageBox.Show(ee.Message);
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel|*.xls|Excel|*.xlsx|All File|*.*";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string fName = openFileDialog.FileName;
ClsExcel clsExcel = ClsExcel.Instance();
string sSQL = @"
select *
from [Sheet1$]
";
DataTable dt = clsExcel.ExcelToDT(fName, sSQL, true);
for (int i = 0; i < dt.Columns.Count; i++)
{
string sCol = dt.Columns[i].ColumnName;
if (sCol.ToLower() == "CustomerCode".ToLower())
{
dt.Columns[i].ColumnName = "cCusCode";
}
if (sCol.ToLower() == "PartNo".ToLower())
{
dt.Columns[i].ColumnName = "cInvCode";
}
if (sCol.ToLower() == "StartDate".ToLower())
{
dt.Columns[i].ColumnName = "dtmStart";
}
if (sCol.ToLower() == "MAN-HOUR".ToLower())
{
dt.Columns[i].ColumnName = "MANHOUR";
}
}
DataColumn dc = new DataColumn();
dc.ColumnName = "sStatus";
dt.Columns.Add(dc);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["sStatus"] = "add";
}
gridControl1.DataSource = dt;
SetBtnEnable(false);
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
string sSQL = "";
try
{
if (dateEdit1.DateTime.Year != dateEdit2.DateTime.Year || dateEdit1.DateTime.Month != dateEdit2.DateTime.Month)
{
dateEdit1.Focus();
throw new Exception("日期必须在同一个年月");
}
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XML|*.xml|所有文件|*.*";
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() != DialogResult.OK)
{
return;
}
string fName = openFileDialog.FileName;
ClsExcel clsExcel = ClsExcel.Instance();
string tablename = "";
string sqltablename = "";
if (toolStripComboBox1.SelectedIndex == -1)
{
MessageBox.Show("请选择导入单据类型");
return;
}
switch (toolStripComboBox1.SelectedIndex)
{
case 0:
tablename = "View - General Ledger Entries $A2:R65536";
sqltablename = "_General Ledger Entries";
xtp0.Show();
break;
case 1:
tablename = "Posted Purchase Credit Memos$A2:J65536";
sqltablename = "_Purchase Credit Memos";
xtp1.Show();
break;
case 2:
tablename = "Posted Purchase Invoices$A2:M65536";
sqltablename = "_Purchase Invoices";
xtp2.Show();
break;
case 3:
tablename = "Posted Sales Credit Memos$A2:I65536";
sqltablename = "_Sales Credit Memos";
xtp3.Show();
break;
case 4:
tablename = "Posted Sales Invoices$A2:L65536";
sqltablename = "_Sales Invoices";
xtp4.Show();
break;
case 5:
tablename = "View - Bank Account Ledger Ent$A2:J65536";
sqltablename = "_Bank Account Ledger Entries";
xtp5.Show();
break;
}
DataTable dt = DbHelperXML.ReadFromXml(fName);
SqlConnection conn = new SqlConnection(Conn);
conn.Open();
//conn.ConnectionTimeout = 0;
SqlTransaction tran = conn.BeginTransaction();
try
{
//导入
int iCount = 0;
sSQL = "truncate table [" + sqltablename + "]";
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlClass sc = new SqlClass("[" + sqltablename + "]");
for (int j = 0; j < dt.Columns.Count; j++)
{
sc.ToString("[" + dt.Columns[j].ColumnName + "]", dt.Rows[i][dt.Columns[j].ColumnName].ToString().Replace("'", ""));
}
sSQL = sc.UpdateSql();
DbHelperSQL.ExecuteNonQuery(tran, CommandType.Text, sSQL);
iCount = iCount + 1;
}
//核对
switch (toolStripComboBox1.SelectedIndex)
{
case 0:
sSQL = "select a.*,null as 问题数据 from [" + sqltablename + "] a ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl0.DataSource = dt;
gridView0.OptionsBehavior.Editable = false;
gridView0.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView0.BestFitColumns();
break;
case 1:
sSQL = "select a.*,case when b.cVenCode is null then '用友未找到供应商' end as 问题数据 from [" + sqltablename + "] a left join Vendor b on a.[Buy-from Vendor No.]=b.cVenCode ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl1.DataSource = dt;
gridView1.OptionsBehavior.Editable = false;
gridView1.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView1.BestFitColumns();
break;
case 2:
sSQL = "select a.*,case when b.cVenCode is null then '用友未找到供应商' end as 问题数据 from [" + sqltablename + "] a left join Vendor b on a.[Buy-from Vendor No.]=b.cVenCode ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl2.DataSource = dt;
gridView2.OptionsBehavior.Editable = false;
gridView2.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView2.BestFitColumns();
break;
case 3:
sSQL = "select a.*,case when b.cCusCode is null then '用友未找到客户' end as 问题数据 from [" + sqltablename + "] a left join Customer b on a.[Sell-to Customer No.]=b.cCusCode ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl3.DataSource = dt;
gridView3.OptionsBehavior.Editable = false;
gridView3.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView3.BestFitColumns();
break;
case 4:
sSQL = "select a.*,case when b.cCusCode is null then '用友未找到客户' end as 问题数据 from [" + sqltablename + "] a left join Customer b on a.[Sell-to Customer No.]=b.cCusCode ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl4.DataSource = dt;
gridView4.OptionsBehavior.Editable = false;
gridView4.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView4.BestFitColumns();
break;
case 5:
sSQL = "select a.*,null as 问题数据 from [" + sqltablename + "] a ";
dt = DbHelperSQL.ExecuteDataset(tran, CommandType.Text, sSQL).Tables[0];
gridControl5.DataSource = dt;
gridView5.OptionsBehavior.Editable = false;
gridView5.Columns["Amount"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
gridView5.BestFitColumns();
break;
}
tran.Commit();
MessageBox.Show("导入成功，共" + iCount + "条");
}
catch (Exception ee)
{
tran.Rollback();
throw new Exception("导入失败，原因：" + ee.Message);
}
//DataColumn dc = new DataColumn();
//dc.ColumnName = "选择";
//dc.DataType = System.Type.GetType("System.Boolean");
//dt.Columns.Add(dc);
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// dt.Rows[i]["选择"] = true;
//}
//gridControl1.DataSource = dt;
//gridView1.BestFitColumns();
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
}