private void ColumnSetUp()
{
ExcelColumns.Add(1, "A");
ExcelColumns.Add(2, "B");
ExcelColumns.Add(3, "C");
ExcelColumns.Add(4, "D");
ExcelColumns.Add(5, "E");
ExcelColumns.Add(6, "F");
ExcelColumns.Add(7, "G");
ExcelColumns.Add(8, "H");
ExcelColumns.Add(9, "I");
ExcelColumns.Add(10, "J");
ExcelColumns.Add(11, "K");
ExcelColumns.Add(12, "L");
ExcelColumns.Add(13, "M");
ExcelColumns.Add(14, "N");
ExcelColumns.Add(15, "O");
ExcelColumns.Add(16, "P");
ExcelColumns.Add(17, "Q");
ExcelColumns.Add(18, "R");
ExcelColumns.Add(19, "S");
ExcelColumns.Add(20, "T");
ExcelColumns.Add(21, "U");
ExcelColumns.Add(22, "V");
ExcelColumns.Add(23, "W");
ExcelColumns.Add(24, "X");
ExcelColumns.Add(25, "Y");
ExcelColumns.Add(26, "Z");
}
static void Main(string[] args)
{
List <ExcelColumn> columns = new List <ExcelColumn>()
{
new ExcelColumn {
ColumnName = "Name"
},
new ExcelColumn {
ColumnName = "Customer Reference Number"
},
new ExcelColumn {
ColumnName = "Gender", ValidationDataSet = new List <string>()
{
"Male", "Female"
}
}
};
using (IExcelGenerator generator = new ExcelGenerator("anand.xlsx", "serviceSheet", "data"))
{
ExcelColumns excelColumns = new ExcelColumns(columns);
var dataPopulater = generator.GetExcelDataPopulater(excelColumns);
dataPopulater.PopulateData();
generator.NativeExcelPackageObject.SaveAs(new FileInfo("./students.xlsx"));
}
}
/// <summary>
/// 取出列名及列号
/// </summary>
/// <param name="columnNameRow"></param>
public void Caculate_Columns(int columnNameRow)
{
try
{
Cells cells = _workSheet.Cells;
//int row = 0;
int row2 = cells.MaxDataRow;
int column = 0;
int num = cells.MaxDataColumn;
if (num < 4)
{
num = 4;
}
this.ExcelColumns = new Dictionary <string, int>();
for (int i = column; i <= num; i++)
{
try
{
string a = (cells[columnNameRow, i].Value == null) ? "" : cells[columnNameRow, i].Value.ToString();
ExcelColumns.Add(a, i);
}
catch
{
}
}
}
catch
{
//Commons.ShowMessage_Info(DialogType.Warring, ex.Message);
}
}
public static void dumpExcel(IEnumerable<schools.dumpRow> datas, Stream str) {
var cols = new ExcelColumns(typeof(schools.dumpRow));
foreach (var key in cols.Keys.Where(k => k.EndsWith("Start") || k.EndsWith("End"))) {
var col = cols[key];
col.Format = ExcelFormat.Date;
col.getData = d => LowUtils.IntToDate((long)d);
}
ExcelExport.linqToExcel(datas, str, cols);
}
/// <summary>
/// Removes the selected excel column from the excel column list
/// </summary>
public void RemoveFieldFromExcelColumns()
{
if (SelectedExcelColumn != null)
{
int columnNum = this.SelectedExcelColumn.ColumnNumber;
ExcelColumns.Remove(ExcelColumns.Where(i => i.ColumnNumber == columnNum).Single());
RenumberExcelColumns();
}
}
public void ShoudGetAllColumns()
{
ExcelColumns columns = new ExcelColumns();
ExcelColumn column = new ExcelColumn {
ColumnName = "Test", ErrorMessage = "not"
};
columns.Add(column);
Assert.IsType <List <ExcelColumn> >(columns.GetColumns());
}
public List <string> PKColumnNameList()
{
List <string> result = new List <string>();
foreach (ExcelColumn aExcelColumns in ExcelColumns.Where(aExcelColumns => aExcelColumns.IsPK == 1 && !result.Contains(aExcelColumns.ColumnName)))
{
result.Add(aExcelColumns.ColumnName);
}
return(result);
}
public List <string> ColumnNameList()
{
List <string> result = new List <string>();
if (ExcelColumns != null && ExcelColumns.Count > 0)
{
result.AddRange(ExcelColumns.Select(c => c.ColumnName));
}
return(result);
}
public ExcelColumn ExcelColumnKey(string columnName)
{
ExcelColumn aExcelColumn = null;
if (ExcelColumns != null && ExcelColumns.Any(c => c.ColumnName == columnName))
{
aExcelColumn = ExcelColumns.SingleOrDefault(c => c.ColumnName == columnName);
}
return(aExcelColumn);
}
public void ShouldAddOneColumn()
{
ExcelColumns columns = new ExcelColumns();
ExcelColumn column = new ExcelColumn {
ColumnName = "Test", ErrorMessage = "not"
};
columns.Add(column);
Assert.True(columns.Count() == 1);
}
public List <int> GetAllExcelColumnIndexes()
{
if (string.IsNullOrWhiteSpace(ExcelColumns))
{
return(new List <int>());
}
return(ExcelColumns.Replace(" ", "").Split(',')
.Select(x => x.Split('-'))
.Select(p => new { First = int.Parse(p.First()), Last = int.Parse(p.Last()) })
.SelectMany(x => Enumerable.Range(x.First, x.Last - x.First + 1))
.OrderBy(z => z).ToList());
}
public void ShouldGetExcelDataPopulater()
{
ExcelColumns columns = new ExcelColumns();
ExcelColumn column = new ExcelColumn {
ColumnName = "Test", ErrorMessage = "not"
};
columns.Add(column);
IExcelDataPopulater excelDataPopulater = excelGenerator.GetExcelDataPopulater(columns);
Assert.NotNull(excelDataPopulater);
}
/// <summary>
/// 返回建值对《key：外键ColumnName,Value：父Sheet中对应列名》
/// </summary>
/// <returns></returns>
public Dictionary <string, string> FKColumnNameList()
{
Dictionary <string, string> result = null;
foreach (ExcelColumn aExcelColumns in ExcelColumns.Where(aExcelColumns => aExcelColumns.IsFK == 1))
{
if (result == null)
{
result = new Dictionary <string, string>();
}
if (!result.Keys.Contains(aExcelColumns.ColumnName))
{
result.Add(aExcelColumns.ColumnName, aExcelColumns.FKColumnName);
}
}
return(result);
}
public void ShoudPopulateData()
{
ExcelColumns columns = new ExcelColumns();
ExcelColumn column = new ExcelColumn {
ColumnName = "Test", ErrorMessage = "not"
};
columns.Add(column);
IExcelDataPopulater excelDataPopulater = new ExcelDataPopulater(excelGenerator.NativeExcelPackageObject, excelGenerator.UiSheetName, excelGenerator.DataSheetName)
{
ExcelColumns = columns
};
excelDataPopulater.PopulateData();
byte[] data = excelGenerator.GetAsByteArray();
Assert.NotNull(data);
}
public ExcelTable(ref ExcelColumns columns)
{
_columns = columns;
_table = new ExcelObject("Table");
if (_table != null)
{
int _column_count = columns.Count();
for (int index = 0; index < _column_count; index++)
{
ExcelObject _value = columns.GetColumnObject(index);
_table.AddObject(-(index + 1), ref _value);
};
};
_rows = new SortedList();
}
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 ExcelColumns CreateColumns(ref ExcelColumns exist_columns, ref DataTable data_table)
{
ExcelColumns result = null;
int column_count = 0;
if (exist_columns == null)
result = new ExcelColumns();
else
result = exist_columns;
if ((result != null) && (data_table != null))
{
column_count = result.Count();
int global_index = 0;
for (int index = 0; index < data_table.Columns.Count; index++)
{
string _name = data_table.Columns[index].ColumnName.ToString();
string _alias = _name;
if (_name.ToLower() == "dateaquired")
{
_alias = "DateAcquired";
};
if (_column_filter.Length > 0)
{
if (_name.ToLower().Contains(_column_filter.ToLower()) && _name != "AssetGUID")
continue;
};
string _type = ConvertSystemTypeToExcelType(data_table.Columns[index].DataType.ToString());
string _system_type = data_table.Columns[index].DataType.ToString();
string _style_id = ConvertSystemTypeToExcelStyle(data_table.Columns[index].DataType.ToString());
int _width = ConvertSystemTypeToCellWidth(data_table.Columns[index].DataType.ToString());
//columns and rows stored on common table object, need set not collise indexes
result.AddColumn(column_count + global_index + 1, _name, _alias, _width, _type, _system_type, _style_id);
global_index++;
};
};
return result;
}
public ExcelRow AddHeader(ref ExcelTable excel_table, ref ExcelColumns exist_columns)
{
ExcelRow result = null;
if ((excel_table != null) && (exist_columns != null))
{
int column_count = exist_columns.Count();
if (column_count > 0)
{
result = AddRow(0, ref excel_table);
if (result != null)
{
string column_name = string.Empty;
string column_alias = string.Empty;
for (int index = 0; index < column_count; index++)
{
ExcelObject _object = exist_columns.GetColumnObject(index);
if (_object != null)
{
column_name = _object.GetPropertyValue("Name");
column_alias = _object.GetPropertyValue("Alias");
};
if (column_name.Length > 0)
result.SetHeaderValue(column_name, column_alias);
};
};
};
};
return result;
}
//Warning: this function not filter columns by filter;
public ExcelColumns AddColumn(ref ExcelColumns exist_columns, string column_name, string column_system_type)
{
ExcelColumns result = null;
int column_count = 0;
if (exist_columns == null)
result = new ExcelColumns();
else
result = exist_columns;
if ((result != null) && (column_name.Length > 0))
{
column_count = result.Count();
string _name = column_name;
string __type = ConvertSystemTypeToExcelType(column_system_type);
string __system_type = column_system_type;
string __style_id = ConvertSystemTypeToExcelStyle(column_system_type);
int __width = ConvertSystemTypeToCellWidth(column_system_type);
string _alias = _name;
if (_name.ToLower() == "dateaquired")
_alias = "DateAcquired";
result.AddColumn(column_count + 1, _name, _alias, __width, __type, __system_type, __style_id);
};
return result;
}
public ExcelColumns AddColumn(ref ExcelColumns exist_columns, ref DataColumn data_column)
{
ExcelColumns result = null;
int column_count = 0;
if (exist_columns == null)
result = new ExcelColumns();
else
result = exist_columns;
if ((result != null) && (data_column != null))
{
column_count = result.Count();
string _name = data_column.ColumnName.ToString();
if (_column_filter.Length > 0)
{
if (_name.ToLower().Contains(_column_filter.ToLower()) && _name != "AssetGUID")
{
string __type = ConvertSystemTypeToExcelType(data_column.DataType.ToString());
string __system_type = data_column.DataType.ToString();
string __style_id = ConvertSystemTypeToExcelStyle(data_column.DataType.ToString());
int __width = ConvertSystemTypeToCellWidth(data_column.DataType.ToString());
string _alias = _name;
if (_name.ToLower() == "dateaquired")
_alias = "DateAcquired";
//columns and rows stored on common table object, need set not collise indexes
result.AddColumn(column_count + 1, _name, _alias, __width, __type, __system_type, __style_id);
};
}
else
{
string _type = ConvertSystemTypeToExcelType(data_column.DataType.ToString());
string _system_type = data_column.DataType.ToString();
string _style_id = ConvertSystemTypeToExcelStyle(data_column.DataType.ToString());
int _width = ConvertSystemTypeToCellWidth(data_column.DataType.ToString());
string _alias = _name;
if (_name.ToLower() == "dateaquired")
_alias = "DateAcquired";
//columns and rows stored on common table object, need set not collise indexes
result.AddColumn(column_count + 1, _name, _alias, _width, _type, _system_type, _style_id);
};
};
return result;
}
/// <summary>
/// Adjusts the numbers of the excelcolumns
/// </summary>
public void RenumberExcelColumns()
{
int count = 1;
foreach (ExcelCell x in this.ExcelColumns)
{
x.ColumnNumber = count;
x.ColumnLetter = ExcelService.ReturnColumLetter(count);
count++;
}
ObservableCollection <ExcelCell> newExcelColumns = new ObservableCollection <ExcelCell>(ExcelColumns.OrderBy(i => i.ColumnNumber));
this.ExcelColumns = newExcelColumns;
}
public bool IsEveryColumnCorred()
{
bool result = ExcelColumns.All(aExcelColumn => ColumnRule(aExcelColumn.ColumnName) != null);
return(result);
}
