private IXLColumn CreateColumn(string name, IXLColumn previousColumn)
{
var newColumn = previousColumn.ColumnRight();
newColumn.FirstCell().SetValue(name);
return(newColumn);
}
public void InsertingColumnsPreservesFormatting()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
IXLColumn column2 = ws.Column(2);
column2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
column2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);
column1.InsertColumnsAfter(1);
column1.InsertColumnsBefore(1);
column2.InsertColumnsBefore(1);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FrenchLilac, ws.Column(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Xanadu, ws.Column(5).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(2, 1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(2, 5).Style.Fill.BackgroundColor);
}
public void InsertingColumnsBefore2()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Columns("1,3").Style.Fill.SetBackgroundColor(XLColor.Red);
ws.Column(2).Style.Fill.SetBackgroundColor(XLColor.Yellow);
ws.Cell(2, 2).SetValue("X").Style.Fill.SetBackgroundColor(XLColor.Green);
IXLColumn column1 = ws.Column(1);
IXLColumn column2 = ws.Column(2);
IXLColumn column3 = ws.Column(3);
IXLColumn columnIns = ws.Column(2).InsertColumnsBefore(1).First();
string outputPath = Path.Combine(TestHelper.TestsOutputDirectory, @"ForTesting\Sandbox.xlsx");
wb.SaveAs(outputPath);
Assert.AreEqual(XLColor.Red, ws.Column(1).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(1).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(1).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Column(3).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, ws.Column(3).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Column(3).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", ws.Column(3).Cell(2).GetString());
Assert.AreEqual(XLColor.Red, columnIns.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, columnIns.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, columnIns.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", column2.Cell(2).GetString());
}
private static DataType GuessType(IXLColumn col)
{
var colDistinctValues = col
.CellsUsed()
.Skip(1)
.Select(cell => cell.Value.ToString())
.Distinct()
.ToArray();
return(GuessType(colDistinctValues));
}
public static void AreColumnsEquals(IXLColumn expected, IXLColumn actual, string message = null)
{
if (expected.Equals(actual))
{
return;
}
message ??= string.Empty;
Assert.AreEqual(expected.IsHidden, actual.IsHidden, string.Format(message, "IsHidden"));
Assert.AreEqual(expected.OutlineLevel, actual.OutlineLevel, string.Format(message, "OutlineLevel"));
Assert.AreEqual(expected.Width, actual.Width, 1e-6, string.Format(message, "Width"));
}
public IXLColumn CopyTo(IXLColumn column)
{
column.Clear();
var newColumn = (XLColumn)column;
newColumn.Width = Width;
newColumn.InnerStyle = InnerStyle;
AsRange().CopyTo(column);
return(newColumn);
}
public void InsertingColumnsBefore1()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet1");
ws.Columns("1,3").Style.Fill.SetBackgroundColor(XLColor.Red);
ws.Column(2).Style.Fill.SetBackgroundColor(XLColor.Yellow);
ws.Cell(2, 2).SetValue("X").Style.Fill.SetBackgroundColor(XLColor.Green);
IXLColumn column1 = ws.Column(1);
IXLColumn column2 = ws.Column(2);
IXLColumn column3 = ws.Column(3);
IXLColumn columnIns = ws.Column(1).InsertColumnsBefore(1).First();
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(2).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Column(3).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, ws.Column(3).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, ws.Column(3).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, ws.Column(4).Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", ws.Column(3).Cell(2).GetString());
Assert.AreEqual(ws.Style.Fill.BackgroundColor, columnIns.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, columnIns.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(ws.Style.Fill.BackgroundColor, columnIns.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column1.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Green, column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Yellow, column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.Red, column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual("X", column2.Cell(2).GetString());
}
public IXLColumn CopyTo(IXLColumn column)
{
column.Clear();
var newColumn = (XLColumn)column;
newColumn._width = _width;
newColumn.Style = GetStyle();
using (var asRange = AsRange())
asRange.CopyTo(column).Dispose();
return(newColumn);
}
public IXLColumn CopyTo(IXLColumn column)
{
column.Clear();
var newColumn = (XLColumn)column;
newColumn.Width = Width;
newColumn.InnerStyle = InnerStyle;
newColumn.IsHidden = IsHidden;
(this as XLRangeBase).CopyTo(column);
return(newColumn);
}
private void CreateSheetAndHeaders()
{
_workBook = new XLWorkbook();
_sheet = _workBook.Worksheets.Add(ResourceSheetName);
_projectColumn = CreateColumn(_sheet, ProjectColumnName);
_fileColumn = CreateColumn(FileColumnName, _projectColumn);
_nameColumn = CreateColumn(NameColumnName, _fileColumn);
_defaultCultureColumn = CreateColumn(DefaultCultureColumn, _nameColumn);
CreateColumn("da", _defaultCultureColumn);
_sheet.SheetView.FreezeRows(1);
}
/// <summary>
/// ''' TODO AGGIUNGERE PARAMETRO ROUNDING OPPURE CELLFORMAT
/// ''' </summary>
/// ''' <param name="sheetName"></param>
/// ''' <param name="labelCell"></param>
/// ''' <param name="labelCellValue"></param>
/// ''' <param name="excludeHeaders"></param>
/// ''' <returns></returns>
public ExcelHelper AddRowTotal(string sheetName, string labelCell = "A", string labelCellValue = "TOT.", bool excludeHeaders = true)
{
IXLRange range = this.GetRangeUsed(sheetName);
if (range == null)
{
//SD.Log("range not found in " + sheetName, SD.LogLevel.Error);
return(this);
}
int rowsNumber = range.RowCount();
IXLCell lastCellUsed = range.LastCellUsed();
IXLColumn lastColUsed = lastCellUsed.WorksheetColumn();
IXLRow lastRowUsed = lastCellUsed.WorksheetRow();
string lastColLetter = lastColUsed.ColumnLetter();
int lastRowNumber = lastRowUsed.RowNumber();
IXLRows rows = lastRowUsed.InsertRowsBelow(1);
//IXLRow newRow = rows.Last();
var ws = this._workbook.Worksheets.Worksheet(sheetName);
IXLRow newRow = ws.LastRowUsed().RowBelow();
if (labelCell.Trim() != "")
{
newRow.Cell(labelCell).Value = labelCellValue;
newRow.Cell(labelCell).Style.Font.Bold = true;
}
var firstTotalCellAddress = newRow.FirstCell().CellRight().Address;
var lastTotalCellAddress = newRow.Cell(lastColLetter).Address;
IXLRange rangeTotal = this.GetRangeUsed(sheetName, firstTotalCellAddress, lastTotalCellAddress);
//int i = rangeTotal.Cells().Count() + 1;
int i = rangeTotal.ColumnCount() + 1;
int firstDataRowIndex = 0;
// escludo la riga delle intestazioni
if (excludeHeaders)
{
firstDataRowIndex = 2;
}
for (int k = 1; k <= i; k++)
{
XLDataType colDataType = newRow.Cell(k).CellAbove(1).DataType;
if (colDataType == XLDataType.Number)
{
string colLetter = newRow.Cell(k).Address.ColumnLetter;
string formula = "=SUM(" + colLetter + firstDataRowIndex.ToString() + ":" + colLetter + rowsNumber.ToString() + ")";
this.AddFormula(sheetName, newRow.Cell(k).Address, formula);
}
}
newRow.AsRange().RangeUsed().Style.Border.TopBorder = XLBorderStyleValues.Thick;
return(this);
}
private static void CreateColumns(IXLColumn column)
{
column.FirstCell()
.SetValue("Forr\u00E1s ID").CellRight()
.SetValue("Forr\u00E1s").CellRight()
.SetValue("Projekt ID").CellRight()
.SetValue("Projekt").CellRight()
.SetValue("Egy\u00E9ni webc\u00EDm").CellRight()
.SetValue("Felhaszn\u00E1l\u00F3").CellRight()
.SetValue("\u00C1llapota").CellRight()
.SetValue("IP").CellRight()
.SetValue("Elkezd\u0151d\u00F6tt").CellRight()
.SetValue("Befejez\u0151d\u00F6tt").CellRight()
.SetValue("Hivatkoz\u00E1s").CellRight();
}
public void ApplyStyle(IXLColumn column)
{
switch (Type)
{
case "percent":
column.Style.NumberFormat.Format = "0%";
column.Width = 7;
break;
case "integer":
column.Style.NumberFormat.Format = "# ##0;-# ##0;0";
column.Width = 10;
break;
case "decimal":
column.Style.NumberFormat.Format = "# ##0.0##;_-# ##0.0##;0";
column.Width = 10;
break;
case "datetime":
case "datetime+utc":
column.Style.DateFormat.Format = "dd.mm.yyyy hh:mm:ss";
column.Width = 20;
break;
case "date":
case "date+utc":
column.Style.DateFormat.Format = "dd.mm.yyyy";
column.Width = 10;
break;
case "time":
case "time+utc":
column.Style.DateFormat.Format = "hh:mm:ss";
column.Width = 10;
break;
}
if (Width > 0)
{
column.Width = Width;
}
if (!string.IsNullOrWhiteSpace(FontColor))
{
column.Style.Font.FontColor = FontColor.StartsWith("#") ? XLColor.FromHtml(FontColor) : XLColor.FromName(FontColor);
}
}
public void CopyingColumns()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("Sheet");
IXLColumn column1 = ws.Column(1);
column1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
column1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
column1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
column1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromColor(Color.MediumSeaGreen));
column1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
column1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));
ws.Cell(1, 2).Value = column1;
ws.Cell(1, 3).Value = column1.Column(1, 7);
IXLColumn column2 = ws.Column(2);
Assert.AreEqual(XLColor.Red, column2.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column2.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column2.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column2.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column2.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column2.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column2.Cell(7).Style.Fill.BackgroundColor);
IXLColumn column3 = ws.Column(3);
Assert.AreEqual(XLColor.Red, column3.Cell(1).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromArgb(1, 1, 1), column3.Cell(2).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), column3.Cell(3).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromIndex(26), column3.Cell(4).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromColor(Color.MediumSeaGreen),
column3.Cell(5).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromName("Blue"), column3.Cell(6).Style.Fill.BackgroundColor);
Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), column3.Cell(7).Style.Fill.BackgroundColor);
}
// 统计一列里面所有单元的最大字符数。注：字符数约定按照西文字符数计算，一个汉字等于两个西文字符
static int GetMaxChars(IXLColumn column)
{
int max = 0;
foreach (IXLCell cell in column.CellsUsed())
{
// 跳过 Merged 的 Cell。也就是表格标题
if (cell.IsMerged())
{
continue;
}
string text = cell.GetString();
int current = GetCharWidth(text);
if (current > max)
{
max = current;
}
}
return(max);
}
static void Main(string[] args)
{
IXLWorkbook workbook = new XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Excel Örnek\\Verilerim.xlsx");
IXLWorksheet worksheet = workbook.Worksheet("Veriler");
IXLRange range = worksheet.RangeUsed();
IXLRangeRow rangeRow = range.FirstRow();
IXLRow row = worksheet.Row(5);
IXLColumn column = worksheet.Column("C");
double width = column.ColumnRight(5).Width;
Console.WriteLine("Sütun Genişliği:" + width);
Console.WriteLine("Value : " + row.FirstCell().Value);
Console.WriteLine("C5'in Değeri: " + worksheet.Cell("C5").Value);
IXLAddress address = worksheet.Cell("C5").Address;
IXLCell cell = worksheet.Cell("C5");
for (int i = 1; i <= range.RowCount(); i++)
{
if (range.Row(i).Cell(2).Value.ToString() == "Yılmaz")
{
IXLAddress address1 = range.Row(i).Cell(2).Address;
Console.WriteLine(address1.RowNumber + " / " + address1.ColumnLetter);
break;
}
}
Console.Read();
}
private void SetColumnsWidth(IXLRange range, IList <ExcelDynamicColumn> columns)
{
for (int i = 0; i < columns.Count; i++)
{
ExcelDynamicColumn column = columns[i];
if (column.Width == null && !column.AdjustToContent)
{
continue;
}
if (Type == PanelType.Vertical)
{
IXLColumn excelColumn = range.Cell(1, i + 1).WorksheetColumn();
if (column.Width != null)
{
excelColumn.Width = column.Width.Value;
}
if (column.AdjustToContent)
{
excelColumn.AdjustToContents();
}
}
else
{
IXLRow excelRow = range.Cell(i + 1, 1).WorksheetRow();
if (column.Width != null)
{
excelRow.Height = column.Width.Value;
}
if (column.AdjustToContent)
{
excelRow.AdjustToContents();
}
}
}
}
public IXLColumn CopyTo(IXLColumn column)
{
column.Clear();
var newColumn = (XLColumn)column;
newColumn._width = _width;
newColumn.Style = GetStyle();
using (var asRange = AsRange())
asRange.CopyTo(column).Dispose();
return newColumn;
}
public async Task <bool> ExportToExcelFileAsync(DataToExport dataToExport, string filePath)
{
bool success = false;
await Task.Run(() =>
{
try
{
using var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet();
string canalType = dataToExport.CanalCharacteristics.canalType;
IList <Parameter> canalGeometryParameters = dataToExport.CanalCharacteristics.Item2;
ws.Cell("A2").SetValue("Тип канала");
ws.Cell("A3").SetValue(canalType);
for (int i = 0; i < canalGeometryParameters.Count; i++)
{
ws.Cell(2, i + 4).SetValue(canalGeometryParameters[i].Name);
ws.Cell(3, i + 4).SetValue(canalGeometryParameters[i].Value + $" {canalGeometryParameters[i].MeasureUnit}");
}
string materialType = dataToExport.MaterialCharacteristics.materialType;
IList <Parameter> materialPropertyParameters = dataToExport.MaterialCharacteristics.Item2;
ws.Cell("A5").SetValue("Тип материала");
ws.Cell("A6").SetValue(materialType);
for (int i = 0; i < materialPropertyParameters.Count; i++)
{
ws.Cell(5, i + 4).SetValue(materialPropertyParameters[i].Name);
ws.Cell(6, i + 4).SetValue(materialPropertyParameters[i].Value + $" {materialPropertyParameters[i].MeasureUnit}");
}
IXLColumn lastMergedColumn = ws.LastColumnUsed();
ws.Range(ws.Cell(1, 1), ws.LastColumnUsed().Cell(1)).Merge().SetValue("Входные параметры");
IXLCell xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> variableParameters = dataToExport.VariableParameters;
for (int i = 0; i < variableParameters.Count; i++)
{
xLCell.SetValue(variableParameters[i].Name);
xLCell = xLCell.CellBelow().SetValue(variableParameters[i].Value + $" {variableParameters[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Варьируемые параметры");
lastMergedColumn = ws.LastColumnUsed();
xLCell = ws.LastColumnUsed().ColumnRight().ColumnRight().Cell(2);
IList <Parameter> empiricalParametersOfMathModel = dataToExport.EmpiricalParametersOfMathModel;
for (int i = 0; i < empiricalParametersOfMathModel.Count; i++)
{
xLCell.SetValue(empiricalParametersOfMathModel[i].Name);
xLCell = xLCell.CellBelow().SetValue(empiricalParametersOfMathModel[i].Value + $" {empiricalParametersOfMathModel[i].MeasureUnit}");
xLCell = xLCell.CellRight().CellAbove();
}
ws.Range(lastMergedColumn.ColumnRight().ColumnRight().Cell(1),
ws.LastColumnUsed().Cell(1)).Merge().SetValue("Эмпирические коэффициенты математической модели");
IDictionary <string, IList <Parameter> > discreteOutputParameters = dataToExport.DiscreteOutputParameters;
xLCell = xLCell.CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow().CellBelow()
.WorksheetRow().Cell(1);
foreach (KeyValuePair <string, IList <Parameter> > keyValuePair in discreteOutputParameters)
{
IXLCell firstCell = xLCell;
foreach (Parameter parameter in keyValuePair.Value)
{
xLCell.SetValue(parameter.Name);
xLCell = xLCell.CellBelow().SetValue($"{parameter.Value} {parameter.MeasureUnit}");
xLCell = xLCell.CellAbove().CellRight();
}
if (keyValuePair.Value.Count <= 0)
{
continue;
}
if (keyValuePair.Value.Count == 1)
{
xLCell = xLCell.CellRight();
continue;
}
ws.Range(firstCell.CellAbove(), ws.LastRowUsed().LastCellUsed().CellAbove().CellAbove()).
Merge().SetValue(keyValuePair.Key);
xLCell = xLCell.CellRight();
}
xLCell = xLCell.CellLeft().CellLeft().CellAbove().CellAbove();
ws.Range(xLCell, xLCell.WorksheetRow().Cell(1)).Merge().SetValue("Результаты");
xLCell = ws.LastRowUsed().Cell(1).CellBelow().CellBelow();
ws.Range(xLCell, xLCell.CellRight().CellRight().CellRight()).Merge().SetValue("Таблица результатов");
IList <(Parameter coordinate, Parameter temperature, Parameter viscosity)> resultsTable = dataToExport.ContiniousResults;
xLCell.CellBelow().SetValue($"{resultsTable[0].coordinate.Name}, {resultsTable[0].coordinate.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].temperature.Name}, {resultsTable[0].temperature.MeasureUnit}")
.CellRight().SetValue($"{resultsTable[0].viscosity.Name}, {resultsTable[0].viscosity.MeasureUnit}");
xLCell = xLCell.CellBelow().CellBelow();
NumberFormatInfo nfi = new NumberFormatInfo
{
NumberDecimalSeparator = "."
};
foreach (var(coordinate, temperature, viscosity) in resultsTable)
{
xLCell.SetValue(((double)coordinate.Value)
.ToString($"F{dataToExport.CoordinatePrecision}", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)temperature.Value).ToString($"F2", nfi))
.SetDataType(XLDataType.Number)
.CellRight().SetValue(((double)viscosity.Value).ToString("F2", nfi))
.SetDataType(XLDataType.Number);
xLCell = xLCell.CellBelow();
}
//ws.RowsUsed().AdjustToContents();
//ws.ColumnsUsed().AdjustToContents();
//using var stream = new MemoryStream();
//dataToExport.TemperaturePlot.Save(stream, ImageFormat.Png);
//ws.AddPicture(stream).MoveTo(ws.Cell("H8"))
// .WithSize(dataToExport.TemperaturePlot.Width, dataToExport.TemperaturePlot.Height);
//using var stream1 = new MemoryStream();
//dataToExport.ViscosityPlot.Save(stream1, ImageFormat.Png);
//ws.AddPicture(stream1).MoveTo(ws.Cell("O8"))
// .WithSize(dataToExport.ViscosityPlot.Width, dataToExport.ViscosityPlot.Height);
wb.SaveAs(filePath);
success = true;
}
catch { success = false; }
});
return(success);
}
public static Bio2DA ReadExcelTo2DA(IExportEntry export, string Filename)
{
var Workbook = new XLWorkbook(Filename);
IXLWorksheet iWorksheet = null;
if (Workbook.Worksheets.Count() > 1)
{
try
{
iWorksheet = Workbook.Worksheet("Import");
}
catch
{
MessageBox.Show("Import Sheet not found");
return(null);
}
}
else
{
iWorksheet = Workbook.Worksheet(1);
}
//Do we want to limit user to importing same column structure as existing? Who would be stupid enough to do something else??? ME.
// - Kinkojiro, 2019
//STEP 1 Clear existing data
Bio2DA bio2da = new Bio2DA();
bio2da.export = export;
//STEP 2 Read columns and row names
//Column names
IXLRow hRow = iWorksheet.Row(1);
foreach (IXLCell cell in hRow.Cells(hRow.FirstCellUsed().Address.ColumnNumber, hRow.LastCellUsed().Address.ColumnNumber))
{
if (cell.Address.ColumnNumber > 1) //ignore excel column 1
{
bio2da.ColumnNames.Add(cell.Value.ToString());
}
}
//Row names
IXLColumn column = iWorksheet.Column(1);
foreach (IXLCell cell in column.Cells())
{
if (cell.Address.RowNumber > 1) //ignore excel row 1
{
bio2da.RowNames.Add(cell.Value.ToString());
}
}
//Populate the Bio2DA now that we know the size
bio2da.Cells = new Bio2DACell[bio2da.RowCount, bio2da.ColumnCount];
//Step 3 Populate the table.
//indices here are excel based. Subtract two to get Bio2DA based.
for (int rowIndex = 2; rowIndex < (bio2da.RowCount + 2); rowIndex++)
{
for (int columnIndex = 2; columnIndex < bio2da.ColumnCount + 2; columnIndex++)
{
IXLCell xlCell = iWorksheet.Cell(rowIndex, columnIndex);
string xlCellContents = xlCell.Value.ToString();
if (!string.IsNullOrEmpty(xlCellContents))
{
Bio2DACell newCell = new Bio2DACell();
if (int.TryParse(xlCellContents, out int intVal))
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_INT;
newCell.Data = BitConverter.GetBytes(intVal);
}
else if (float.TryParse(xlCellContents, out float floatVal))
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_FLOAT;
newCell.Data = BitConverter.GetBytes(floatVal);
}
else
{
newCell.Type = Bio2DACell.Bio2DADataType.TYPE_NAME;
newCell.Pcc = export.FileRef; //for displaying, if this displays before the export is reloaded and 2da is refreshed
newCell.Data = BitConverter.GetBytes((long)export.FileRef.FindNameOrAdd(xlCellContents)); //long because names are 8 bytes not 4
}
bio2da[rowIndex - 2, columnIndex - 2] = newCell;
}
else
{
bio2da.IsIndexed = true; //Null cells = indexing
}
}
}
return(bio2da);
}
