public void WriteExcelWorkBook(EOrderRow workBook, bool rewrite)
{
XmlSerializer x = new XmlSerializer(typeof(EOrderRow));
using (FileStream file = new FileStream("cesta.xml", FileMode.OpenOrCreate))
using (TextWriter tw = new StreamWriter(file))
{
x.Serialize(tw, workBook);
}
}
//public string CreateTable()
//{
//}
public void WriteExcelWorkBook(EOrderRow workBook, bool rewrite)
{
Column col = new Column();
//string connectionString = "Data Source=ServerName; Initial Catalog=DatabaseName;User ID=username;Password=password";
//string sqlCreate = "Create Table" + workBook.Name + "(";
//foreach (var column in col)
//{
// sqlCreate += column.Name;
// if (column.Type == "int")
// {
// sqlCreate += " int ";
// }
// else if (column.Type == "long")
// {
// sqlCreate += " bigint ";
// }
// else if (column.Type == "double")
// {
// sqlCreate += " decimal(6,2) ";
// }
// else if (column.Type == "string")
// {
// sqlCreate += " varchar(MAX) ";
// }
// sqlCreate += ",";
//}
//sqlCreate += ");";
//using (SqlConnection conn = new SqlConnection(connectionString))
//{
// conn.Open();
// SqlCommand cmd = new SqlCommand(sqlCreate, conn);
// cmd.ExecuteNonQuery();
// cmd.Dispose();
// cmd.Clone();
//}
}
public EOrderRow ReadEbook(string filename, List<string> sheets)
{
string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=Yes;'";
EOrderRow workBook = new EOrderRow();
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
List<EOrderRow> list = new List<EOrderRow>();
foreach (var item in sheets)
{
OleDbCommand command = new OleDbCommand("select * from [" + item + "]", connection);
using (OleDbDataReader dr = command.ExecuteReader())
while (dr.Read() && dr.IsDBNull(0) == false)
{
try
{
list.Add(
new EOrderRow
{
oldCode = dr["Slovnaft kód"].ToString(),
newCode = Convert.ToInt32(dr["Nový SAP kód"]),
good = dr["Tovar"].ToString(),
mj = dr["MJ"].ToString(),
quantity = Convert.ToInt32(dr["Balenie (kusov v kartóne)"]),
newPrice = Convert.ToDouble(dr["SN ČS DDU 10.2.2015 EUR/KS"]),
UPL = Convert.ToInt32(dr["UPL"]),
EAN = Convert.ToInt64(dr["EAN"])
});
}
catch (IndexOutOfRangeException)
{
throw new IndexOutOfRangeException("Nazov stlpca v exceli nezodpoveda preddefinovanemu nazvu");
}
}
}
}
return workBook;
}
