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; }