protected void CreateNewSheet(string sheetName) { var dt = MetadataFileFormat.CreateDataTable(sheetName); CreateNewSheet(dt); GetSheets(); }
public void GetTracks() { var dt = new DataTable("Track"); using (var conn = new OleDbConnection()) { conn.ConnectionString = GetConnectionString(Filename); conn.Open(); var commandText = string.Format("SELECT * FROM [{0}${1}]", SheetName, SheetRange); using (var cmd = new OleDbCommand(commandText, conn)) { var adapter = new OleDbDataAdapter(); adapter.SelectCommand = cmd; adapter.FillSchema(dt, SchemaType.Source); adapter.Fill(dt); } } _reservedColumnsCount = 0; _tracks = new List <string>(); foreach (DataColumn col in dt.Columns) { if (!MetadataFileFormat.GetReservedColumnNames().Contains(col.ColumnName)) { _tracks.Add(col.ColumnName); } else { _reservedColumnsCount++; } } }
public void Write(CubeMetadata metadata) { if (metadata == null) { throw new ArgumentNullException(); } GetSheets(); if (_sheets.IndexOf(SheetName + "$") == -1) { CreateNewSheet(SheetName); } RaiseProgressStatus("Opening Xls file"); DataTable dataTable = MetadataFileFormat.WriteInDataTable(metadata); dataTable.TableName = SheetName + "$"; _rowTotal = dataTable.Rows.Count; using (var conn = new OleDbConnection(GetConnectionString(Filename))) { using (var da = new OleDbDataAdapter()) { var sb = new StringBuilder(); sb.AppendFormat("INSERT INTO [{0}$] (", SheetName); foreach (DataColumn col in dataTable.Columns) { sb.AppendFormat(" {0},", col.ColumnName); } sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.Append(") VALUES ("); for (int i = 0; i < dataTable.Columns.Count; i++) { sb.Append(" ?,"); } sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.Append(")"); da.InsertCommand = new OleDbCommand(sb.ToString(), conn); foreach (DataColumn col in dataTable.Columns) { da.InsertCommand.Parameters.Add(string.Format("@{0}", col.ColumnName), OleDbType.VarChar, 255, col.ColumnName); } da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated); conn.Open(); da.Update(dataTable); da.RowUpdated -= new OleDbRowUpdatedEventHandler(OnRowUpdated); } } RaiseProgressStatus("Xls file written"); }
public void Write(CubeMetadata metadata) { if (metadata == null) { throw new ArgumentNullException(); } RaiseProgressStatus("Building Csv file content"); DataTable dataTable = MetadataFileFormat.WriteInDataTable(metadata); _rowTotal = dataTable.Rows.Count; var sb = new StringBuilder(); foreach (DataColumn col in dataTable.Columns) { sb.AppendFormat("\"{0}\"{1}", col.ColumnName, Definition.FieldSeparator); } sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.AppendLine(); //goto next line foreach (DataRow row in dataTable.Rows) { _rowCount++; foreach (string item in row.ItemArray) { var str = (item).Replace(Definition.TextQualifier.ToString(), Definition.TextQualifier.ToString() + Definition.TextQualifier.ToString()); sb.AppendFormat("\"{0}\"{1}", str, Definition.FieldSeparator); } sb.Remove(sb.Length - 1, 1); //Remove the last comma sb.AppendLine(); //goto next line RaiseProgressStatus("Creating row {0} of {1}", _rowCount, _rowTotal); } RaiseProgressStatus("Csv file content built"); RaiseProgressStatus("Writing Csv file content"); using (StreamWriter outfile = new StreamWriter(Filename, false, Encoding.UTF8)) { outfile.Write(sb); } RaiseProgressStatus("Csv file written"); }
public static IEnumerable <string> GetReservedColumnNames() { return(from column in MetadataFileFormat.CreateDataTable("").Columns.Cast <DataColumn>() select column.ColumnName); }