public static DataTable SimpleDataExport(Range range, bool rangeHasHeaders)
{
#region #SimpleDataExport
Worksheet worksheet = range.Worksheet;
// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);
//Validate cell value types. If cell value types in a column are different, the column values are exported as text.
for (int col = 0; col < range.ColumnCount; col++)
{
CellValueType cellType = range[0, col].Value.Type;
for (int r = 1; r < range.RowCount; r++)
{
if (cellType != range[r, col].Value.Type)
{
dataTable.Columns[col].DataType = typeof(string);
break;
}
}
}
// Create the exporter that obtains data from the specified range,
// skips the header row (if required) and populates the previously created data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;
// Perform the export.
exporter.Export();
#endregion #SimpleDataExport
// A custom method that displays the resulting data table.
return(dataTable);
}
public TGroup GetCells(IVisio.Shape shape, CellValueType type)
{
var data_for_shape = this.query.GetCells(shape, type);
var cells = this.ToCellGroup(data_for_shape.Cells);
return(cells);
}
private void _commit(SurfaceTarget surface, CellValueType type)
{
if (this._records == null || this._records.Count < 1)
{
return;
}
var stream = this._records.BuildStreamArray(StreamType.Src);
if (stream.Array.Length == 0)
{
throw new VisioAutomation.Exceptions.InternalAssertionException();
}
var values = this._records.BuildValuesArray();
if (type == CellValueType.Formula)
{
var flags = this._compute_setformula_flags();
int c = surface.SetFormulas(stream, values, (short)flags);
}
else
{
const object[] unitcodes = null;
var flags = this._compute_setresults_flags();
surface.SetResults(stream, unitcodes, values, (short)flags);
}
}
/// <summary>
/// 解析单元格值类型
/// </summary>
/// <param name="cellValueType">单元格值类型</param>
/// <returns></returns>
public static CellType Resolve(CellValueType cellValueType)
{
switch (cellValueType)
{
case CellValueType.Unknown:
return(CellType.Unknown);
case CellValueType.Number:
return(CellType.Numeric);
case CellValueType.Date:
case CellValueType.String:
return(CellType.String);
case CellValueType.Formula:
return(CellType.Formula);
case CellValueType.Empty:
return(CellType.Blank);
case CellValueType.Boolean:
return(CellType.Boolean);
case CellValueType.Error:
return(CellType.Error);
default:
return(CellType.Blank);
}
}
/// <summary>
/// 重设 CellValueType, CellStyle, IsValid 属性.
/// </summary>
public void Reset()
{
//this._cellValue = null;
this._cellValueType = CellValueType.StringDenotable;
this._cellStyle = null;
this._isValid = true;
}
public static void SetAndGetValueOfCellA1()
{
#region radspreadsheet-features-formulas_0
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
workbook.ActiveWorksheet.Cells[0, 0].SetValue("=3+4");
#endregion
#region radspreadsheet-features-formulas_1
FormulaCellValue formulaCellValue = workbook.ActiveWorksheet.Cells[0, 0].GetValue().Value as FormulaCellValue;
if (formulaCellValue != null)
{
RadExpression expression = formulaCellValue.Value;
//expression is AdditionExpression with operands 3 and 4
CellValueFormat format = workbook.ActiveWorksheet.Cells[0, 0].GetFormat().Value;
string valueAsString = formulaCellValue.GetValueAsString(format);
//valueAsString = "=3+4"
string resultValueAsString = formulaCellValue.GetResultValueAsString(format);
//resultAsString = "7"
CellValueType valueType = formulaCellValue.ValueType;
//valueType = Formula
CellValueType resultValueType = formulaCellValue.ResultValueType;
//resultValueType = Number
}
#endregion
}
public List <TGroup> GetCells(IVisio.Shape shape, CellValueType type)
{
var data_for_shape = query.GetCells(shape, type);
var first_section = data_for_shape.Sections[0];
var cellgroups = this.__ToCellGroups(first_section);
return(cellgroups);
}
public List <TGroup> GetCells(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var data_for_shapes = this.query.GetCells(page, shapeids, type);
var list = new List <TGroup>(shapeids.Count);
var objects = data_for_shapes.Select(d => this.ToCellGroup(d.Cells));
list.AddRange(objects);
return(list);
}
public override bool Equals(object aObject)
{
if (aObject is CellValueType)
{
CellValueType cellValueType = (CellValueType)aObject;
return(Value == cellValueType.Value);
}
return(false);
}
public List <TGroup> GetCellsMultiRow(IVisio.Shape shape, CellValueType type)
{
this._enforce_type(CellGroupBuilderType.MultiRow);
var sec_cols = this.query_sections_multirow[0];
var cellqueryresult = __GetCells(query_sections_multirow, shape, type);
var shape0_sectionshaperows0 = cellqueryresult[0];
var cellgroups = this._sectionshaperows_to_cellgroups(shape0_sectionshaperows0, sec_cols);
return(cellgroups);
}
public TGroup GetCellsSingleRow(IVisio.Shape shape, CellValueType type)
{
this._enforce_type(CellGroupBuilderType.SingleRow);
var cellqueryresult = this.__GetCells(query_cells_singlerow, shape, type);
var cols = this.query_cells_singlerow.Columns;
var first_row = cellqueryresult[0];
var cells = this.ToCellGroup(first_row, cols);
return(cells);
}
public string ToMysqlString(string cellgs)
{
StringBuilder sbuilder = new StringBuilder();
if (Cellgs.Equals("ALL") || cellgs.Equals("ALL") || Cellgs.Equals(cellgs))
{
sbuilder.Append(" `" + CellName + "` ");
switch (CellValueType.ToLower())
{
case "int":
sbuilder.Append("int ");
break;
case "boolean":
sbuilder.Append(" TINYINT(1) ");
break;
case "double":
sbuilder.Append(" double ");
break;
case "float":
sbuilder.Append(" float ");
break;
case "string":
if (CellLength < 500)
{
sbuilder.Append(" VARCHAR(" + CellLength + ") ");
}
else if (CellLength < 10000)
{
sbuilder.Append(" TEXT ");
}
else
{
sbuilder.Append(" LONGTEXT ");
}
break;
case "long":
sbuilder.Append(" bigint ");
break;
}
if (IsPKey)
{
sbuilder.Append(" NOT NULL primary key ");
}
sbuilder.Append(" COMMENT '" + CellNotes + "'");
}
return(sbuilder.ToString());
}
static CellValueType()
{
Blank = new CellValueType("Blank", 4);
Boolean = new CellValueType("Boolean", 5);
Currency = new CellValueType("Currency", 7);
Date = new CellValueType("Date", 3);
Double = new CellValueType("Double", 2);
Error = new CellValueType("Error", 6);
Integer = new CellValueType("Integer", 1);
Text = new CellValueType("Text", 0);
Time = new CellValueType("Time", 8);
}
/// <summary>
/// Gets all the custom properties defined on a shape
/// </summary>
/// <remarks>
/// If there are no custom properties then null will be returned</remarks>
/// <param name="shape"></param>
/// <returns>A list of custom properties</returns>
public static CustomPropertyDictionary GetCells(IVisio.Shape shape, CellValueType type)
{
var prop_names = CustomPropertyHelper.GetNames(shape);
var dic = new CustomPropertyDictionary(prop_names.Count);
var cells = CustomPropertyCells.GetCells(shape, type);
for (int prop_index = 0; prop_index < prop_names.Count; prop_index++)
{
string prop_name = prop_names[prop_index];
dic[prop_name] = cells[prop_index];
}
return(dic);
}
public static TextFormat GetFormat(IVisio.Shape shape, CellValueType type)
{
var cells = new TextFormat();
cells.CharacterFormats = CharacterFormatCells.GetCells(shape, type);
cells.ParagraphFormats = ParagraphFormatCells.GetCells(shape, type);
cells.TextBlock = TextBlockCells.GetCells(shape, type);
if (HasTextXFormCells(shape))
{
cells.TextXForm = TextXFormCells.GetCells(shape, type);
}
cells.CharacterTextRuns = TextFormat.GetTextRuns(shape, IVisio.VisRunTypes.visCharPropRow, true);
cells.ParagraphTextRuns = TextFormat.GetTextRuns(shape, IVisio.VisRunTypes.visParaPropRow, true);
cells.TabStops = TextHelper.GetTabStops(shape);
return(cells);
}
private void barButtonItemRangeToDataTable_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
if (barCheckItemStopEmptyRow.Checked)
{
ExportSelectionStopOnEmptyRow();
return;
}
#region #SimpleDataExport
Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
Range range = worksheet.Selection;
bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked;
// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);
//Validate cell value types. If cell value types in a column are different, the column values are exported as text.
for (int col = 0; col < range.ColumnCount; col++)
{
CellValueType cellType = range[0, col].Value.Type;
for (int r = 1; r < range.RowCount; r++)
{
if (cellType != range[r, col].Value.Type)
{
dataTable.Columns[col].DataType = typeof(string);
break;
}
}
}
// Create the exporter that obtains data from the specified range,
// skips the header row (if required) and populates the previously created data table.
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;
// Perform the export.
exporter.Export();
#endregion #SimpleDataExport
// A custom method that displays the resulting data table.
ShowResult(dataTable);
}
public List <List <TGroup> > GetCells(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var data_for_shapes = query.GetCells(page, shapeids, type);
var list_cellgroups = new List <List <TGroup> >(shapeids.Count);
foreach (var d in data_for_shapes)
{
var first_section = d.Sections[0];
var cellgroups = this.__ToCellGroups(first_section);
list_cellgroups.Add(cellgroups);
}
return(list_cellgroups);
}
public static List <ConnectionPointCells> GetCells(IVisio.Shape shape, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(shape, type));
}
public static List <List <ConnectionPointCells> > GetCells(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(page, shapeids, type));
}
public static List <UserDefinedCellCells> GetCells(IVisio.Shape shape, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(shape, type));
}
public static List <List <UserDefinedCellCells> > GetCells(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(page, shapeids, type));
}
public void Commit(IVisio.Page page, CellValueType type)
{
var surface = new SurfaceTarget(page);
this._commit(surface, type);
}
public void Commit(IVisio.Shape shape, CellValueType type)
{
var surface = new SurfaceTarget(shape);
this._commit(surface, type);
}
public static List <List <ParagraphFormatCells> > GetCells(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(page, shapeids, type));
}
public static List <TextFormat> GetFormat(IVisio.Page page, IList <int> shapeids, CellValueType type)
{
var charcells = CharacterFormatCells.GetCells(page, shapeids, type);
var paracells = ParagraphFormatCells.GetCells(page, shapeids, type);
var textblockcells = TextBlockCells.GetCells(page, shapeids, type);
var page_shapes = page.Shapes;
var formats = new List <TextFormat>(shapeids.Count);
for (int i = 0; i < shapeids.Count; i++)
{
var format = new TextFormat();
format.CharacterFormats = charcells[i];
format.ParagraphFormats = paracells[i];
format.TextBlock = textblockcells[i];
formats.Add(format);
var shape = page_shapes.ItemFromID[shapeids[i]];
format.CharacterTextRuns = TextFormat.GetTextRuns(shape, IVisio.VisRunTypes.visCharPropRow, true);
format.ParagraphTextRuns = TextFormat.GetTextRuns(shape, IVisio.VisRunTypes.visParaPropRow, true);
format.TabStops = TextHelper.GetTabStops(shape);
}
return(formats);
}
public static TextBlockCells GetCells(IVisio.Shape shape, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(shape, type));
}
public static List <ParagraphFormatCells> GetCells(IVisio.Shape shape, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(shape, type));
}
public static List <TextXFormCells> GetCells(Microsoft.Office.Interop.Visio.Page page, IList <int> shapeids, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(page, shapeids, type));
}
public Dictionary <IVisio.Shape, IList <ControlCells> > GetControls(TargetShapes targetshapes, CellValueType cvt)
{
targetshapes = targetshapes.ResolveToShapes(this._client);
if (targetshapes.Shapes.Count < 1)
{
return(new Dictionary <IVisio.Shape, IList <ControlCells> >(0));
}
var dic = new Dictionary <IVisio.Shape, IList <ControlCells> >(targetshapes.Shapes.Count);
foreach (var shape in targetshapes.Shapes)
{
var controls = ControlCells.GetCells(shape, cvt);
dic[shape] = controls;
}
return(dic);
}
public static TextXFormCells GetCells(Microsoft.Office.Interop.Visio.Shape shape, CellValueType type)
{
var query = lazy_query.Value;
return(query.GetCells(shape, type));
}
