public static bool FixXLColumnTypes(DataSet ds)
{
try
{
DataTable dtdict = ds.Tables["DataDictionary"];
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType NOT IN ( 'text','varchar','char','string')";
DataTable dtdict2 = dv.ToTable();
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString().ToLower();
if (measname.StartsWith("IntHx_by_TYPE"))
{
measname = "IntHx_by_TYPE";
}
if (measname != "IntHx_by_TYPE")
{
if (DataType == "date")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(DateTime));
}
else //if (DataType == "numeric")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(Double));
if (measname == "IntHx_by_TYPE") //also do the other 2 IntHx tables
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables["IntHx_by_CAT"], varname, typeof(Double));
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables["IntHx_by_ALL"], varname, typeof(Double));
}
}
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
public static bool FixXLDates(DataSet ds)
{
try
{
DataTable dtdict = ds.Tables["DataDict"];
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType = 'date'";
DataTable dtdict2 = dv.ToTable();
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString();
if (DataType == "date")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(DateTime));
}
else if (DataType == "numeric")
{
SpreadsheetGearUtils.FixXLColumnDatatype(ds.Tables[measname], varname, typeof(Double));
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
public static bool FixXLColumnTypes(DataTable dt, DataTable dtdict)
{
try
{
DataView dv = dtdict.AsDataView();
dv.RowFilter = "DataType NOT IN ( 'text','varchar','char','string')";
DataTable dtdict2 = dv.ToTable();
SpreadsheetGearUtils.FixXLColumnDatatype(dt, "id", typeof(String));
for (int i = 0; i < dtdict2.Rows.Count; i++)
{
DataRow row = dtdict2.Rows[i];
string measname = row["measname"].ToString();
string varname = row["varname"].ToString();
string DataType = row["DataType"].ToString().ToLower();
List <string> skipvars = new List <string> {
"id", "txgrp", "txstyle", "txintensity", "timept", "group"
};
if (skipvars.Contains(varname))
{
//do nothing
}
else if (DataType == "char" || DataType == "varchar" || DataType == "nvarchar")
{
//do nothing
}
else if (DataType == "date" || DataType == "smalldatetime" || DataType == "datetime")
{
DataColumn col = dt.Columns[varname];
if (col != null)
{
var coltype = dt.Columns[varname].DataType;
if (coltype.Name == "String")
{
SpreadsheetGearUtils.FixXLColumnDatatype(dt, varname, typeof(DateTime));
}
}
}
else //if (DataType == "numeric")
{
DataColumn col = dt.Columns[varname];
if (col != null)
{
var coltype = dt.Columns[varname].DataType;
if (coltype.Name == "String")
{
SpreadsheetGearUtils.FixXLColumnDatatype(dt, varname, typeof(Double?));
}
}
}
}
}
catch (Exception)
{
return(false);
}
return(true);
}
