private void LoadMasterFile()
{
var provider = new ExcelProvider();
var sheet = this.shellViewModel.Sheets.SelectedItem;
if (sheet != null)
{
var schools = provider.Read(this.shellViewModel.MasterFilePath, sheet.Name);
var distinct = (from s in schools
orderby s.SchoolFullName
select s).DistinctBy(s => s.SchoolFullName);
this.shellViewModel.Schools.Clear();
this.shellViewModel.FilteredSchools.Clear();
foreach (var school in distinct)
{
var viewModel = new SchoolViewModel(school);
this.shellViewModel.Schools.Add(viewModel);
this.shellViewModel.FilteredSchools.Add(viewModel);
}
}
this.shellViewModel.ApplyFilter();
}
private void LoadFile(string fileName, string workSheetName = null)
{
var excel = new ExcelProvider(fileName, workSheetName);
Assert.AreEqual(5, excel.Columns.Count());
Assert.AreEqual(1, excel.Columns.First().Index);
Assert.AreEqual("A", excel.Columns.First().Header);
Assert.AreEqual("ID", excel.Columns.First().Name);
Assert.AreEqual(5, excel.Columns.Last().Index);
Assert.AreEqual("E", excel.Columns.Last().Header);
Assert.AreEqual("BirthDate", excel.Columns.Last().Name);
Assert.AreEqual(5, excel.Rows.Count());
Assert.AreEqual(5, excel.Rows.Last().Index);
Assert.AreEqual(5, excel.Rows.Last().Get <int>(1));
Assert.AreEqual(5, excel.Rows.Last().Get <int>("A"));
Assert.AreEqual(5, excel.Rows.Last().GetByName <int>("ID"));
Assert.AreEqual("Peter", excel.Rows.Last().Get <string>(2));
Assert.AreEqual("Peter", excel.Rows.Last().Get <string>("B"));
Assert.AreEqual("Peter", excel.Rows.Last().GetByName <string>("FirstName"));
Assert.AreEqual(DateTime.Parse("3/05/1979 0:00:00"), excel.Rows.Last().GetByName <DateTime>("BirthDate"));
}
public ExcelDataAccess(string path)
{
_Path = path;
_exProvider = ExcelProvider.Create(@path);
if (_exProvider == null)
MessageShow.FileDoesNotExist(path);
}
public override void Execute(object parameter)
{
var provider = new ExcelProvider();
var sheets = provider.ReadSchema(this.shellViewModel.MasterFilePath);
this.shellViewModel.Sheets.Clear();
foreach (var sheet in sheets)
{
this.shellViewModel.Sheets.Add(new SheetViewModel(sheet));
}
if (!string.IsNullOrEmpty(this.shellViewModel.SelectedSheetName))
{
foreach (var sheet in this.shellViewModel.Sheets)
{
if (sheet.Name == this.shellViewModel.SelectedSheetName)
{
this.shellViewModel.Sheets.SelectedItem = sheet;
}
}
}
this.shellViewModel.Sheets.EnsureSelected();
}
public DataSet GetDataFromExcel(string strFileName, out ArrayList al_error)
{
DataSet ds = null;
ExcelProvider.GetExcelData(strFileName, out ds, out al_error);
return(ds);
}
private void btnExport_Click(object sender, EventArgs e)
{
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel Files | *.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
{
var excelProvider = new ExcelProvider();
var fileBytes = excelProvider.ExportQuery("BuildingPMDebtor", FilterData(), new ExcelStyleSheet());
File.WriteAllBytes(sfd.FileName, fileBytes);
Process.Start(sfd.FileName);
// MessageBox.Show("Saved Succesfully", "Report", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private void btnExport_Click(object sender, EventArgs e)
{
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel Files | *.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
{
var excelProvider = new ExcelProvider();
var fileBytes = excelProvider.ExportQuery("Trustee Report List", _TrusteeReportResults.AsQueryable(), new ExcelStyleSheet());
File.WriteAllBytes(sfd.FileName, fileBytes);
Process.Start(sfd.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
public ActionResult <List <Booking> > GetSeed()
{
var xls = new ExcelProvider(_context);
var res = xls.ParseData();
return(null);
}
private void CreateOffer_btn_Click(object sender, RoutedEventArgs e)
{
ExcelProvider ex = new ExcelProvider();
ex.OpenExcelFile(kpFilePath, true);
ex.WriteTOcell("F11", DateTime.Today.ToString(), "");
ex.CloseWithoutSaving();
}
public static ExcelProvider Create(string filePath, string sheet)
{
ExcelProvider provider = new ExcelProvider();
provider.sheet = sheet;
provider.filePath = filePath;
return(provider);
}
public async Task <IActionResult> SearchExcel(string name)
{
var products = await SearchProducts(name);
ExcelProvider <ProductOutputModel> excel = new ExcelProvider <ProductOutputModel>();
var productsExcelStream = excel.CreateExcel(products);
return(File(productsExcelStream, "application/ms-excel", "Catalog.xlsx"));
}
static void Main(string[] args)
{
log4net.Config.XmlConfigurator.Configure();
string firstName, lastName, dob, lexisNexisID;
string filePath = @"yor directory path\your file name";
ExcelProvider provider = ExcelProvider.Create(filePath, "Sheet1");
foreach (ExcelRow row in (from x in provider select x))
{
Console.WriteLine("{0}", row.GetString(1));
static void Main(string[] args)
{
var excelProvider = new ExcelProvider();
var words = excelProvider.ReadColumn("C:\\Users\\StephanyHenrique\\Documents\\planilha2.xls");
var googleProvider = new GoogleProvider();
foreach (var word in words)
{
googleProvider.SaveAudio(word);
}
}
public void ShouldParseExact()
{
using (var excelProvider = new ExcelProvider(@"D:\ExcelParser.xlsx"))
{
var customers = excelProvider.ParseExact<Customer>(x => x.Cells["Id"].Value.Equals("1"));
foreach (var customer in customers)
{
//Do Something
}
}
}
public void ShouldParseExact()
{
using (var excelProvider = new ExcelProvider(@"D:\ExcelParser.xlsx"))
{
var customers = excelProvider.ParseExact <Customer>(x => x.Cells["Id"].Value.Equals("1"));
foreach (var customer in customers)
{
//Do Something
}
}
}
public void TestCreateExcel()
{
ExcelProvider <Product> excel = new ExcelProvider <Product>();
// Empty list test
var mem = excel.CreateExcel(new List <Product>());
ValidateExcel(mem, true);
// Normal list
var products2 = new List <Product>
{
new Product
{
Id = 1,
Name = "Test",
LastUpdate = DateTime.Now,
Photo = "test.photo",
Price = 1
},
new Product
{
Id = 2,
Name = "Test2",
LastUpdate = DateTime.Now,
Photo = "test2.photo",
Price = 2
}
};
var mem2 = excel.CreateExcel(products2);
// Different list
var sheet = ValidateExcel(mem2);
Assert.AreEqual(sheet.Cell(2, 1).Value, products2[0].Name);
var testList = new List <TestClass>
{
new TestClass
{
Byte = 2,
Int = 3,
Long = 4
}
};
ExcelProvider <TestClass> excelTestClass = new ExcelProvider <TestClass>();
var mem3 = excelTestClass.CreateExcel(testList);
var sheet3 = ValidateExcel(mem3);
Assert.AreEqual(Convert.ToInt32(sheet3.Cell(2, 1).Value), testList[0].Int);
}
private void ToXlsFile(ListView lv_items, string strFileName, string strPageName)
{
Cursor.Current = Cursors.WaitCursor;
DataTable tab = new DataTable(strPageName);
for (int i = 1; i <= lv_items.Columns.Count; i++)
{
System.Type tag = lv_items.Columns[i - 1].Tag as System.Type;
tab.Columns.Add(lv_items.Columns[i - 1].Text, tag);
}
foreach (ListViewItem item in lv_items.Items)
{
DataRow row = tab.NewRow();
for (int j = 1; j <= lv_items.Columns.Count; j++)
{
System.Type type2 = lv_items.Columns[j - 1].Tag as System.Type;
if (j == 1)
{
if (string.IsNullOrEmpty(item.Text))
{
row[lv_items.Columns[j - 1].Text] = DBNull.Value;
}
else if (type2 == typeof(decimal))
{
row[lv_items.Columns[j - 1].Text] = Convert.ToDecimal(item.Text);
}
else
{
row[lv_items.Columns[j - 1].Text] = item.Text;
}
}
else if (string.IsNullOrEmpty(item.SubItems[j - 1].Text))
{
row[lv_items.Columns[j - 1].Text] = DBNull.Value;
}
else if (type2 == typeof(decimal))
{
row[lv_items.Columns[j - 1].Text] = Convert.ToDecimal(item.SubItems[j - 1].Text);
}
else
{
row[lv_items.Columns[j - 1].Text] = item.SubItems[j - 1].Text;
}
}
tab.Rows.Add(row);
}
ExcelProvider.SetExcelData(strFileName, tab);
Cursor.Current = Cursors.Default;
}
private void GetExcelImportTemplate()
{
string excelFileTemplate = ModelInfoSelected.ModelName + "_Template_" + DateTime.Now.ToString("yyyyMMddHHmmssfff");
var dialog = new SaveFileDialog()
{
FileName = excelFileTemplate,
Filter = "Excel Files(*.xlsx)|*.xlsx|All(*.*)|*"
};
if (dialog.ShowDialog() == true)
{
DataTable tempTable = (new DataTable()).FromHeaders(ModelInfoSelected.ModelProperties);
bool exportResult = ExcelProvider.Export(tempTable, dialog.FileName);
BBCodeBlock codeBlock = new BBCodeBlock();
if (exportResult)
{
codeBlock.BBCode = string.Format("Succesfully imported excel {0} model data template.\nFile: {1}.\n[color=Green]Open file?[/color]", ModelInfoSelected.ModelName, dialog.FileName);
var dlg = new ModernDialog
{
Title = "Import Excel Template",
Content = codeBlock
};
dlg.Buttons = new[] { dlg.YesButton, dlg.NoButton };
bool?result = dlg.ShowDialog();
if (result != null && result.Value)
{
Process.Start(dialog.FileName);
}
}
else
{
codeBlock.BBCode = string.Format("Error importing excel {0} model data template.\nFile: {1}.\n[color=Red]Please try again.[/color]", ModelInfoSelected.ModelName, dialog.FileName);
var dlg = new ModernDialog
{
Title = "Import Excel Template",
Content = codeBlock
};
dlg.Buttons = new[] { dlg.OkButton };
dlg.ShowDialog();
}
}
}
private void LoadDataBtn_Click_1(object sender, EventArgs e)
{
DataProvider provider = new ExcelProvider();
data = provider.Load(sourceFilePath);
if (data != null)
{
ColumnChartBtn.Enabled = true;
LineChartBtn.Enabled = true;
CurveChartBtn.Enabled = true;
ChangeStyleBtn.Enabled = true;
AddChartBtn.Enabled = true;
PieChartbtn.Enabled = true;
AddRowDataBtn.Enabled = true;
AddColumnDataBtn.Enabled = true;
TransposeMatrixBtn.Enabled = true;
}
}
private void ImportDataFromExcelFile()
{
var dialog = new OpenFileDialog()
{
Filter = "Excel Files(*.xlsx)|*.xlsx|All(*.*)|*"
};
if (dialog.ShowDialog() == true)
{
DataTable tempTable;
bool importResult = ExcelProvider.Import(out tempTable, dialog.FileName);
BBCodeBlock codeBlock = new BBCodeBlock();
if (importResult)
{
codeBlock.BBCode = string.Format("Succesfully imported excel {0} model data.\nFile: {1}.\n[color=Green]Load data?[/color]", ModelInfoSelected.ModelName, dialog.FileName);
var dlg = new ModernDialog
{
Title = "Import Excel Data",
Content = codeBlock
};
dlg.Buttons = new[] { dlg.YesButton, dlg.NoButton };
bool?result = dlg.ShowDialog();
if (result != null && result.Value)
{
ModuleItems = tempTable;
}
}
else
{
codeBlock.BBCode = string.Format("Error importing excel {0} model data.\nFile: {1}.\n[color=Red]Please try again.[/color]", ModelInfoSelected.ModelName, dialog.FileName);
var dlg = new ModernDialog
{
Title = "Import Excel Data",
Content = codeBlock
};
dlg.Buttons = new[] { dlg.OkButton };
dlg.ShowDialog();
}
}
}
public override Task WriteResponseBodyAsync(OutputFormatterWriteContext context)
{
try
{
var response = context.HttpContext.Response;
string xml = GetXMLFromObject(context.Object);
var accept = context.HttpContext.Request.Headers["Accept"].FirstOrDefault();
response.ContentType = accept;
var templateFile = context.HttpContext.Request.Headers["Template"].FirstOrDefault();
ExcelProvider.ConvertToStream(templateFile, xml, response.Body);
return(Task.CompletedTask);
}
catch
{
throw;
}
}
public ActionResult GetExport(int count = 0)
{
var productFaker = new Faker <Product>()
.CustomInstantiator(f => new Product())
.RuleFor(p => p.Id, f => f.IndexFaker)
.RuleFor(p => p.Ean, f => f.Commerce.Ean13())
.RuleFor(p => p.Name, f => f.Commerce.ProductName())
.RuleFor(p => p.Description, f => f.Lorem.Sentence(f.Random.Int(5, 20)))
.RuleFor(p => p.Brand, f => f.Company.CompanyName())
.RuleFor(p => p.Category, f => f.Commerce.Categories(1).First())
.RuleFor(p => p.Price, f => f.Commerce.Price(1, 1000, 2, "€"))
.RuleFor(p => p.Quantity, f => f.Random.Int(0, 1000))
.RuleFor(p => p.Rating, f => f.Random.Float(0, 1))
.RuleFor(p => p.ReleaseDate, f => f.Date.Past(2));
var excelProvider = new ExcelProvider();
excelProvider.Generate(productFaker.Generate(count), null, "Sheet 1");
return(File(excelProvider.File, "application/octet-stream"));
}
private void btnExport_Click(object sender, EventArgs e)
{
using (SaveFileDialog sfd = new SaveFileDialog())
{
sfd.Filter = "Excel Files | *.xlsx";
if (sfd.ShowDialog() == DialogResult.OK)
{
try
{
if (sfd.FileName != "" && sfd.FileName.EndsWith(".xlsx"))
{
var excelProvider = new ExcelProvider();
var results = _AllResults.Select(a => new TransactionSearchModel
{
BuildingPath = a.BuildingPath,
TransactionDate = a.TransactionDate,
AccountNumber = a.AccountNumber,
LinkAccount = (a.LinkAccount == "\0\0\0\0\0\0\0") ? string.Empty : a.LinkAccount, //Pastel handles null values as \0\0\0\0\0\0\0
Reference = a.Refrence,
Description = a.Description,
Amount = a.Amount
}).AsQueryable();
var fileBytes = excelProvider.ExportQuery("Transaction Search", results, new ExcelStyleSheet());
File.WriteAllBytes(sfd.FileName, fileBytes);
Process.Start(sfd.FileName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Report Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
private static void Main(string[] args)
{
var e = new ExcelProvider();
e.Read(OdsFile, "KiTa");
var data = File.ReadAllBytes(CsvFile);
var lines = File.ReadAllLines(CsvFile);
using (var reader = new StreamReader(CsvFile, true))
{
reader.Read();
Console.WriteLine(reader.CurrentEncoding);
}
var c = new CsvProvider();
c.Read(CsvFile);
}
public void ToExcel(string url, string name, string stateName, string columnsOverride, bool?labelColumns, string xlsName = "", string controllerAssembly = "Concentrator.ui.Management.Controllers")
{
if (string.IsNullOrEmpty(controllerAssembly))
{
controllerAssembly = "Concentrator.ui.Management.Controllers";
}
var grid = new System.Web.UI.WebControls.GridView();
var addExtraColumns = labelColumns ?? false;
if (string.IsNullOrEmpty(xlsName))
{
xlsName = name;
}
var jsonData = (((JsonResult)GetDataSource(url, controllerAssembly)).Data);
var data = jsonData.GetType().GetProperty("results").GetValue(jsonData, null);
DatabaseStateColumnProvider provider = new DatabaseStateColumnProvider();
var columns = provider.GetColumnDefinitions(Client.User.UserID, stateName, GetUnitOfWork());
if (columnsOverride != null)
{
string[] values = columnsOverride.Split(',');
foreach (var item in values)
{
var stateColumns = columns.Where(c => columnsOverride.Contains(c.Header)).ToList();
columns = stateColumns;
}
}
if (addExtraColumns)
{
DefaultColumnDefinition fromDateColumn = new DefaultColumnDefinition()
{
DataIndex = "fromDate", Header = "FromDate", Hidden = false, Width = 100
};
columns.Add(fromDateColumn);
DefaultColumnDefinition toDateColumn = new DefaultColumnDefinition()
{
DataIndex = "toDate", Header = "toDate", Hidden = false, Width = 100
};
columns.Add(toDateColumn);
DefaultColumnDefinition labelColumn = new DefaultColumnDefinition()
{
DataIndex = "Label", Header = "Label", Hidden = false, Width = 100
};
columns.Add(labelColumn);
DefaultColumnDefinition priceColumn = new DefaultColumnDefinition()
{
DataIndex = "Price", Header = "Price", Hidden = false, Width = 100
};
columns.Add(priceColumn);
DefaultColumnDefinition productIDColumn = new DefaultColumnDefinition()
{
DataIndex = "ProductID", Header = "ProductID", Hidden = false, Width = 100
};
columns.Add(productIDColumn);
DefaultColumnDefinition vendorIDColumn = new DefaultColumnDefinition()
{
DataIndex = "VendorID", Header = "VendorID", Hidden = false, Width = 100
};
columns.Add(vendorIDColumn);
DefaultColumnDefinition connectorIDColumn = new DefaultColumnDefinition()
{
DataIndex = "ConnectorID", Header = "ConnectorID", Hidden = false, Width = 100
};
columns.Add(connectorIDColumn);
}
var excelProvider = new ExcelProvider(columns, data, xlsName);
var excel = excelProvider.GetExcelDocument();
Response.Clear();
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", name));
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(excel);
Response.End();
}
public void inputpromotionpricelistdetail(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF2 inf = (datainportF2)obj;
string filename = inf.filename;
string programe = inf.programe.Trim();
// string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_KApromotinprice Where tbl_KApromotinprice.programId = '" + programe + "'");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
//list vn
var listvn = new List <string>();
listvn = (from tbl_karegion in dc.tbl_karegions
select tbl_karegion.Region.Trim()).ToList();
listvn.Add("VN");
//list vn
// listvn.Contains("dog");
// lisKanuber
var listkeyaccount = new List <string>();
var listkeyaccountrs = (from tbl_ka_prCustomer in dc.tbl_ka_prCustomers
group tbl_ka_prCustomer.KeyAccount by tbl_ka_prCustomer.KeyAccount into h
select h.Key);
foreach (var item in listkeyaccountrs)
{
listkeyaccount.Add(((int)item).ToString());
}
// listvn.Add("VN");
//liskanumber
// listsaledistric
var listsaledistric = new List <string>();
listsaledistric = (from tbl_ka_prCustomer in dc.tbl_ka_prCustomers
group tbl_ka_prCustomer.Salesdistrict by tbl_ka_prCustomer.Salesdistrict into g
select g.Key.Trim()).ToList();
// listvn.Add("VN");
//listsaledistric
// listsafuction
var listfuction = new List <string>();
listfuction = (from tbl_KAlistpricefunction in dc.tbl_KAlistpricefunctions
group tbl_KAlistpricefunction.function by tbl_KAlistpricefunction.function into g
select g.Key.Trim()).ToList();
// listvn.Add("VN");
//listsafuction
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
DataTable batable = new DataTable();
batable.Columns.Add("product_code", typeof(string));
batable.Columns.Add("product_group", typeof(string));
batable.Columns.Add("percent_amount", typeof(double));
batable.Columns.Add("amount", typeof(double));
batable.Columns.Add("customerid", typeof(double));
batable.Columns.Add("keyaccount", typeof(double));
batable.Columns.Add("sales_region", typeof(string));
batable.Columns.Add("saledistrict", typeof(string));
batable.Columns.Add("programId", typeof(string));
batable.Columns.Add("fromdate", typeof(DateTime));
batable.Columns.Add("todate", typeof(DateTime));
batable.Columns.Add("row", typeof(int));
string sales_region = "0";
double keyaccount = 0;
string saledistrict = "0";
double customerid = 0;
//string product_group = "0";
//double percent_amount = 0;
//double amount = 0;
int product_codeid = 0;
// int product_nameid = 0;
int product_groupid = 0;
// int product_groupnameid = 0;
// int percent_amountid = 0;
int amountid = 0;
int unitid = 0;
int UoMid = 0;
//int customeridid = 0;
//int keyaccountid = 0;
int sales_regionid = 0;
//int saledistrictid = 0;
int colid = 0;
int fromdateid = 0;
int todateid = 0;
// int rowif = 0;
int headindex = 0;
bool grouproduct = false;
double penctamount = 0;
double amount = 0;
for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++)
{
headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
if (value != null)
{
#region setcolum
if (value.Trim() == "CnTy")
{
sales_regionid = columid;
headindex = 0;
}
if (value.Trim() == "Material")
{
// product_nameid = columid;
colid = product_codeid;
product_codeid = columid;
grouproduct = false;
headindex = 0;
}
if (value.Trim() == "Material")
{
if (colid < columid && colid != 0)
{
product_codeid = colid;
headindex = 0;
grouproduct = false;
}
}
if (value.Trim() == "Matl Group")
{
colid = product_groupid;
product_groupid = columid;
// product_groupnameid = columid;
grouproduct = true;
headindex = 0;
}
if (value.Trim() == "Matl Group")
{
if (colid < columid && colid != 0)
{
product_groupid = colid;
headindex = 0;
grouproduct = true;
}
}
if (value.Trim() == "Amount")
{
amountid = columid;
headindex = 0;
}
if (value.Trim() == "Unit")
{
unitid = columid;
headindex = 0;
}
if (value.Trim() == "UoM")
{
UoMid = columid;
headindex = 0;
}
if (value.Trim() == "Valid From")
{
fromdateid = columid;
headindex = 0;
}
if (value.Trim() == "Valid to")
{
todateid = columid;
headindex = 0;
}
#endregion
// view basetable
}
//------------
}// forcolum
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string sales_regionvalue = sourceData.Rows[rowid][sales_regionid].ToString();
if (headindex != 0 && sales_regionvalue != "" && listvn.Contains(sales_regionvalue.Trim())) // trong nhoms fuction
{
sales_region = sales_regionvalue;
keyaccount = 0;
}
if (headindex != 0 && sales_regionvalue != "" && listkeyaccount.Contains(sales_regionvalue)) // trong nhoms fuction
{
// sales_region = sales_regionvalue;
keyaccount = double.Parse(sales_regionvalue);
}
if (headindex != 0 && sales_regionvalue != "" && listsaledistric.Contains(sales_regionvalue.Trim())) // trong nhoms fuction
{
sales_region = "0";
keyaccount = 0;
saledistrict = sales_regionvalue;
}
if (headindex != 0 && sales_regionvalue != "" && sales_regionvalue.Trim().Length >= 8 && Utils.IsValidnumber(sales_regionvalue)) // trong nhoms fuction
{
sales_region = "0";
keyaccount = 0;
saledistrict = "0";
customerid = double.Parse(sales_regionvalue.ToString());
}
if (headindex != 0 && sales_regionvalue != "" && listfuction.Contains(sales_regionvalue.Trim())) // trong nhoms fuction
{
DataRow dr = batable.NewRow();
dr["sales_region"] = sales_region.Trim();
dr["programId"] = programe;// sourceData.Rows[rowid][product_codeid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
// dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial);
dr["keyaccount"] = keyaccount;
dr["saledistrict"] = saledistrict.ToString(); // sourceData.Rows[rowid][columunit].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columunit);
dr["customerid"] = customerid; //sourceData.Rows[rowid][columname].ToString().Trim();//Utils.GetValueOfCellInExcel(worksheet, rowid, columname);
if (sourceData.Rows[rowid][product_codeid] != null && sourceData.Rows[rowid][product_codeid].ToString() != "" && grouproduct == false)
{
dr["product_code"] = sourceData.Rows[rowid][product_codeid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
dr["product_group"] = "0";
}
else
{
dr["product_code"] = "0";
}
if (sourceData.Rows[rowid][product_groupid] != null && sourceData.Rows[rowid][product_groupid].ToString() != "" && grouproduct == true)
{
dr["product_group"] = sourceData.Rows[rowid][product_groupid].ToString().Trim();// Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
dr["product_code"] = "0";
}
else
{
dr["product_group"] = "0";
}
dr["row"] = rowid;// double.Parse(sourceData.Rows[rowid][columpamount].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
if (sourceData.Rows[rowid][unitid].ToString().Trim() == "%")
{
// dr["percent_amount"] = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
// dr["amount"] = 0;
penctamount = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);;
amount = 0;
dr["percent_amount"] = penctamount;
dr["amount"] = amount;
}
else
{
penctamount = 0;
amount = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);;
dr["percent_amount"] = penctamount;
dr["amount"] = amount;
// dr["amount"] = double.Parse(sourceData.Rows[rowid][amountid].ToString());// Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
// dr["percent_amount"] = 0;
}
dr["fromdate"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][fromdateid].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From);
dr["todate"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][todateid].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
if (penctamount + amount != 0)
{
batable.Rows.Add(dr);
}
}
#endregion
} //fro row
//conpy to server
string destConnString = Utils.getConnectionstr();
//adapter.FillSchema(sourceData, SchemaType.Source);
//sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
//sourceData.Columns["Billed Qty"].DataType = typeof(float);
//sourceData.Columns["Cond Value"].DataType = typeof(float);
//sourceData.Columns["Sales Org"].DataType = typeof(string);
//sourceData.Columns["Cust Name"].DataType = typeof(string);
//sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
//sourceData.Columns["Mat Group"].DataType = typeof(string);
//sourceData.Columns["Mat Group Text"].DataType = typeof(string);
//sourceData.Columns["UoM"].DataType = typeof(string);
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tbl_KApromotinprice";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("product_code", "product_code");
bulkCopy.ColumnMappings.Add("product_group", "product_group");
// bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme");
bulkCopy.ColumnMappings.Add("percent_amount", "percent_amount");
bulkCopy.ColumnMappings.Add("amount", "amount");
bulkCopy.ColumnMappings.Add("customerid", "customerid");
bulkCopy.ColumnMappings.Add("keyaccount", "keyaccount");
bulkCopy.ColumnMappings.Add("sales_region", "sales_region");
bulkCopy.ColumnMappings.Add("saledistrict", "saledistrict");
bulkCopy.ColumnMappings.Add("programId", "programId");
bulkCopy.ColumnMappings.Add("fromdate", "fromdate");
bulkCopy.ColumnMappings.Add("todate", "todate");
bulkCopy.ColumnMappings.Add("row", "row");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
//copy to server
// string connection_string = Utils.getConnectionstr();
// LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// var typeffmain = typeof(tbl_KAbaseprice);
// var typeffsub = typeof(tbl_KAbaseprice);
// VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
// inputcdata1.ShowDialog();
// View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
// TB.ShowDialog();
// }
}
public void inputbasepricelistdetail(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// string filename = theDialog.FileName.ToString();
// string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
dc.ExecuteCommand("DELETE FROM tbl_KAbaseprice");
// dc.tblFBL5Nnewthisperiods.DeleteAllOnSubmit(rsthisperiod);
dc.CommandTimeout = 0;
dc.SubmitChanges();
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
DataTable batable = new DataTable();
batable.Columns.Add("PriceList", typeof(string));
batable.Columns.Add("Material", typeof(string));
batable.Columns.Add("MaterialNAme", typeof(string));
batable.Columns.Add("Amount", typeof(double));
batable.Columns.Add("Unit", typeof(string));
batable.Columns.Add("UoM", typeof(string));
batable.Columns.Add("Valid_From", typeof(DateTime));
batable.Columns.Add("Valid_to", typeof(DateTime));
string Pricelist = "";
int columpricelist = 0;
int columpmaterial = 0;
int columname = 0;
int columpamount = 0;
int columunit = 0;
int columUoM = 0;
int columValid_From = 0;
int columValid_to = 0;
int headindex = 0;
for (int rowid = 0; rowid < sourceData.Rows.Count; rowid++)
{
headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
if (value != null)
{
#region setcolum
if (value.Trim() == "CnTy")
{
columpricelist = columid;
headindex = 0;
}
if (value.Trim() == "Material")
{
if (columname == 0)
{
columpmaterial = columid;
headindex = 0;
}
}
if (value.Trim() == "Material")
{
if (columpmaterial != 0)
{
columname = columid;
headindex = 0;
}
}
if (value.Trim() == "Amount")
{
columpamount = columid;
headindex = 0;
}
if (value.Trim() == "Unit")
{
columunit = columid;
headindex = 0;
}
if (value.Trim() == "UoM")
{
columUoM = columid;
headindex = 0;
}
if (value.Trim() == "Valid From")
{
columValid_From = columid;
headindex = 0;
}
if (value.Trim() == "Valid to")
{
columValid_to = columid;
headindex = 0;
}
#endregion
// view basetable
}
//------------
} // colum
#region setvalue of pricelist
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string valuepricelist = sourceData.Rows[rowid][columpricelist].ToString();
if (headindex != 0 && valuepricelist != "" && valuepricelist != "YPR0")
{
Pricelist = valuepricelist;
}
if (headindex != 0 && valuepricelist == "YPR0")
{
DataRow dr = batable.NewRow();
dr["PriceList"] = Pricelist.Trim();
// dr["Material"] = Utils.GetValueOfCellInExcel(worksheet, rowid, columpmaterial);
dr["Material"] = sourceData.Rows[rowid][columpmaterial].ToString().Trim();
dr["MaterialNAme"] = sourceData.Rows[rowid][columname].ToString().Trim(); //Utils.GetValueOfCellInExcel(worksheet, rowid, columname);
dr["Amount"] = double.Parse(sourceData.Rows[rowid][columpamount].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columpamount);
dr["Unit"] = sourceData.Rows[rowid][columunit].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columunit);
dr["UoM"] = sourceData.Rows[rowid][columUoM].ToString().Trim(); // Utils.GetValueOfCellInExcel(worksheet, rowid, columUoM);
dr["Valid_From"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_From].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_From);
dr["Valid_to"] = Utils.chageExceldatetoData(sourceData.Rows[rowid][columValid_to].ToString()); // Utils.GetValueOfCellInExcel(worksheet, rowid, columValid_to);
batable.Rows.Add(dr);
}
#endregion
}// row
//conpy to server
string destConnString = Utils.getConnectionstr();
//adapter.FillSchema(sourceData, SchemaType.Source);
//sourceData.Columns["Posting Date"].DataType = typeof(DateTime);
//sourceData.Columns["Invoice Doc Nr"].DataType = typeof(float);
//sourceData.Columns["Billed Qty"].DataType = typeof(float);
//sourceData.Columns["Cond Value"].DataType = typeof(float);
//sourceData.Columns["Sales Org"].DataType = typeof(string);
//sourceData.Columns["Cust Name"].DataType = typeof(string);
//sourceData.Columns["Outbound Delivery"].DataType = typeof(string);
//sourceData.Columns["Mat Group"].DataType = typeof(string);
//sourceData.Columns["Mat Group Text"].DataType = typeof(string);
//sourceData.Columns["UoM"].DataType = typeof(string);
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tbl_KAbaseprice";
// Write from the source to the destination.
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.ColumnMappings.Add("PriceList", "PriceList");
bulkCopy.ColumnMappings.Add("Material", "Material");
bulkCopy.ColumnMappings.Add("MaterialNAme", "MaterialNAme");
bulkCopy.ColumnMappings.Add("Amount", "Amount");
bulkCopy.ColumnMappings.Add("Unit", "Unit");
bulkCopy.ColumnMappings.Add("UoM", "UoM");
bulkCopy.ColumnMappings.Add("Valid_From", "[Valid From]");
bulkCopy.ColumnMappings.Add("Valid_to", "[Valid to]");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
//bulkCopy.ColumnMappings.Add("Valid_to", "Valid_to");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
//copy to server
// string connection_string = Utils.getConnectionstr();
// LinqtoSQLDataContext dc = new LinqtoSQLDataContext(connection_string);
// var typeffmain = typeof(tbl_KAbaseprice);
// var typeffsub = typeof(tbl_KAbaseprice);
// VInputchange inputcdata1 = new VInputchange("", "Base price list", dc, "tbl_KAbaseprice", "tbl_KAbaseprice", typeffmain, typeffsub, "id", "id", "");
// inputcdata1.ShowDialog();
// View.Viewdatatable TB = new View.Viewdatatable(batable, "lIST DATA");
// TB.ShowDialog();
// }
}
private void importsexcel2(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
edlpinput_ctrl md = new edlpinput_ctrl();
bool kq = md.deleteedlp();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("Soldto", typeof(double));
batable.Columns.Add("SalesOrg", typeof(string));
batable.Columns.Add("CustName", typeof(string));
batable.Columns.Add("InvoiceDocNr", typeof(double));
batable.Columns.Add("OutboundDelivery", typeof(string));
batable.Columns.Add("MatNumber", typeof(string));
batable.Columns.Add("MatText", typeof(string));
batable.Columns.Add("BilledQty", typeof(double));
batable.Columns.Add("CondValue", typeof(double));
batable.Columns.Add("MatGroup", typeof(string));
batable.Columns.Add("MatGroupText", typeof(string));
batable.Columns.Add("UoM", typeof(string));
int Soldto = -1;
int SalesOrg = -1;
int CustName = -1;
int InvoiceDocNr = -1;
int OutboundDelivery = -1;
int MatNumber = -1;
int MatText = -1;
int BilledQty = -1;
int CondValue = -1;
int MatGroup = -1;
int MatGroupText = -1;
int UoM = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
//OleDbCommand command = new OleDbCommand(
// @"SELECT [], [],[],[],
// [], [], [],[],
// [], [], [], [] FROM [Sheet1$]
// WHERE (([Invoice Doc Nr] is not null ) AND ([Sales Org]<> ''))", conn);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("Sold-to"))
{
Soldto = columid;
// headindex = rowid;
}
if (value.Trim() == ("Sales Org"))
{
SalesOrg = columid;
// headindex = 0;
}
if (value.Trim() == ("Cust Name"))
{
CustName = columid;
// headindex = 0;
}
if (value.Trim() == ("Invoice Doc Nr"))
{
InvoiceDocNr = columid;
// headindex = 0;
}
if (value.Trim() == ("Outbound Delivery"))
{
OutboundDelivery = columid;
}
if (value.Trim() == ("Mat Number"))
{
MatNumber = columid;
}
if (value.Trim() == ("Mat Text"))
{
MatText = columid;
}
if (value.Trim() == ("Billed Qty"))
{
BilledQty = columid;
}
if (value.Trim() == ("Cond Value"))
{
CondValue = columid;
}
if (value.Trim() == ("Mat Group"))
{
MatGroup = columid;
}
if (value.Trim() == ("Mat Group Text"))
{
MatGroupText = columid;
}
if (value.Trim() == ("UoM"))
{
UoM = columid;
}
}
#endregion
} // colum
} // roww off heatder
#region
if (Soldto == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Sold to ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SalesOrg == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Sales Org", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CustName == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Cust Name", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (InvoiceDocNr == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Invoice Doc Nr ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (OutboundDelivery == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Outbound Delivery", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatNumber == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Mat Number ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatText == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Mat Text", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BilledQty == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Billed Qty ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (CondValue == -1)
{
MessageBox.Show("Dữ liệu thiếu cột CondValue ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroup == -1)
{
MessageBox.Show("Dữ liệu thiếu cột MatGroup", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MatGroupText == -1)
{
MessageBox.Show("Dữ liệu thiếu cột MatGroupText", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (UoM == -1)
{
MessageBox.Show("Dữ liệu thiếu cột UoM", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
#endregion
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string InvoiceDocNrv = sourceData.Rows[rowixd][InvoiceDocNr].ToString();
if (InvoiceDocNrv != "" && Utils.IsValidnumber(InvoiceDocNrv))
{
if (double.Parse(InvoiceDocNrv) > 0)
{
DataRow dr = batable.NewRow();
dr["Soldto"] = double.Parse(sourceData.Rows[rowixd][Soldto].ToString());
dr["SalesOrg"] = sourceData.Rows[rowixd][SalesOrg].ToString().Trim();
dr["CustName"] = sourceData.Rows[rowixd][CustName].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][InvoiceDocNr].ToString()))
{
dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
}
dr["InvoiceDocNr"] = double.Parse(sourceData.Rows[rowixd][InvoiceDocNr].ToString());
dr["OutboundDelivery"] = sourceData.Rows[rowixd][OutboundDelivery].ToString().Trim();
dr["MatNumber"] = sourceData.Rows[rowixd][MatNumber].ToString().Trim();
dr["MatText"] = sourceData.Rows[rowixd][MatText].ToString().Trim();
if (Utils.IsValidnumber(sourceData.Rows[rowixd][BilledQty].ToString()))
{
dr["BilledQty"] = double.Parse(sourceData.Rows[rowixd][BilledQty].ToString());
}
if (Utils.IsValidnumber(sourceData.Rows[rowixd][CondValue].ToString()))
{
dr["CondValue"] = double.Parse(sourceData.Rows[rowixd][CondValue].ToString());
}
dr["MatGroup"] = sourceData.Rows[rowixd][MatGroup].ToString().Trim();
dr["MatGroupText"] = sourceData.Rows[rowixd][MatGroupText].ToString().Trim();
dr["UoM"] = sourceData.Rows[rowixd][UoM].ToString().Trim();
batable.Rows.Add(dr);
}
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tblEDLP";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("[Soldto]", "[Sold-to]");
bulkCopy.ColumnMappings.Add("[SalesOrg]", "[Sales Org]");
bulkCopy.ColumnMappings.Add("[CustName]", "[Cust Name]");
bulkCopy.ColumnMappings.Add("[InvoiceDocNr]", "[Invoice Doc Nr]");
bulkCopy.ColumnMappings.Add("[OutboundDelivery]", "[Outbound Delivery]");
bulkCopy.ColumnMappings.Add("[MatNumber]", "[Mat Number]");
bulkCopy.ColumnMappings.Add("[MatText]", "[Mat Text]");
bulkCopy.ColumnMappings.Add("[BilledQty]", "[Billed Qty]");
bulkCopy.ColumnMappings.Add("[CondValue]", "[Cond Value]");
bulkCopy.ColumnMappings.Add("[MatGroup]", "[Mat Group]");
bulkCopy.ColumnMappings.Add("[MatGroupText]", "[Mat Group Text]");
bulkCopy.ColumnMappings.Add("[UoM]", "[UoM]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcel2(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
Salesinput_ctrl md = new Salesinput_ctrl();
bool kq = md.deleteedlp();
//if (!kq)
//{
// MessageBox.Show("Không xóa được bảng Edlpinput!", "Thông báo ", MessageBoxButtons.OK, MessageBoxIcon.Information);
//}
datainportF inf = (datainportF)obj;
string filename = inf.filename;
string connectionString = "";
if (filename.Contains(".xlsx") || filename.Contains(".XLSX"))
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " + filename + ";" + "Extended Properties=Excel 8.0;";
}
//------
//---------------fill data
ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvide.GetDataFromExcel(filename);
// Sales Group Sales Group desc Sales Off Sales Office Desc
System.Data.DataTable batable = new System.Data.DataTable();
// batable.Columns.Add("Soldto", typeof(double));
batable.Columns.Add("ContractNo", typeof(string));
batable.Columns.Add("BatchNo", typeof(double));
batable.Columns.Add("PayType", typeof(string));
batable.Columns.Add("PaidRequestAmt", typeof(double));
batable.Columns.Add("PaidNote", typeof(string));
batable.Columns.Add("PaymentDoc", typeof(string));
// PaymentDoc
string username = Utils.getusername();
batable.Columns.Add("Username", typeof(string));
batable.Columns["Username"].DefaultValue = username;
batable.Columns.Add("status", typeof(Boolean));
batable.Columns["status"].DefaultValue = false;
#region setcolum
int PaymentDocid = -1;
int ContractNoid = -1;
int BatchNoid = -1;
int PayTypeid = -1;
int PaidRequestAmtid = -1;
int PaidNoteid = -1;
// View.Viewdatatable vi1 = new View.Viewdatatable(sourceData, "Test");
// vi1.ShowDialog();
// int headindex = -2;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null)
{
#region setcolum
if (value.Trim() == "ContractNo")
{
ContractNoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("BatchNo"))
{
BatchNoid = columid;
// headindex = rowid;
}
if (value.Trim() == ("PayType"))
{
PayTypeid = columid;
// headindex = rowid;
}
if (value.Trim() == ("PaidRequestAmt"))
{
PaidRequestAmtid = columid;
// headindex = rowid;
}
if (value.Trim() == "PaidNote")
{
PaidNoteid = columid;
// headindex = rowid;
}
if (value.Trim() == "PaymentDoc")
{
PaymentDocid = columid;
// headindex = rowid;
}
#endregion
}
} // colum
} // roww off heatder
#endregion
if (ContractNoid == -1)
{
MessageBox.Show("Please check ContractNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (BatchNoid == -1)
{
MessageBox.Show("Please check BatchNo colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PayTypeid == -1)
{
MessageBox.Show("Please check PayType colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaidRequestAmtid == -1)
{
MessageBox.Show("Please check PaidRequestAmt colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaidNoteid == -1)
{
MessageBox.Show("Please check PaidNote colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (PaymentDocid == -1)
{
MessageBox.Show("Please check PaymentDoc colunm", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region setvalue of massconfirm
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string PaidRequestAmt = sourceData.Rows[rowixd][PaidRequestAmtid].ToString();
if (PaidRequestAmt != "" && Utils.IsValidnumber(PaidRequestAmt))
{
DataRow dr = batable.NewRow();
dr["ContractNo"] = sourceData.Rows[rowixd][ContractNoid].ToString().Trim();
dr["BatchNo"] = double.Parse(sourceData.Rows[rowixd][BatchNoid].ToString().Trim());
dr["PayType"] = sourceData.Rows[rowixd][PayTypeid].ToString().Trim();
dr["PaidRequestAmt"] = double.Parse(sourceData.Rows[rowixd][PaidRequestAmtid].ToString().Trim());
dr["PaidNote"] = sourceData.Rows[rowixd][PaidNoteid].ToString().Trim();
dr["PaymentDoc"] = sourceData.Rows[rowixd][PaymentDocid].ToString().Trim();
batable.Rows.Add(dr);
}
#endregion
}// row
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.DestinationTableName = "tbl_MassConfirmTemp";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("ContractNo", "ContractNo");
bulkCopy.ColumnMappings.Add("BatchNo", "BatchNo");
bulkCopy.ColumnMappings.Add("PayType", "PayType");
bulkCopy.ColumnMappings.Add("PaidRequestAmt", "PaidRequestAmt");
bulkCopy.ColumnMappings.Add("PaidNote", "PaidNote");
bulkCopy.ColumnMappings.Add("PaymentDoc", "PaymentDoc");
bulkCopy.ColumnMappings.Add("Username", "Username");
bulkCopy.ColumnMappings.Add("status", "status");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
private void importsexcelSumaryletter(object obj)
{
// List<tblFBL5N> fbl5n_ctrllist = new List<tblFBL5N>();
// fbl5n_ctrl md = new fbl5n_ctrl();
// bool kq = md.deleteFbl5n();
datauploadSumary inf = (datauploadSumary)obj;
string filename = inf.filename;
DateTime todate = inf.todate;
DateTime returdate = inf.returdate;
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("GroupLetter", typeof(string));
batable.Columns.Add("Nameletter", typeof(string));
batable.Columns.Add("Tongtiennonuoc", typeof(double));
batable.Columns.Add("Tongtienthanhtoan", typeof(double));
batable.Columns.Add("Tongno", typeof(double));
// batable.Columns.Add("Stt", typeof(string));
batable.Columns.Add("toDate", typeof(DateTime));
batable.Columns.Add("returndate", typeof(DateTime));
int GroupLetterid = -1;
int Nameletterid = -1;
int Tongtiennonuocid = -1;
int Tongtienthanhtoanid = -1;
int Tongnoid = -1;
for (int rowid = 0; rowid < 3; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
//
//
//
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("Group Letter"))
{
GroupLetterid = columid;
// headindex = rowid;
}
if (value.Trim() == ("Name letter"))
{
Nameletterid = columid;
// headindex = 0;
}
if (value.Trim() == ("1.Tổng Tiền Nợ Nước"))
{
Tongtiennonuocid = columid;
// headindex = 0;
}
if (value.Trim() == ("2.Tổng Tiền Thanh Toán"))
{
Tongtienthanhtoanid = columid;//
}
if (value.Trim() == ("Tổng Tiền Công Nợ (1)+(2)"))
{
Tongnoid = columid;//
}
}
#endregion
} // colum
} // roww off heatder
if (GroupLetterid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Group Letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Nameletterid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Name letter", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Tongtiennonuocid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột 1.Tổng Tiền Nợ Nước ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Tongtienthanhtoanid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột 2.Tổng Tiền Thanh Toán ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Tongnoid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Tổng Tiền Công Nợ (1)+(2) ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// int stt = 0;
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string GroupLetter = sourceData.Rows[rowixd][GroupLetterid].ToString();
string Nameletter = sourceData.Rows[rowixd][Nameletterid].ToString();
string tongtiennuoc = sourceData.Rows[rowixd][Tongtiennonuocid].ToString();
if (GroupLetter != "" && Nameletter != "" && Utils.IsValidnumber(tongtiennuoc))
{
// stt = stt + 1;
DataRow dr = batable.NewRow();
dr["Tongtiennonuoc"] = double.Parse(sourceData.Rows[rowixd][Tongtiennonuocid].ToString()); //.Trim();
dr["Tongtienthanhtoan"] = double.Parse(sourceData.Rows[rowixd][Tongtienthanhtoanid].ToString()); //.Trim();
dr["Tongno"] = double.Parse(sourceData.Rows[rowixd][Tongnoid].ToString()); //.Trim();
dr["GroupLetter"] = sourceData.Rows[rowixd][GroupLetterid].ToString().Trim();
dr["Nameletter"] = sourceData.Rows[rowixd][Nameletterid].ToString().Trim();
// dr["Stt"] = stt;
dr["toDate"] = todate;
dr["returndate"] = returdate;
batable.Rows.Add(dr);
}
#endregion
}
// Utils util = new Utils();
string destConnString = Utils.getConnectionstr();
//---------------fill data
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tblNKAArletterRpts";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("GroupLetter", "[GroupLetter]");
bulkCopy.ColumnMappings.Add("Nameletter", "[GroupName]");
bulkCopy.ColumnMappings.Add("Tongtiennonuoc", "[tongnonuoc]");
bulkCopy.ColumnMappings.Add("Tongtienthanhtoan", "[tongtienthanhtoan]");
bulkCopy.ColumnMappings.Add("Tongno", "[tongno]");
// bulkCopy.ColumnMappings.Add("Stt", "[stt]");
bulkCopy.ColumnMappings.Add("toDate", "[toDate]");
bulkCopy.ColumnMappings.Add("returndate", "[returndate]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
public static bool Import(string filePath, string szTemplateValue)
{
try
{
GlobalMethod.log.Info("开始导入");
EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.AutoDetectChangesEnabled = false;
EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.ValidateOnSaveEnabled = false;
Hashtable htUsers = new Hashtable();
var lstUsers = EnterRepository.GetRepositoryEnter().UsersRepository.LoadEntities().ToList();
foreach (var item in lstUsers)
{
if (!htUsers.ContainsKey(item.Tel))
{
htUsers.Add(item.Tel, item.ID);
}
}
var lstEmployee = EnterRepository.GetRepositoryEnter().EmployeeRepository.LoadEntities().ToList();
Hashtable htEmployee = new Hashtable();
foreach (var item in lstEmployee)
{
if (htEmployee.Contains(item.Name))
{
continue;
}
htEmployee.Add(item.Name, item.ID);
}
string[] values = szTemplateValue.Split(',');
ExcelProvider excelProvider = ExcelProvider.Create(filePath, "Sheet1");
int passCount = 0;
int importCount = 0;
foreach (ExcelRow row in excelProvider)
{
int ID = 0;
Models.Users user = new Models.Users();
for (int index = 0; index < values.Length; index++)
{
switch (values[index])
{
case "网报序号":
if (!string.IsNullOrEmpty(row.GetString("网报序号")))
{
user.ID = int.Parse(row.GetString(values[index]));
}
break;
case "多退少补":
user.MoneyBack = row.GetString(values[index]);
break;
case "酒店房价":
user.HotelExpense = row.GetString(values[index]);
break;
case "酒店":
user.Hotel = row.GetString(values[index]);
break;
case "房号":
user.Room = row.GetString(values[index]);
break;
case "已交款额":
user.PayMoney = row.GetString(values[index]);
break;
case "备注":
user.Baks = row.GetString(values[index]);
break;
case "联系方式":
user.Tel = row.GetString(values[index]);
break;
case "网报密码":
user.Pwd = row.GetString(values[index]);
break;
case "所在学校":
user.School = row.GetString(values[index]);
break;
case "性别":
user.Gender = row.GetString(values[index]);
break;
case "报名次序":
string sequences = row.GetString(values[index]);
if (string.IsNullOrEmpty(sequences))
{
sequences = "0";
}
user.Sequences = int.Parse(sequences);
break;
case "业务员":
user.EmployeeName = row.GetString(values[index]);
break;
case "意向同住人":
user.ExceptRoomie = row.GetString(values[index]);
break;
case "报考类型":
user.Template = row.GetString(values[index]);
break;
case "收缴余款所在地":
user.PayPlace = row.GetString(values[index]);
break;
case "所报学校":
user.ExamSchool = row.GetString(values[index]);
break;
case "姓名":
user.Name = row.GetString(values[index]);
break;
case "提交考点":
user.ExamPlace = row.GetString(values[index]);
break;
default:
break;
}
}
if (string.IsNullOrEmpty(user.Tel))
{
passCount++;
GlobalMethod.log.Warn(string.Format("导入excel,考生{0}号码为空，跳过", user.Name));
continue;
}
object szEmployeeID = htEmployee[user.EmployeeName];
if (szEmployeeID == null)
{
passCount++;
GlobalMethod.log.Warn(string.Format("导入excel,考生{0}的业务员姓名未能在系统内找到，跳过", user.Name));
continue;
}
user.EmployeeID = szEmployeeID == null ? 0 : int.Parse(szEmployeeID.ToString());
if (!htUsers.ContainsKey(user.Tel))
{
user.CreateTime = DateTime.Now;
EnterRepository.GetRepositoryEnter().UsersRepository.AddEntity(user);
}
else
{
user.ID = int.Parse(htUsers[user.Tel].ToString());
EnterRepository.GetRepositoryEnter().UsersRepository.EditEntity(user, new string[] { "EmployeeID", "Name", "School", "ExamSchool", "Sequences", "Tel", "Baks", "Pwd", "PayMoney", "ExamPlace", "Room", "Hotel", "HotelExpense", "MoneyBack", "Gender", "Template", "PayPlace", "ExceptRoomie", "Status" });
}
}
importCount = EnterRepository.GetRepositoryEnter().SaveChange();
GlobalMethod.log.Info(string.Format("本次导入成功，共导入{0}考生,应数据异常跳过{1}个"
, importCount.ToString()
, passCount.ToString()));
return(true);
}
catch (Exception ex)
{
GlobalMethod.log.Error(ex);
throw;
}
finally
{
EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.AutoDetectChangesEnabled = true;
EnterRepository.GetRepositoryEnter().UsersRepository.db.Configuration.ValidateOnSaveEnabled = true;
}
}
private void importFreglassessexcel(object obj)
{
string connection_string = Utils.getConnectionstr();
LinqtoSQLDataContext db = new LinqtoSQLDataContext(connection_string);
fREEGALSSES_CTRL Rm = new fREEGALSSES_CTRL();
string username = Utils.getusername();
bool kq = Rm.deleteallFreglassesBEgin();
datainportF inf = (datainportF)obj;
string filename = inf.filename;
//OleDbCommand command = new OleDbCommand(
// @"SELECT [CUSTOMER], [SALORG],[COLAMT],[PERNO]
// FROM [Sheet1$]
// WHERE ( [CUSTOMER] is not null ) ", conn);
// ExcelProvider ExcelProvide = new ExcelProvider();
//#endregion
System.Data.DataTable sourceData = ExcelProvider.GetDataFromExcel(filename);
System.Data.DataTable batable = new System.Data.DataTable();
batable.Columns.Add("CUSTOMER", typeof(double));
batable.Columns.Add("SALORG", typeof(string));
// batable.Columns.Add("PERNO", typeof(string));
batable.Columns.Add("Freeglass3years", typeof(int));
batable.Columns.Add("Freeglass3phantram", typeof(int));
batable.Columns.Add("AmountFreeglassesValue", typeof(double));
batable.Columns.Add("TypeDoc", typeof(string));
batable.Columns.Add("userupdate", typeof(string));
batable.Columns.Add("Posting_Date", typeof(DateTime));
// CUSTOMER SALORG Freeglass3years Freeglass3phantram AmountFreeglassesValue
int CUSTOMERid = -1;
int SALORGid = -1;
int Freeglass3yearsid = -1;
int Freeglass3phantramid = -1;
int AmountFreeglassesValueid = -1;
int rowseet = sourceData.Rows.Count;
if (rowseet > 5)
{
rowseet = 5;
}
for (int rowid = 0; rowid < rowseet; rowid++)
{
// headindex = 1;
for (int columid = 0; columid < sourceData.Columns.Count; columid++)
{
#region
string value = sourceData.Rows[rowid][columid].ToString();
// MessageBox.Show(value +":"+ rowid);
if (value != null && value != "")
{
// #region setcolum
if (value.Trim() == ("CUSTOMER"))
{
CUSTOMERid = columid;
// headindex = rowid;
}
if (value.Trim() == ("SALORG"))
{
SALORGid = columid;
// headindex = 0;
}
if (value.Trim() == ("Freeglass3years"))
{
Freeglass3yearsid = columid;
// headindex = 0;
}
if (value.Trim() == ("Freeglass3phantram"))
{
Freeglass3phantramid = columid;
}
if (value.Trim() == ("AmountFreeglassesValue"))
{
AmountFreeglassesValueid = columid;
}
}
#endregion
} // colum
} // roww off heatder
if (CUSTOMERid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột CUSTOMER", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (SALORGid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột SALORG", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Freeglass3yearsid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Freeglass3years", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (Freeglass3phantramid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột Freeglass3phantram", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (AmountFreeglassesValueid == -1)
{
MessageBox.Show("Dữ liệu thiếu cột AmountFreeglassesValue", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
for (int rowixd = 0; rowixd < sourceData.Rows.Count; rowixd++)
{
#region
// string valuepricelist = Utils.GetValueOfCellInExcel(worksheet, rowid, columpricelist);
string CUSTOMERvl = sourceData.Rows[rowixd][CUSTOMERid].ToString();
if (CUSTOMERvl != "" && Utils.IsValidnumber(CUSTOMERvl))
{
if (double.Parse(CUSTOMERvl) > 0)
{
DataRow dr = batable.NewRow();
dr["CUSTOMER"] = double.Parse(sourceData.Rows[rowixd][CUSTOMERid].ToString());//.Trim();
dr["SALORG"] = sourceData.Rows[rowixd][SALORGid].ToString().Trim();
if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
{
dr["Freeglass3years"] = int.Parse(sourceData.Rows[rowixd][Freeglass3yearsid].ToString().Trim());
}
else
{
dr["Freeglass3years"] = 0;
}
//----------------
if (sourceData.Rows[rowixd][Freeglass3phantramid].ToString() != "")
{
dr["Freeglass3phantram"] = int.Parse(sourceData.Rows[rowixd][Freeglass3phantramid].ToString().Trim());
}
else
{
dr["Freeglass3phantram"] = 0;
}
///----
if (sourceData.Rows[rowixd][Freeglass3yearsid].ToString() != "")
{
dr["AmountFreeglassesValue"] = int.Parse(sourceData.Rows[rowixd][AmountFreeglassesValueid].ToString().Trim());
}
else
{
dr["AmountFreeglassesValue"] = 0;
}
dr["TypeDoc"] = "Begin";//.Trim();//sourceData.Rows[rowixd][COLAMTid].ToString().Trim();
dr["userupdate"] = username;
dr["Posting_Date"] = DateTime.Today;
batable.Rows.Add(dr);
}
}
#endregion
// Utils util = new Utils();
//---------------fill data
}
string destConnString = Utils.getConnectionstr();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destConnString))
{
bulkCopy.DestinationTableName = "tblFBL5NNewCol3year";
// Write from the source to the destination.
bulkCopy.ColumnMappings.Add("CUSTOMER", "[Customer code]");
bulkCopy.ColumnMappings.Add("SALORG", "[Region]");
bulkCopy.ColumnMappings.Add("Freeglass3years", "[Freeglass3years]");
bulkCopy.ColumnMappings.Add("Freeglass3phantram", "[Freeglass3phantram]");
bulkCopy.ColumnMappings.Add("AmountFreeglassesValue", "[AmountFreeglassesValue]");
bulkCopy.ColumnMappings.Add("TypeDoc", "TypeDoc");
bulkCopy.ColumnMappings.Add("userupdate", "[userupdate]");
bulkCopy.ColumnMappings.Add("Posting_Date", "[Posting Date]");
try
{
bulkCopy.WriteToServer(batable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo lỗi Bulk Copy !", MessageBoxButtons.OK, MessageBoxIcon.Error);
Thread.CurrentThread.Abort();
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ReportToExcelController" /> class.
/// </summary>
/// <param name="manager">The manager.</param>
public ReportToExcelController(IReportManager manager)
{
this.reportManager = manager;
this.excelProvider = new ExcelProvider();
this.pdfGenerator = new PDFGenerator();
}
