public bool SetValue(string column_name, string value, bool is_white)
{
bool result = false;
if (_list != null)
{
if (!_list.Contains(column_name))
{
ExcelObject _cell = new ExcelObject("Cell");
if (_cell != null)
{
ExcelObject _data = new ExcelObject("Data");
if (_data != null)
{
string column_type = "";
string column_system_type = "";
string style_id = "";
int column_index = 0;
ExcelColumns _columns = _table.GetColumns();
if (_columns != null)
{
ExcelObject _column = _columns.GetColumnObject(column_name);
if (_column != null)
{
column_type = _column.GetPropertyValue("Type");
column_system_type = _column.GetPropertyValue("SystemType");
style_id = _column.GetPropertyValue("StyleId");
column_index = Int32.Parse(_column.GetPropertyValue("Index"));
if (column_index < 0)
column_index = -column_index;
_column.IsNewLine = true;
};
};
int style_int = 0;
try
{
style_int = Int32.Parse(style_id);
if (!is_white)
style_int = style_int + 4;
}
catch
{
}
_cell.AddProperty("ss", "StyleID", "s" + style_int.ToString());
_row.AddObject(column_index, ref _cell);
if (value.Length > 0)
{
_cell.AddObject(0, ref _data);
_data.AddProperty("ss", "Type", column_type);
if (column_system_type == "System.DateTime")
value = GetExcelShortDateFormat(value);
if (column_system_type == "System.Decimal")
value = GetExcelDoubleFormat(value);
ExcelObject _value = new ExcelObject(value);
if (_value != null)
{
_data.AddObject(0, ref _value);
_value.IsXML = false;
_list.Add(column_name, _value);
result = true;
};
};
};
};
}
else
{
if (value.Length > 0)
((ExcelObject)_list[column_name]).Name = value;
};
};
return result;
}
public bool SetHeaderValue(string column_name, string column_alias)
{
bool result = false;
//string value = column_name;
string value = column_alias;
if (_list != null)
{
if (!_list.Contains(column_name))
{
ExcelObject _cell = new ExcelObject("Cell");
if (_cell != null)
{
ExcelObject _data = new ExcelObject("Data");
if (_data != null)
{
string column_type = "";
string column_system_type = "";
string style_id = "";
int column_index = 0;
ExcelColumns _columns = _table.GetColumns();
if (_columns != null)
{
ExcelObject _column = _columns.GetColumnObject(column_name);
if (_column != null)
{
column_type = _column.GetPropertyValue("Type");
column_system_type = _column.GetPropertyValue("SystemType");
style_id = _column.GetPropertyValue("StyleId");
column_index = Int32.Parse(_column.GetPropertyValue("Index"));
if (column_index < 0)
column_index = -column_index;
_column.IsNewLine = true;
};
};
_cell.AddProperty("ss", "StyleID", "s21");
_row.AddObject(column_index, ref _cell);
if (value.Length > 0)
{
_cell.AddObject(0, ref _data);
_data.AddProperty("ss", "Type", "String");
ExcelObject _value = new ExcelObject(value);
if (_value != null)
{
_data.AddObject(0, ref _value);
_value.IsXML = false;
_list.Add(column_name, _value);
result = true;
};
};
};
};
}
else
((ExcelObject)_list[column_name]).Name = value;
};
return result;
}
public ExcelDocument()
{
_column_filter = "";
_header = "<?xml version=\"1.0\"?>";
_header += "<?mso-application progid=\"Excel.Sheet\"?>";
//_footer = "";
_root = new ExcelObject("Workbook");
if (_root != null)
{
_root.AddProperty("", "xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
_root.AddProperty("xmlns", "o", "urn:schemas-microsoft-com:office:office");
_root.AddProperty("xmlns", "x", "urn:schemas-microsoft-com:office:excel");
_root.AddProperty("xmlns", "ss", "urn:schemas-microsoft-com:office:spreadsheet");
_root.AddProperty("xmlns", "html", "http://www.w3.org/TR/REC-html40");
ExcelObject _styles = new ExcelObject("Styles");
if (_styles != null)
{
ExcelObject _style_default = new ExcelObject("Style");
if (_style_default != null)
{
_style_default.AddProperty("ss", "ID", "Default");
_style_default.AddProperty("ss", "Name", "Normal");
_styles.AddObject(0, ref _style_default);
};
ExcelObject _header_default = new ExcelObject("Style");
if (_header_default != null)
{
ExcelObject _header_align = new ExcelObject("Alignment");
if (_header_align != null)
{
_header_align.AddProperty("ss", "Vertical", "Bottom");
_header_align.AddProperty("ss", "WrapText", "0");
_header_default.AddObject(0, ref _header_align);
};
ExcelObject _header_font = new ExcelObject("Font");
if (_header_font != null)
{
_header_font.AddProperty("ss", "Color", "#FFFFFF");
_header_font.AddProperty("ss", "Bold", "1");
_header_default.AddObject(0, ref _header_font);
};
ExcelObject _header_interior = new ExcelObject("Interior");
if (_header_interior != null)
{
_header_interior.AddProperty("ss", "Color", "#333333");
_header_interior.AddProperty("ss", "Pattern", "Solid");
_header_default.AddObject(0, ref _header_interior);
};
_header_default.AddProperty("ss", "ID", "s21");
_styles.AddObject(0, ref _header_default);
};
ExcelObject _number_default = new ExcelObject("Style");
if (_number_default != null)
{
ExcelObject _number_format_default = new ExcelObject("NumberFormat");
if (_number_format_default != null)
{
_number_default.AddObject(0, ref _number_format_default);
};
ExcelObject _number_default_interior = new ExcelObject("Interior");
if (_number_default_interior != null)
{
_number_default_interior.AddProperty("ss", "Color", "#FFFFFF");
_number_default_interior.AddProperty("ss", "Pattern", "Solid");
_number_default.AddObject(0, ref _number_default_interior);
};
_number_default.AddProperty("ss", "ID", "s22");
_styles.AddObject(0, ref _number_default);
};
ExcelObject _number_zero_precision = new ExcelObject("Style");
if (_number_zero_precision != null)
{
ExcelObject _number_format_zero_precision = new ExcelObject("NumberFormat");
if (_number_format_zero_precision != null)
{
_number_format_zero_precision.AddProperty("ss", "Format", "0");
_number_zero_precision.AddObject(0, ref _number_format_zero_precision);
};
ExcelObject _number_zero_precision_interior = new ExcelObject("Interior");
if (_number_zero_precision_interior != null)
{
_number_zero_precision_interior.AddProperty("ss", "Color", "#FFFFFF");
_number_zero_precision_interior.AddProperty("ss", "Pattern", "Solid");
_number_zero_precision.AddObject(0, ref _number_zero_precision_interior);
};
_number_zero_precision.AddProperty("ss", "ID", "s23");
_styles.AddObject(0, ref _number_zero_precision);
};
ExcelObject _number_four_precision = new ExcelObject("Style");
if (_number_four_precision != null)
{
ExcelObject _number_format_four_precision = new ExcelObject("NumberFormat");
if (_number_format_four_precision != null)
{
_number_format_four_precision.AddProperty("ss", "Format", "0.0000");
_number_four_precision.AddObject(0, ref _number_format_four_precision);
};
ExcelObject _number_four_precision_interior = new ExcelObject("Interior");
if (_number_four_precision_interior != null)
{
_number_four_precision_interior.AddProperty("ss", "Color", "#FFFFFF");
_number_four_precision_interior.AddProperty("ss", "Pattern", "Solid");
_number_four_precision.AddObject(0, ref _number_four_precision_interior);
};
_number_four_precision.AddProperty("ss", "ID", "s24");
_styles.AddObject(0, ref _number_four_precision);
};
ExcelObject _date_style = new ExcelObject("Style");
if (_date_style != null)
{
ExcelObject _date_format = new ExcelObject("NumberFormat");
if (_date_format != null)
{
_date_format.AddProperty("ss", "Format", "Short Date");
_date_style.AddObject(0, ref _date_format);
};
ExcelObject _date_style_interior = new ExcelObject("Interior");
if (_date_style_interior != null)
{
_date_style_interior.AddProperty("ss", "Color", "#FFFFFF");
_date_style_interior.AddProperty("ss", "Pattern", "Solid");
_date_style.AddObject(0, ref _date_style_interior);
};
_date_style.AddProperty("ss", "ID", "s25");
_styles.AddObject(0, ref _date_style);
};
//Other color
ExcelObject _number_default_ = new ExcelObject("Style");
if (_number_default_ != null)
{
ExcelObject _number_format_default_ = new ExcelObject("NumberFormat");
if (_number_format_default_ != null)
{
_number_default_.AddObject(0, ref _number_format_default_);
};
ExcelObject _number_default_interior_ = new ExcelObject("Interior");
if (_number_default_interior_ != null)
{
_number_default_interior_.AddProperty("ss", "Color", "#C0C0C0");
_number_default_interior_.AddProperty("ss", "Pattern", "Solid");
_number_default_.AddObject(0, ref _number_default_interior_);
};
_number_default_.AddProperty("ss", "ID", "s26");
_styles.AddObject(0, ref _number_default_);
};
ExcelObject _number_zero_precision_ = new ExcelObject("Style");
if (_number_zero_precision_ != null)
{
ExcelObject _number_format_zero_precision_ = new ExcelObject("NumberFormat");
if (_number_format_zero_precision_ != null)
{
_number_format_zero_precision_.AddProperty("ss", "Format", "0");
_number_zero_precision_.AddObject(0, ref _number_format_zero_precision_);
};
ExcelObject _number_zero_precision_interior_ = new ExcelObject("Interior");
if (_number_zero_precision_interior_ != null)
{
_number_zero_precision_interior_.AddProperty("ss", "Color", "#C0C0C0");
_number_zero_precision_interior_.AddProperty("ss", "Pattern", "Solid");
_number_zero_precision_.AddObject(0, ref _number_zero_precision_interior_);
};
_number_zero_precision_.AddProperty("ss", "ID", "s27");
_styles.AddObject(0, ref _number_zero_precision_);
};
ExcelObject _number_four_precision_ = new ExcelObject("Style");
if (_number_four_precision_ != null)
{
ExcelObject _number_format_four_precision_ = new ExcelObject("NumberFormat");
if (_number_format_four_precision_ != null)
{
_number_format_four_precision_.AddProperty("ss", "Format", "0.0000");
_number_four_precision_.AddObject(0, ref _number_format_four_precision_);
};
ExcelObject _number_four_precision_interior_ = new ExcelObject("Interior");
if (_number_four_precision_interior_ != null)
{
_number_four_precision_interior_.AddProperty("ss", "Color", "#C0C0C0");
_number_four_precision_interior_.AddProperty("ss", "Pattern", "Solid");
_number_four_precision_.AddObject(0, ref _number_four_precision_interior_);
};
_number_four_precision_.AddProperty("ss", "ID", "s28");
_styles.AddObject(0, ref _number_four_precision_);
};
ExcelObject _date_style_ = new ExcelObject("Style");
if (_date_style_ != null)
{
ExcelObject _date_format_ = new ExcelObject("NumberFormat");
if (_date_format_ != null)
{
_date_format_.AddProperty("ss", "Format", "Short Date");
_date_style_.AddObject(0, ref _date_format_);
};
ExcelObject _date_style_interior_ = new ExcelObject("Interior");
if (_date_style_interior_ != null)
{
_date_style_interior_.AddProperty("ss", "Color", "#C0C0C0");
_date_style_interior_.AddProperty("ss", "Pattern", "Solid");
_date_style_.AddObject(0, ref _date_style_interior_);
};
_date_style_.AddProperty("ss", "ID", "s29");
_styles.AddObject(0, ref _date_style_);
};
_root.AddObject(0, ref _styles);
};
};
}
public ExcelTable CreateWorkSheet(string name, ref ExcelColumns columns)
{
ExcelTable result = null;
if (_root != null)
{
ExcelObject _worksheet = new ExcelObject("Worksheet");
if (_worksheet != null)
{
_worksheet.AddProperty("ss", "Name", name);
ExcelTable _table = new ExcelTable(ref columns);
if (_table != null)
{
ExcelObject _table_object = _table.GetTableObject();
if (_table_object != null)
{
_worksheet.AddObject(0, ref _table_object);
_table_object.IsNewLine = true;
};
result = _table;
};
//***_root.AddObject(0, ref _worksheet);
};
};
return result;
}
public bool AddColumn(int index, string column_name, string column_alias, int width, string type, string system_type, string style_id)
{
bool result = false;
if (_list != null)
{
if (!_list.Contains(column_name))
{
ExcelObject _object = new ExcelObject("Column");
if (_object != null)
{
_object.AddProperty("ss", "AutoFitWidth", "0");
_object.AddProperty("ss", "Width", width.ToString());
_object.AddProperty("", "Type", type, false);
_object.AddProperty("", "Name", column_name, false);
_object.AddProperty("", "Alias", column_alias, false);
_object.AddProperty("", "SystemType", system_type, false);
_object.AddProperty("", "StyleId", style_id, false);
_object.AddProperty("", "Index", index.ToString(), false);
_list.Add(column_name, _object);
result = true;
};
};
};
return result;
}