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