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