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 = "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)
{
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);
}
}